How to increase the size of tablespace?

A tablespace can have multiple datafiles.

SQL> select tablespace_name,sum(bytes/1024/1024) from dba_data_files group by tablespace_name;

TABLESPACE_NAME                SUM(BYTES/1024/1024)
------------------------------                 --------------------
USERDATA                                         100
SYSAUX                                              200
UNDOTBS                                           100
SYSTEM                                               200

Here the system tablespace size is 200m if we want to increase the system tablespace size to 230m then?there are 2 ways 1)add datafile with size 30m   2)resize the system tablespace.


add the new datafile system1.dbf with size 30m.

SQL> alter tablespace system add datafile '/u01/app/oracle/system1.dbf' size 30m;

Tablespace altered.

SQL> select tablespace_name,sum(bytes/1024/1024) from dba_data_files group by tablespace_name;

TABLESPACE_NAME                SUM(BYTES/1024/1024)

------------------------------ --------------------

USERDATA                                        100

SYSAUX                                             200

UNDOTBS                                          100

SYSTEM                                             230

now the size of system tablespace is changed from 200 to 230mb.but before there is only one system datafile but now two datafiles for system tablespace.

SQL> select tablespace_name,file_name,bytes/1024/1024 from dba_data_files;

TABLESPACE_NAME                FILE_NAME                      BYTES/1024/1024

------------------------------ ------------------------------ ---------------

SYSTEM                         /u01/app/oracle/system.dbf                 200

SYSTEM                         /u01/app/oracle/system1.dbf                 30

2nd way is resizing the system tablespace datafile system.dbf from 200  to 230m.

SQL> alter database datafile '/u01/app/oracle/system.dbf' resize 230m;

Database altered.

SQL> select tablespace_name,file_name,bytes/1024/1024 from dba_data_files;

TABLESPACE_NAME                FILE_NAME                      BYTES/1024/1024
------------------------------ ------------------------------ ---------------
SYSTEM                         /u01/app/oracle/system.dbf                 230
SYSTEM                         /u01/app/oracle/system1.dbf                 30