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数据字典详解

[5] Oracle中的数据字典技术及常用数据字典总结

[6] oracle 数据字典