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#';
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;
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;
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]
版权@:转载请标明出处!郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。