溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹“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 ~]$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有哪些”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
