cancel
Showing results for 
Search instead for 
Did you mean: 

How to decrease the size of datafile

Former Member
0 Kudos

Hi all

I want to resize my datafile. I want to reduce the size of my datafile. But when i try to do so through Brtools it gives error for any size i specify. Error is " ORA - 03297: File contains used data beyond requested RESIZE value."

Through DB02 when i checked it shows only 22% used but still it gives this error.

Can any one help me on this.

Thanks in advance.

Vitthal Prabhu

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Really thanks to all of you for your replies.

What i think i should reorganize the table to solve my proble. Table name is PSAPEC6

Vitthal Prabhu

Former Member
0 Kudos

Hi Vitthal;

As the others have explained well, your problem is that your table used to be much larger than it is now. Your high water mark is artificially high and you want to reset it. There are several ways to do this and they are all essentially reorg.

Assuming you are running Oracle 10g and your table is in a locally managed tablespace with automatic segment space managment, you can reorg the table live (without causing an interuption of service or taking a maintenance window):

Let's check to see if you meet the prerequisites first - run:

select tablespace_name, SEGMENT_SPACE_MANAGEMENT, EXTENT_MANAGEMENT from dba_tablespaces where tablespace_name in (select tablespace_name from dba_tables where table_name='PSAPEC6');

If your database reports LOCAL and AUTO, you are ready for a live reorg.

first, you need to enable row movement on the table, like this:

alter table PSAPEC6 enable row movement;

Next you need to reorg the table, like this:

alter table PSAPEC6 shrink space compact;

now your table has been reorganized into a smaller object with a lower highwater mark and your users haven't had to incur an outage.

Cheers,

Chip

Former Member
0 Kudos

I am having a similar issue. Normally, you can shrink a file by the amount of freespace at the end of a file. In DB02, you can click on the freespace button to see the free space mapping.

I am getting the ORA-3297 error in BRTOOLS / BRSPACE, even though I am only shrinking by the amount of free space in the last free chunk. Normally, I am able to free this space, but in a number of cases today, I can not.

I believe the reason is that the freespace report in DB02 does not indicate whether or not the last freespace chunk is at the end of the file or not. So, in the past, I have been lucky that I could free up the space. However, today, I must be in the situation that there are used chunks of space above the last freespace chunk.

Does my thinking make sense? Wouldn't it be nice if DB02 had a "shrinkable space" report? Does anyone know of a SQL query that definitively informs you about the amount of space you could shrink each file by?

Former Member
0 Kudos

Pardon me, the script above does that. I had tried a version of this recognizable code from another web source and it's syntax gave troubles. I got the above to work just fine.

Former Member
0 Kudos

Hi,

in some cases you have to shutdown your database and after the startup the resize works.

regards,

/dirk

Former Member
0 Kudos

Hi

Is there in limit on minimum size of database? That is is anywhere it is mentioned that size of data file shoud not be less than 2GB

Thanks

Former Member
0 Kudos

Hi,

max datafile size is 2 GB,

It is not always possible to decrease the size of a file to a specific value. It could be that the file contains data beyond the specified decreased size, in which case the database will return an error.

regards,

kaushal

Former Member
0 Kudos

Hi

Then is it possible to combine 2 datafiles and then reduce the size of the combined file?

Thanks

Former Member
0 Kudos

Hi,

There's no direct way to do this in Oracle like alter datafile merge/split.

There's always alternative to accomplish the same, for example create intermediate tablespace and move the objects.

mark helpful answers,

regards,

kaushal

former_member204746
Active Contributor
0 Kudos

which tablespace does that?

if you have at least one block used near the end of the datafile, you won`t be able to resize it. this also has to do with "high watermark".

only way to decrease it is to reorg the whole tablespace. check SAP note 646681.

0 Kudos

No, the smallest size of a data file is 16K, so I don't think it is a factor in your situation.

The real limit as some other pointed out is the "high watermark" let's explain it with an example:

You have a 1Gb data file, at one point, you used 900M of it. but since then you deleted 700M of the data so now it's 200M used.

In DB02 you will see the tablespace is 20% used. BUT you will only be able to reduce your file size to 900M not 200M. The reason being is the "high watermark" - the highest level the datafile has been filled in history is 900M. (Technically this means Oracle has had used 900M and blocks up to 900M are no longer clean, even if you deleted data, the blocks are marked "has been used" by Oracle and cannot be dropped).

Think it another way, once you used 900M, and you deleted 700M, how can you be so sure that all the data deleted are the 201M to 900M? it is very possible that several block close to the end of the file is still actively used by the data while data in the middle of the datafile has been deleted. These space will be available for you to used (store new data) but you can't simple drop these blocks - which means you can't reduce the size of your datafile to exclude this space.

So the real limit is the high-watermark which means the highest level you've ever used this data file.

Former Member
0 Kudos

Hi,

you can use following script

it's use the contents of dba_free_space and has been proven to be fastest than select from dba_extents.

-


rem Subject: Calculation of HighwaterMark of datafiles

rem

rem Remarks: minimal size of a datafile is 2 Oracle blocks

rem resizing should always be a multiple of Oracle blocks

rem

rem Requirements: select on sys.dba_data_files

rem select on sys.dba_free_space

rem select on sys.v_$parameter

rem

rem -


set serveroutput on

execute dbms_output.enable(2000000);

declare

cursor c_dbfile is

select tablespace_name

,file_name

,file_id

,bytes

from sys.dba_data_files

where status !='INVALID'

order by tablespace_name,file_id;

cursor c_space(v_file_id in number) is

select block_id,blocks

from sys.dba_free_space

where file_id=v_file_id

order by block_id desc;

blocksize binary_integer;

filesize binary_integer;

extsize binary_integer;

begin

/* get the blocksize of the database, needed to calculate the startaddress */

select value

into blocksize

from v$parameter

where name = 'db_block_size';

/* retrieve all datafiles */

for c_rec1 in c_dbfile

loop

filesize := c_rec1.bytes;

<<outer>>

for c_rec2 in c_space(c_rec1.file_id)

loop

extsize := ((c_rec2.block_id - 1)blocksize + c_rec2.blocksblocksize);

if extsize = filesize

then

filesize := (c_rec2.block_id - 1)*blocksize;

else

/* in order to shrink the free space must be uptil end of file */

exit outer;

end if;

end loop outer;

if filesize = c_rec1.bytes

then

dbms_output.put_line('Tablespace: '

||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);

dbms_output.put_line('Can not be resized, no free space at end of file.')

;

dbms_output.put_line('.');

else

if filesize < 2*blocksize

then

dbms_output.put_line('Tablespace: '

||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);

dbms_output.put_line('Can be resized uptil: '||2*blocksize

||' Bytes, Actual size: '||c_rec1.bytes||' Bytes');

dbms_output.put_line('.');

else

dbms_output.put_line('Tablespace: '

||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);

dbms_output.put_line('Can be resized uptil: '||filesize

||' Bytes, Actual size: '||c_rec1.bytes);

dbms_output.put_line('.');

end if;

end if;

end loop;

end;

/

-


When trying to resize beyond the highwatermark of the datafile an ORA-3297 is

returned. The problem is what is the highwatermark because the error is not

showing it. Resizing up to the minimum is in one step possible using the above scripts.

regards,

kaushal