问题：怎么使用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命令，表空间变小，可以为其他表空间分配空间了
