Strange System tablespace behaviour !!

[Chris Fung]

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,

[Bill Hassell]

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/

[Thierry Poels_1]

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.

[Christian Gebhardt]

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]

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

[Chris Fung]

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]

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]

Here's an old copy of rda.

Go to metalink for a more current one.

P

[Indira Aramandla]

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.

[Yogeeraj_1]

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

[Rory R Hammond]

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.

[Chris Fung]

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,