Sign up ×

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?

share|improve this question
Do you know why the SYSTEM tablespace is running out of space? That's pretty odd in an 11.2 database-- if someone has incorrectly created an object in the SYSTEM tablespace, that's something you need to correct. Even things like AUD$ are normally moved to SYSAUX. If you really are simply running out of space but everything in the tablespace should be in the tablespace, adding a new data file isn't a big deal. –  Justin Cave Dec 17 '12 at 20:57
Thanks for the quick response, its a data warehousing environment, i think tablespace is undersize (just 4GB ) , do you think undersized system tablespace can contribute to the slowness of full database backup(datapump) , second , do i need to take any precautionary measure before adding the new data file into system tablespace , or its just like adding data file for any other tablespace? –  user16308 Dec 17 '12 at 21:05
I'm not sure what an "undersized system tablespace" means. Normally, the SYSTEM tablespace doesn't grow meaningfully after the database is installed and the application is created. A full database backup would be done using RMAN, DataPump would be for creating a logical backup (which is not a substitute for a physical backup-- if you don't have a physical backup, you absolutely need to back up your database). It seems highly unlikely that performance issues would be caused by a lack of free space in the SYSTEM tablespace unless something was badly configured. –  Justin Cave Dec 17 '12 at 21:12
I agree with you , we are working on having the RMAN setup , so can i just resize the system tablespace(increase the size of existing datafile) rather adding the data file on ASM to avoid the overhead on controfile? –  user16308 Dec 17 '12 at 21:16
I'm with @JustinCave on this one. Do a root cause analysis first to find out why the tablespace is so big - don't add a datafile or extend yet. If you need help with that, ask another question. –  Phil Dec 17 '12 at 21:18

1 Answer 1

I'm assuming you're not using ASM.

Either set the current datafile to AUTOEXTEND:

alter database datafile 
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.

share|improve this answer
its a data warehousing environment , size of database is 3.5TB, we have LOBs in this db, which is taking most of the space, although we have dedicate tablespace for LOBs. –  user16308 Dec 17 '12 at 21:25
@user16308 - The size of the database should have little or nothing to do with the size of the SYSTEM tablespace. The SYSTEM tablespace needs a few bytes to store information about the definition of a table or an index. That space is constant, though, whether the table is empty or whether it has 1 billion rows. –  Justin Cave Dec 17 '12 at 21:29

Your Answer


