Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Partitioning Enhancements in Oracle Database 11g Release 1

This article uses simple examples to describe the partitioning enhancements in Oracle 11g Release 1.

Related articles.

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:

Interval partitioning, described below, is a form of range partitioning, so the previous list also implies the following combinations:

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');11g

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
11g
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:

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 many of the advantages of partitioning, but leaves the decision of how the data is partitioned to the application layer.

Conditions and restrictions on system partitioning include:

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:

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. See here.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.