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

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

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

c;f[k3vc0Connected to:
T3LIr%X]0Oracle9i Enterprise Edition Release 9.2.0.8.0 - ProductionITPUB个人空间eS3r!}(~%L:l3b5Z%?C~
With the Partitioning, OLAP and Oracle Data Mining options
C\)shF2L"J ~0JServer Release 9.2.0.8.0 - Production

SQL> create tablespace init_test logging datafile 'd:\init_test_32M.dbf' size 10ITPUB个人空间3M%f,^OJdv ZR
0M extent management local uniform. size32Msegment space management auto;

Tablespace created.

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

ITPUB个人空间$~L6M8q.v}
User created.

SQL> conn /as sysdba
M}L!Et/\x0Connected.ITPUB个人空间JC z4Qedz
SQL> grant connect,resource to init_test;

Grant succeeded.

SQL> conn init_test/init_test;ITPUB个人空间\ v9Uqx9C!J5s)n
Connected.
-|(mf @#F0SQL> 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"
`x*Ez,}Z:S.g0   ( "ID" NUMBER
E^0@*W5pOe&D!z0   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGITPUB个人空间_Wx [,H0}p#a
  STORAGE(INITIAL32MNEXT32MMINEXTENTS 1 MAXEXTENTS 2147483645ITPUB个人空间_t8Fx|oA
o
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
g9cL-h[w.WsR gQ0  TABLESPACE"INIT_TEST";

现在我们把这个表导出:

C:\Documents and Settings\Paul Yi>exp init_test/init_test tables=init_test file=
.QdM6g:G#eO/D0c:\init_test.dmp

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

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

ITPUB个人空间 l[7V&Z].`$Lm4O+y
Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
Jn6LO OF0With the Partitioning, OLAP and Oracle Data Mining optionsITPUB个人空间3YIze%R-js
JServer Release 9.2.0.8.0 - Production
i$DB,b.H0Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...ITPUB个人空间8t
|"j(Sy^+n
. . exporting table                      INIT_TEST          0 rows exportedITPUB个人空间/h'v;~5`n1M8ZL!D)X
Export terminated successfully without warnings.
.VT3s0R$a%U0

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

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

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

y;}ft[Dv0Connected to:
0L)[ {%g}:t0Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
T#kCCz:Z*P-a%wtW0With the Partitioning, OLAP and Oracle Data Mining optionsITPUB个人空间IeT*sL(ws5C
JServer Release 9.2.0.8.0 - Production

SQL> create tablespace imp_init_test logging datafile 'd:\imp_init_test_32M.dbf'ITPUB个人空间,u Y`um'i4?*o
 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
-^2n E
W#\"B.h n0p_init_test;

User created.

SQL> grant connect,resource to imp_init_test;

Grant succeeded.

SQL> exitITPUB个人空间cD3Z/cq"Eb.P
Q5`
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
a7PNy,XI6w3a u0With the Partitioning, OLAP and Oracle Data Mining options
K_
gs!l?g? w0JServer Release 9.2.0.8.0 - Production

进行导入:

C:\Documents and Settings\Paul Yi>impsystem/abcdefg@primaryfromuser=init_test
BzLW6[$g n"A3w0GM R0touser=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

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

B7bK}X+AIx(C+T0Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - ProductionITPUB个人空间~(z9f?}#~!_7O
With the Partitioning, OLAP and Oracle Data Mining options
*cD:ws,R-Cv0JServer 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
XaVp"zU s0. importing INIT_TEST's objects into IMP_INIT_TESTITPUB个人空间-G.Q3R qH
nA
. . importing table                    "INIT_TEST"          0 rows imported
{&C[T$@B'tW6x:SIfe0Import 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"
+s&O)t_/Sy#c&}s5_ `0   ( "ID" NUMBER
YK9djUP-p#w0   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
C@P%hM0  STORAGE(INITIAL 32MNEXT 32MMINEXTENTS 1 MAXEXTENTS 2147483645
JVZ*?4|`nt,eQ1G0  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
j])B"c+Y0  TABLESPACE "IMP_INIT_TEST" ;

可以看到 存储参数没变

并且看下空间容量

SQL>
B*m1Q
_u1H4N0SQL> select a.tablespace_name,a.total as total_mb,b.free as free_mb,a.total-b.free as used_mb from
-XI!`R1Y&G$Y-z'J0  2  (select tablespace_name,sum(round(bytes/1024/1024)) total from DBA_DATA_FILES group by tablespace_name) a,ITPUB个人空间#U[7x/J\:J
  3  (select tablespace_name,sum(round(bytes/1024/1024)) free from DBA_FREE_SPACE group by tablespace_name) b
`,vq,k,Pm9v!z0  4  where a.tablespace_name=b.tablespace_name and a.tablespace_name in ('INIT_TEST','IMP_INIT_TEST');
~W`3fr Wh$?0 ITPUB个人空间I-z6rx+X C]9b
TABLESPACE_NAME                  TOTAL_MB    FREE_MB    USED_MBITPUB个人空间 w~` ]5v g-n
------------------------------ ---------- ---------- ----------ITPUB个人空间"O h'fE]7AM
IMP_INIT_TEST                                          100         68         32ITPUB个人空间r2s:@d)sq:t
INIT_TEST                                                   100         64         36ITPUB个人空间 v6gsL2uk(D

可以看到 占用的就是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

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

ITPUB个人空间6jaX"R6V^
Connected to:
Rv5d6V8Q*M4@Kf}0Oracle9i Enterprise Edition Release 9.2.0.8.0 - ProductionITPUB个人空间Q0Z4\CR1Vz
With the Partitioning, OLAP and Oracle Data Mining options
5D M3jtH0F~0JServer Release 9.2.0.8.0 - Production

SQL> conn imp_init_test/imp_init_testITPUB个人空间n2_2ir7[$X2Qqgt
Connected.ITPUB个人空间(UGN/^L|;oW:J
SQL> drop table init_test;

Table dropped.

SQL> CREATE TABLE "IMP_INIT_TEST"."INIT_TEST"ITPUB个人空间:\c\k3_
  2     (       "ID" NUMBER
wp4u%HW#w/}1K Y;w0  3     );

Table created.

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

CREATE TABLE "IMP_INIT_TEST"."INIT_TEST"
E
o3n:SC @ pSRda0   ( "ID" NUMBER
)RUQq&b.M:Dz0   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGITPUB个人空间*`5E9{#Kts#E
  STORAGE(INITIAL 64k NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
uE!xHm Zc0  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
P6C$TLH0  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 fromITPUB个人空间p/E*{-q4|
  2  (select tablespace_name,sum(round(bytes/1024/1024)) total from DBA_DATA_FILES group by tablespace_name) a,
D[9a#V2} c+B,t1E(M0  3  (select tablespace_name,sum(round(bytes/1024/1024)) free from DBA_FREE_SPACE group by tablespace_name) bITPUB个人空间)Z&Y3Bmeu
  4  where a.tablespace_name=b.tablespace_name and a.tablespace_name in ('INIT_TEST','IMP_INIT_TEST');
g.~"\5pY\a2e0 ITPUB个人空间BRZ
V5h,?:e
|
TABLESPACE_NAME                  TOTAL_MB    FREE_MB    USED_MBITPUB个人空间qQ j.`~-C#}%q
------------------------------ ---------- ---------- ----------
R!m$S_|z0IMP_INIT_TEST                                           100        100          0ITPUB个人空间9}wxc7M1^
INIT_TEST                                                     100         64         36

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