Re: impdp partion_options

From: Connor McDonald <mcdonald.connor_at_gmail.com>
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   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 _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-l
Received on Thu Oct 19 2017 - 02:18:12 CEST

Original text of this message