oracle分区表分区导出,Oracle 分区表创建与备份小测

创建hash分区表，包含blob类型的字段，分两个分区，分别称为“part_1”和“part_2”，当然也可以指定更多的分区，在此次测试中，数据量和分区数不是我们主要关心的对象，因此只建2个分区。

SQL>create table wallpaper(id int,paper blob)

partition by hash(id)

(partition part_1,

partition part_2)

/

Table created.

插入数据，数据为事先上传到操作系统目录中的，并在数据库里把存放文件的目录设置为目录对象。

SQL>declare

l_blob blob;

l_bfile bfile;

begin

for i in 1..6 loop

insert into wallpaper(id,paper)

values(i,empty_blob())

returning paper into l_blob;

l_bfile :=bfilename('PAPERPATH',i||'.jpg');

dbms_lob.fileopen(l_bfile);

dbms_lob.loadfromfile(l_blob,l_bfile,dbms_lob.getlength(l_bfile));

dbms_lob.fileclose(l_bfile);

end loop;

commit;

end;

/

PL/SQL procedure successfully completed.

查看blob字段大小：

SQL>select id,dbms_lob.getlength(paper) blen from wallpaper;

ID BLEN

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

2    1254041

554581

6     514649

1    3043204

3     191283

4     127974

6 rows selected.

查看分区内信息分布：

SQL>select id from wallpaper partition(part_1);

ID

----------

2

5

6

SQL> select id from wallpaper partition(part_2);

ID

----------

1

3

4

[oracle@dbserver dmp]$ expdp eric/gao directory=DUMP_FILE_DIR dumpfile=eric_blob20150630.dmp

Export: Release 11.2.0.1.0 - Production on Fri May 29 19:42:08 2015

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 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "ERIC"."SYS_EXPORT_SCHEMA_01":  eric/******** directory=DUMP_FILE_DIR dumpfile=eric_blob20150630.dmp

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 6.312 MB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "ERIC"."WALLPAPER":"PART_2"                 3.212 MB       3 rows

. . exported "ERIC"."WALLPAPER":"PART_1"                 1.744 MB       3 rows

. . exported "ERIC"."MUSIC_EMP":"PART_1"                 5.531 KB       8 rows

. . exported "ERIC"."MUSIC_EMP":"PART_2"                   5.5 KB       6 rows

. . exported "ERIC"."SUMMARY"                            5.539 KB       4 rows

Master table "ERIC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

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

Dump file set for ERIC.SYS_EXPORT_SCHEMA_01 is:

/tmp/dmp/eric_blob20150630.dmp

Job "ERIC"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:43:05

我们可以看到有2个分区表都被按照分区单独导出了。因此，在数据量比较大的情况下，可以考虑使用分区表方案，需要确定使用何种类型的分区表以及索引。