首页 » ORACLE 9i-23ai » 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)评论关闭。