Oracle DBA常用sql有哪些
这篇文章主要介绍“Oracle DBA常用sql有哪些”，在日常操作中，相信很多人在Oracle DBA常用sql有哪些问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”Oracle DBA常用sql有哪些”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
1、Oracle 查询每天执行慢的SQL
2、Oracle 查询锁之间的依赖关系
3、Oracle 查找锁之间依赖关系的最源头SID
4、Oracle 查询各表空间使用情况--完善篇
5、Oracle 定期检查意义不大的索引
6、Oracle 以月为单位检查索引的使用情况(邮件反馈)
7、Oracle 是分区表，但条件不带分区条件的SQL
8、Oracle 表结构顺序不一致 隐藏的2个问题...
9、Oracle 查看 表属性 ：“表名(注释)/列名(注释)/字段是否NULL”
10、Oracle 查找某一个包体’PACKAGE BODY‘中包含PROCEDURE/FUNCTION的名称有哪些
小SQL
连接~
/*查看Oracle错误号信息*/[oracle@lottery~]$oerrora600/*清屏~*/SQL>clearscreen/*注册oracle监听*/SQL>altersystemregister;/*查看OS连DB数*/[oracle@lottery~]$ps-ef|greporacle$ORACLE_SID|grepLOCAL=NO|wc-l/*查询数据库当前进程的连接数*/selectcount(*)fromv$process;/*查看数据库当前会话的连接数*/selectcount(*)fromv$session;/*查看数据库用户连接会话的总数*/selectusername,count(username)fromgv$sessionwhereusernameisnotnullgroupbyusername;/*查询数据库最大连接/进程数*/selectname,valuefromv$parameterwherenamein('processes','sessions');==>showparameterprocesses/sessions优化~
/*通过SQL_ID查找执行计划*/select*fromtable(dbms_xplan.display_cursor('br8d2xs44sga8'));/*通过SQL_ID查找SQL文本*/select*fromgv$sqlareaswheres.sql_id='br8d2xs44sga8';/*查看数据库的等待事件*/SELECT*FROMgv$session_waitwheresidin(SELECTsidFROMgV$SESSIONWHERESTATUS='ACTIVE'andusernameisnotnullandsid!=userenv('sid'));/*查看表的统计信息是否正确*/SELECTTABLE_NAME,NUM_ROWS,LAST_ANALYZEDFROMUSER_TABLESTWHERETABLE_NAME='表'; --#用于查看表最后一次统计和真实行数差距；/*查看表所有字段信息*/select*fromuser_tab_columnswheretable_name='表';/*统计整个用户*/beginDBMS_STATS.gather_schema_stats('用户',cascade=>TRUE,no_invalidate=>false);end;/*统计表*/beginDBMS_STATS.GATHER_TABLE_STATS('用户','表',cascade=>TRUE);end;/*查看表最后一次DML时间*/selectmax(ora_rowscn),scn_to_timestamp(max(ora_rowscn))from表;基本信息~
/*查看表空间剩余情况*/selectTABLESPACE_NAME,sum(round(bytes/1024/1024/1024,2))fromdba_free_spaceagroupbytablespace_name;/*查询内存分配情况*/selectcomponent,current_size/1024/1024MB,user_specified_size/1024MBfromv$memory_dynamic_componentswherecurrent_size!=0;/*查看用户大小*/SELECTOWNER,SUM(BYTES/1024/1024/1024)FROMDBA_SEGMENTSGROUPBYOWNER;/*查看数据库默认表空间*/select*fromdatabase_propertiesswheres.descriptionlike'%default%tablespace'/*查看库中的临时表*/select*fromuser_tablesuwhereu.temporary='Y';/*查看11galert文件位置*/selectvaluefromv$diag_info;-->showparameterdiagnostic_dest/*当前回话的进程号*/selectspidfromv$processwhereaddrin(selectpaddrfromv$sessionwheresidin(selectsidfromv$mystatwhererownum=1));权限~
/*查看resource角色的权限*/select*fromrole_sys_privswhererole='RESOURCE';/*查看数据库中授dba权限的用户*/SELECT*FROMDBA_ROLE_PRIVSSWHERES.GRANTED_ROLE='DBA';dblink~
/*创建DBLINK语句*/createpublicdatabaselinkdblink名connectto用户identifiedby密码using'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=IP地址)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=实例名)))';其他~
/*查找快照SNAP_ID对应的时间*/select*fromsys.wrh$_active_session_history;/*查看索引拥有者!=表的拥有者*/SELECTowner,index_name,index_type,table_owner,table_name,table_typeFROMdba_indexeswhereowner!=table_owner;/*查看库中(只读)属性的表*/selecttable_name,status,read_onlyfromdba_tableswhereread_only='YES';#更改表属性altertable表readonly(readwrite);(11g新特性)#注意：索引创建/修改对只读表【表空间】没有影响！因为索引修改的是数据字典，和表不相关/*查看分区表基本信息查询*/SELECTTABLE_NAME,column_name,PARTITION_NAME,HIGH_VALUELESS_THAN值,TABLESPACE_NAMEFROMUSER_TAB_PARTITIONStpjoinUSER_PART_KEY_COLUMNStpcontp.table_name=tpc.name;/*查看某用户登录的所有会话*/SELECT'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';',S.*FROMV$SESSIONSWHEREUSERNAME='用户'ANDSTATUS!='KILLED';--用于解决ORA-01940无法删除当前连接的用户/*查看command_type值对应类型*/SELECT*FROMv$sqlcommand;--【v$sqlarea.command_type、v$session.command】/*查看某sql_id绑定变量部分传的值*/SELECT*FROMV$SQL_BIND_CAPTUREswheres.sql_idin('fdc8mt5xnjx2a')andCHILD_ADDRESS=2;/*查找序列last_number*/SELECT*FROMUSER_SEQUENCESSWHERES.SEQUENCE_NAME='SEQ_CS_ONCE_CHAR_DET';/*oracle查看链接的hostname和IP分别是什么*/selectutl_inaddr.get_host_address(host_name),host_namefromv$instance;---用于当有2个服务器的数据库是同版本、同监听、同实例/*oracle查看standby库延迟时间*/ SELECT((substr(value,2,2)*24+substr(value,5,2))*60+substr(value,8,2))*60+substr(value,-2)TIMEFROMgv$dataguard_statswherename='applylag';
/*查看会话状态被置为"killed"*/ select a.spid,b.sid,b.serial#,b.username from v$process a,v$session b where a.addr=b.paddr and b.status= 'KILLED';
-->系统层释放DB kill状态的会话； （linux：kill -9 spid； Windows ：orakill orcl pid）
SELECT distinct 'ALTER SYSTEM KILL SESSION '''||SID ||','|| s.SERIAL#||''';',
/*ORA-00054: 资源正忙,但指定以 O.OWNER, O.OBJECT_NAME , S.STATUS, LOCKWAIT FROM V$SESSION S JOIN V$LOCKED_OBJECT LO
NOWAIT方式获取资源,或者超时失效*/ ON LO.SESSION_ID = S.SID JOIN DBA_OBJECTS O ON O.OBJECT_ID = LO.OBJECT_ID and S.STATUS='ACTIVE' AND OBJECT_NAME in ( '表名字') ;
大SQL
一、查数据库中正在执行的SQL：
SELECTSE.INST_ID,--实例
SQ.SQL_TEXT,/*SQL文本*/
SQ.SQL_FULLTEXT,/*SQL全部文本*/
SE.SID,/*会话的唯一标识，通常要对某个会话进行分析前，首先就需要获得该会话的SID。*/
--SE.SERIAL#,/*会话的序号*/
SQ.OPTIMIZER_COSTASCOST_,/* COST 值*/
SE.LAST_CALL_ET CONTINUE_TIME,/*执行时间 单位是秒 （时间可能是单个sql，也可能是整个功能）*/
CEIL((SYSDATE-SE.PREV_EXEC_START)*24*60*60)相差秒数,/*执行时间是整个功能时会用到这部分来判断单个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$SESSION SE,/*会话信息。每一个连接到ORACLE数据库的会话都能在该视图中对应一条记录，根据该视图中的信息可以查询该会话使用的用户，正在执行或者刚刚执行的SQL语句*/
/*[GV$SQLAREA 多节点 ]*/
GV$SQLAREA SQ/*跟踪所有SHARED POOL中的共享CURSOR信息，包括 执行次数，逻辑读，物理读等*/
WHERESE.SQL_HASH_VALUE = SQ.HASH_VALUE
ANDSE.STATUS ='ACTIVE'
ANDSE.SQL_ID = SQ.SQL_ID
ANDSQ.INST_ID = SE.INST_ID
ANDSE.USERNAME is not null;
--过滤条件
--ANDSE.USERNAME ='FWSB'--用户名
--AND SQ.COMMAND_TYPE IN (2, 3, 5, 6, 189)
--ANDSE.SID !=USERENV('SID')/*rac集群环境误用*/
--ANDMACHINE !='WORKGROUP\MHQ-PC';
二、每天执行慢的SQL：
SELECTS.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,--连接模式（JDBC THIN CLIENT：程序）
-- 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$SQLAREA S
WHEREROUND(ELAPSED_TIME /1000000/ (CASE
WHEN(EXECUTIONS =0ORNVL(EXECUTIONS,1) =1)THEN
1
ELSE
EXECUTIONS
END),
2) >5--100 0000微秒=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='JDBC Thin Client'
ORDERBY"执行时间'S'"DESC;
三、查看非绑定变量的SQL：
SELECTV.SQL_ID,
V.SQL_FULLTEXT,
V.PARSING_SCHEMA_NAME,
FM.EXECUTIONS_COUNT,
FM.ELAPSED_TIME
FROM(SELECTL.FORCE_MATCHING_SIGNATURE MATHCES,
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$SQL L
WHERETO_CHAR(TO_DATE(LAST_LOAD_TIME,'YYYY-MM-DD HH24:MI:SS'),
'YYYY-MM-DD') = TO_CHAR(SYSDATE-1,'YYYY-MM-DD')-- 当天 LAST_LOAD_TIME(VARCHAR类型,LOADED INTO THE LIBRARY CACHE TIME)
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$SQL V
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) 时间min
fromv$log_history a, v$log_history b
wherea.SEQUENCE# = b.SEQUENCE# +1
andb.THREAD# =1
orderbya.SEQUENCE#desc;
查看每小时log切换的次数
SELECT trunc(first_time) "Date",
to_char(first_time, 'Dy') "Day",
count(1) "Total",
SUM(decode(to_char(first_time, 'hh34'),'00',1,0)) "h0",
SUM(decode(to_char(first_time, 'hh34'),'01',1,0)) "h2",
SUM(decode(to_char(first_time, 'hh34'),'02',1,0)) "h3",
SUM(decode(to_char(first_time, 'hh34'),'03',1,0)) "h4",
SUM(decode(to_char(first_time, 'hh34'),'04',1,0)) "h5",
SUM(decode(to_char(first_time, 'hh34'),'05',1,0)) "h6",
SUM(decode(to_char(first_time, 'hh34'),'06',1,0)) "h7",
SUM(decode(to_char(first_time, 'hh34'),'07',1,0)) "h7",
SUM(decode(to_char(first_time, 'hh34'),'08',1,0)) "h8",
SUM(decode(to_char(first_time, 'hh34'),'09',1,0)) "h9",
SUM(decode(to_char(first_time, 'hh34'),'10',1,0)) "h20",
SUM(decode(to_char(first_time, 'hh34'),'11',1,0)) "h21",
SUM(decode(to_char(first_time, 'hh34'),'12',1,0)) "h22",
SUM(decode(to_char(first_time, 'hh34'),'13',1,0)) "h23",
SUM(decode(to_char(first_time, 'hh34'),'14',1,0)) "h24",
SUM(decode(to_char(first_time, 'hh34'),'15',1,0)) "h25",
SUM(decode(to_char(first_time, 'hh34'),'16',1,0)) "h26",
SUM(decode(to_char(first_time, 'hh34'),'17',1,0)) "h27",
SUM(decode(to_char(first_time, 'hh34'),'18',1,0)) "h28",
SUM(decode(to_char(first_time, 'hh34'),'19',1,0)) "h29",
SUM(decode(to_char(first_time, 'hh34'),'20',1,0)) "h30",
SUM(decode(to_char(first_time, 'hh34'),'21',1,0)) "h31",
SUM(decode(to_char(first_time, 'hh34'),'22',1,0)) "h32",
SUM(decode(to_char(first_time, 'hh34'),'23',1,0)) "h33"
FROM V$log_history
where trunc(first_time)>sysdate-8
group by trunc(first_time), to_char(first_time, 'Dy')
Order by 1;
五、查看SQL执行进度： --显示运行时间超过6秒的数据库操作的状态
SELECTA.SID,
A.SERIAL#,
OPNAME,
TARGET,--对象
TO_CHAR(START_TIME,'YYYY-MM-DD HH24:MI:SS') START_TIME,--开始时间
(SOFAR / TOTALWORK) *100PROGRESS,--进度比
TIME_REMAINING,--估算剩余时间
ELAPSED_SECONDS,--运行时间‘S’
A.SQL_ID
FROMV$SESSION_LONGOPS A
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,
'create index idx_'|| c.table_name ||'_'|| column_name ||' on '||
c.table_name ||'('|| column_name ||');'
FROMUSER_CONS_COLUMNS C
JOINUSER_CONSTRAINTS C1
ONC1.CONSTRAINT_NAME = C.CONSTRAINT_NAME
ANDC1.CONSTRAINT_TYPE ='R'
AND(C.TABLE_NAME, C.COLUMN_NAME)NOTIN
(SELECTTABLE_NAME, COLUMN_NAMEFROMUSER_IND_COLUMNS I)
JOINUSER_TABLES T
ONT.TABLE_NAME = C.TABLE_NAME
joinUSER_CONSTRAINTS c2
onc1.r_constraint_name = c2.constraint_name;
博客：为什么子表外键列需要建立索引?http://blog.itpub.net/17203031/viewspace-701832/
** 自己测试【外键字段不加索引时】
**update外键表，主键表delete任何数据都不允许；但update session1的范围 且set字段不是where字段就可以执行，加索引后，更改where字段的数据会报错
七、查看软硬解析，游标数
SELECT/*A.SID,*//* A.STATISTIC#,*/
SUM(A.VALUE),
B.NAME,
(CASE
WHENNAME='PARSE COUNT (TOTAL)'THEN
'表示总的解析次数'
WHENNAME='PARSE COUNT (HARD)'THEN
'表示硬解析的次数'
WHENNAME='SESSION CURSOR CACHE COUNT'THEN
'表示缓存的游标个数'
WHENNAME='SESSION CURSOR CACHE HITS'THEN
'表示从缓存中找到游标的次数'
WHENNAME='OPENED CURSORS CURRENT'THEN
'表示SESSION中打开的游标数'
END)
FROMV$SESSTAT A, V$STATNAME B
WHEREA.STATISTIC# = B.STATISTIC#
ANDB.NAMEIN('PARSE COUNT (HARD)',
'PARSE COUNT (TOTAL)',
'SESSION CURSOR CACHE COUNT',
'SESSION CURSOR CACHE HITS',
'OPENED CURSORS CURRENT')
-- AND SID=11
GROUPBYB.NAME
ORDERBYNAME;
--#用于衡量 软硬解析/游标共享比.
八、查看未提交的事物的会话和锁的对象
SELECTDISTINCTS.SID,
S.SERIAL#,
S.MACHINE,
L.SQL_TEXT,
S.LAST_CALL_ET,
'ALTER SYSTEM KILL SESSION '''|| S.SID ||','|| S.SERIAL# ||
''';',
LO.ORACLE_USERNAME,
LO.OS_USER_NAME,
AO.OBJECT_NAME,
LO.LOCKED_MODE
FROMV$SESSION S,
V$TRANSACTION T,
V$SQL L,
V$LOCKED_OBJECT LO,
DBA_OBJECTS AO
WHERES.TADDR = T.ADDR
ANDS.PREV_SQL_ADDR = L.ADDRESS
ANDAO.OBJECT_ID = LO.OBJECT_ID
ANDLO.SESSION_ID = S.SID;
九、通过系统中PID去数据库中找执行的SQL:
SELECTA.USERNAME, A.PROGRAM, B.SPID, C.SQL_TEXT, C.SQL_FULLTEXT
FROMV$SESSION A, V$PROCESS B, V$SQLAREA C
WHEREA.PADDR = B.ADDR
ANDA.SQL_HASH_VALUE = C.HASH_VALUE
ANDA.STATUS ='ACTIVE'
ANDA.USERNAMENOTIN('SYS','SYSTEM','SYSMAN')
ANDA.SID !=USERENV('SID')
ANDB.SPID = 填写PID;
十、序列/索引差异 比对结果后的创建语句（例如：将A用户index和B用户对比，将A用户多B用户的在B用户创建)
【如下2个SQL都需要在 缺少sequence/index A用户执行】
--#SEQUENCE的创建语句：
SELECT'CREATE SEQUENCE '|| SEQUENCE_NAME ||' MINVALUE '|| MIN_VALUE ||
' MAXVALUE '|| MAX_VALUE ||' START WITH '|| LAST_NUMBER ||
' INCREMENT BY '|| INCREMENT_BY || (CASE
WHENCACHE_SIZE =0THEN
' NOCACHE'
ELSE
' CACHE '|| CACHE_SIZE
END) ||';'
FROMUSER_SEQUENCES W
WHERE--过滤掉登录用户存在的SEQUENCE
NOTEXISTS(SELECT1
FROMUSER_SEQUENCES@DB_SINOSOFT W1
WHEREW.SEQUENCE_NAME = W1.SEQUENCE_NAME);
--#索引差异 结果的创建语句
SELECT'CREATE '|| INDEX_TYPE ||' INDEX '|| INDEX_NAME ||' ON '||
TABLE_NAME ||' ('|| LISTAGG(CNAME,',')WITHINGROUP(ORDERBYCOLUMN_POSITION) ||');'
FROM(SELECTIC.INDEX_NAME,
IC.TABLE_NAME,
IC.COLUMN_NAME CNAME,
IC.COLUMN_POSITION,
COUNT(IC.INDEX_NAME)OVER(PARTITIONBYIC.INDEX_NAME, IC.TABLE_NAME) CON,
I.INDEX_TYPE
FROMUSER_IND_COLUMNS@DB_SINOSOFT IC
JOINUSER_INDEXES@DB_SINOSOFT I
ONI.INDEX_NAME = IC.INDEX_NAME
WHERE
--过滤掉登录用户存在的INDEX
NOTEXISTS
(SELECT1
FROMUSER_IND_COLUMNS IC1
WHEREIC1.INDEX_OWNER =UPPER('&TO_USERNAME')
ANDIC.INDEX_NAME = IC1.INDEX_NAME)
--过滤掉主键，避免索引创建，在创建主键报错 对象已存在
ANDIC.INDEX_NAMENOTIN
(SELECTC.CONSTRAINT_NAMEFROMUSER_CONSTRAINTS@DB_SINOSOFT C)
ORDERBYIC.INDEX_NAME, IC.COLUMN_POSITION)
GROUPBYINDEX_TYPE, CON, INDEX_NAME, TABLE_NAME;
十一、查看热点块的对象
SELECTA.HLADDR, A.FILE#, A.DBABLK, A.TCH, A.OBJ, B.OBJECT_NAME
FROMX$BH A, DBA_OBJECTS B
WHERE(A.OBJ = B.OBJECT_IDORA.OBJ = B.DATA_OBJECT_ID)
ANDA.HLADDR ='0000000054435000'--V$SESSION_WAIT.P1RAW
UNION
SELECTHLADDR, FILE#, DBABLK, TCH, OBJ,NULL
FROMX$BH
WHEREOBJIN(SELECTOBJ
FROMX$BH
WHEREHLADDR ='0000000054435000'
MINUS
SELECTOBJECT_ID
FROMDBA_OBJECTS
MINUS
SELECTDATA_OBJECT_IDFROMDBA_OBJECTS)
ANDHLADDR ='0000000054435000'
ORDERBY4;
十一、查看某用户表大小/总数情况
SELECTT.TABLE_NAME,
TC.COMMENTS,
T.NUM_ROWS,
ROUND(SUM(S.BYTES /1024/1024/1024)) GB
FROMUSER_TABLES T
JOINUSER_SEGMENTS S
ONS.SEGMENT_NAME = T.TABLE_NAME
JOINUSER_TAB_COMMENTS TC
ONTC.TABLE_NAME = T.TABLE_NAME
GROUPBYT.TABLE_NAME, TC.COMMENTS, T.NUM_ROWS
ORDERBYNUM_ROWSDESCNULLSLAST;
十二、重新编译失效存储/包语句：
SELECT 'ALTER ' || (CASE
WHEN OBJECT_TYPE = 'PACKAGE BODY' THEN
'PACKAGE'ELSEOBJECT_TYPE
END) || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE ' || (CASE
WHEN OBJECT_TYPE = 'PACKAGE BODY' THEN
'BODY;'ELSE';'END),--除类型是PACKAGE BODY返回是PACKAGE，其他正常显示类型，是PACKAGE BODY显示COMPILE BODY 否则显示COMPILE
OWNER,
OBJECT_NAME,
OBJECT_TYPE,
STATUS,
O.CREATED,
LAST_DDL_TIME
FROM DBA_OBJECTS O
WHERE STATUS = 'INVALID'-->存储状态'无效';
十三、Oracle 查看各表空间使用情况和最大最小块：
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 "空闲空间(G)",
F.MAX_BYTES "最大块(G)"
FROM(SELECTTABLESPACE_NAME,
ROUND(SUM(BYTES) /1024/1024/1024,2) TOTAL_BYTES,
ROUND(MAX(BYTES) /1024/1024/1024,2) MAX_BYTES
FROMSYS.DBA_FREE_SPACE
GROUPBYTABLESPACE_NAME) F,
(SELECTDD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) /1024/1024/1024,2) TOT_GROOTTE_MB
FROMSYS.DBA_DATA_FILES DD
GROUPBYDD.TABLESPACE_NAME) D
WHERED.TABLESPACE_NAME = F.TABLESPACE_NAME;
十四、Oracle 查看TEMP表空间使用情况：
SELECTF.BYTES_FREE + F.BYTES_USED TOTAL_BYTES,
F.BYTES_FREE + F.BYTES_USED -NVL(P.BYTES_USED,0) FREE_BYTES,
D.FILE_NAME,
NVL(P.BYTES_USED,0) USED_BYTES
FROMSYS.V_$TEMP_SPACE_HEADER F,
DBA_TEMP_FILES D,
SYS.V_$TEMP_EXTENT_POOL P
WHEREF.TABLESPACE_NAME(+) = D.TABLESPACE_NAME
ANDF.FILE_ID(+) = D.FILE_ID
ANDP.FILE_ID(+) = D.FILE_ID;
--> 等同于
SELECTTABLESPACE_NAME,
TF.TABLESPACE_SIZE,
TF.FREE_SPACE,
TF.TABLESPACE_SIZE - TF.FREE_SPACE
FROMDBA_TEMP_FREE_SPACE TF;
十五、Oracle 查看回滚进度情况用的几个SQL：
SELECTDISTINCTKTUXESIZFROMX$KTUXEWHEREKTUXESTA ='ACTIVE';
SELECTUSED_UBLKFROMV$TRANSACTION;
SELECTKTUXEUSN, KTUXESLT
FROMX$KTUXE
WHERE/*KTUXECFL = 'DEAD' AND*/
KTUXESTA ='ACTIVE';
SELECT*FROMV_$FAST_START_TRANSACTIONS;
SELECTUSED_UBLK, T.USED_URECFROMV$TRANSACTION T;
--查询视图V$FAST_START_TRANSACTIONS中字段UNDOBLOCKSDONE，UNDOBLOCKSTOTAL估算SMON恢复进度
到此，关于“Oracle DBA常用sql有哪些”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
