A quick and elegant way to extract DDL instructions of the database is to create a dmp file with only the structures, extract the SQL statements to import (indexfile option) and format with Pretoria .
Pretoria is a tool that analyzes the structure of import-file created specifically to indexfile option. You can search and replace the storage parameters of tables, indexes and clusters, as well to separate the instructions for creating tables and indexes on their own. These files can be pre-create all segments of the database, basically to reorganize. If you have a database with hundreds of tables, do a manual reorganization in the DDL time consuming. Pretoria employ only takes a few minutes.
If SCOTT is locked, you will have to unlock it and assign a password.
export is executed without segments bd.
Puedes especificar en lugar de owner=y sea full=y o incluso por tablas específicas.
Building
The resulting file 'sch_scott.idx' contains the DDL of tables, constraints and indexes. The first two appear as comments (with REM at the beginning of the line), the only legible at first glance are the CREATE INDEX. This is where the tool comes in Pretoria. The implementation of import with the option indexfile not actually execute any DDL or import data (in case of using a full dmp file) all it does is remove the DDLs and stored in the file specified in the option indexfile.
Segregated
If our interest is to extract the DDL statements as they are, just run the following statement:
With this, the file sch_scott.idx barely legible on the face is processed and divided into two files, one with the definition of the tables and the other with the definition of indexes and constraints. Sample code
without applying Pretoria:
Example Pretoria applying code:
Example specifying parameter file.
The file should contain the parameters that need updating in the script output. For the example above I specified only to update the tablespace for tables and DEPT BONUS:
As the result: CREATE
Changing the USERS tablespace for the table in TS1 and TS2 BONUS for DEPT.
still better, if you're going to reorganize the database tables by moving the tablespace can do it with the following line in the parameter file:
Making all tables in the SCOTT schema are assigned the default tablespace TS_DATA, quite useful if it is hundreds of tables. You can even specify the tablespace in particular tables, for example:
is, by default assigned to the tables in the SCOTT schema tablespace except TS_DATA BONUS to the table to which you are going to assign TS1.
addition tablespace parameters you can specify the file to update the script with these values \u200b\u200bare:
The other options including Pretoria may be running:
Pretoria is a tool that analyzes the structure of import-file created specifically to indexfile option. You can search and replace the storage parameters of tables, indexes and clusters, as well to separate the instructions for creating tables and indexes on their own. These files can be pre-create all segments of the database, basically to reorganize. If you have a database with hundreds of tables, do a manual reorganization in the DDL time consuming. Pretoria employ only takes a few minutes.
Creating dmp file
Oracle export tool basically does is remove the definition of the segments database (tables, indexes) and the data they contain. For this exercise I will use the SCOTT schema created by Oracle as part of such schemes for a 10gR2 database mounted on Linux RHEL 4.If SCOTT is locked, you will have to unlock it and assign a password.
SQL> alter user scott account unlock;
User altered.
SQL> alter user scott IDENTIFIED BY tiger;
User altered.
User altered.
SQL> alter user scott IDENTIFIED BY tiger;
User altered.
export is executed without segments bd.
[oracle @ testserver ~] $ exp scott / tiger @ orcl file = ~ / sch_scott.dmp owner = scott rows = n grants = n statistics = none
Export: Release 10.2.0.4.0 - Production on Tue Aug 3
12:40:14 2010 Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: table data (rows) will not be exported
Note: grants on tables/views/sequences/roles will not be exported
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS
. . exporting table DEPT
. . exporting table ;
EMP. . exporting table ; SALES
. . exporting table SALGRADE
. . exporting table TEST3
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
Export: Release 10.2.0.4.0 - Production on Tue Aug 3
12:40:14 2010 Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: table data (rows) will not be exported
Note: grants on tables/views/sequences/roles will not be exported
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS
. . exporting table DEPT
. . exporting table ;
EMP. . exporting table ; SALES
. . exporting table SALGRADE
. . exporting table TEST3
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
Puedes especificar en lugar de owner=y sea full=y o incluso por tablas específicas.
Building
indexfile file file is created indexfile index-option-to import.
[oracle @ testserver ~] $ imp scott / tiger @ orcl file = sch_scott.dmp fromuser = scott scott indexfile Tousen = = sch_scott.idx
constraints = y Import: Release 10.2.0.4.0 - Production on Tue Aug 3
12:47:02 2010 Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning
, OLAP, Data Mining and Real Application Testing options Export
file created by EXPORT: V10.02.01 via Conventional path import done
in character set and AL16UTF16 US7ASCII
NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) Import terminated successfully
Without warnings.
constraints = y Import: Release 10.2.0.4.0 - Production on Tue Aug 3
12:47:02 2010 Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning
, OLAP, Data Mining and Real Application Testing options Export
file created by EXPORT: V10.02.01 via Conventional path import done
in character set and AL16UTF16 US7ASCII
NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) Import terminated successfully
Without warnings.
The resulting file 'sch_scott.idx' contains the DDL of tables, constraints and indexes. The first two appear as comments (with REM at the beginning of the line), the only legible at first glance are the CREATE INDEX. This is where the tool comes in Pretoria. The implementation of import with the option indexfile not actually execute any DDL or import data (in case of using a full dmp file) all it does is remove the DDLs and stored in the file specified in the option indexfile.
Segregated
DDL
Pretoria is actually a set of Java classes converted to the platform language to avoid the drawbacks that represents the encoding of shell scripts in different types of * nix tool particularly AWK. In the documentation section brings a graphic scheme that developers used to make it efficient.If our interest is to extract the DDL statements as they are, just run the following statement:
[oracle @ testserver ~] $ java sch_scott.idx Pretoria-i-ot-oi scott_tables.ddl scott_indexes.ddl - nc
input Spawn thread! Spawn process
threads! Spawn threads
output!
Input thread done!
All done!
input Spawn thread! Spawn process
threads! Spawn threads
output!
Input thread done!
All done!
With this, the file sch_scott.idx barely legible on the face is processed and divided into two files, one with the definition of the tables and the other with the definition of indexes and constraints. Sample code
without applying Pretoria:
REM CREATE TABLE "SCOTT". "BONUS" ("ENAME" VARCHAR2 (10), "JOB"
REM VARCHAR2 (9), "SAL" NUMBER, "COMM" NUMBER) PCTUSED PCTFREE 10 INITRANS 1 40
MAXTRANS 255 REM STORAGE (INITIAL 65536 FREELISTS 1 freelist
BUFFER_POOL DEFAULT REM GROUPS 1) TABLESPACE "USERS" LOGGING nocompress;
REM CREATE TABLE "SCOTT". "DEPT" ("DEPTNO" NUMBER (2, 0), "DNAME"
REM VARCHAR2 (14), "LOC" VARCHAR2 (13)) PCTFREE 40 INITRANS 1 10 PCTUSED MAXTRANS
255 REM STORAGE (INITIAL 65536 FREELISTS 1 freelist REM GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING nocompress;
REM VARCHAR2 (9), "SAL" NUMBER, "COMM" NUMBER) PCTUSED PCTFREE 10 INITRANS 1 40
MAXTRANS 255 REM STORAGE (INITIAL 65536 FREELISTS 1 freelist
BUFFER_POOL DEFAULT REM GROUPS 1) TABLESPACE "USERS" LOGGING nocompress;
REM CREATE TABLE "SCOTT". "DEPT" ("DEPTNO" NUMBER (2, 0), "DNAME"
REM VARCHAR2 (14), "LOC" VARCHAR2 (13)) PCTFREE 40 INITRANS 1 10 PCTUSED MAXTRANS
255 REM STORAGE (INITIAL 65536 FREELISTS 1 freelist REM GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING nocompress;
Example Pretoria applying code:
CREATE TABLE "SCOTT". "BONUS"
("ENAME" VARCHAR2 (10),
"JOB" VARCHAR2 (9),
"SAL" NUMBER,
"COMM" NUMBER) PCTFREE 10 PCTUSED
40 INITRANS 1 STORAGE
MAXTRANS
255 (INITIAL 65536
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT ) TABLESPACE "USERS" LOGGING NOCOMPRESS ;
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER ( 2 ,0 ) ,
"DNAME" VARCHAR2 ( 14 ) ,
"LOC" VARCHAR2 ( 13 ) )
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE
( INITIAL 65536
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT ) TABLESPACE "USERS" LOGGING NOCOMPRESS ;
("ENAME" VARCHAR2 (10),
"JOB" VARCHAR2 (9),
"SAL" NUMBER,
"COMM" NUMBER) PCTFREE 10 PCTUSED
40 INITRANS 1 STORAGE
MAXTRANS
255 (INITIAL 65536
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT ) TABLESPACE "USERS" LOGGING NOCOMPRESS ;
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER ( 2 ,0 ) ,
"DNAME" VARCHAR2 ( 14 ) ,
"LOC" VARCHAR2 ( 13 ) )
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE
( INITIAL 65536
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT ) TABLESPACE "USERS" LOGGING NOCOMPRESS ;
Example specifying parameter file.
[oracle @ testserver ~] $ java-s storage Pretoria sch_scott.idx-ot-i-oi scott_tables.ddl
Hashing scott_indexes.ddl-nc storage parameters ...
Done! Spawn
input thread! Spawn process
threads! Spawn threads
output! Input thread
done!
All done!
Hashing scott_indexes.ddl-nc storage parameters ...
Done! Spawn
input thread! Spawn process
threads! Spawn threads
output! Input thread
done!
All done!
The file should contain the parameters that need updating in the script output. For the example above I specified only to update the tablespace for tables and DEPT BONUS:
"SCOTT". "BONUS" TABLESPACE TS1
"SCOTT". "DEPT" TABLESPACE TS2
"SCOTT". "DEPT" TABLESPACE TS2
As the result: CREATE
TABLE "SCOTT"."BONUS"
( "ENAME" VARCHAR2 ( 10 ) ,
"JOB" VARCHAR2 ( 9 ) ,
"SAL" NUMBER ,
"COMM" NUMBER )
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE
( INITIAL 65536
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT ) TABLESPACE TS1 LOGGING NOCOMPRESS ;
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER ( 2 ,0 ) ,
"DNAME" VARCHAR2 (14),
"LOC" VARCHAR2 (13)) PCTFREE 10 PCTUSED
40 INITRANS 1 STORAGE
MAXTRANS
255 (INITIAL 65536
; FREELISTS 1
freelist BUFFER_POOL
DEFAULT GROUPS 1) LOGGING TABLESPACE TS2 nocompress;
( "ENAME" VARCHAR2 ( 10 ) ,
"JOB" VARCHAR2 ( 9 ) ,
"SAL" NUMBER ,
"COMM" NUMBER )
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE
( INITIAL 65536
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT ) TABLESPACE TS1 LOGGING NOCOMPRESS ;
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER ( 2 ,0 ) ,
"DNAME" VARCHAR2 (14),
"LOC" VARCHAR2 (13)) PCTFREE 10 PCTUSED
40 INITRANS 1 STORAGE
MAXTRANS
255 (INITIAL 65536
; FREELISTS 1
freelist BUFFER_POOL
DEFAULT GROUPS 1) LOGGING TABLESPACE TS2 nocompress;
Changing the USERS tablespace for the table in TS1 and TS2 BONUS for DEPT.
still better, if you're going to reorganize the database tables by moving the tablespace can do it with the following line in the parameter file:
default_t ("SCOTT") TABLESPACE TS_DATA
Making all tables in the SCOTT schema are assigned the default tablespace TS_DATA, quite useful if it is hundreds of tables. You can even specify the tablespace in particular tables, for example:
default_t ("SCOTT") TABLESPACE TS_DATA
"SCOTT". "BONUS" TABLESPACE TS1
"SCOTT". "BONUS" TABLESPACE TS1
is, by default assigned to the tables in the SCOTT schema tablespace except TS_DATA BONUS to the table to which you are going to assign TS1.
addition tablespace parameters you can specify the file to update the script with these values \u200b\u200bare:
- PCTUSED
- PCTFREE
- INITIAL NEXT
- MINEXTENTS
- MAXEXTENTS
- PCTINCREASE
- FREELISTS
- freelist GROUPS
- INITRANS
- MAXTRANS
- DEGREE
The other options including Pretoria may be running:
[ testserver @ oracle ~] $ java java
Pretoria Pretoria [-l] [-ss] [-nc] [-nv]-i-s \u0026lt;indexfile> \u0026lt;table outputfile> \u0026lt;storagefile>-ot-oi \u0026lt; outputfile index> java
Pretoria-p [-l] [-ss] [-nc] [-nv]-i-ot \u0026lt;indexfile> \u0026lt;table outputfile> -oi <index outputfile>
java Pretoria -p [-l] [-ss] [-nc] [-nv] -o1 outputfile for index DDL
-oi : using -o1 switch - output directory for index DDL
-oc : using -o1 or -o2 switch - outputfile for constraint DDL
-o1 : output mode 1
-o2 : output mode 2
-l : line mode - print each DDL statement on one cowabunga line
-p : pretty printing only - DO NOT process
new storage parameters-ss: strip storage clause - except for LOB's
-nc: do not connect - DO NOT CONNECT generate commands
-nv: do not validate - add constraint NOVALIDATE enable DDL in Pretoria
3c - Kurt Van Meerbeeck - www.ora600.be
In the top of the scripts generated by Pretoria appears a section on comments where the authors mention that before running the script in SQL * Plus must activate the following 'set SQLBLANK on' as it can have blank lines between lines of instructions which would lead to errors when run.
Pretoria Pretoria [-l] [-ss] [-nc] [-nv]-i-s \u0026lt;indexfile> \u0026lt;table outputfile> \u0026lt;storagefile>-ot-oi \u0026lt; outputfile index> java
Pretoria-p [-l] [-ss] [-nc] [-nv]-i-ot \u0026lt;indexfile> \u0026lt;table outputfile> -oi <index outputfile>
java Pretoria -p [-l] [-ss] [-nc] [-nv] -o1 outputfile for index DDL
-oi : using -o1 switch - output directory for index DDL
-oc : using -o1 or -o2 switch - outputfile for constraint DDL
-o1 : output mode 1
-o2 : output mode 2
-l : line mode - print each DDL statement on one cowabunga line
-p : pretty printing only - DO NOT process
new storage parameters-ss: strip storage clause - except for LOB's
-nc: do not connect - DO NOT CONNECT generate commands
-nv: do not validate - add constraint NOVALIDATE enable DDL in Pretoria
3c - Kurt Van Meerbeeck - www.ora600.be
In the top of the scripts generated by Pretoria appears a section on comments where the authors mention that before running the script in SQL * Plus must activate the following 'set SQLBLANK on' as it can have blank lines between lines of instructions which would lead to errors when run.
0 comments:
Post a Comment