导出当前用户下所有外键、主键、索引、sequence的DDL

在数据转换的过程中，我使用create table as ...等方式，批量将A的数据表创建到B下后，
主键、外键、索引是需要手工建的，一个一个嫌麻烦，故写下了以下脚本。


可使A用户下的主键、外键、索引都产生出来，然后到B用户下创建。


--------------------------------------------------------------------------------
10g及以上


--导出索引
SELECT 'CREATE ' || DECODE(B.INDEX_TYPE, 'NORMAL', '', B.INDEX_TYPE) ||
       DECODE(B.UNIQUENESS, 'NONUNIQUE', '', B.UNIQUENESS) || ' INDEX ' ||
       B.INDEX_NAME || ' ON ' || B.TABLE_NAME || ' (' ||
       (SELECT TO_CHAR(WMSYS.WM_CONCAT(A.COLUMN_NAME))
          FROM USER_IND_COLUMNS A
         WHERE A.INDEX_NAME = B.INDEX_NAME) || ');'
  FROM USER_INDEXES B
  WHERE B.TABLE_NAME IN
       ('IB02', 'IB03', 'IC02', 'IC05', 'IC06', 'IC10', 'IC11', 'IC13',
        'IC14', 'IC16', 'IC18', 'IC20', 'IC21', 'IC22', 'IC26', 'IC27',
        'IC28', 'IC40', 'IC41', 'IC43', 'IC44', 'IC45', 'IC51', 'IC64',
        'IC68', 'IC93', 'ICA8', 'ICD1', 'ICD2', 'ICD3', 'ICE1', 'ICE2',
        'ICF1', 'ICF2', 'ID02', 'LB03', 'LC01', 'LC02', 'LC04', 'LC06',
        'LC09', 'LC12', 'LC13', 'LC14', 'LC16', 'LC17', 'LC20', 'LC21',
        'LC22', 'LC25', 'LC26', 'LC27', 'LC28', 'LC30', 'LC31', 'LC32',
        'LC33', 'LC40', 'LC41', 'LC47', 'LC48', 'LC58', 'MB03', 'MB06',
        'MC01', 'MC01', 'MC02', 'MC05');


--导出主键
SELECT 'ALTER TABLE ' || B.TABLE_NAME || ' ADD CONSTRAINT ' ||
       B.CONSTRAINT_NAME || ' PRIMARY KEY (' ||
       (SELECT TO_CHAR(WMSYS.WM_CONCAT(A.COLUMN_NAME))
          FROM USER_CONS_COLUMNS A
         WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME) || ');'
  FROM USER_CONSTRAINTS B
 WHERE B.CONSTRAINT_TYPE = 'P' 
 AND B.TABLE_NAME IN
       ('IB02', 'IB03', 'IC02', 'IC05', 'IC06', 'IC10', 'IC11', 'IC13',
        'IC14', 'IC16', 'IC18', 'IC20', 'IC21', 'IC22', 'IC26', 'IC27',
        'IC28', 'IC40', 'IC41', 'IC43', 'IC44', 'IC45', 'IC51', 'IC64',
        'IC68', 'IC93', 'ICA8', 'ICD1', 'ICD2', 'ICD3', 'ICE1', 'ICE2',
        'ICF1', 'ICF2', 'ID02', 'LB03', 'LC01', 'LC02', 'LC04', 'LC06',
        'LC09', 'LC12', 'LC13', 'LC14', 'LC16', 'LC17', 'LC20', 'LC21',
        'LC22', 'LC25', 'LC26', 'LC27', 'LC28', 'LC30', 'LC31', 'LC32',
        'LC33', 'LC40', 'LC41', 'LC47', 'LC48', 'LC58', 'MB03', 'MB06',
        'MC01', 'MC01', 'MC02', 'MC05');




 --导出外键
 SELECT 'ALTER TABLE ' || B.TABLE_NAME || ' ADD CONSTRAINT ' ||
        B.CONSTRAINT_NAME || ' FOREIGN KEY (' ||
        (SELECT TO_CHAR(WMSYS.WM_CONCAT(A.COLUMN_NAME))
           FROM USER_CONS_COLUMNS A
          WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME) || ') REFERENCES ' ||
        (SELECT B1.table_name FROM USER_CONSTRAINTS B1
          WHERE B1.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME) || '(' ||
        (SELECT TO_CHAR(WMSYS.WM_CONCAT(A.COLUMN_NAME))
           FROM USER_CONS_COLUMNS A
          WHERE A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME) || ');'
   FROM USER_CONSTRAINTS B
  WHERE B.CONSTRAINT_TYPE = 'R'
  AND B.TABLE_NAME IN
       ('IB02', 'IB03', 'IC02', 'IC05', 'IC06', 'IC10', 'IC11', 'IC13',
        'IC14', 'IC16', 'IC18', 'IC20', 'IC21', 'IC22', 'IC26', 'IC27',
        'IC28', 'IC40', 'IC41', 'IC43', 'IC44', 'IC45', 'IC51', 'IC64',
        'IC68', 'IC93', 'ICA8', 'ICD1', 'ICD2', 'ICD3', 'ICE1', 'ICE2',
        'ICF1', 'ICF2', 'ID02', 'LB03', 'LC01', 'LC02', 'LC04', 'LC06',
        'LC09', 'LC12', 'LC13', 'LC14', 'LC16', 'LC17', 'LC20', 'LC21',
        'LC22', 'LC25', 'LC26', 'LC27', 'LC28', 'LC30', 'LC31', 'LC32',
        'LC33', 'LC40', 'LC41', 'LC47', 'LC48', 'LC58', 'MB03', 'MB06',
        'MC01', 'MC01', 'MC02', 'MC05');
