呵呵,truncate是清空表的,1,先看表属于哪个表空间SQL> select table_name,tablespace_name from user_tables where table_name='T1';TABLE_NAME TABLESPACE_NAME ------------------------------ -------------------- T1 USERS2,查看表空间大小SQL> select tablespace_name,file_name,bytes/1024/1024 mb from dba_data_files;TABLESPACE_NAME FILE_NAME MB -------------------- -------------------------------------------------- ---- SYSTEM /oracle/u01/app/oracle/oradata/EMREP/system.dbf 300 UNDOTBS /oracle/u01/app/oracle/oradata/EMREP/undotbs.dbf 100 SYSAUX /oracle/u01/app/oracle/oradata/EMREP/sysaux.dbf 100 USERS /oracle/u01/app/oracle/oradata/EMREP/user.dbf 603,查看表空已使用大小 select a.tablespace_name,total,free,total-free used from ( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files group by tablespace_name) a, ( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space group by tablespace_name) b where a.tablespace_name=b.tablespace_name; 4,更改表空间大小(更改的值要大于已使用的表空间的大小)alter database datafile '/oracle/u01/app/oracle/oradata/EMREP/user.dbf' resize 50m;5,查看更改结果SQL> select tablespace_name,file_name,bytes/1024/1024 mb from dba_data_files;TABLESPACE_NAME FILE_NAME MB -------------------- -------------------------------------------------- ---- SYSTEM /oracle/u01/app/oracle/oradata/EMREP/system.dbf 300 UNDOTBS /oracle/u01/app/oracle/oradata/EMREP/undotbs.dbf 100 SYSAUX /oracle/u01/app/oracle/oradata/EMREP/sysaux.dbf 100 USERS /oracle/u01/app/oracle/oradata/EMREP/user.dbf 50
直接truncate table A就可以了,delete数据是不会减小所占表空间大小的
select bytes/1024/8 from dba_segments where owner='用户名' and table_name='表名字'ms 这样应该可以
当然 truncate 之前, 你的表记录 要多一些, 比方,一万条以上然后 truncate 之后 你在重新看一下大小,truncate 之后hwm 也会降低的
执行
alter table A move;
或者
alter table A enable row movement;
alter table A shrink space;
如果想减少整体表空间大小,企业管理器界面直接将之减小即可.
------------------------------ --------------------
T1 USERS2,查看表空间大小SQL> select tablespace_name,file_name,bytes/1024/1024 mb from dba_data_files;TABLESPACE_NAME FILE_NAME MB
-------------------- -------------------------------------------------- ----
SYSTEM /oracle/u01/app/oracle/oradata/EMREP/system.dbf 300
UNDOTBS /oracle/u01/app/oracle/oradata/EMREP/undotbs.dbf 100
SYSAUX /oracle/u01/app/oracle/oradata/EMREP/sysaux.dbf 100
USERS /oracle/u01/app/oracle/oradata/EMREP/user.dbf 603,查看表空已使用大小
select a.tablespace_name,total,free,total-free used from
( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
group by tablespace_name) a,
( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name; 4,更改表空间大小(更改的值要大于已使用的表空间的大小)alter database datafile '/oracle/u01/app/oracle/oradata/EMREP/user.dbf' resize 50m;5,查看更改结果SQL> select tablespace_name,file_name,bytes/1024/1024 mb from dba_data_files;TABLESPACE_NAME FILE_NAME MB
-------------------- -------------------------------------------------- ----
SYSTEM /oracle/u01/app/oracle/oradata/EMREP/system.dbf 300
UNDOTBS /oracle/u01/app/oracle/oradata/EMREP/undotbs.dbf 100
SYSAUX /oracle/u01/app/oracle/oradata/EMREP/sysaux.dbf 100
USERS /oracle/u01/app/oracle/oradata/EMREP/user.dbf 50