Oracle數據庫常用經典查詢

Oracle數據庫常用經典查詢

本文收集了常用的數據庫查詢，需要數據庫管理員權限：

1. 查詢臨時表空間使用情況

SELECT TABLESPACE_NAME, 
    TABLESPACE_SIZE / 1024 / 1024 TABLESPACE_SIZE_MB,
    ALLOCATED_SPACE / 1024 / 1024 ALLOCATED_SPACE_MB,
    FREE_SPACE / 1024 / 1024 FREE_SPACE_MB,
    TO_CHAR((1 - FREE_SPACE / TABLESPACE_SIZE) * 100, '900.00') PERCENTAGE_USED
FROM DBA_TEMP_FREE_SPACE
;

2. 查詢使用TEMP表空間的語句的SID, SERIAL#:

SELECT   B.TABLESPACE, B.SEGFILE#, B.SEGBLK#, ROUND (  (  ( B.BLOCKS * P.VALUE ) / 1024 / 1024 / 1024 ), 2 ) SIZE_GB
       , A.SID, A.SERIAL#, A.USERNAME, A.OSUSER, A.PROGRAM, A.STATUS
    FROM V$SESSION A, V$SORT_USAGE B, V$PROCESS C, V$PARAMETER P
   WHERE P.NAME = 'db_block_size'
     AND A.SADDR = B.SESSION_ADDR
     AND A.PADDR = C.ADDR
ORDER BY SIZE_GB DESC;

3. 根據上面查詢 出的SERIAL#,查詢出對應的語句：

SELECT S.SID, S.SERIAL#, T.SQL_FULLTEXT,T.SQL_ID,S.SQL_HASH_VALUE,T.HASH_VALUE 
FROM V$SESSION S, V$SQL T 
WHERE S.SQL_ADDRESS = T.ADDRESS 
AND S.SQL_HASH_VALUE = T.HASH_VALUE
AND S.SERIAL# = '&SERIAL#';

4. 查詢表空間的使用情況：

SELECT 	TSU.TABLESPACE_NAME, CEIL(TSU.USED_GB) "used GB" --15467
,	DECODE(CEIL(TSF.FREE_GB), NULL,0,CEIL(TSF.FREE_GB)) "free GB"
,	DECODE(100 - CEIL(TSF.FREE_GB/TSU.USED_GB*100), NULL, 100
,   100 - CEIL(TSF.FREE_GB/TSU.USED_GB*100)) "% used"
FROM	(SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024/1024 USED_GB 
          FROM DBA_DATA_FILES 
          GROUP BY TABLESPACE_NAME 
         UNION ALL
	 SELECT TABLESPACE_NAME || '  **TEMP**',SUM(BYTES)/1024/1024/1024 USED_GB 
           FROM DBA_TEMP_FILES 
           GROUP BY TABLESPACE_NAME
	) TSU
   ,	(SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024/1024 FREE_GB 
           FROM DBA_FREE_SPACE 
           GROUP BY TABLESPACE_NAME
         ) TSF
WHERE	TSU.TABLESPACE_NAME = TSF.TABLESPACE_NAME (+)
AND ( TSF.TABLESPACE_NAME IN ('&tablespace_name1', '&tablespace_name1') );

5. 查詢運行時間長的Session:

SELECT *
FROM
  (SELECT *
    FROM
    (SELECT VP.START_TIME "Start Time",ROUND((VP.LAST_UPDATE_TIME - VP.START_TIME)*60*24*60) ELAPSED,VP.MESSAGE "Message",
            DECODE (VP.TOTALWORK, 0, 0, ROUND (100 * VP.SOFAR / VP.TOTALWORK, 2)) "Percent", VP.TIME_REMAINING||' sec' REMAINING
     FROM V$SESSION_LONGOPS VP
     WHERE VP.SID = &session_id --實際要替換的參數
     )
  ORDER BY 1 DESC
  ); 

6. 查詢數據庫表的大小

SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 / 1024 GB
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = '&table_name' <span style="font-family: Arial, Helvetica, sans-serif;">--實際要替換的參數</span>
GROUP BY SEGMENT_NAME; 

