Home » RDBMS Server » Server Administration » System Tablespace is almost full. (Oracle 10g,10.2.0.1.0,Window server 2003.)
System Tablespace is almost full. [message #435404] Wed, 16 December 2009 02:49 Go to next message
deep0983
Messages: 28
Registered: April 2009
Junior Member
Hello All,

I am facing one problem relating to space management. In my database system tablespace is full(98 %) and I don't have enough space on my hard disk for adding new datafile into it.

Pls help on this.

Any clue much appreciated.

Regards
Deep
Re: System Tablespace is almost full. [message #435407 is a reply to message #435404] Wed, 16 December 2009 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Drop/move all non-SYS objects that are in it.
Nothing else you can do.

Regards
Michel
Re: System Tablespace is almost full. [message #435409 is a reply to message #435407] Wed, 16 December 2009 02:54 Go to previous messageGo to next message
deep0983
Messages: 28
Registered: April 2009
Junior Member

Hi Michal,

Thanks for quick reply.

I have checked normal user's objects in system tablespace and below is the output of query which i used for this.

SQL> SELECT tablespace_name tbs,
2 owner,
3 segment_type,
4 COUNT(DISTINCT(segment_name )) anzahl,
5 SUM(bytes/1024/1024) mb
6 FROM dba_segments
7 WHERE tablespace_name = 'SYSTEM'
8 AND owner not in ('SYS','SYSTEM','OUTLN')
9 GROUP BY tablespace_name, segment_type, owner ;

no rows selected
Re: System Tablespace is almost full. [message #435411 is a reply to message #435404] Wed, 16 December 2009 02:58 Go to previous messageGo to next message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

Delete all trace and log files
If you have archived redo log files
- Delete all archived redo log files
- Delete all RMAN backups
- Take fresh copy of RMAN backup and put it on another server
Re: System Tablespace is almost full. [message #435412 is a reply to message #435411] Wed, 16 December 2009 03:02 Go to previous messageGo to next message
deep0983
Messages: 28
Registered: April 2009
Junior Member
can you please suggest any other alternative accept this?
Re: System Tablespace is almost full. [message #435413 is a reply to message #435411] Wed, 16 December 2009 03:03 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
And Also check for the audit logs too(if enabled)

sriram Smile
Re: System Tablespace is almost full. [message #435414 is a reply to message #435413] Wed, 16 December 2009 03:08 Go to previous messageGo to next message
deep0983
Messages: 28
Registered: April 2009
Junior Member
Hello sriram,

My database is running on no archivelog mode.

Pls send neccssary command to check audit logs is enabled or not.

Regards
Deep
Re: System Tablespace is almost full. [message #435415 is a reply to message #435414] Wed, 16 December 2009 03:13 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL> sho parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      NONE


SQL> select count(*) from sys.aud$;

  COUNT(*)
----------
         0



And how do you know that it is 98 % full?(How you are calculating).
sriram Smile

[Updated on: Wed, 16 December 2009 03:24]

Report message to a moderator

Re: System Tablespace is almost full. [message #435417 is a reply to message #435409] Wed, 16 December 2009 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can move SYSTEM and OUTLN objects outside SYSTEM tablespace.

Regards
Michel
Re: System Tablespace is almost full. [message #435420 is a reply to message #435404] Wed, 16 December 2009 03:56 Go to previous messageGo to next message
deep0983
Messages: 28
Registered: April 2009
Junior Member
I can use this but what i have found these objects are not occupying much space in system tablespace.

So I think move it outside system tablespace will not solve problem.
Re: System Tablespace is almost full. [message #435421 is a reply to message #435404] Wed, 16 December 2009 04:01 Go to previous messageGo to next message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

Add new hard disk Razz
Re: System Tablespace is almost full. [message #435422 is a reply to message #435420] Wed, 16 December 2009 04:03 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
I can use this but what i have found these objects are not occupying much space in system tablespace.


Please show us.How you are calculating.
Try to Remove all unnecessary files from the hard disk and add datafile.


sriram Smile
Re: System Tablespace is almost full. [message #435424 is a reply to message #435420] Wed, 16 December 2009 04:04 Go to previous messageGo to next message
deep0983
Messages: 28
Registered: April 2009
Junior Member
These are some of few objects which occupy large amount of space from my system tablespace.

SEGMENT_NAME SEGMENT_TYPE OWNER BLOCKS BYTES/1024/1024

C_OBJ#_INTCOL# CLUSTER SYS 140160 1095
SOURCE$ TABLE SYS 120712 943.0625
IDL_UB1$ TABLE SYS 105808 826.625
IDL_UB2$ TABLE SYS 35448 276.9375
C_TOID_VERSION# CLUSTER SYS 25776 201.375
I_H_OBJ#_COL# INDEX SYS 16768 131

Hope this will help.

[Updated on: Wed, 16 December 2009 04:05]

Report message to a moderator

Re: System Tablespace is almost full. [message #435427 is a reply to message #435424] Wed, 16 December 2009 04:08 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
http://mwidlake.wordpress.com/2009/08/03/why-is-my-system-tablespace-so-big/

sriram Smile
Re: System Tablespace is almost full. [message #435428 is a reply to message #435404] Wed, 16 December 2009 04:09 Go to previous messageGo to next message
deep0983
Messages: 28
Registered: April 2009
Junior Member
Thanks for your help All.

Now can you please suggest what all mehod we can use in oracle when our system tablespace is almost full? Becuase i think increase space in a datafile is only solution for solving these types of problem.

Re: System Tablespace is almost full. [message #435429 is a reply to message #435428] Wed, 16 December 2009 04:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
YOu've already told us that you don't have the space to increase your datafiles.
Re: System Tablespace is almost full. [message #435431 is a reply to message #435404] Wed, 16 December 2009 04:17 Go to previous messageGo to next message
deep0983
Messages: 28
Registered: April 2009
Junior Member

I want to convey was the available option for solving these types of issue.

Yes I agree I don't have enough space on my hard drive for adding or resizing datafile.
Re: System Tablespace is almost full. [message #435438 is a reply to message #435431] Wed, 16 December 2009 04:27 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

deep0983 wrote on Wed, 16 December 2009 11:17

Yes I agree I don't have enough space on my hard drive for adding or resizing datafile.

So solution accepted Razz
Re: System Tablespace is almost full. [message #435439 is a reply to message #435431] Wed, 16 December 2009 04:30 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
i guess this is all about testing (database) server.

Sriram Smile
Re: System Tablespace is almost full. [message #435557 is a reply to message #435431] Wed, 16 December 2009 16:03 Go to previous message
serhade
Messages: 1
Registered: December 2009
Location: IST-Turkey
Junior Member
Move datafiles to another disk which have more free space.
Look at this.

Serhad Erdem

SQL> select file_name from dba_data_files where tablespace_name='SYSTEM';

FILE_NAME
--------------------------------------------------------------------------------
/u01/oracle/ora10g/oradata/test/system01.dbf

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1301512 bytes
Variable Size 119809016 bytes
Database Buffers 163577856 bytes
Redo Buffers 524288 bytes
Database mounted.
SQL> !cp /u01/oracle/ora10g/oradata/test/system01.dbf
/u01/oracle/ora10g/oradata/test/system001.dbf


SQL> alter database rename file
'/u01/oracle/ora10g/oradata/test/system01.dbf' to
'/u01/oracle/ora10g/oradata/test/system001.dbf';

Database altered.

SQL> alter database open;

Database altered.
Previous Topic: Can be copy a file from ASM file system to Unix file system without using RMAN
Next Topic: Upgrade from Oracle 9i to Oracle 10g
Goto Forum:
  


Current Time: Fri Dec 02 18:17:51 CST 2016

Total time taken to generate the page: 0.33794 seconds