分区表导入的注意事项测试

2021-08-30 10:42:52

SQL> column segment_name format A20
select segment_name, partition_name, segment_type, tablespace_name from dba_segments
where segment_name = UPPER('&&TAB')
order by partition_name
old 2: where segment_name = UPPER('&&TAB')
new 2: where segment_name = UPPER('range_t')

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
-------------------- --------------- ------------------------------------ --------------------
RANGE_T P1 TABLE PARTITION TEST_TBS
RANGE_T P2 TABLE PARTITION TEST_TBS
RANGE_T P3 TABLE PARTITION TEST_TBS
RANGE_T P4 TABLE PARTITION TEST_TBS

SQL> create index idx_ranget_time on range_t(ord_day,ord_month,ord_year) local tablespace idx_tbs;

Index created.
分区索引的元数据
SQL> column name format A15
column column_name format A15
SELECT name, object_type, column_name FROM dba_part_key_columns
WHERE name=UPPER('SQL> SQL> 2 idx_ranget_time');

NAME OBJECT_TYP COLUMN_NAME
--------------- ---------- ---------------
IDX_RANGET_TIME INDEX ORD_DAY
IDX_RANGET_TIME INDEX ORD_MONTH
IDX_RANGET_TIME INDEX ORD_YEAR

SQL> column segment_name format A20
select segment_name, partition_name, segment_type, tablespace_name from dba_segments
where segment_name = UPPER('SQL> 2 idx_ranget_time');

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
-------------------- --------------- ------------------------------------ --------------------
IDX_RANGET_TIME P1 INDEX PARTITION IDX_TBS
IDX_RANGET_TIME P2 INDEX PARTITION IDX_TBS
IDX_RANGET_TIME P3 INDEX PARTITION IDX_TBS
IDX_RANGET_TIME P4 INDEX PARTITION IDX_TBS

下面我们使用数据泵导出该表，为后续迁移做准备，注意这里分区表RANGE_T放在表空间TEST_TBS，分区索引IDX_RANGET_TIME放在IDX_TBS

expdp mv/oracle directory=oracle_base dumpfile=ranget.dmp tables=range_t;
具体操作如下
[oracle@rac1 ~]$ expdp mv/oracle directory=oracle_base dumpfile=ranget.dmp tables=range_t;

