System tablespace 97 % full

[Jerome B]

Hi all,

The SYSTEM tablespace of a 8i DB is full at 97 %

Could it be the reason why Oracle take up to 99% CPU ? (more disk swapping
because of less disk space ?)

How could I empty it ?

my configuration is Oracle 8.1.7 on Dell P4/W2KPro

[Anthony Hogan]

Empty it??? The SYSTEM tablespace contains the data dictionary tables
for the entire database.

Extend the datafile or (preferably) add another.

Anthony Hogan

[Howard J. Rogers]

You couldn't.

Well, do some housekeeping checks first:

select table_name from dba_tables where tablespace='SYSTEM'
and owner <> 'SYS';

Nothing should come up, apart from SYSTEM owned stuff. Any of your *own*
objects should be moved to another tablespace pronto, and then you can
investigate why non-data dictionary stuff is getting into the dictionary
tablespace. A big no-no.

Do the same for dba_indexes.

Do a 'select username from dba_users where default_tablespace='SYSTEM'; and
a 'select username from dba_users where temporary_tablespace='SYSTEM';.
No-one bar SYS should have the default tablespace set to SYSTEM, and no-one
including SYS should have SYSTEM as their temporary tablespace.

Make sure SYSTEM is allowed to autoextend (it does by default, but some
"cleve" DBA may have thought this not a good idea and changed it. It *is* a
good idea: SYSTEM breaks all the "good" DBA rules, and is supposed to. It
knows what it's doing, so let it). Do a select * from dba_data_files and
make sure the "AUT" column is set to "Y". If not,
alter database datafile 'path/system01.dbf' autoextend on next 10m;'.

Also, make sure you are not using the dreadful auditing feature (show
parameter audit_trail. If it's set to anything other than NONE, you are). In
which case, try and get out of using it, and if you can't, at least move the
AUD$ table into its own tablespace, and its associated indexes into their
own tablespace, too:

create tablespace AUDIT_DATA etc etc;
create tablespace AUDIT_INDX etc etc
alter table sys.AUD$ move tablespace AUDIT_DATA;
create index i_aud1 on aud$(sessionid,ses$tid) tablespace AUDIT_INDX;

Otherwise, don't even think of playing around with what's in there. If it's
all legitimate, it stays put come hell or high water. It works that way.

Regards
HJR

"Jerome B" <jba...@oxymel.com> wrote in message
news:3d0ee7ab$0$239$626a...@news.free.fr...

[Knut Talman]

> The SYSTEM tablespace of a 8i DB is full at 97 %
>
> Could it be the reason why Oracle take up to 99% CPU ? (more disk swapping
> because of less disk space ?)
>
> How could I empty it ?

You can empty it (or get more free space) by deleting all tables, stored
procedures and so on. But I'm sure that's not what you wanted to hear.
The system tablespace holds the data dictionary=all information about the
objects in the database, the tablespaces, redo log files, everyting. There might
be two reasons why your system tablespace is at 97%.
1. There are users which store their objects in the system tablespace. That
happens if you create users and forget to specify the default tablespace. You
can check it with
select USERNAME, DEFAULT_TABLESPACE from dba_users;

2. It is too small -> resize it or add a new datafile

Regards,

Knut

[Richard Foote]

IF you've installed the Oracle JVM option and you have no real intention of
using it, you are potentially wasting a lot of space in the System
tablespace. Uninstalling it could help to effectively "empty" some of the
contents in the System tablespace.

Regards

Richard

"Jerome B" <jba...@oxymel.com> wrote in message
news:3d0ee7ab$0$239$626a...@news.free.fr...

[NorwoodThree]

Since you arent going to bother to learn about something you are
supposed to be supporting, by all means drop the system tablespace or
start dropping objects in it. That would be really funny! Let us
know how it goes...try it without a backup for extra fun.

[Daniel Morgan]

NorwoodThree wrote:

I want to watch.

Daniel Morgan

[Richard Foote]

Hi,

IF space is such a major issue (as the obvious suggestion of making the
system tablespace bigger is not being applied) AND the JVM option is
installed AND you have NO intention of using this option (or learning this
option as he could well have enough to learn about at the moment) THEN
uninstalling the JVM (with the utljavrm.sql and rmjvm.sql Oracle supplied
scripts) will effectively save much space (about 160M).

Sorry I hurt your feelings.

Richard

"NorwoodThree" <norwoo...@my-deja.com> wrote in message
news:ba03e2c.02061...@posting.google.com...

[Ivan Saez]

Jerome,

There are three posibilities:

1) users (other than sys and system) are creating objects in the system
tablespace.
2) auditing is enabled.
3) your system tablespace is just to small. Enlarge it.

Others have pointed out how to detect 1 & 2 and how to enlarge the system
tablespace.
About Oracle taking up 99% of the CPU : it's difficult to say something about
but I don't think it
has to do with your system tblspace being for 97% full.

kind regards,

Ivan

[Jerome B]

Hi Ivan,

First of all, thanks for your the respectful tone of your reply,

My issue was that the server was stuck at 100% cpu usage when a java program
inserting data into the db was running.
The insertion was ways slower than before ...

My surprise was to see a 97% full system tablespace, which seemed rather
abnormal to me (I ain't no dba but you surely guessed that).

I thought there was temp object created in it and left so I (badly) asked
how to erase them (and find them of course).

At the end, I solved the problem which was a misconfiguration of a tool (for
O/R mapping) used in that program, which made the class not use one of the
index.

Kind regards,

Jerome

"Ivan Saez" <i.a.saez....@tue.nl> a écrit dans le message de news:
3D108F1E...@tue.nl...

[Ivan Saez]

Jerome,

I'm glad you solved the problem. Probably the tools is creating temporary

objects in the system tablespace.

Check what temporary tablespace the account you are using has. May be it's
system. If so then change it to temp.

The tone some others replied to you was offensive. It's so easy to mock and make
wild assumptions.