I will demonstrate how to rebuild table DEPARTMENTS using datapump.
Find FK to DEPARTMENTS
select constraint_type, constraint_name ref_constraint, table_name fk_table from user_constraints
where r_constraint_name in (select constraint_name from user_constraints where table_name=’DEPARTMENTS’);
C REF_CONSTRAINT FK_TABLE
- ------------------------------ ------------------------------
R EMP_DEPT_FK EMPLOYEES
R JHIST_DEPT_FK JOB_HISTORY
Add DEPARTMENTS and FK_TABLE to expdp_metadata.par
$ cat expdp_metadata.par
directory=DATA_PUMP_DIR
userid='/ as sysdba'
metrics=Y
dumpfile=hr.dmp
content=METADATA_ONLY
schemas=HR
include=TABLE:"IN ('DEPARTMENTS','EMPLOYEES','JOB_HISTORY')"
reuse_dumpfiles=Y
Add FK_TABLE to impdp_rebuild_tab.par
$ cat impdp_rebuild_tab.par
directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=hr.dmp
schemas=HR
exclude=TABLE:"IN ('EMPLOYEES','JOB_HISTORY')"
exclude=STATISTICS
sqlfile=rebuild_tab.sql
Since include and exclude cannot be used together, exclude is used to not import statistics.
Add REF_CONSTRAINT to impdp_rebuild_ref.par
$ cat impdp_rebuild_ref.par
directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=hr.dmp
schemas=HR
include=REF_CONSTRAINT:"IN ('EMP_DEPT_FK','JHIST_DEPT_FK')"
sqlfile=rebuild_ref.sql
Datapump it
$ cat rebuild.sh
expdp parfile=expdp_metadata.par
impdp parfile=impdp_rebuild_tab.par
impdp parfile=impdp_rebuild_ref.par
[oracle@lax:db01]/home/oracle
$ ./rebuild.sh
Export: Release 11.2.0.3.0 - Production on Wed Aug 14 21:23:56 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA parfile=expdp_metadata.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Completed 3 TABLE objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Completed 23 COMMENT objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Completed 12 INDEX objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Completed 6 CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Completed 12 INDEX_STATISTICS objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Completed 8 REF_CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Completed 2 TRIGGER objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Completed 3 TABLE_STATISTICS objects in 0 seconds
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/hr.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 21:24:03
Import: Release 11.2.0.3.0 - Production on Wed Aug 14 21:24:05 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_SCHEMA_01": /******** AS SYSDBA parfile=impdp_rebuild_tab.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Completed 1 TABLE objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Completed 5 COMMENT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Completed 2 INDEX objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Completed 1 CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Completed 2 REF_CONSTRAINT objects in 0 seconds
Job "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 21:24:07
Import: Release 11.2.0.3.0 - Production on Wed Aug 14 21:24:08 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_SCHEMA_01": /******** AS SYSDBA parfile=impdp_rebuild_ref.par
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Completed 2 REF_CONSTRAINT objects in 0 seconds
Job "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 21:24:10
[oracle@lax:db01]/home/oracle
$
Edit and Run rebuild_tab.sql
Add: set echo on
Edit made to rebuild_tab.sql will be underlined in run output.
nohup sqlplus "/ as sysdba" @rebuild_tab.sql > rebuild_tab.log 2>&1 &
$ cat rebuild_tab.log
nohup: ignoring input
SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 14 21:44:16 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected.
LAX:(SYS@db01)> alter session set current_schema=hr;
Session altered.
LAX:(SYS@db01)> -- CONNECT SYS
LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
Session altered.
LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
Session altered.
LAX:(SYS@db01)> ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
Session altered.
LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
Session altered.
LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
Session altered.
LAX:(SYS@db01)> ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
Session altered.
LAX:(SYS@db01)> -- new object type path: SCHEMA_EXPORT/TABLE/TABLE
LAX:(SYS@db01)> /*
LAX:(SYS@db01)> CREATE TABLE "HR"."DEPARTMENTS"
LAX:(SYS@db01)> ( "DEPARTMENT_ID" NUMBER(4,0),
LAX:(SYS@db01)> "DEPARTMENT_NAME" VARCHAR2(30 BYTE) CONSTRAINT "DEPT_NAME_NN" NOT NULL ENABLE,
LAX:(SYS@db01)> "MANAGER_ID" NUMBER(6,0),
LAX:(SYS@db01)> "LOCATION_ID" NUMBER(4,0),
LAX:(SYS@db01)> "LAST_UPDATE" DATE DEFAULT sysdate NOT NULL ENABLE
LAX:(SYS@db01)> ) SEGMENT CREATION IMMEDIATE
LAX:(SYS@db01)> PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
LAX:(SYS@db01)> NOCOMPRESS LOGGING
LAX:(SYS@db01)> STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
LAX:(SYS@db01)> PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
LAX:(SYS@db01)> BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
LAX:(SYS@db01)> TABLESPACE "USER_DATA" ;
LAX:(SYS@db01)> */
LAX:(SYS@db01)> -- MANUAL EDIT: drop and recreate table
LAX:(SYS@db01)> create table departments_old compress as select * from departments;
Table created.
LAX:(SYS@db01)> drop table departments cascade constraints purge;
Table dropped.
LAX:(SYS@db01)> create table departments as select * from departments_old;
Table created.
LAX:(SYS@db01)> -- MANUAL EDIT: add DEFAULT values
LAX:(SYS@db01)> alter table departments modify(last_update default sysdate);
Table altered.
LAX:(SYS@db01)> -- new object type path: SCHEMA_EXPORT/TABLE/COMMENT
LAX:(SYS@db01)> COMMENT ON COLUMN "HR"."DEPARTMENTS"."DEPARTMENT_ID" IS 'Primary key column of departments table.';
Comment created.
LAX:(SYS@db01)> COMMENT ON COLUMN "HR"."DEPARTMENTS"."DEPARTMENT_NAME" IS 'A not null column that shows name of a department. Administration,
2 Marketing, Purchasing, Human Resources, Shipping, IT, Executive, Public
3 Relations, Sales, Finance, and Accounting. ';
Comment created.
LAX:(SYS@db01)> COMMENT ON COLUMN "HR"."DEPARTMENTS"."MANAGER_ID" IS 'Manager_id of a department. Foreign key to employee_id column of employees table. The manager_id column of the employee table references this column.';
Comment created.
LAX:(SYS@db01)> COMMENT ON COLUMN "HR"."DEPARTMENTS"."LOCATION_ID" IS 'Location id where a department is located. Foreign key to location_id column of locations table.';
Comment created.
LAX:(SYS@db01)> COMMENT ON TABLE "HR"."DEPARTMENTS" IS 'Departments table that shows details of departments where employees
2 work. Contains 27 rows; references with locations, employees, and job_history tables.';
Comment created.
LAX:(SYS@db01)> -- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX
LAX:(SYS@db01)> -- CONNECT HR
LAX:(SYS@db01)> CREATE UNIQUE INDEX "HR"."DEPT_ID_PK" ON "HR"."DEPARTMENTS" ("DEPARTMENT_ID")
2 PCTFREE 10 INITRANS 2 MAXTRANS 255
3 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
4 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
5 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
6 TABLESPACE "USER_DATA" PARALLEL 1 ;
Index created.
LAX:(SYS@db01)>
LAX:(SYS@db01)> ALTER INDEX "HR"."DEPT_ID_PK" NOPARALLEL;
Index altered.
LAX:(SYS@db01)> CREATE INDEX "HR"."DEPT_LOCATION_IX" ON "HR"."DEPARTMENTS" ("LOCATION_ID")
2 PCTFREE 10 INITRANS 2 MAXTRANS 255
3 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
4 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
5 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
6 TABLESPACE "USER_DATA" PARALLEL 1 ;
Index created.
LAX:(SYS@db01)>
LAX:(SYS@db01)> ALTER INDEX "HR"."DEPT_LOCATION_IX" NOPARALLEL;
Index altered.
LAX:(SYS@db01)> -- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
LAX:(SYS@db01)> -- CONNECT SYS
LAX:(SYS@db01)> ALTER TABLE "HR"."DEPARTMENTS" ADD CONSTRAINT "DEPT_ID_PK" PRIMARY KEY ("DEPARTMENT_ID")
2 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
3 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
4 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
5 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
6 TABLESPACE "USER_DATA" ENABLE;
Table altered.
LAX:(SYS@db01)> -- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
LAX:(SYS@db01)> ALTER TABLE "HR"."DEPARTMENTS" ADD CONSTRAINT "DEPT_LOC_FK" FOREIGN KEY ("LOCATION_ID")
2 REFERENCES "HR"."LOCATIONS" ("LOCATION_ID") ENABLE;
Table altered.
LAX:(SYS@db01)> ALTER TABLE "HR"."DEPARTMENTS" ADD CONSTRAINT "DEPT_MGR_FK" FOREIGN KEY ("MANAGER_ID")
2 REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") DISABLE;
Table altered.
LAX:(SYS@db01)> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lax:db01]/home/oracle
$
Edit and Run rebuild_ref.sql
Add: set echo on
nohup sqlplus "/ as sysdba" @rebuild_ref.sql > rebuild_ref.log 2>&1 &
$ cat rebuild_ref.log
nohup: ignoring input
SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 14 21:55:51 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
LAX:(SYS@db01)> -- CONNECT SYS
LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
Session altered.
LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
Session altered.
LAX:(SYS@db01)> ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
Session altered.
LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
Session altered.
LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
Session altered.
LAX:(SYS@db01)> ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
Session altered.
LAX:(SYS@db01)> -- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
LAX:(SYS@db01)> ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
2 REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE;
Table altered.
LAX:(SYS@db01)> ALTER TABLE "HR"."JOB_HISTORY" ADD CONSTRAINT "JHIST_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
2 REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE;
Table altered.
LAX:(SYS@db01)> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lax:db01]/home/oracle
$
Don’t forget to gather statistics.
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'HR',tabname=>'DEPARTMENTS',method_opt=>'FOR ALL COLUMNS SIZE 1',estimate_percent=>dbms_stats.auto_sample_size,degree=>4);
Thinking Out Loud 