Reducing datafile size to recover free space – Oracle Database 10g

At time, we want to recover some space from database just to allocate the same to some other tablespace or to return it back to OS disk. This situation arises many times.
And many time we hit with error “ORA-03297: file contains used data beyond requested RESIZE value“.
The concept is simple and many of you must be knowing, but just putting in a simpler words.

Lets take an example of one of the datafile in a database.

Lets see the total free space in a datafile 194.

SQL> select sum(bytes)/1024/1024 from dba_free_space
2  where tablespace_name = ‘APPS_TS_TX_DATA’
3  and file_id = 194;

SUM(BYTES)/1024/1024
——————–
1844.125

Now lets see the distribution for the file.

SQL> select file_id, block_id, blocks, bytes,  ‘Free’ from dba_free_space
2  where tablespace_name = ‘APPS_TS_TX_DATA’
3  and file_id = 194
4  and rownum < 7
5  order by block_id desc;

   FILE_ID   BLOCK_ID     BLOCKS      BYTES ‘FRE
———- ———- ———- ———- —-
194      35001     220992 1810366464 Free
194      13433         16     131072 Free
194      13417         16     131072 Free
194      13401         16     131072 Free
194      13385         16     131072 Free
194      13369         16     131072 Free

We can see that there are so many blocks which are free.  Its divided into chunks of 16 Blocks and each of these chunks are given a block ID. Now we can see that this formating is done until block 35001 and after that there is no partitions. Beyond the block ID 35001, the whole space is available as a single large unit. This is because of high water mark. When a object is created, it will be created physically in the datafile and will be allocated a block. The number of blocks it will be allocated will depend on the parameter “INITIAL EXTENT” which can be given at the time of creating an object. If we dont give this parameter it will take a default value of 16. So 16 block gets assigned when you create any abject, example a table.

You might be wondering that after block 35001, we have all free space and also we have free space at blocks 13401, 13417, 13433 etc. But where are the blocks between 13433 and 35001??

The answer can be found from dba_extents. All the blocks between 13433 and 35001 are occupied by the objects and are not free. That why you are not able to see them in dba_free_space view. But you can find then in dba_extents. So in case of file 194, objects were getting created until block no 35001 (so we can see that block formating till block 35001) and then at later point of time some of the objects got dropped, so the space got freed, but the formating of blocks remain intact (example block IDs which got freed are 13401, 13417, 13433 etc.). This we call it as high water mark for a datafile.

As we saw earlier that we have around 1844.125 MB free space. Can we recover all of them?? I wish .. but no, we cannot. Reason being that, some of the blocks are free “in-between” and there is a fragmentation. To make my point clear, lets try to reduce the file size.

Lets try that !!

This file original size is 2000M

SQL> select bytes/1024/1024 from dba_data_files where file_id = 194;


BYTES/1024/1024
—————
2000

and as per dba_free_space its having 1844.125 MB free space. Lets reduce the file size by 1800 MB and it will definately give error.

SQL> alter database datafile 194 resize 200M;
alter database datafile 194 resize 200M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

What happened??

The reason you are not able to free the space is because we have certain objects created in between and there is no chunk of 1800MB free. The structure of this file is as given below.

X -> Occupied Block
0 -> Free Block

1.jpg

So here block 35001, 13433 are free, but in-between blocks are occupied. When you are trying to reduce the size of datafile by 1800 MB, you are going beyond block ID 35001, where the objects do exits. After those objects there may be free space, but that doesnt matter, you can at max reduce the size of datafile until block ID 35001, and not beyond that.

So here if we see that there are around 220992 blocks free after block ID 35001. That makes a space of around (220992 X 8192 = 1810366464 bytes = 1726.5 MB free). Thats what the bytes column is showing.
So we can reduce the size of this datafile by maximum of 1810366464 bytes = 172.5 MB. If you try to reduce the size more than that, then you will hit with an error ORA-03297: file contains used data beyond requested RESIZE value.

Now try reducing by 1700 MB and it will succeed !!!

SQL> alter database datafile 194 resize 300M;

database altered

What if you want to still reduce the size beyond 1800 MB. i.e. what if you want to make the size to 200MB.

To do that, you need to do following steps.

- Find block ID at 1800th byte.

We know that last 1810366464 bytes are free.
1800 MB = 1887436800 Bytes
Bytes Remaining = 1887436800 – 1810366464 = 77070336 Bytes = 9408 Blocks
Block ID till we want to clear the objects id  35001 – 9408 = 25593

- Drop all the objects which are beyond this block. That will make 1800MB free in this datafile and you can reduce the size of datafile by 1800 MB.

Moral of story is dont get into these many calculations. Its good to know the logic and helps in crisis situation. But not good to go for such drop object, reduce the size if file and recreate the object again.

About these ads

