常用脚本
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：