EPADMRT > @resizea
CURRENT_MEG SAVINGS ID TABLESPACE_NAME RESIZE_COMMAND
----------- ---------- ----- --------------- ---------------------------------------------------------------------------------------
30000 -1 3 UNDO_TS alter database datafile '/var/oradata03/PADMRT/PADMRT_UNDO_01.dbf' resize 30001m;
30000 -1 19 UNDO_TS alter database datafile '/var/oradata04/PADMRT/PADMRT_UNDO_02.dbf' resize 30001m;
20000 -1 11 OPLA_DATA alter database datafile '/var/oradata01/PADMRT/OPLA_DATA01.dbf' resize 20001m;
15360 -1 8 AGILEODM_INDX alter database datafile '/var/oradata01/PADMRT/AGILEODM_INDX_DATA02.dbf' resize 15361m;
20480 -1 13 AGILEODM alter database datafile '/var/oradata02/PADMRT/AGILEODM_DATA04.dbf' resize 20481m;
5120 -1 7 AGILEODM_INDX alter database datafile '/var/oradata01/PADMRT/AGILEODM_INDX_DATA01.dbf' resize 5121m;
20480 -1 14 AGILEODM alter database datafile '/var/oradata02/PADMRT/AGILEODM_DATA05.dbf' resize 20481m;
32767.1875 -.8125 6 AGILEODM alter database datafile '/var/oradata01/PADMRT/AGILEODM_DATA01.dbf' resize 32768m;
13236 0 5 AGILEODI alter database datafile '/var/oradata01/PADMRT/AGILEODI_DATA01.dbf' resize 13236m;
22784 6 9 AGILEODI alter database datafile '/var/oradata02/PADMRT/AGILEODI_DATA02.dbf' resize 22778m;
32767 30 10 AGILEODM alter database datafile '/var/oradata02/PADMRT/AGILEODM_DATA02.dbf' resize 32737m;
10240 42 20 AGILEODI alter database datafile '/var/oradata01/PADMRT/AGILEODI_DATA03.dbf' resize 10198m;
3000 51 16 VQA_DATA alter database datafile '/var/oradata03/PADMRT/VQA_DATA02.dbf' resize 2949m;
3000 72 15 VQA_DATA alter database datafile '/var/oradata01/PADMRT/VQA_DATA01.dbf' resize 2928m;
3000 79 18 VQA_DATA alter database datafile '/var/oradata04/PADMRT/VQA_DATA04.dbf' resize 2921m;
1335 151 4 USERS alter database datafile '/var/oradata01/PADMRT/users01.dbf' resize 1184m;
10240 202 21 AGILEODI alter database datafile '/var/oradata01/PADMRT/AGILEODI_DATA04.dbf' resize 10038m;
6144 938 2 SYSAUX alter database datafile '/var/oradata02/PADMRT/PLMDM_SYSAUX_DATA1_01.dbf' resize 5206m;
4096 1058 1 SYSTEM alter database datafile '/var/oradata02/PADMRT/PLMDM_SYS_DATA1_01.dbf' resize 3038m;
32767 1391 22 OPLA_DATA alter database datafile '/var/oradata01/PADMRT/OPLA_DATA02.dbf' resize 31376m;
10000 7053 17 VQA_DATA alter database datafile '/var/oradata03/PADMRT/VQA_DATA03.dbf' resize 2947m;
32767 7492 12 AGILEODM alter database datafile '/var/oradata02/PADMRT/AGILEODM_DATA03.dbf' resize 25275m;
32767 8749 23 AGILEODM alter database datafile '/var/oradata03/PADMRT/AGILEODM_DATA06.dbf' resize 24018m;
The full resizea.sql follows:
set termout off
drop table alan99;
set pages 50
set wrap off
col resize_command for a180
col id for 9999
create table alan99 as
select max(block_id+blocks) block_id,file_id from dba_extents
group by file_id;
column tablespace_name format a15
column pct format 999.99
set wrap off
set lines 320
set trunc off
column file_id format 999
set termout on
select b.bytes/1024/1024 CURRENT_meg,b.bytes/1024/1024-trunc(c.block_id*8192/1024/1024+1) savings,
b.file_id id,b.tablespace_Name,'alter database datafile '''||b.file_name||''' resize '
||trunc(c.block_id*8192/1024/1024+1)||'m;' resize_command
from
dba_data_files b, alan99 c
where b.file_id=c.file_id
order by 2;
set termout off
drop table alan99;
set termout on