20 thoughts on “Reducing datafile size to recover free space – Oracle Database 10g

  1. Same explanation applies for system tablespace files as well.

    Example: My system tablespace has 1 file of 2000M and I want to reduce it to 1500M

    SQL> select file_name, bytes/1024/1024 from dba_data_files;

    FILE_NAME
    ——————————————————————————–
    BYTES/1024/1024
    —————
    /dy/oracle/product/db10g/dbf/htmldb01.dbf
    1000

    /dy/oracle/product/db10g/dbf/sysaux01.dbf
    2000

    /dy/oracle/product/db10g/dbf/undotbs01.dbf
    200

    FILE_NAME
    ——————————————————————————–
    BYTES/1024/1024
    —————
    /dy/oracle/product/db10g/dbf/system01.dbf
    2000

    SQL> alter database datafile ‘/dy/oracle/product/db10g/dbf/system01.dbf’ resize 1500M;

    Database altered.

    SQL>

    Since nothing was present in those blocks, I was able to resize.

  2. This is great looking at the logic this way, even though, as the writer said, it is not such a good idea to actually reclaim space in such way.

  3. Hello,

    This is actually a very good document posted by writer. I understood everything very clearly.
    Thanks You.
    Keep posting such things in future….

    Regards,
    Ajay

  4. Hi All,

    Does any one knows how to initial the free space after resizing the data file? My company is using some storage management software to migrate our oracle databases to a new storage system which can save money and disks space.

    For example as the following shows, you re-size from 2000M to 1500M, but the (500M) free data blocks on the disk are still non-zeros. In order for us to migrate the new storage system we’ll need to zero those free blocks. For file system, I can use the dd command to create a zero file, then delete it, but what if a database is using ASM on raw devices. I couldn’t find any way to create a 500M zero file. Is there any tools or Oracle commands to do that? How about RMAN? Any advise or info would be appreciated. Thanks much!

    FILE_NAME
    ——————————————————————————–
    BYTES/1024/1024
    —————
    /dy/oracle/product/db10g/dbf/system01.dbf
    2000

    SQL> alter database datafile ‘/dy/oracle/product/db10g/dbf/system01.dbf’ resize 1500M;

    Database altered.

    SQL>

  5. How about this? 10g only, assumes locally managed.

    SELECT ‘alter database datafile ‘ || file_id || ‘ resize ‘ ||
    to_char((( round ((hwm – 5*BLOCK_SIZE) / initial_extent, 0) + 1) * initial_extent + 5*BLOCK_SIZE) / 1024) || ‘K;’ resize
    FROM (
    SELECT tablespace_name, file_id, max(block_id * 16384) hwm FROM
    dba_extents WHERE tablespace_name = ‘APPS_TS_TX_DATA’
    GROUP BY tablespace_name, file_id) hwm, dba_tablespaces
    WHERE dba_tablespaces.tablespace_name = hwm.tablespace_name

    • The approach you should take in the case such as yours is
      1> identify those tables/indexes etc XXX (13433 onward) and move them(alter table move or move it to a different tbspace itself/dropping them like you said included)! Then resize tbspace…

      If you are on 10g…
      You could identify tables in the mentioned datafiles.
      And then:
      ALTER TABLE XYZ ENABLE ROW MOVEMENT;
      ALTER TABLE XYZ SHRINK SPACE CASCADE;
      -there is an option of COMPACT too. The documentation has more explanation of this feature…
      May seem like a a little exhaustive,but it will work ;)

      -Channesh

  6. Hi James,

    Does your query (below) work with 11g? What the magic number of 16384?

    Thank you.

    SELECT ‘alter database datafile ‘ || file_id || ‘ resize ‘ ||
    to_char((( round ((hwm – 5*BLOCK_SIZE) / initial_extent, 0) + 1) * initial_extent + 5*BLOCK_SIZE) / 1024) || ‘K;’ resize
    FROM (
    SELECT tablespace_name, file_id, max(block_id * 16384) hwm FROM
    dba_extents WHERE tablespace_name = ‘APPS_TS_TX_DATA’
    GROUP BY tablespace_name, file_id) hwm, dba_tablespaces
    WHERE dba_tablespaces.tablespace_name = hwm.tablespace_name

  7. Does your query (below) work with Oracle11g? What is the magic number of 16383? It can be a very large number when multiplied by block_id?

    Thanks.

    SELECT ‘alter database datafile ‘ || file_id || ‘ resize ‘ ||
    to_char((( round ((hwm – 5*BLOCK_SIZE) / initial_extent, 0) + 1) * initial_extent + 5*BLOCK_SIZE) / 1024) || ‘K;’ resize
    FROM (
    SELECT tablespace_name, file_id, max(block_id * 16384) hwm FROM
    dba_extents WHERE tablespace_name = ‘APPS_TS_TX_DATA’
    GROUP BY tablespace_name, file_id) hwm, dba_tablespaces
    WHERE dba_tablespaces.tablespace_name = hwm.tablespace_name

  8. Pingback: 2010 in review « IN ORACLE MILIEU …
  9. SQL> select sum(bytes)/1024/1024 from dba_free_space where tablespace_name = ‘S4_CLE’ and file_id = 18;

    SUM(BYTES)/1024/1024
    ——————–
    15344
    SQL> select file_id, block_id, blocks, bytes,’Free’ from dba_free_space where tablespace_name = ‘S4_CLE’ and file_id=18 and rownum select sum(bytes)/1024/1024 from dba_data_files where file_id=18;

    SUM(BYTES)/1024/1024
    ——————–
    16142

    — But when i tried to resize, i was not able to …why? what is that i am missing here

    SQL> alter database datafile 18 resize 500M;
    alter database datafile 18 resize 500M
    *
    ERROR at line 1:
    ORA-03297: file contains used data beyond requested RESIZE value

    SQL> alter database datafile 18 resize 10G;
    alter database datafile 18 resize 10G
    *
    ERROR at line 1:
    ORA-03297: file contains used data beyond requested RESIZE value

    your help is very much appreciated…..

  10. With havin so much content do you ever run into any problems of plagorism or copyright violation?
    My blog has a lot of exclusive content I’ve either authored myself or outsourced but it seems a lot of it is popping it up all over the web without my permission. Do you know any methods to help stop content from being ripped off? I’d definitely appreciate
    it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s