共计 16374 个字符，预计需要花费 41 分钟才能阅读完成。
这篇文章主要介绍“Oracle DBA 常用 sql 有哪些”，在日常操作中，相信很多人在 Oracle DBA 常用 sql 有哪些问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”Oracle DBA 常用 sql 有哪些”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！
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 ~]$oerr ora 600 /* 清屏~*/ SQL clear screen /* 注册 oracle 监听 */ SQL alter system register; /* 查看 OS 连 DB 数 */ [oracle@lottery ~]$ ps -ef | grep oracle$ORACLE_SID| grep LOCAL=NO| wc -l /* 查询数据库当前进程的连接数 */ select count(*) from v$process; /* 查看数据库当前会话的连接数 */ select count(*) from v$session; /* 查看数据库用户连接会话的总数 */ select username,count (username) from gv$session where username is not null group by username; /* 查询数据库最大连接 / 进程数 */ select name,value from v$parameter where name in (processes , sessions == show parameter processes/sessions 优化~
/* 通过 SQL_ID 查找执行计划 */ select * from table(dbms_xplan.display_cursor( br8d2xs44sga8 /* 通过 SQL_ID 查找 SQL 文本 */ select * from gv$sqlarea s where s.sql_id= br8d2xs44sga8 /* 查看数据库的等待事件 */ SELECT * FROM gv$session_wait where sid in (SELECT sid FROM gV$SESSION WHERE STATUS= ACTIVE and username is not null and sid!=userenv( sid /* 查看表的统计信息是否正确 */ SELECT TABLE_NAME,NUM_ROWS,LAST_ANALYZED FROM USER_TABLES T WHERE TABLE_NAME= 表 –# 用于查看表最后一次统计和真实行数差距； /* 查看表所有字段信息 */ select * from user_tab_columns where table_name= 表 ; /* 统计整个用户 */ begin DBMS_STATS.gather_schema_stats( 用户 ,cascade= TRUE,no_invalidate= false); end; /* 统计表 */ begin DBMS_STATS.GATHER_TABLE_STATS(用户 , 表 , cascade= TRUE); end ; /* 查看表最后一次 DML 时间 */ select max(ora_rowscn),scn_to_timestamp(max(ora_rowscn)) from 表; 基本信息~
/* 查看表空间剩余情况 */ select TABLESPACE_NAME,sum(round(bytes/1024/1024/1024,2)) from dba_free_space a group by tablespace_name; /* 查询内存分配情况 */ select component,current_size/1024/1024 MB, user_specified_size/1024 MB from v$memory_dynamic_components where current_size!=0; /* 查看用户大小 */ SELECT OWNER,SUM(BYTES/1024/1024/1024) FROM DBA_SEGMENTS GROUP BY OWNER; /* 查看数据库默认表空间 */ select * from database_properties s where s.description like %default%tablespace /* 查看库中的临时表 */ select * from user_tables u where u.temporary= Y /* 查看 11g alert 文件位置 */ select value from v$diag_info; — show parameter diagnostic_dest /* 当前回话的进程号 */ select spid from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat where rownum=1)); 权限~
/* 查看 resource 角色的权限 */ select * from role_sys_privs where role= RESOURCE ; /* 查看数据库中授 dba 权限的用户 */ SELECT * FROM DBA_ROLE_PRIVS S WHERE S.GRANTED_ROLE= DBA dblink~
/* 创建 DBLINK 语句 */ create public database link dblink 名 connect to 用户 identified by 密码 using (DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP) (HOST = IP 地址 )(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = 实例名))) 其他~
/* 查找快照 SNAP_ID 对应的时间 */ select * from sys.wrh$_active_session_history; /* 查看索引拥有者!= 表的拥有者 */ SELECT owner,index_name,index_type,table_owner,table_name,table_type FROM dba_indexes where owner!=table_owner; /* 查看库中 (只读) 属性的表 */ select table_name,status,read_only from dba_tables where read_only= YES #更改表属性 alter table 表 read only(read write);(11g 新特性) #注意：索引创建 / 修改对只读表【表空间】没有影响！因为索引修改的是数据字典，和表不相关 /* 查看分区表基本信息查询 */ SELECT TABLE_NAME,column_name,PARTITION_NAME,HIGH_VALUE LESS_THAN 值,TABLESPACE_NAME FROM USER_TAB_PARTITIONS tp join USER_PART_KEY_COLUMNS tpc on tp.table_name=tpc.name; /* 查看某用户登录的所有会话 */ SELECT ALTER SYSTEM KILL SESSION ||SID|| , ||SERIAL#|| , S.* FROM V$SESSION S WHERE USERNAME= 用户 AND STATUS!= KILLED – 用于解决 ORA-01940 无法删除当前连接的用户 /* 查看 command_type 值对应类型 */ SELECT * FROM v$sqlcommand; –【v$sqlarea.command_type、v$session.command 】/* 查看某 sql_id 绑定变量部分传的值 */ SELECT * FROM V$SQL_BIND_CAPTURE s where s.sql_id in (fdc8mt5xnjx2a) and CHILD_ADDRESS=2; /* 查找序列 last_number*/ SELECT * FROM USER_SEQUENCES S WHERE S.SEQUENCE_NAME= SEQ_CS_ONCE_CHAR_DET /*oracle 查看链接的 hostname 和 IP 分别是什么 */ select utl_inaddr.get_host_address(host_name), host_name from v$instance;— 用于当有 2 个服务器的数据库是同版本、同监听、同实例 /*oracle 查看 standby 库延迟时间 */ SELECT ((substr(value,2,2)*24 +substr(value,5,2))*60+substr(value,8,2 ))* 60+ substr(value,-2) TIME FROM gv$dataguard_stats where name = apply lag
/* 查看会话状态被置为 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：
SELECT SE.INST_ID, – 实例
SQ.SQL_TEXT, /*SQL 文本 */
SQ.SQL_FULLTEXT, /*SQL 全部文本 */
SE.SID, /* 会话的唯一标识，通常要对某个会话进行分析前，首先就需要获得该会话的 SID。*/
–SE.SERIAL#, /* 会话的序号 */
SQ.OPTIMIZER_COST AS COST_, /* COST 值 */
SE.LAST_CALL_ET CONTINUE_TIME, /* 执行时间 单位是秒（时间可能是单个 sql，也可能是整个功能）*/
CEIL((SYSDATE-SE.PREV_EXEC_START)*24*60*60) 相差秒数, /* 执行时间是整个功能时会用到这部分来判断单个 sql 执行时间 */
SE.PREV_EXEC_START, /*SQL execution start of the last executed SQL statement*/
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 语句 */
FROM GV$SESSION SE, /* 会话信息。每一个连接到 ORACLE 数据库的会话都能在该视图中对应一条记录，根据该视图中的信息可以查询该会话使用的用户，正在执行或者刚刚执行的 SQL 语句 */
/*[GV$SQLAREA 多节点]*/
GV$SQLAREA SQ /* 跟踪所有 SHARED POOL 中的共享 CURSOR 信息，包括 执行次数，逻辑读，物理读等 */
WHERE SE.SQL_HASH_VALUE = SQ.HASH_VALUE
AND SE.STATUS = ACTIVE
AND SE.SQL_ID = SQ.SQL_ID
AND SQ.INST_ID = SE.INST_ID
AND SE.USERNAME is not null;
– 过滤条件
–AND SE.USERNAME = FWSB – 用户名
–AND SQ.COMMAND_TYPE IN (2, 3, 5, 6, 189)
–AND SE.SID != USERENV (SID)/*rac 集群环境误用 */
–AND MACHINE != WORKGROUP\MHQ-PC ;
二、 每天执行慢的 SQL：
SELECT S.SQL_TEXT,
S.SQL_FULLTEXT,
S.SQL_ID,
ROUND(ELAPSED_TIME / 1000000 / (CASE
WHEN (EXECUTIONS = 0 OR NVL(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
FROM GV$SQLAREA S
WHERE ROUND (ELAPSED_TIME / 1000000 / ( CASE
WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN
1
ELSE
EXECUTIONS
END),
2) 5 –100 0000 微秒 =1S
— AND S.PARSING_SCHEMA_NAME = USER
AND TO_CHAR(S.LAST_LOAD_TIME, YYYY-MM-DD ) =
TO_CHAR( SYSDATE, YYYY-MM-DD )
AND S.COMMAND_TYPE IN (2, 3, 5 , 6, 189) /* 值对应类型 2：INSERT、3：SELECT、6：UPDATE、7：DELETE、189：MERGE 查询 V$SQLCOMMAND*/
AND MODULE = JDBC Thin Client
ORDER BY 执行时间 S DESC;
三、查看非绑定变量的 SQL：
SELECT V.SQL_ID,
V.SQL_FULLTEXT,
V.PARSING_SCHEMA_NAME,
FM.EXECUTIONS_COUNT,
FM.ELAPSED_TIME
FROM (SELECT L.FORCE_MATCHING_SIGNATURE MATHCES,
MAX(L.SQL_ID || L.CHILD_NUMBER) MAX_SQL_CHILD,
DENSE_RANK() OVER(ORDER BY COUNT(*) DESC ) RANKING,
ROUND(SUM (ROUND(ELAPSED_TIME / 1000000 / (CASE
WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN
1
ELSE
EXECUTIONS
END),
5))) ELAPSED_TIME,
SUM(L.EXECUTIONS) EXECUTIONS_COUNT
FROM V$SQL L
WHERE TO_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)
AND L.MODULE LIKE %JDBC% – 程序连接
AND L.FORCE_MATCHING_SIGNATURE 0
AND L.PARSING_SCHEMA_NAME = UPPER (USERNAME) – 用户
AND L.COMMAND_TYPE IN (2, 3, 5 , 6, 189) – 命令类型 2：INSERT、3：SELECT、6：UPDATE、7：DELETE、189：MERGE 查询 V$SQLCOMMAND
GROUP BY L.FORCE_MATCHING_SIGNATURE
HAVING COUNT (*) 5) FM,
V$SQL V
WHERE FM.MAX_SQL_CHILD = (V.SQL_ID || V.CHILD_NUMBER)
AND EXECUTIONS_COUNT = 50 – 执行次数超过 50 次先筛选改写，后续慢慢在范围小
ORDER BY FM.RANKING;
–V$SQL_BIND_CAPTURE – 记录包含变量得表.. 包括 ROWNUM :1 变量
四、查看 LOG 切换频率：
select b.SEQUENCE#,
b.FIRST_TIME,
a.SEQUENCE#,
a.FIRST_TIME,
round(((a.FIRST_TIME – b.FIRST_TIME) * 24 ) * 60, 2) 时间 min
from v$log_history a, v$log_history b
where a.SEQUENCE# = b.SEQUENCE# + 1
and b.THREAD# = 1
order by a.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 秒的数据库操作的状态
SELECT A.SID,
A.SERIAL#,
OPNAME,
TARGET, – 对象
TO_CHAR(START_TIME, YYYY-MM-DD HH24:MI:SS ) START_TIME, – 开始时间
(SOFAR / TOTALWORK) * 100 PROGRESS, – 进度比
TIME_REMAINING, – 估算剩余时间
ELAPSED_SECONDS, – 运行时间‘S’
A.SQL_ID
FROM V$SESSION_LONGOPS A
WHERE SID = ;
*** 其中 SID 和 SERIAL# 是与 V$SESSION 中的匹配的，
*** OPNAME：指长时间执行的操作名. 如： TABLE SCAN
*** 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：主要是并行查询一起使用。
六、查询外键字段在主键表中没有索引的
SELECT C.*,
C1.r_constraint_name,
c2.table_name,
T.NUM_ROWS,
create index idx_ || c.table_name || _ || column_name || on ||
c.table_name || ( || column_name ||
FROM USER_CONS_COLUMNS C
JOIN USER_CONSTRAINTS C1
ON C1.CONSTRAINT_NAME = C.CONSTRAINT_NAME
AND C1.CONSTRAINT_TYPE = R
AND (C.TABLE_NAME, C.COLUMN_NAME) NOT IN
( SELECT TABLE_NAME, COLUMN_NAME FROM USER_IND_COLUMNS I)
JOIN USER_TABLES T
ON T.TABLE_NAME = C.TABLE_NAME
join USER_CONSTRAINTS c2
on c1.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
WHEN NAME = PARSE COUNT (TOTAL) THEN
表示总的解析次数
WHEN NAME = PARSE COUNT (HARD) THEN
表示硬解析的次数
WHEN NAME = SESSION CURSOR CACHE COUNT THEN
表示缓存的游标个数
WHEN NAME = SESSION CURSOR CACHE HITS THEN
表示从缓存中找到游标的次数
WHEN NAME = OPENED CURSORS CURRENT THEN
表示 SESSION 中打开的游标数
END )
FROM V$SESSTAT A, V$STATNAME B
WHERE A.STATISTIC# = B.STATISTIC#
AND B.NAME IN ( PARSE COUNT (HARD) ,
PARSE COUNT (TOTAL) ,
SESSION CURSOR CACHE COUNT ,
SESSION CURSOR CACHE HITS ,
OPENED CURSORS CURRENT )
— AND SID=11
GROUP BY B.NAME
ORDER BY NAME;
–# 用于衡量 软硬解析 / 游标共享比.
八、查看未提交的事物的会话和锁的对象
SELECT DISTINCT S.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
FROM V$SESSION S,
V$TRANSACTION T,
V$SQL L,
V$LOCKED_OBJECT LO,
DBA_OBJECTS AO
WHERE S.TADDR = T.ADDR
AND S.PREV_SQL_ADDR = L.ADDRESS
AND AO.OBJECT_ID = LO.OBJECT_ID
AND LO.SESSION_ID = S.SID;
九、通过系统中 PID 去数据库中找执行的 SQL:
SELECT A.USERNAME, A.PROGRAM, B.SPID, C.SQL_TEXT, C.SQL_FULLTEXT
FROM V$SESSION A, V$PROCESS B, V$SQLAREA C
WHERE A.PADDR = B.ADDR
AND A.SQL_HASH_VALUE = C.HASH_VALUE
AND A.STATUS = ACTIVE
AND A.USERNAME NOT IN ( SYS , SYSTEM , SYSMAN)
AND A.SID != USERENV (SID)
AND B.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
WHEN CACHE_SIZE = 0 THEN
NOCACHE
ELSE
CACHE || CACHE_SIZE
END ) ||
FROM USER_SEQUENCES W
WHERE – 过滤掉登录用户存在的 SEQUENCE
NOT EXISTS ( SELECT 1
FROM USER_SEQUENCES@DB_SINOSOFT W1
WHERE W.SEQUENCE_NAME = W1.SEQUENCE_NAME);
–# 索引差异 结果的创建语句
SELECT CREATE || INDEX_TYPE || INDEX || INDEX_NAME || ON ||
TABLE_NAME || ( || LISTAGG(CNAME, , ) WITHIN GROUP (ORDER BY COLUMN_POSITION) ||
FROM (SELECT IC.INDEX_NAME,
IC.TABLE_NAME,
IC.COLUMN_NAME CNAME,
IC.COLUMN_POSITION,
COUNT(IC.INDEX_NAME) OVER ( PARTITION BY IC.INDEX_NAME, IC.TABLE_NAME) CON,
I.INDEX_TYPE
FROM USER_IND_COLUMNS@DB_SINOSOFT IC
JOIN USER_INDEXES@DB_SINOSOFT I
ON I.INDEX_NAME = IC.INDEX_NAME
WHERE
– 过滤掉登录用户存在的 INDEX
NOT EXISTS
( SELECT 1
FROM USER_IND_COLUMNS IC1
WHERE IC1.INDEX_OWNER = UPPER ( TO_USERNAME)
AND IC.INDEX_NAME = IC1.INDEX_NAME)
– 过滤掉主键，避免索引创建，在创建主键报错 对象已存在
AND IC.INDEX_NAME NOT IN
( SELECT C.CONSTRAINT_NAME FROM USER_CONSTRAINTS@DB_SINOSOFT C)
ORDER BY IC.INDEX_NAME, IC.COLUMN_POSITION)
GROUP BY INDEX_TYPE, CON, INDEX_NAME, TABLE_NAME;
十一、查看热点块的对象
SELECT A.HLADDR, A.FILE#, A.DBABLK, A.TCH, A.OBJ, B.OBJECT_NAME
FROM X$BH A, DBA_OBJECTS B
WHERE (A.OBJ = B.OBJECT_ID OR A.OBJ = B.DATA_OBJECT_ID)
AND A.HLADDR = 0000000054435000 –V$SESSION_WAIT.P1RAW
UNION
SELECT HLADDR, FILE#, DBABLK, TCH, OBJ, NULL
FROM X$BH
WHERE OBJ IN ( SELECT OBJ
FROM X$BH
WHERE HLADDR = 0000000054435000
MINUS
SELECT OBJECT_ID
FROM DBA_OBJECTS
MINUS
SELECT DATA_OBJECT_ID FROM DBA_OBJECTS)
AND HLADDR = 0000000054435000
ORDER BY 4;
十一、查看某用户表大小 / 总数情况
SELECT T.TABLE_NAME,
TC.COMMENTS,
T.NUM_ROWS,
ROUND (SUM (S.BYTES / 1024 / 1024 / 1024 )) GB
FROM USER_TABLES T
JOIN USER_SEGMENTS S
ON S.SEGMENT_NAME = T.TABLE_NAME
JOIN USER_TAB_COMMENTS TC
ON TC.TABLE_NAME = T.TABLE_NAME
GROUP BY T.TABLE_NAME, TC.COMMENTS, T.NUM_ROWS
ORDER BY NUM_ROWS DESC NULLS LAST ;
十二、 重新编译失效存储 / 包语句：
SELECT ALTER || (CASE
WHEN OBJECT_TYPE = PACKAGE BODY THEN
PACKAGE ELSE OBJECT_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 查看各表空间使用情况和最大最小块：
SELECT UPPER (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 (SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / 1024 / 1024 / 1024 , 2) TOTAL_BYTES,
ROUND (MAX (BYTES) / 1024 / 1024 / 1024 , 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
( SELECT DD.TABLESPACE_NAME,
ROUND (SUM (DD.BYTES) / 1024 / 1024 / 1024 , 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME;
十四、 Oracle 查看 TEMP 表空间使用情况 ：
SELECT F.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
FROM SYS.V_$TEMP_SPACE_HEADER F,
DBA_TEMP_FILES D,
SYS.V_$TEMP_EXTENT_POOL P
WHERE F.TABLESPACE_NAME(+) = D.TABLESPACE_NAME
AND F.FILE_ID(+) = D.FILE_ID
AND P.FILE_ID(+) = D.FILE_ID;
— 等同于
SELECT TABLESPACE_NAME,
TF.TABLESPACE_SIZE,
TF.FREE_SPACE,
TF.TABLESPACE_SIZE – TF.FREE_SPACE
FROM DBA_TEMP_FREE_SPACE TF;
十五、 Oracle 查看回滚进度情况用的几个 SQL：
SELECT DISTINCT KTUXESIZ FROM X$KTUXE WHERE KTUXESTA = ACTIVE ;
SELECT USED_UBLK FROM V$TRANSACTION;
SELECT KTUXEUSN, KTUXESLT
FROM X$KTUXE
WHERE /*KTUXECFL = DEAD AND*/
KTUXESTA = ACTIVE ;
SELECT * FROM V_$FAST_START_TRANSACTIONS;
SELECT USED_UBLK, T.USED_UREC FROM V$TRANSACTION T;
– 查询视图 V$FAST_START_TRANSACTIONS 中字段 UNDOBLOCKSDONE，UNDOBLOCKSTOTAL 估算 SMON 恢复进度
到此，关于“Oracle DBA 常用 sql 有哪些”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！
