Home » RDBMS Server » Server Administration » not able to resize back datafile. (Oracle 10g linux)
not able to resize back datafile. [message #411825] Tue, 07 July 2009 01:10 Go to next message
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


Re: not able to resize back datafile. [message #411827 is a reply to message #411825] Tue, 07 July 2009 01:18 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

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 Go to previous messageGo to next message
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.
Re: not able to resize back datafile. [message #411848 is a reply to message #411839] Tue, 07 July 2009 02:41 Go to previous messageGo to next message
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
Re: not able to resize back datafile. [message #411864 is a reply to message #411848] Tue, 07 July 2009 03:47 Go to previous messageGo to next message
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.
Re: not able to resize back datafile. [message #411873 is a reply to message #411864] Tue, 07 July 2009 04:06 Go to previous messageGo to next message
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
Re: not able to resize back datafile. [message #411876 is a reply to message #411873] Tue, 07 July 2009 04:09 Go to previous messageGo to next message
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....
Re: not able to resize back datafile. [message #411885 is a reply to message #411876] Tue, 07 July 2009 04:21 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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....
Re: not able to resize back datafile. [message #411938 is a reply to message #411925] Tue, 07 July 2009 05:52 Go to previous messageGo to next message
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
Re: not able to resize back datafile. [message #412758 is a reply to message #411825] Sat, 11 July 2009 08:30 Go to previous messageGo to next message
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
Re: not able to resize back datafile. [message #412766 is a reply to message #412758] Sat, 11 July 2009 09:19 Go to previous messageGo to next message
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
Re: not able to resize back datafile. [message #412797 is a reply to message #412766] Sun, 12 July 2009 02:05 Go to previous messageGo to next message
sruji6
Messages: 4
Registered: July 2009
Junior Member
this is the first time i am replying in forums.
sorry for the reply.
Re: not able to resize back datafile. [message #412798 is a reply to message #412797] Sun, 12 July 2009 02:22 Go to previous messageGo to next message
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
Re: not able to resize back datafile. [message #412923 is a reply to message #412798] Mon, 13 July 2009 07:28 Go to previous messageGo to next message
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.
Re: not able to resize back datafile. [message #412934 is a reply to message #412923] Mon, 13 July 2009 08:10 Go to previous messageGo to next message
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
Re: not able to resize back datafile. [message #412935 is a reply to message #412923] Mon, 13 July 2009 08:11 Go to previous message
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.
Previous Topic: DATABASE LINK
Next Topic: ASM migration to new server.
Goto Forum:
  


Current Time: Thu Dec 05 13:46:06 CST 2024