[TABLESPACE] how to use resize to make tablespaces smaller

Question: how to use resize to make the tablespace smaller, or how to make the tablespace resize to a reasonable value, and free up some space

View the current block (standard block) size

Click (here) to collapse or open

SQL > set verify off

SQL > column file_name format A50 word_wrapped

SQL > column smallest format 999990 heading "Smallest | Size | Poss."

SQL > column currsize format 999990 heading "Current | Size"

SQL > column savings format 999990 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

See how much space a tablespace can shrink

Click (here) to collapse or open

SQL > select file_name

2 ceil ((nvl (hwm,1) * & & blksize) / 1024ax 1024) smallest

3 ceil (blocks*&&blksize/1024/1024) currsize

4 ceil (blocks*&&blksize/1024/1024)-

5 ceil ((nvl (hwm,1) * & & blksize) / 1024ax 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 2048 1909

/ backup/test02.dbf 36 1024 988

-

Sum 2897-the total size that can be vacated is 2897m

Generate operation statement

Click (here) to collapse or open

SQL > column cmd format a75 word_wrapped

SQL > select 'alter database datafile''| | file_name | |''resize' | |

2 ceil ((nvl (hwm,1) * & & blksize) / 1024Accord 1024) | | 'mash' 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) / 1024ax 1024) > 0

CMD

Alter database datafile'/ backup/test02.dbf' resize 36m

Alter database datafile'/ oracle/app/oracle/oradata/mydb/test01.dbf' resize

139m

By executing the above resize command, the tablespace becomes smaller and other tablespaces can be allocated.