oracle檢視當前使用者下所有外來鍵、主鍵、索引、sequence的建立語句

oracle檢視當前使用者下所有外來鍵、主鍵、索引、sequence的建立語句

前不久挖掘人員問我，想把一個伺服器上得A使用者下得主鍵、外來鍵、索引都移到B使用者下

方法可以透過pl/sql developer工具可以匯出在匯入

以下是 oracle檢視當前使用者下所有主鍵的建立語句可以直接貼到其他使用者

SELECT --'ALTER TABLE '||A.TABLE_NAME||' ADD CONSTRAINT '||A.CONSTRAINT_NAME||' PRIMARY KEY ('||A.COLUMN_NAME||') USING INDEX TABLESPACE USERS PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED);'
 'ALTER TABLE ' || A.TABLE_NAME || ' ADD CONSTRAINT ' || A.CONSTRAINT_NAME ||
 ' PRIMARY KEY (' || A.COLUMN_NAME || ');'
  FROM USER_CONS_COLUMNS A
  JOIN USER_CONSTRAINTS B
    ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
   AND A.POSITION = 1
   AND B.CONSTRAINT_TYPE = 'P';

外來鍵的建立語句

SELECT DISTINCT 'ALTER TABLE ' || CON1.QCSJ_C000000000400004 ||
                ' ADD CONSTRAINT ' || CON1.QCSJ_C000000000400002 ||
                ' FOREIGN KEY (' || CON1.COLUMN_NAME || ') REFERENCES ' ||
                CON2.TABLE_NAME || ' (' || CON2.COLUMN_NAME || ');'
  FROM (SELECT *
          FROM USER_CONS_COLUMNS A
          JOIN USER_CONSTRAINTS B
            ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
           AND A.POSITION = 1
           AND B.CONSTRAINT_TYPE = 'R') CON1,
       (SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
          FROM (SELECT A1.CONSTRAINT_NAME,
                       A1.TABLE_NAME,
                       A1.COLUMN_NAME,
                       B1.CONSTRAINT_TYPE,
                       B1.R_CONSTRAINT_NAME
                  FROM USER_CONS_COLUMNS A1
                  JOIN USER_CONSTRAINTS B1
                    ON A1.CONSTRAINT_NAME = B1.CONSTRAINT_NAME
                 WHERE A1.POSITION = 1
                   and a1.CONSTRAINT_NAME in
                       ((SELECT B1.R_CONSTRAINT_NAME
                          FROM USER_CONS_COLUMNS A1
                          JOIN USER_CONSTRAINTS B1
                            ON A1.CONSTRAINT_NAME = B1.CONSTRAINT_NAME
                         WHERE A1.POSITION = 1)))) CON2
 WHERE CON1.R_CONSTRAINT_NAME = CON2.CONSTRAINT_NAME;

普通索引的建立語句
SELECT 'CREATE INDEX ' || INDEX_NAME || ' ON SINOSOFT.' || TABLE_NAME || ' (' ||
       COLUMN_NAME || ');'
  FROM USER_IND_COLUMNS
 WHERE INDEX_NAME IN (SELECT INDEX_NAME
                        FROM USER_IND_COLUMNS UIC
                       WHERE NOT EXISTS
                       (SELECT 1
                                FROM USER_CONS_COLUMNS UCC
                               WHERE POSITION = 1
                                 AND UIC.INDEX_NAME = UCC.CONSTRAINT_NAME)
                       GROUP BY INDEX_NAME
                      HAVING COUNT(INDEX_NAME) = 1);
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;