| System Tablespace is almost full. [message #435404] |
Wed, 16 December 2009 02:49  |
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 #435409 is a reply to message #435407] |
Wed, 16 December 2009 02:54  |
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 #435415 is a reply to message #435414] |
Wed, 16 December 2009 03:13 |
 |
ramoradba
Messages: 2456
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 
[Updated on: Wed, 16 December 2009 03:24] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: System Tablespace is almost full. [message #435424 is a reply to message #435420] |
Wed, 16 December 2009 04:04 |
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 #435428 is a reply to message #435404] |
Wed, 16 December 2009 04:09 |
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 #435557 is a reply to message #435431] |
Wed, 16 December 2009 16:03 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
