Wednesday, October 30, 2013

Extend the System Tablespace on Oracle

There are two ways to extend the system table space.

1. Set the current datafile to AUTOEXTEND
2. Add a new datafile

1. Set the Current datafile to AUTOEXTEND

SELECT 'ALTER database datafile ''' || file_name || ''' ' || ' AUTOEXTEND ON maxsize 2097152000;'
FROM dba_data_files
WHERE tablespace_name = 'SYSTEM';

ALTER database datafile '/u01/app/oracle/oradata/XE/system.dbf'  AUTOEXTEND ON maxsize 2097152000;

database datafile '/U01/APP/ORACLE/ORADATA/XE/SYSTEM.DBF' altered.

2. Add a New datafile

alter tablespace SYSTEM add datafile '/u01/app/oracle/oradata/XE/system2.dbf' size 1024m;