-- ts.createResizeCmds.sql
-- Purpose: Create Resize Cmds for Destination DB.
set verify off
set linesize 140
COL tablespace_name FORMAT a35
COL bytes FORMAT 999999999999
prompt
prompt +---------------------------------------+
prompt | Create Resize Cmds for Destination DB |
prompt +---------------------------------------+
prompt
prompt * The commands created are typically used prior to an import on the destination DB.
prompt * It is assumed all the tablespaces on the destination are using BFTS.
prompt
SELECT
'ALTER TABLESPACE ' || df.tablespace_name || ' RESIZE ' || df.totalspace || 'm;' "Cmd"
FROM
(select tablespace_name,round(sum(bytes) / 1048576) TotalSpace
from dba_data_files group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments group by tablespace_name) tu
WHERE df.tablespace_name = tu.tablespace_name
AND df.tablespace_name NOT IN ('SYSTEM','SYSAUX')
AND df.tablespace_name NOT LIKE 'UNDO%'
ORDER BY df.tablespace_name ASC;
