Как создать весь DDL схемы Oracle (сценарий)?

Может ли кто-нибудь сказать мне, как я могу генерировать DDL для всех таблиц, представлений, индексов, пакетов, процедур, функций, триггеров, типов, последовательностей, синонимов, грантов и т.д. внутри схемы Oracle? В идеале я хотел бы также скопировать строки, но это менее важно.

Я хочу сделать это в запланированной задаче какого-то типа, а не вручную каждый раз, чтобы исключить использование мастера в SQL Developer.

В идеале, поскольку я буду запускать это на нескольких схемах, которые имеют гранты и синонимы друг другу, я хотел бы иметь способ сделать поиск/замену в выходном файле, чтобы имена схем совпадали с именами моих новых схемы будут.

Спасибо!

Ответы

Ответ 1

Вы можете вывести схему в файл через пакет SQL * Plus и пакет dbms_metadata. Затем замените имя схемы на другое с помощью sed. Это работает для Oracle 10 и выше.

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

Поместите все в скрипт и запустите его через cron (планировщик). Экспорт объектов может быть сложным, когда используются расширенные функции. Не удивляйтесь, если вам нужно добавить еще несколько исключений в приведенный выше код.

Ответ 2

Если вы хотите индивидуально генерировать ddl для каждого объекта,

Запросы:

- GENERATE DDL ДЛЯ ВСЕХ ОБЪЕКТОВ ПОЛЬЗОВАТЕЛЯ

- 1. ДЛЯ ВСЕХ ТАБЛИЦЕЙ

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

- 2. ДЛЯ ВСЕХ ИНДЕКСОВ

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

- 3. ДЛЯ ВСЕХ ВИДОВ

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

ИЛИ

SELECT TEXT FROM USER_VIEWS

- 4. ДЛЯ ВСЕХ МАТЕРИРОВАННЫХ ВИДОВ

SELECT QUERY FROM USER_MVIEWS

- 5. ДЛЯ ВСЕХ ФУНКЦИЙ

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

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

Функция GET_DDL не поддерживает какой-либо объект object_type, такой как LOB, MATERIALIZED VIEW, TABLE PARTITION

SO, Консолидированный запрос для генерации DDL будет:

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;

Ответ 3

Проблема с объектами, такими как 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

Процедура get_ddl для PACKAGE вернет тело spec и, поэтому будет лучше изменить запрос на all_objects, чтобы тела пакета не были возвращены в select.

Пока я изменил запрос на это:

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;

Хотя другие изменения могут потребоваться в зависимости от типов объектов, которые вы получаете...

Ответ 5

Сначала экспортируйте метаданные схемы:

expdp dumpfile=filename logfile=logname directory=dir_name schemas=schema_name

а затем импортировать с помощью параметра sqlfile (он не будет импортировать данные, он просто запишет схему DDL в этот файл)

impdp dumpfile=filename logfile=logname directory=dir_name sqlfile=ddl.sql

Ответ 6

Чтобы создать DDL script для целого SCHEMA, то есть USER, вы можете использовать dbms_metadata.get_ddl.

Выполните следующий script в SQL * Plus, созданный Tim Hall:

При появлении запроса имя пользователя.

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

Ответ 7

Результат этого запроса очень чистый (оригинал здесь)

clear screen
accept uname prompt 'Enter User Name : '
accept outfile prompt  ' Output filename : '

spool &&outfile..gen

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT dbms_metadata.get_ddl('USER','&&uname') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&&uname') from dual;

spool off