Comment générer le DDL complet D'un schéma Oracle (scriptable)?

est-ce que quelqu'un peut me dire comment je peux générer le DDL pour toutes les tables, vues, index, paquets, procédures, fonctions, déclencheurs, types, séquences, synonymes, subventions, etc. dans un schéma Oracle? Idéalement, je voudrais copier les lignes trop mais c'est moins important.

je veux faire cela sur une sorte de travail programmé et pas manuellement à chaque fois, donc cela exclut l'utilisation de L'assistant dans le développeur SQL.

idéalement, puisque je vais exécuter ceci sur plusieurs schémas qui avoir des subventions et des synonymes les uns pour les autres, je voudrais avoir un moyen de faire une recherche/remplacement dans la sortie de sorte que les noms de schéma correspondent quels que soient les noms de mes nouveaux schémas vont être.

Merci!

41
demandé sur Lalit Kumar B 2012-06-04 22:40:35

6 réponses

Vous pouvez transférer le schéma vers un fichier via le paquet SQL*Plus et dbms_metadata. Ensuite, remplacez le nom du schéma par un autre via sed. Cela fonctionne pour Oracle 10 et plus.

sqlplus<<EOF
set long 100000
set head off
set echo off
set pagesize 0
set verify off
set feedback off
spool schema.out

select dbms_metadata.get_ddl(object_type, object_name, owner)
from
(
    --Convert DBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type:
    select
        owner,
        --Java object names may need to be converted with DBMS_JAVA.LONGNAME.
        --That code is not included since many database don't have Java installed.
        object_name,
        decode(object_type,
            'DATABASE LINK',      'DB_LINK',
            'JOB',                'PROCOBJ',
            'RULE SET',           'PROCOBJ',
            'RULE',               'PROCOBJ',
            'EVALUATION CONTEXT', 'PROCOBJ',
            'CREDENTIAL',         'PROCOBJ',
            'CHAIN',              'PROCOBJ',
            'PROGRAM',            'PROCOBJ',
            'PACKAGE',            'PACKAGE_SPEC',
            'PACKAGE BODY',       'PACKAGE_BODY',
            'TYPE',               'TYPE_SPEC',
            'TYPE BODY',          'TYPE_BODY',
            'MATERIALIZED VIEW',  'MATERIALIZED_VIEW',
            'QUEUE',              'AQ_QUEUE',
            'JAVA CLASS',         'JAVA_CLASS',
            'JAVA TYPE',          'JAVA_TYPE',
            'JAVA SOURCE',        'JAVA_SOURCE',
            'JAVA RESOURCE',      'JAVA_RESOURCE',
            'XML SCHEMA',         'XMLSCHEMA',
            object_type
        ) object_type
    from dba_objects 
    where owner in ('OWNER1')
        --These objects are included with other object types.
        and object_type not in ('INDEX PARTITION','INDEX SUBPARTITION',
           'LOB','LOB PARTITION','TABLE PARTITION','TABLE SUBPARTITION')
        --Ignore system-generated types that support collection processing.
        and not (object_type = 'TYPE' and object_name like 'SYS_PLSQL_%')
        --Exclude nested tables, their DDL is part of their parent table.
        and (owner, object_name) not in (select owner, table_name from dba_nested_tables)
        --Exclude overflow segments, their DDL is part of their parent table.
        and (owner, object_name) not in (select owner, table_name from dba_tables where iot_type = 'IOT_OVERFLOW')
)
order by owner, object_type, object_name;

spool off
quit
EOF

cat schema.out|sed 's/OWNER1/MYOWNER/g'>schema.out.change.sql

mettez tout dans un script et lancez-le via cron (scheduler). Exporter des objets peut être délicat lorsque des fonctionnalités avancées sont utilisées. Ne soyez pas surpris si vous avez besoin d'ajouter quelques exceptions au code ci-dessus.

50
répondu MichaelN 2018-09-15 00:28:43

si vous voulez générer individuellement ddl pour chaque objet,

les Requêtes sont:

--GENERATE DDL FOR ALL USER OBJECTS

--1. POUR TOUTES LES TABLES

SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME) FROM USER_TABLES;

-- 2. POUR TOUS LES INDEX

SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME) FROM USER_INDEXES WHERE INDEX_TYPE ='NORMAL';

-- 3. POUR TOUS LES POINTS DE VUE

SELECT DBMS_METADATA.GET_DDL('VIEW', VIEW_NAME) FROM USER_VIEWS;

