Blog do projektu Open Source JavaHotel

wtorek, 6 grudnia 2011

Oracle, awk, tablespace

Problem
Assume we have the Oracle database schema export and want to deploy this schema to another database.
CREATE TABLE "SCOTT"."BONUS" 
( "ENAME" VARCHAR2(10), 
"JOB" VARCHAR2(9), 
"SAL" NUMBER, 
"COMM" NUMBER 
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 
TABLESPACE "T_SPACE1" 
Probably after deploying this table definition we receive the error message ORA-00959 (Table space T_SPACE1 does not exist).  Of course - no problem to create T_SPACE1 table space manually but what to do if we have thousands of tables with tens or even hundreds different table spaces ?
Solution
I found the following awk script useful. It simply extracts all table spaces names from table schema export and creates a script for creating all table spaces used. Datafile name is the same as table space name.
BEGIN {  }

/TABLESPACE/ {
   for (i=1; i < NF; i++) {
     if ($i == "TABLESPACE") {
       TSPACENAME=toupper($(i+1))
       gsub(",.*","",TSPACENAME);
       namespaces[TSPACENAME] = 1;
     }   
   }
}
END { 
  for (i in namespaces) {
    TSPACENAME=i;
    TFILENAME=tolower(TSPACENAME); gsub("\"","",TFILENAME); 
    TFILENAME = TFILENAME ".dbf";

    print "CREATE TABLESPACE " TSPACENAME
    print "DATAFILE '/home/oracle/app/oracle/oradata/test/" TFILENAME "'"
    print "size 1m autoextend on next 1m maxsize 2048m EXTENT MANAGEMENT LOCAL;"
    print
  }
}
You can catch the standard output and modify it before deployment - for instance remove creating TEMP and USERS table space. An example of usage:
awk -f namespace.awk < MY_USERS_TABLES.SQL >create_tablespace.sql
Additional remarks

  1. This script assumes that TABLESPACE clause and the following table space name is in the same line. It does not hold true every time. It is possible to extend this script but it requires more coding.
  2. The predicat i<NF (not i<=NF) is on purpose. Just skips if TABLESPACE clause is the last in the line. Probably it makes sense to add a warning message on that.
  3. The gsub(",.*","",TSPACENAME); regards the case when table space name is followed by , (coma). For instance  TABLESPACE "T_SPACE", PARTITION .. Unfortunately awk does not allow to specify more than one field deliminator, so coma is included as a part of table space name and should be removed after that.

Brak komentarzy:

Publikowanie komentarza