CODEWITHSUNDEEP
oracle-databaseoracle11gexportsequenceddl
Alexx
Alexx

Reputation: 3692

Oracle export all sequence DDL? (11g)

I am using DBVisualizer to export an entire schema, but it does not 'do' sequences.

There are dozens of sequences in the schema. How can I mass export the DDL to generate all sequences?

Thank you!

Upvotes: 2

Views: 6406

Answers (2)

Drewry Pope
Drewry Pope

Reputation: 327

SELECT
    'DROP SEQUENCE ' || sequence_owner || '.' || sequence_name || '; ' ||
    'CREATE SEQUENCE ' || sequence_owner || '.' || sequence_name ||
        ' INCREMENT BY ' || increment_by ||
        ' START WITH ' || last_number ||
        ' MAXVALUE ' || max_value ||
        ' MINVALUE ' || min_value ||
        CASE WHEN cycle_flag = 'N' THEN ' NO' ELSE ' ' END || 'CYCLE ' ||
        CASE WHEN cache_size = 0 THEN 'NOCACHE' ELSE 'CACHE ' || cache_size END ||
        CASE WHEN order_flag = 'N' THEN ' NO' ELSE ' ' END || 'ORDER;' ddl_sql
FROM
    dba_sequences
WHERE
    sequence_owner NOT IN
        (
            'ANONYMOUS',
            'APEX_030200',
            'APEX_PUBLIC_USER',
            'APPQOSSYS',
            'AQ_ADMINISTRATOR_ROLE',
            'ARCSERVE',
            'CONNECT',
            'CTXSYS',
            'DATAPUMP_EXP_FULL_DATABASE',
            'DATAPUMP_IMP_FULL_DATABASE',
            'DBA',
            'DBAMON',
            'DBSNMP',
            'DIP',
            'EM_DD_ACC',
            'EXFSYS',
            'EXP_FULL_DATABASE',
            'EXP_USR',
            'FLOWS_FILES',
            'IMP_FULL_DATABASE',
            'JAVADEBUGPRIV',
            'LOGSTDBY_ADMINISTRATOR',
            'MDDATA',
            'MDSYS',
            'MGMT_USER',
            'NAGIOS',
            'OEM_ADVISOR',
            'OEM_MONITOR',
            'OLAPSYS',
            'OLAP_DBA',
            'OLAP_USER',
            'ORACLE_OCM',
            'ORDDATA',
            'ORDSYS',
            'OUTLN',
            'OWB$CLIENT',
            'OWBSYS',
            'OWBSYS_AUDIT',
            'RECOVERY_CATALOG_OWNER',
            'RESOURCE',
            'SCHEDULER_ADMIN',
            'SCOTT',
            'SI_INFORMTN_SCHEMA',
            'SPATIAL_CSW_ADMIN_USR',
            'SPATIAL_WFS_ADMIN_USR',
            'SYS',
            'SYSMAN',
            'SYSTEM',
            'TEST',
            'TSMSYS',
            'WMSYS',
            'WRHSE_ETL',
            'XDB'
        )
ORDER BY
    sequence_owner,
    sequence_name
;

One may not need to drop the sequences if they never existed in the first place, removing that line from this query should resolve that if you don't want to ignore many errors on import. The sequence_owner list of schemas' sequences which you don't want to export may vary -- I only have tested this on a couple databases. The purpose of the NOT IN list is to avoid system sequences that don't need to be, and potentially shouldn't be, imported. I presume if you used user_sequences, you could run this on schemas without the appropriate privileges. Be aware that user_sequences doesn't have a sequence_owner column, as the owner is the user running it.

Upvotes: 0

Olafur Tryggvason
Olafur Tryggvason

Reputation: 4874

Use this select:

select to_char (dbms_metadata.get_ddl ('SEQUENCE', user_objects.object_name)) as ddl
  from user_objects
 where object_type = 'SEQUENCE'

Upvotes: 6

Related Questions