OraFAQ Forum: Server Administration » not able to resize back datafile.

Home » RDBMS Server » Server Administration » not able to resize back datafile. (Oracle 10g linux)

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

not able to resize back datafile. [message #411825]

Tue, 07 July 2009 01:10

IT Guru
Messages: 59
Registered: January 2007

Member

Oracle 10g linux

system datafile size is 5.37gb

total segments /extents size is 0.487915039 gb

total dba_free_size for system datafile is 5gb

already tried with

ALTER TABLESPACE system COALESCE ;

but when try to resize back

Syntax:

alter database datafile 1 resize 4g;
ORA-03297: file contains used data beyond requested RESIZE value

FILE_NAME : /u01/app/oracle/oradata/qanew/system01.dbf
TABLESPACE_NAME : SYSTEM
BYTES : 5,767,168,000.00
BLOCKS : 704,000.00
STATUS : AVAILABLE
AUTOEXTENSIBLE : YES
MAXBYTES : 34,359,721,984.00
MAXBLOCKS : 4,194,302.00
USER_BYTES : 5,767,102,464.00
USER_BLOCKS : 703,992.00
ONLINE_STATUS : SYSTEM

Attachment: database info for qanew 125.rar
(Size: 708.58KB, Downloaded 1396 times)

Report message to a moderator

Re: not able to resize back datafile. [message #411827 is a reply to message #411825]

Tue, 07 July 2009 01:18

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

babu.knb@gmail.com

I hope your attachment very small size. Instead of attach please post all the details as per Orafaq rules.

Also please try to avoid duplicate post.

Thanks

[Updated on: Tue, 07 July 2009 01:18]

Report message to a moderator

Re: not able to resize back datafile. [message #411839 is a reply to message #411825]

Tue, 07 July 2009 02:23

IT Guru
Messages: 59
Registered: January 2007

Member

attachment contain 4 diffrent excel file

output of
1) dba_data_files
2) dba_segments
3) dba_extents
4) dba_free_space

I have use metalink noteid. 130866.1

Can anyone tellme how to reset highwatermark of system tablespace in oracle 10g linux.

Report message to a moderator

Re: not able to resize back datafile. [message #411848 is a reply to message #411839]

Tue, 07 July 2009 02:41

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Move or drop the objects that are beyond the size you want.

Regards
Michel

Report message to a moderator

Re: not able to resize back datafile. [message #411864 is a reply to message #411848]

Tue, 07 July 2009 03:47

IT Guru
Messages: 59
Registered: January 2007

Member

already move all objects in same tablespace.

cant drop as belong to sys users in system tablespace.

alter table <tablename> shrink . not working of sys tables.

Report message to a moderator

Re: not able to resize back datafile. [message #411873 is a reply to message #411864]

Tue, 07 July 2009 04:06

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Why do you want to shrink SYSTEM tablespace?
Every bit of space in it is in used (unless you created you own objects in it).

Regards
Michel

Report message to a moderator

Re: not able to resize back datafile. [message #411876 is a reply to message #411873]

Tue, 07 July 2009 04:09

IT Guru
Messages: 59
Registered: January 2007

Member

hi,

have already give output of dba_free_space , dba_data_files etc...so can chk

about 5 gb space is free

May be as heavy record of sys.ADU$ tables

where already truncat same..... still not able to resize.....

anyway to rest system tablespace watermark....

Report message to a moderator

Re: not able to resize back datafile. [message #411885 is a reply to message #411876]

Tue, 07 July 2009 04:21

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

have already give output of dba_free_space , dba_data_files etc...so can chk

No as I can't download files.

Quote:

where already truncat same..... still not able to resize.....

Check DBA_EXTENTS to know what is at the end of the files.
Or use a script I posted at:
http://www.dba-village.com/village/dvp_scripts.ScriptDetails?ScriptIdA=1510

Regards
Michel

[Updated on: Tue, 07 July 2009 04:22]

Report message to a moderator

Re: not able to resize back datafile. [message #411925 is a reply to message #411885]

Tue, 07 July 2009 05:31

IT Guru
Messages: 59
Registered: January 2007

Member

okay but after knowing .say as highwater mark is 5gb

but how can I rest it back....

Report message to a moderator

Re: not able to resize back datafile. [message #411938 is a reply to message #411925]

Tue, 07 July 2009 05:52

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You can't if they are SYS objects (but indexes).

Regards
Michel

Report message to a moderator

Re: not able to resize back datafile. [message #412758 is a reply to message #411825]

Sat, 11 July 2009 08:30

sruji6
Messages: 4
Registered: July 2009

Junior Member

try this command
alter database datafile '<system datafile file path>' resize 6G;

As your current SYSTEM datafile is 5.37Gb give it as 6G when resizing.

please try this on test DB and then apply it in production

Report message to a moderator

Re: not able to resize back datafile. [message #412766 is a reply to message #412758]

Sat, 11 July 2009 09:19

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

OP's purpose is to DECREASE size not to increase it.
And OP knows your command as it posted it.

Please read the topic before posting.

Regards
Michel

Report message to a moderator

Re: not able to resize back datafile. [message #412797 is a reply to message #412766]

Sun, 12 July 2009 02:05

sruji6
Messages: 4
Registered: July 2009

Junior Member

this is the first time i am replying in forums.
sorry for the reply.

Report message to a moderator

Re: not able to resize back datafile. [message #412798 is a reply to message #412797]

Sun, 12 July 2009 02:22

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Nice to see a newcomer, you are welcome.
Please read OraFAQ Forum Guide; you have a Test forum at the bottom of the page, don't hesitate to use it.

Regards
Michel

Report message to a moderator

Re: not able to resize back datafile. [message #412923 is a reply to message #412798]

Mon, 13 July 2009 07:28

smunir362
Messages: 310
Registered: September 2007

Senior Member

create a new tablespace e.g. system1 and move all the objects to the new tablespace and then resize the system tablesapce.

Report message to a moderator

Re: not able to resize back datafile. [message #412934 is a reply to message #412923]

Mon, 13 July 2009 08:10

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No, you don't move any object from SYSTEM tablespace out of it (unless you badly put in it some user objects).
Objects in SYSTEM tablespace are owned by Oracle, you don't do anything with them unless you are an expert or you have the go from Oracle support.

Regards
Michel

Report message to a moderator

Re: not able to resize back datafile. [message #412935 is a reply to message #412923]

Mon, 13 July 2009 08:11

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

smunir362 wrote on Mon, 13 July 2009 08:28

create a new tablespace e.g. system1 and move all the objects to the new tablespace and then resize the system tablesapce.

I would strongly suggest against doing this, for anything other than user created objects.

Report message to a moderator

Previous Topic:	DATABASE LINK
Next Topic:	ASM migration to new server.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 01 17:16:17 CST 2024