oracle数据库 修改表空间数据文件大小，优化存储

查询数据文件存储使用情况

/**  示例1 只查询*/
select b.file_id　　文件ID,
       　　b.tablespace_name　　表空间,
       　　b.file_name　　　　　物理文件名,
       　　b.bytes / 1024 / 1024　　　　　　　总字节数,
       　　(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024　　　已使用,
       　　sum(nvl(a.bytes, 0)) / 1024 / 1024　　　　　　　　剩余,
       　　round(sum(nvl(a.bytes, 0)) / (b.bytes) * 100, 2) 　剩余百分比 　　from dba_free_space a,
       dba_data_files b 　　where a.file_id = b.file_id 　　group by b.tablespace_name,
       b.file_name,
       b.file_id,
       b.bytes 　　order by b.tablespace_name;

/** 示例2 附带查询压缩脚本*/
select a.file#,
a.name,
a.bytes / 1024 / 1024 CurrentMB,
ceil(HWM * a.block_size) / 1024 / 1024 ResizeTo,
(a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB,
'alter database datafile ''' || a.name || ''' resize ' ||
ceil(HWM * a.block_size) / 1024 / 1024 || 'M;' ResizeCmd
from v$datafile a,
(SELECT file_id, MAX(block_id + blocks - 1) HWM
FROM DBA_EXTENTS
GROUP BY file_id) b
where a.file# = b.file_id(+)
And (a.bytes - HWM * a.block_size) >0
and rownum < 10

修改数据文件容量语句

alter database datafile 'D:\DSINSTALL\ORACLE\ORADATA\ORCL\USERS_DATA01.DBF' resize 10M;

楼主在此处遇见了一个问题，在清理了一大批数据后，发现表空间的存储大小基本没变，强行修改数据文件大小也不行，会提示“资源正在占用”，后面了解到，在删除数据时最好使用下列语句：

/* 清空表i数据*/
truncate table table_name;
注：不要使用delete删除，由于oracle自带回滚机制或者oracle处于数据保护机制，使用delete删除的数据oracle会临时保存，类似于假删除/回收站
/* 删除用户及附属数据*/
drop user user_name descade;

如果数据表曾经存在大数据情况下，还需要修改表的初始化STORAGE值，语句如下，该语句的含义是修改表/索引的初始值为64k，每次增长的幅度为32k

--表
select 'ALTER TABLE '||owner||'.'||table_name||' MOVE TABLESPACE '||tablespace_name||' STORAGE(INITIAL 64K NEXT 32K);' 
  from dba_tables 
 where owner='AA' and initial_extent>65536  
 
 
--索引
select 'ALTER INDEX '||owner||'.'||index_name||' REBUILD STORAGE(INITIAL 64K NEXT 32K);' 
  from dba_indexes 
 where owner='AA'  and initial_extent>65536
 
 
--分区表
select 'ALTER table '||table_owner||'.'||table_name||' MOVE PARTITION '||PARTITION_NAME||' STORAGE(INITIAL 64K NEXT 32K);'   
  from DBA_tab_PARTITIONS 
 where table_owner='AA' and initial_extent>65536
 
 
--分区索引
select 'ALTER INDEX '||index_owner||'.'||index_name||' REBUILD PARTITION '||PARTITION_NAME||' STORAGE(INITIAL 64K NEXT 32K);'   
  from DBA_ind_PARTITIONS 
 where index_owner='AA' and initial_extent>65536

清空当前用户数据库表下存储大于1M的表数据脚本查询语句

select
'truncate table ' || segment_name||';',
bytes/1024/1024 MB,
u.*
from user_extents u
where bytes/1024/1024 > 1

查询数据库中各个表的实际数据存储使用情况

select
segment_name, sum(bytes)/1024/1024 MB
from user_extents u
group by segment_name