Manage SYSAUX size by Example.

========================
General
========================
There is a general alert for Tablespace low on free space.

To check which Tablespace is low on disk space:

SELECT TABLESPACE_NAME, 
    (MAX(MAX_SPACE)-MAX(USED_SPACE)  )AS DBA_FREE_SPACE_MB,
    MAX(USED_SPACE) AS USED_SPACE_MB, 
    MAX(MAX_SPACE) AS MAX_SPACE_MB, 
    ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100) as FREE_PCT, 
    CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100)<15)  THEN 'Y' ELSE 'N' END AS ADD_MORE_SPACE 
FROM ( 
SELECT tablespace_name,  
     ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE, 
     0 AS MAX_SPACE, 
     0 AS USED_SPACE   
 FROM DBA_FREE_SPACE
 WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM' OR tablespace_name = 'SYSAUX'  
 GROUP BY tablespace_name  
 UNION ALL 
 SELECT tablespace_name, 
     0 AS FREE_SPACE, 
     ROUND( SUM(CASE WHEN (bytes>maxbytes)  THEN bytes ELSE maxbytes END)/1024/1024) AS MAX_SPACE, 
     0 AS USED_SPACE 
 FROM DBA_DATA_FILES
 WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM' OR tablespace_name = 'SYSAUX' 
 GROUP BY tablespace_name 
 UNION ALL 
 SELECT tablespace_name, 
     0 AS FREE_SPACE, 
     0 AS MAX_SPACE, 
     ROUND(SUM(bytes/1024/1024)) AS USED_SPACE 
  FROM DBA_SEGMENTS
 WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM' OR tablespace_name = 'SYSAUX' 
GROUP BY tablespace_name 
) 
GROUP BY tablespace_name;

TABLESPACE_NAME DBA_FREE_SPACE_MB USED_SPACE_MB MAX_SPACE_MB  FREE_PCT ADD_MORE_SPACE
--------------- ----------------- ------------- ------------ --------- --------------
DWH_INDEX                   13200         29568        42768        31 N
SYSAUX                         78           922         1000         8 Y
IGT_TABLE                    6351          7649        14000        45 N
SYSTEM                        637           363         1000        64 N
IGT_INDEX                    6604          1396         8000        83 N
DWH_TABLE                   19792         12976        32768        60 N

It is SYSAUX Tablespace.

To Check what Segment is occupying SYSAUX Tablespace:

SELECT owner, tablespace_name, segment_name, USED_MB

 FROM 

  (SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB

     FROM DBA_SEGMENTS

    WHERE tablespace_name LIKE '%SYSAUX%'

    GROUP BY owner, tablespace_name,segment_name

    ORDER BY ROUND(SUM(bytes)/1024/1024) DESC

  )

 WHERE ROWNUM < 11;

OWNER      TABLESPACE_NAME SEGMENT_NAME                      USED_MB

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

SYS        SYSAUX          WRH$_ACTIVE_SESSION_HISTORY           341

SYS        SYSAUX          I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST         72

SYS        SYSAUX          WRH$_ACTIVE_SESSION_HISTORY_PK         51

SYS        SYSAUX          WRI$_OPTSTAT_HISTGRM_HISTORY           41

SYS        SYSAUX          I_WRI$_OPTSTAT_H_ST                    29

SYS        SYSAUX          WRH$_SYSMETRIC_HISTORY                 27

SYS        SYSAUX          WRH$_SYSMETRIC_HISTORY_INDEX           20

SYS        SYSAUX          WRI$_ADV_MESSAGE_GROUPS_PK             13

SYS        SYSAUX          SCHEDULER$_EVENT_LOG                   12

SYS        SYSAUX          SYS_LOB0000005981C00038$$              12

Looks like AWR data is occupying most data.

Another tools to check for SYSAUX contents:

A. V$SYSAUX_OCCUPANTS View

