| Resize datafile - Problem [message #214211] |
Mon, 15 January 2007 07:43  |
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 #214220 is a reply to message #214211] |
Mon, 15 January 2007 08:09  |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
