Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, aijaz.

Asked: March 29, 2017 - 6:53 am UTC

Last updated: March 29, 2017 - 2:11 pm UTC

Version: Oracle 11g Enterprise Edn 11.2.0.3.0 - 64bit

Viewed 10K+ times! This question is

You Asked

Hi,

Is it possible to add a second datafile to a SYSTEM tablesapce of database, when this tablespace is almost full with few bytes free.
(This tablespace is AutoExtensible with Unlimited Maxbytes and Maxblocks)

Appreciate your response ASAP



and Chris said...

If the data file is autoextend on with unlimited maxbytes, there's no need to add a second one! Oracle Database will allocate more space when it needs it (assuming there's disk space available...)

So you don't need to do anything here. Let the files grow as they normally would.

But if you really do feel the need to, you certainly can add another file to the system tablespace:

select file_name, file_id, status from dba_data_files
where  tablespace_name = 'SYSTEM';

FILE_NAME                            FILE_ID  STATUS     
/ade/b/573371012/oracle/dbs/t_db1.f  1        AVAILABLE  

ALTER TABLESPACE SYSTEM ADD DATAFILE 't_db2.f' 
  SIZE 1M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
 
select file_name, file_id, status from dba_data_files
where  tablespace_name = 'SYSTEM';

FILE_NAME                            FILE_ID  STATUS     
/ade/b/573371012/oracle/dbs/t_db1.f  1        AVAILABLE  
/ade/b/573371012/oracle/dbs/t_db2.f  5        AVAILABLE  


Is this answer out of date? If it is, please let us know via a Comment