Oracle查询锁表用户释放被锁的表SQL 及超时120S的SQL 误删表还原

女爷i 2022-08-09 17:49 271阅读 0赞

**--查看被锁的表**  
SELECT object\_name, machine, s.sid, s.serial\# ,o.OWNER  
FROM gv$locked\_object l, dba\_objects o, gv$session s   
WHERE l.object\_id　= o.object\_id   
AND l.session\_id = s.sid  
and o.OWNER='JC02\_LOAN';  
  
  
  
  
**--释放SESSION SQL: **  
\--alter system kill session 'sid, serial\#';

ALTER system kill session '44, 19193';

**---查询SQL超时120s的**  
select 'ALTER system kill session '''||s.sid||','||s.serial\# ||''';',s.machine,s.username,s.program,s.sid,s.serial\#,a.sql\_id,s.status,'OS id: '||p.spid "OS ID",s.event,'call\_et: '||s.last\_call\_et,'wait time: '|| wait\_time  
 "Wait\_time",a.sql\_text,s.sql\_hash\_value,s.state  
from v$session s,v$process p,v$sqlarea a  
 where s.paddr = p.addr  
and s.sql\_hash\_value = a.hash\_value(+)  
and s.type = 'USER'  
and s.status = 'ACTIVE'

and s.last\_call\_et > 120 ;

**--修改用户秘密**

**alter user system identified by values 'tiger';**

**--还原误删的表**

flashback table SYS\_ACL to before drop;

select 'flashback table ' || r.original\_name || ' to before drop rename to '|| r.original\_name ||'; ' ,r.\*  
  from recyclebin r  
 where r.type = 'TABLE'  
   and to\_char(to\_date(r.droptime, 'yyyy-MM-dd hh24:mi:ss'), 'yyyy-MM-dd') =  
       '2015-04-20'

create tablespace jypt datafile 'D:\\oracle\\product\\10.2.0\\oradata\\jieyue\\jypt.dbf ' size 100m autoextend on next 10m;  
create user jypt identified by password default tablespace jypt;  
grant dba to jypt;  
  
  
\#create tablespace loan datafile '\\u01\\app\\oracle\\oradata\\sales\\jieyue\\loan.dbf ' size 100m autoextend on next 10m;  
\#create user loan identified by loan default tablespace loan;  
\#grant dba to loan;