Home Code Snippets Oracle Reference Oracle Functions Oracle Error Codes Forum Oracle Jobs Oracle Blogs

How to build and maintain an Oracle shell

In productized development where installation scripts and Oracle dump files are the basis for building a new database instance, complexities can be managed by storing Oracle exports and all SQL files in a version control system (such as CVS, Perforce, or Subversion).

A shell database is essentially the logical starting point of a database wth some tables preloaded with essential lookup data, stored database code, constraints, and views defined, but the many of the segments start out empty.

To build a new database deliverable one can follow a number of steps.

1)  Get the latest shell (export dmp)  and create user SQL scripts from version control

2) Connect to the target database with SYSDBA and drop the application users (USERA, USERB)  and their tablespaces (including USER_DATA and USER_INDEXES).

3) Create the users' tablespaces, then create the users anew with the SQL scripts.

4) Import the latest shell  (e.g.  imp fromuser=(USERA,USERB)  touser=(USERA,USERB) file=last_shell.dmp log=step4.log )

5) Apply any database upgrade scripts readied for deployment since last shell was created.

6) Drop and recreate sequences.  This will assure that the actual sequence numbers will be computed from values in associated tables.

7) Review the newly created schemas and recompile any invalid triggers, PL/SQL, and views.   Investigate objects that won't recompile and remove any obsolete objects not cleared by upgrade scripts. Invalid objects will raise errors when imported by end-users and cause calls to product technical support.

8) Rebuild indexes on unique or primary keys that appear to be on the USER_DATA into the USER_INDEXES tablespace.

9) Export the full database  — save this as it will be needed later.  (e.g. exp userid=sys file=NEW_SHELL_STEP9.dmp full=Y compress=N log=STEP13.log )

10) Export the database — all user object definitions
         (e.g. exp userid=USERA file=NEW_SHELL_USERA.dmp  rows=N statistics=NONE log=STEP10A.log )

         (e.g. exp userid=USERB file=NEW_SHELL_USERB.dmp  rows=N statistics=NONE log=STEP10B.log )

11) Export the database — all user data

         (e.g. exp userid=USERA file=NEW_DATA_USERA.dmp  rows=Y statistics=NONE log=STEP11A.log )

         (e.g. exp userid=USERB file=NEW_DATA_USERB.dmp  rows=Y statistics=NONE log=STEP11B.log )

12) With SQLPlus, connect to the target database as SYSDBA, then enter the following commands:



      then re-run the create users script. 

13) Extract DDL for creating USERA objects to a text file.
         (e.g. strings NEW_SHELL_USERA.dmp  | egrep -v -fgrep_v.inp | sed -fsed_nl.inp > USERA_DDL.SQL )

14) Extract DDL for creating USERB objects to a text file.
         (e.g.  strings NEW_SHELL_USERB.dmp  | egrep -v -fgrep_v.inp | sed -fsed_nl.inp > USERB_DDL.SQL )

15)  Edit the USERA_DDL.SQL to remove extraneous information
           Remove the header information from the 1st line down to the line after CONNECT USERA.
           Skip DDL statements until you find the first CREATE TRIGGER, CREATE FUNCTION,  CREATE PROCEDURE, or CREATE VIEW statement.  Delete all lines from this point to the end of the file.

15) Perform the same editing to the USERB_DDL.SQL file.

16) With SQLPlus, connect to the target database as USERA, then enter the following commands:

           SPOOL STEP16.LOG
           SET ECHO ON
           SPOOL OFF

17) With SQLPlus, connect to the target database as USERB, then enter the following commands:

           SPOOL STEP17.LOG
           SET ECHO ON
           SPOOL OFF

18) Re-import the data saved from Step 11.

  (e.g. imp userid=usera file=DATA_USERA.dmp log=STEP18A.log ignore=Y rows=Y constraints=N )

  (e.g. imp userid=userb file=DATA_USERB.dmp log=STEP18B.log ignore=Y rows=Y constraints=N )

19) Re-import the data saved from Step 10

  (e.g. imp userid=sys fromuser=USERA touser=USERA file=NEW_SHELL_USERA.dmp log=STEP19A.log ignore=Y rows=N constraints=Y )
  (e.g. imp userid=sys fromuser=USERB touser=USERB file=NEW_SHELL_USERB.dmp log=STEP19B.log ignore=Y rows=N constraints=Y )

20) Export data for the last time

 (e.g. exp userid=sys full=Y file=last_shell.dmp compress=N log=STEP20.log )

21) Put the last_shell.dmp back into your source control system.

NOTES: Step 13, Step 14:     The strings command is available on both Windows and Unix platforms.

It might be a good idea to store the extracted DDL files in version control as they are useful artifacts of the software development process and are useful as reference representations of the database definitions at various points in the software lifecycle.

This entry was posted in Uncategorized. Bookmark the permalink. Follow any comments here with the RSS feed for this post. Post a comment or leave a trackback: Trackback URL.

One Comment

  1. arkytekture says:

    For Oracle 8i, 9i, the grep_v.inp file looks like:

    ^TABLE "
           The sed_nl.inp file looks like:

    s/^CREATE .$/CREATE/g
    s/ FREELISTS [0-9]*//g
    s/ FREELIST GROUPS [0-9]*)/)/g
    s/STORAGE(INITIAL [0-9]*)//g
    s/ ADD.$/ ADD/g
    s/ CHECK (.$/ CHECK (/g
    I'll be showing you better and more useful techniques to extract the DDL with DBMS_METADATA for Oracle 9 / 10 / 11

Post a Comment

You must be logged in to post a comment.