This article uses simple examples to describe the partitioning enhancements in Oracle 11g Release 1.
Extended Composite Partitioning
In previous releases of Oracle, composite partitioning was limited to Range-Hash and Range-List partitioning. Oracle 11g Release 1 extends this to allow the following composite partitioning schemes:
- Range-Hash (available since 8i)
- Range-List (available since 9i)
- Range-Range
- List-Range
- List-Hash
- List-List
The follow code provides an example of one of the new composite partitioning schemes. First we create a table with List-Hash composite partitioning.
CREATE TABLE list_hash_tab (
id NUMBER,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE
)
PARTITION BY LIST (code)
SUBPARTITION BY HASH (id)
(
PARTITION part_aa values ('AA')
(
SUBPARTITION part_aa_01,
SUBPARTITION part_aa_02
),
partition part_bb values ('BB')
(
SUBPARTITION part_bb_01,
SUBPARTITION part_bb_02
)
);
Next we populate it with some data, which we expect to be spread throughout the subpartitions.
DECLARE
l_code VARCHAR2(10);
BEGIN
FOR i IN 1 .. 40 LOOP
IF MOD(i, 2) = 0 THEN
l_code := 'BB';
ELSE
l_code := 'AA';
END IF;
INSERT INTO list_hash_tab (id, code, description, created_date)
VALUES (i, l_code, 'Description for ' || i || ' ' || l_code, SYSDATE);
END LOOP;
COMMIT;
END;
/
EXEC DBMS_STATS.gather_table_stats(USER, 'LIST_HASH_TAB', granularity=>'ALL');
Finally, we query the
USER_TAB_SUBPARTITIONS view to see if the data has been distributed across the subpartitions.
COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN subpartition_name FORMAT A20
SELECT table_name, partition_name, subpartition_name, num_rows
FROM user_tab_subpartitions
ORDER by table_name, partition_name, subpartition_name;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
-------------------- -------------------- -------------------- ----------
LIST_HASH_TAB PART_AA PART_AA_01 7
LIST_HASH_TAB PART_AA PART_AA_02 13
LIST_HASH_TAB PART_BB PART_BB_01 10
LIST_HASH_TAB PART_BB PART_BB_02 10
4 rows selected.
SQL>
The query shows the data has been split into the two partitions based on the vale of the
CODE column, then hashed between the subpartitions.
Interval Partitioning
Interval partitioning is an extension of range partitioning, where the system is able to create new partitions as they are required. The
PARTITION BY RANGE clause is used in the normal way to identify the transition point for the partition, then the new
INTERVAL clause used to calculate the range for new partitions when the values go beyond the existing transition point.
The following code shows an example of a table using interval partitioning.
CREATE TABLE interval_tab (
id NUMBER,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE
)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION part_01 values LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY'))
);
Querying the
USER_TAB_PARTITIONS view shows there is only a single partition.
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');
COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN high_value FORMAT A40
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 0
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
1 row selected.
SQL>
Provided we insert data with a created_date value less than '01-NOV-2007' the data will be placed in the existing partition and no new partitions will be created.
INSERT INTO interval_tab VALUES (1, 'ONE', 'One', TO_DATE('16-OCT-2007', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (2, 'TWO', 'Two', TO_DATE('31-OCT-2007', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
1 row selected.
SQL>
If we add data beyond the range of the existing partition, a new partition is created.
INSERT INTO interval_tab VALUES (3, 'THREE', 'Three', TO_DATE('01-NOV-2007', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (4, 'FOUR', 'Four', TO_DATE('30-NOV-2007', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TAB SYS_P44 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
2 rows selected.
SQL>
If we insert data for two months after the current largest transition point, only the required partition is created, not the intervening partitions.
INSERT INTO interval_tab VALUES (5, 'FIVE', 'Five', TO_DATE('01-JAN-2008', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (4, 'FOUR', 'Four', TO_DATE('31-JAN-2008', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TAB SYS_P44 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TAB SYS_P45 TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
3 rows selected.
SQL>
Notice that a partition to hold January 2008 data has not been created. If we insert data for this time period, the missing partition is created.
INSERT INTO interval_tab VALUES (7, 'SEVEN', 'Seven', TO_DATE('01-DEC-2007', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (8, 'EIGHT', 'Eight', TO_DATE('31-DEC-2007', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TAB SYS_P44 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TAB SYS_P45 TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TAB SYS_P46 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
4 rows selected.
SQL>
The following restrictions apply to interval partitioned tables:
- Interval partitioning is restricted to a single partition key that must be a numerical or date range.
- At least one partition must be defined when the table is created.
- Interval partitioning is not supported for index-organized tables.
- You cannot create a domain index on an interval partitioned table.
- Interval partitioning can be used as the primary partitioning mechanism in composite partitioning, but it can't be used at the subpartition level.
- A
MAXVALUE partition cannot be defined for an interval partitioned table.
- NULL values are not allowed in the partition column.
System Partitioning
As you would expect, system partitioning allows large tables to be broken down into smaller partitions, but unlike other partitioning schemes, the database has no control over the placement of rows during insert operations. The following example shows the creation of a system partitioned table.
CREATE TABLE system_partitioned_tab (
id NUMBER,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE
)
PARTITION BY SYSTEM
(
PARTITION part_1,
PARTITION part_2
);
The partition must be explicitly defined in all insert statements or an error is produced.
INSERT INTO system_partitioned_tab VALUES (1, 'ONE', 'One', SYSDATE);
*
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by
the System method
SQL>
The
PARTITION clause is used to define which partition the row should be placed in.
INSERT INTO system_partitioned_tab PARTITION (part_1) VALUES (1, 'ONE', 'One', SYSDATE);
INSERT INTO system_partitioned_tab PARTITION (part_2) VALUES (2, 'TWO', 'Two', SYSDATE);
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'SYSTEM_PARTITIONED_TAB');
COLUMN table_name FORMAT A25
COLUMN partition_name FORMAT A20
COLUMN high_value FORMAT A10
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------- -------------------- ---------- ----------
SYSTEM_PARTITIONED_TAB PART_1 1
SYSTEM_PARTITIONED_TAB PART_2 1
2 rows selected.
SQL>
Notice that the
HIGH_VALUE for the partitions is blank.
The
PARTITION clause is optional for update and delete statements, but omitting this clause will force all partitions to be scanned, since there is no way perform automatic partition pruning when the database has no control over row placement. When the
PARTITION clause is used, you must be sure to perform the operation against the correct partition.
SQL> DELETE FROM system_partitioned_tab PARTITION (part_2) WHERE id = 1;
0 rows deleted.
SQL> UPDATE system_partitioned_tab PARTITION (part_1) SET code = 'TWO' WHERE id = 2;
0 rows deleted.
SQL>
The
PARTITION clause can also be used in queries to target specific partitions.
SELECT COUNT(*)
FROM system_partitioned_tab PARTITION (part_1);
COUNT(*)
----------
1
1 row selected.
SQL>
System partitioning gives you all the advantages partitioning, but leaves the decision of how the data is partitioned to the application layer.
Conditions and restrictions on system partitioning include:
- If you specify the
PARTITION BY SYSTEM clause, but don't define partitions, a single partition is created with the name in the format of "SYS_Pn".
- If you specify
PARTITION BY SYSTEM PARTITIONS n clause, the database creates "n" partitions with the name in the format of "SYS_Pn". The range of allowable values for "n" is from 1 to 1024K-1.
- System partitioning is not available for index-organized tables or a table that is part of a cluster.
- System partitioning can play no part in composite partitioning.
- You cannot split a system partition.
- System partitioning cannot be specified in a
CREATE TABLE ... AS SELECT statement.
- To insert data into a system-partitioned table using an INSERT INTO ... AS subquery statement, you must use partition-extended syntax to specify the partition into which the values returned by the subquery will be inserted.
Reference Partitioning
Reference partitioning allows tables related by foreign keys to be logically equi-partitioned. The child table is partitioned using the same partitioning key as the parent table without having to duplicate the key columns. Partition maintenance operations performed on the parent table are reflected on the child table, but no partition maintenance operations are allowed on the child table.
The following code contains a partitioned parent table and a dependent reference partitioned child table.
CREATE TABLE parent_tab (
id NUMBER NOT NULL,
code VARCHAR2(10) NOT NULL,
description VARCHAR2(50),
created_date DATE,
CONSTRAINT parent_tab_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (created_date)
(
PARTITION part_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')),
PARTITION part_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY'))
);
CREATE TABLE child_tab (
id NUMBER NOT NULL,
parent_tab_id NUMBER NOT NULL,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE,
CONSTRAINT child_tab_pk PRIMARY KEY (id),
CONSTRAINT child_parent_tab_fk FOREIGN KEY (parent_tab_id)
REFERENCES parent_tab (id)
)
PARTITION BY REFERENCE (child_parent_tab_fk);
Child records that foreign key to rows in the first partition of the parent table should be placed in the first partition of the child table. So we insert two rows into the first partition and one row into the second of the parent table. We then insert three rows into the child table, with one foreign keyed to a row in the first partition and two foreign keyed to a row in the second partition of the master table.
INSERT INTO parent_tab VALUES (1, 'ONE', '1 ONE', SYSDATE);
INSERT INTO parent_tab VALUES (2, 'TWO', '2 TWO', SYSDATE);
INSERT INTO parent_tab VALUES (3, 'THREE', '3 THREE', ADD_MONTHS(SYSDATE,12));
INSERT INTO child_tab VALUES (1, 1, 'ONE', '1 1 ONE', SYSDATE);
INSERT INTO child_tab VALUES (2, 3, 'TWO', '2 3 TWO', SYSDATE);
INSERT INTO child_tab VALUES (3, 3, 'THREE', '3 3 THREE', SYSDATE);
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'PARENT_TAB');
EXEC DBMS_STATS.gather_table_stats(USER, 'CHILD_TAB');
We now expect the parent table to have 2 records in the 2007 partition and 1 in the 2008 partition, while the child table should have 1 row in the 2007 partition and 2 rows in the 2008 partition. The following query confirms out expectation.
COLUMN table_name FORMAT A25
COLUMN partition_name FORMAT A20
COLUMN high_value FORMAT A40
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------- -------------------- ---------------------------------------- ----------
CHILD_TAB PART_2007 1
CHILD_TAB PART_2008 2
PARENT_TAB PART_2007 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PARENT_TAB PART_2008 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-M 1
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
4 rows selected.
SQL>
The following conditions and restrictions apply to reference partitioning:
- The child table must specify a referential integrity constraint defined on the table being created. This constraint must be in
ENABLE VALIDATE NOT DEFERRABLE state (the default) and refer to a primary or unique key on the parent table.
- The foreign key columns referenced in constraint must be NOT NULL.
- The constraint cannot use the
ON DELETE SET NULL clause.
- The parent table referenced must be an existing partitioned table. All partitioning methods except interval partitioning are supported.
- The foreign key cannot contain any virtual columns.
- The referenced primary key or unique constraint on the parent table cannot contain any virtual columns.
- Reference partitioning cannot be used for index-organized tables, external tables, or domain index storage tables.
- A chain of reference partitioned tables can be created, but constraint used can't be self-referencing.
- The
ROW MOVEMENT setting for both tables must match.
- Reference partitioning cannot be specified in a
CREATE TABLE ... AS SELECT statement.
Virtual Column-Based Partitioning
Oracle 11g supports the concept of virtual columns on tables. These virtual columns are not physically stored in the table, but derived from data in the table. These virtual columns can be used in the partition key in all basic partitioning schemes. The example below creates a table that is list partitioned on a virtual column that represents the first letter in the username column of the table.
CREATE TABLE users (
id NUMBER,
username VARCHAR2(20),
first_letter VARCHAR2(1)
GENERATED ALWAYS AS
(
UPPER(SUBSTR(TRIM(username), 1, 1))
) VIRTUAL
)
PARTITION BY LIST (first_letter)
(
PARTITION part_a_g VALUES ('A','B','C','D','E','F','G'),
PARTITION part_h_n VALUES ('H','I','J','K','L','M','N'),
PARTITION part_o_u VALUES ('O','P','Q','R','S','T','U'),
PARTITION part_v_z VALUES ('V','W','X','Y','Z')
);
The following code inserts two rows into each partition defined in the table.
INSERT INTO users (id, username) VALUES (1, 'Andy Pandy');
INSERT INTO users (id, username) VALUES (1, 'Burty Basset');
INSERT INTO users (id, username) VALUES (1, 'Harry Hill');
INSERT INTO users (id, username) VALUES (1, 'Iggy Pop');
INSERT INTO users (id, username) VALUES (1, 'Oliver Hardy');
INSERT INTO users (id, username) VALUES (1, 'Peter Pervis');
INSERT INTO users (id, username) VALUES (1, 'Veruca Salt');
INSERT INTO users (id, username) VALUES (1, 'Wiley Cyote');
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'USERS');
The following query shows the data was distributed as expected.
COLUMN table_name FORMAT A25
COLUMN partition_name FORMAT A20
COLUMN high_value FORMAT A40
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------- -------------------- ---------------------------------------- ----------
USERS PART_A_G 'A', 'B', 'C', 'D', 'E', 'F', 'G' 2
USERS PART_H_N 'H', 'I', 'J', 'K', 'L', 'M', 'N' 2
USERS PART_O_U 'O', 'P', 'Q', 'R', 'S', 'T', 'U' 2
USERS PART_V_Z 'V', 'W', 'X', 'Y', 'Z' 2
4 rows selected.
SQL>
Single Partition Transportable for Oracle Data Pump
Oracle 11g allows single table partitions to be transported between databases, rather than requiring the whole table to be transported. To show this in action we need to create two tablespaces to hold the table partitions and give the test use a quota on these tablespaces.
CONN sys/password@db11g AS SYSDBA
CREATE TABLESPACE transport_test_ts_1
DATAFILE '/u01/app/oracle/oradata/DB11G/tt_ts_1'
SIZE 128K AUTOEXTEND ON NEXT 128K;
CREATE TABLESPACE transport_test_ts_2
DATAFILE '/u01/app/oracle/oradata/DB11G/tt_ts_2'
SIZE 128K AUTOEXTEND ON NEXT 128K;
ALTER USER test
QUOTA UNLIMITED ON transport_test_ts_1
QUOTA UNLIMITED ON transport_test_ts_2;
CONN test/test@db11g
Next, we create and populate a test partitioned table.
CREATE TABLE transport_test_tab (
id NUMBER NOT NULL,
code VARCHAR2(10) NOT NULL,
description VARCHAR2(50),
created_date DATE,
CONSTRAINT transport_test_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (created_date)
(
PARTITION part_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY'))
TABLESPACE transport_test_ts_1,
PARTITION part_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY'))
TABLESPACE transport_test_ts_2
);
INSERT INTO transport_test_tab VALUES (1, 'ONE', '1 ONE', SYSDATE);
INSERT INTO transport_test_tab VALUES (2, 'TWO', '2 TWO', SYSDATE);
INSERT INTO transport_test_tab VALUES (3, 'THREE', '3 THREE', ADD_MONTHS(SYSDATE,12));
INSERT INTO transport_test_tab VALUES (4, 'FOUR', '4 FOUR', ADD_MONTHS(SYSDATE,12));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'TRANSPORT_TEST_TAB');
The following query shows that each partition is on a separate tablespace and contains some data.
COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN tablespace_name FORMAT A20
SELECT table_name, partition_name, tablespace_name, num_rows
FROM user_tab_partitions;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
-------------------- -------------------- -------------------- ----------
TRANSPORT_TEST_TAB PART_2007 TRANSPORT_TEST_TS_1 2
TRANSPORT_TEST_TAB PART_2008 TRANSPORT_TEST_TS_2 2
2 rows selected.
SQL>
Any tablespaces we wish to transport must be made read only.
CONN sys/password@db11g AS SYSDBA
ALTER TABLESPACE transport_test_ts_1 READ ONLY;
When we perform the data pump export, we can specify the individual partition we wish to export using the following syntax.
tables=schema.table:partition transportable=always
Notice the ":partition" section of the
TABLES parameter. The
TRANSPORTABLE parameter indicates that we wish to capture just the metadata for the partiton.
We can now run the Data Pump export using the following command.
expdp system/password tables=test.transport_test_tab:part_2007 transportable=always
directory=data_pump_dir dumpfile=part_2007.dmp
The output is displayed below.
$ expdp system/password tables=test.transport_test_tab:part_2007 transportable=always
directory=data_pump_dir dumpfile=part_2007.dmp
Export: Release 11.1.0.6.0 - Production on Friday, 19 October, 2007 16:40:45
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** tables=test.transport_test_tab:part_2007
transportable=always directory=data_pump_dir dumpfile=part_2007.dmp
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
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
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/DB11G/dpdump/part_2007.dmp
******************************************************************************
Datafiles required for transportable tablespace TRANSPORT_TEST_TS_1:
/u01/app/oracle/oradata/DB11G/tt_ts_1
Datafiles required for transportable tablespace USERS:
/u01/app/oracle/oradata/DB11G/users01.dbf
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 16:40:55
$
To simulate transporting the tablespace, we will drop the existing table and tablespaces. Notice, that we don't actually remove the datafile associated with the first tablespace, as this is the datafile containing our transportable partition.
CONN sys/password@db11g AS SYSDBA
DROP TABLE test.transport_test_tab;
DROP TABLESPACE transport_test_ts_1 INCLUDING CONTENTS;
DROP TABLESPACE transport_test_ts_2 INCLUDING CONTENTS AND DATAFILES;
We can now import the dump file to snap in our transportable partition using the
PARTITION_OPTIONS=DEPARTITION parameter setting, which converts all partitions into standalone table segments.
impdp system/password partition_options=departition dumpfile=part_2007.dmp
transport_datafiles='/u01/app/oracle/oradata/DB11G/tt_ts_1'
The expected output is displayed below.
$ impdp system/password partition_options=departition dumpfile=part_2007.dmp
transport_datafiles='/u01/app/oracle/oradata/DB11G/tt_ts_1'
Import: Release 11.1.0.6.0 - Production on Friday, 19 October, 2007 16:47:04
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** partition_options=departition
dumpfile=part_2007.dmp transport_datafiles=/u01/app/oracle/oradata/DB11G/tt_ts_1
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
CREATE UNIQUE INDEX "TEST"."TRANSPORT_TEST_PK" ON "TEST"."TRANSPORT_TEST_TAB" ("ID") PCTFREE 10
INITRANS 2 MAXTRANS 255 STORAGE(SEG_FILE 4 SEG_BLOCK 59 OBJNO_REUSE 70550 INITIAL 65536
NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "USERS" PARALLEL 1
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39083: Object type CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "TEST"."TRANSPORT_TEST_TAB" ADD CONSTRAINT "TRANSPORT_TEST_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(SEG_FILE 4 SEG_BLOCK 59 OBJNO_REUSE 70550
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type
INDEX:"TEST"."TRANSPORT_TEST_PK" creation failed
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 3 error(s) at 16:47:07
$
The table segment is named using a combination of the table and partition name, so dependent objects fail because they are referencing the wrong table name. We can see the new segment using the following query.
CONN test/test@db11g
EXEC DBMS_STATS.gather_schema_stats(USER);
COLUMN table_name FORMAT A30
COLUMN tablespace_name FORMAT A20
SELECT table_name, tablespace_name, partitioned, num_rows
FROM user_tables;
TABLE_NAME TABLESPACE_NAME PAR NUM_ROWS
------------------------------ -------------------- --- ----------
TRANSPORT_TES_PART_2007 TRANSPORT_TEST_TS_1 NO 2
1 row selected.
SQL>
Partition Advisor
The
SQL Access Advisor, originally introduced in Oracle 10g, has been updated to include advice on partitioning existing tables, materialized views and indexes. Partitioning exiting tables can be quite a lengthy process, so don't expect this advice to provide a quick fix.
Enhanced Statistics Collection for Partitioned Objects
Oracle 11g includes improvements to statistics collection for partitioned objects so untouched partitions are not rescanned. This significantly increases the speed of statistics collection on large tables where some of the partitions contain static data. Where partition exchange load (PEL) is used to add data to the a table, only the newly added partition must be scanned to update the global statistics.
