首页 » ORACLE » truncate table 会不会释放datafile的空间?如何才能释放?

truncate table 会不会释放datafile的空间?如何才能释放?

下面我将做一这一实验来证明
oracle 10g r2

system@ORCL> create tablespace anbob datafile '/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf' size 40m;

Tablespace created.

anbob@ORCL> create table testspace as select * from all_objects;

Table created.

anbob@ORCL> select segment_name,tablespace_name from user_segments where segment_name='TESTSPACE';

SEGMENT_NAME         TABLESPACE_NAME
-------------------- --------------------
TESTSPACE            TT

anbob@ORCL> select count(*) from testspace;

  COUNT(*)
----------
     40770

anbob@ORCL> insert into testspace select * from testspace;

40770 rows created.

anbob@ORCL> /

81540 rows created.

anbob@ORCL> /

163080 rows created.

anbob@ORCL> select count(*) from testspace;

  COUNT(*)
----------
    326160

anbob@ORCL>exec dbam_stats.gather_table_states(user,'TESTSPACE');


anbob@ORCL> alter table testspace move tablespace anbob;

Table altered.

anbob@ORCL> select tablespace_name,bytes/1024/1024 from user_free_space where tablespace_name='ANBOB';

TABLESPACE_NAME      BYTES/1024/1024
-------------------- ---------------
ANBOB                         2.9375

anbob@ORCL> select dbms_metadata.get_ddl('TABLE','TESTSPACE') from dual;

