问连接oracle元数据表
EN

Stack Overflow用户

提问于 2020-02-25 14:20:01

回答 2查看 59关注 0票数 0

我正在尝试连接元数据表，以查询表上的统计信息，如触发器计数、序列、包、过程、视图等，但我找不到连接表的键。下面是我的问题

SELECT * 
FROM user_constraints   a
    JOIN user_tables    b ON a.table_name = b.table_name
    JOIN user_triggers  c ON a.table_name = c.table_name;

我想执行一个大型查询，从所有主要的元数据表中提取所有数据，谢谢

oracle

oracle11g

metadata

oracle-sqldeveloper

sql

回答 2

Stack Overflow用户

发布于 2020-02-25 15:50:13

这里是一个开始，展示了约束、索引和触发器……

SELECT   t.owner,
         t.table_name,
         -- Constraints
        ( SELECT count(*) 
          FROM   all_constraints c
          WHERE  c.owner = t.owner
          AND    c.table_name = t.table_name 
          AND    c.constraint_type = 'P' ) primary_key_constraints,
        ( SELECT count(*) 
          FROM   all_constraints c
          WHERE  c.owner = t.owner
          AND    c.table_name = t.table_name 
          AND    c.constraint_type = 'R' ) foreign_key_constraints,
        ( SELECT count(*) 
          FROM   all_constraints c
          WHERE  c.owner = t.owner
          AND    c.table_name = t.table_name 
          AND    c.constraint_type = 'U' ) unique_constraints,
        ( SELECT count(*) 
          FROM   all_constraints c
          WHERE  c.owner = t.owner
          AND    c.table_name = t.table_name 
          AND    c.constraint_type = 'C' ) check_constraints,
        ( SELECT count(*) 
          FROM   all_constraints c
          WHERE  c.owner = t.owner
          AND    c.table_name = t.table_name 
          AND    c.constraint_type NOT IN ('P','R','U', 'C') ) other_constraints,
          -- Indexes
        ( SELECT count(*)
          FROM   all_indexes i
          WHERE  i.table_owner = t.owner
          AND    i.table_Name = t.table_name
          AND    i.uniqueness = 'UNIQUE' ) unique_indexes,
        ( SELECT count(*)
          FROM   all_indexes i
          WHERE  i.table_owner = t.owner
          AND    i.table_Name = t.table_name
          AND    i.uniqueness = 'NONUNIQUE' ) nonunique_indexes,
        -- Triggers
        ( SELECT count(*)
          FROM   all_triggers tr
          WHERE  tr.table_owner = t.owner
          AND    tr.table_name = t.table_Name ) triggers
FROM     all_tables t
WHERE    t.table_name = 'MY_FAVORITE_TABLE';

您可以使用要连接的(REFERENCED_OWNER，REFERENCED_NAME，REFERENCED_TYPE)列使用DBA_DEPENDENCIES添加包和视图等。但这只会计算直接引用该表的对象。例如，它不会计算通过同义词引用该表的包。例如，它不会计算引用了表的视图的包(尽管视图会被计算在内)。

票数 0

Stack Overflow用户

发布于 2020-02-25 19:18:31

试试这个：

-- This SQL will show all dependent objects for a table including FK references and index references
-- =================================================================================================
-- constraint references
select to_char(sysdate, 'DD-MON-YYYY') date_evaluated
, cons.owner ||'.'|| cons.table_name || ' (TABLE)' as object
, 'FK Reference to' relationship
, col.owner ||'.'|| col.table_name || ' (TABLE)' as referenced_object
, cons.owner owner
, cons.table_name name
, 'TABLE' type
, col.owner referenced_owner
, col.table_name referenced_name
, 'TABLE' referenced_type
from dba_cons_columns      col
,    dba_constraints       cons
where 1=1
  and cons.owner = nvl(:object_owner, cons.owner)
  and cons.table_name = nvl(:object_name, cons.table_name)
  and cons.r_owner = col.owner
  and cons.r_constraint_name = col.constraint_name
union
-- object references from dba_dependencies
select /*+ MATERIALIZE */ to_char(sysdate, 'DD-MON-YYYY') date_evaluated
, referenced_owner || '.' || referenced_name || '(' || referenced_type || ')' as object
, 'Referenced in' relationship
, owner || '.' || name || '(' || type || ')' as referenced_object
, referenced_owner
, referenced_name
, referenced_type
, owner
, name
, type
from dba_dependencies
where 1=1
  and name not like 'BIN$%'
  and referenced_name not like 'BIN$%'
  and type in ('TABLE', 'MATERIALIZED VIEW', 'VIEW', 'PACKAGE', 'TRIGGER', 'INDEX')
  and referenced_type in ('TABLE', 'MATERIALIZED VIEW', 'VIEW')
  and referenced_owner like nvl(:object_owner, referenced_owner)
  and referenced_name like nvl(:object_name, referenced_name)
union
-- object references from dba_indexes
select /*+ MATERIALIZE */ to_char(sysdate, 'DD-MON-YYYY') date_evaluated
, table_owner || '.' || table_name || '(TABLE)' as object
, 'Referenced in' relationship
, owner || '.' || index_name || '(INDEX)' as referenced_object
, table_owner referenced_owner
, table_name referenced_name
, '(TABLE)' referenced_type
, owner owner
, index_name name
, '(INDEX)' type
from dba_indexes
where 1=1
  and table_owner like nvl(:object_owner, table_owner)
  and table_name like nvl(:object_name, table_name)
;