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
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.
Persistent Storage Solutions 
but what if we have system table space…
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.
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.
Thanks man!
I was suffering of spaces problems and with that explanation I could solved!
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
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>
Very nice explanation
i found very few of this kind, this very clear to understand.
Will reducing the datafile size make the file unusable ?
Thank you
great documnet ,this document helps me to solve my proble
great documnent ,this document helps me to solve my problem
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
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
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
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…..
Nice article!
I think, this script http://nikolayivankin.wordpress.com/2012/04/27/useful-scripts-get-a-list-of-objects-to-be-moved-for-a-data-file-resize/ will be useful for your readers.
Hi there, this weekend is fastidious for me,
for the reason that this time i am reading this impressive informative post here at
my house.
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.