Oracle 分区表创建与备份小测-LMLPHP  


思路:
创建一个包含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


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

11-09 12:05
查看更多