取 Oracle Schema信息

获取表名
select TABLE_NAME from sys.user_tables;

获取视图名字
select VIEW_NAME from sys.user_views;

获取表别名
select a.synonym_name,b.owner,b.object_name
from sys.user_synonyms a, sys.all_objects b
where a.table_owner=b.owner
      and a.table_name=b.object_name
      and OBJECT_TYPE='TABLE';
     
获取视图别名     
select a.synonym_name,b.owner,b.object_name
from sys.all_synonyms a,sys.all_objects b
where a.owner='PMBF'
      and a.table_owner=b.owner
      and a.table_name=b.object_name
      and OBJECT_TYPE='VIEW';
     
获取字段信息
select COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE
from sys.all_tab_columns
where owner='DWH'
      and TABLE_NAME='EMPLOYEES'
order by column_id;
     
获取外键与主键信息
select b.owner,b.table_name,b.constraint_type,a.column_name
from  sys.all_constraints b,sys.all_cons_columns a
where b.constraint_type in ('R','P')
      and b.owner=a.owner
      and a.constraint_name=b.constraint_name
      and a.owner='PMBF';
     
获取存储过程
select OBJECT_NAME,PROCEDURE_NAME
from  sys.user_procedures;

获取参数列表
select OBJECT_NAME,PACKAGE_NAME,ARGUMENT_NAME,DATA_TYPE,IN_OUT
from sys.user_arguments
order by OBJECT_NAME,PACKAGE_NAME,sequence;