Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite
1

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
1  
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
1  
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
2  
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

up vote 3 down vote

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.

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
2  
@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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.