Expanding system tablespace

oracleoracle-11g-r2tablespaces

I need to add new data file into the system tablespace because its running out of space. Are there any precautionary measures I should consider since this is a production environment?

Best Answer

I'm assuming you're not using ASM.

Either set the current datafile to AUTOEXTEND:

alter database datafile 
   '/full/path/to/system01.dbf' 
autoextend on
next 128m
maxsize 8192m;

Or, add a new datafile:

alter tablespace SYSTEM
  add datafile '/full/path/to/system02.dbf'
size 512m;

Obviously, alter the numbers and path as needed.

Adding a datafile impacts your backups. Take a full backup immediately after adding it - this is the only thing you really have to worry about.

4Gb is HUGE for a SYSTEM tablespace. I'd first investigate whether there are some large objects being stored in the tablespace that shouldn't be there (DBA_EXTENTS is your friend).

Also, note that you cannot remove a datafile once it has been added to the SYSTEM tablespace.

Related Question