Home » RDBMS Server » Server Administration » system table space (Ora 10gR2 on RH Linux)
system table space [message #527141] Sat, 15 October 2011 11:02 Go to next message
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 ?
Re: system table space [message #527142 is a reply to message #527141] Sat, 15 October 2011 11:17 Go to previous messageGo to next message
BlackSwan
Messages: 21944
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
Re: system table space [message #527146 is a reply to message #527141] Sat, 15 October 2011 12:10 Go to previous messageGo to next message
Michel Cadot
Messages: 57611
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
Re: system table space [message #527179 is a reply to message #527141] Sun, 16 October 2011 08:30 Go to previous messageGo to next message
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.


Re: system table space [message #527180 is a reply to message #527179] Sun, 16 October 2011 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 57611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thank you for the feedback.

Regards
Michel
Re: system table space [message #527181 is a reply to message #527179] Sun, 16 October 2011 09:03 Go to previous messageGo to next message
John Watson
Messages: 4082
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.
Re: system table space [message #527207 is a reply to message #527181] Mon, 17 October 2011 00:49 Go to previous messageGo to next message
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 49 times)
Re: system table space [message #527220 is a reply to message #527207] Mon, 17 October 2011 01:34 Go to previous message
Michel Cadot
Messages: 57611
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
Previous Topic: table refresh
Next Topic: long convert varchar2 issue
Goto Forum:
  


Current Time: Fri Apr 18 17:43:14 CDT 2014

Total time taken to generate the page: 0.10923 seconds