on 11-28-2007 9:49 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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?
Hi,
in some cases you have to shutdown your database and after the startup the resize works.
regards,
/dirk
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.