Home » RDBMS Server » Server Administration » USERS01.dbf file size (Oracle 10g)
USERS01.dbf file size [message #356111] Wed, 29 October 2008 10:26 Go to next message
techguru07
Messages: 2
Registered: October 2008
Junior Member
Hi Friends,

I am new to Oracle and I have question about , how to reduce the size of USERS01.DBF file? It is almost 13 gb. And it creats problem when I do defrag utility on my windows machine. I dont need the some dbs I created, so dropped it, still the size of this file is same. Please help me.
Re: USERS01.dbf file size [message #356123 is a reply to message #356111] Wed, 29 October 2008 12:13 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Search for ALTER DATABASE DATAFILE. However, if there is just one block in use at the end of the file you will not be able to reduce its size.
Re: USERS01.dbf file size [message #356125 is a reply to message #356111] Wed, 29 October 2008 12:31 Go to previous messageGo to next message
techguru07
Messages: 2
Registered: October 2008
Junior Member
I tried following statement
alter database datafile '/oracle/product/10.2.0/oradata/oradb/users01.dbf' resize 900m

but it gives me ORA-03297 even though I increase the size to 10 gig.

Please help
Re: USERS01.dbf file size [message #356127 is a reply to message #356125] Wed, 29 October 2008 12:49 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Quote:
>>I dont need the some dbs I created, so dropped it,


I can't able to understand...

ORA-03297: file contains used data beyond requested RESIZE value 

Cause: Some portion of the file in the region to be trimmed is currently in use by a database object 

Action: Drop or move segments containing extents in this region prior to resizing the file, 
or choose a resize value such that only free space is in the trimmed.  


Babu
Re: USERS01.dbf file size [message #356159 is a reply to message #356125] Wed, 29 October 2008 15:22 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
techguru07 wrote on Wed, 29 October 2008 13:31

but it gives me ORA-03297 even though I increase the size to 10 gig.



What does this mean?
Re: USERS01.dbf file size [message #356167 is a reply to message #356159] Wed, 29 October 2008 16:20 Go to previous message
vjeedigunta
Messages: 201
Registered: March 2005
Location: Hyderabad
Senior Member

You cannot reduce the size unless you can afford reorg ( export / drop / resize / import ) ..

-sai.
Previous Topic: Performance tuning
Next Topic: ORA-03106: fatal two-task communication protocol error
Goto Forum:
  


Current Time: Sat Dec 03 08:00:22 CST 2016

Total time taken to generate the page: 0.03372 seconds