--------------------------------------------------------------------------------


9i、10g都可以


SELECT 'drop SEQUENCE ' || T.SEQUENCE_NAME || ';'
  FROM USER_SEQUENCES T
 WHERE T.SEQUENCE_NAME IN
       ('SEQ_A_AAZ257', 'SEQ_A_BAZ601', 'SEQ_A_BAE088', 'SEQ_A_AAZ033',
        'SEQ_A_BAZ605', 'SEQ_A_BAZ603', 'SEQ_A_BAZ001', 'SEQ_A_BAZ002',
        'SEQ_A_BAZ601', 'SEQ_A_BAZ612', 'SEQ_A_BAZ613', 'SEQ_A_BAZ606',
        'SEQ_A_BAZ609', 'SEQ_A_BAC402', 'SEQ_A_BZA203', 'SEQ_A_BZA668',
        'SEQ_A_BAZ607');


--导出sequence
SELECT 'CREATE SEQUENCE ' || SEQUENCE_NAME || ' MINVALUE ' || MIN_VALUE ||
       ' MAXVALUE ' || MAX_VALUE || ' START WITH ' || LAST_NUMBER ||
       ' INCREMENT BY ' || INCREMENT_BY || (CASE
         WHEN CACHE_SIZE = 0 THEN
          ' NOCACHE'
         ELSE
          ' CACHE ' || CACHE_SIZE
       END) || ';'
  FROM USER_SEQUENCES;




--------------------------------------------------------------------------------
9i及以下


CREATE OR REPLACE FUNCTION ZJW_C2L(IND IN VARCHAR2) RETURN VARCHAR2 IS
  RET_MSG VARCHAR2(1000);
BEGIN
  SELECT SUBSTR(MAX(SYS_CONNECT_BY_PATH(COLUMN_NAME, ',')), 2) AS COLUMN_NAME
    INTO RET_MSG
    FROM (SELECT C.COLUMN_NAME, ROWNUM AS RN
            FROM USER_IND_COLUMNS C
           WHERE C.INDEX_NAME = IND
           ORDER BY C.COLUMN_POSITION)
   START WITH RN = 1
  CONNECT BY RN - 1 = PRIOR RN;
  RETURN RET_MSG;
END ZJW_C2L;