DBMS_METADATA.GET_DDL('TABLE','TESTSPACE')
--------------------------------------------------------------------------------

  CREATE TABLE "ANBOB"."TESTSPACE"
   (    "OWNER" VARCHAR2(30) NOT NULL ENABLE,
        "OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER NOT NULL ENABLE,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE NOT NULL ENABLE,
        "LAST_DDL_TIME" DATE NOT NULL ENABLE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "ANBOB"
  
看ddl目的在看INITIAL 参数,因为truncate 是会按这个参数指定的大小新分配一个segment

anbob@ORCL> truncate table testspace;

Table truncated.

anbob@ORCL> select tablespace_name,bytes/1024/1024 from user_free_space where tablespace_name='ANBOB';

TABLESPACE_NAME      BYTES/1024/1024
-------------------- ---------------
ANBOB                         39.875

anbob@ORCL> conn system/oracle
Connected.
system@ORCL> alter database  datafile '/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf' resize 5m;

Database altered.

resize 成功,再做一个如果truncate 的表不在表空间的末端看是不是可以?

system@ORCL>conn anbob/anbob
anbob@ORCL> insert into testspace select * from all_objects;
insert into testspace select * from all_objects
*
ERROR at line 1:
ORA-01653: unable to extend table ANBOB.TESTSPACE by 128 in tablespace ANBOB

system@ORCL> alter database  datafile '/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf' autoextend on;

Database altered.

system@ORCL> conn anbob/anbob
Connected.
anbob@ORCL> insert  into testspace select * from all_objects;

40770 rows created.

anbob@ORCL> /

40770 rows created.

anbob@ORCL> insert  into testspace select * from testspace;

81540 rows created.

anbob@ORCL> /

163080 rows created.

anbob@ORCL> commit;

Commit complete.

anbob@ORCL> exec dbms_stats.GATHER_TABLE_STATS(USER,'TESTSPACE');

PL/SQL procedure successfully completed.

anbob@ORCL> select segment_name,bytes/1024/1024 from USER_segments where segment_name='TESTSPACE';

SEGMENT_NAME         BYTES/1024/1024
-------------------- ---------------
TESTSPACE                         37

anbob@ORCL> conn system/oracle
Connected.
system@ORCL>  select file_name,tablespace_name,bytes/1024/1024 mb from dba_data_files where tablespace_name='ANBOB';

FILE_NAME                                                    TABLESPACE_NAME              MB
------------------------------------------------------------ -------------------- ----------
/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf              ANBOB                        38



anbob@ORCL> create table testspace2 tablespace anbob as  select * from testspace;

Table created.

anbob@ORCL> exec dbms_stats.GATHER_TABLE_STATS(USER,'TESTSPACE2');

PL/SQL procedure successfully completed.

anbob@ORCL> conn system/oracle
Connected.
system@ORCL>  select file_name,tablespace_name,bytes/1024/1024 mb from dba_data_files where tablespace_name='ANBOB';

FILE_NAME                                                    TABLESPACE_NAME              MB
------------------------------------------------------------ -------------------- ----------
/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf              ANBOB                   74.0625

system@ORCL> select tablespace_name,bytes/1024/1024 from dba_free_space where tablespace_name='ANBOB';

no rows selected

sys@ORCL> conn anbob/anbob
Connected.
anbob@ORCL> truncate table TESTSPACE;

Table truncated.

anbob@ORCL> exec dbms_stats.GATHER_TABLE_STATS(USER,'TESTSPACE');

PL/SQL procedure successfully completed.

anbob@ORCL> conn / as sysdba
Connected.
sys@ORCL> select tablespace_name,bytes/1024/1024 from dba_free_sPAce where tablespace_name='ANBOB'; 

TABLESPACE_NAME      BYTES/1024/1024
-------------------- ---------------
ANBOB                        36.9375


sys@ORCL> select file_name,tablespace_name,bytes/1024/1024 mb from dba_data_files where tablespace_name='ANBOB';

FILE_NAME                                                    TABLESPACE_NAME              MB
------------------------------------------------------------ -------------------- ----------
/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf              ANBOB                   74.0625

sys@ORCL> alter database  datafile '/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf' resize 40m;
alter database  datafile '/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf' resize 40m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

sys@ORCL> ALTER TABLESPACE anbob COALESCE;

Tablespace altered.

sys@ORCL> alter database  datafile '/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf' resize 40m;
alter database  datafile '/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf' resize 40m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

anbob@ORCL> drop table TESTSPACE purge;

Table dropped.

sys@ORCL> alter database  datafile '/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf' resize 40m;
alter database  datafile '/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf' resize 40m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

再造一个表出来,让他覆盖刚才truncate的空间,从datafile size 可以看出;
sys@ORCL> conn anbob/anbob
Connected.
anbob@ORCL> create table testspace tablespace anbob as select * from testspace2 ;

Table created.

anbob@ORCL> conn / as sysdba
Connected.
sys@ORCL> select file_name,tablespace_name,bytes/1024/1024 mb from dba_data_files where tablespace_name='ANBOB';

FILE_NAME                                                    TABLESPACE_NAME              MB
------------------------------------------------------------ -------------------- ----------
/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf              ANBOB                   74.0625

sys@ORCL> select tablespace_name,bytes/1024/1024 from dba_free_sPAce where tablespace_name='ANBOB'; 

no rows selected

解决方法 exp/imp expdp/impdp导出那些表

anbob@ORCL> drop  table testspace purge;

Table dropped.

anbob@ORCL> drop  table testspace2 purge;

Table dropped.

anbob@ORCL> conn system/oracle
Connected.
system@ORCL> alter database  datafile '/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf' resize 40m;

Database altered.

system@ORCL> alter database  datafile '/u01/app/oracle/oradata/ORCL/datafile/anbob.dbf' resize 5m;

Database altered.

note:
1,truncate table,不会立即清除数据从存储中,只做标记为无效,后面的事务可以覆盖使用,这就是为什么有些老牛做的工具可以找回truncate的数据,原理就是他们把truncate的数据可以从数据文件中dump出来,再抽取,导入。所以你truncate了表发现错了千万不要再动数据库了找人恢复
2,truncate table 的数据如果在表空间的末端,那么datafile 是可以resize的,否则是不可以的
3,如果segment又没在文件的末端,存储比较紧张,那么解决方法也有转储出来,如exp,把表drop 掉,再imp,这样就可以resize了,因为表空间上已经没有可以阻挡的segment type(如果dump过block的话会发现那些类型的)
4,如果表空间没有free block,在dba_free_space视图是没有那条表空间的记录的

打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Byron Arico | #1
    2011-12-21 at 09:07

    I signed up to your rss feed!