Oracle exp中compress参数的影响测试

Oracle exp中compress参数的影响测试

 

1 创建表时initial默认为64K，手动设置为5M；

SQL> create table t1(id number) storage(initial 5m);

 

2 没有T1的相关数据

SQL> select segment_name,bytes,initial_extent from user_segments; 

原因：在没有插入数据时，不会分配空间，在用户段中也查询不到，这是11g的新功能，延时段分配；

 

3 插入数据

SQL> insert into t1 values(1);

SQL> COMMIT;

 

4 t1表所占空间为5M，初始值5M

SQL> select segment_name,bytes,initial_extent from user_segments;

T1(bytes=5242880,initial_extent=5242880)

 

5 清空表

SQL> truncate table t1;

 

6 t1表所占空间变成initial初始值5M

SQL> select segment_name,bytes,initial_extent from user_segments;

T1(bytes=5242880,initial_extent=5242880)

 

SQL> select 5242880/1024/1024 from dual; ---5M

清空表后，t1仍然占有5M的空间，即初始区分配的大小

 

7 更改initial参数

SQL> alter table t1 move storage(initial 64K);

 

SQL> select segment_name,bytes,initial_extent from user_segments;

T1(bytes=65536,initial_extent=65536)

 

8 插入数据

SQL> insert into t1 select level as id from dual connect by level<=100;

 

9 t1表所占空间为64K

SQL> select segment_name,bytes,initial_extent from user_segments;

T1(bytes=65536,initial_extent=65536)

因为插入的数据很小，不足64K，所以区的个数没有增加

 

10 清空表t1

SQL> truncate table t1;

 

SQL> select segment_name,bytes,initial_extent from user_segments;

T1(bytes=65536,initial_extent=65536)

 

11 插入大量数据

SQL> insert into t1 select level as id from dual connect by level<=1000000;

 

12 查询,表t1所占空间13M，区扩展28个

SQL> select segment_name,bytes,initial_extent from user_segments;

T1(bytes=13631488,initial_extent=65536,extents=28)

 

select 13631488/1024/1024 from dual;---13M

 

13 exp导出t1(不指定compress参数，则默认Y)

[oracle11@back ~]$ exp chen/chen file=a.dmp tables=t1

......

About to export specified tables via Conventional Path ...

. . exporting table                             T1    1000000 rows exported

Export terminated successfully without warnings.

......

 

14 导入

SQL> show user

USER is "CHEN"

 

SQL> drop table t1 purge;

 

[oracle11@back ~]$ imp chen/chen file=a.dmp fromuser=chen touser=chen ignore=y

......

. importing CHEN's objects into CHEN

. . importing table                           "T1"    1000000 rows imported

Import terminated successfully without warnings.

......

 

SQL> col segment_name for a25

SQL> select segment_name,bytes,initial_extent,EXTENTS from user_segments;

 

SEGMENT_NAME                   BYTES INITIAL_EXTENTS EXTENTS

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

T1                          13631488       13631488     1

.....

 

10 rows selected.

导入后表t1的初始值自动变成所有区的总和，区由28个变成1个，也就是将28个区压缩成一个区，这是由于compress=Y参数引起的。

compress=Y主要目的是为了消除存储碎片，以保证某张表的所有记录都存储在连续的空间里。

但是负面效应很明显，且自oracle9i开始，使用了本地管理的表空间，存储碎片的问题应该比低版本好多了。

 

15 更改t1初始值为64K

SQL> alter table t1 move storage(initial 64k);

 

SQL> select segment_name,bytes,initial_extent from user_segments;

 

SEGMENT_NAME                   BYTES INITIAL_EXTENT

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

T1                          13631488          65536

......

 

10 rows selected.

 

16 重新导出导入,指定compress=n

[oracle11@back ~]$ rm -rf a.dmp

[oracle11@back ~]$ exp chen/chen file=a.dmp tables=t1 compress=n

......

About to export specified tables via Conventional Path ...

. . exporting table                             T1    1000000 rows exported

Export terminated successfully without warnings.

......

 

SQL> drop table t1 purge;

 

[oracle11@back ~]$ imp chen/chen file=a.dmp fromuser=chen touser=chen ignore=y

......

. importing CHEN's objects into CHEN

. . importing table                           "T1"    1000000 rows imported

Import terminated successfully without warnings.

......

 

17 导出时取消压缩(compress=N),初始值和区的个数不变

SQL> select segment_name,bytes,initial_extent from user_segments;

 

SEGMENT_NAME                   BYTES INITIAL_EXTENT  EXTENTS

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

T1                          13631488          65536     28

......

10 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-1775132/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29785807/viewspace-1775132/