7. 查詢數據庫表的依賴：

SELECT * FROM DBA_DEPENDENCIES WHERE REFERENCED_NAME='&object_name';--輸入對象名稱

8. 輸出創建表的語句

DECLARE
V_STR LONG;
CURSOR CUR 
IS
SELECT COLUMN_NAME||' '||DECODE(DATA_TYPE,  'NUMBER', DECODE(NULLIF(DATA_LENGTH, 22), NULL, DATA_TYPE, DATA_TYPE||'('||DATA_LENGTH||')'),
                                                                              'DATE', DATA_TYPE,
                                                                              DATA_TYPE||'('||DATA_LENGTH||')'
                                                                              )||','  COL
FROM DBA_TAB_COLS 
WHERE TABLE_NAME='&SOURCE_TABLE_NAME'
AND OWNER='&SOURCE_TABLE_OWNER'
ORDER BY COLUMN_NAME ASC;
BEGIN
       FOR REC IN CUR LOOP
       V_STR:=V_STR||REC.COL||CHR(10);
       END LOOP;
       V_STR:='CREATE TABLE'||' &TABLE_NAME'||' '||'('||CHR(10)||V_STR||CHR(10)||');';
       DBMS_OUTPUT.PUT_LINE(V_STR);
END;

9. 查詢表結構

SELECT DBMS_METADATA.GET_DDL('TABLE','&table_name','&schema') FROM DUAL;--替換表名與表所在的schema

10. 查詢數據庫的字符集

SELECT * FROM NLS_DATABASE_PARAMETERS;

11. 查詢 數據庫用到的database link:

SELECT * FROM DBA_DB_LINKS WHERE DB_LINK='&db_link';

12. 查詢數據表上的索引

select * from dba_indexes where owner='&owner' and table_name='&table_name';

13. 查詢數據表用到的索引列

select * from dba_ind_columns where index_owner='&owner' and table_name='&table_name';

14. 查詢數據表裡的存儲過程/函數/自定義類型/觸發器/包

select * from dba_source 
where onwer='&owner'
and type='&type';

15. 查詢約束

select * from dba_constraints;
select * from all_constraints;
select * from user_constraints;

16. 查詢同義詞

select * from dba_synonyms;
select * from all_synonyms;
select * from user_synonyms;

17. 查詢出發器

select * from dba_triggers;
select * from all_triggers;
select * from user_triggers;

18. 查詢視圖

select * from dba_views;
select * from all_views;
select * from user_views;

19. 查詢序列

select * from dba_sequences;
select * from all_sequences;
select * from user_sequences;

20. 查詢物化視圖

select * from DBA_MVIEWS;
select * from ALL_MVIEWS;
select * from USER_MVIEWS;

21. 查詢數據庫對象

select * from DBA_OBJECTS;
select * from ALL_OBJECTS;
select * from USER_OBJECTS;

22. 查詢存儲過程

select * from DBA_PROCEDURES;
select * from ALL_PROCEDURES;
select * from USER_PROCEDURES;

23. 查詢數據庫表

select * from DBA_TABLES;
select * from ALL_TABLES;
select * from USER_TABLES;

24. 查詢數據庫表的列

select * from DBA_TAB_COLUMNS;
select * from ALL_TAB_COLUMNS;
select * from USER_TAB_COLUMNS;
select * from DBA_TAB_COLS;
select * from ALL_TAB_COLS;
select * from USER_TAB_COLS;

25. 查詢數據庫表列的備注

select * from DBA_TAB_COMMENTS;
select * from ALL_TAB_COMMENTS;
select * from USER_TAB_COMMENTS;

26. 查看查詢的執行計劃

explain plan for select * from dict;
select * from table(dbms_xplan.display);

持續更新中...

----------------------------------------------------------------------------------------------------------------------------------------------------

如果您們在嘗試的過程中遇到什麼問題或者我的代碼有錯誤的地方，請給予指正，非常感謝！

聯系方式：[email protected]

版權＠：轉載請標明出處！