Database, Tablespace, Datafiles and Related Issues

Dear friend,
We are going to learn 4 things here:
1) Basics of Databases, Tablespaces and Datafiles.
2) Getting tablespace sizes for all the schema available in oracle database.
3) Space used by a table or index.
4) Extend the tablespace size in case your application has stopped inserting/updating data in database. 
   The issue is "ORA-01653: unable to extend table".
5) Create a duplicate table without copying data in Oracle

------------------------------------------
1) Basics: Databases, Tablespaces and Datafiles are closely related, but they have important differences.

Database:   An Oracle database consists of one or more logical storage units called tablespaces, 
            which collectively store all of the database's data.
            
Tablespace: Each Tablespace in Oracle database consists of one or more files called datafiles, which 
            are physical files in the operating system in which Oracle is running. Tablespace provides a layer of abstraction 
            between physical and logical data and serves to allocate storage for all DBMS managed segments/objects. Database 
            segment/object are those which occupies physical space, eg: Table data and Indexes. Hence tablespace only specify 
            database storage location. A common use of tablespaces is to optimize performance. For example, a heavily used index 
            can be placed on a fast SCSI disk. On the other hand, a database table which contains archived data that is rarely 
            accessed could be stored on a less expensive but slower SATA disk.
            
Datafiles:  A database's data is collectively stored in the datafiles that constitute each tablespace of the database. 
            This means one tablespace can have multiple datafiles. For example, the simplest Oracle database would have 
            one tablespace and one datafile. Another database can have three tablespaces, each consisting of two 
            datafiles, total of six datafiles.

2) Below query will reveal the "Used Space" and "Free Space" in all the Tablespaces present in your database:

SELECT DF.TABLESPACE_NAME "TABLESPACE",
      DF.BYTES / (1024 * 1024) "SIZE (MB)",
      SUM(FS.BYTES) / (1024 * 1024) "FREE (MB)",
      NVL(ROUND(SUM(FS.BYTES) * 100 / DF.BYTES),1) "% FREE",
      ROUND((DF.BYTES - SUM(FS.BYTES)) * 100 / DF.BYTES) "% USED"
 FROM DBA_FREE_SPACE FS,
      (SELECT TABLESPACE_NAME,SUM(BYTES) BYTES
         FROM DBA_DATA_FILES
        GROUP BY TABLESPACE_NAME) DF
WHERE FS.TABLESPACE_NAME (+)  = DF.TABLESPACE_NAME
GROUP BY DF.TABLESPACE_NAME,DF.BYTES;

3) To know the space used by a Table, Index or anything use below query:
SELECT SEGMENT_TYPE,BYTES/1024/1024 "SIZE IN MB" FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'INDEX_NAME_OF_TABLE';

SELECT SEGMENT_TYPE,BYTES/1024/1024 "SIZE IN MB" FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'TABLE_NAME';

4) If you face any of the below issues in production/uat environement:

java.sql.BatchUpdateException: ORA-01654: unable to extend index SCHEMA_NAME.INDEX_NAME by 1024 in tablespace SCHEMA_NAME
java.sql.SQLException: ORA-01653: unable to extend table SCHEMA_NAME.TABLE_NAME by 128 in tablespace SCHEMA_NAME

Which shows Tablespace size need to be extended. You need to add one tablespace file for existing SCHEMA_NAME whose space has become full.
So get the FILE_NAME first:

SELECT * FROM  DBA_DATA_FILES WHERE TABLESPACE_NAME='SCHEMA_NAME';  
OR 

SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SCHEMA_NAME';

FILE_NAME will be like as : 
/home/oracle/dbf/SCHEMA_NAME-01.DBF
/home/oracle/dbf/SCHEMA_NAME-02.DBF
Or in Windows:
C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SCHEMA_NAME.DBF

The above data shows there are already 2 dbf files, so add one more. New file: /home/oracle/dbf/SCHEMA_NAME-03.DBF
Open Oracle in command prompt:

For Windows:
SQL> alter tablespace SCHEMA_NAME add datafile 'C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\SCHEMA_NAME-03.DBF' size 1G autoextend on maxsize 2G;

For Unix:
SQL> alter tablespace SCHEMA_NAME add datafile '/home/oracle/dbf/SCHEMA_NAME-03.DBF' size 2G autoextend on maxsize 32G;
alter tablespace SCHEMA_NAME add datafile '/home/oracle/dbf/SCHEMA_NAME-03.DBF' size 2G autoextend on maxsize 32G
*
ERROR at line 1:
ORA-03206: maximum file size of (4194304) blocks in AUTOEXTEND clause is out of range

It means, that big size (2G) file can't be added in your system. There may be other reason too. Change the size to below (500M or 1G):
SQL> alter tablespace SCHEMA_NAME add datafile '/home/oracle/dbf/SCHEMA_NAME-03.DBF' size 500M autoextend on maxsize 10G;
Tablespace altered.

Run above query again with new DBF file name, 500M or 1G again will be added. Now run below query again, you will see new DBF files:
SELECT * FROM  DBA_DATA_FILES WHERE  TABLESPACE_NAME='SCHEMA_NAME';

5) Create a duplicate table without creating Data from original table:
CREATE TABLE DUPLICATE_TABLE as select * from ORIGINAL_TABLE where 1=0;
-----------------------------END-----------------------------