TABLESPACE TYPE

 TABLESPACE TYPE:-

1) PERMANENT TABLESPACE :- Used to store Object data. 

A) SMALLFILE TABLESPACE
  B) BIGFILE TABLESPACE  ---> FROM 10G

2) UNDO TABLESPACE  :- Used to Support ROLLBACK, READ CONSISTENCY, FLASHBACK

3) TEMPORARY TABLESPACE .


1) PERMANENT TABLESPACE --

A)SMALLFILE TABLESPACE

CREATE TABLESPACE ACC DATAFILE '/u01/app/oracle/oradata/orcl/acc01.dbf' size 1m;

Tablespace created.

 SELECT TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 ORG_SIZE_MB,MAXBYTES/1024/1024 MAX_SIZE_MB FROM DBA_DATA_FILES;

TABLESPACE_NA FILE_NAME                                     ORG_SIZE_MB MAX_SIZE_MB                                   
------------- --------------------------------------------- ----------- -----------                                   
USERS         /u01/app/oracle/oradata/orcl/users01.dbf                5  32767.9844                                   
UNDOTBS1      /u01/app/oracle/oradata/orcl/undotbs01.dbf             60  32767.9844                                   
SYSAUX        /u01/app/oracle/oradata/orcl/sysaux01.dbf             510  32767.9844                                   
SYSTEM        /u01/app/oracle/oradata/orcl/system01.dbf             680  32767.9844                                   
EXAMPLE       /u01/app/oracle/oradata/orcl/example01.dbf            100  32767.9844                                   
ACC           /u01/app/oracle/oradata/orcl/acc01.dbf                  1           0 

B) * BIGFILE TABLESPACE ---> 10G  

SQL> CREATE BIGFILE TABLESPACE DUC DATAFILE '/u01/app/oracle/oradata/orcl/duc01.dbf' size 1m AUTOEXTEND ON MAXSIZE UNLIMITED;


Tablespace created.

SQL> SELECT TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 ORG_SIZE_MB,MAXBYTES/1024/1024 MAX_SIZE_MB FROM DBA_DATA_FILES;

TABLESPACE_NA FILE_NAME                                     ORG_SIZE_MB MAX_SIZE_MB                                   
------------- --------------------------------------------- ----------- -----------                                   
USERS         /u01/app/oracle/oradata/orcl/users01.dbf                5  32767.9844                                   
UNDOTBS1      /u01/app/oracle/oradata/orcl/undotbs01.dbf             60  32767.9844                                   
SYSAUX        /u01/app/oracle/oradata/orcl/sysaux01.dbf             510  32767.9844                                   
SYSTEM        /u01/app/oracle/oradata/orcl/system01.dbf             680  32767.9844                                   
EXAMPLE       /u01/app/oracle/oradata/orcl/example01.dbf            100  32767.9844                                   
ACC           /u01/app/oracle/oradata/orcl/acc01.dbf                 20        1024                                   
ACC           /u01/app/oracle/oradata/orcl/acc02.dbf                  1        1024                                   
DUC           /u01/app/oracle/oradata/orcl/duc01.dbf                  1    33554432                                   

8 rows selected.

SQL> SELECT 33554432/1024 FROM DUAL;

33554432/1024                                                                                                         
-------------                                                                                                         
        32768                                                                                                         

SQL> SELECT 32768/1024 FROM DUAL;

32768/1024                                                                                                           
----------                                                                                                           
        32

 2) UNDO TABLESPACE :- STORE UNDO DATA ( OLD IMAGE OF TRANSACTION )
     ROLLBACK
   READ CONSISTENCY
   FLASHBACK


 SHOW PARAMETER UNDO

NAME                                 TYPE        VALUE                                                               
------------------------------------ ----------- ------------------------------                                       
undo_management                      string      AUTO                                                                 
undo_retention                       integer     900                                                                 
undo_tablespace                      string      UNDOTBS1                                                             


SQL> CREATE UNDO TABLESPACE UNDO2 DATAFILE '/u01/app/oracle/oradata/orcl/undo02.dbf' size 10m;

Tablespace created.

If you want change undo file location , use this Parameter

SQL> ALTER SYSTEM SET UNDO_TABLESPACE='UNDO2';

System altered.

SQL> SHOW PARAMETER UNDO

NAME                                 TYPE        VALUE                                                               
------------------------------------ ----------- ------------------------------                                       
undo_management                      string      AUTO                                                                 
undo_retention                       integer     900                                                                 
undo_tablespace                      string      UNDO2                                                               


3)TEMPORARY TABLESPACE :- ORDER BY
DISTINCT
CREATE INDEX ...
  GROUP BY
  etc

SQL> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/u01/app/oracle/oradata/orcl/temp02.dbf' size 10m;

Tablespace created.

If you want change TEMP file location , use this Parameter


SQL> DESC DATABASE_PROPERTIES


 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 PROPERTY_NAME                                                     NOT NULL VARCHAR2(30)
 PROPERTY_VALUE                                                             VARCHAR2(4000)
 DESCRIPTION                                                                VARCHAR2(4000)

SQL> COL PROPERTY_VALUE FOR A30

SQL> SELECT PROPERTY_NAME,PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DEFAULT%';

PROPERTY_NAME                  PROPERTY_VALUE                                                                         
------------------------------ ------------------------------                                                         
DEFAULT_TEMP_TABLESPACE        TEMP                                                                                     
DEFAULT_PERMANENT_TABLESPACE   USERS                                                                                 
DEFAULT_EDITION                ORA$BASE                                                                               
DEFAULT_TBS_TYPE               SMALLFILE                                                                             

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

Database altered.

SQL> SELECT PROPERTY_NAME,PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DEFAULT%';

PROPERTY_NAME                  PROPERTY_VALUE                                                                         
------------------------------ ------------------------------                                                         
DEFAULT_TEMP_TABLESPACE        TEMP2                                                                                 
DEFAULT_PERMANENT_TABLESPACE   USERS                                                                                 
DEFAULT_EDITION                ORA$BASE                                                                               
DEFAULT_TBS_TYPE               SMALLFILE                                                                             

 DEFAULT_PERMANENT_TABLESPACE  ---> USERS */ 

SQL> CREATE USER KRISH IDENTIFIED BY KRISH;

User created.

SQL> GRANT CREATE SESSION,CREATE TABLE TO KRISH;

Grant succeeded.

SQL> CONN KRISH/KRISH
Connected.
SQL>
SQL> CREATE TABLE T1(ID INT);

Table created.


If you want change DEFAULT PERMANENT TABLESPACE  , use this Parameter 

SQL> SELECT TABLESPACE_NAME FROM USER_TABLES WHERE TABLE_NAME='T1';

TABLESPACE_NAME
------------------------------
USERS


SQL> INSERT INTO T1 VALUES(1)

1 ROW CREATED

 ALTER DATABASE DEFAULT TABLESPACE EXAMPLE;


SQL> SELECT PROPERTY_NAME,PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DEFAULT%';

PROPERTY_NAME        PROPERTY_VALUE
------------------------------ ------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP
DEFAULT_PERMANENT_TABLESPACE   EXAMPLE
DEFAULT_EDITION        ORA$BASE
DEFAULT_TBS_TYPE        SMALLFILE


SQL> SELECT TABLESPACE_NAME FROM USER_TABLES WHERE TABLE_NAME='T2';

TABLESPACE_NAME
------------------------------
EXAMPLE