-
resize datafile
I try to resize the datafile,but it give me an error :
ora-03297:
But, in fact, it shows enough space in the "used space section"
Please could you give a further explanation
-
Do a tablespace map and that will tell you where the data is stored within the datafiles.
You may see enough space but is it contiguous space? I think that is what happening - you're trying to shrink the file but the data is fragmented throughout it.
Try running this.
______________________________
drop table contg_spc;
create global temporary table contg_spc(
tablespace_name CHAR(30),
contiguous_bytes NUMBER)
on commit preserve rows;
DECLARE
tsname char(30);
cursor cur1 is
select *
from dba_free_space
order by tablespace_name, block_id;
cur1rec cur1%rowtype;
next1rec cur1%rowtype;
total number:=0;
BEGIN
open cur1;
FETCH cur1 into cur1rec;
total := total + cur1rec.bytes;
LOOP
FETCH cur1 into next1rec;
exit when cur1%notfound;
tsname := cur1rec.tablespace_name;
if next1rec.block_id = cur1rec.block_id + cur1rec.blocks
and next1rec.tablespace_name = tsname
then
total := total + next1rec.bytes;
else
insert into contg_spc values (cur1rec.tablespace_name, total);
total := next1rec.bytes;
end if;
cur1rec := next1rec;
END LOOP;
INSERT into contg_spc values (cur1rec.tablespace_name, total);
END;
/
set pagesize 60 echo off lines 80;
ttitle center 'Contiguous Free Space Report' skip 1 center new_today skip 3;
column tsname format a15 heading "Tablespace Name";
column contg_bytes format 9,999,999,999 heading "Contigious Bytes";
column byte_cnt format 99999999 heading "Bytes Count";
column tot_bytes format 9,999,999,999 heading "Total #|Of Bytes";
column todays_date noprint new_value new_today format a1;
break on tsname skip;
spool contigfree.lis
select tablespace_name tsname,contiguous_bytes contg_bytes,count (*) byte_cnt,
contiguous_bytes*count(*) tot_bytes,
to_char(sysdate,'FMMonth DD, YYYY') todays_date
from contg_spc
group by tablespace_name, contiguous_bytes
order by tablespace_name, contiguous_bytes desc;
spool off;
clear breaks;
clear columns;
ttitle off;
________________________________________
It may help you in determining where within the datafile the data sits and how much contiguous space you have.
If you are using OEM then you can run the tablespace map from the tools menu to tell you more precisely, visually, the fragmentation (ie, segment size, object, datafile, extent id, etc.). From there you can determine what you have to do or how far you can shrink your datafile.
Last edited by ramaral; 12-10-2002 at 10:01 AM.
-
Your datafile may contain data beyond the RESIZE value.Try using a higher RESIZE value.It will work.
Check DBA_FREE_SPACE_COALESED view for extent fragmentation.
Joe
-
Hi,
Use this query to determine up to where you can resize (downwards):
Code:
column file_name format a40;
column highwater format 9999999999;
set pagesize 9999
select a.file_name,round((b.maximum+c.blocks-1)*d.db_block_size/(1024*1024)) highwaterMB
from
dba_data_files a, (select file_id, max(block_id) maximum
from dba_extents
group by file_id) b,
dba_extents c, (select value db_block_size
from v$parameter
where name='db_block_size') d
where a.file_id = b.file_id and c.file_id = b.file_id and c.block_id = b.maximum
order by a.tablespace_name,a.file_name
/
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|