SQL> create table t ( x int , y int, z int )
2 partition by list ( x )
3 (
4 partition p1 values (1),
5 partition p2 values (2),
6 partition p3 values (3)
7 );
Table created.
SQL>
SQL> insert into t select 1, rownum, rownum from dual connect by level <=
10000;
10000 rows created.
SQL> insert into t select 2, rownum, rownum from dual connect by level <=
10000;
10000 rows created.
SQL> insert into t select 3, rownum, rownum from dual connect by level <=
10000;
10000 rows created.
SQL>
SQL> create index t_ix1 on t ( x ) ;
Index created.
SQL> create index t_ix2 on t ( z ) local;
Index created.
SQL>
SQL> create index t_ix3 on t ( y )
2 global partition by range ( y )
3 (
4 partition p1 values less than ( 5000 ),
5 partition p2 values less than ( maxvalue )
6 );
Index created.
C:\bin>expdp userid=mcdonac tables=t dumpfile=t directory=temp
Export: Release 12.2.0.1.0 - Production on Thu Oct 19 08:14:55 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights
reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 -
64bit Production
Starting "MCDONAC"."SYS_EXPORT_TABLE_01": userid=mcdonac/******** tables=t
dumpfile=t directory=temp
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
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/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "MCDONAC"."T":"P1" 152.0 KB 10000
rows
. . exported "MCDONAC"."T":"P2" 152.0 KB 10000
rows
. . exported "MCDONAC"."T":"P3" 152.0 KB 10000
rows
Master table "MCDONAC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MCDONAC.SYS_EXPORT_TABLE_01 is:
C:\TEMP\T.DMP
Job "MCDONAC"."SYS_EXPORT_TABLE_01" successfully completed at Thu Oct 19
08:15:32 2017 elapsed 0 00:00:35
SQL> drop table t purge;
Table dropped.
C:\bin>impdp userid=mcdonac dumpfile=t directory=temp
partition_options=merge
Import: Release 12.2.0.1.0 - Production on Thu Oct 19 08:16:02 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights
reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 -
64bit Production
Master table "MCDONAC"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "MCDONAC"."SYS_IMPORT_FULL_01": userid=mcdonac/********
dumpfile=t directory=temp partition_options=merge
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MCDONAC"."T":"P1" 152.0 KB 10000
rows
. . imported "MCDONAC"."T":"P2" 152.0 KB 10000
rows
. . imported "MCDONAC"."T":"P3" 152.0 KB 10000
rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "MCDONAC"."SYS_IMPORT_FULL_01" successfully completed at Thu Oct 19
08:16:16 2017 elapsed 0 00:00:12
SQL> select * from user_indexes where table_name = 'T'
2 @pr
==============================
INDEX_NAME : T_IX3
INDEX_TYPE : NORMAL
TABLE_OWNER : MCDONAC
TABLE_NAME : T
TABLE_TYPE : TABLE
UNIQUENESS : NONUNIQUE
COMPRESSION : DISABLED
PREFIX_LENGTH :
TABLESPACE_NAME : USERS
INI_TRANS : 2
MAX_TRANS : 255
INITIAL_EXTENT : 65536
NEXT_EXTENT : 1048576
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE :
PCT_THRESHOLD :
INCLUDE_COLUMN :
FREELISTS :
FREELIST_GROUPS :
PCT_FREE : 10
LOGGING : YES
BLEVEL : 1
LEAF_BLOCKS : 63
DISTINCT_KEYS : 10000
AVG_LEAF_BLOCKS_PER_KEY : 1
AVG_DATA_BLOCKS_PER_KEY : 3
CLUSTERING_FACTOR : 30000
STATUS : VALID
NUM_ROWS : 30000
SAMPLE_SIZE : 30000
LAST_ANALYZED : 19-OCT-17
DEGREE : 1
INSTANCES : 1
PARTITIONED : NO
TEMPORARY : N
GENERATED : N
SECONDARY : N
BUFFER_POOL : DEFAULT
FLASH_CACHE : DEFAULT
CELL_FLASH_CACHE : DEFAULT
USER_STATS : NO
DURATION :
PCT_DIRECT_ACCESS :
ITYP_OWNER :
ITYP_NAME :
PARAMETERS :
GLOBAL_STATS : YES
DOMIDX_STATUS :
DOMIDX_OPSTATUS :
FUNCIDX_STATUS :
JOIN_INDEX : NO
IOT_REDUNDANT_PKEY_ELIM : NO
DROPPED : NO
VISIBILITY : VISIBLE
DOMIDX_MANAGEMENT :
SEGMENT_CREATED : YES
ORPHANED_ENTRIES : NO
INDEXING : FULL
==============================
INDEX_NAME : T_IX2
INDEX_TYPE : NORMAL
TABLE_OWNER : MCDONAC
TABLE_NAME : T
TABLE_TYPE : TABLE
UNIQUENESS : NONUNIQUE
COMPRESSION : DISABLED
PREFIX_LENGTH :
TABLESPACE_NAME : USERS
INI_TRANS : 2
MAX_TRANS : 255
INITIAL_EXTENT : 65536
NEXT_EXTENT : 1048576
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE :
PCT_THRESHOLD :
INCLUDE_COLUMN :
FREELISTS :
FREELIST_GROUPS :
PCT_FREE : 10
LOGGING : YES
BLEVEL : 1
LEAF_BLOCKS : 63
DISTINCT_KEYS : 10000
AVG_LEAF_BLOCKS_PER_KEY : 1
AVG_DATA_BLOCKS_PER_KEY : 3
CLUSTERING_FACTOR : 30000
STATUS : VALID
NUM_ROWS : 30000
SAMPLE_SIZE : 30000
LAST_ANALYZED : 19-OCT-17
DEGREE : 1
INSTANCES : 1
PARTITIONED : NO
TEMPORARY : N
GENERATED : N
SECONDARY : N
BUFFER_POOL : DEFAULT
FLASH_CACHE : DEFAULT
CELL_FLASH_CACHE : DEFAULT
USER_STATS : NO
DURATION :
PCT_DIRECT_ACCESS :
ITYP_OWNER :
ITYP_NAME :
PARAMETERS :
GLOBAL_STATS : YES
DOMIDX_STATUS :
DOMIDX_OPSTATUS :
FUNCIDX_STATUS :
JOIN_INDEX : NO
IOT_REDUNDANT_PKEY_ELIM : NO
DROPPED : NO
VISIBILITY : VISIBLE
DOMIDX_MANAGEMENT :
SEGMENT_CREATED : YES
ORPHANED_ENTRIES : NO
INDEXING : FULL
==============================
INDEX_NAME : T_IX1
INDEX_TYPE : NORMAL
TABLE_OWNER : MCDONAC
TABLE_NAME : T
TABLE_TYPE : TABLE
UNIQUENESS : NONUNIQUE
COMPRESSION : DISABLED
PREFIX_LENGTH :
TABLESPACE_NAME : USERS
INI_TRANS : 2
MAX_TRANS : 255
INITIAL_EXTENT : 65536
NEXT_EXTENT : 1048576
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE :
PCT_THRESHOLD :
INCLUDE_COLUMN :
FREELISTS :
FREELIST_GROUPS :
PCT_FREE : 10
LOGGING : YES
BLEVEL : 1
LEAF_BLOCKS : 76
DISTINCT_KEYS : 3
AVG_LEAF_BLOCKS_PER_KEY : 25
AVG_DATA_BLOCKS_PER_KEY : 22
CLUSTERING_FACTOR : 66
STATUS : VALID
NUM_ROWS : 30000
SAMPLE_SIZE : 30000
LAST_ANALYZED : 19-OCT-17
DEGREE : 1
INSTANCES : 1
PARTITIONED : NO
TEMPORARY : N
GENERATED : N
SECONDARY : N
BUFFER_POOL : DEFAULT
FLASH_CACHE : DEFAULT
CELL_FLASH_CACHE : DEFAULT
USER_STATS : NO
DURATION :
PCT_DIRECT_ACCESS :
ITYP_OWNER :
ITYP_NAME :
PARAMETERS :
GLOBAL_STATS : YES
DOMIDX_STATUS :
DOMIDX_OPSTATUS :
FUNCIDX_STATUS :
JOIN_INDEX : NO
IOT_REDUNDANT_PKEY_ELIM : NO
DROPPED : NO
VISIBILITY : VISIBLE
DOMIDX_MANAGEMENT :
SEGMENT_CREATED : YES
ORPHANED_ENTRIES : NO
INDEXING : FULL
PL/SQL procedure successfully completed.
On Tue, Oct 17, 2017 at 11:26 PM, Schauss, Peter [US] (ES&CSO) <
peter.schauss@xxxxxxx> wrote:
Oracle 12.1.0.2 - Enterprise Edition
Redhat Linux 6.2
We are looking at the possibility of unpartitioning tables to save
licensing costs. If I use the impdp partition_option=merge, what happens
to the partitioned indexes?
Thanks,
Peter
--
//www.freelists.org/webpage/oracle-l
