常用脚本

1：查询被锁住的对象

?

select b.owner, b.object_name, l.session_id, l.locked_mode
  from v$locked_object l, dba_objects b
 where b.object_id = l.object_id

locked_mode的含义：?

0：none?

1：null?空?
2：Row-S?行共享(RS)：共享表锁，sub?share??
3：Row-X?行独占(RX)：用于行的修改，sub?exclusive??
4：Share?共享锁(S)：阻止其他DML操作，share?
5：S/Row-X?共享行独占(SRX)：阻止其他事务操作，share/sub?exclusive??
6：exclusive?独占(X)：独立访问使用，exclusive?

?

2：查询登陆用户的sid

?

select sid from v$mystat where rownum=1

?

?

3：查询逻辑读最多的sql，按时间倒序排序

?

select s.SQL_FULLTEXT,
       s.SQL_ID,
       s.LAST_LOAD_TIME,
       s.BUFFER_GETS / decode(s.EXECUTIONS, 0, 1, s.EXECUTIONS) buffer_get
  from v$sql s
 order by buffer_get desc, s.LAST_LOAD_TIME desc

?

4：在sqlplus中统计执行信息

?

alter session set statistics_level=all

?

?

5:dbms_stats收集和删除统计信息

?

EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

EXEC DBMS_STATS.gather_schema_stats('SCOTT');
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15);

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15);

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);

EXEC DBMS_STATS.delete_database_stats;
EXEC DBMS_STATS.delete_schema_stats('SCOTT');
EXEC DBMS_STATS.delete_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.delete_index_stats('SCOTT', 'EMPLOYEES_PK');

?

?

6:查询绑定变量的值

?

select b.SQL_ID, b.CHILD_NUMBER, b.name, b.VALUE_STRING
  from V$SQL_BIND_CAPTURE b

?

?

7:查询share pool中sql语句占用了多少内存

?

select trunc(sum(sharable_mem)/1024/1024) ||'M' from v$sql 

?

?

8：查询sga中各组件占用内存大小

?

select name,bytes/1024/1024 from v$sgainfo

?

?

9：查询整个系统中sql解析的情况

?

select * from v$sysstat where name like '%parse%'

?

?

10：查询shared pool情况

?

SELECT   pool, NAME, TRUNC (BYTES / 1024 / 1024) bsize
    FROM v$sgastat
   WHERE pool = 'shared pool'
ORDER BY bsize DESC

?

?

11:查询shared pool实际占用内存情况

?

SELECT   sum (BYTES / 1024 / 1024)
    FROM v$sgastat
   WHERE pool = 'shared pool' and name !='free memory'

?

?

12：查询V$SQL_PLAN中的执行计划

?

SELECT ID,
       LPAD(' ', DEPTH) || OPERATION OPERATION,
       OPTIONS,
       OBJECT_NAME,
       OPTIMIZER,
       COST
  FROM V$SQL_PLAN
 WHERE SQL_ID = '7hzv19tjg807d'
 START WITH ID = 0
CONNECT BY (PRIOR ID = PARENT_ID AND PRIOR HASH_VALUE = HASH_VALUE AND
           PRIOR CHILD_NUMBER = CHILD_NUMBER)
 ORDER SIBLINGS BY ID, POSITION;

?

?

13:获取触发器脚本

?

select dbms_metadata.get_ddl('TRIGGER', 'TRG_DEL_T1') from user_triggers u;

? 或者

?

select dbms_metadata.get_ddl('TRIGGER', 'TRG_DEL_T1') from dual;

?

?

14：导出awr报告

?

@?/rdbms/admin/awrrpt

?

?

15:查询awr采样时间

?

SELECT * FROM Dba_Hist_Wr_Control;

?

?

16:修改awr采样时间，单位是分钟

?

BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL => 10);
END;

?

?

17:查询临时表空间使用情况，收缩临时表空间文件

?