imp/exp使用遇到的问题

IMP-00003: ORACLE error 1659 encountered
ORA-01659: unable to allocate MINEXTENTS beyond 128 in tablespace USERS

IMP-00032: SQL statement exceeded buffer length

一个是ora-01659的错误，大致如下：

IMP-00003: ORACLE error 1659 encountered
ORA-01659: unable to allocate MINEXTENTS beyond 128 in tablespace USERS

我导出的时候仅是导出表结构，忽略数据的，怎么还有空间问题呢，后来才发现exp里如果不加compress=no的话，那么那些个建表语句就会将他的initial空间指定为现有所有数据空间之和，这也是为了导入的时候空间连续，减少碎片的。后来加上那个参数就基本可以了。起码ini空间不会那么大。

还有个是如下错误：

IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:

这个需要导入的时候制定一个大的buffer参数来控制就没有问题了(buffer=5400000)

如果在EXP的时候加了compress=y,这里的compress并非压缩dmp文件的意思
而是指在EXP的时候,   表的initial尺寸定义将会等于：
1、该表的当前实际占用空间尺寸。
2、或者该表曾经的最大占用空间尺寸，且高水位标记没有降下来。
这表示即使该表没有一条记录, 但如果该表曾经有4M数据量, 且高水位
标记没降下来，那么EXP的时候，加compress=y, 该表的initial将是4M

表的initial等于4M，那么在IMP的时候，不管该表有没有记录， 该表都将
占用4M空间。实验如下：
SQL> create table test nologging as select rownum id from dba_tab_columns;
Table created.
SQL> insert into test select * from test;
67448 rows created.
SQL> /
134896 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from test;
   COUNT(*)
----------
269792

SQL> select segment_name,bytes from user_segments;
SEGMENT_NAME   BYTES
------------   -----
TEST       4194304
可以看到该表占用了4M空间

现在用delete方式(不降低HWM), 删除test表的所有记录
SQL> delete test;
269792 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from test;
   COUNT(*)
----------
      0
然后分别用compress=y和n引导出
exp test/test file=test_y.dmp tables=test compress=y
exp test/test file=test_n.dmp tables=test compress=n

此时有两个文件，其中
test_n.dmp表示没加 compress=Y 参数
test_y.dmp表示加了 compress=Y 参数
2009-09-26   13:01          4,096 test_n.dmp
2009-09-26   13:01          4,096 test_y.dmp

然后我们删除该表
SQL> truncate table test;
Table truncated.
SQL> drop table test;
Table dropped.
purge recyclebin;

再看此时该表所在表空间的占用情况
select /*+ ordered use_merge(a,b) */
   a.tablespace_name             表空间名,
   total/(1024*1024)             表空间大小,
   (total-free)/(1024*1024)        表空间使用大小,
   free/(1024*1024)             表空间剩余大小,
   round((total-free)/total,4)*100 "使用率%"
from (select   tablespace_name,sum(bytes) free from dba_free_space
       group by tablespace_name) a,
   (select   tablespace_name,sum(bytes) total from dba_data_files
       group by tablespace_name) b
where   a.tablespace_name = b.tablespace_name
and a.tablespace_name = 'TS_TEST';

表空间名 表空间使用大小
-------- ----------
TS_TEST 1016.625

然后导入test_y.dmp, 看表空间的增长情况
imp test/test file=test_y.dmp fromuser=test touser=test

select /*+ ordered use_merge(a,b) */
   a.tablespace_name             表空间名,
   total/(1024*1024)             表空间大小,
   (total-free)/(1024*1024)        表空间使用大小,
   free/(1024*1024)             表空间剩余大小,
   round((total-free)/total,4)*100 "使用率%"
from (select   tablespace_name,sum(bytes) free from dba_free_space
       group by tablespace_name) a,
   (select   tablespace_name,sum(bytes) total from dba_data_files
       group by tablespace_name) b
where   a.tablespace_name = b.tablespace_name
and a.tablespace_name = 'TS_TEST';

表空间名 表空间使用大小
-------- ----------
TS_TEST 1020.625
发现，TS_TEST表空间多了4M占用，而此时表test并无记录
然后用PL/SQL DEVELOPER的导出工具来看此时test表的定义
发现，test表的initial 值是4M
create table TEST.TEST
(
   ID NUMBER
)
tablespace TS_TEST
   pctfree 10
   initrans 1
   maxtrans 255
   storage
   (
initial 4M
minextents 1
maxextents unlimited
   );
  
接着，再换一个文件test_n.dmp导入
SQL> truncate table test;
Table truncated.
SQL> drop table test;
Table dropped.
purge recyclebin;
imp test/test file=test_n.dmp fromuser=test touser=test

然后看表空间使用情况
select /*+ ordered use_merge(a,b) */
   a.tablespace_name             表空间名,
   total/(1024*1024)             表空间大小,
   (total-free)/(1024*1024)        表空间使用大小,
   free/(1024*1024)             表空间剩余大小,
   round((total-free)/total,4)*100 "使用率%"
from (select   tablespace_name,sum(bytes) free from dba_free_space
       group by tablespace_name) a,
   (select   tablespace_name,sum(bytes) total from dba_data_files
       group by tablespace_name) b
where   a.tablespace_name = b.tablespace_name
and a.tablespace_name = 'TS_TEST';
表空间名 表空间使用大小
-------- ----------
TS_TEST 1016.625
发现表空间的已用空间几乎没有增加
然后用PL/SQL DEVELOPER的导出工具来看此时test表的定义
发现test表的initial 是 64K
create table TEST.TEST
(
   ID NUMBER
)
tablespace TS_TEST
   pctfree 10
   initrans 1
   maxtrans 255
   storage
   (
initial 64K
minextents 1
maxextents unlimited
   );

另外，如果表的initial 已经是 4M,那么即使是compress=N 也会生成 INITIAL 4M 的定义
可以通过命令修改initial值
alter table test move storage(initial 64K); 

compress=Y主要目的是为了消除存储碎片，以保证某张表的所有记录都存储在连续的空间里
但是负面效应很明显，且自oracle9i开始，使用了本地管理的表空间，存储碎片的问题应该
比低版本好多了。

更多0