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

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

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

点击(此处)折叠或打开

1. SQL> set verify off
   
2. SQL> column file_name format a50 word_wrapped
3. SQL> column smallest format 999,990 heading “Smallest|Size|Poss.”
4. SQL> column currsize format 999,990 heading “Current|Size”
5. SQL> column savings format 999,990 heading “Poss.|Savings”
6. SQL> break on report
7. SQL> compute sum of savings on report
8. SQL>
9. SQL> column value new_val blksize
10. SQL> select value from v$parameter where name = ‘db_block_size’;
12. VALUE
13. ––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––
14. 8192

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

点击(此处)折叠或打开

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

生成操作语句

点击(此处)折叠或打开

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

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