1,查看数据库的基本属性:
SELECT dbid 数据库编号,name 数据库名称,db_unique_name 全局名称,created 创建时间,log_mode 归档方式,open_mode 访问方式,platform_name 版本类型 FROM v$database;
2,查看所有数据库对象的类别和大小:
SELECT count(name) num_instances,type,sum(source_size) source_size,sum(parsed_size) parsed_size,sum(code_size) code_size,
sum(error_size) error_size, sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required FROM dba_object_size GROUP BY type ORDER BY 2;
NUM_INSTANCES TYPE SOURCE_SIZE PARSED_SIZE CODE_SIZE ERROR_SIZE SIZE_REQUIRED
------------- -------------------------- ----------- ----------- ---------- ---------- -------------
264 FUNCTION 90226 163614 286766 0 540606
16417 JAVA CLASS 0 0 79741994 0 79741994
306 JAVA DATA 0 0 9061460 0 9061460
770 JAVA RESOURCE 0 0 10738795 0 10738795
843 PACKAGE 5570226 5597328 2593462 0 13761016
787 PACKAGE BODY 16979979 0 20748156 0 37728135
83 PROCEDURE 195788 80300 266716 0 542804
77 SEQUENCE 0 21269 0 0 21269
170 TRIGGER 202051 0 176799 0 378850
1859 TYPE 897097 1438090 587281 0 2922468
173 TYPE BODY 326762 13013 922875 0 1262650
11 rows selected.
3,查看当前数据库的object数目:
SELECT decode(o.type#,1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6, 'SEQUENCE','OTHER') 对象类型 , count(*) 对象数目
FROM sys.obj$ o WHERE o.type# > 1 GROUP BY decode(o.type#,1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE','OTHER')
union SELECT 'COLUMN', count(*) FROM sys.col$
union SELECT 'DB LINK' , count(*) FROM all_objects;
对象类型 对象数目
---------------- ----------
CLUSTER 10
COLUMN 55879
DB LINK 49896
OTHER 23667
SEQUENCE 141
SYNONYM 20027
TABLE 1634
VIEW 3670
8 rows selected.
4,查看当前数据库的字符集:
SELECT userenv('language') FROM dual;
或者：
SELECT userenv('lang') FROM dual;
或者：
SELECT * FROM sys.props$ WHERE name='NLS_CHARACTERSET';
5,查看数据库文件的属性;
查看控制文件的命令如下
SELECT name FROM v$controlfile;
查看数据文件的命令如下
SELECT tablespace_name,file_id,bytes/1024/1024,file_name FROM dba_data_files ORDER BY file_id;
查看日志文件的命令如下
SELECT * FROM v$logfile;
查看日志文件的组名、成员数量、状态和大小，命令如下
SELECT group#,members,bytes,status FROM v$log;
查看临时文件的命令如下
SELECT name,bytes FROM v$tempfile;
6,查看数据库归档信息
查看数据库归档参数设置：
show parameter archive
查看数据库归档位置信息:
SELECT dest_id,dest_name,status,name_space,binding, target, destination FROM v$archive_dest;
查看归档日志文件信息:
SELECT name, FIRST_CHANGE#,next_change# FROM v$archived_log;
查看归档进程信息:
SELECT * FROM v$archive_processes;
7,查看数据库的后台进程
SELECT * FROM v$bgprocess;
8,查看系统的当前时间
1）查看系统当前时间的命令如下:
SQL> SELECT to_char(sysdate, 'yyyy"年"mm"月"dd"日", hh24"时":mi"分":ss"秒"') 现在时间 FROM dual;
现在时间
------------------------------------------------------------------------
2013年01月27日, 00时:11分:02秒
2）从sysdate函数中获取部分时间内容的命令及结果如下：
取年月日: SELECT TRUNC(SYSDATE) FROM DUAL;
取年份: SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL;
取月份: SELECT TO_CHAR(SYSDATE,'MM') FROM DUAL;
取日: SELECT TO_CHAR(SYSDATE,'DD') FROM DUAL;
取时分秒: SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') FROM DUAL;
取小时: SELECT TO_CHAR(SYSDATE,'HH24') FROM DUAL;
取分钟: SELECT TO_CHAR(SYSDATE,'MI') FROM DUAL;
取秒钟: SELECT TO_CHAR(SYSDATE,'SS') FROM DUAL;
取星期: SELECT TO_CHAR(SYSDATE,'D') FROM DUAL;
取一年的第几周: SELECT TO_CHAR(SYSDATE,'WW') FROM DUAL;
取一年中的第几天: SELECT TO_CHAR(SYSDATE,'DDD') FROM DUAL;
取午夜后时间间隔的秒数差：
SELECT TO_CHAR(SYSDATE,'SSSSS') FROM DUAL;
9,修改系统的当前时间
修改系统当前时间的命令如下：
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
10,查看数据库连接的客户端信息
查看客户端的登录信息:
SELECT process,osuser,username,machine,logon_time ,SQL_text FROM v$session a,v$SQLtext b WHERE a.SQL_address=b.address;
查看客户端的主机地址信息:
SELECT SYS_CONTEXT('USERENV','TERMINAL') TERMINAL,SYS_CONTEXT('USERENV','HOST') HOST,SYS_CONTEXT('USERENV','OS_USER') OS_USER,SYS_CONTEXT
('USERENV','IP_ADDRESS') IP_ADDRESS FROM DUAL
11,查看客户端的会话信息
SELECT A.SID,B.SPID,A.SERIAL#,a.lockwait,A.USERNAME,A.OSUSER,a.logon_time,A.last_call_et/3600 LAST_HOUR,
A.STATUS, 'orakill '||sid||' '||spid HOST_COMMAND,'alter system kill session '''||A.sid||','||A.SERIAL# ||'''' SQL_COMMAND
FROM v$session A,V$PROCESS B WHERE A.PADDR=B.ADDR AND SID>6;
12,查看正在运行的占I/O较大的session
SELECT se.sid,se.serial#,pr.SPID,si.physical_reads, si.block_changes,
se.username,se.status,se.terminal,se.program,se.MODULE,se.SQL_address,st.event,st.p1text
FROM v$session se,v$session_wait st,v$sess_io si,v$process pr
WHERE st.sid=se.sid AND st.sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st.wait_time=0 AND st.event NOT LIKE '%SQL%'
ORDER BY physical_reads DESC;
13,查看使用CPU较多的用户session
SELECT a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
FROM v$session a,v$process b,v$sesstat c WHERE c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr ORDER BY value desc;
14,查看正在使用回滚段的会话
SELECT r.name ROLLBACK_SEGMENT,s.sid,s.serial#,s.username, t.status,t.cr_get,t.phy_io,t.used_ublk,t.noundo,substr(s.program, 1, 78) OPERATING_PROGRAM FROM
sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r WHERE t.addr = s.taddr and t.xidusn = r.usn ORDER BY t.cr_get,t.phy_io;
15,查看正在使用临时段的会话
SELECT se.username,sid,serial#,SQL_address,machine,program,tablespace,segtype,contents FROM v$session se, v$sort_usage su WHERE se.saddr=su.session_addr;
16,查看耗资源的会话进程（top session）
SELECT s.schemaname schema_name,decode(sign(48 - command),1,
to_char(command), 'Action Code #' || to_char(command) ) action, status session_status,s.osuser os_user_name,s.sid,p.spid,s.serial# serial_num,
nvl(s.username,'[Oracle process]') user_name,s.terminal terminal,s.program program,st.value criteria_value FROM v$sesstat st,v$session s,v$process p
WHERE st.sid = s.sid and st.statistic# = to_number('38') and ('ALL'='ALL' or s.status ='ALL') and p.addr=s.paddr
ORDER BY st.value desc,p.spid asc,s.username asc,s.osuser asc;
17,查看object为哪些进程所用
SELECT p.spid,s.sid,s.serial# serial_num, s.username user_name,a.type object_type,s.osuser os_user_name,a.owner,a.object object_name,decode(sign(48 -
command),1,to_char(command), 'Action Code #' || to_char(command) ) action, p.program Oracle_process,s.terminal terminal,s.program program,s.status
session_status FROM v$session s, v$access a, v$process p WHERE s.paddr = p.addr and s.type = 'USER' and a.sid = s.sid and a.object='SUBSCRIBER_ATTR'
ORDER BY s.username, s.osuser;
18,查看SID对应的进程
SELECT pro.spid FROM v$session ses,v$process pro WHERE ses.sid=148 and ses.paddr=pro.addr;
19,查看当前用户的默认表空间
SELECT username,default_tablespace FROM user_users;
20,查看表空间的基本属性
SELECT tablespace_name,block_size,status,extent_management, initial_extent,next_extent,min_extents,max_extents,min_extents FROM dba_tablespaces;
21:查看表空间的数据文件和容量信息
(1) 查看表空间的数据文件和容量信息
SELECT tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space FROM dba_data_files ORDER BY tablespace_name;
(2) 查看物理文件在表空间的容量使用情况
SELECT b.file_id 文件ID,b.tablespace_name 表空间名, b.file_name 物理文件名, b.bytes/(1024*1024) 总容量MB, (b.bytes-sum(nvl(a.bytes,0))) /(1024*1024)
已使用数, sum(nvl(a.bytes,0)) /(1024*1024) 剩余数,round(sum(nvl(a.bytes,0))/(b.bytes)*100,2) 剩余百分比 FROM dba_free_space a,dba_data_files b
WHERE a.file_id=b.file_id GROUP BY b.tablespace_name,b.file_name,b.file_id,b.bytes ORDER BY b.tablespace_name DESC;
22,查看表空间的容量统计信息
(1) 查看表空间的容量统计信息
SELECT upper(ts.tablespace_name) "表空间名称", ddts.Tot_grootte_Mb "表空间容量(M)",ddts.Tot_grootte_Mb - ts.total_bytes "已使用空间(M)",to_char(round
((ddts.Tot_grootte_Mb - ts.total_bytes) / ddts.Tot_grootte_Mb * 100,2),'990.99') "使用百分比",ts.total_bytes "剩余空间容量(M)",ts.max_bytes "最大块容量(M)"
FROM (SELECT tablespace_name,round(SUM(bytes)/(1024*1024),2) total_bytes,round(MAX(bytes)/(1024*1024),2) max_bytes FROM sys.dba_free_space GROUP BY
tablespace_name) ts,(SELECT dd.tablespace_name,round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb FROM sys.dba_data_files dd GROUP BY dd.tablespace_name)
ddts WHERE ddts.tablespace_name = ts.tablespace_name ORDER BY ts.tablespace_name DESC;
(2) 查看表空间的块容量信息
SELECT tablespace_name, max_blocks, count_blocks,sum_free_blocks, to_char(100*sum_free_blocks/sum_alloc_blocks, '99.99') || '%' AS pct_free FROM ( SELECT
tablespace_name , sum(blocks) AS sum_alloc_blocks FROM dba_data_files GROUP BY tablespace_name), (SELECT tablespace_name AS fs_ts_name, max(blocks) AS
max_blocks, count(blocks) AS count_blocks, sum(blocks) AS sum_free_blocks FROM dba_free_space GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name;
23,查看表空间的容量增长情况
SELECT A.tablespace_name,round((1-(A.total)/B.total)*100,2) used_percent From (SELECT tablespace_name,sum(bytes) total FROM dba_free_space GROUP BY
tablespace_name) A,(SELECT tablespace_name,sum(bytes) total FROM dba_data_files GROUP BY tablespace_name) B WHERE A.tablespace_name=B.tablespace_name;
24,查看无法扩展的段
SELECT segment_name 段名, segment_type 段类型, owner 所有者,a.tablespace_name "表空间名", initial_extent/1024 "inital_extent(K)",next_extent/1024
"next_extent(K)",pct_increase, b.bytes/1024 "表空间最大剩余容量(K)",b.sum_bytes/1024 "表空间总容量(K(K)" FROM dba_segments a,(SELECT tablespace_name,MAX
(bytes) bytes,SUM(bytes) sum_bytes FROM dba_free_space GROUP BY tablespace_name) b WHERE a.tablespace_name=b.tablespace_name AND next_extent>b.bytes ORDER BY
4,3,1;
25,查看一个表所占用的表空间信息
SELECT s.*,bytes/1024/1024 FROM user_segments s where segment_name ='EMP';
26,查看用户表所占用的空间容量
SELECT owner,table_name,NUM_ROWS, BLOCKS*8192/1024/1024 "Size M",EMPTY_BLOCKS, LAST_ANALYZED FROM dba_tables WHERE owner='HR' ;
其中8192为db_block_size的容量，用“SHOW PARAMETER db_block_size;”命令查找。
27,查看表段或索引段所使用的空间容量
SELECT owner,segment_name,SUM(bytes)/1024/1024 FROM dba_segments WHERE owner='SYS' And segment_name='UNDO$' GROUP BY owner,segment_name ORDER BY 3 DESC;
28,查看表空间的碎片
Select tablespace_name 表空间名,TO_CHAR(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))),'999.99') 自有碎片索引 From dba_free_space Group by
tablespace_name order by 1;
当一个表空间的自由碎片索引值超过30时就需要做碎片整理了。可采用自动整理方法进行整理，命令为：Alter tablespace tablespace_name coalesce;
好象Alter tablespace tablespace_name coalesce; 不起作用，有待进一步研究
29,查看数据文件状态
SELECT d.name,d.file#,d.status, h.status FROM v$datafile d, v$datafile_header h WHERE d.file#=h.file#;
30,查看表空间数据文件的读写性能
查看表空间数据文件的读写性能有以下两种方法。
方法1：命令如下
SELECT name,phyrds,phywrts,avgiotim,miniotim,maxiowtm,maxiortm FROM v$filestat,v$datafile WHERE v$filestat.file#=v$datafile.file#;
方法2：命令如下
SELECT fs.name name,f.phyrds,f.phyblkrd,f.phywrts,f.phyblkwrt ,f.readtim,f.writetim FROM v$filestat f, v$datafile fs WHERE f.file# = fs.file# ORDER BY
fs.name;
注意:如果phyblkrd与phyrds的值很接近的话，则表明这个表空间中存在全表扫描的表，因此，需要调整这些表的索引或优化SQL语句。
31,查看数据文件的HWM和文件头大小
SELECT v1.file_name,v1.file_id,num1 totle_space,num3 free_space,num1-num3 "USED_SPACE(HWM)",nvl(num2,0) data_space,num1-num3-nvl(num2,0) file_head FROM
(SELECT file_name,file_id,SUM(bytes) num1 FROM Dba_Data_Files GROUP BY file_name,file_id) v1,(SELECT file_id,SUM(bytes) num2 FROM dba_extents GROUP BY
file_id) v2,(SELECT file_id,SUM(BYTES) num3 FROM DBA_FREE_SPACE GROUP BY file_id) v3 WHERE v1.file_id=v2.file_id(+) AND v1.file_id=v3.file_id(+);
其中各参数容量的单位为字节，其含义说明如下：
Totle_pace：数据文件的总大小
Free_space：数据文件的剩余大小
Used_space：数据文件的已用空间
Data_space：数据文件中段数据占用空间，即数据空间
File_Head：数据文件头部占用空间
32,查看系统的回滚段及其状态
SELECT rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents Extents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs, v$rollstat.gets
Gets, v$rollstat.waits Waits, v$rollstat.writes Writes, sys.dba_rollback_segs.status status FROM v$rollstat, sys.dba_rollback_segs, v$rollname WHERE
v$rollname.name(+) = sys.dba_rollback_segs.segment_name and v$rollstat.usn (+) = v$rollname.usn ORDER BY rownum;
33,查看现有回滚段及其状态
SELECT OWNER,SEGMENT_NAME,TABLESPACE_NAME,SUM(BYTES)/1024/1024 M FROM DBA_SEGMENTS WHERE SEGMENT_TYPE='ROLLBACK'
GROUP BY OWNER,SEGMENT_NAME,TABLESPACE_NAME;
34,查看回滚段的名称及容量
SELECT segment_name, tablespace_name, r.status, (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,max_extents, v.curext CurExtent From
dba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn(+) ORDER BY segment_name;
35,结合数据文件查看所有回滚段的状态
SELECT segment_name,owner,tablespace_name,initial_extent,next_extent,dba_rollback_segs.status FROM dba_rollback_segs,v$datafile WHERE file_id=file#;
36,查看回滚段的扩展信息
SELECT substr(name,1,40) name,extents,rssize,optsize,aveactive,extends,wraps,shrinks,hwmsize FROM v$rollname rn,v$rollstat rs WHERE (rn.usn=rs.usn);
其中，各参数的含义如下：
参 数 含 义
EXTENTS 回滚段中的盘区数量。
RSSIZE 以字节为单位的回滚段的尺寸。
OPTSIZE optimal参数设定的值。
AVEACTIVE 从回滚段中删除盘区时释放的平均空间的大小（bytes）。
EXTENDS 系统为回滚段增加盘区的次数。
SHRINKS 系统从回滚段中清除盘区（即回滚段收缩）的次数。回滚段每次清除盘区时，系统可能会从这个回滚段中消除一个或多个盘区。
37,查看用户正在使用的回滚段
SELECT s.username, u.name FROM v$transaction t,v$rollstat r,v$rollname u,v$session s WHERE s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn ORDER BY
s.username;
38,查看会话正在使用的回滚段
SELECT r.name 回滚段名,s.sid,s.serial#,s.username 用户名,s.status,t.cr_get,t.phy_io,t.used_ublk,t.noundo,substr(s.program, 1, 78) 操作程序 FROM
sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r WHERE t.addr = s.taddr and t.xidusn = r.usn ORDER BY t.cr_get,t.phy_io;
39,查看数据表的基本属性
功 能 命 令
分析表的性能 analyze table scott.emp compute statistics;
查看表的创建时间 SELECT object_name,created FROM user_objects WHERE object_name='EMP';
查看表的大小 SELECT sum(bytes)/(1024*1024) as "size(M)" FROM user_segments WHERE segment_name='EMP';
查看表的存储参数 SELECT num_rows,blocks,empty_blocks as empty,avg_space,chain_cnt,avg_row_len FROM dba_tables WHERE owner='SCOTT' and table_name='EMP';
40,查看表的存储参数
SELECT partition_name, high_value, high_value_length,tablespace_name,pct_free,pct_used, ini_trans, max_trans, initial_extent,next_extent, min_extent,
max_extent,pct_increase, FREELISTS,freelist_groups, LOGGING, BUFFER_POOL, num_rows,blocks,empty_blocks, avg_space, chain_cnt, avg_row_len,
sample_size,last_analyzed FROM dba_tab_partitions WHERE tablespace_name = 'USERS' ORDER BY partition_position;
45,查看表在表空间中的存储情况
SELECT segment_name,sum(bytes),count(*) ext_quan FROM dba_extents WHERE tablespace_name='&tablespace_name' and segment_type='TABLE' GROUP BY
tablespace_name,segment_name;
46,查看数据表对应的区块数
SELECT TABLE_NAME,BLOCKS,TABLESPACE_NAME,PCT_FREE,PCT_USED,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,
NUM_ROWS,EMPTY_BLOCKS,AVG_ROW_LEN , CACHE,TABLE_LOCK, SAMPLE_SIZE,LAST_ANALYZED,BUFFER_POOL FROM dba_tables WHERE table_name='EMP';
47,查看碎片程度较高的表
SELECT segment_name table_name , COUNT(*) extents FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name HAVING COUNT(*) = (SELECT MAX(
COUNT(*) ) FROM dba_segments GROUP BY segment_name);
48,查看有primary key和forgen key关系的表
SELECT a.owner 外键OWNER, a.table_name 外键表,c.column_name 外键列,b.owner 主键OWNER,b.table_name 主键表,d.column_name 主键列 FROM user_constraints
a,user_constraints b,user_cons_columns c,user_cons_columns d WHERE a.r_constraint_name=b.constraint_name and a.constraint_type='R' and b.constraint_type='P'
and a.r_owner=b.owner and a.constraint_name=c.constraint_name and b.constraint_name=d.constraint_name and a.owner=c.owner and a.table_name=c.table_name and
b.owner=d.owner and b.table_name=d.table_name;
49,查看用户没有主键的表
SELECT table_name FROM all_tables WHERE owner = '&OWNER'
MINUS
SELECT table_name FROM all_constraints WHERE owner = '&&OWNER' AND constraint_type = 'P'
50,查看失效的主键
SELECT owner,constraint_name,table_name,status FROM all_constraints WHERE owner = '&OWNER' AND status = 'DISABLED' AND constraint_type = 'P';
51,查看表的约束属性
SELECT a.constraint_name, a.constraint_type,a.* FROM user_constraints a WHERE table_name='EMP';
52,查看某表的约束条件
SELECT constraint_name, constraint_type,search_condition, r_constraint_name FROM user_constraints WHERE table_name = 'EMP';
53,查看记录序号rownum在6～10之间的数据
SELECT rownum,employee_id,first_name,department_id FROM emp WHERE rownum<=10
minus
SELECT rownum,employee_id,first_name,department_id FROM emp WHERE rownum<=5;
54,查找重复记录
SELECT * FROM emp WHERE ROWID!=(SELECT MAX(ROWID) FROM emp e WHERE emp.employee_id=e.employee_id AND emp.first_name=e.first_name);
55,删除重复记录
delete FROM emp WHERE ROWID!=(SELECT MAX(ROWID) FROM emp e WHERE emp.employee_id=e.employee_id AND emp.first_name=e.first_name);
56,查找当前用户下所有的索引
SELECT user_indexes.table_name, user_indexes.index_name,uniqueness, column_name FROM user_ind_columns, user_indexes
WHERE user_ind_columns.index_name = user_indexes.index_name and user_ind_columns.table_name = user_indexes.table_name ORDER BY user_indexes.table_type,
user_indexes.table_name, user_indexes.index_name, column_position;
57,查看索引段中区（extent）的数量
SELECT segment_name,count(*) FROM dba_extents WHERE segment_type='INDEX' and owner='HR' GROUP BY segment_name;
58,对CLOB字段进行全文检索
对CLOB字段进行全文检索可以用SELECT语句，但如果使用WHERE子句时必须使用dbms_lob.instr（）函数，命令如下：
首先创建一个测试表TEST：
SQL>CREATE TABLE TEST3(A CLOB,B INT,C INT);
然后往表里插入数据：
SQL> INSERT INTO TEST3 VALUES('TEST 测试 CLOB',11111,22222);
1 row created.
SQL> commit;
Commit complete.
对表进行检索:
SQL> SELECT * FROM TEST3 T WHERE dbms_lob.instr(T.A,'TEST',1,1)>0;
A B C
-------------------------------------------------------------------------------- ---------- ----------
TEST 测试 CLOB 11111 22222
59,查看存储函数的状态
SELECT object_name,status FROM user_objects WHERE object_type='FUNCTION';
60,查看存储过程的状态
SELECT object_name,status FROM user_objects WHERE object_type='PROCEDURE';
61,查看触发器、过程、函数的创建脚本
(1) 查看触发器、过程、函数的创建脚本，命令如下：
SELECT * FROM user_source;
(2) 查看单个函数或过程的源代码，命令如下，结果如图29-77所示。
SQL> SELECT text FROM all_source WHERE owner='HR' and name='UPDATE_SAL';
TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------
procedure update_sal(name varchar2,newsal number)
IS
BEGIN
update emp set salary=newsal where lower(first_name)=lower(name);
commit;
end;
6 rows selected.
62,查看PID对应的SQL语句
SELECT a.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.SQL_text FROM v$session a,v$process b,v$SQLtext c WHERE b.spid=spid AND
b.addr=a.paddr AND a.SQL_address=c.address(+) ORDER BY c.piece;
63,根据PID找出消耗CPU最高的进程所对应的SQL语句
SELECT P.pid pid,S.sid sid,P.spid spid,S.username username,S.osuser osname,P.serial# S_#,P.terminal,P.program program,P.background,S.status,RTRIM(SUBSTR
(a.SQL_text, 1, 80)) SQLTXT FROM v$process P, v$session S,v$SQLArea A WHERE P.addr = s.paddr AND S.SQL_address = a.address (+) AND P.spid LIKE '%&pid%';
64,根据进程的PID查看SPID、PROGRAM和SQL语句
SELECT v$process.username,spid,v$session.program,SQL_text,v$session.process FROM v$SQLArea,v$session,v$process WHERE v$SQLArea.address=v$session.SQL_address
and v$SQLArea.hash_value=v$session.SQL_hash_value and v$session.paddr=v$process.addr and v$process.spid in (pid);
65,根据SID查看正在运行的SQL语句
SELECT command_type,SQL_text,sharable_mem,persistent_mem,runtime_mem,sorts,version_count,loaded_versions,open_versions,users_opening,executions,
users_executing,loads,first_load_time,invalidations, parse_calls,disk_reads,buffer_gets,rows_processed,sysdate start_time,sysdate finish_time,'>'|| address
SQL_address,'N' status FROM v$SQLArea WHERE address = (SELECT SQL_address FROM v$session WHERE sid=131);
或者：
SELECT SQL_text FROM v$SQLtext_with_newlines WHERE hash_value=(SELECT SQL_HASH_VALUE FROM v$session WHERE sid=&sid) ORDER BY piece;
66,根据进程的地址查看SQL文本
SELECT address, SQL_text FROM v$SQLArea WHERE address='00';
67,查看用户正在运行的SQL语句
method 1:
SELECT user_name,SQL_text FROM v$open_cursor WHERE sid in(SELECT sid FROM (SELECT sid,serial# FROM v$session WHERE status= 'ACTIVE'));
method 2:
SELECT S.MACHINE,TERMINAL, OSUSER,USERNAME,SQL_TEXT FROM V$SQLTEXT T, V$SESSION S WHERE T.ADDRESS=S.SQL_ADDRESS AND T.HASH_VALUE=S.SQL_HASH_VALUE;
68,查看Disk Read最高的SQL语句
SELECT SQL_text FROM (SELECT * FROM v$SQLArea ORDER BY disk_reads) WHERE rownum<=5;
69,查看回滚段里正在运行的SQL语句
SELECT d.SQL_text,a.name FROM v$rollname a,v$transaction b,v$session c,v$SQLtext d WHERE a.usn=b.xidusn and b.addr=c.taddr and c.SQL_address=d.address and
c.SQL_hash_value=d.hash_value and a.usn=1;--可修改usn
70,查看视图对应的SQL语句
SQL> SELECT view_name,text_length,text FROM user_views WHERE view_name='VIEW_EMP';
VIEW_NAME TEXT_LENGTH TEXT
-------------------- ----------- --------------------------------------------------------------------------------
VIEW_EMP 34 select employee_id,job_id from emp
71,查看SQL语句的执行情况
SELECT sid, serial#, opname,to_char(start_time, 'HH24:MI:SS') as start_t,(sofar/totalwork)*100 as percent_complete FROM v$session_longops;
72,查看数据库角色
(1) 查看系统所有角色的命令如下:
SELECT * FROM Dba_Roles;
(2) 查看当前用户的角色，命令如下:
SELECT * FROM user_role_privs;
73,查看连接数据库的用户信息
SELECT s.osuser os_user_name,decode(sign(48 - command),1,to_char(command),'Action Code #' || to_char(command)) action,p.program Oracle_process, status
session_status,s.terminal terminal,s.program program,s.username user_name,''query,0 memory,0 cpu_usage,s.sid, s.serial# serial_num
FROM v$session s,v$process p WHERE s.paddr=p.addr and s.type = 'USER' ORDER BY s.username, s.osuser;
--注意''符号和0
74,查看正在使用数据库的用户信息
SELECT username,schemaname,osuser,machine,terminal,logon_time FROM v$session WHERE username is not null;
75,查看当前用户的SID和SERIA#
SQL> SELECT sid, serial#, status, username FROM v$session WHERE audsid=userenv('sessionid');
SID SERIAL# STATUS USERNAME
---------- ---------- ---------------- ------------------------------------------------------------
159 5 ACTIVE HR
76,查看进程号所属的用户名
SELECT p.spid, s.username FROM v$process p , v$session s WHERE p.addr=s.paddr;
77,查看当前用户下所有的约束|对象|视图|触发器|索引|快照|表
user_constraints
user_objects
user_views
user_triggers
user_indexes
user_snapshots
user_tables
78,查看数据库对象结构被修改过的情况
SELECT OBJECT_NAME 对象名,OBJECT_TYPE 对象类型,TO_CHAR(CREATED, 'YYYY-Mon-DD HH24:MI') 创建时间,TO_CHAR(LAST_DDL_TIME, 'YYYY-Mon-DD HH24:MI') 修改时
间,TIMESTAMP 时间戳,STATUS 状态 FROM USER_OBJECTS WHERE to_char(LAST_DDL_TIME,'yyyymmdd')>'20080101';
79,查看用户的系统权限
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='HR';
80,查看用户角色所拥有的系统权限
SELECT * FROM dba_role_privs WHERE grantee='HR';
81,查看当前用户读取其他用户对象的权限
SELECT * FROM user_tab_privs;
82,查看当前用户所拥有的系统权限
SELECT * FROM user_sys_privs;
83,查看当前用户的系统权限和表级权限
查看当前用户系统权限的命令如下:
SELECT * FROM user_sys_privs;
查看当前用户表级权限的命令如下:
SELECT * FROM user_tab_privs;
84,查看当前会话所具有的权限
SELECT * FROM session_privs;
85,查看用户表空间的授权信息
查看用户的表空间及授权情况的命令如下:
SELECT A.USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,B.PRIVILEGE,C.GRANTED_ROLE,D.TABLE_NAME FROM DBA_USERS A,DBA_SYS_PRIVS B,DBA_ROLE_PRIVS
C,DBA_TABLES D WHERE A.USERNAME=B.GRANTEE(+) AND A.USERNAME=C.GRANTEE(+) AND A.USERNAME=D.OWNER(+) AND A.USERNAME='HR';
86,查看正在备份的数据文件
SELECT * FROM v$backup;
87,查看需要恢复的文件
SELECT * FROM v$recover_file;
注意：没有表示不需要恢复。
88,查看恢复时要用到的日志文件
SELECT * FROM v$recovery_log;
89,查看所有的锁及类型
SQL> SELECT * FROM v$lock;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
30FB9A40 30FB9A54 165 XR 4 0 1 0 7355 0
30FB9A9C 30FB9AB0 165 CF 0 0 2 0 7355 0
30FB9B54 30FB9B68 165 RS 25 1 2 0 7351 0
30FB9C0C 30FB9C20 166 RT 1 0 6 0 7351 0
30FB9C68 30FB9C7C 167 PW 1 0 3 0 7337 0
30FB9CC4 30FB9CD8 164 TS 3 1 3 0 7338 0
30FB9D7C 30FB9D90 167 MR 1 0 4 0 7342 0
30FB9DD8 30FB9DEC 167 MR 2 0 4 0 7342 0
30FB9E34 30FB9E48 167 MR 3 0 4 0 7342 0
30FB9E90 30FB9EA4 167 MR 4 0 4 0 7342 0
30FB9EEC 30FB9F00 167 MR 5 0 4 0 7342 0
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
30FB9F48 30FB9F5C 167 MR 6 0 4 0 7342 0
30FB9FA4 30FB9FB8 167 MR 201 0 4 0 7342 0
13 rows selected.
其中，如果BLOCK=1，则表示是造成锁的会话（session），此时可以杀掉对应的SID会话进程。
90,查看锁的状况
SELECT S.SID SESSION_ID, S.SERIAL#, S.USERNAME,DECODE(LMODE, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6,
'Exclusive', TO_CHAR(LMODE)) MODE_HELD, DECODE(REQUEST, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6,
'Exclusive', TO_CHAR(REQUEST)) MODE_REQUESTED,O.OWNER||'.'||O.OBJECT_NAME ||' ('||O.OBJECT_TYPE||')' Objectname, S.TYPE LOCK_TYPE, L.ID1 LOCK_ID1, L.ID2
LOCK_ID2 FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID;
91,查看锁的资源信息
SELECT sys.v_$session.machine,sys.v_$session.osuser,v$lock.sid, sys.v_$session.serial#,decode(v$lock.type,'MR','Media Recovery','RT','Redo
Thread','UN','User Name','TX', 'Transaction','TM','DML','UL','PL/SQL User Lock','DX','Distributed Xaction','CF','Control File','IS','Instance
State','FS','File Set','IR','Instance Recovery', 'ST','Disk Space Transaction','TS','Temp Segment','IV','Library Cache Invalida-tion','LS','Log Start or
Switch','RW','Row Wait','SQ','Sequence Number','TE','Extend Table','TT','Temp Table','Unknown') LockType,rtrim(object_type) || ' ' || rtrim(owner) || '.'||
object_name object_name,decode(lmode, 0, 'None',1, 'Null',2,'Row-S',3, 'Row-X',4, 'Share',5, 'S/Row-X',6,'Exclusive','Unknown') LockMode,decode(request, 0,
'None',1, 'Null',2, 'Row-S',3, 'Row-X',4, 'Share',5, 'S/Row-X',6, 'Exclusive', 'Unknown') RequestMode,ctime, block FROM v$lock, all_objects, sys.v_$session
WHERE v$Lock.sid > 6 and sys.v_$session.sid = v$lock.sid and v$lock.id1 = all_objects.object_id;
92,查看系统被锁的事务时间
SELECT * FROM v$locked_object ;
93,查看每个实例（INSTANCE）分配的PCM锁的数目
Oracle使用PCM（Parallel Cache Management）锁维护缓冲区的一致性，同时通过IDLM（集成的分布式锁管理器）实现PCM锁，并通过专门的LCK进程实现INSTANCE实例间的数据一
致。命令如下：
SELECT count(*) "PCM锁的数量" FROM v$lock_element WHERE bitand(flags,4)<>0;
94,查看造成等待的锁的信息
SELECT b.sid,b.serial#,b.username,machine,a.event,b.wait_time,chr(bitand(b.p1,-16777216)/16777215)||chr(bitand(b.p1, 16711680)/65535) "Enqueue Type" FROM
v$session_wait a,v$session b WHERE a.event not like 'SQL*N%' and a.event not like 'rdbms%' and a.sid=b.sid and b.sid>8 and a.event='enqueue' ORDER BY
b.username;
95,查看锁定的进程及锁类型信息
select v$lock.sid,decode(v$lock.type,'MR', 'Media Recovery','RT','Redo Thread','UN','User Name','TX', 'Transaction','TM', 'DML','UL', 'PL/SQL User
Lock','DX', 'Distributed Xaction','CF', 'Control File','IS', 'Instance State','FS', 'File Set','IR', 'Instance Recovery','ST', 'Disk Space Transaction','TS',
'Temp Segment','IV', 'Library Cache Invalida-tion','LS', 'Log Start or Switch','RW', 'Row Wait','SQ', 'Sequence Number','TE', 'Extend Table','TT', 'Temp
Table','Other') LockType,rtrim(object_type) || ' ' || rtrim(owner) || '.' || object_name object_name,decode(lmode, 0, 'None',1, 'Null',2, 'Row-S',3, 'Row-
X',4, 'Share',5, 'S/Row-X',6, 'Exclusive', 'Other') LockMode,decode(request, 0, 'None',1, 'Null',2, 'Row-S',3, 'Row-X',4, 'Share',5, 'S/Row-X',6,
'Exclusive', 'Other') RequestMode,ctime, block from v$lock, all_objects where v$lock.id1 = all_objects.object_id;
96, 查看被锁定的各类数据库对象
SELECT OS_USER_NAME, Oracle_USERNAME AS orauser,s.sid,o.object_name,o.object_type, s.serial#, a.SQL_text FROM v$locked_object l, dba_objects o, v$session s,
v$SQLArea a WHERE l.object_id = o.object_id AND s.SQL_ADDRESS = a.address AND l.SESSION_ID = s.sid;
97,查看被锁定的数据表的方法
SELECT S.SID SESSION_ID, S.USERNAME,DECODE(LMODE, 0, 'None', 1, 'Null', 2, 'Row-S (SS)',3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6,'Exclusive',
TO_CHAR(LMODE)) MODE_HELD, DECODE(REQUEST, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)',6, 'Exclusive', TO_CHAR
(REQUEST)) MODE_REQUESTED, O.OWNER||'.'||O.OBJECT_NAME||' ('||O.OBJECT_TYPE||')', S.TYPE LOCK_TYPE, L.ID1 LOCK_ID1,L.ID2 LOCK_ID2 FROM V$LOCK L,
SYS.DBA_OBJECTS O, V$SESSION S WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID ;
98,查看锁定表的会话
SELECT A.OWNER,A.OBJECT_NAME,B.XIDUSN,B.XIDSLOT,B.XIDSQN,B.SESSION_ID,B.Oracle_USERNAME,B.OS_USER_NAME,
B.PROCESS,B.LOCKED_MODE,C.MACHINE,C.STATUS,C.SERVER,C.SID,C.SERIAL#,C.PROGRAM FROM ALL_OBJECTS A,V$LOCKED_OBJECT B,
SYS.GV_$SESSION C WHERE (A.OBJECT_ID = B.OBJECT_ID ) AND (B.PROCESS = C.PROCESS ) ORDER BY 1,2 ;
99,查看锁定对象的客户端信息
SELECT s.machine, s.osuser "OS-User", s.username "Ora-User", s.sid "Session-ID",s.serial# "Serial", s.process "Process-ID", s.status "Status",l.name "Obj
Locked",l.mode_held "Lock Mode" FROM v$session s,dba_dml_locks l,v$process p WHERE l.session_id = s.sid and p.addr = s.paddr;
100,杀掉被锁的会话进程
ALTER SYSTEM KILL SESSION 'SESSION_ID,SERIAL#';
如：ALTER SYSTEM KILL SESSION '169,1';
101,查看数据库服务器参数
SELECT * FROM nls_database_parameters;
102,查看客户端参数
SELECT * FROM nls_instance_parameters;
103,查看会话环境参数
SELECT * FROM nls_session_parameters;
104,检查共享内存的剩余情况
SELECT request_misses, request_failures FROM v$shared_pool_reserved;
注意：对于共享内存的剩余情况，request_misses和request_failures应该接近0。
105,查看数据库共享池性能
SQL> SELECT namespace,gets,gethitratio,pins,pinhitratio,reloads,Invalidations FROM v$librarycache WHERE namespace in
('SQLAREA','TABLE/PROCEDURE','BODY','TRIGGER');
NAMESPACE GETS GETHITRATIO PINS PINHITRATIO RELOADS INVALIDATIONS
------------------------------ ---------- ----------- ---------- ----------- ---------- -------------
TABLE/PROCEDURE 14845 .821421354 107356 .945107865 1297 0
BODY 1469 .972089857 1851 .943814155 63 0
TRIGGER 58 .568965517 467 .914346895 15 0
106,查看共享池的命中与重载信息
Select sum(pins) 命中次数,sum(reloads) 重载次数 from v$librarycache;
注意：如果sum(pins)/sum(reloads)的值接近于0，说明命中率比较合适，若大于1，则要增加SHARED_ POOL_SIZE的大小（在初始化参数文件中）。
107，查看共享池中的SQLArea
SELECT SQL_TEXT,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM,SORTS,VERSION_COUNT,LOADED_VERSIONS,OPEN_VERSIONS,USERS_OPENING,EXECUTIONS,
USERS_EXECUTING,LOADS,FIRST_LOAD_TIME,INVALIDATIONS,PARSE_CALLS,DISK_READS,BUFFER_GETS,ROWS_PROCESSED FROM V$SQLAREA;
108，查看SQLArea中的大匿名块
SELECT SQL_text FROM v$SQLArea WHERE command_type=47 and length(SQL_text)>500;
109，查看库缓冲区的主要参数
SELECT namespace,gets,gethits,gethitratio, pins,pinhits,pinhitratio,reloads,invalidations FROM v$librarycache;
v$librarycache中主要参数的含义说明如下：
参 数 说 明
GETS 当提交一条SQL语句时，在共享池中请求lock过程进行语句分析的次数。
GETHITS 命中次数。
GETHITRATIO 库缓冲区的命中率。
PINS 在共享池中执行一次一条语句的次数。
PINGETS 有相同的语句在库缓冲区里被调用的次数。
RELOADS 没有相同的语句在库缓冲区里被调用的次数而需要重新装载的次数。
INVALIDATIONS 没有相同的语句在库缓冲区里被调用而失效的次数。
110，查看库缓冲区的命中率
SELECT sum(pins) "Total Pins", sum(reloads) "Total Reloads", sum(reloads)/sum(pins) *100 libcache FROM v$librarycache;
111，查看库缓冲区的重载比率
SELECT sum(pins) "Executions",sum(reloads) "cache Misses", sum(reloads)/sum(pins) FROM v$librarycache;
112，查看字典缓冲区（dict. Cache）的命中和重载情况
查看LIBRAR YCACHE的重载情况：
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
查看SQL语句的执行情况：
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
查看SQL语句的命中和重载统计信息：
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) " CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;
查看SQL语句的命中和失效信息：
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) " DICTIONARY CACHE GET MISSES" FROM V$ROWCACHE
113，查看SGA中字典缓冲区的命中率
SELECT round(1-sum(getmisses)/sum(gets),2) "data dictionary hitratio" FROM v$rowcache;
几个参考数据如下所示：
— library cache、gethitratio应大于90%
— pinhitratio应大于90%
— 数据重载比率（Reload Ratio）应小于1%
— 数据字典的命中率（Data Dictionary Hit Ratio）应大于85%
114， 查看Buffer Cache的命中率
SELECT name, 1-(physical_reads/(db_block_gets+consistent_gets)) "HIT_RATIO" FROM sys.v$buffer_pool_statistics WHERE db_block_gets+consistent_gets>0;
115，调整Buffer Cache容量值
SQL> SELECT size_for_estimate, buffers_for_estimate,estd_physical_read_factor, estd_physical_reads FROM V$DB_CACHE_ADVICE WHERE name = 'DEFAULT' AND
block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size') AND advice_status = 'ON';
SIZE_FOR_ESTIMATE BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
----------------- -------------------- ------------------------- -------------------
16 1996 1.9627 27370
32 3992 1.3595 18958
48 5988 1.1047 15406
64 7984 1.0947 15265
80 9980 1.0504 14647
96 11976 1.004 14001
112 13972 1 13945
128 15968 1 13945
144 17964 1 13945
160 19960 1 13945
176 21956 1 13945
SIZE_FOR_ESTIMATE BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
----------------- -------------------- ------------------------- -------------------
188 23453 1 13945
192 23952 1 13945
208 25948 1 13945
224 27944 1 13945
240 29940 1 13945
256 31936 1 13945
272 33932 1 13945
288 35928 1 13945
304 37924 1 13945
320 39920 .9768 13622
21 rows selected.
可根据以上数据调整db_cache_size大小。
116，检查Buffer Pool的hit_ratio失效率
SELECT name, round((physical_reads/(db_block_gets+consistent_gets)),2) "MISS_HIT_RATIO" FROM v$buffer_pool_statistics
WHERE (db_block_gets+ consistent_gets)> 0;
注意:正常时db buffer cache hit ratio应该大于90%，buffer pool MISS_HIT_RATIO应该小于10%。
117,查看数据缓冲区参数的值
SELECT name,type,value,display_value, description FROM v$parameter WHERE name in('db_cache_size','db_block_size','shared_pool_size','sort_area_size');
118,查看数据高速缓冲区的性能
SELECT round(1-p.value/(b.value+c.value),2) "db buffer cache hitratio" FROM v$sysstat p,v$sysstat b,v$sysstat c WHERE p.name='physical reads' and b.name='db
block gets' and c.name='consistent gets';
119,查看单个用户下空闲块数少于10%的段（segment）
SELECT owner, table_name, blocks, empty_blocks FROM dba_tables WHERE empty_blocks/(blocks+empty_blocks)<0.1 and owner='HR' and blocks+empty_blocks!=0;
120,查看log buffer的大小
SELECT rbar.name,rbar.value,re.name,re.value,(rbar.value*100)/re.value||'%' "radio" FROM v$sysstat rbar,v$sysstat re WHERE rbar.name='redo buffer allocation
retries' and re.name='redo entries';
注意:当radio值大于1%时，一般需要增加log buffer的大小。
121,查看SGA中重做日志缓存区的命中率
SELECT name, gets, misses, immediate_gets, immediate_misses, Decode(gets,0,0,misses/gets*100) ratio1, Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2 FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
122,查看是否存在log buffer等待
SELECT * FROM v$session_wait WHERE event='log buffer wait';
如果出现等待，则可以增加log buffer的大小，也可以通过将log 文件移到访问速度更快的磁盘来解决。
123,查看log buffer值
SELECT name,value FROM v$sysstat WHERE name in ('redo buffer allocation retries','redo entries');
注意:一般情况下，Redo Buffer Allocation Retries接近0，小于Redo Entries的1％，如果一直在增长，表明进程已经不得不等待Redo Buffer的空间。如果Redo Buffer
Allocation Retries过大，则增加log buffer的值。
124,查看日志文件上是否存在磁盘I/O竞争现象
查看日志文件上是否存在磁盘I/O竞争现象的命令如下，结果如图29-133所示。如果存在竞争，可以考虑将log文件转移到独立的、更快的存储设备上或增大log文件。
SELECT event,total_waits,time_waited,average_wait FROM v$system_event WHERE event like 'log file switch completion%';
125,检查点的设置是否合理
检查alert.log文件中是否存在'checkpoint not complete'：
SELECT event,total_waits,time_waited,average_wait FROM v$system_event WHERE event like 'log file switch (check%';
如果存在等待，则调整log_checkpoint_interval、log_checkpoint_timeout的设置。
126,检查Log Archiver是否存在等待
SELECT event,total_waits,time_waited,average_wait FROM v$system_event WHERE event like 'log file switch (arch%';
如果存在等待，则检查保存归档日志的存储设备是否已满，增加日志文件组，调整log_archiver_max_processes
127,检查DBWn是否存在等待，如果存在则需要调整
SELECT total_waits FROM v$system_event WHERE event='free buffer waits';
128,查看PGA参数
SELECT * FROM v$pgastat;
129,查看用户的PGA分配
SELECT s.username username,s.serial# serial,se.sid,n.name,max(se.value) maxmem FROM v$sesstat se, v$statname n,v$session s WHERE n.statistic# = se.statistic#
and n.name in ('session pga memory','session pga memory max','session uga memory','session uga memory max') and s.sid=se.sid and upper(s.username)=upper
('&user_name') GROUP BY n.name,se.sid,s.username,s.serial# ORDER BY serial;
130,查看排序区参数值
SELECT name,type,value,display_value FROM v$parameter WHERE name like 'sort%';
131,查看Large Pool参数值
SELECT * FROM v$sgastat WHERE pool='large pool';
132,查看Java语言区参数值
SELECT name,type,value,display_value FROM v$parameter WHERE name like 'java%';
133,查看I/O竞争和负载平衡
SELECT df.name 数据文件名,fs.phyrds 读次数,fs.phywrts 写次数,round((fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds)),2) 读时间秒数,round((fs.writetim/decode
(fs.phywrts,0,-1,fs.phywrts)),2) 写时间秒数 FROM v$datafile df,v$filestat fs WHERE df.file#=fs.file# ORDER BY df.name;
134,查看会话的I/O
SELECT io.block_gets, io.consistent_gets, io.physical_reads FROM v$sess_io io, v$session s WHERE s.audsid=USERENV('SESSIONID') and io.sid=s.sid;
135,查看数据文件的I/O
SELECT phyrds, phywrts, d.name FROM v$datafile d, v$filestat f WHERE d.file#=f.file# ORDER BY d.name;
136,查看表空间的I/O比例
SELECT B.tablespace_name name,B.file_name "file",A.phyrds pyr, A.phyblkrd pbr,A.phywrts pyw, A.phyblkwrt pbw FROM v$filestat A, dba_data_files B WHERE
A.file# = B.file_id ORDER BY B.tablespace_name;
137,查看文件系统的I/O比例
SELECT substr(C.file#,1,2) "#", substr(C.name,1,30) "Name", C.status, C.bytes, D.phyrds, D.phywrts FROM v$datafile C, v$filestat D WHERE C.file# = D.file#;
138,查看等待情况
SELECT v$waitstat.class, v$sysstat.name, v$waitstat.count,SUM(v$sysstat.value) as sum_value FROM v$waitstat, v$sysstat WHERE v$sysstat.name IN ('db block
gets','consistent gets') GROUP BY v$waitstat.class, v$waitstat.count,v$sysstat.name;
139,查看等待时间最多的5个系统等待事件
SELECT * FROM (SELECT * FROM v$system_event WHERE event not like 'SQL%' ORDER BY total_waits desc) WHERE rownum<=5;
140,查看回滚段的争用情况
SELECT name, waits, gets, waits/gets "Ratio" FROM v$rollstat A, v$rollname B WHERE A.usn = B.usn;
141,查看disk_reads性能较差的SQL语句
SELECT * FROM (SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,SQL_text FROM v$SQLArea ORDER BY disk_reads DESC) WHERE ROWNUM<=5 ;
142,查看当前正在使用的SQL优化方式
检查PLAN_TABLE中ID=0的POSITION列的值，命令如下：
SELECT decode(nvl(position,-1),-1,'RBO',1,'CBO') FROM plan_table WHERE id=0;
143,查看SQL语句的语法分析和编译的时间
查看语法分析和编译时间
SELECT * FROM v$sysstat WHERE name like 'parse%';
查看SQL语句执行的等待时间
SELECT total_waits,total_timeouts,time_waited,average_wait,event FROM v$system_event WHERE event='latch free';
平均等待时间= total_waits/parse count(hard)
响应时间=服务时间（parse time cpu）+等待时间
144, 查看频繁执行的SQL语句
SELECT SQL_text,parse_calls,executions FROM v$SQLArea;
145,查看居留内存运行的实体信息
SELECT name ,kept,type,sharable_mem FROM v$db_object_cache WHERE type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE') and owner='SYS' ORDER BY name;
372
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
