TABLESPACE MANAGEMENT

Tablespace:

-----------

  --> it has one or more datafiles 

 types:

 ------

   --> temporary tablespaces

   --> undo tablespaces 

   --> actual tablespaces

    

         --> small file tablespace

         --> big file  tablespace

--> allocating extent size

     -- autoallocate

     -- manual

--> extents size(segments)

  

     -- 64kb for samll segments

     -- 1mb for medium segments

     -- 64mb for large segments

--> extents size start from 64kb to 64mb

--> The default tablespace extent management is the autoallocate option.

--> segment space management

     

     -- automatic 

     -- manual

--> extent allocation and deallocation

--> storage parameters

    

       -- initial extent(initial)

       -- next extents(next)

       -- extent management

       -- allocate type

       -- segment space management

       

Examples:

---------

  --> creating tablespace  

        

      SQL> CREATE SMALLFILE TABLESPACE STUDENT   DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\student01.dbf' SIZE 100M  REUSE AUTOEXTEND ON NEXT 64K MAXSIZE 200M LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K SEGMENT SPACE MANAGEMENT AUTO; 

      

  

      SQL> CREATE SMALLFILE TABLESPACE SALES

    DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SALES01.DBF' SIZE 100M REUSE AUTOEXTEND ON NEXT 64K              MAXSIZE 100M LOGGING EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE ( INITIAL 64K NEXT 64K PCTINCREASE 20)      MINIMUM EXTENT 64K;

      SQL> CREATE BIGFILE TABLESPACE EXAMS

    DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\exams.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 64K 

    MAXSIZE 200M LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K SEGMENT SPACE MANAGEMENT AUTO;

      SQL> ALTER DATABASE DEFAULT TABLESPACE STUDENT;

      SQL> ALTER TABLESPACE STUDENT READ ONLY;

      SQL> ALTER TABLESPACE STUDENT READ WRITE;

    

      SQL> ALTER TABLESPACE STUDENT OFFLINE NORMAL/TEMPORARY/IMMEDIATE/RECOVER;

      SQL> ALTER TABLESPACE STUDENT NOLOGGING/LOGGING;

      

  --> creating temporary tablespace

                     

      SQL> CREATE SMALLFILE TEMPORARY TABLESPACE ORDER

    DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ORDERS01.DBF' SIZE 100M REUSE AUTOEXTEND ON NEXT 64K              MAXSIZE 200M LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K SEGMENT SPACE MANAGEMENT AUTO; 

        

      SQL> CREATE BIGFILE TEMPORARY TABLESPACE ORDER 

    DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ORDERS01.DBF' SIZE 100M REUSE AUTOEXTEND ON NEXT 64K              MAXSIZE 200M LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K SEGMENT SPACE MANAGEMENT AUTO;       

      SQL> CREATE SMALLFILE TABLESPACE ORDER

    DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ORDERS01.DBF' SIZE 100M REUSE AUTOEXTEND ON NEXT 64K              MAXSIZE 200M LOGGING EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE ( INITIAL 64K NEXT 64K MAXEXTENTS      UNLIMITED PCTINCREASE 20) MINIMUM EXTENT 64K TEMPORARY;

      SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE ORDER

  --> creating undo tablespace

      SQL> CREATE SMALLFILE UNDO TABLESPACE UNDO 

    DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\undo01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 64K              MAXSIZE 300M RETENTION NOGUARANTEE;

      SQL> CREATE SMALLFILE UNDO TABLESPACE UNDO 

    DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\undo01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 64K               MAXSIZE 300M RETENTION GUARANTEE;

      SQL> CREATE BIGFILE UNDO TABLESPACE UNDO

    DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\undo01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 64K               MAXSIZE 300M RETENTION NOGUARANTEE;

      SQL> CREATE BIGFILE UNDO TABLESPACE UNDO 

    DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\undo01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 64K               MAXSIZE 300M RETENTION GUARANTEE;

      SQL> ALTER TABLESPACE UNDO OFFLINE NORMAL/TEMPORARY/IMMEDIATE/RECOVERY;

     

      SQL> ALTER TABLESPACE UNDO ONLINE;

      SQL> ALTER TABLESPACE UNDO RETENTION NOGUARANTEE/GUATANTEE;

  --> addign datafiles to tablespace

      SQL> ALTER TABLESPACE USERS ADD DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\sales02.dbf' 

    SIZE 100M REUSE AUTOEXTEND ON NEXT 64K MAXSIZE 300M

   

  --> adding space to tablespace 

      

      SQL> ALTER DATABASE DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF' RESIZE 600M

      SQL> ALTER DATABASE DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF' OFFLINE/ONLINE

      SQL> ALTER DATABASE DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF' 

    AUTOEXTEND ON MAXSIZE UNLIMITED

      SQL> ALTER DATABASE DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF' AUTOEXTEND OFF

      SQL> ALTER DATABASE DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF' AUTOEXTEND ON NEXT 128K