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.sqlAdditional remarks
- 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.
- 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.
- 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:
Prześlij komentarz