问题：怎么使用resize使表空间变小或者说怎么让表空间resize到一个合理的值，并且腾出部分空间

查看当前块（标准块）大小

点击( 此处)折叠或打开

SQL > set verify off
SQL > column file_name format a50 word_wrapped
SQL > column smallest format 999 ,990 heading "Smallest|Size|Poss."
SQL > column currsize format 999 ,990 heading "Current|Size"
SQL > column savings format 999 ,990 heading "Poss.|Savings"
SQL > break on report
SQL > compute sum of savings on report
SQL >
SQL > column value new_val blksize
SQL > select value from v$parameter where name = 'db_block_size' ;
VALUE
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
8192

查看某个表空间可以收缩多少空间

点击( 此处)折叠或打开

SQL > select file_name ,
2 ceil ( (nvl (hwm ,1 ) * & &blksize ) /1024/1024 ) smallest ,
3 ceil ( blocks * & &blksize/1024/1024 ) currsize ,
4 ceil ( blocks * & &blksize/1024/1024 ) -
5 ceil ( (nvl (hwm ,1 ) * & &blksize ) /1024/1024 ) savings
6 from dba_data_files a ,
7 ( select file_id , max (block_id +blocks +7 ) hwm
8 from dba_extents where tablespace_name = 'TEST'
9 group by file_id ) b
10 where a .file_id = b .file_id ( + ) and tablespace_name = 'TEST' ;
Smallest
Size Current Poss .
FILE_NAME Poss . Size Savings
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
/oracle/app/oracle/oradata/mydb/test01 .dbf 139 2 ,048 1 ,909
/backup/test02 .dbf 36 1 ,024 988
- - - - - - - -
sum 2 ,897 --可以腾出空间的总大小2897M

生成操作语句

点击( 此处)折叠或打开

SQL > column cmd format a75 word_wrapped
SQL > select 'alter database datafile ' '' | |file_name | | '' ' resize ' | |
2 ceil ( (nvl (hwm ,1 ) * & &blksize ) /1024/1024 ) | | 'm;' cmd
3 from dba_data_files a ,
4 ( select file_id , max (block_id +blocks +7 ) hwm
5 from dba_extents where tablespace_name = 'TEST'
6 group by file_id ) b
7 where b .file_id = a .file_id ( + )
8 and ceil ( blocks * & &blksize/1024/1024 ) -
9 ceil ( (nvl (hwm ,1 ) * & &blksize ) /1024/1024 ) > 0 ;
CMD
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
alter database datafile '/backup/test02.dbf' resize 36m ;
alter database datafile '/oracle/app/oracle/oradata/mydb/test01.dbf' resize
139m

执行上述resize命令，表空间变小，可以为其他表空间分配空间了

ITPub博客

【TABLESPACE】怎么使用resize使表空间变小