Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Strange System tablespace behaviour !!

Chris Fung
Frequent Advisor

Strange System tablespace behaviour !!

Dear all,

My database just encountered a very strange behaviour today !! The monitoring script for checking the object extents sent alert message to the monitoring console and complianting no free space to allocate more extents for some of the database objects which all belongs to either "sys" or "system"

I checked against the database and found that the system tablespace still have plenty of room (541MB out of 600MB). And I rerun the script by myself and I could not notice the same alert message !!

Just wondering what happened to my system tablespace ?? I have gone through the Alert log as well, but there was no glue at all. The time for running the monitoring script is 19:00. I will assume the workload is not heavy at that moment (after office hours).

Since the script has been running for more than 10 months.....I assume it should be stable as well !!

Now, the only problem lies on the system tablespace !!

Please could you give me some idea on what is going on ?

By the way, my configuration is =>

Oracle 8.1.7.0.0
It is a datawarehouse database.

Much appreciated for your support.

Cheers,

Chris,
11 REPLIES
Bill Hassell
Honored Contributor

Re: Strange System tablespace behaviour !!

Since this is Oracle, most likely this is an SGA problem. First check that the DBA has not changed anything recently in the SGA setup. If all is well, Oracle may be requesting more shared memory than is actually available. For 32bit versions of Oracle, there are severe limitations on the maximum amount of shared memory that can be obtained for SGA. Shared memory comes out of a pool (a window) that also has memory mapped files and shared libraries, all of which will compete for the available space.

If Oracle needs a bit more space and no single segment is large enough (due to fragmentation) then Oracle will complain. You need to monitor the shared memory area with ipcs -bmop both during normal operations and then when Oracle reports a problem. A better tool is shminfo which is available at: ftp://contrib:9unsupp8@hprc.external.hp.com/sysadmin/programs/shminfo/


Bill Hassell, sysadmin
Thierry Poels_1
Honored Contributor

Re: Strange System tablespace behaviour !!

Hi,

did you check where these objects reside? Objects belonging to SYSTEM (or even SYS) do not necessarily reside in the SYSTEM tablespace. Maybe another tablespace ran out of free space?

Maybe you can also check the largest next extents for each tablespace. Maybe there are extreme values which can only be resolved after a tablespace coalesce.

BTW 600 MB for the system tablespace is quite large; lots of space wasted & extra time for backup and recovery.


regards,
Thierry.
All unix flavours are exactly the same . . . . . . . . . . for end users anyway.
Christian Gebhardt
Honored Contributor

Re: Strange System tablespace behaviour !!

Hi

In the system tablespace normally you have a rollback segment "system". This rbs grows and shrinks sometimes.

So if one transaction uses this rollbacksegment than the space is allocated by this transaction, after a "commit" or "rollback" the rbs shrinks to its "optimal size".

In the view "v$rollstat" you can check the status of your rollback segement and you can see for example if the rbs in the system tablespace has shrinks or you can see the maxsize (hwmsize) of your rbs.

select n.name,rssize,optsize,hwmsize, shrinks from v$rollstat s, v$rollname n where s.usn=n.usn;



Chris
Alexander M. Ermes
Honored Contributor

Re: Strange System tablespace behaviour !!

Hi there.
Pls check or have checked, if some user takes tablespace SYSTEM as temporary tablespace.
This might use SYSTEM for sorts etc.
Rgds
Alexander M. Ermes
.. and all these memories are going to vanish like tears in the rain! final words from Rutger Hauer in "Blade Runner"
Chris Fung
Frequent Advisor

Re: Strange System tablespace behaviour !!

Hi there,

Perhaps Alexander got his point that some user account is using the "System" tablespace as the temporary tablespace. However, I still have to confirm it by tomorrow !!

For other advice, rollback segment probably not the cause of this problem, because I have calcualted the total consumption for the "system" rollback segment, it only consume 28MB at most. Therefore, I don't think it contributed the problem.

What makes me so anxious is that we do not have any configuration change in SGA and other system configuration (except we created a new user account....and that's why I suspect it would be the cause of the problem although I remembered I set the temporary tablespace for that user account to "Temp".

To summaries my problem:
- The monitoring script looking for database objects that potentially cannot acquire next extent prompted up alert message. Database objects were belonged to "sys" and "System" => This indicated the system tablespace was almost full/lack of freespace at that particular moment.
- When I logged into the system and check the dba_free_space, the system tablespace has plenty of space ( 541MB out of 600MB)
- I rerun the monitor script manually and could not re-generate the same alert message.

Thank you very much for your help, will update U all on my investigation.

Cheers,

Chris,
Steven E. Protter
Exalted Contributor

Re: Strange System tablespace behaviour !!

Run this program on your system. It's install prep, but its really good at chekding out kernel issues.

go to metalink.oracle.com


download rda

install it and run it.

P
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Steven E. Protter
Exalted Contributor

Re: Strange System tablespace behaviour !!

Here's an old copy of rda.

Go to metalink for a more current one.

P
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Indira Aramandla
Honored Contributor

Re: Strange System tablespace behaviour !!

The error message that reported on the console will be recorded in the alert log. Please check to see the alert log and find which tablespace the error message is complaining about. And if the error message is about the system tablespace, then may be any user created objects int eh system tablespace. When you create objects and do not specify the tablespace and the temporary tablespace name in the create script, then the object will be created in the system tablespace and the temporary tablespace will be system as well. And the other thing is the amount of free space shown in the system tablespace out of 600MB may not be contigious free space.
Advice your users to specify the ts while creating objects. To find the contiguous_bytes in a tablespace refer the attachment.

Hope this helps.
Never give up, Keep Trying
Yogeeraj_1
Honored Contributor

Re: Strange System tablespace behaviour !!

hi,

please check who is using the SYSTEM tablespace using the following SQL:

select username,default_tablespace,temporary_tablespace
from dba_users
where default_tablespace='SYSTEM'
or temporary_tablespace='SYSTEM'

maybe this can shed some light!

Hope this helps!
Best Regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Rory R Hammond
Trusted Contributor

Re: Strange System tablespace behaviour !!

Chris
hope this might help.
Your note specifically identified that the objects in questioned belonged to sys or system.

Look at the percent increase for these objects and Check to see if they are trying to extend to something like 50% of it value. You might only have to modify the percent increase to 0 and the adjust the next and maxextents paramenters.



There are a 100 ways to do things and 97 of them are right
Chris Fung
Frequent Advisor

Re: Strange System tablespace behaviour !!

Dear all,

Thank you very much for all you support !!

As expected, the problem did not happen last night. I verified the DBA_USER views, and there is one user account have "System" as the temporary tablespace.

And I think this probably the ultimate reason for the cause (but I only 50% sure....)

Cause this account has been setup for another application (An ad hoc query application - BusinessObject) as a proxy for all the "BusinessObject" clients to connect through this proxy to access our database ........ I am just wondering whether this setup will have the same effect as I setup a normal user account to run some kinds of "Sorting" Which will cause the consumption of the temporary tablespace.....!! If so, then this must be cause!!

Many Thanks,

Cheers,

Chris,