oracle 中快速批量获取Meta data

set serveroutput on

set linesize  1000

set feedback off

set long 999999999

set pagesize  3000

--SELECT DBMS_METADATA.GET_DDL(O.OBJECT_TYPE, O.object_name,O.OWNER)

FROM DBA_OBJECTS O where O.OBJECT_TYPE IN ('INDEX','PROCEDURE','FUNCTION') and owner = 'ABC' and object_name in ('CCC');

--获取 MetaData

## 去掉tablespace、STORAGE等信息，获取干净的 Create语句（session级别）

execute  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);

execute  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',FALSE);

execute  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',FALSE);

## 获取单表的MetaData

spool D:/create_tab.sql

SELECT DBMS_METADATA.GET_DDL('TABLE','TABLE_NAME','SCHEMA') from dual;

spool off

set feedback on

#######

## 恢复tablespace、STORAGE等信息（session级别）

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');

## 批量获取表的MetaData (某schema下所有表)

方法一：

spool /mnt/backup/create_1000tabs.sql

SELECT DBMS_METADATA.GET_DDL('TABLE',tab.table_name,tab.owner) FROM dba_tables tab where owner='CARFINANCE';

spool off

方法二：

spool /mnt/backup/create_1000tabs.sql

SELECT DBMS_METADATA.GET_DDL(o.object_type, O.object_name,o.owner) FROM DBA_OBJECTS O where o.owner='CARFINANCE' and o.object_type='TABLE';

spool off

方法三（不用）：

## 拼接批量sql

select 'SELECT DBMS_METADATA.GET_DDL('||'''TABLE'','||''''||table_name||''','||'''CARFINANCE'''||') FROM dual;' from dba_tables where owner='CARFINANCE';

--获取索引的MetaData

set pages 300 lines 300

col index_owner for a10

col index_name for a20

col table_name for a20

col column_name  for a20

select index_owner,index_name,table_name,column_name from dba_ind_columns where table_owner='SAPSR3' and table_name='BKKIT_OLD'；

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

SELECT DBMS_METADATA.GET_DDL('INDEX','BKKIT~0','SAPSR3') FROM DUAL;

######

--获取视图的MetaData

select * from dba_dependencies where TYPE='VIEW' and  REFERENCED_NAME='BK';

SELECT dbms_metadata.get_ddl('VIEW', 'V_EMP') FROM DUAL;

--获取主键的MetaData

select a.constraint_name, a.column_name

from dba_cons_columns a, dba_constraints b

where a.constraint_name = b.constraint_name

and b.constraint_type = 'P'

and a.table_name = 'BK_OLD';

select owner,constraint_name,CONSTRAINT_TYPE,table_name from dba_constraints where owner='SAP' and table_name='BK_OLD';

----select owner,constraint_name,CONSTRAINT_TYPE,table_name from dba_constraints where owner='SAP' and table_name='BK';

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','PK_DEPT','SAP')FROM DUAL;

查看外键

ALTER TABLE EMP ADD  constraints EMP_DEPTNO FOREIGN KEY  (deptno)  REFERENCES dept(deptno);

--表空间的MetaData

SELECT DBMS_METADATA.GET_DDL('TABLESPACE','SYSAUX') FROM DUAL;

###

----获取某个用户的表 MetaData

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)  FROM USER_ALL_TABLES u   WHERE u.nested='NO'   AND (u.iot_type is null or u.iot_type='IOT');

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)  FROM DBA_ALL_TABLES u   WHERE u.owner='SAP' and u.nested='NO'   AND (u.iot_type is null or u.iot_type='IOT');

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');

---获取comments

select  'comment on column '||table_name||'.'||colume_name||'  is '||''''||comments||''';' from  all_col_comments where owner='SAP' and table_name='BK';

select  'comment on table '||table_name||'  is '||''''||comments||''';' from  all_col_comments where owner='SAP' and table_name='BK';

###

-- 获取trigger

set serveroutput on

set linesize  1000

set feedback off

set long 999999999

set pagesize  3000

SELECT DBMS_METADATA.GET_DDL(O.OBJECT_TYPE, O.object_name,O.OWNER)

FROM DBA_OBJECTS O where O.OBJECT_TYPE ='TRIGGER' and owner='BMP' and object_name='TRIG_INFO';

SELECT DBMS_METADATA.GET_DDL(O.OBJECT_TYPE, O.object_name,O.OWNER)

FROM DBA_OBJECTS O where O.OBJECT_TYPE ='TRIGGER' and owner='BMP' and object_name='TRIG_ENTERPRISE';