查看以及修改表空间占用情况

查看表空间占用情况

Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name order by Sum(bytes)/1024/1024 desc

修改表空间大小

/*
以前从平台1中分离出来的数据库，库中许多表表数据为空，但表初始大小很大
此脚本批量更新这种表，修改表初始大小64K
1.遍历表大小大于10M的表
2.判断是否有数据
3.针对没有数据的情况，修改表初始大小为64K
*/
declare
Type cur_ref Is Ref Cursor;
rcursor cur_ref;
i integer:=0;
sTableName varchar2(200);
sTableSpace varchar2(200);
sTmpSql varchar2(4000);
begin
sTmpSql := ‘select segment_name from user_extents where segment_type = ”TABLE” group by segment_name having sum(bytes)/1024/1024 > 10 order by sum(bytes)/1024/1024 desc’;
open rcursor for sTmpSql;
loop
fetch rcursor into sTableName;
exit when rcursor%notfound;
sTmpSql := ‘select count(1) from ‘||sTableName||”;
execute immediate sTmpSql into i;
if i=0 then
sTmpSql := ‘select t.TABLESPACE_NAME from user_tables t where t.TABLE_NAME = ”’||sTableName||””;
execute immediate sTmpSql into sTableSpace ;
sTmpSql := ‘alter table ‘||sTableName||’ MOVE TABLESPACE ‘||sTableSpace||’ storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)’;
execute immediate sTmpSql;
end if;
end loop;
end;