--导出索引
SELECT 'CREATE ' || DECODE(T.INDEX_TYPE, 'NORMAL', '', T.INDEX_TYPE) ||
       DECODE(T.UNIQUENESS, 'NONUNIQUE', '', T.UNIQUENESS) || ' INDEX ' ||
       T.INDEX_NAME || ' ON ' || T.TABLE_NAME || ' (' ||
       (ZJW_C2L(T.INDEX_NAME)) || ');'
  FROM USER_INDEXES T
 WHERE T.TABLE_NAME IN
       ('IB02', 'IB03', 'IC02', 'IC05', 'IC06', 'IC10', 'IC11', 'IC13',
        'IC14', 'IC16', 'IC18', 'IC20', 'IC21', 'IC22', 'IC26', 'IC27',
        'IC28', 'IC40', 'IC41', 'IC43', 'IC44', 'IC45', 'IC51', 'IC64',
        'IC68', 'IC93', 'ICA8', 'ICD1', 'ICD2', 'ICD3', 'ICE1', 'ICE2',
        'ICF1', 'ICF2', 'ID02', 'LB03', 'LC01', 'LC02', 'LC04', 'LC06',
        'LC09', 'LC12', 'LC13', 'LC14', 'LC16', 'LC17', 'LC20', 'LC21',
        'LC22', 'LC25', 'LC26', 'LC27', 'LC28', 'LC30', 'LC31', 'LC32',
        'LC33', 'LC40', 'LC41', 'LC47', 'LC48', 'LC58', 'MB03', 'MB06',
        'MC01', 'MC01', 'MC02', 'MC05')




 --导出主键
  SELECT 'alter table '||T.table_name||
  ' add constraint '||T.constraint_name||' primary key ('||ZJW_C2L(T.constraint_name)||');' 
  FROM USER_CONSTRAINTS T
   WHERE T.TABLE_NAME IN
       ('IB02', 'IB03', 'IC02', 'IC05', 'IC06', 'IC10', 'IC11', 'IC13',
        'IC14', 'IC16', 'IC18', 'IC20', 'IC21', 'IC22', 'IC26', 'IC27',
        'IC28', 'IC40', 'IC41', 'IC43', 'IC44', 'IC45', 'IC51', 'IC64',
        'IC68', 'IC93', 'ICA8', 'ICD1', 'ICD2', 'ICD3', 'ICE1', 'ICE2',
        'ICF1', 'ICF2', 'ID02', 'LB03', 'LC01', 'LC02', 'LC04', 'LC06',
        'LC09', 'LC12', 'LC13', 'LC14', 'LC16', 'LC17', 'LC20', 'LC21',
        'LC22', 'LC25', 'LC26', 'LC27', 'LC28', 'LC30', 'LC31', 'LC32',
        'LC33', 'LC40', 'LC41', 'LC47', 'LC48', 'LC58', 'MB03', 'MB06',
        'MC01', 'MC01', 'MC02', 'MC05')
     AND T.CONSTRAINT_TYPE ='P';


--导出外键  
  SELECT 'alter table '||T.table_name||
  ' add constraint '||T.constraint_name||' foreign key ('||ZJW_C2L(T.R_constraint_name)||
  ') references '||(SELECT Q.TABLE_NAME FROM USER_CONSTRAINTS Q WHERE Q.constraint_name = T.r_constraint_name) ||'('||ZJW_C2L(T.r_constraint_name)||');'
  FROM USER_CONSTRAINTS T
   WHERE T.TABLE_NAME IN
       ('IB02', 'IB03', 'IC02', 'IC05', 'IC06', 'IC10', 'IC11', 'IC13',
        'IC14', 'IC16', 'IC18', 'IC20', 'IC21', 'IC22', 'IC26', 'IC27',
        'IC28', 'IC40', 'IC41', 'IC43', 'IC44', 'IC45', 'IC51', 'IC64',
        'IC68', 'IC93', 'ICA8', 'ICD1', 'ICD2', 'ICD3', 'ICE1', 'ICE2',
        'ICF1', 'ICF2', 'ID02', 'LB03', 'LC01', 'LC02', 'LC04', 'LC06',
        'LC09', 'LC12', 'LC13', 'LC14', 'LC16', 'LC17', 'LC20', 'LC21',
        'LC22', 'LC25', 'LC26', 'LC27', 'LC28', 'LC30', 'LC31', 'LC32',
        'LC33', 'LC40', 'LC41', 'LC47', 'LC48', 'LC58', 'MB03', 'MB06',
        'MC01', 'MC01', 'MC02', 'MC05')
     AND T.CONSTRAINT_TYPE ='R';
--------------------------------------------------------------------------------




也可以通过DBMS_METADATA.GET_DDL来取得对象DDL
-----SELECT DBMS_METADATA.GET_DDL(A.OBJECT_TYPE,A.OBJECT_NAME) from dba_objects where object_type='TABLE';


object_type:
CLUSTER
CONSUMER GROUP
CONTEXT
DATABASE LINK
DIMENSION
DIRECTORY
EVALUATION CONTEXT
FUNCTION
INDEX
INDEX PARTITION
INDEXTYPE
JAVA CLASS
JAVA DATA
JAVA RESOURCE
JAVA SOURCE
LIBRARY
LOB
MATERIALIZED VIEW
OPERATOR
PACKAGE
PACKAGE BODY
PROCEDURE
QUEUE
RESOURCE PLAN
RULE SET
SEQUENCE
SYNONYM
TABLE
TABLE PARTITION
TRIGGER
TYPE
TYPE BODY
VIEW
XML SCHEMA

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29968458/viewspace-1361515/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29968458/viewspace-1361515/