oracledba.help
Scripts

Oracle DBA Scripts

-- 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;