【TABLESPACE】怎么使用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';
    
11. 
    
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';
    
11. 
    
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;
    
11. 
    
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