oracle数据库日常管理语句汇总.docx
《oracle数据库日常管理语句汇总.docx》由会员分享，可在线阅读，更多相关《oracle数据库日常管理语句汇总.docx（22页珍藏版）》请在冰豆网上搜索。
oracle数据库日常管理语句汇总
一、查看临时表空间使用情况
Select
f.tablespace_name
,sum（f.bytes_free+f.bytes_used）
/1024/1024/1024"totalGB"
,sum（（f.bytes_free+f.bytes_used）-nvl（p.bytes_used,0））
/1024/1024/1024"FreeGB"
,sum（nvl（p.bytes_used,0））
/1024/1024/1024"UsedGB"
fromsys.v_$temp_space_headerf,dba_temp_filesd,sys.v_$temp_extent_poolp
wheref.tablespace_name（+）=d.tablespace_name
andf.file_id（+）=d.file_id
andp.file_id（+）=d.file_id
groupby
f.tablespace_name
二、查看使用临时表空间的SQL
Selectse.username,
se.sid,
su.extents,
su.blocks*to_number（rtrim（p.value））asSpace,
tablespace,
segtype,
sql_text
fromv$sort_usagesu,v$parameterp,v$sessionse,v$sqls
wherep.name='db_block_size'
andsu.session_addr=se.saddr
ands.hash_value=su.sqlhash
ands.address=su.sqladdr
orderbyse.username,se.sid
三、收缩临时表空间
altertablespacetempshrinkspace;
altertablespacetempshrinktempfile''
四、重建索引
alterindexPK_CROSSRELATIONrebuild;
五、查看表空间使用情况
SELECTUPPER（F.TABLESPACE_NAME）"表空间名",
D.TOT_GROOTTE_MB"表空间大小（M）",
D.TOT_GROOTTE_MB-F.TOTAL_BYTES"已使用空间（M）",
TO_CHAR（ROUND（（D.TOT_GROOTTE_MB-F.TOTAL_BYTES）/D.TOT_GROOTTE_MB*100,2）,'990.99'）||'%'"使用比",
F.TOTAL_BYTES"空闲空间（M）",
F.MAX_BYTES"最大块（M）"
FROM（SELECTTABLESPACE_NAME,
ROUND（SUM（BYTES）/（1024*1024）,2）TOTAL_BYTES,
ROUND（MAX（BYTES）/（1024*1024）,2）MAX_BYTES
FROMSYS.DBA_FREE_SPACE
GROUPBYTABLESPACE_NAME）F,
（SELECTDD.TABLESPACE_NAME,
ROUND（SUM（DD.BYTES）/（1024*1024）,2）TOT_GROOTTE_MB
FROMSYS.DBA_DATA_FILESDD
GROUPBYDD.TABLESPACE_NAME）D
WHERED.TABLESPACE_NAME=F.TABLESPACE_NAME
ORDERBY1;
六、查询表空间的总容量
selecttablespace_name,sum（bytes）/1024/1024asMB
fromdba_data_files
groupbytablespace_name;
七、查询表空间使用率
selecttotal.tablespace_name,
round（total.MB,2）asTotal_MB,考试大论坛
round（total.MB-free.MB,2）asUsed_MB,
round（（1-free.MB/total.MB）*100,2）||'%'asUsed_Pct
from（selecttablespace_name,sum（bytes）/1024/1024asMB
fromdba_free_space
groupbytablespace_name）free,
（selecttablespace_name,sum（bytes）/1024/1024asMB
fromdba_data_files
groupbytablespace_name）total
wherefree.tablespace_name=total.tablespace_name;
八、查找当前表级锁
selectsess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
fromv$locked_objectlo,
dba_objectsao,
v$sessionsess
whereao.object_id=lo.object_idandlo.session_id=sess.sid;
杀掉锁表进程：
altersystemkillsession'436,35123';
九、监控当前数据库谁在运行什么SQL语句
selectosuser,username,sql_text
fromv$sessiona,v$sqltextb
wherea.sql_address=b.addressorderbyaddress,piece;
十、找使用CPU多的用户session
selecta.sid,spid,status,substr（a.program,1,40）prog,a.terminal,osuser,value/60/100value
fromv$sessiona,v$processb,v$sesstatc
wherec.statistic#=12and
c.sid=a.sidand
a.paddr=b.addr
orderbyvaluedesc;
十一、查看死锁信息
SELECT（SELECTusername
FROMv$session
WHERESID=a.SID）blocker,a.SID,'isblocking',
（SELECTusername
FROMv$session
WHERESID=b.SID）blockee,b.SID
FROMv$locka,v$lockb
WHEREa.BLOCK=1ANDb.request>0ANDa.id1=b.id1ANDa.id2=b.id2;
十二、具有最高等待的对象
SELECTo.OWNER,o.object_name,o.object_type,a.event,
SUM（a.wait_time+a.time_waited）total_wait_time
FROMv$active_session_historya,dba_objectso
WHEREa.sample_timeBETWEENSYSDATE-30/2880ANDSYSDATE
ANDa.current_obj#=o.object_id
GROUPBYo.OWNER,o.object_name,o.object_type,a.event
ORDERBYtotal_wait_timeDESC;
十三、查看具有最高等待的对象
SELECTa.session_id,s.osuser,s.machine,s.program,o.owner,o.object_name,
o.object_type,a.event,
SUM（a.wait_time+a.time_waited）total_wait_time
FROMv$active_session_historya,dba_objectso,v$sessions
WHEREa.sample_timeBETWEENSYSDATE-30/2880ANDSYSDATE
ANDa.current_obj#=o.object_id
ANDa.session_id=s.SID
GROUPBYo.owner,
o.object_name,
o.object_type,
a.event,
a.session_id,
s.program,
s.machine,
s.osuser
ORDERBYtotal_wait_timeDESC;
十四、查看等待最多的SQL
SELECTa.program,a.session_id,a.user_id,d.username,s.sql_text,
SUM（a.wait_time+a.time_waited）total_wait_time
FROMv$active_session_historya,v$sqlareas,dba_usersd
WHEREa.sample_timeBETWEENSYSDATE-30/2880ANDSYSDATE
ANDa.sql_id=s.sql_id
ANDa.user_id=d.user_id
GROUPBYa.program,a.session_id,a.user_id,s.sql_text,d.username;
十五、显示正在等待锁的所有会话
SELECT*FROMDBA_WAITERS;
十七、查数据库中正在执行的SQL
SELECTSE.INST_ID,--实例
SQ.SQL_TEXT,/*SQL文本*/
SQ.SQL_FULLTEXT,/*SQL全部文本*/
SE.SID,/*会话的唯一标识，通常要对某个会话进行分析前，首先就需要获得该会话的SID。
*/
--SE.SERIAL#,/*会话的序号*/
SQ.OPTIMIZER_COSTASCOST_,/*COST值*/
SE.LAST_CALL_ETCONTINUE_TIME,/*执行时间可能是单个sql也可能是整个功能*/
SE.PREV_EXEC_START,/*SQLexecutionstartofthelastexecutedSQLstatement*/
SE.EVENT,/*等待事件*/
SE.LOCKWAIT,/*是否等待LOCK（SE，P）*/
SE.MACHINE,/*客户端的机器名。
（WORKGROUP\PC-201211082055）*/
SQ.SQL_ID,/*SQL_ID*/
SE.USERNAME,/*创建该会话的用户名*/
SE.LOGON_TIME/*登陆时间*/
--SE.TERMINAL,/*客户端运行的终端名。
（PC-201211082055）*/
--,SQ.HASH_VALUE,/*一个SQL产生的HASH值*/
--SQ.PLAN_HASH_VALUE/*执行SQL的HASH值（解析后HASH值），与SQL_ADDRESS关联查询其他SQL相关视图后即可查询会话当前正在执行的SQL语句*/
FROMGV$SESSIONSE,/*会话信息。
每一个连接到ORACLE数据库的会话都能在该视图中对应一条记录，根据该视图中的信息可以查询该会话使用的用户，正在执行或者刚刚执行的SQL语句*/
/*[GV$SQLAREA多节点]*/
GV$SQLAREASQ/*跟踪所有SHAREDPOOL中的共享CURSOR信息，包括执行次数，逻辑读，物理读等*/
WHERESE.SQL_HASH_VALUE=SQ.HASH_VALUE
ANDSE.STATUS='ACTIVE'
ANDSE.SQL_ID=SQ.SQL_ID
ANDSQ.INST_ID=SE.INST_ID
ANDSE.USERNAMEisnotnull;
--过滤条件
--ANDSE.USERNAME='FWSB'--用户名
--ANDSQ.COMMAND_TYPEIN（2,3,5,6,189）
--ANDSE.SID!
=USERENV（'SID'）/*rac集群环境误用*/
--ANDMACHINE!
='WORKGROUP\MHQ-PC';
十八、每天执行慢的SQL
十九、查看非绑定变量的SQLSELECTS.SQL_TEXT,
S.SQL_FULLTEXT,
S.SQL_ID,
ROUND（ELAPSED_TIME/1000000/（CASE
WHEN（EXECUTIONS=0ORNVL（EXECUTIONS,1）=1）THEN
1
ELSE
EXECUTIONS
END）,
2）"执行时间'S'",
S.EXECUTIONS"执行次数",
S.OPTIMIZER_COST"COST",
S.SORTS,
S.MODULE,--连接模式（JDBCTHINCLIENT：
程序）
--S.LOCKED_TOTAL,
S.PHYSICAL_READ_BYTES"物理读",
--S.PHYSICAL_READ_REQUESTS"物理读请求",
S.PHYSICAL_WRITE_REQUESTS"物理写",
--S.PHYSICAL_WRITE_BYTES"物理写请求",
S.ROWS_PROCESSED"返回行数",
S.DISK_READS"磁盘读",
S.DIRECT_WRITES"直接路径写",
S.PARSING_SCHEMA_NAME,
S.LAST_ACTIVE_TIME
FROMGV$SQLAREAS
WHEREROUND（ELAPSED_TIME/1000000/（CASE
WHEN（EXECUTIONS=0ORNVL（EXECUTIONS,1）=1）THEN
1
ELSE
EXECUTIONS
END）,
2）>5--1000000微秒=1S
--ANDS.PARSING_SCHEMA_NAME=USER
ANDTO_CHAR（S.LAST_LOAD_TIME,'YYYY-MM-DD'）=
TO_CHAR（SYSDATE,'YYYY-MM-DD'）
ANDS.COMMAND_TYPEIN（2,3,5,6,189）/*值对应类型2：
INSERT、3：
SELECT、6：
UPDATE、7：
DELETE、189：
MERGE查询V$SQLCOMMAND*/
ANDMODULE='JDBCThinClient'
ORDERBY"执行时间'S'"DESC;
SELECTV.SQL_ID,
V.SQL_FULLTEXT,
V.PARSING_SCHEMA_NAME,
FM.EXECUTIONS_COUNT,
FM.ELAPSED_TIME
FROM（SELECTL.FORCE_MATCHING_SIGNATUREMATHCES,
MAX（L.SQL_ID||L.CHILD_NUMBER）MAX_SQL_CHILD,
DENSE_RANK（）OVER（ORDERBYCOUNT（*）DESC）RANKING,
ROUND（SUM（ROUND（ELAPSED_TIME/1000000/（CASE
WHEN（EXECUTIONS=0ORNVL（EXECUTIONS,1）=1）THEN
1
ELSE
EXECUTIONS
END）,
5）））ELAPSED_TIME,
SUM（L.EXECUTIONS）EXECUTIONS_COUNT
FROMV$SQLL
WHERETO_CHAR（TO_DATE（LAST_LOAD_TIME,'YYYY-MM-DDHH24:
MI:
SS'）,
'YYYY-MM-DD'）=TO_CHAR（SYSDATE-1,'YYYY-MM-DD'）--当天LAST_LOAD_TIME（VARCHAR类型,LOADEDINTOTHELIBRARYCACHETIME）
ANDL.MODULELIKE'%JDBC%'--程序连接
ANDL.FORCE_MATCHING_SIGNATURE<>0
ANDL.PARSING_SCHEMA_NAME=UPPER（'&USERNAME'）--用户
ANDL.COMMAND_TYPEIN（2,3,5,6,189） --命令类型2：
INSERT、3：
SELECT、6：
UPDATE、7：
DELETE、189：
MERGE查询V$SQLCOMMAND
GROUPBYL.FORCE_MATCHING_SIGNATURE
HAVINGCOUNT（*）>5）FM,
V$SQLV
WHEREFM.MAX_SQL_CHILD=（V.SQL_ID||V.CHILD_NUMBER）
ANDEXECUTIONS_COUNT>=50--执行次数超过50次先筛选改写，后续慢慢在范围小
ORDERBYFM.RANKING;
--V$SQL_BIND_CAPTURE--记录包含变量得表..包括ROWNUM<:
1变量
二十、查看LOG切换频率
selectb.SEQUENCE#,
b.FIRST_TIME,
a.SEQUENCE#,
a.FIRST_TIME,
round（（（a.FIRST_TIME-b.FIRST_TIME）*24）*60,2）
fromv$log_historya,v$log_historyb
wherea.SEQUENCE#=b.SEQUENCE#+1
andb.THREAD#=1
orderbya.SEQUENCE#desc;
二十一、查看SQL执行进度
--显示运行时间超过6秒的数据库操作的状态
SELECTA.SID,
A.SERIAL#,
OPNAME,
TARGET,--对象
TO_CHAR（START_TIME,'YYYY-MM-DDHH24:
MI:
SS'）START_TIME,--开始时间
（SOFAR/TOTALWORK）*100PROGRESS,--进度比
TIME_REMAINING,--估算剩余时间
ELAPSED_SECONDS,--运行时间‘S’
A.SQL_ID
FROMV$SESSION_LONGOPSA
WHERESID=;
***其中SID和SERIAL#是与V$SESSION中的匹配的，
***OPNAME：
指长时间执行的操作名.如：
TABLESCAN
***TARGET：
被操作的OBJECT_NAME.如：
TABLEA
***TARGET_DESC：
描述TARGET的内容
***SOFAR：
这个是需要着重去关注的，表示已要完成的工作数，如扫描了多少个块。
***TOTALWORK：
指目标对象一共有多少数量（预计）。
如块的数量。
***START_TIME：
进程的开始时间
***LAST_UPDATE_TIM：
最后一次调用SET_SESSION_LONGOPS的时间
***TIME_REMAINING：
估计还需要多少时间完成，单位为秒
***ELAPSED_SECONDS：
指从开始操作时间到最后更新时间
***MESSAGE：
对于操作的完整描述，包括进度和操作内容。
***USERNAME：
与V$SESSION中的一样。
***SQL_ADDRESS：
关联V$SQL
***SQL_HASH_VALUE：
关联V$SQL
***QCSID：
主要是并行查询一起使用。
二十二、查询外键字段在主键表中没有索引的
SELECTC.*,
C1.r_constraint_name,
c2.table_name,
T.NUM_ROWS,
'createindexidx_'||c.table_name||'_'||column_name||'on'||
c.table_name||'（'||column_name||'）;'
FROMUSER_CONS_COLUMNSC
JOINUSER_CONSTRAINTSC1
ONC1.CONSTRAINT_NAME=C.CONSTRAINT_NAME
ANDC1.CONSTRAINT_TYPE='R'
AND（C.TABLE_NAME,C.COLUMN_NAME）NOTIN
（SELECTTABLE_NAME,COLUMN_NAMEFROMUSER_IND_COLUMNSI）
JOINUSER_TABLEST
ONT.TABLE_NAME=C.TABLE_NAME
joinUSER_CONSTRAINTSc2
onc1.r_constraint_name=c2.constraint_name;
博客：
为什么子表外键列需要建立索引?
http:
升级会员 