SELECT OCCUPANT_NAME,OCCUPANT_DESC, SCHEMA_NAME, MOVE_PROCEDURE, SPACE_USAGE_KBYTES 

  FROM V$SYSAUX_OCCUPANTS 

ORDER BY SPACE_USAGE_KBYTES DESC;

OCCUPANT_NAME        OCCUPANT_DESC                  SCHEMA_NAME     MOVE_PROCEDURE       SPACE_USAGE_KBYTES

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

SM/AWR               Server Manageability - Automat SYS                                              592448

                     ic Workload Repository                                              

SM/OPTSTAT           Server Manageability - Optimiz SYS                                              175104

                     er Statistics History                                               

SM/ADVISOR           Server Manageability - Advisor SYS                                               58048

                      Framework                                                          

XDB                  XDB                            XDB             XDB.DBMS_XDB.MOVEXDB              56256

JOB_SCHEDULER        Unified Job Scheduler          SYS                                               15104

LOGMNR               LogMiner                       SYSTEM          SYS.DBMS_LOGMNR_D.SE               7808 

WM                   Workspace Manager              WMSYS           DBMS_WM.move_proc                  7296

SM/OTHER             Server Manageability - Other C SYS                                                6336

                     omponents                                                            

SMON_SCN_TIME        Transaction Layer - SCN to TIM SYS                                                3328

                     E mapping                                                           

EM_MONITORING_USER   Enterprise Manager Monitoring  DBSNMP                                             1920

                     User                                                                

SQL_MANAGEMENT_BASE  SQL Management Base Schema     SYS                                                1728

PL/SCOPE             PL/SQL Identifier Collection   SYS                                                1472

AO                   Analytical Workspace Object Ta SYS             DBMS_AW.MOVE_AWMETA                1408

XSOQHIST             OLAP API History Tables        SYS             DBMS_XSOQ.OlapiMoveProc            1408

LOGSTDBY             Logical Standby                SYSTEM          SYS.DBMS_LOGSTDBY.                 1024

STREAMS              Oracle Streams                 SYS                                                1024

B. utlsyxsz.sql script.

sqlplus / as sysdba @/software/oracle/111/rdbms/admin/utlsyxsz.sql

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Apr 13 07:33:41 2016

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

This script estimates the space required for the SYSAUX tablespace.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the Report File Name

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is utlsyxsz.txt.  To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name: SYSAUX_USAGE.txt

Using the report name SYSAUX_USAGE.txt

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SYSAUX Size Estimation Report

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Estimated at

07:34:17 on Apr 13, 2016 ( Wednesday ) in Timezone -05:00

DB_NAME     HOST_PLATFORM                             INST STARTUP_TIME      PAR

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

* IGT       rnd-mas-1-ver-5 - Linux x86 64-bit           1 07:28:45 (04/06)  NO

~~~~~~~~~~~~~~~~~~~~

Current SYSAUX usage

~~~~~~~~~~~~~~~~~~~~

| Total SYSAUX size:                         922.3 MB

|

| Total size of SM/AWR                       578.6 MB (  62.7% of SYSAUX )

| Total size of SM/OPTSTAT                   171.0 MB (  18.5% of SYSAUX )

| Total size of SM/ADVISOR                    56.7 MB (   6.1% of SYSAUX )

| Total size of XDB                           54.9 MB (   6.0% of SYSAUX )

| Total size of JOB_SCHEDULER                 14.8 MB (   1.6% of SYSAUX )

| Total size of LOGMNR                         7.6 MB (   0.8% of SYSAUX )

| Total size of WM                             7.1 MB (   0.8% of SYSAUX )

| Total size of SM/OTHER                       6.2 MB (   0.7% of SYSAUX )

| Total size of SMON_SCN_TIME                  3.3 MB (   0.4% of SYSAUX )

| Total size of EM_MONITORING_USER             1.9 MB (   0.2% of SYSAUX )

