I created a table with deferred segment creation in an Oracle 11.2.0.1.0 Enterprise Edition database:
SQL> create table andrew.tab1 (col1 number)
2 segment creation deferred;
Table created.
SQL>
Then I created the following Data Pump parameter file:
C:\Users\AJ0294094>more datapump1.params
content=all
directory=data_pump_dir
dumpfile=andrew.dmp
logfile=andrew.log1
tables=andrew.tab1
C:\Users\AJ0294094>
… and used Data Pump to export the table I had just created:
C:\Users\AJ0294094>expdp system/secret_password1 parfile=datapump1.params
Export: Release 11.2.0.1.0 - Production on Fri Aug 30 18:07:35 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** parfile=datapump1.params
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ANDREW"."TAB1" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
D:\ORACLE\11.2.0\ADMIN\MDMDEV1\DPDUMP\ANDREW.DMP
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 18:07:40
C:\Users\AJ0294094>
I copied the dump file (andrew.dmp)
from the source database’s Data Pump directory to the target database’s
Data Pump directory. I assume you know how to do this yourself. Then I
pointed my ORACLE_SID to the target database, which was set up using
Oracle 11.2.0.1.0 Standard Edition, and created a new file of Data Pump
parameters as follows:
C:\Users\AJ0294094>more datapump2.params
content=all
directory=data_pump_dir
dumpfile=andrew.dmp
logfile=andrew.log2
C:\Users\AJ0294094>
When I tried to import the table, the job failed as Standard Edition does not support deferred segment creation:
C:\Users\AJ0294094>impdp system/secret_password2 parfile=datapump2.params
Import: Release 11.2.0.1.0 - Production on Fri Aug 30 18:12:08 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** parfile=datapump2.params
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"ANDREW"."TAB1" failed to create with error:
ORA-00439: feature not enabled: Deferred Segment Creation
Failing sql is:
CREATE TABLE "ANDREW"."TAB1" ("COL1" NUMBER) SEGMENT CREATION DEFERRED PCTFREE 1
0 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS"
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 18:12:11
C:\Users\AJ0294094>
I created a third Data Pump parameter file as shown below using the sqlfile parameter:
C:\Users\AJ0294094>more datapump3.params
content=metadata_only
directory=data_pump_dir
dumpfile=andrew.dmp
include=table
logfile=andrew.log3
sqlfile=andrew.sql
C:\Users\AJ0294094>
Then I ran the Data Pump import again:
C:\Users\AJ0294094>impdp system/secret_password2 parfile=datapump3.params
Import: Release 11.2.0.1.0 - Production on Fri Aug 30 18:13:41 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_FULL_01": system/******** parfile=datapump3.params
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at 18:13:43
C:\Users\AJ0294094>
This created a file of SQL in the Data Pump directory to precreate the table. Its contents were as follows. Note that the table is to be precreated with segment creation deferred:
-- CONNECT SYSTEM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "ANDREW"."TAB1"
( "COL1" NUMBER
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS" ;
I amended the SQL to create the table with segment creation immediate and then I was able to run it. If you have this problem, and your file contains several create table statements, you should make this change using a global replacement command:
C:\Users\AJ0294094>sqlplus system/secret_password2
SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 30 18:19:09 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
SQL> -- CONNECT SYSTEM
SQL> ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
Session altered.
SQL> ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
Session altered.
SQL> ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
Session altered.
SQL> ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
Session altered.
SQL> ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
Session altered.
SQL> ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
Session altered.
SQL> -- new object type path: TABLE_EXPORT/TABLE/TABLE
SQL> CREATE TABLE "ANDREW"."TAB1"
2 ( "COL1" NUMBER
3 ) SEGMENT CREATION IMMEDIATE
4 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
5 TABLESPACE "USERS" ;
Table created.
SQL>
Finally,
I created another parameter file and imported the data into the table.
Of course in this case it was not necessary as there was only one table and it was empty. However, I am showing this step because if you have to do this, your dump file may well contain a mix of tables, some with data and some without.
C:\Users\AJ0294094>more datapump4.params
content=data_only
directory=data_pump_dir
dumpfile=andrew.dmp
C:\Users\AJ0294094>impdp system/secret_password2 parfile=datapump4.params
Import: Release 11.2.0.1.0 - Production on Fri Aug 30 18:32:14 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** parfile=datapump4.params
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ANDREW"."TAB1" 0 KB 0 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 18:32:18
C:\Users\AJ0294094>
No comments:
Post a Comment