«Back to Home

Oracle Jump Start
Topics

How To Use Tablespace In OraclePLSQL

Description

Tablespace stores schema objects like tables, indexes, view etc. logically into the tablespace and physically in the datafiles related to the corresponding tablespace.

Datafiles
  • Physical files
  • dbf extension
  • Store schema objects
  • Written by database writer processes
  • Table space are logical entity
Types of Tablespace

1


Example
 
In this example, you will see, how to create and drop tablespace in Oracle.
  1. Select name from v$tablespace;  
  2. Select name from v$datafile;  
2
 
3
  1. create tablespace mytbs datafile 'c:\app\mcntr004\oradata\pnt\datafile\mytbs1.dbf' size 100m;  
4

  1. Select ts#, name from v$datafile;  
5

Using Alter table in table space

Example
  1. alter tablespace mytbs add datafile 'c:\app\mcntr004\oradata\pnt\datafile\mytbs2.dbf' size 100m;  
  2. select ts#, name from v$datafile;  
6
  1. desc dba_tablespaces;  
7
  1. select tablespace_name, status from dba_tablespaces;  
8
  1. alter database datafile 'c:\app\mcntr004\oradata\pnt\datafile\mytbs1.dbf' resize 200m;  
  2. desc v$datafile;  
9
  1. alter tablespace mytbs read only;  
  2. alter tablespace mytbs read write;  
  3. alter tablespace mytbs force logging;  
  4. alter tablespace mytbs nologging;  
10
  1. alter tablespace yourtbs rename to mytbs;  
  2. select name from v$tablespace;  
11

Drop Tablespace
  1. drop tablespace mytbs including contents and datafiles;  
  2. select name from v$tablespace;  
12


Summary

Thus, we learnt, tablespace stores the schema objects like tables, indexes, view etc. logically into the tablespace and physically in the datafiles related to the corresponding tablespace. We learnt, how to use tablespace in Oracle with the examples.