| Total size of SQL_MANAGEMENT_BASE            1.7 MB (   0.2% of SYSAUX )

| Total size of PL/SCOPE                       1.4 MB (   0.2% of SYSAUX )

| Total size of XSOQHIST                       1.4 MB (   0.1% of SYSAUX )

| Total size of AO                             1.4 MB (   0.1% of SYSAUX )

| Total size of STREAMS                        1.0 MB (   0.1% of SYSAUX )

| Total size of LOGSTDBY                       1.0 MB (   0.1% of SYSAUX )

| Total size of AUTO_TASK                      0.3 MB (   0.0% of SYSAUX )

| Total size of TSM                            0.3 MB (   0.0% of SYSAUX )

| Total size of Others                        11.9 MB (   1.3% of SYSAUX )

|

~~~~~~~~~~~~~~~~~~~~

AWR Space Estimation

~~~~~~~~~~~~~~~~~~~~

| To estimate the size of the Automatic Workload Repository (AWR)

| in SYSAUX, we need the following values:

|

|     - Interval Setting (minutes)

|     - Retention Setting (days)

|     - Number of Instances

|     - Average Number of Active Sessions

|     - Number of Datafiles

|

| For 'Interval Setting',

|   Press <return> to use the current value:     60.0 minutes

|   otherwise enter an alternative

|

C. Just make sure that there are no "zombie" old snapshots.

Check for oldest snapshot using DBA_HIST_SNAPSHOT or SYS.WRM$_SNAPSHOT, and then purge old snapshots with DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range();

For Example:

BEGIN                                                               

  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range(low_snap_id => 7556, high_snap_id=>1000);                                         

END;

/

SELECT snap_id, 

       TO_CHAR(begin_interval_time,'YYYYMMDD hh24:mi:ss')min_begin_interval,

  TO_CHAR(end_interval_time,'YYYYMMDD hh24:mi:ss') min_end_interval

  FROM DBA_HIST_SNAPSHOT 

 WHERE snap_id = (SELECT MIN(snap_id) FROM DBA_HIST_SNAPSHOT);

Or

 SELECT snap_id, 

       TO_CHAR(begin_interval_time,'YYYYMMDD hh24:mi:ss')min_begin_interval,

   TO_CHAR(end_interval_time,'YYYYMMDD hh24:mi:ss') min_end_interval

  FROM SYS.WRM$_SNAPSHOT 

 WHERE snap_id = (SELECT MIN(snap_id) FROM SYS.WRM$_SNAPSHOT );

   SNAP_ID MIN_BEGIN_INTERVAL MIN_END_INTERVAL

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

     35749 20160408 13:18:39  20160408 15:00:17

In this case all looks OK.

There are no old "zombie" snapshots.

It seems that the default size of 1Gb is just too small for this DB.

The solution would be to increase the tablespace of SYSAUX, to 2Gb.

 

========================
Solution
========================
Increase the tablespace of SYSAUX.

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_sysaux_01.dbf' RESIZE 2000M;

Database altered

SELECT TABLESPACE_NAME, 
    (MAX(MAX_SPACE)-MAX(USED_SPACE)  )AS DBA_FREE_SPACE_MB,
    MAX(USED_SPACE) AS USED_SPACE_MB, 
    MAX(MAX_SPACE) AS MAX_SPACE_MB, 
    ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100) as FREE_PCT, 
    CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100)<15)  THEN 'Y' ELSE 'N' END AS ADD_MORE_SPACE 
