导航：首页 > 数据库 >

怎么导入导出Oracle分区表数据

发表于：2024-11-25 作者：千家信息网编辑

千家信息网最后更新 2024年11月25日，这篇文章主要介绍"怎么导入导出Oracle分区表数据"，在日常操作中，相信很多人在怎么导入导出Oracle分区表数据问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答"怎么导

千家信息网最后更新 2024年11月25日怎么导入导出Oracle分区表数据

这篇文章主要介绍"怎么导入导出Oracle分区表数据"，在日常操作中，相信很多人在怎么导入导出Oracle分区表数据问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答"怎么导入导出Oracle分区表数据"的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

导入导入Oracle 分区表数据是Oracle DBA 经常完成的任务之一。分区表的导入导出同样可以以普通表的导入导出方式，只不过导入导出需要考虑到分区的特殊性，如分区索引，将分区迁移到普通表，或使用原始分区表导入到新的分区表。下面将描述使用imp/exp，impdp/expdp导入导出

分区表数据
一、分区级别的导入导出
可以导出一个或多个分区，也可以导出所有分区(即整个表)。
可以导入所有分区(即整个表)，一个或多个分区以及子分区。
对于已经存在数据的表，使用imp导入时需要使用参数IGNORE=y，而使用impdp，加table_exists_action=append | replace 参数。

二、创建演示环境
1.查看当前数据库的版本

select * from v$version where rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production

2.创建一个分区表
alter session set nls_date_format='yyyy-mm-dd';

create table tb_pt (
sal_date date not null,
sal_id number not null,
sal_row number(12) not null)
partition by range(sal_date)
(
partition sal_11 values less than(to_date('2017-01-01','yyyy-mm-dd')) ,
partition sal_12 values less than(to_date('2019-01-01','yyyy-mm-dd')) ,
partition sal_13 values less than(to_date('2023-01-01','yyyy-mm-dd')) ,
partition sal_14 values less than(to_date('2025-01-01','yyyy-mm-dd')) ,
partition sal_15 values less than(to_date('2028-01-01','yyyy-mm-dd')) ,
partition sal_16 values less than(to_date('2030-01-01','yyyy-mm-dd')) ,
partition sal_other values less than (maxvalue)
) nologging;

3.创建一个唯一索引
create unique index tb_pt_ind1 on tb_pt(sal_date) nologging;

4.为分区表生成数据
---插入数据
insert into tb_pt select trunc(sysdate)+rownum, dbms_random.random, rownum from dual connect by level<=8000;

---查询验证
select count(1) from tb_pt partition(sal_11);

COUNT(1)
----------
0

select count(1) from tb_pt partition(sal_12);

COUNT(1)
----------
509

select count(1) from tb_pt partition(sal_other);

COUNT(1)
----------
3473

select * from tb_pt partition(sal_12) where rownum < 3;

SAL_DATE SAL_ID SAL_ROW
--------- ---------- ----------
2017-08-10 -2.044E+09 1
2017-08-11 -1.992E+09 2

select count(1) from tb_pt;

COUNT(1)
----------
8000

---收集分区表统计信息
exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'TB_PT', estimate_percent => 100,method_opt=> 'for all indexed columns',cascade=>TRUE,granularity=>'ALL');

三、使用exp/imp导出导入分区表数据
1.导出整个分区表
exp scott/tiger file='/home/oracle/dmp/tb_pt.dmp' log='/home/oracle/dmp/tb_pt.log' tables=tb_pt

注意：需保证数据库字符集和操作系统环境变量指定的字符集一致。

2.导出单个分区
[oracle@slient dmp]$ exp scott/tiger file='/home/oracle/dmp/tb_pt_sal_16.dmp' log='/home/oracle/dmp/tb_pt_sal_16.log' tables=tb_pt:sal_16

Export: Release 11.2.0.4.0 - Production on Wed Aug 9 19:15:06 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table TB_PT
. . exporting partition SAL_16 731 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
[oracle@slient dmp]$

在上面的导出过程中再次出现了统计信息错误的情况，因此采取了对该对象收集统计信息，但并不能解决该错误，但在exp命令行中增加statistics=none即可，如下：

exp scott/tiger file='/home/oracle/dmp/tb_pt_sal_16.dmp' log='/home/oracle/dmp/tb_pt_sal_16.log' tables=tb_pt:sal_16 statistics=none
如果要导出多个分区，则在tables参数中增加分区数。如：tables="(tb_pt:sal_15,tb_pt:sal_16)"

3.使用imp工具生成创建分区表的DDL语句
imp scott/tiger tables=tb_pt indexfile='/home/oracle/dmp/cr_tb_pt.sql' file='/home/oracle/dmp/tb_pt.dmp' ignore=y

这里我们在imp上加了个参数：indexfile='/home/oracle/dmp/cr_tb_pt.sql'，这条imp语句只会在对应的文件里生成分区表的ddl 语句。然后编辑创建好就可以了。

4.导入单个分区(使用先前备份的单个分区导入文件)
--导入前先将分区实现truncate

select count(1) from tb_pt partition(sal_16);

COUNT(1)
----------
731

alter table tb_pt truncate partition sal_16;

Table truncated.

select count(1) from tb_pt partition(sal_16);

COUNT(1)
----------
0

