oracle 第06章 数据字典
2016-04-20(更新)
2015-10-13
目录
01、用户
02、权限
03、数据文件
04、表空间
05、表
06、分区表
07、列
08、表约束
09、列约束
10、索引
11、分区索引
12、视图
13、序列号
14、同义词
15、库连接
16、过程、函数、包
17、触发器
18、计划任务
01、用户
SELECT username, user_id, account_status, created FROM dba_users ORDER BY account_status DESC;
02、权限
SELECT b.username, b.user_id, a.privilege, a.admin_option FROM dba_sys_privs a, (SELECT username, user_id, account_status, created FROM dba_users WHERE account_status = 'OPEN' ORDER BY account_status DESC) b WHERE a.grantee = b.username ORDER BY grantee;
03、数据文件
SELECT file_id, file_name, tablespace_name, bytes, user_bytes, maxbytes, autoextensible, status, online_status FROM dba_data_files ORDER BY tablespace_name, file_name ASC;
04、表空间
SELECT tablespace_name, status, contents, logging FROM dba_tablespaces ORDER BY tablespace_name ASC; --表空间使用率 SELECT SYSDATE, a.tablespace_name, ROUND(total / 1024 / 1024 / 1024, 0) || 'G' total, ROUND(used / 1024 / 1024 / 1024, 0) || 'G' used, ROUND((total - used) / 1024 / 1024 / 1024, 0) || 'G' free, ROUND(used / total, 2) * 100 || '%' used_ratio FROM (SELECT tablespace_name, SUM(bytes) used FROM dba_segments GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(DECODE(autoextensible, 'YES', maxbytes, 'NO', bytes)) total FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name ORDER BY 2;
05、表
SELECT b.username, a.table_name, a.tablespace_name, a.num_rows, a.status, a.logging FROM dba_tables a, (SELECT username, user_id, account_status, created FROM dba_users WHERE account_status = 'OPEN' AND username NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP', 'CTXSYS') ORDER BY account_status DESC) b WHERE a.owner = b.username AND a.tablespace_name IS NOT NULL AND a.num_rows IS NOT NULL ORDER BY a.owner, a.tablespace_name, a.table_name;
06、分区表
SELECT b.username, a.table_name, a.tablespace_name, a.num_rows, a.partition_name, a.partition_position FROM dba_tab_partitions a, (SELECT username, user_id, account_status, created FROM dba_users WHERE account_status = 'OPEN' AND username NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP', 'CTXSYS') ORDER BY account_status DESC) b WHERE a.table_owner = b.username ORDER BY b.username, a.table_name,a.partition_position;
07、列
SELECT b.username, a.table_name, a.column_id, a.column_name, a.data_type, a.data_length, a.nullable FROM dba_tab_columns a, (SELECT username, user_id, account_status, created FROM dba_users WHERE account_status = 'OPEN' AND username NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP', 'CTXSYS') ORDER BY account_status DESC) b WHERE a.owner = b.username ORDER BY b.username, a.table_name, a.column_id;
08、表约束
SELECT b.username, a.table_name, a.constraint_name, a.constraint_type, a.status, a.last_change FROM dba_constraints a, (SELECT username, user_id, account_status, created FROM dba_users WHERE account_status = 'OPEN' AND username NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP', 'CTXSYS') ORDER BY account_status DESC) b WHERE a.owner = b.username ORDER BY b.username, a.table_name;
09、列约束
SELECT b.username, a.table_name, a.column_name, a.constraint_name FROM dba_cons_columns a, (SELECT username, user_id, account_status, created FROM dba_users WHERE account_status = 'OPEN' AND username NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP', 'CTXSYS') ORDER BY account_status DESC) b WHERE a.owner = b.username ORDER BY b.username, a.table_name;
10、索引
SELECT c.* FROM (SELECT a.owner username, a.table_name, a.num_rows, b.column_position column_id, b.column_name, a.index_name, a.status, a.index_type, a.uniqueness FROM dba_indexes a, dba_ind_columns b WHERE a.index_name = b.index_name AND a.table_name = b.table_name) c, (SELECT username, user_id, account_status, created FROM dba_users WHERE account_status = 'OPEN' AND username NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP', 'CTXSYS') ORDER BY account_status DESC) d WHERE c.username = d.username ORDER BY d.username, c.table_name, c.column_id;
11、分区索引
SELECT d.* FROM (SELECT i.index_owner, c.table_name, c.column_name, i.index_name, i.status, i.tablespace_name, i.partition_name, i.partition_position FROM dba_ind_partitions i, (SELECT a.owner, a.table_name, b.column_name, b.index_name FROM dba_indexes a, dba_ind_columns b WHERE a.table_name = b.table_name AND a.index_name = b.index_name) c WHERE i.index_name = c.index_name) d, (SELECT username, user_id, account_status, created FROM dba_users WHERE account_status = 'OPEN' AND username NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP', 'CTXSYS') ORDER BY account_status DESC) e WHERE d.index_owner = e.username ORDER BY index_owner, table_name, index_name, partition_position;
12、视图
SELECT b.username, a.view_name, a.text_length, a.text FROM dba_views a, (SELECT username, user_id, account_status, created FROM dba_users WHERE account_status = 'OPEN' AND username NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP', 'CTXSYS') ORDER BY account_status DESC) b WHERE a.owner = b.username ORDER BY b.username, a.view_name;
13、序列号
SELECT b.username, a.sequence_name, a.min_value, a.max_value, a.increment_by, a.last_number FROM dba_sequences a, (SELECT username, user_id, account_status, created FROM dba_users WHERE account_status = 'OPEN' AND username NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP', 'CTXSYS') ORDER BY account_status DESC) b WHERE a.sequence_owner = b.username ORDER BY b.username, a.sequence_name;
14、同义词
SELECT a.* FROM dba_synonyms a, (SELECT username, user_id, account_status, created FROM dba_users WHERE account_status = 'OPEN' AND username NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP', 'CTXSYS') ORDER BY account_status DESC) b WHERE a.owner = b.username ORDER BY b.username, a.synonym_name
15、库连接
SELECT * FROM dba_db_links ORDER BY owner, db_link, username;
16、过程、函数、包
SELECT b.username, a.object_type, a.object_name, a.procedure_name, a.object_id, a.subprogram_id FROM dba_procedures a, (SELECT username, user_id, account_status, created FROM dba_users WHERE account_status = 'OPEN' AND username NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP', 'CTXSYS') ORDER BY account_status DESC) b WHERE a.owner = b.username ORDER BY b.username, a.object_type, a.object_name, a.procedure_name;
17、触发器
SELECT b.username, a.trigger_name, a.trigger_type, a.triggering_event, a.table_name, a.column_name, a.status, a.trigger_body FROM dba_triggers a, (SELECT username, user_id, account_status, created FROM dba_users WHERE account_status = 'OPEN' AND username NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP', 'CTXSYS') ORDER BY account_status DESC) b WHERE a.owner = b.username;
18、计划任务
SELECT job, log_user username, last_date, this_date, next_date, total_time, broken, interval, failures, what FROM dba_jobs;
参考资料
[1] Jason Price.精通Oracle Database 12c SQL&PLSQL编程(第3版).[M].北京:清华大学出版社,2014
[2] 林树泽.Oracle 11g R2 DBA操作指南[M].北京:清华大学出版社,2013
[3] oracle数据字典
[4] oracle数据字典详解
[6] oracle 数据字典