Home » RDBMS Server » Server Administration » large system01.dbf
large system01.dbf [message #58161] Tue, 05 August 2003 08:20 Go to next message
Joep
Messages: 2
Registered: August 2003
Junior Member
New machine, new installation oracle 8.1.7, creation 2 instances ORCL + LCS . Import of 6 databases by importfunction in database specific tablespaces.
Running for 6 weeks now
I see a system01.dbf for ORCL which measures 17.197 Gb.
Whats the next step to make.
resizing is no option.
can't find a user who has data in the system tablespace.
How to proceed ?
Thanks a lot.

Report message to a moderator

Re: large system01.dbf [message #58166 is a reply to message #58161] Tue, 05 August 2003 08:50 Go to previous messageGo to next message
shad
Messages: 50
Registered: October 2000
Member
Is database auditing enabled on the database fro which the export/import is coming from?
If so, then Truncate the sys.aud$ table and coalesce the tablespace before exporting and importing.
-- When u say you can not find users with objects how are u going about that? use
select OWNER,
SEGMENT_NAME,
SEGMENT_TYPE,
TABLESPACE_NAME,
BYTES
from dba_segments
where TABLESPACE_NAME = 'SYSTEM'
and OWNER not in ('SYS','SYSTEM')
order by OWNER, SEGMENT_NAME;
to find these objects in system tablespace.

Regards

Report message to a moderator

Re: large system01.dbf [message #58167 is a reply to message #58161] Tue, 05 August 2003 08:50 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
 
you mean , 
the system file size in OS IS 17 gb??.
and are u sure no USER tables/ indexes are in sytem tablespace?
then check for audit records and statistics. They might be still stored in system tablespace

and
is there any particular reason for 'RESIZING IS NOT AN OPTION?'

becuase 
i was under assumption that
' we can resize the system datafile as long as where are downsizing the datafile to a size that is higer than the size acutally used'.
sometime back i did the same in a test machine.
downized the system datafile to acutal_size_it_utilizing+ 40%_ofactuall_size_utlized)

Report message to a moderator

Re: large system01.dbf [message #58168 is a reply to message #58161] Tue, 05 August 2003 09:04 Go to previous messageGo to next message
shad
Messages: 50
Registered: October 2000
Member
Is database auditing enabled on the database fro which the export/import is coming from?
If so, then Truncate the sys.aud$ table and coalesce the tablespace before exporting and importing.
-- When u say you can not find users with objects how are u going about that? use
select OWNER,
SEGMENT_NAME,
SEGMENT_TYPE,
TABLESPACE_NAME,
BYTES
from dba_segments
where TABLESPACE_NAME = 'SYSTEM'
and OWNER not in ('SYS','SYSTEM')
order by OWNER, SEGMENT_NAME;
to find these objects in system tablespace.

Regards

Report message to a moderator

Re: large system01.dbf [message #58189 is a reply to message #58161] Wed, 06 August 2003 03:12 Go to previous message
Joep
Messages: 2
Registered: August 2003
Junior Member
New insights. I use 5 users databases with each 60 tables and 200 indexes. 95% of the data by an import is stored in the correct user tablespace. But there are some datafiles/index which are beeing created in the system tabelspace.I did several imports (n=5) to get things tuned whereafter i each time dropped the user. In the last import all the data and indexes have no relation with the system tabspace. But i think the repeated creation of some datafiles and indexes in the system datspace made it grow this large.
If this is the case how can i free it ?

Joep

Report message to a moderator

Previous Topic: Lose Password in9i
Next Topic: DB-link failed
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 20 09:53:20 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.