What will happen if your SYSTEM tablespace is running out of space..?


What will happen if your SYSTEM tablespace is running out of space..? and how will you check that..?
what are the possible work around for that issue..?
You can run the following
SQL>select AUTOEXTENSIBLE ,MAXBLOCKS,file_name ,tablespace_name from dba_data_files where tablespace_name=’SYSTEM’ ;
Check the autoextensible parameter value : YES or NO. If YES, and your mount point or drive is running out of space then turn autoextend OFF for that file and add a new datafile to a different mount or drive where you have got enough space.
To add datafile
SQL>Alter tablespace system add datafile ” size M ;
Else find the existing size of the datafile, and resize your existing datafile to bigger size for system tablespace :
SQL>Select bytes/1024/1024 “MB” ,name from v$datafile where ts# in (Select ts# from v$tablespace where name=’SYSTEM’) ;
SQL>Alter tablespace system datafile ” resize M ;
(FOR A BIG FILE TABLESPACE)
for the normal one, use:
SQL>ALTER DATABASE DATAFILE RESIZE M;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s