Best practices - multiple db creation scripts

[vivekm]

Dear all,

I am going through trying to create a best practices approach to maintaining database creation scripts such as creating tablespaces, users, tables, synonyms, etc.

I have got to the stage of splitting each script by its type and have got a consistent set of scripts. Now, the question is that there is going to be a script for maintaining the live version, one for each of the development phase (of which there may be 10 or so). I want to have one source of database scripts, if possible?!?! Can people tell me of their practices which may be of use to getting as close to 'one set of scripts' as possible.

Additionally, can people tell me whether they maintain two sets of scripts for storage purposes. Each 'CREATE TABLE ...' statement contains a storage clause. The live tables will have a big amount in their storage clauses but the development environments will have a small space allocated in their 'CREATE TABLE ...' clause. Do people maintain two sets of scripts for this or one?

Regards,
Vivek

 

[jee]

The way I do things may be too simplistic for your case, but, but it may give you some ideas.

First, for our development databases we use defaults for all of the storage parameters - only on the production system do we specify a storage clause. I have a separate script for each table, and then I have a "master" script that invokes all of the individual scripts.

In the individual table scripts, storage clauses are surrounded by substitution variables like this:

CREATE TABLE grafic.change_reason (
   change_reason      VARCHAR2 (250)
   CONSTRAINT pk_change_reason PRIMARY KEY
   &open_prod
   USING INDEX TABLESPACE current_index_ts
   &close_prod  
    )
   &open_prod
   TABLESPACE         current_data_ts
   STORAGE            (INITIAL 4k  NEXT 4k)
   &close_prod
;

The "master" script which invokes these scripts first invokes a script which determines if the table creation is being done on a development or production system (determined by what tablespaces are present).

So in the "master" script you might have something like this:

--  See if this is a production or development system
@@dev-or-prod.sql

SET verify OFF

@@create_table_1.sql
@@create_table_2.sql
.
.
.

Finally the body of dev-or-prod.sql looks like this:

   COLUMN OPEN    NEW_VALUE v_open    NOPRINT
   COLUMN CLOSE   NEW_VALUE v_close   NOPRINT
   COLUMN SYSTYPE NEW_VALUE V_systype NOPRINT   

   SELECT '  ' OPEN, '  ' CLOSE, 'PROD' SYSTYPE
   FROM   dual
   WHERE EXISTS (
         SELECT *
         FROM   SYS.USER_TABLESPACES
         WHERE  TABLESPACE_NAME = 'CURRENT_DATA_TS')
UNION
   SELECT '/*' OPEN, '*/' CLOSE, 'DEV' SYSTYPE
   FROM   dual
   WHERE NOT EXISTS (
         SELECT *
         FROM   SYS.USER_TABLESPACES
         WHERE  TABLESPACE_NAME = 'CURRENT_DATA_TS')
   ;
    
   /* Define variables */
   define open_prod = '&&v_open'
   define close_prod = '&&v_close'
   define sys_type = '&&v_systype'

So the open_prod and close_prod variables get defined as spaces if this is run on a production system and as the '/*' and '*/' pair on a development system (which will comment out the storage clause).


This way the same exact set of scripts can be run on development and productions systems.