====== Per SQL Spool Scripte erzeugen ======
SQL*Plus im Detail unter [[http://www.pipperr.de/knowhow/sqlplus/sqlplus.html|Einführung SQL*Plus]]
==== Synonyme erzeugen ====
##SOURCESCHEME## mit dem Schemanamen ersetzen
-- Setup some SQLPlus parameters.
SET heading off
-- Spool everything to an file to reuse the generated SQL.
spool synonymspool
-- Do the Query.
SELECT 'CREATE SYNONYM ##DESTINATIONSCHEME##.' || TABLE_NAME || ' FOR ##SOURCESCHEME##.' || TABLE_NAME || ';'
FROM cat WHERE TABLE_NAME NOT LIKE 'BIN%';
-- Close spool.
spool off
Erzeugte Datei myspool.lst mit Editor öffenen und bei Bedarf editieren und in SQL*Plus aufrufen.
==== Constraints neu anlegen ====
SELECT 'alter table '|| b.TABLE_NAME||' ADD (CONSTRAINT '||a.constraint_name||' FOREIGN KEY ('||a.COLUMN_NAME||')
REFERENCES '||reftab.TABLE_NAME||'('||reftab.COLUMN_NAME||'));'
FROM all_cons_columns a
, dba_constraints b
, (SELECT c.COLUMN_NAME,d.TABLE_NAME,d.CONSTRAINT_NAME FROM all_cons_columns c,dba_constraints d
WHERE c.CONSTRAINT_NAME = d.CONSTRAINT_NAME) reftab
WHERE a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
AND b.delete_rule = 'CASCADE'
AND b.owner=:OWNER
AND reftab.CONSTRAINT_NAME = b.R_CONSTRAINT_NAME;
==== Move Table and Lob Columns ====
SELECT 'alter table '||table_name||' move tablespace xxx_DATA;'
FROM dba_tables
WHERE tablespace_name = 'USERS' AND owner='xxxx' AND table_name NOT LIKE 'DR$%'
/
SELECT 'ALTER table '||TABLE_NAME||' MOVE LOB ('||column_name||') STORE AS (TABLESPACE xx_DATA);'
FROM dba_lobs
WHERE tablespace_name = 'USERS' AND owner='xxx' AND table_name NOT LIKE 'DR$%'
/
{{tag>sql script}}