The task of upgrading databases in Oracle comes along about as often as new U.S. presidents, however many of the important tasks are the same, setting up new infrastructure, parting ways with the past letting some of it wither on the vine, and planning what needs to be preserved while moving ahead.
Instead of upgrading an Oracle database in place, some plans also call for moving to new hardware. A tried and true approach is to generate scripts and use DataPump exports to recreate your application data on the future database.
Let me tell you about of a team of DBAs who took an export and began to import the data structures and data rows into the new database. After a while, they realized one of the schemas was kind of large and that the whole operation might take longer to complete than allotted. Desparate to beat the clock, they began reverse engineering all the tables schema by schema and applying the resulting scripts to the new target database. Now DBAs were competing with the import to create schemas and move data.
Triggers were not firing in the new system, and applications fell flat on their faces. Alas, the grants, synonyms, roles, and sequences were left behind in this haste, and the applications were not working on the new production environment.
What to do to rescue the installation? Well, reverse engineering sequences would be an onerous chore with the shiny, trusted front-end tool that the DBAs used every day because navigating to each application schema and manually clicking on each sequence to reveal its definition.
The other issue was not ALL of the sequences were needed. Not all of those SYS, sample, special purpose schemas created by Oracle installations were going to be migrated. Besides the new production database was all set and we didn't want collisions or kibble.
The solution: use dynamic SQL to generate a single script of the sequences required from the old system, and run them on the new system, excluding unnecessary schemas.
Here's the Oracle SQL script that took care of that problem:
'CREATE SEQUENCE '||sequence_owner||'.'||sequence_name||' START WITH '|| TO_CHAR(last_number+1) ||
' INCREMENT BY '|| TO_CHAR(increment_by)||' MAXVALUE '||TO_CHAR(max_value) ||
' MINVALUE '||TO_CHAR(min_value),
CASE WHEN cycle_flag='N' THEN ' NOCYCLE ' ELSE ' CYCLE ' END cyc_flag,
CASE WHEN order_flag='N' THEN ' NOORDER ' ELSE ' ORDER ' END ord_flag,
– sequence_owner, sequence_name, last_number,
CASE WHEN cache_size=0 THEN ' NOCACHE ' ELSE ' CACHE '||TO_CHAR(cache_size) END,
– min_value, max_value,
sequence_owner NOT IN ('SYS','SYSTEM') — usual DBA accounts
and sequence_owner NOT IN ( — Oracle 9i Demo Schemas
— Doc ID: 207560.1 Type: FAQ Modified Date: 22-MAR-2009
'HR',– (Human Resources)
'OE',– (Order Entry)
'PM', – (Product Media)
'SH',– (Sales History)
'QS',– (Queued Shipping)
'QS_WS',– (Western Shipping)
'QS_ES',– (Eastern Shipping)
'QS_OS',– (Overseas Shipping)
'QS_CBADM',– (Customer Billing Administration)
'QS_CB',– (Customer Billing)
'QS_CS')– (Customer Service)
and sequence_owner NOT IN ('IX', — new sample schema in Oracle 10g
and sequence_owner NOT IN (– Schemas created by Oracle
— Doc ID: 160861.1 Type: REFERENCE Modified Date: 29-MAY-2009
'SCOTT', – by script $ORACLE_HOME/rdbms/admin/utlsampl.sql
'OUTLN',– by script $ORACLE_HOME/rdbms/admin/sql.bsq
'DBSNMP',– if Enterprise Manager Intelligent Agent is installed
'MGMT_VIEW',– is part of the DB Control Repository
'SYSMAN',– is part of the DB Control Repository and Grid
– see Note 270516.1 for details
'TRACESVR',– if Enterprise Manager is installed
'AURORA$JIS$UTILITY$',– — if Oracle Servlet Engine (OSE) is installed
'MDSYS',– if Oracle Spatial option is installed
'MDDATA',– if Oracle Spatial option is installed
'ORDSYS',– if interMedia Audio option is installed
'ORDPLUGINS',– if interMedia Audio option is installed
'SI_INFORMTN_SCHEMA',– if interMedia option is installed
'CTXSYS',– if Oracle Text option is installed
'WKSYS',– if Oracle Ultra Search option is installed
'WK_TEST',– (10g) if Oracle Ultra Search option is installed
'REPADMIN',– if Replication Option is installed
'LBACSYS',– if Oracle Label Security option is installed
'DVF',– if Oracle Database Vault option is installed
'DVSYS',– if Oracle Database Vault option is installed
'ODM',– if Oracle Data Mining option is installed
'DMSYS',– (10g) if Oracle Data Mining 10g option is installed
'DMSYS',– in 10g version to replace ODM and ODM_MTR schemas
'OLAPSYS',– if OLAP option is installed
'WMSYS',– if Oracle Workspace Manager script owmctab.plb is
'ANONYMOUS',– if catqm.sql catalog script for SQL XML management
'XDB',– is executed
'EXFSYS',– is the Expression Filter Feature repository
'DIP',– for provisioning event processing
'TSMSYS'– Transparent Session Migration
order by sequence_owner, sequence_name ;
If other users besides the named owners need SELECT privileges on the sequence objects, then another script to generate those privileges would need to follow the sequences creation script, but that is an easy adaptation of the above script for most DBAs.