====== 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

<code plsql>
  -- 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
</code>

Erzeugte Datei myspool.lst mit Editor öffenen und bei Bedarf editieren und in SQL*Plus aufrufen.


==== Constraints neu anlegen ==== 

<code plsql>
 
  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;
</code>

==== Move Table and Lob Columns ==== 

<code plsql>
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$%'
/
</code>

{{tag>sql script}}