FROM ( 
SELECT tablespace_name,  
     ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE, 
     0 AS MAX_SPACE, 
     0 AS USED_SPACE   
 FROM DBA_FREE_SPACE
 WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM' OR tablespace_name = 'SYSAUX'  
 GROUP BY tablespace_name  
 UNION ALL 
 SELECT tablespace_name, 
     0 AS FREE_SPACE, 
     ROUND( SUM(CASE WHEN (bytes>maxbytes)  THEN bytes ELSE maxbytes END)/1024/1024) AS MAX_SPACE, 
     0 AS USED_SPACE 
 FROM DBA_DATA_FILES
 WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM' OR tablespace_name = 'SYSAUX' 
 GROUP BY tablespace_name 
 UNION ALL 
 SELECT tablespace_name, 
     0 AS FREE_SPACE, 
     0 AS MAX_SPACE, 
     ROUND(SUM(bytes/1024/1024)) AS USED_SPACE 
  FROM DBA_SEGMENTS
 WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM' OR tablespace_name = 'SYSAUX' 
GROUP BY tablespace_name 
) 
GROUP BY tablespace_name;

TABLESPACE_NAME DBA_FREE_SPACE_MB USED_SPACE_MB MAX_SPACE_MB   FREE_PCT ADD_MORE_SPACE
--------------- ----------------- ------------- ------------ ---------- --------------
DWH_INDEX                   13200         29568        42768         31 N
SYSAUX                   1074          926       2000        54 N
IGT_TABLE                    6349          7651        14000         45 N
SYSTEM                        637           363         1000         64 N
IGT_INDEX                    6604          1396         8000         83 N
DWH_TABLE                   19792         12976        32768         60 N

6 rows selected

Understanding bytes, max_bytes, and user_bytes in DBA_DATA_FILES by example

General:
Consider following scenario:
Tablespace has one datafile.
Datafile was defined as AUTOEXTENSIBLE=NO, with size 100M
Tablespace has reached in maximum size of 100M
Now, resize the datafile to 300Mb.

Steps
1. Initial situation
SELECT tablespace_name, 
       autoextensible, 
       bytes/1024/1024 AS BYTES_MB, 
       maxbytes/1024/1024 AS MAXBYTES_MB, 
       user_bytes/1024/1024 AS USER_MB
FROM DBA_DATA_FILES 
WHERE TABLESPACE_NAME = 'MY_TABLESPACE'

TABLESPACE_NAME      AUTOEXTENSIBLE   BYTES_MB MAXBYTES_MB    USER_MB
-------------------- -------------- ---------- ----------- ----------
MY_TABLESPACE        NO                    100           0    99.9375


SELECT (SUM(bytes))/1024/1024 AS bytes_MB
FROM USER_SEGMENTS
WHERE TABLESPACE_NAME = 'MY_TABLESPACE'

  BYTES_MB
----------
    98.875

2. Now add another 200M to the same datafile:

ALTER DATABASE DATAFILE '/oracle_db/db1/db_inst/MY_DATAFILE_01.dbf' RESIZE 300M;

3. Check new situation.
SELECT tablespace_name, 
       autoextensible, 
       bytes/1024/1024 AS BYTES_MB, 
       maxbytes/1024/1024 AS MAXBYTES_MB, 
       user_bytes/1024/1024 AS USER_MB
FROM DBA_DATA_FILES 
WHERE TABLESPACE_NAME = 'MY_TABLESPACE';

TABLESPACE_NAME       AUTOEXTENSIBLE   BYTES_MB MAXBYTES_MB    USER_MB
--------------------- -------------- ---------- ----------- ----------
MY_TABLESPACE         NO                    300           0   299.9375


SELECT bytes/1024/1024 AS FREE_MB
  FROM DBA_FREE_SPACE;

TABLESPACE_NAME                   FREE_MB
------------------------------ ----------
MY_TABLESPACE                     200.125

Conclusion.
The data in DBA_DATA_FILES is misleading.
A. MAXBYTES for non autoextensible files is always zero.
B. USER_BYTES represent the max available space for oracle, not the actual used space.
C. The BYTES represent the used space, from OS prospective, i.e. same values as in USER_BYTES plus some small overhead.
D. To get the actual used space, need to do
DBA_DATA_FILE.user_bytes - DBA_FREE_SPACE.bytes

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