
思路:
创建一个包含BLOB字段的分区表,并使用数据泵进行导出,查看该表是如何被导出的。
操作过程:
创建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