重要的sql

重要视图：

  user_tables   user_objects  user_indexes  user_constraints  user_tab_columns  user_cons_columns

select num_rows,blocks from dba_tables where table_name=？？？  v$sessino  v$event_name v$system_event ;

收集信息：

begin

dbms_stats.gather_table_stats('SYS','表名');

end;

查询所有事务的状态

select status from v$transaction

查询所有持有锁的会话和对象

     select session_id,oracle_username,locked_mode,

            object_name,object_type,dba_objects.object_id

     from dba_objects,v$locked_object

     where dba_objects.object_id=v$locked_object.OBJECT_ID;

查询所有被锁阻塞的会话，一旦有锁释放，最前面的最先获得锁

select sid,type,lmode,request,block from v$enqueue_lock where type in('TX','TM')

查询锁的所有信息，所类型，请求什么类型，阻塞了几个会话

select sid,type,lmode,request,block from v$lock where type in('TX','TM')

哪个会话阻塞了按个会话

select a.sid blocker_sid,a.serial#,a.username block_username,b.type,

decode(b.lmode,0,'None'，1,'null',2,'row share', 3,'row exclusive',4 ,'share',5,'share row exclusive',6,'exclusive') lock_lmod,

b.ctime time_hold,c.sid waiter_sid,d.USERNAME waiting_username,

decode(c.request,0,'None'，1,'null',2,'row share', 3,'row exclusive',4 ,'share',5,'share row exclusive',6,'exclusive') request_lmod,

c.ctime time_waited

from v$lock b, v$enqueue_lock c,v$session a,v$session d

where a.sid = b.SID

and b.id1 = c.id1

and b.id2 = c.id2

and b.type = 'TX'

and b.block = 1

and c.sid = d.sid

当前会话sid

select userenv('sid') from dual;

杀死会话   sid,serial#这两个字段在v$session视图中

ALTER SYSTEM KILL SESSION 'sid,serial#';

查看硬解析，value是硬解析的次数

select name, value     

  from v$mystat a, v$statname b

where a.STATISTIC# = b.STATISTIC#

   and b.NAME like 'parse%';

   

 跟踪sql(生成的跟踪文件在E:\oracle\diag\rdbms\mydb\mysid\trace下 )

alter session set events='10053 trace name context forever,level 1';