OraFAQ Forum: Server Administration » how to reduce system data file

Home » RDBMS Server » Server Administration » how to reduce system data file (

) 1 Vote

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

how to reduce system data file [message #151081]

Tue, 13 December 2005 00:56

rajshah_ocp
Messages: 3
Registered: December 2005
Location: delhi

Junior Member

hello all

plz tell me how to reduce system datafile size,
actually in my company database system datafile size is around
5GB,i have already transfer all user n its segments to another
tablespace.
when i view how much free sapce in systm tablespace using
"SELECT * FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='SYSTEM';

IT dsiplay lots of free space in system datafile .
but when i try to resize the system datafile using
alter database datafile 'c:\oracle\oradata\system01.dbf' resize 400m ;

then its display message'u cannot resize datafile the data is beyond of size'......

thank u

Report message to a moderator

Re: how to reduce system data file [message #151087 is a reply to message #151081]

Tue, 13 December 2005 01:06

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Always, show what you did.
Literally 'explaining' what you did, helps very less.
>>then its display message'u cannot resize datafile the data is beyond of size'......

It means,
you cannot reduce the filesize below its maximum used size.

Report message to a moderator

Re: how to reduce system data file [message #151306 is a reply to message #151087]

Wed, 14 December 2005 03:41

rajshah_ocp
Messages: 3
Registered: December 2005
Location: delhi

Junior Member

thax for ur reply,
but u tell,is there any way to reduce to system datafile or not.
coz in system datafile lots of free space but not reducing, i dont know y.
plz tell me clearly

Report message to a moderator

Re: how to reduce system data file [message #151316 is a reply to message #151081]

Wed, 14 December 2005 04:27

hemantpande
Messages: 2
Registered: December 2005
Location: Pune

Junior Member

Hi,

Check any segment is allocated other than 'SYS','SYSTEM'or 'OUTLN' is there or not in system tablespace.

( select distinct(owner) from dba_segments where tablespace_name='SYSTEM' Wink

Check any Temporary / Undo segement is using system tablespace.
after all of this coalace the system tablespace and then reszie the system tablespace datafile.

Regard's

Hemant.

Report message to a moderator

Re: how to reduce system data file [message #151493 is a reply to message #151316]

Thu, 15 December 2005 02:58

rajshah_ocp
Messages: 3
Registered: December 2005
Location: delhi

Junior Member

hi
i have check that one,and i didnt find another object inside system datafile except sys,system/otln.
i have also run this command select * from dba_free_space
and its display lots of free space inside system datafile.
but still its not reducing,plz tell me now what i have to do for reduce system datafile.

thanx

Report message to a moderator

Re: how to reduce system data file [message #152167 is a reply to message #151493]

Wed, 21 December 2005 08:03

sunil_v_mishra
Messages: 506
Registered: March 2005

Senior Member

well find the HWM (high water mark) then .... shrink the datafile ....

you can find the HWM for a table .. i dont know how to do it for tablespace.

Note:- shrinking the system datafile is possible on sun solaris but in windows it depends on file locking mechanism .. it may some time corrupt the system datafile so pls take backup before doing any things.

Regards
Sunilkumar

[Updated on: Wed, 21 December 2005 08:08]

Report message to a moderator

Re: how to reduce system data file [message #152418 is a reply to message #151493]

Fri, 23 December 2005 01:17

hemantpande
Messages: 2
Registered: December 2005
Location: Pune

Junior Member

Hello,

I think log of fragmentation is there so i think better way to remove it.

Hemant.

Report message to a moderator

Previous Topic:	Changing System Clock - RISKS ?
Next Topic:	Network components needed

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 13:35:01 CST 2025