1. drop表后,要执行如下语句,否则会留在垃圾堆中。
execute immediate 'purge recyclebin';
或者drop table XXX purge
删除分区后,自己就会回收空间。
2. 数据表空间设置为AUTOEXTEND后,会逐步表变大的,不会自动缩小了。
需要执行如下命令,
ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\ORADB\BA.DBF ' RESIZE 1000M
上个命令有效前提是表空间有足够的空闲空间。用如下命令获取:
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;
3. 临时表空间调整
ALTER TABLESPACE TEMP SHRINK tempfile 'F:\APP\BRUCE\ORADATA\NETMAX\TEMP01.DBF' keep 100M;
4. 整理表的HWM
declare
cursor v_cur is
select table_name from tabs;
v_tablename varchar2(100);
v_sql1 varchar2(1000);
v_sql2 varchar2(1000);
begin
open v_cur;
loop
fetch v_cur
into v_tablename;
v_sql1 := 'alter table ' || v_tablename || ' enable row movement';
v_sql2 := 'alter table ' || v_tablename || ' shrink space';
dbms_output.put_line(v_tablename);
execute immediate v_sql1;
execute immediate v_sql2;
exit when v_cur%notfound;
end loop;
end;