Resize many datafiles feat. BRTOOLS

[Former Member]

Hi,

i have to resize many datafiles to get more freespace in some

sapdata's, this is okay and sometimes needfull.

With the sapdba, you can resize a datafile to the exact data size.

Example:

i have a datafile like a size of 2001M, in that datafile i have 951M data's.

With the SAPDBA you can resize exactly to 951M, this is a option in the

SAPDBA.

Now we have the BRTOOLS (Oracle 10) and i can't find this option.

With the BRTOOLS i can set the filesize also to 951M, but i have to check

for all datafiles the DB02 and the freespace in the datafiles, and this takes to much time.

Did anybody knows a solution without the DB02 transaktion - only on database?!?!

Thank you very much for your help.

Regards,

Markus

[lbreddemann]

Hi Markus,

this functionality is indeed not present in the BRTOOLS.

So why is that?

I would guess it's because this feature is not used so much.

Most often the problem is not to get the datafiles small again without doing anything else.

In most cases after deleting lots of data, the tablespace needs to be reorganized to release the now free space already allocated by segments.

Also sizing down a datafile to it's absolute minimum is often not a good choice for tablespaces where data should be changed/added. Transactions may abort due to failing space requirements.

In addition to that the technique of SAPDB was not that good. It just looked which block was the highest allocated block in a datafile (the datafile high-watermark) and down to that point a datafile can be resized.

Anyhow, you can of course do the same manually if you really feel that it is necessary:

1. find out how much space can be gained per datafile:

select fs.tablespace_name, fs.file_id, fs.block_id*8192 as minsize, df.bytes  as actsize
from dba_free_space fs,
    dba_data_files df
where
    fs.tablespace_name = df.tablespace_name
 and fs.file_id         = df.file_id
 and df.blocks between fs.block_id and fs.block_id + fs.blocks;

TABLESPACE_NAME                   FILE_ID    MINSIZE    ACTSIZE
------------------------------ ---------- ---------- ----------
SYSTEM                                  1  275324928  471859200
PSAPUNDO                                2  196157440  209715200
SYSAUX                                  3  257564672  262144000
EXAMPLE                                 4   81862656   94371840
IPDB                                    6    7806976   10485760
IPDB_BIN                                7      73728    5242880
IPDB_HIST                               8     663552    5242880
IPDB_INDX                               9    5709824   10485760
SAPDATA                                10   19996672   52428800
PSAPBWS                                11   50077696   52428800
CORRTBS                                 5   10559488   20971520

2. Perform a resize (I take file_id 1 this time):

SQL> alter database datafile 1 resize 275324928;
Database altered.

3. Check if it worked:

SQL> select bytes from dba_data_files where file_id=1;

     BYTES
----------
 275324928

4. The file does not appear any longer in the resultlist:

SQL> select fs.tablespace_name, fs.file_id, fs.block_id*8192 as minsize, df.bytes  as actsize
  2  from dba_free_space fs,
  3      dba_data_files df
  4  where
  5      fs.tablespace_name = df.tablespace_name
  6   and fs.file_id         = df.file_id
  7   and df.blocks between fs.block_id and fs.block_id + fs.blocks;

TABLESPACE_NAME                   FILE_ID    MINSIZE    ACTSIZE
------------------------------ ---------- ---------- ----------
PSAPUNDO                                2  196157440  209715200
SYSAUX                                  3  257564672  262144000
EXAMPLE                                 4   81862656   94371840
IPDB                                    6    7806976   10485760
IPDB_BIN                                7      73728    5242880
IPDB_HIST                               8     663552    5242880
IPDB_INDX                               9    5709824   10485760
SAPDATA                                10   19996672   52428800
PSAPBWS                                11   50077696   52428800
CORRTBS                                 5   10559488   20971520

I guess it's pretty obvious that this method has it's drawbacks (see above).

The statement can of course be extended so that you order by the difference of actual size and minimal size and it would also be easy to generate the ALTER DATABASE DATAFILE... scripts from this.

But this can be done by somebody who really feels the urge to make the datafiles small withou reorganizing the segments...

KR Lars