Re: impdp partion_options
Date: Thu, 19 Oct 2017 08:18:12 +0800
Message-ID: <CAB=aETCdFqh7THphxDMMtqEPMDjXTkKY6+KREp3kSkEY7NRYkw_at_mail.gmail.com>
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 10000rows
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 10000rows
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/MARKERJob "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 _at_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_at_ngc.com> 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
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- Connor McDonald =========================== blog: connormcdonald.wordpress.com twitter: _at_connor_mc_d "If you are not living on the edge, you are taking up too much room." - Jayne Howard *Fine print: Views expressed here are my own and not necessarily that of my employer* -- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 19 2017 - 02:18:12 CEST