Home » RDBMS Server » Server Administration » Resize datafile - Problem
Resize datafile - Problem [message #214211] Mon, 15 January 2007 07:43 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I' having oracle 9i. I try to resize the datafile, but it is not allowing to resize.

SQL> select  file_id, file_name, bytes/1024 Size_K from dba_data_files where file_id = 20 ;

   FILE_ID FILE_NAME                                    SIZE_K
---------- ---------------------------------------- ----------
        20 /dbofsa/oradata/data/owb_tar07.dbf          2097152

SQL> select sum(bytes/1024) Size_K from dba_extents where file_id = 20 ;

    SIZE_K
----------
       440

SQL> alter database datafile '/dbofsa/oradata/data/owb_tar07.dbf' resize 5M ;
alter database datafile '/dbofsa/oradata/data/owb_tar07.dbf' resize 5M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


SQL>


What could be the mistake.

Brian.

[Updated on: Mon, 15 January 2007 07:58] by Moderator

Report message to a moderator

Re: Resize datafile - Problem [message #214214 is a reply to message #214211] Mon, 15 January 2007 07:48 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,
I forgot to metion, a Table has occupied in file_ID 20 and it has a 'LONG RAW' column.

Brian.
Re: Resize datafile - Problem [message #214217 is a reply to message #214214] Mon, 15 January 2007 07:57 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
May be you are trying to resize the file below its HWM.
Please search the forum
http://www.orafaq.com/forum/?SQ=d103bbaa28fa00eb7c1ad864690e1e84&t=search&srch=ORA-03297%3A+file+contains+used+data+beyond+re quested+RESIZE+value&btn_submit=Search&field=all&forum_limiter=&search_logic=AND&sort_order=DESC&author=
Re: Resize datafile - Problem [message #214220 is a reply to message #214211] Mon, 15 January 2007 08:09 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
select
   a.tablespace_name,
   a.file_id,
   a.file_name, 
   a.bytes file_size_in_bytes,
   (c.block_id+(c.blocks-1)) * &_BLOCK_SIZE HWM_BYTES,
   a.bytes - ((c.block_id+(c.blocks-1)) * &_BLOCK_SIZE) SAVING
from dba_data_files a,
   (select file_id,max(block_id) maximum
    from dba_extents
    group by file_id) b,
dba_extents c
where a.file_id = b.file_id
and c.file_id = b.file_id
and c.block_id = b.maximum
order by 6;
FILE_ID FILE_SIZE_IN_BYTES  HWM_BYTES     SAVING
------- ------------------ ---------- ----------
     51 1048576000         1048576000          0
     53 1048576000         1048576000          0
     52 2097152000         1678835712  418316288
     49 2097152000         1429274624  667877376
     50 2097152000         1362427904  734724096
     54 3145728000         2338390016  807337984



go through below link
http://www.oracleadvice.com/Tips/dfhwm.htm

you can set your datafile size up to HWM_BYTES columns.
or you can resize your HWM through alter table move + rebuild index or export and import.

example on 10gr1
SQL> select bytes/1024 from dba_data_files where file_id = 6;

BYTES/1024
----------
      7168

SQL> select sum(bytes/1024) from dba_segments where tablespace_name = 'EXAMPLE';


SUM(BYTES/1024)
---------------
           5440

SQL> alter database
  2  datafile 'c:\oracle\product\10.1.0\oradata\db02\example01.dbf' resize 5500k
;
alter database
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


SQL> ed
Wrote file afiedt.buf

  1  select
  2     a.file_name,
  3     a.bytes file_size_in_bytes,
  4     (c.block_id+(c.blocks-1)) * &_BLOCK_SIZE HWM_BYTES,
  5     a.bytes - ((c.block_id+(c.blocks-1)) * &_BLOCK_SIZE) SAVING
  6  from dba_data_files a,
  7     (select file_id,max(block_id) maximum
  8      from dba_extents
  9      group by file_id) b,
 10  dba_extents c
 11  where a.file_id = b.file_id
 12  and c.file_id = b.file_id
 13  and c.block_id = b.maximum
 14* and c.tablespace_name = 'EXAMPLE'
SQL> /
Enter value for _block_size: 8192
old   4:    (c.block_id+(c.blocks-1)) * &_BLOCK_SIZE HWM_BYTES,
new   4:    (c.block_id+(c.blocks-1)) * 8192 HWM_BYTES,
Enter value for _block_size: 8192
old   5:    a.bytes - ((c.block_id+(c.blocks-1)) * &_BLOCK_SIZE) SAVING
new   5:    a.bytes - ((c.block_id+(c.blocks-1)) * 8192) SAVING

FILE_NAME
--------------------------------------------------------------------------------

FILE_SIZE_IN_BYTES  HWM_BYTES     SAVING
------------------ ---------- ----------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB02\EXAMPLE01.DBF
           7340032    6356992     983040


SQL> alter database
  2  datafile 'c:\oracle\product\10.1.0\oradata\db02\example01.dbf' resize 63569
92;

Database altered.


regards
Taj

[Updated on: Mon, 15 January 2007 08:10]

Report message to a moderator

Previous Topic: Getting an "ORA-01031: insufficient privileges" error occasionally
Next Topic: REDO LOG status
Goto Forum:
  


Current Time: Sat Dec 03 18:18:50 CST 2016

Total time taken to generate the page: 0.05529 seconds