修改oracle某个表的初始空间大小

一个表的数据比较多时，他的INITIAL_EXTENT也会比较大，如果delete删除数据，该表的INITIAL_EXTENT不会变，还是很大，造成占用表空间资源浪费。

–查看表空间的使用情况

SELECT A.TABLESPACE_NAME,      
       FILENUM,   
       TOTAL "TOTAL (MB)",  
       F.FREE "FREE (MB)",
       TO_CHAR(ROUND(FREE * 100 / TOTAL, 2), '990.00') "FREE%", 
       TO_CHAR(ROUND((TOTAL - FREE) * 100 / TOTAL, 2), '990.00') "USED%",    
       ROUND(MAXSIZES, 2) "MAX (MB)"
  FROM (SELECT TABLESPACE_NAME,          
               COUNT(FILE_ID) FILENUM,        
               SUM(BYTES / (1024 * 1024)) TOTAL,          
               SUM(MAXBYTES) / 1024 / 1024 MAXSIZES      
          FROM DBA_DATA_FILES       
         GROUP BY TABLESPACE_NAME) A,     
       (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / (1024 * 1024))) FREE     
          FROM DBA_FREE_SPACE      
         GROUP BY TABLESPACE_NAME) F
 WHERE A.TABLESPACE_NAME = F.TABLESPACE_NAME

–查看表占用的空间大小 INITIAL_EXTENT

SELECT bytes/1024/1024 ||'MB' TABLE_SIZE ,u.* FROM USER_SEGMENTS U WHERE U.SEGMENT_NAME=upper('dep_info');

–查看oracle某个用户占用的表空间大小

select sum(BYTES) / 1024 / 1024/1024 as SIZE_G from dba_segments tt where tt.owner=upper('bvrfis_panjin1124');

–查看用户下所有表的初始空间

 select * from all_tables a where a.OWNER = upper('test');

–修改DEP_INFO表的INITIAL_EXTENT初始空间为64k

 alter table DEP_INFO move storage(initial 64k);