pub上的一道案例有可能先前建立的表空间设置的init extent 过大

http://www.itpub.net/thread-999984-1-1.html

测试如下:

C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Thu Jun 5 17:45:54 2008

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> create tablespace init_test logging datafile 'd:\init_test_32M.dbf' size 10
0M extent management local uniform. size 32M segment space management auto;

Tablespace created.

SQL> create user init_test identified by init_test default tablespace init_test;

User created.

SQL> conn /as sysdba
Connected.
SQL> grant connect,resource to init_test;

Grant succeeded.

SQL> conn init_test/init_test;
Connected.
SQL> create table init_test (id number);

Table created.

SQL>select dbms_metadata.get_ddl('TABLE','INIT_TEST','INIT_TEST') FROM DUAL;

CREATE TABLE "INIT_TEST"."INIT_TEST"
( "ID" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 32M NEXT 32M MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "INIT_TEST" ;

现在我们把这个表导出:

C:\Documents and Settings\Paul Yi>exp init_test/init_test tables=init_test file=
c:\init_test.dmp

Export: Release 9.2.0.8.0 - Production on Thu Jun 5 17:58:02 2008

Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table INIT_TEST 0 rows exported
Export terminated successfully without warnings.

下面建立需要导入的表空间和用户名

C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Thu Jun 5 17:59:28 2008

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> create tablespace imp_init_test logging datafile 'd:\imp_init_test_32M.dbf'
size 100M extent management local segment space management auto;

Tablespace created.

SQL> create user imp_init_test identified by imp_init_test default tablespace im
p_init_test;

User created.

SQL> grant connect,resource to imp_init_test;

Grant succeeded.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

进行导入:

C:\Documents and Settings\Paul Yi>imp system/abcdefg@primary fromuser=init_test
touser=imp_init_test file=c:\init_test.dmp buffer=10240000

Import: Release 9.2.0.8.0 - Production on Thu Jun 5 18:02:17 2008

Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by INIT_TEST, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing INIT_TEST's objects into IMP_INIT_TEST
. . importing table "INIT_TEST" 0 rows imported
Import terminated successfully without warnings.

SQL>conn imp_init_test/imp_init_test;

SQl> select dbms_metadata.get_ddl('TABLE','INIT_TEST','IMP_INIT_TEST') FROM DUAL;

CREATE TABLE "IMP_INIT_TEST"."INIT_TEST"
( "ID" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 32M NEXT 32M MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "IMP_INIT_TEST" ;

可以看到存储参数没变

并且看下空间容量

SQL>
SQL> select a.tablespace_name,a.total as total_mb,b.free as free_mb,a.total-b.free as used_mb from
2 (select tablespace_name,sum(round(bytes/1024/1024)) total from DBA_DATA_FILES group by tablespace_name) a,
3 (select tablespace_name,sum(round(bytes/1024/1024)) free from DBA_FREE_SPACE group by tablespace_name) b
4 where a.tablespace_name=b.tablespace_name and a.tablespace_name in ('INIT_TEST','IMP_INIT_TEST');

TABLESPACE_NAME                  TOTAL_MB    FREE_MB    USED_MB
------------------------------ ---------- ---------- ----------
IMP_INIT_TEST                                         100         68         32
INIT_TEST                                                   100         64         36

可以看到占用的就是init extent的大小

这个时候我们改小imp_init_test用户下init_test表的大小 :

删除重建表即可:

C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Thu Jun 5 18:09:52 2008

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> conn imp_init_test/imp_init_test
Connected.
SQL> drop table init_test;

Table dropped.

SQL> CREATE TABLE "IMP_INIT_TEST"."INIT_TEST"
2 ( "ID" NUMBER
3 );

Table created.

SQL> select dbms_metadata.get_ddl('TABLE','INIT_TEST','IMP_INIT_TEST') FROM DUAL;

CREATE TABLE "IMP_INIT_TEST"."INIT_TEST"
( "ID" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 64k NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "IMP_INIT_TEST" ;

再查下表空间容量:

select a.tablespace_name,a.total as total_mb,b.free as free_mb,a.total-b.free as used_mb from
2 (select tablespace_name,sum(round(bytes/1024/1024)) total from DBA_DATA_FILES group by tablespace_name) a,
3 (select tablespace_name,sum(round(bytes/1024/1024)) free from DBA_FREE_SPACE group by tablespace_name) b
4 where a.tablespace_name=b.tablespace_name and a.tablespace_name in ('INIT_TEST','IMP_INIT_TEST');

TABLESPACE_NAME                  TOTAL_MB    FREE_MB    USED_MB
------------------------------ ---------- ---------- ----------
IMP_INIT_TEST                                           100        100          0
INIT_TEST                                                     100         64         36

呵呵新表空间变成0 生效了吧

迁移表结构时存储空间过大问题