文档课题：分区表的导出导入测试.

数据库：oracle 19.13

系统：rhel 7.9 64位

环境介绍：单实例 + 多租户

1、数据准备

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 ADTPDB                         READ WRITE NO

SQL> alter session set container=adtpdb;

 

Session altered.

 

SQL> create user sms identified by sms;

 

User created.

 

SQL> grant dba to sms;

 

Grant succeeded.

 

SQL> conn sms/sms@adtpdb

Connected.

 

SQL> create table sales(

  2    product_id varchar2(5),sales_count number(10,2)

  3  )

  4  partition by range(sales_count)

  5  (

  6    partition p1 values less than(1000),

  7    partition p2 values less than(2000),

  8    partition p3 values less than(3000)

  9  );

 

Table created.

 

SQL> col TABLE_OWNER for a15

SQL> col TABLE_NAME for a15

SQL> col PARTITION_NAME for a15

SQL> col high_value for a15

SQL> set line 200

SQL> select table_owner,table_name,partition_name,high_value,partition_position from dba_tab_partitions u where u.table_name='SALES'

 

TABLE_OWNER     TABLE_NAME      PARTITION_NAME  HIGH_VALUE      PARTITION_POSITION

--------------- --------------- --------------- --------------- ------------------

SMS             SALES           P1              1000                             1

SMS             SALES           P2              2000                             2

SMS             SALES           P3              3000                             3

SQL> insert into sales values('1',600);

 

1 row created.

 

SQL> insert into sales values('2',1000);

 

1 row created.

 

SQL> insert into sales values('3',2300);

 

1 row created.

 

SQL> alter table sales add partition p4 values less than(maxvalue);

 

Table altered.

 

SQL> insert into sales values('4',6000);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from sales partition (p1);

 

PRODU SALES_COUNT

----- -----------

1             600

 

SQL> select * from sales partition (p2);

 

PRODU SALES_COUNT

----- -----------

2            1000

 

SQL> select * from sales partition (p3);

 

PRODU SALES_COUNT

----- -----------

3            2300

 

SQL> select * from sales partition (p4);

 

PRODU SALES_COUNT

----- -----------

4            6000

 

2、导出数据

说明：现对P1分区做expdp导出.

SQL> conn sys/oracle_4U@adtpdb as sysdba

Connected.

SQL> create directory expdp_dir as '/home/oracle';

 

Directory created.

 

SQL> grant read,write on directory expdp_dir to sms;

 

Grant succeeded.

 

[oracle@ora-leo-19cs ~]$ expdp sms/sms@adtpdb directory=expdp_dir dumpfile=partition_p1.dmp tables=sales:p1 logfile=expdp_p1.log

 

Export: Release 19.0.0.0.0 - Production on Wed Jan 31 21:20:09 2024

