惯用脚本

2012-09-10

常用脚本1：查询被锁住的对象?select b.owner, b.object_name, l.session_id, l.locked_modefrom v$locked_

常用脚本

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 = 0CONNECT 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:查询临时表空间使用情况，收缩临时表空间文件

select name,bytes/1024/1024/1024 from v$tempfile;查询临时表空间大小select max(segblk#)*8192/1024/1024/1024  from v$tempseg_usage;把临时表空间文件收缩到10Galter database tempfile 'E:\oracle\product\10.2.0\oradata\dev\TEMP01.dbf' resize 10G;查询临时表空间使用信息select * from   from v$tempseg_usage;

18:启动，禁用触发器,查询触发器状态

ALTER  TRIGGER  trg_add_gw DISABLE;ALTER  TRIGGER  trg_add_gw ENABLE;

SELECT T.OWNER,       T.TRIGGER_NAME,       T.STATUS  FROM DBA_TRIGGERS T WHERE T.OWNER = 'CARMOT_TRIGGER';

19:对用户所占用的空间进行倒序排序

SELECT OWNER,       ROUND(SUM(BYTES) / 1024 / 1024, 2) "USED / M"  FROM DBA_SEGMENTS GROUP BY OWNER ORDER BY SUM(BYTES) DESC;

20：对某个用户所拥有的段进行倒序排序

SELECT OWNER,       SEGMENT_NAME,       BYTES / 1024 / 1024  FROM DBA_SEGMENTS WHERE OWNER = 'CARMOT_GG_1' ORDER BY BYTES DESC;

21：杀掉锁住对象的session

SELECT OBJECT_NAME AS 对象名称,       S.SID,       S.SERIAL#,       P.SPID      AS 系统进程号  FROM V$LOCKED_OBJECT L,       DBA_OBJECTS     O,       V$SESSION       S,       V$PROCESS       P WHERE L.OBJECT_ID = O.OBJECT_ID AND       L.SESSION_ID = S.SID AND       S.PADDR = P.ADDR; alter system kill session 'sid,serial#';

22：使用hints

SELECT /*+ FULL(u) FULL(e) */ *  FROM TBUSER U, TBENTITY E WHERE U.CURRENTENTITYID = E.ENTITYID   AND U.USERID = 1;

23：跟踪sql用到的一些参数

alter session set timed_statistics = true;alter session set statistics_level=ALL;alter session set max_dump_file_size=UNLIMITED;ALTER SESSION SET TRACEFILE_IDENTIFIER= 'TUNE';

24：回收站

SELECT * from user_recyclebin;purge user_recyclebin;

25：

热点排行

其他数据库

惯用脚本