OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » system table space (Ora 10gR2 on RH Linux)

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

system table space [message #527141]

Sat, 15 October 2011 11:02

vdsk
Messages: 41
Registered: February 2006
Location: India & UAE

Member

Hi
a newbie dba here..

select TABLESPACE_NAME,
sum(BYTES) Total_free_space,
max(BYTES) largest_free_extent
from dba_free_space
group by TABLESPACE_NAME
/

result output is attached .txt file.

the SYSTEM & USERS table space shows only <10 mb free space.
Is it a bad sign? What I should do ?

Attachment: dbfilespace.txt
(Size: 0.27KB, Downloaded 1110 times)

Report message to a moderator

Re: system table space [message #527142 is a reply to message #527141]

Sat, 15 October 2011 11:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311

Report message to a moderator

Re: system table space [message #527146 is a reply to message #527141]

Sat, 15 October 2011 12:10

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

1/ Many of us can't or don't want to download files, so post them inline in text mode and post them formatted.

2/ Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

3/ Also always post your Oracle version, with 4 decimals.

4/ Use SQL*Plus and copy and paste your session.

5/ Oracle don't care about USERS tablespace, it is yours

6/ Having few space in SYSTEM tablespace is dangerous.

Regards
Michel

Report message to a moderator

Re: system table space [message #527179 is a reply to message #527141]

Sun, 16 October 2011 08:30

vdsk
Messages: 41
Registered: February 2006
Location: India & UAE

Member

Thanks for replies..Shall adhere to posting guidelines.

Select tablespace_name, autoextensible from dba_data_files

This solves my query as I find SYSTEM, SYSAUX,USER & UNDOTBS1
are autoextended.

Report message to a moderator

Re: system table space [message #527180 is a reply to message #527179]

Sun, 16 October 2011 08:56

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Thank you for the feedback.

Regards
Michel

Report message to a moderator

Re: system table space [message #527181 is a reply to message #527179]

Sun, 16 October 2011 09:03

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Hello - just one word of advice: generally speaking, you shouldn't rely on autoextend. A good DBA will monitor space usage, and add space in advance. This is particularly important for SYSTEM: you don't want your users to take the pain of delays when SYSTEM is extended. Related to this, if you do have autoextend enabled, check the columns MAXBYTES and INCREMENT_BY in DBA_DATA_FILES. The defaults will let your files grow to the maximum possible which might fill your filesystem, and the increase will be in really small units which is dreadfully inefficient. I usually set the maximum size after asking my sysadmins what file size they are happy with, and set the increment to 1GB so that it won't happen to often.

Report message to a moderator

Re: system table space [message #527207 is a reply to message #527181]

Mon, 17 October 2011 00:49

vdsk
Messages: 41
Registered: February 2006
Location: India & UAE

Member

Hi John

SELECT *
FROM dba_data_files
WHERE tablespace_name IN ('SYSTEM', 'SYSAUX','USER' , 'UNDOTBS1');

Attached files.lst text format.

As you can see , increment_by is small.

Your comments pl.

Thanks

Attachment: files.lst
(Size: 4.04KB, Downloaded 1477 times)

Report message to a moderator

Re: system table space [message #527220 is a reply to message #527207]

Mon, 17 October 2011 01:34

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Do not put SYSTEM tablespace in autoextend but assure there is at 10% of free space.

Many of us can't or don't want to download files, so post them inline in text mode and post them formatted.

Regards
Michel

Report message to a moderator

Previous Topic:	table refresh
Next Topic:	long convert varchar2 issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 14:09:19 CDT 2024