Export: Release 19.0.0.0.0 - Production on Mon Aug 30 10:11:49 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "MV"."SYS_EXPORT_TABLE_01": mv/******** directory=oracle_base dumpfile=ranget.dmp tables=range_t
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 "MV"."RANGE_T":"P4" 8.171 KB 95 rows
. . exported "MV"."RANGE_T":"P3" 8.140 KB 93 rows
. . exported "MV"."RANGE_T":"P2" 8.109 KB 92 rows
. . exported "MV"."RANGE_T":"P1" 8.031 KB 92 rows
Master table "MV"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MV.SYS_EXPORT_TABLE_01 is:
/oracle/base/ranget.dmp
Job "MV"."SYS_EXPORT_TABLE_01" successfully completed at Mon Aug 30 10:13:05 2021 elapsed 0 00:01:09

注意:这里导出了表的元数据，数据，统计信息，索引和索引的统计信息，在导出分区表时，Oracle会逐个分区（独立的表段）导出，这也意味着在使用
impdp导入数据时，也会逐个创建分区表，此时也需要处理分区表所在的空间，判断是否目标库已经存在，如果没有则需要创建或者remap_tablespace;

下面我们导入该表，注意我们在目标库先创建用户mv,这也符合常规业务需求，但是一般表空间目标库是没有的，我们remap_tablespace做映射。

[oracle@rac1 ~]$ impdp mv/oracle directory=oracle_base dumpfile=ranget.dmp tables=range_t remap_tablespace=TEST_TBS:USERS;

Import: Release 19.0.0.0.0 - Production on Mon Aug 30 10:24:50 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-31626: job does not exist
ORA-31633: unable to create master table "MV.SYS_IMPORT_TABLE_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1163
ORA-00959: tablespace 'TEST_TBS' does not exist
ORA-06512: at "SYS.KUPV$FT", line 1056
ORA-06512: at "SYS.KUPV$FT", line 1044

注意，这个导入是失败的，Oracle要求分区表的分区对应的表空间必须存在。下面我们创建对应空间
SQL> create tablespace test_tbs datafile '/oracle/base/oradata/PROD/test_tbs.dbf' size 20m;

Tablespace created.

SQL> alter user mv quota unlimited on test_tbs;

User altered.

继续导入
[oracle@rac1 ~]$ impdp mv/oracle directory=oracle_base dumpfile=ranget.dmp tables=range_t remap_tablespace=TEST_TBS:USERS;

Import: Release 19.0.0.0.0 - Production on Mon Aug 30 10:27:04 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "MV"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "MV"."SYS_IMPORT_TABLE_01": mv/******** directory=oracle_base dumpfile=ranget.dmp tables=range_t remap_tablespace=TEST_TBS:USERS
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MV"."RANGE_T":"P4" 8.171 KB 95 rows
. . imported "MV"."RANGE_T":"P3" 8.140 KB 93 rows
. . imported "MV"."RANGE_T":"P2" 8.109 KB 92 rows
. . imported "MV"."RANGE_T":"P1" 8.031 KB 92 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX:"MV"."IDX_RANGET_TIME" failed to create with error:
ORA-00959: tablespace 'IDX_TBS' does not exist

Failing sql is:
CREATE INDEX "MV"."IDX_RANGET_TIME" ON "MV"."RANGE_T" ("ORD_DAY", "ORD_MONTH", "ORD_YEAR") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "IDX_TBS" LOCAL (PARTITION "P1" NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "IDX_TBS" , PARTITION "P2" NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "IDX_TBS" , PARTITION "P3" NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "IDX_TBS" , PARTITION "P4" NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "IDX_TBS" ) PARALLEL 1

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
Job "MV"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Mon Aug 30 10:28:09 2021 elapsed 0 00:01:02
下面我们查询分区表的分区段表空间是否remap成功。
SQL> select segment_name, partition_name, segment_type, tablespace_name from dba_segments
where segment_name = UPPER('&&TAB')

old 2: where segment_name = UPPER('&&TAB')
new 2: where segment_name = UPPER('range_t')

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
-------------------- -------------------- ------------------------------------ ------------------------------
RANGE_T P1 TABLE PARTITION USERS
RANGE_T P2 TABLE PARTITION USERS
RANGE_T P3 TABLE PARTITION USERS
RANGE_T P4 TABLE PARTITION USERS
此时说明remap_tablespace生效了。

这次数据进去了，但是分区索引导入失败，原因是索引所在表空间不存在,我们增加一个remap条目IDX_TBS:USERS，
[oracle@rac1 ~]$ impdp mv/oracle directory=oracle_base dumpfile=ranget.dmp tables=range_t remap_tablespace=TEST_TBS:USERS,IDX_TBS:USERS;

Import: Release 19.0.0.0.0 - Production on Mon Aug 30 10:34:13 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "MV"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "MV"."SYS_IMPORT_TABLE_01": mv/******** directory=oracle_base dumpfile=ranget.dmp tables=range_t remap_tablespace=TEST_TBS:USERS,IDX_TBS:USERS
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MV"."RANGE_T":"P4" 8.171 KB 95 rows
. . imported "MV"."RANGE_T":"P3" 8.140 KB 93 rows
. . imported "MV"."RANGE_T":"P2" 8.109 KB 92 rows
. . imported "MV"."RANGE_T":"P1" 8.031 KB 92 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/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "MV"."SYS_IMPORT_TABLE_01" successfully completed at Mon Aug 30 10:34:30 2021 elapsed 0 00:00:13
继续查询导入表和索引的信息
SQL> l
1 select segment_name, partition_name, segment_type, tablespace_name from dba_segments
2* where segment_name = UPPER('&&TAB')
SQL> /
old 2: where segment_name = UPPER('&&TAB')
new 2: where segment_name = UPPER('range_t')

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ -------------------- ------------------------------------ --------------------
RANGE_T P1 TABLE PARTITION USERS
RANGE_T P2 TABLE PARTITION USERS
RANGE_T P3 TABLE PARTITION USERS
RANGE_T P4 TABLE PARTITION USERS

SQL> select segment_name, partition_name, segment_type, tablespace_name from dba_segments
2 where segment_name = UPPER('&seg_name');
Enter value for seg_name: IDX_RANGET_TIME
old 2: where segment_name = UPPER('&seg_name')
new 2: where segment_name = UPPER('IDX_RANGET_TIME')

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ -------------------- ------------------------------------ --------------------
IDX_RANGET_TIME P1 INDEX PARTITION USERS
IDX_RANGET_TIME P2 INDEX PARTITION USERS
IDX_RANGET_TIME P3 INDEX PARTITION USERS
IDX_RANGET_TIME P4 INDEX PARTITION USERS

总结对于分区表的导入，需要创建原始分区表所在的表空间，再使用Remap_tablespace才会生效，而分区索引可以remap成功。注意用户对空间必须具有quota的权限。

补充，remap_table操作

[oracle@rac1 ~]$ impdp mv/oracle directory=oracle_base dumpfile=ranget.dmp tables=range_t remap_table=range_t:range_t_old remap_tablespace=TEST_TBS:USERS,IDX_TBS:USERS;

Import: Release 19.0.0.0.0 - Production on Mon Aug 30 10:43:59 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "MV"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "MV"."SYS_IMPORT_TABLE_01": mv/******** directory=oracle_base dumpfile=ranget.dmp tables=range_t remap_table=range_t:range_t_old remap_tablespace=TEST_TBS:USERS,IDX_TBS:USERS
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MV"."RANGE_T_OLD":"P4" 8.171 KB 95 rows
. . imported "MV"."RANGE_T_OLD":"P3" 8.140 KB 93 rows
. . imported "MV"."RANGE_T_OLD":"P2" 8.109 KB 92 rows
. . imported "MV"."RANGE_T_OLD":"P1" 8.031 KB 92 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/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "MV"."SYS_IMPORT_TABLE_01" successfully completed at Mon Aug 30 10:44:15 2021 elapsed 0 00:00:14

SQL> column segment_name format A20
select segment_name, partition_name, segment_type, tablespace_name from dba_segments
where segment_name =SQL> 2 'RANGE_T_OLD';

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
-------------------- -------------------- ------------------------------------ ------------------------------
RANGE_T_OLD P1 TABLE PARTITION USERS
RANGE_T_OLD P2 TABLE PARTITION USERS
RANGE_T_OLD P3 TABLE PARTITION USERS
RANGE_T_OLD P4 TABLE PARTITION USERS