Add space to Tablespace

===============================
Resize Datafile.
===============================
1. Check current datafile size.

SELECT file_name, tablespace_name, 
       ROUND(bytes/1024/1024) AS bytes_mb, 
       ROUND(maxbytes/1024/1024) AS maxbytes_mb
FROM DBA_DATA_FILES;

2. Resize datafile.
ALTER DATABASE DATAFILE '/path/to/data/file/name.dbf' RESIZE 2000M;

===============================
Create Tablespace
===============================

CREATE TABLESPACE MY_TABLESPACE 

DATAFILE '/oracle_db/db1/db_sid/my_index_01.dbf' 
SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED;
[EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO]

===============================
Add new Datafile to existing Tablespace
===============================
ALTER TABLESPACE MY_TABLESPACE 
ADD DATAFILE '/oracle_db/db1/db_sid/my_index_02.dbf' 
SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED;
[EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO]

===============================
Handle ORA-30036:unable to extend segment
===============================

ORA-30036: unable to extend segment is thrown when when Tablespace is running out of space:

Steps to resolve this error:
1. Check host space availability.

On Linix:
oracle@host:/software/oracle/admin/sid>df . -h
Filesystem  Size  Used  Avail Use% Mounted on
/dev/mapper/Volume00-LogVol14
            7.9G  4.5G  3.4G  58%  /software/oracle

2. Add space to Tablespace

Option A.  Add Datafile to Tablespace

When adding new datafile, existing storage definitions of existing Tablespace cannot be changed.

Example I. Datafile with pre-defined and unlimited size:

ALTER TABLESPACE MY_TBS 
ADD DATAFILE '/path/to/data/file/name/ora_tbs_02.dbf' 
SIZE 100M AUTOEXTEND ON maxsize 500M;

Example II. Datafile with unlimited size: (OS limit, 32 Gb)

ALTER TABLESPACE MY_TBS 
ADD DATAFILE '/path/to/data/file/name/ora_tbs_02.dbf'
SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED;

Option B. Resize Existing Datafile:
Current situation:
SELECT tablespace_name, 
       file_name, 
       max_bytes/1024/1024 AS max_bytes_MB
FROM DBA_DATA_FILES;

To add more space:
Resize the datafile:
ALTER DATABASE DATAFILE '/path/to/data/file/name.dbf' RESIZE 2000M;

or increase maxsize:
ALTER DATABASE DATAFILE '/path/to/data/file/name.dbf' AUTOEXTEND ON MAXSIZE 2000M;

or, set to unlimited:
ALTER DATABASE DATAFILE '/path/to/data/file/name.dbf' 
      AUTOEXTEND ON MAXSIZE UNLIMITED;

===============================
Free up space to Linux
===============================

DROP TABLESPACE my_tbs INCLUDING CONTENTS AND DATAFILES;

To free up space taken up a datafile.
ALTER DATABASE DATAFILE '/oracle_db/db1/db_orainst/orainst_01.dbf' RESIZE 1M;
ORA-03214: File Size specified is smaller than minimum required

SELECT TABLESPACE_NAME, BLOCK_SIZE, INITIAL_EXTENT 
FROM DBA_TABLESPACES 
WHERE TABLESPACE_NAME LIKE '%SOME_TBS%'
  
TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT
------------------------------ ---------- --------------
SOME_TBS_TABLE                       8192          65536
SOME_TBS_INDEX                       8192          65536

The size of datafile cannot be smaller than the size of initial extent.

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_gin_01.dbf' RESIZE 70M;
ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_ginindex_01.dbf' RESIZE 70M;
Database altered.

===============================
RESIZE vs AUTOEXTEND ON MAXSIZE
===============================
To add more space is is possible to use either:
Resize the datafile:
ALTER DATABASE DATAFILE '/path/to/data/file/name.dbf' RESIZE 2000M;

or increase Maxsize:
ALTER DATABASE DATAFILE '/path/to/data/file/name.dbf' AUTOEXTEND ON MAXSIZE 2000M;

When using the RESIZE Syntax, Oracle does not update maxbytes value in DBA_DATA_FILES.
This can lead to weird findings. 
To sync maxbytes to the actual size, use 'AUTOEXENT ON MAXSIZE' command

For example:
SET LINESIZE 140
SET PAGESIZE 200
COL tablespace_name FOR A30
COL file_name FOR A60
COL Mb FOR 9999999999999
COL MAX_MB FOR 9999999999999

SELECT tablespace_name, 
       file_name, 
       ROUND(bytes/1024/1024) AS Mb,   
       ROUND(maxbytes/1024/1024) AS MAX_MB
 FROM DBA_DATA_FILES
ORDER BY tablespace_name, file_name;  

Initial Situation. IGT_TABLE tablespace is 100% full

