連結：http://blog.itpub.net/28602568/viewspace-1396081/

標題： 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)
/oracle 檢視standby庫延遲時間/	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, 'hh24'),'00',1,0)) "h0",

SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1",

SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2",

SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3",

SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4",

SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5",

SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6",

SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7",

SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8",

SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9",

SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10",

SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11",

SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12",

SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13",

SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14",

SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15",

SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16",

SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17",

SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18",

SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19",

SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20",

SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21",

SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22",

SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23"

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恢復進度

【源於本人筆記】若有書寫錯誤，表達錯誤，請指正...

此條目發表在 SQL、SQL最佳化篇分類目錄。將固定連線加入收藏夾。

Oracle DBA常用sql分享

相關文章