OR

SELECT TEXT FROM USER_VIEWS

-- 4. POUR TOUS LES MATERILIZED Vues

SELECT QUERY FROM USER_MVIEWS

-- 5. POUR L'ENSEMBLE DE LA FONCTION

SELECT DBMS_METADATA.GET_DDL('FUNCTION', OBJECT_NAME) FROM USER_PROCEDURES WHERE OBJECT_TYPE = 'FUNCTION'

===============================================================================================

GET_DDL Function doesnt support for some object_type like LOB, MATERIALIZED VIEW, TABLE PARTITION

DONC, États requête pour générer DDL sera:

SELECT OBJECT_TYPE, OBJECT_NAME,DBMS_METADATA.GET_DDL(OBJECT_TYPE, OBJECT_NAME, OWNER)
  FROM ALL_OBJECTS 
  WHERE (OWNER = 'XYZ') AND OBJECT_TYPE NOT IN('LOB','MATERIALIZED VIEW', 'TABLE PARTITION') ORDER BY OBJECT_TYPE, OBJECT_NAME;
7
répondu Gurupreet Singh Bhatia 2015-11-19 09:09:18

Il y a un problème avec les objets tels que PACKAGE_BODY:

SELECT DBMS_METADATA.get_ddl(object_Type, object_name, owner) FROM ALL_OBJECTS WHERE OWNER = 'WEBSERVICE';


ORA-31600 invalid input value PACKAGE BODY parameter OBJECT_TYPE in function GET_DDL
ORA-06512: на  "SYS.DBMS_METADATA", line 4018
ORA-06512: на  "SYS.DBMS_METADATA", line 5843
ORA-06512: на  line 1
31600. 00000 -  "invalid input value %s for parameter %s in function %s"
*Cause:    A NULL or invalid value was supplied for the parameter.
*Action:   Correct the input value and try the call again.



SELECT DBMS_METADATA.GET_DDL(REPLACE(object_type,' ','_'), object_name, owner)
  FROM all_OBJECTS 
  WHERE (OWNER = 'OWNER1');
4
répondu v0devil 2013-06-26 11:11:28

la procédure get_ddl pour un paquet retournera à la fois spec et body, il sera donc préférable de changer la requête sur les all_objects pour que les corps du paquet ne soient pas retournés sur select.

jusqu'à présent, j'ai changé la requête:

SELECT DBMS_METADATA.GET_DDL(REPLACE(object_type, ' ', '_'), object_name, owner)
FROM all_OBJECTS
WHERE (OWNER = 'OWNER1')
and object_type not like '%PARTITION'
and object_type not like '%BODY'
order by object_type, object_name;

Bien que d'autres changements pourraient être nécessaires selon les types d'objet que vous obtenez...

4
répondu Vitor Pinto 2013-11-12 14:58:14

tout d'abord exporter les métadonnées du schéma et ensuite importer en utilisant l'option sqlfile(il ne sera pas importer des données juste il écrira ddl de schéma à ce fichier)

exemple:- expdp dumpfile=filename logfile=logname directory=dir name schemas=schema name

username:/ as sysdba

impdp dumpfile=filename logfile=logname directory=dir sqlfile=sql.log

username:/ as sysdba

2
répondu ravi 2014-09-29 05:37:44

pour générer le script DDL pour un SCHÉMA c'est à dire un utilisateur, vous pouvez utiliser dbms_metadata.get_ddl.

exécutez le script suivant dans SQL * Plus créé en Tim Hall:

Donner nom d'utilisateur lorsqu'on vous le demande.

set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000

begin
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/

variable v_username VARCHAR2(30);

exec:v_username := upper('&1');

select dbms_metadata.get_ddl('USER', u.username) AS ddl
from   dba_users u
where  u.username = :v_username
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl
from   dba_ts_quotas tq
where  tq.username = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from   dba_role_privs rp
where  rp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from   dba_sys_privs sp
where  sp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from   dba_tab_privs tp
where  tp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl
from   dba_role_privs rp
where  rp.grantee = :v_username
and    rp.default_role = 'YES'
and    rownum = 1
union all
select to_clob('/* Start profile creation script in case they are missing') AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
and    rownum = 1
union all
select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
union all
select to_clob('End profile creation script */') AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
and    rownum = 1
/

set linesize 80 pagesize 14 feedback on trimspool on verify on
2
répondu Lalit Kumar B 2015-11-19 06:53:52