共計 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 小編會繼續努力為大家帶來更多實用的文章！
