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

WHERETSU.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' --实际要替换的参数

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);

持续更新中...

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

如果您们在尝试的过程中遇到什么问题或者我的代码有错误的地方，请给予指正，非常感谢！

联系方式：david.louis.tian@outlook.com

版权＠：转载请标明出处！