oracle数据库⽇常维护⼿册
数据库操作分类
DDL：数据库模式定义语⾔，关键字：create
DML：数据操纵语⾔，关键字：Insert、delete、update
DCL：数据库控制语⾔，关键字：grant、remove
DQL：数据库查询语⾔，关键字：select
锁表
为什么会锁表？
DML锁⼜可以分为，⾏锁、表锁、死锁
⾏锁：当事务执⾏数据库插⼊、更新、删除操作时，该事务⾃动获得操作表中操作⾏的排它锁。
表级锁：当事务获得⾏锁后，此事务也将⾃动获得该⾏的表锁(共享锁)，以防⽌其它事务进⾏DDL语句影
响记录⾏的更新。事务也可以在进⾏过程中获得共享锁或排它锁，只有当事务显⽰使⽤LOCK TABLE语句显⽰的定义⼀个排它锁时，事务才会获得表上的排它锁，也可使⽤LOCK TABLE显⽰的定义⼀个表级的共享锁(LOCK TABLE具体⽤法请参考相关⽂档)。
死锁：当两个事务需要⼀组有冲突的锁，⽽不能将事务继续下去的话，就出现死锁。
如事务1在表A⾏记录#3中有⼀排它锁，并等待事务2在表A中记录#4中排它锁的释放，⽽事务2在表A记录⾏#4中有⼀排它锁，并等待事务1在表A中记录#3中排它锁的释放，事务1与事务2彼此等待，因此就造成了死锁。死锁⼀般是因拙劣的事务设计⽽产⽣。
死锁只能使⽤SQL下:alter system kill session “sid,serial#”；或者使⽤相关操作系统kill进程的命令，如UNIX下kill -9 sid，或者使⽤其它⼯具杀掉死锁进程。
DDL锁⼜可以分为：排它DDL锁、共享DDL锁、分析锁
排它DDL锁：创建、修改、删除⼀个数据库对象的DDL语句获得操作对象的排它锁。如使⽤alter table语句时，为了维护数据的完成性、⼀致性、合法性，该事务获得⼀排它DDL锁。
共享DDL锁：需在数据库对象之间建⽴相互依赖关系的DDL语句通常需共享获得DDL锁。如创建⼀个包，该包中的过程与函数引⽤了不同的数据库表，当编译此包时，该事务就获得了引⽤表的共享DDL锁。
分析锁：ORACLE使⽤共享池存储分析与优化过的SQL语句及PL/SQL程序，使运⾏相同语句的应⽤速度更快。⼀个在共享池中缓存的对象获得它所引⽤数据库对象的分析锁。分析锁是⼀种独特的DDL锁类型，ORACLE使⽤它追踪共享池对象及它所引⽤数据库对象之间的依赖关系。当⼀个事务修改或删除了共享池持有分析锁的数据库对象时，ORACLE使共享池中的对象作废，下次在引⽤这条SQL/PLSQL语句
时，ORACLE重新分析编译此语句。
涉及的表
SELECT * FROM v$lock;
SELECT * FROM v$sqlarea;
SELECT * FROM v$session;
SELECT * FROM v$process ;
SELECT * FROM v$locked_object;
SELECT * FROM all_objects;
SELECT * FROM v$session_wait;
--查看被锁的表
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
--查看那个⽤户那个进程照成死锁
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
--查看连接的进程
SELECT sid, serial#, username, osuser FROM v$session;
--查某session 正在执⾏的sql语句，从⽽可以快速定位到哪些操作或者代码导致事务⼀直进⾏没有结束等.
SELECT
sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN
(SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.sid = '321') /* 此处为SID*/
ORDER BY piece ASC;
--查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode
SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
FROM v$session s, v$lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid;
这个语句将查到数据库中所有的DML语句产⽣的锁，还可以发现，
任何DML语句其实产⽣了两个锁，⼀个是表锁，⼀个是⾏锁。
--杀掉进程 sid,serial#
alter system kill session'111,12305';
误删数据恢复
删除表数据有三种⽅式：delete、drop和truncate
delete误删除的解决⽅法
原理：
利⽤oracle提供的闪回⽅法，如果在删除数据后还没做⼤量的操作（只要保证被删除数据的块没被覆写），就可以利⽤闪回⽅式直接回删除的数据
具体步骤为：
例如:
误删除了100条数据
删除语句为：
delete from 表名 where kid = '5';
*确定删除数据的时间（在删除数据之前的时间就⾏，不过最好是删除数据的时间点）
*可以⽤⼀下语句出执⾏删除语句的时间
select r.FIRST_LOAD_TIME,r.* from v$sqlarea r order by r.FIRST_LOAD_TIME desc ;
*⽤以下语句出删除的数据：
select * from 表名 as of timestamp to_timestamp('删除时间点','yyyy-mm-dd hh24:mi:ss') where kid = '5'
select * from 表名 as of timestamp sysdate - 3/1440 where kid = '5' ; --3分钟之前的数据
*把删除的数据重新插⼊原表：
注意要保证主键不重复。
insert into 表名 (select * from 表名 as of timestamp to_timestamp('删除时间点','yyyy-mm-dd hh24:mi:ss') where kid = '5');
insert into 表名 (select * from 表名 as of timestamp sysdate - 3/1440 where kid = '5');
如果表结构没有发⽣改变，还可以直接使⽤闪回整个表的⽅式来恢复数据。
具体步骤为：
表闪回要求⽤户必须要有flash any table权限
--开启⾏移动功能
·alter table 表名 enable row movement
drop删除表--恢复表数据
·flashback table 表名 to timestamp to_timestamp(删除时间点','yyyy-mm-dd hh24:mi:ss')
--关闭⾏移动功能 ( 千万别忘记 )
·alter table 表名 disable row movement
drop、trancate误删除的解决⽅法
原理：由于oracle在删除表时，没有直接清空表所占的块,oracle把这些已删除的表的信息放到了⼀个虚拟容器“回收站”中，⽽只是对该表的数据块做了可以被覆写的标志，所以在块未被重新使⽤前还可以恢复。
具体步骤：
*查询这个“回收站”或者查询user_table视图来查已被删除的表:
· select table_name,dropped from user_tables
· select object_name,original_name,type,droptime from user_recyclebin
在以上信息中，表名都是被重命名过的，字段table_name或者object_name就是删除后在回收站中的存放表名
*如果还能记住表名，则可以⽤下⾯语句直接恢复：
flashback table 原表名 to before drop
如果记不住了，也可以直接使⽤回收站的表名进⾏恢复，然后再重命名，参照以下语句：
flashback table "回收站中的表名(如：Bin$DSbdfd4rdfdfdfegdfsf==$0)" to before drop rename to 新表名
oracle的闪回功能除了以上基本功能外，还可以闪回整个数据库：
使⽤数据库闪回功能，可以使数据库回到过去某⼀状态, 语法如下：
SQL>alter database flashback on
SQL>flashback database to scn SCNNO;
SQL>flashback database to timestamp to_timestamp('2007-2-12 12:00:00','yyyy-mm-dd hh24:mi:ss');
误更新恢复
*可以⽤⼀下语句出执⾏删除语句的时间
select r.FIRST_LOAD_TIME,r.* from v$sqlarea r order by r.FIRST_LOAD_TIME desc ;
*创建⼀个表保存取出的备份数据
create table 表名_bak
as
select * from 表名 as of timestamp sysdate -5/1440;
操作成功后你看看新表⾥⾯是不是你之前的数据.如果是的话再把新表数据弄到原表就好了
发表评论