imp scott/tiger tables=tb_pt:sal_16 file='/home/oracle/dmp/tb_pt_sal_16.dmp' ignore=y

Export: Release 11.2.0.1.0 - Production on Wed Mar 9 13:55:39 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing o

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing partition "TB_PT":"SAL_16"
IMP-00058: ORACLE error 1502 encountered
ORA-01502: index 'SCOTT.TB_PT_IND1' or partition of such index is in unusable state

Import terminated successfully with warnings.

收到了ORA-01502错误，下面查看索引的状态，并对其重建索引后再执行导入

--查看索引的状态
select index_name ,status from dba_indexes where table_name='TB_PT';

INDEX_NAME STATUS
------------------------------ --------
TB_PT_IND1 UNUSABLE

--重建索引
alter index TB_PT_IND1 rebuild online;

Index altered.

--再次导入成功
imp scott/tiger tables=tb_pt:sal_16 file='/home/oracle/dmp/tb_pt_sal_16.dmp' ignore=y

select count(*) from tb_pt partition(sal_16);

COUNT(*)
----------
731

5.导入整个表

--首先truncate 整个表
truncate table tb_pt;

Table truncated.

imp scott/tiger tables=tb_pt file='/home/oracle/dmp/tb_pt.dmp' ignore=y indexes=y

select count(1) from tb_pt partition(sal_other);

COUNT(1)
----------
3473

四、使用expdp/impdb来实现分区表的导入导出

1.查看导入导出的目录设置

SQL> select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------
SYS UTLFILE /home/oracle
SYS XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml
SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/hosts/slient/state
SYS DATA_PUMP_DIR /u01/app/oracle/admin/test/dpdump/
SYS ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state

SQL> grant read,write on directory DATA_PUMP_DIR to scott;

Grant succeeded.

2.为分区表创建一个本地索引

create index tb_pt_local_idx
on tb_pt(sal_id)
local
(partition local1,
partition local2,
partition local3,
partition local4,
partition local5,
partition local6,
partition local7)
;

3.导出整个表
expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=tb_pt.dmp logfile=tb_pb.log tables=tb_pt parallel=3

4.导出多个分区

expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=tb_pts.dmp logfile=tb_pt.log tables=(tb_pt:sal_16,tb_pt:sal_other) parallel=2
-bash: syntax error near unexpected token `('

检查了脚本没有错误：原来在linux 5中需要在括号加上双引号才可以

expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=tb_pts.dmp logfile=tb_pt.log tables="(tb_pt:sal_16,tb_pt:sal_other)" parallel=2

5.截断分区sal_other

alter table tb_pt truncate partition(sal_other);

Table truncated.

SQL> select count(*) from tb_pt partition(sal_other);

COUNT(*)
----------
0

--查看索引的状态, TB_PT_IND1不可用
SQL> select index_name,status,partitioned from dba_indexes where table_name='TB_PT';

INDEX_NAME STATUS PAR
------------------------------ -------- ---
TB_PT_IND1 UNUSABLE NO
TB_PT_LOCAL_IDX N/A YES

select index_name ,partition_name, status from dba_ind_partitions where index_owner='SCOTT';

INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
TB_PT_LOCAL_IDX LOCAL1 USABLE
TB_PT_LOCAL_IDX LOCAL2 USABLE
TB_PT_LOCAL_IDX LOCAL3 USABLE
TB_PT_LOCAL_IDX LOCAL4 USABLE
TB_PT_LOCAL_IDX LOCAL5 USABLE
TB_PT_LOCAL_IDX LOCAL6 USABLE
TB_PT_LOCAL_IDX LOCAL7 USABLE

6.导入单个分区

impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=tb_pts.dmp logfile=tb_pt_imp.log tables=tb_pt:sal_other skip_unusable_indexes=y table_exists_action=replace

SQL> select index_name,status,partitioned from dba_indexes where table_name='TB_PT';

INDEX_NAME STATUS PAR
------------------------------ -------- ---
TB_PT_IND1 VALID NO
TB_PT_LOCAL_IDX N/A YES

从上面的导入情况可以看出，尽管执行了truncate partition，然而使用impdp导入工具，并且使用参数table_exists_action=replace可以避免使用imp导入时唯一和主键索引需要重建的问题。注意，如果没有使用table_exists_action=replace参数，将会收到ORA-39151错误，如下:
ORA-39151: Table "SCOTT"."TB_PT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

7.导入整个表
impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=tb_pt.dmp logfile=tb_pt_fullimp.log tables=tb_pt skip_unusable_indexes=y table_exists_action=replace

五、参数skip_unusable_indexes的作用

SQL> show parameter skip

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes boolean TRUE

该参数允许在导入分区数据时延迟对索引的处理，即先将数据导入，导入后再来重建索引分区。
在命令行导入中未指定导入参数skip_unusable_indexes时，则对于索引相关的问题，根据数据库初始化参数的值来确定。
在命令行导入中如果指定了参数skip_unusable_indexes时，则该参数的值优先于数据库初始化参数的设定值。
skip_unusable_indexes=y对unique index不起作用，因为此时的unique index扮演者constraint的作用，所以在insert数据时index必须被更新。
对于单个分区导入时PK，unique index的处理，必须先重建索引然后进行导入。
使用impdp数据泵实现导入并使用参数table_exists_action=replace可以解决上述问题，即ORA-01502错误。