Jak wygenerować cały DDL schematu Oracle (scriptable)?

Jeśli chcesz indywidualnie wygenerować ddl dla każdego obiektu,

Zapytania to:

--GENEROWANIE DDL DLA WSZYSTKICH OBIEKTÓW UŻYTKOWNIKA

--1. DLA WSZYSTKICH TABEL

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

--2. DLA WSZYSTKICH INDEKSÓW

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

--3. DLA WSZYSTKICH ODSŁON

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

Lub

SELECT TEXT FROM USER_VIEWS

--4. DLA WSZYSTKICH MATERIALNYCH POGLĄDÓW

SELECT QUERY FROM USER_MVIEWS

--5. DLA WSZYSTKICH FUNCTION

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

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

Funkcja GET_DDL nie obsługuje niektórych object_type, takich jak LOB, zmaterializowany widok, partycja tabeli

Więc skonsolidowane zapytanie do generowania DDL będzie:

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;

Występuje problem z obiektami takimi jak 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');

Procedura get_ddl dla pakietu zwróci zarówno spec, jak i body, więc lepiej będzie zmienić zapytanie w all_objects, aby ciała pakietów nie były zwracane w select.

Jak na razie zmieniłem zapytanie na to:

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;

Chociaż inne zmiany mogą być potrzebne w zależności od typów obiektów, które otrzymujesz...

Najpierw wyeksportuj metadane schematu, a następnie zaimportuj za pomocą opcji sqlfile (nie importuje danych tylko zapisuje ddl schematu do tego pliku)

Przykład:- 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

Aby wygenerować skrypt DDL dla całego schematu tj. użytkownika , możesz użyć dbms_metadata.get_ddl .

Wykonaj następujący skrypt w SQL * Plus stworzony przez Tim Hall :

Podaj nazwę użytkownika Po wyświetleniu monitu.

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