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;