Tablespace Creation in Oracle

Tablespaces are Crazy in oracle 

2 Types are tablespaces based on the size:

a) Small file Tablespace 

b) Big file Tablespace 

3 Types of Tablespaces in Oracle 

a) Normal Tablespaces like SYSTEM, SYSAUX, USERS, EXAMPLE and ANY Other Custom Tablespace 

b) Undo Tablespaces

c) Tamp Tablespaces

Small File Tablespaces:

CREATE TABLESPACE TBS1

DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/tbs1.dbf'

SIZE 10M AUTOEXTEND ON;

CREATE UNDO TABLESPACE UNDOTBS11

DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/undotbs11.dbf'

SIZE 10M AUTOEXTEND ON;

CREATE TEMPORARY TABLESPACE TEMPTBS1

TEMPFILE '/u01/app/oracle/oradata/DEVDB/datafile/temptbs1.dbf' 

SIZE 10M AUTOEXTEND ON;

select TABLESPACE_NAME,BIGFILE from dba_tablespaces;

BIG File Tablespaces:

CREATE BIGFILE TABLESPACE BIGTBS1

DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/bigtbs1.dbf'

SIZE 10M AUTOEXTEND ON;

CREATE BIGFILE UNDO TABLESPACE BIGUNDOTBS11

DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/bigfileundotbs1.dbf'

SIZE 10M AUTOEXTEND ON;

CREATE BIGFILE TEMPORARY TABLESPACE BIGTEMPTBS1

TEMPFILE '/u01/app/oracle/oradata/DEVDB/datafile/bigfiletemptbs1.dbf' 

SIZE 10M AUTOEXTEND ON;

select TABLESPACE_NAME,BIGFILE from dba_tablespaces;

SQL> CREATE BIGFILE TABLESPACE TBS1

DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/tbs1.dbf'

SIZE 10M AUTOEXTEND ON;

  2    3

Tablespace created.

SQL> 

SQL> CREATE UNDO TABLESPACE UNDOTBS11

DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/undotbs11.dbf'

SIZE 10M AUTOEXTEND ON;

  2    3

Tablespace created.

SQL> CREATE TEMPORARY TABLESPACE TEMPTBS1

TEMPFILE '/u01/app/oracle/oradata/DEVDB/datafile/temptbs1.dbf'

SIZE 10M AUTOEXTEND ON;

  2    3

Tablespace created.

SQL> CREATE BIGFILE TABLESPACE BIGTBS1

DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/bigtbs1.dbf'

SIZE 10M AUTOEXTEND ON;

  2    3

Tablespace created.

SQL> CREATE BIGFILE UNDO TABLESPACE BIGUNDOTBS11

DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/bigfileundotbs1.dbf'

SIZE 10M AUTOEXTEND ON;

  2    3

Tablespace created.

SQL> CREATE BIGFILE TEMPORARY TABLESPACE BIGTEMPTBS1

TEMPFILE '/u01/app/oracle/oradata/DEVDB/datafile/bigfiletemptbs1.dbf'

SIZE 10M AUTOEXTEND ON;

  2    3

Tablespace created.

SQL>

SQL> select TABLESPACE_NAME,BIGFILE from dba_tablespaces;

TABLESPACE_NAME                BIG

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

SYSTEM                         NO

SYSAUX                         NO

UNDOTBS1                       NO

TEMP                           NO

USERS                          NO

TBS1                           YES

UNDOTBS11                      NO

TEMPTBS1                       NO

BIGTBS1                        YES

BIGUNDOTBS11                   YES

BIGTEMPTBS1                    YES

11 rows selected.

SQL>

SQL> select NAME,BIGFILE from v$tablespace;

NAME                           BIG

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

SYSAUX                         NO

SYSTEM                         NO

UNDOTBS1                       NO

USERS                          NO

TEMP                           NO

TBS1                           YES

UNDOTBS11                      NO

TEMPTBS1                       NO

BIGTBS1                        YES

BIGUNDOTBS11                   YES

BIGTEMPTBS1                    YES

11 rows selected.

SQL>

Regards,

Mallik