Oracle 分区表创建与备份小测

  


思路：
创建一个包含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
5 54581
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个分区表都被按照分区单独导出了。因此，在数据量比较大的情况下，可以考虑使用分区表方案，需要确定使用何种类型的分区表以及索引。