OraFAQ Forum: Server Administration » USERS01.dbf file size

Home » RDBMS Server » Server Administration » USERS01.dbf file size (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

USERS01.dbf file size [message #356111]

Wed, 29 October 2008 10:26

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.

Report message to a moderator

Re: USERS01.dbf file size [message #356123 is a reply to message #356111]

Wed, 29 October 2008 12:13

joy_division
Messages: 4963
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.

Report message to a moderator

Re: USERS01.dbf file size [message #356125 is a reply to message #356111]

Wed, 29 October 2008 12:31

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

Report message to a moderator

Re: USERS01.dbf file size [message #356127 is a reply to message #356125]

Wed, 29 October 2008 12:49

babuknb
Messages: 1736
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

Report message to a moderator

Re: USERS01.dbf file size [message #356159 is a reply to message #356125]

Wed, 29 October 2008 15:22

joy_division
Messages: 4963
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?

Report message to a moderator

Re: USERS01.dbf file size [message #356167 is a reply to message #356159]

Wed, 29 October 2008 16:20

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.

Report message to a moderator

Previous Topic:	Performance tuning
Next Topic:	ORA-03106: fatal two-task communication protocol error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jul 07 23:46:01 CDT 2024