A commonly asked question i see is around the use of dataump for moving partitions around and what is and isn't possible. I decided to do some small testing for myself to illustrate some of what is (and isn't possible).
The examples below are based on a very simple list partitioned table and hopefully it's going to get the points across.
So first up we create a very basic list partitioned table with a couple of indexes and 4 rows of data.
CREATE TABLE demo(
pkcol NUMBER PRIMARY KEY,
partcol NUMBER)
PARTITION BY LIST (partcol) (
PARTITION p1 VALUES (1),
PARTITION p2 VALUES (2),
PARTITION p3 VALUES (3));
OPS$ORACLE@DEMODB>create index loc_idx on demo(partcol) local;
Index created.
OPS$ORACLE@DEMODB>create index loc_idx on demo(partcol) local;
Index created.
OPS$ORACLE@DEMODB>insert into demo values (1,1);
1 row created.
OPS$ORACLE@DEMODB>insert into demo values (2,2);
1 row created.
OPS$ORACLE@DEMODB>insert into demo values (3,3);
1 row created.
OPS$ORACLE@DEMODB>insert into demo values (4,1);
1 row created.
OPS$ORACLE@DEMODB>commit;
Commit complete.
Now lets do an export of just P1 from that table
[oracle@server-name]:DEMODB:[~]# expdp / tables=demo:p1
Export: Release 11.2.0.3.0 - Production on Fri Oct 3 19:59:28 2014
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 option
Starting "OPS$ORACLE"."SYS_EXPORT_TABLE_03": /******** tables=demo:p1
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 16 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "OPS$ORACLE"."DEMO":"P1" 5.429 KB 2 rows
Master table "OPS$ORACLE"."SYS_EXPORT_TABLE_03" successfully loaded/unloaded
******************************************************************************
Dump file set for OPS$ORACLE.SYS_EXPORT_TABLE_03 is:
/oracle/11.2.0.3.0.DB/rdbms/log/expdat.dmp
Job "OPS$ORACLE"."SYS_EXPORT_TABLE_03" successfully completed at 19:59:41
Now lets see what metadata actually got put into the file
impdp / sqlfile=test.sql
Import: Release 11.2.0.3.0 - Production on Fri Oct 3 20:00:17 2014
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 option
Master table "OPS$ORACLE"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "OPS$ORACLE"."SYS_SQL_FILE_FULL_01": /******** sqlfile=test.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "OPS$ORACLE"."SYS_SQL_FILE_FULL_01" successfully completed at 20:00:19
If we display the content of that file we see thats it's the complete DDL of everything and not just the partition ddl. If you think about it this is the only way it could really do it.
[oracle@server-name]:DEMODB:[/oracle/11.2.0.3.0.DB/rdbms/log]# cat test.sql
-- CONNECT OPS$ORACLE
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 "OPS$ORACLE"."DEMO"
( "PKCOL" NUMBER,
"PARTCOL" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
PARTITION BY LIST ("PARTCOL")
(PARTITION "P1" VALUES (1) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ,
PARTITION "P2" VALUES (2) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ,
PARTITION "P3" VALUES (3) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ) ;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/INDEX
CREATE INDEX "OPS$ORACLE"."LOC_IDX" ON "OPS$ORACLE"."DEMO" ("PARTCOL")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL
(PARTITION "P1"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ,
PARTITION "P2"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ,
PARTITION "P3"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ) PARALLEL 1 ;
ALTER INDEX "OPS$ORACLE"."LOC_IDX" NOPARALLEL;
-- new object type path: TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ALTER TABLE "OPS$ORACLE"."DEMO" ADD PRIMARY KEY ("PKCOL")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
DECLARE I_N VARCHAR2(60);
I_O VARCHAR2(60);
NV VARCHAR2(1);
c DBMS_METADATA.T_VAR_COLL;
df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';
BEGIN
DELETE FROM "SYS"."IMPDP_STATS";
i_n := 'LOC_IDX';
i_o := 'OPS$ORACLE';
EXECUTE IMMEDIATE stmt USING 0,I_N,NV,NV,I_O,0,0,0,0,0,0,0,0,NV,NV,TO_DATE('2014-10-03 19:58:11',df),NV;
i_n := 'LOC_IDX';
i_o := 'OPS$ORACLE';
EXECUTE IMMEDIATE stmt USING 0,I_N,'P1',NV,I_O,0,0,0,0,0,0,0,NV,NV,NV,TO_DATE('2014-10-03 19:58:11',df),NV;
i_n := 'LOC_IDX';
i_o := 'OPS$ORACLE';
EXECUTE IMMEDIATE stmt USING 0,I_N,'P2',NV,I_O,0,0,0,0,0,0,0,NV,NV,NV,TO_DATE('2014-10-03 19:58:11',df),NV;
i_n := 'LOC_IDX';
i_o := 'OPS$ORACLE';
EXECUTE IMMEDIATE stmt USING 0,I_N,'P3',NV,I_O,0,0,0,0,0,0,0,NV,NV,NV,TO_DATE('2014-10-03 19:58:11',df),NV;
DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');
DELETE FROM "SYS"."IMPDP_STATS";
END;
/
So quite a lot of extra stuff in there. Now lets take that file and load in 'just' a single partition
impdp / tables=demo:p2
Import: Release 11.2.0.3.0 - Production on Fri Oct 3 20:02:46 2014
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 option
ORA-39002: invalid operation
ORA-39164: Partition OPS$ORACLE.DEMO:P2 was not found.
First interesting point here - the file clearly contains the full DDL for the table - but something about the file content means i can't import P2 as i didn't export it - so the data is being tagged in some way to make that check happen. So expdp is doing something slightly more than just select * from table partition (pxxx).
Ok - now lets 'just' load in the partition we did export
[oracle@server-name]:DEMODB:[/oracle/11.2.0.3.0.DB/rdbms/log]# impdp / tables=demo:p1
Import: Release 11.2.0.3.0 - Production on Fri Oct 3 20:03:20 2014
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 option
Master table "OPS$ORACLE"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "OPS$ORACLE"."SYS_IMPORT_TABLE_01": /******** tables=demo:p1
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "OPS$ORACLE"."DEMO":"P1" 5.429 KB 2 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "OPS$ORACLE"."SYS_IMPORT_TABLE_01" successfully completed at 20:03:21
This loads fine - but you'll see the partition is loaded under the table data section not some special partition section. This means that the full table ddl was run and we have all the partitions - again when you think about it this is the only way it could work - so we see a select from both P1 and P2 is valid.
OPS$ORACLE@DEMODB>select * from demo partition (p2);
no rows selected
OPS$ORACLE@DEMODB>select * from demo partition (p1);
PKCOL PARTCOL
---------- ----------
1 1
4 1
Right lets clear the table down
OPS$ORACLE@DEMODB>truncate table demo;
Table truncated.
Now we try and 'just' load that partition again
[oracle@server-name]:DEMODB:[/oracle/11.2.0.3.0.DB/rdbms/log]# impdp / tables=demo:p1
Import: Release 11.2.0.3.0 - Production on Fri Oct 3 20:04:58 2014
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 option
Master table "OPS$ORACLE"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "OPS$ORACLE"."SYS_IMPORT_TABLE_01": /******** tables=demo:p1
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "OPS$ORACLE"."DEMO" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "OPS$ORACLE"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 20:04:59
And it fails of course as the partition already exists - if we want to load it we can just use the DATA_ONLY option of the content variable.
impdp / tables=demo:p1 content=DATA_ONLY
Import: Release 11.2.0.3.0 - Production on Fri Oct 3 20:05:53 2014
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 option
Master table "OPS$ORACLE"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "OPS$ORACLE"."SYS_IMPORT_TABLE_01": /******** tables=demo:p1 content=DATA_ONLY
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "OPS$ORACLE"."DEMO":"P1" 5.429 KB 2 rows
Job "OPS$ORACLE"."SYS_IMPORT_TABLE_01" successfully completed at 20:05:56
So what if we had no partition p1 - it was a newly added partition in an existing table that we were moving into our copy table somewhere else? Lets drop the partition and try and import.
OPS$ORACLE@DEMODB>alter table demo drop partition p1;
Table altered.
impdp / tables=demo:p1
Import: Release 11.2.0.3.0 - Production on Fri Oct 3 20:08:02 2014
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 option
Master table "OPS$ORACLE"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "OPS$ORACLE"."SYS_IMPORT_TABLE_01": /******** tables=demo:p1
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "OPS$ORACLE"."DEMO" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "OPS$ORACLE"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 20:08:03And it fails as the table already exists of course - so lets try with the append option.....
impdp / tables=demo:p1 table_exists_action=append
Import: Release 11.2.0.3.0 - Production on Fri Oct 3 20:08:35 2014
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 option
Master table "OPS$ORACLE"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "OPS$ORACLE"."SYS_IMPORT_TABLE_01": /******** tables=demo:p1 table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "OPS$ORACLE"."DEMO" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-39242: Unable to export/import TABLE_DATA:"OPS$ORACLE"."DEMO":"P1" due to table attributes.
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "OPS$ORACLE"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 20:08:37
So there's an obscure error - but with an obvious cause - P1 does not exist (don't know why it just doesn't say that ........
So how to deal with this case - we have a new partition in the source which doesnt exist in the destination system? Well one option is shown below:
impdp / tables=demo:p1 partition_options=departition
Import: Release 11.2.0.3.0 - Production on Fri Oct 3 20:23:43 2014
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 option
Master table "OPS$ORACLE"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "OPS$ORACLE"."SYS_IMPORT_TABLE_01": /******** tables=demo:p1 partition_options=departition
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "OPS$ORACLE"."DEMO_P1" 5.429 KB 2 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-14016: underlying table of a LOCAL partitioned index must be partitioned
Failing sql is:
CREATE INDEX "OPS$ORACLE"."I_LOC_IDX_P1" ON "OPS$ORACLE"."DEMO_P1" ("PARTCOL") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL (PARTITION "P1" PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-31684: Object type CONSTRAINT:"OPS$ORACLE"."SYS_C0058428" already exists
Job "OPS$ORACLE"."SYS_IMPORT_TABLE_01" completed with 2 error(s) at 20:23:45
Couple of errors here but the main table load has worked - the option i chose here 'departition' turns the P1 'partition' into a new table name - by default named TABLENAME_PARTNAME - so DEMO_P1 in this case - some of the later ddl then fails.
We can se though the table is created fine
OPS$ORACLE@DEMODB>select * from demo_p1;
PKCOL PARTCOL
---------- ----------
1 1
4 1
Now if we want to get this into the demo table as a partion we need to use partition exchange - but first we need to create a 'real dummy' partition in the table that we can swap with.
OPS$ORACLE@DEMODB>alter table demo add partition p1 values (1);
Table altered.
So thats added ok and is empty
OPS$ORACLE@DEMODB>select * from demo partition (p1);
no rows selected
So lets give it a try - we exchange partition to just swap the 2 objects - the dummy partition becomes a standalone table and the table becomes partition p1 (all that happens is a switch in the data dictionary)
OPS$ORACLE@DEMODB>alter table demo exchange partition p1 with table demo_p1;
alter table demo exchange partition p1 with table demo_p1
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
And it fails - due to the ddl that failed during the import - as we can see the table definitions are different
OPS$ORACLE@DEMODB>desc demo_p1
Name Null? Type
----------------------------------------- -------- ----------------------------
PKCOL NUMBER
PARTCOL NUMBER
OPS$ORACLE@DEMODB>desc demo
Name Null? Type
----------------------------------------- -------- ----------------------------
PKCOL NOT NULL NUMBER
PARTCOL NUMBER
So lets add in the missing index and pk.
OPS$ORACLE@DEMODB>alter table demo_p1 add primary key (pkcol);
Table altered.
OPS$ORACLE@DEMODB>CREATE INDEX "OPS$ORACLE"."I_LOC_IDX_P1" ON "OPS$ORACLE"."DEMO_P1" ("PARTCOL");
Index created.
Now the table matches
OPS$ORACLE@DEMODB>desc demo_p1
Name Null? Type
----------------------------------------- -------- ----------------------------
PKCOL NOT NULL NUMBER
PARTCOL NUMBER
So lets try the switch again
OPS$ORACLE@DEMODB>alter table demo exchange partition p1 with table demo_p1;
Table altered.
OK - looks good - a quick query will tell us if it looks OK
OPS$ORACLE@DEMODB>select * from demo partition (p1);
PKCOL PARTCOL
---------- ----------
1 1
4 1
OPS$ORACLE@DEMODB>select * from demo_p1;
no rows selected
And there we go - we loaded in a new partition into an existing table with datapump - well with datapump and a little extra help......
Comments
Post a Comment