Shrink Datafiles – Adjusting HWM

Shrink Datafiles – Adjusting HWM

Scenario:-
Lets suppose the datafile size is 100m with autoexted upto maxsize 32gb.
1) Created a table
2) Inserted 50millions of rows with commit
— Data file size extended from 100mb to 20gb.
3) performed so many DML’s
— Data file size extended from 100mb to >20gb.
4) Truncated Table/Dropped Table.

Space will can be used further to use for other objects in datafile. But as per my requirement when i really won’t use that datafile, then what is the use to keep datafile size > 20gb and which is causing much disk usage at OS level? Instead of that check the HWM of datafile, at what minimum size you can resize it?

Script for MAX-Shrink:-

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size';
/
select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+) order by savings desc
/

In lower versions you can use below query to find out possible savings from each data files of Database.

set linesize 400
col tablespace_name format a15
col file_size format 99999
col file_name format a50
col hwm format 99999
col can_save format 99999
SELECT tablespace_name, file_name, file_size, hwm, file_size-hwm can_save
FROM (SELECT /*+ RULE */ ddf.tablespace_name, ddf.file_name file_name,
ddf.bytes/1048576 file_size,(ebf.maximum + de.blocks-1)*dbs.db_block_size/1048576 hwm
FROM dba_data_files ddf,(SELECT file_id, MAX(block_id) maximum FROM dba_extents GROUP BY file_id) ebf,dba_extents de,
(SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') dbs
WHERE ddf.file_id = ebf.file_id
AND de.file_id = ebf.file_id
AND de.block_id = ebf.maximum
ORDER BY 1,2);

Sample Output of above query:-

                                                    Smallest
                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
-------------------------------------------------- -------- -------- --------
/u02/oradata/cpdevdb/cp_jrnl_idx3.dbf                13,697   32,708   19,011
/u02/oradata/cpdevdb/cp_jrnl_idx2.dbf                13,761   32,748   18,987
/u02/oradata/cpdevdb/cp_jrnl_idx1_01.dbf             13,953   32,748   18,795
/u02/oradata/cpdevdb/cp_jrnl_idx1.dbf                13,953   32,728   18,775
/u02/oradata/cpdevdb/cp_jrnl_idx3_02.dbf             15,681   32,738   17,057
/u02/oradata/cpdevdb/cp_jrnl_idx3_03.dbf             15,681   32,718   17,037
/u02/oradata/cpdevdb/psindex_01.dbf                  11,285   26,718   15,433
/u02/oradata/cpdevdb/cp_jrnl_idx4.dbf                22,913   32,728    9,815
/u02/oradata/cpdevdb/cpled_2010_1.dbf                 7,721   10,858    3,137
/u02/oradata/cpdevdb/cpled_2009_1.dbf                 4,745    6,828    2,083
/u02/oradata/cpdevdb/cpled_2008_1.dbf                 4,873    6,948    2,075
/u02/oradata/cpdevdb/cpled_2009_2.dbf                 4,825    6,728    1,903
/u02/oradata/cpdevdb/cpled_idx_1.dbf                 19,905   21,000    1,095
                                                                     --------
sum                                                                   145,203

By the output i can reclaim space around 141gb of space, without reorganizing any objects. 

Resize Datafiles:-

We can resize datafile up to “Smallest Size Poss” value (or) we can assign any fixed size (or) On top of that we can enable autoextend up to maximum size of datafile.

alter database datafile '/u02/oradata/cpdevdb/cp_jrnl_idx3.dbf' resize 13700m;
alter database datafile '/u02/oradata/cpdevdb/cp_jrnl_idx3.dbf' autoextend on next 10m maxsize 32767m;

After resizing datafile, Possible savings recorded is around 19GB.

Execute above Script to check changes after resizing of datafiles:-

                                                   Smallest
                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
-------------------------------------------------- -------- -------- --------
/u02/oradata/cpdevdb/cp_jrnl_idx3.dbf                13,697   13,700        3
/u02/oradata/cpdevdb/cp_jrnl_idx2.dbf                13,761   13,770        9
/u02/oradata/cpdevdb/cp_jrnl_idx1_01.dbf             13,953   13,970       17
/u02/oradata/cpdevdb/cp_jrnl_idx1.dbf                13,953   13,970       17
/u02/oradata/cpdevdb/cp_jrnl_idx3_02.dbf             15,681   15,690        9
/u02/oradata/cpdevdb/cp_jrnl_idx3_03.dbf             15,681   15,690        9
/u02/oradata/cpdevdb/psindex_01.dbf                  11,285   11,300       15
/u02/oradata/cpdevdb/cp_jrnl_idx4.dbf                22,913   22,920        7
/u02/oradata/cpdevdb/cpled_2010_1.dbf                 7,721    7,730        9
/u02/oradata/cpdevdb/cpled_2009_1.dbf                 4,745    4,750        5
/u02/oradata/cpdevdb/cpled_2008_1.dbf                 4,873    4,900       27
/u02/oradata/cpdevdb/cpled_2009_2.dbf                 4,825    4,850       25
/u02/oradata/cpdevdb/cpled_idx_1.dbf                 19,905   19,910        5
                                                                     --------
sum                                                                       157

In earlier output possible savings is 141gb, Now possible savings is 157mb, So we reclaimed ~141gb of space at OS level.  
Now lets compare OS level free space  Before & After

Before:-

$df -h /u02
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vgemc4-lvu02
                      1.5T  1.3T  161G  89% /u02

After:-

$df -h /u02
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vgemc4-lvu02
                      1.5T  942G  461G  68% /u02

I able to reclaim space by adjusting HWM from two databases around 300GB.  Here you go……………

Note:- I have reclaimed for other databases reside on that server, so you can see much differences before and after.

==============================================

Question:  How do I reduce the size of my Oracle data file sizes?  I want to shrink the size of my data files to reclaim un-used space.  How do I reduce the file sizes?

Answer:  (by John Weeg) You can start reducing the size of your Oracle database files by setting Free Your Space

Conventional database wisdom dictates that we should treat disk space as though it were free, unfettered space waiting to be filled with data. It's a liberating idea, but not entirely practical. Not surprisingly, it's also rife with potential problems. Get too comfortable with the idea of space being "free" and you may suddenly find yourself scrounging around for it.

When setting up a database, for example, a database administrator usually takes educated guesses at the company's space needs, large or small. It's important to point out, however, that those guesses tend to be conservative. The reason? By overestimating the amount of space needed, the administrator is less likely to wind up stuck when he or she first loads all the data. Once the instance runs for a while, it's possible to see just how far off the original estimate of space requirements was. Moreover, the administrator can give back some of that space.

Over-allocation of space at the file level affects the backup/recovery window, file checking times and, most painfully, limits the potential allocation of space to a tablespace that needs the extra room. A simpler solution would be to review the evolution of the script, which lets the administrator know which files can and cannot be resized to create more space.

Alter Database

It's possible to release space from data files but only down to the first block of data. This is done with the 'alter database' command. Rather than go through the tedious process of manually figuring out the command every time it's used, it makes more sense to write a script that will generate this command as needed.

The basic syntax for this command is:

Alter database name datafile 'file_name' resize size;

Where name is the name of the database, file_name is the name of the file andsize is the new size to make this file. We can see this size change in thedba_data_files table as well as from the server.

First, pull in the database name:

Select 'alter database '||a.name
From v$database a;

Once that has been done, it's time to add in data files:

select 'alter database '||a.name||' datafile '''||b.file_name||''''
from v$database a
,dba_data_files b;

While this is closer to the ultimate solution, it's not quite there yet. The question remains: Which data files do you want to alter? At this point, you can use a generally accepted standard, which allows tablespaces to be 70 percent to 90 percent full. If a tablespace is below the 70 percent mark, one way to bring the number up is to de-allocate some of the space.

So how do you achieve percent full? While there are a number of different ways, simple is usually ideal. Here's how it works.

Amount of data file space used:

Select tablespace_name,sum(bytes) bytes_full
From dba_extents
Group by tablespace_name;

Total available data file space:

Select tablespace_name,sum(bytes) bytes_total
From dba_data_files
Group by tablespace_name;

So if we add this with our original statement, we can select on pct_used (less than 70 percent):

select 'alter database '||a.name||' datafile '''||b.file_name||''''
from v$database a
,dba_data_files b
,(Select tablespace_name,sum(bytes) bytes_full
From dba_extents
Group by tablespace_name) c
,(Select tablespace_name,sum(bytes) bytes_total
From dba_data_files
Group by tablespace_name) d
Where b.tablespace_name = c.tablespace_name
And b.tablespace_name = d.tablespace_name
And bytes_full/bytes_total < .7
;

According to the command, a selection has been made based on tablespace. What if you want to resize based on file? It's crucial to remember that multiple files can exist in any tablespace. Plus, only space that is after the last data block can be de-allocated. So the next step should be to find the last data block:

select tablespace_name,file_id,max(block_id) max_data_block_id
from dba_extents
group by tablespace_name,file_id;

Now that the command to find the last data block has been inserted, it is time to find the free space in each file above that last data block:

Select a.tablespace_name,a.file_id,b.bytes bytes_free
From (select tablespace_name,file_id,max(block_id) max_data_block_id
from dba_extents
group by tablespace_name,file_id) a
,dba_free_space b
where a.tablespace_name = b.tablespace_name
and a.file_id = b.file_id
and b.block_id > a.max_data_block_id;

So far, so good. How is it possible, then, to combine commands to ensure the correct amount will be resized? In fact, it's fairly easy.

select 'alter database '||a.name||' datafile '''||b.file_name||'''' ||
' resize '||(bytes_total-bytes_free)
from v$database a
,dba_data_files b
,(Select tablespace_name,sum(bytes) bytes_full
From dba_extents
Group by tablespace_name) c
,(Select tablespace_name,sum(bytes) bytes_total
From dba_data_files
Group by tablespace_name) d
,(Select a.tablespace_name,a.file_id,b.bytes bytes_free
From (select tablespace_name,file_id
,max(block_id) max_data_block_id
from dba_extents
group by tablespace_name,file_id) a
,dba_free_space b
where a.tablespace_name = b.tablespace_name
and a.file_id = b.file_id
and b.block_id > a.max_data_block_id) e
Where b.tablespace_name = c.tablespace_name
And b.tablespace_name = d.tablespace_name

Double Checking the Data file shrink

Now, the thing to do is ensure that the right amount of space - not too much, not too little - has been de-allocated. The rule of thumb to follow: Do not go above 70 percent of the tablespace being used. If you have already pulled out how much is used from dba_extents, you can simply add a check to your statement:

select 'alter database '||a.name||' datafile '''||b.file_name||'''' ||
' resize '||greatest(trunc(bytes_full/.7)
,(bytes_total-bytes_free))
from v$database a
,dba_data_files b
,(Select tablespace_name,sum(bytes) bytes_full
From dba_extents
Group by tablespace_name) c
,(Select tablespace_name,sum(bytes) bytes_total
From dba_data_files
Group by tablespace_name) d
,(Select a.tablespace_name,a.file_id,b.bytes bytes_free
From (select tablespace_name,file_id
,max(block_id) max_data_block_id
from dba_extents
group by tablespace_name,file_id) a
,dba_free_space b
where a.tablespace_name = b.tablespace_name
and a.file_id = b.file_id
and b.block_id > a.max_data_block_id) e
Where b.tablespace_name = c.tablespace_name
And b.tablespace_name = d.tablespace_name
And bytes_full/bytes_total < .7
And b.tablespace_name = e.tablespace_name
And b.file_id = e.file_id;

One last thing to do: Add a statement to indicate what is being changed.

select 'alter database '||a.name||' datafile '''||b.file_name||'''' ||
' resize '||greatest(trunc(bytes_full/.7)
,(bytes_total-bytes_free))||chr(10)||
'--tablespace was '||trunc(bytes_full*100/bytes_total)||
'% full now '||
trunc(bytes_full*100/greatest(trunc(bytes_full/.7)
,(bytes_total-bytes_free)))||'%'
from v$database a
,dba_data_files b
,(Select tablespace_name,sum(bytes) bytes_full
From dba_extents
Group by tablespace_name) c
,(Select tablespace_name,sum(bytes) bytes_total
From dba_data_files
Group by tablespace_name) d
,(Select a.tablespace_name,a.file_id,b.bytes bytes_free
From (select tablespace_name,file_id
,max(block_id) max_data_block_id
from dba_extents
group by tablespace_name,file_id) a
,dba_free_space b
where a.tablespace_name = b.tablespace_name
and a.file_id = b.file_id
and b.block_id > a.max_data_block_id) e
Where b.tablespace_name = c.tablespace_name
And b.tablespace_name = d.tablespace_name
And bytes_full/bytes_total < .7
And b.tablespace_name = e.tablespace_name
And b.file_id = e.file_id
;

OUTPUT:

'ALTERDATABASE'||A.NAME||'DATAFILE'''||B.FILE_NAME||''''||'RESIZE'||GREATEST(TRUNC(BYTES_FULL/.7),(BYTES_TOTAL-BYTES_FREE))||CHR(10)||'--TABLESPACEWAS'||TRUNC(BYTES_FULL*100/BYTES_TOTAL)||'%FULLNOW'|
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/mcse_small_dat1.945.858052637' resize 361009737
--tablespace was 23% full now 70%

alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/sysaux.899.855539713' resize 14485028864
--tablespace was 11% full now 11%

alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/undotbs1.900.855539713' resize 4161798144
--tablespace was 2% full now 2%

alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/undotbs1.900.855539713' resize 2770337792
--tablespace was 2% full now 4%

alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/users.901.855539713' resize 1572864
--tablespace was 10% full now 33%

alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/undotbs3.980.858589691' resize 50502565888
--tablespace was 0% full now 0%

alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/undotbs3.977.858587091' resize 50496274432
--tablespace was 0% full now 0%

alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/undotbs3.972.858563967' resize 50467962880
--tablespace was 0% full now 0%

alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/undotbs3.907.855539897' resize 50869567488
--tablespace was 0% full now 0%

alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/undotbs3.907.855539897' resize 47378857984
--tablespace was 0% full now 0%

alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/undotbs3.907.855539897' resize 47378857984
--tablespace was 0% full now 0%

alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/undotbs3.907.855539897' resize 47378857984
--tablespace was 0% full now 0%

alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/undotbs3.907.855539897' resize 47378857984
--tablespace was 0% full now 0%

alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/undotbs3.907.855539897' resize 47389343744
--tablespace was 0% full now 0%

alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/undotbs3.982.858593159' resize 50547195904
--tablespace was 0% full now 0%

alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/undotbs3.981.858591221' resize 50565480448
--tablespace was 0% full now 0%

alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/undotbs3.976.858585463' resize 50534678528
--tablespace was 0% full now 0%

alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/undotbs3.979.858588305' resize 50494177280
--tablespace was 0% full now 0%

alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/undotbs3.973.858567011' resize 50514100224
--tablespace was 0% full now 0%

alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/undotbs3.971.858563817' resize 50869567488
--tablespace was 0% full now 0%

alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/undotbs3.971.858563817' resize 50276532224
--tablespace was 0% full now 0%

alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/undotbs2.906.855539897' resize 4161798144
--tablespace was 2% full now 2%

alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/undotbs2.906.855539897' resize 314572800
--tablespace was 2% full now 35%

alter database USMCSES1 datafile '+DATA01/dfwusmcses1/datafile/mcse_small_idx1.944.858052739' resize 510806308
--tablespace was 33% full now 70%


24 rows selected.

At last, here's a script that will create the script. Even so, it's important to pay careful attention when applying the created script. Why? Because Rollback, System and Temporary tablespaces are vastly different creatures, and each should not necessarily be held to the 70 percent rule. By the same token, there might be a very good reason for a tablespace to be over allocated -- like the giant load that will triple the volume tonight.

A. Shrink datafiles with free space beyond the high water mark. This can be done as follows (the query is similar to Frosty Z's procedure):

SELECT ceil( blocks*(a.BlockSize)/1024/1024) "Current Size",
   ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Smallest Poss.",
   ceil( blocks*(a.BlockSize)/1024/1024) -
   ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Savings",
   'alter database datafile '''|| file_name || ''' resize ' || 
      ceil((nvl(hwm,1)*(a.BlockSize))/1024/1024/100)*100  || 'm;' "Command"
FROM (SELECT a.*, p.value BlockSize FROM dba_data_files a 
JOIN v$parameter p ON p.Name='db_block_size') a
LEFT JOIN (SELECT file_id, max(block_id+blocks-1) hwm FROM dba_extents GROUP BY file_id ) b
ON a.file_id = b.file_id
WHERE ceil( blocks*(a.BlockSize)/1024/1024) - ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) 
   > 100 /* Minimum MB it must shrink by to be considered. */
ORDER BY "Savings" Desc;

B. After shrinking things above the high water mark, find out what tablespaces would still benefit from having segments moved.

SELECT DISTINCT tablespace_name FROM
(      
    SELECT tablespace_name, block_id + blocks LastBlock,
       lead(block_id) OVER (PARTITION BY File_ID 
          ORDER BY tablespace_name, file_id, block_id) NextBlock
       FROM dba_free_space 
) WHERE LastBlock <> NextBlock AND NextBlock IS NOT NULL;

C. For each of these tablespaces determine which segments need to be moved. (Replace USERS with the name of your tablespace or join it with the previous query)

SELECT distinct de.segment_name
FROM dba_extents de
JOIN
(
   SELECT tablespace_name, file_id, MIN(block_id) LowestFreeBlock
   FROM dba_free_space
   WHERE tablespace_name = 'USERS'
  GROUP BY tablespace_name, file_id
) dfs ON dfs.tablespace_name = de.tablespace_name AND dfs.file_id = de.file_id
WHERE de.tablespace_name = 'USERS'
AND de.block_id > dfs.LowestFreeBlock;

Here is my query:





1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34


set linesize 1000 pagesize 0 feedback off trimspool on

with

 hwm as (

  -- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )

  select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks

  from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn

 ),

 hwmts as (

  -- join ts# with tablespace_name

  select name tablespace_name,relative_fno,hwm_blocks

  from hwm join v$tablespace using(ts#)

 ),

 hwmdf as (

  -- join with datafiles, put 5M minimum for datafiles with no extents

  select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes

  from hwmts right join dba_data_files using(tablespace_name,relative_fno)

 )

select

 case when autoextensible='YES' and maxbytes>=bytes

 then -- we generate resize statements only if autoextensible can grow back to current size

  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)

   ||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '

   ||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'

 else -- generate only a comment when autoextensible is off

  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)

   ||'M from '||to_char(ceil(bytes/1024/1024),999999)

   ||'M after setting autoextensible maxsize higher than current size for file '

   || file_name||' */'

 end SQL

from hwmdf

where

 bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed

order by bytes-hwm_bytes desc

/







and here is a sample output:





1

2

3

4

5


/* reclaim    3986M from    5169M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_undotbs1_o9pfojva_.dbf' resize 1183M;

/* reclaim    3275M from   15864M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_apcpy_o5pfojni_.dbf' resize 12589M;

/* reclaim    2998M from    3655M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_cpy_qt_oepfok3n_.dbf' resize 657M;

/* reclaim    2066M from    2250M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_undotbs2_olpfokc9_.dbf' resize 185M;

/* reclaim     896M from    4000M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_cpy_ocpfok3n_.dbf' resize 3105M;







You get directly the resize statements, with the reclaimable space in comments.
SQL
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/* reclaim   20470M from   20480M */ alter database datafile '+DATA01/dfwusmcses1/datafile/undotbs3.907.855539897' resize 11M;
/* reclaim    8320M from   15360M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_idx1.1505.882092095 */
/* reclaim    3982M from    4125M */ alter database datafile '+DATA01/dfwusmcses1/datafile/undotbs2.906.855539897' resize 144M;
/* reclaim    2510M from    4560M */ alter database datafile '+DATA01/dfwusmcses1/datafile/undotbs1.900.855539713' resize 2051M;
/* reclaim    1844M from   20480M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/undotbs3.971.858563817 */
/* reclaim    1408M from   10240M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_dat1.1570.886807663 */
/* reclaim    1023M from    1024M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/undotbs3.972.858563967 */
/* reclaim    1019M from    1024M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse2_stg_large_dat1.1240.877516511 */
/* reclaim    1019M from    1024M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse2_stg_large_idx1.1241.877516593 */
/* reclaim     998M from    1024M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/undotbs3.979.858588305 */
/* reclaim     996M from    1024M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/undotbs3.977.858587091 */
/* reclaim     990M from    1024M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/undotbs3.980.858589691 */
/* reclaim     979M from    1024M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/undotbs3.973.858567011 */
/* reclaim     960M from   10240M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_dat1.1571.886807703 */
/* reclaim     959M from    1024M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/undotbs3.976.858585463 */
/* reclaim     947M from    1024M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/undotbs3.982.858593159 */
/* reclaim     930M from    1024M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/undotbs3.981.858591221 */
/* reclaim     783M from    1024M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_small_dat1.945.858052637 */
/* reclaim     697M from   14510M */ alter database datafile '+DATA01/dfwusmcses1/datafile/sysaux.899.855539713' resize 13814M;
/* reclaim     683M from    1024M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_small_idx1.944.858052739 */
/* reclaim     650M from    2048M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/system.1461.881279143 */
/* reclaim      64M from   20480M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_dat1.959.858493193 */
/* reclaim      64M from   20480M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_dat1.958.858493039 */
/* reclaim      64M from   20480M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_idx1.970.858563485 */
/* reclaim      64M from   20480M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_dat1.946.858052687 */
/* reclaim      64M from   20480M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_dat1.956.858492913 */
/* reclaim      64M from   20480M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_idx1.941.858052789 */
/* reclaim      64M from   20480M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_dat1.962.858493507 */
/* reclaim      64M from   20480M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_dat1.961.858493357 */
/* reclaim      64M from   10240M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_dat1.1462.881279151 */
/* reclaim      64M from   20480M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_idx1.969.858563433 */
/* reclaim      64M from   10240M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_dat1.1460.881278911 */
/* reclaim      64M from   20480M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_idx1.983.858603855 */
/* reclaim      64M from   20480M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_idx1.964.858504725 */
/* reclaim      64M from   20480M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_dat1.957.858492975 */
/* reclaim      64M from   20480M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_dat1.960.858493297 */
/* reclaim      64M from   30720M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_dat1.1029.861607515 */
/* reclaim      64M from   30720M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_idx1.984.858604089 */
/* reclaim      63M from   32767M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_idx1.975.858578323 */
/* reclaim      63M from   32767M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_dat1.965.858505983 */
/* reclaim      63M from   32767M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_dat1.974.858578221 */
/* reclaim      63M from   32767M after setting autoextensible maxsize higher than current size for file +DATA01/dfwusmcses1/datafile/mcse_large_idx1.978.858588169 */
/* reclaim       4M from       5M */ alter database datafile '+DATA01/dfwusmcses1/datafile/users.901.855539713' resize 2M;

43 rows selected.