TABLESPACE_NAME FILE_NAME                                        MB      MAX_MB
--------------- ------------------------------------------- ------- -----------
IGT_INDEX       /oracle_db/db1/db_igt/ora_igt_index_01.dbf     8000       12000
IGT_TABLE       /oracle_db/db1/db_igt/ora_igt_table_01.dbf    14000       14000
SYSAUX          /oracle_db/db1/db_igt/ora_sysaux_01.dbf         500        6000
SYSTEM          /oracle_db/db1/db_igt/ora_system_01.dbf         700        6000
UNDOTBS         /oracle_db/db1/db_igt/ora_undotbs_01.dbf        900        2000
WORKAREA        /oracle_db/db1/db_igt/ora_workarea_01.dbf       600        6000

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_igt_table_01.dbf' RESIZE 24000M;

TABLESPACE_NAME FILE_NAME                                        MB      MAX_MB
--------------- ------------------------------------------- ------- -----------
IGT_INDEX       /oracle_db/db1/db_igt/ora_igt_index_01.dbf     8000       12000
IGT_TABLE       /oracle_db/db1/db_igt/ora_igt_table_01.dbf    24000       14000
SYSAUX          /oracle_db/db1/db_igt/ora_sysaux_01.dbf         500        6000
SYSTEM          /oracle_db/db1/db_igt/ora_system_01.dbf         700        6000
UNDOTBS         /oracle_db/db1/db_igt/ora_undotbs_01.dbf        900        2000
WORKAREA        /oracle_db/db1/db_igt/ora_workarea_01.dbf       600        6000

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_igt_table_01.dbf' AUTOEXTEND ON MAXSIZE 24000M;

TABLESPACE_NAME FILE_NAME                                        MB      MAX_MB
--------------- ------------------------------------------- ------- -----------
IGT_INDEX       /oracle_db/db1/db_igt/ora_igt_index_01.dbf     8000       12000
IGT_TABLE       /oracle_db/db1/db_igt/ora_igt_table_01.dbf    24000       24000
SYSAUX          /oracle_db/db1/db_igt/ora_sysaux_01.dbf         500        6000
SYSTEM          /oracle_db/db1/db_igt/ora_system_01.dbf         700        6000
UNDOTBS         /oracle_db/db1/db_igt/ora_undotbs_01.dbf        900        2000
WORKAREA        /oracle_db/db1/db_igt/ora_workarea_01.dbf       600        6000

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_igt_table_01.dbf' RESIZE 18000M;

TABLESPACE_NAME FILE_NAME                                        MB      MAX_MB
--------------- ------------------------------------------- ------- -----------
IGT_INDEX       /oracle_db/db1/db_igt/ora_igt_index_01.dbf     8000       12000
IGT_TABLE       /oracle_db/db1/db_igt/ora_igt_table_01.dbf    18000       24000
SYSAUX          /oracle_db/db1/db_igt/ora_sysaux_01.dbf         500        6000
SYSTEM          /oracle_db/db1/db_igt/ora_system_01.dbf         700        6000
UNDOTBS         /oracle_db/db1/db_igt/ora_undotbs_01.dbf        900        2000
WORKAREA        /oracle_db/db1/db_igt/ora_workarea_01.dbf       600        6000

And this is the actual size of the file, on Linux:
-rw-r----- 1 oracle dba 18874376192 Mar 15 11:45 ora_igt_table_01.dbf

18874376192/1024/1024=18000


===============================
Switch UNDO Datafile
===============================

Create new tablespace UNDO_02

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/YOUR/NEW/LOCATION/UNDOTBS02.DBF' SIZE 1000M AUTOEXTEND ON MAXSIZE 6000M;;

Switch to new UNDO_02 Tablespace.

ALTER SYSTEM SET UNDO_TABLESPACE= UNDOTBS2;

ALTER TABLESPACE UNDOTBS OFFLINE;

Drop the old UNDO Tablespace.

DROP TABLESPACE UNDOTBS INCLUDING CONTENTS;

ORA-30042: Cannot offline the undo tablespace

In case of an error ORA-30042: Cannot offline the undo tablespace

SQL>  ALTER TABLESPACE UNDOTBS OFFLINE;

 ALTER TABLESPACE UNDOTBS OFFLINE

*

ERROR at line 1:

ORA-30042: Cannot offline the undo tablespace

1. Check that no rollback segment is in 'NEEDS RECOVERY' status:

SQL> SELECT DISTINCT status from DBA_ROLLBACK_SEGS;

STATUS

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

ONLINE

OFFLINE

2. After couple of minutes issue same command again.

SQL>  ALTER TABLESPACE UNDOTBS OFFLINE;

Tablespace altered.

===============================

Switch Temporary Datafile
===============================

Create new tablespace TEMP_02

CREATE TEMPORARY TABLESPACE TEMP_02

TEMPFILE ‘/u01/oradata/TESTDB/temp2_01.dbf’ SIZE 1000M AUTOEXTEND ON MAXSIZE 6000M;

Switch to new TEMP_02 Tablespace.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_02;

Drop the old TEMP Tablespace.

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

Appendix

Altering Indexes
Costs and Benefits of Coalescing or Rebuilding Indexes