Re: System tablespace 97 % full

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" <jbaton_at_oxymel.com> wrote in message news:3d0ee7ab$0$239$626a54ce_at_news.free.fr...
> 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
>
>
Received on Tue Jun 18 2002 - 03:36:47 CDT