常见的工具SQL

修改密码

ALTER USER wolf IDENTIFIED BY wolf;

解锁

alter user wolf account unlock;

创建用户并附权

create user sett identified by 123456;
grant connect,resource,dba to sett;

查看锁表信息

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 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;

杀 session

alter system kill session '837,728'

表分析

1) select * from all_tables where table_name=’TEMP_TABLE’;（表名一定要大写）

如果对该表没有做过统计分析，则查询的大部分栏位值都为空

2) analyze table tableName compute statistics;

通用杀session 语句

select 'alter system kill session ''' || a.SID || ',' || a.SERIAL# || ''';'
  from v$session a
 where sid in
       (select sid
          from v$lock
         where id1 = (select object_id
                        from user_objects --dba_objects
                       where object_name = upper('TMP_365100045')))

表恢复

flashback table tablename to timestamp to_timestamp('2016-05-07/17:01:50','yyyy-mm-dd hh24:mi:ss');
flashback database to timestamp to_timestamp('frombyte 2007-2-12 12:00:00','yyyy-mm-dd hh24:mi:ss');
insert into AMLDATA.T_NOR_CLIENT_20200101 select * from AMLDATA.T_NOR_CLIENT as of timestamp sysdate-20/1440;

查看存储过程中依赖表

select  a.referenced_owner || '.' || a.referenced_name 
from dba_dependencies a where a.name='存储过程名称（要大些）' and a.referenced='TABLE'

查找存储过程OPERATIONDATA_IMP被哪些session锁住而无法编译

select * FROM dba_ddl_locks where name =upper('OPERATIONDATA_IMP');

从而得到session_id，然后通过

select t.sid,t.serial# from v$session t 
where t.sid=&session_id;

得到sid和serial#

alter system kill session 'sid,serial#'

最后用; kill 相关session即可。