Version 19.13.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 "SMS"."SYS_EXPORT_TABLE_01":  sms/********@adtpdb directory=expdp_dir dumpfile=partition_p1.dmp tables=sales:p1 logfile=expdp_p1.log

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "SMS"."SALES":"P1"                          5.539 KB       1 rows

Master table "SMS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SMS.SYS_EXPORT_TABLE_01 is:

  /home/oracle/partition_p1.dmp

Job "SMS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jan 31 21:20:27 2024 elapsed 0 00:00:15

 

3、验证备份集

--使用如下SQL确认产生的文件为expdp备份集.

SQL> conn sms/sms@adtpdb

Connected.

SQL> set serveroutput on

SQL> declare

  2    v_filetype   NUMBER; -- 0=unknown 1=expdp 2=exp 3=ext

  3    v_info_table sys.ku$_dumpfile_info; -- PL/SQL table with file info

  4  begin

  5    dbms_datapump.get_dumpfile_info(filename   => 'partition_p1.dmp',

  6                                    directory  => upper('impdp_dir'),

  7                                    info_table => v_info_table,

  8                                    filetype   => v_filetype);

  9    dbms_output.put_line('Filetype : ' || v_filetype);

 10  end;

 11  /

Filetype : 1

 

4、删除表分区

说明：现将sms.sales表的P1分区删除.

SQL> conn sms/sms@adtpdb

Connected.

SQL> alter table sales drop partition p1;

 

Table altered.

 

SQL> col TABLE_OWNER for a15

SQL> col TABLE_NAME for a15

SQL> col PARTITION_NAME for a15

SQL> col high_value for a15

SQL> set line 200

SQL> select table_owner,table_name,partition_name,high_value,partition_position from dba_tab_partitions u where u.table_name='SALES';

 

TABLE_OWNER     TABLE_NAME      PARTITION_NAME  HIGH_VALUE      PARTITION_POSITION

--------------- --------------- --------------- --------------- ------------------

SMS             SALES           P2              2000                             1

SMS             SALES           P3              3000                             2

SMS             SALES           P4              MAXVALUE                         3

 

5、表分区恢复

说明：现对表分区使用impdp进行恢复.

SQL> create directory impdp_dir as '/home/oracle/dumpfile';

 

Directory created.

 

SQL> grant read,write on directory impdp_dir to sms;

 

Grant succeeded.

 

[oracle@ora-leo-19cs ~]$ mv partition_p1.dmp dumpfile

[oracle@ora-leo-19cs ~]$ ll dumpfile

total 188

-rw-r----- 1 oracle oinstall 192512 Jan 31 21:20 partition_p1.dmp

 

[oracle@ora-leo-19cs ~]$ vi impdp.sh

impdp sms/sms@adtpdb \

directory=impdp_dir \

remap_schema=SMS:SMS \

remap_table=sales:sales_20240203 \

dumpfile=partition_p1.dmp

logfile=impdp_sales#p1.log \

job_name=sales#p1 \

transform=disable_archive_logging:Y \

table_exists_action=append \

parallel=8

 

说明：若是集群环境需添加cluster=N参数.

 

[oracle@ora-leo-19cs ~]$ sh impdp.sh &

[oracle@ora-leo-19cs dumpfile]$ pwd

/home/oracle/dumpfile

[oracle@ora-leo-19cs dumpfile]$ ls -ltr

total 192

-rw-r----- 1 oracle oinstall 192512 Jan 31 21:20 partition_p1.dmp

-rw-r--r-- 1 oracle oinstall    988 Feb  3 17:46 impdp_sales#p1.log

 [oracle@ora-leo-19cs dumpfile]$ cat impdp_sales#p1.log

;;;

Import: Release 19.0.0.0.0 - Production on Sat Feb 3 17:46:31 2024

Version 19.13.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 "SMS"."SALES#P1" successfully loaded/unloaded

Starting "SMS"."SALES#P1":  sms/********@adtpdb directory=impdp_dir remap_schema=SMS:SMS remap_table=sales:sales_20240203 dumpfile=partition_p1.dmp logfile=impdp_sales#p1.log job_name=sales#p1 transform=disable_archive_logging:Y table_exists_action=append parallel=8

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "SMS"."SALES_20240203":"P1"                 5.539 KB       1 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "SMS"."SALES#P1" successfully completed at Sat Feb 3 17:46:54 2024 elapsed 0 00:00:22

 

6、数据验证

[oracle@ora-leo-19cs dumpfile]$ sqlplus sms/sms@adtpdb

 

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 3 17:49:25 2024

Version 19.13.0.0.0

 

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

 

Last Successful login time: Sat Feb 03 2024 17:46:31 +08:00

 

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.13.0.0.0

 

SQL> col table_name for a15

SQL> col partition_name for a20

SQL> col high_value for a20

SQL> set line 200

SQL> r

  1* select table_name,partition_name,high_value,partition_position from user_tab_partitions where table_name='SALES_20240203'

 

TABLE_NAME      PARTITION_NAME       HIGH_VALUE           PARTITION_POSITION

--------------- -------------------- -------------------- ------------------

SALES_20240203  P1                   1000                                  1

SALES_20240203  P2                   2000                                  2

SALES_20240203  P3                   3000                                  3

SALES_20240203  P4                   MAXVALUE                              4

 

SQL> select * from sales_20240203 partition(p1);

 

PRODU SALES_COUNT

----- -----------

1             600

 

SQL> select * from sales_20240203 partition(p2);

 

no rows selected

 

SQL> select * from sales_20240203 partition(p3);

 

no rows selected

 

SQL> select * from sales_20240203 partition(p4);

 

no rows selected

 

说明：数据成功恢复到sales_20240203临时表.