oracle元数据

1 从objects开始

--都是dba/all/user
select * from dba_objects;

select * from all_objects;      --和dba_objects结构一致，数据少一些

select * from user_objects;     --比dba_objects少一个owner字段

 --dba_object的结构
 owner,  object_name,  object_id,  object_type,  create_time,    last_ddl_time,  status, ...

--distinct owner
sys,  system,  public,  mdsys...

--discinct object_type
table,  trigger,  view,  index,  procedure,  function,  ...40个

2 再来看object_type

--和上面object_type是对应着的，也是dba/all/user三种
select * from user_tables;
--table_name , ..., status, ...

select * from user_triggers;
--trigger_name, trigger_type, ..., table_name, column_name, ...

select * from user_users;
--username, user_id, account_status, expiry_date, created, ....

select * from user_views;
--view_name, text_length, text, ...

select * from user_indexes;
--index_name, index_type, table_owner, table_name, ..., uniqueness, ...

select * from user_tab_columns;
--table_name, column_name, data_type, data_length,...

3 v$ 和gv$打头的视图(view)

参考：“天高任鸟飞”的博客： oracle动态视图v$,v_$,gv$,gv_$与x$之间的关系
原文地址：http://blog.itpub.net/203348/viewspace-1290553/

--经常用的view
select * from v$version;
select * from v$database;
select * from v$instance;
select * from v$session;

--其实都在v$fixed_table
select * from v$fixed_table where name = 'V$VERSION';

--结构：       name, object_id, type, table_num
--distinct type:    table, view

--查看v$fixed_table和gv$_fixed_table的区别
select * from v$fixed_view_definition where view_name = 'V$FIXED_TABLE';

 select * from v$fixed_view_definition where view_name='GV$FIXED_TABLE';

--v$fixed_table定义，当前instance的视图
select  NAME , OBJECT_ID , TYPE , TABLE_NUM from GV$FIXED_TABLE where inst_id = USERENV('Instance');

--gv$_fixed_table定义，gv是全局视图的意思
select inst_id,kqftanam, kqftaobj, 'TABLE', indx from x$kqfta union all select inst_id,kqfvinam, kqfviobj, 'VIEW', 65537 from x$kqfvi union all select inst_id,kqfdtnam, kqfdtobj, 'TABLE', 65537 from x$kqfdt;

--RAC可有多个实例同时装载并打开一个数据库，查看RAC的instance
select distinct inst_id from gv$session;   --1,2,3,4

--$ORACLE_HOME/rdbms/admin存放着系统管理脚本，cdfixed.sql中找到
--v$fixed_table和v_$fixed_table是同义词
create or replace view v_$fixed_table as select * from v$fixed_table;
create or replace public synonym v$fixed_table for v_$fixed_table;
grant select on v_$fixed_table to select_catalog_role;

--gv_$fixed_table和gv$fixed_table是同义词
create or replace view gv_$fixed_table as select * from gv$fixed_table;
create or replace public synonym gv$fixed_table for gv_$fixed_table;
grant select on gv_$fixed_table to select_catalog_role;