遷移表結構時儲存空間過大問題
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.
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
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
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
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
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 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
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
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
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
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 生效了吧
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7199859/viewspace-332625/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- innodb表空間儲存結構
- 透過xtts遷移單例項檔案系統表空間到RAC ASM儲存表空間TTS單例ASM
- 通過xtts遷移單例項檔案系統表空間到RAC ASM儲存表空間TTS單例ASM
- 【實驗】重建臨時表空間解決臨時表空間過大問題
- 表空間遷移
- 遷移表空間
- 利用RMAN遷移表空間碰到的問題(五)
- 利用RMAN遷移表空間碰到的問題(四)
- 利用RMAN遷移表空間碰到的問題(三)
- 利用RMAN遷移表空間碰到的問題(二)
- 利用RMAN遷移表空間碰到的問題(一)
- 轉:Oracle 臨時表空間過大問題解決Oracle
- 【遷移】表空間transport
- RMAN遷移表空間
- ORACLE表批量遷移表空間Oracle
- 遷移SYSTEM表空間為本地管理表空間
- 【實驗】RESIZE方法解決臨時表空間過大問題
- expdp/impdp 遷移表空間
- 【UNDO】使用重建UNDO表空間方法解決UNDO表空間過大問題
- 跨平臺表空間遷移(傳輸表空間)
- 【儲存管理】建立臨時表空間組、建立臨時表空間組及使用
- 遷移表到新的表空間
- oracle系統表空間過大問題處理Oracle
- table/index/LOBINDEX遷移表空間Index
- lob欄位表空間遷移
- 基於可傳輸表空間的表空間遷移
- 【儲存管理】表空間概念
- Oracle 不同平臺間表空間遷移Oracle
- dbms_lob儲存過程導致臨時表空間100%儲存過程
- MySQL 遷移表空間,備份單表MySql
- 表、索引遷移表空間alter table move索引
- 空間遷移
- Oracle中表空間、表、索引的遷移Oracle索引
- 表空間遷移辦法補充
- 使用RMAN簡單遷移表空間
- 資料庫物件遷移表空間資料庫物件
- 【儲存管理】建立永久表空間
- 資料遷移(1)——通過資料泵表結構批量遷移