Tablespace Managment

Database : Collection of related information is stored in particular location is called database.

Database can be defined in 2 ways

                                    1. Physical Database

                                    2. Logical Database

1.  Physical STORAGE STRUCTURES:
Collection of all datafiles, online Redolog files, control files, and initialization parameter files.

2. LOGICAL STORAGE STRUCTURES:

The Following components are available in the logical structure.

                                                i.            Tablespaces 

                                              ii.            Database users

                                            iii.            Database objects

                                            iv.            Segments

                                              v.            Extents

                                            vi.            Data Blocks

Tablepsaces:

A database administrator can use the Tablespaces to do the following:
          * To control the user access by making it read only or read write.
          * Control database size by adding/dropping Tablespaces.
          * Assign specific space quota for database users.
          * Control availability of data by taking individual Tablespaces online or offline.
          * Perform online database backup or re.covery operations.
          * Allocate data storage across devices to improve performance.

Advantages of having different Tablespaces

           * Separation of user data from data dictionary data (which is in System Tablespace)
           * Separation of one application data from another.
           * Store different Tablespacesdatafiles on separate disk drives to reduce I/O contention
           * Take individual Tablespacesoffine while others remain online.
           * Backup individual Tablespaces leaving the rest to for user access.

Database Users:

To access any database we must require database users.thease users also called database accounts or schemas for example sys and system these are system defined database users.

Database Objects:

Anything is created In the database user is called a object.Every database user can create tables,views etc.

Segments: Collection of extents are called a Segment.

Extent: Collection of continues Data Blocks are called a Extent.

Data Block:

Smallest part of a tablespace is called a Data Block 8KB is the default Block size for Oracle 10g&11g

The following Data Blocks are supported by Oracle [ 2k,4k,8k,16k &32k(1kb=1.024 bytes)]

Datafile: Physical storage area of the database is called a datafile.

What is the relation between Tablespace & Datafile:

            Every tablespace should contain atleast one datafile.But datafile should not belongs to two tablespaces.

 Every database must contain the following Tablespaces:

1.System Tablespace

2.Sysaux Tablespace

3.User Tablespace

4.Undo Tablespace

5.Temporary Tablespace

6.Index Tablespace

By default all tablespaces are available in online mode with read and write permissions.

Any tablespace can be offline except system tablespace, Any tablespace can be dropped except default tablespace. A tablespace can be offline or read-only. Whenever a tablespace size is full we can add another datafile to the existing tablespace. Oracle also provides to resizing the existing datafile. We can reuse unused datafile.

1.User Tablespace :-            This tablespace  contain actual data or permanent data.

2.Temporary Tablespace :- This tablespace contains temporary data. ex:Sorting information

3.Undo Tablespace :-            Undo tablespace contain uncommitted data.

4.Index Tablespace :-            Index tablespace contain index tables.

Multiblock Environment :

In oracle 8i all tablespaces should have 2k block size or 4k blocksize or 8k block size etc.But from  oracle 9i onwords every tablespace can have individual block size.This feature provides maximum utilization of diskspace.

Creation of big tablespace: This feature is available from oracle 10g onwords.By using this feature we can create a tablespace upto 8 terabyte size without this feature normal tablespace can be created with size of maximum 2 terabytes.

Demo’s on Tablespace & Datafiles:

The following synatax is used to create a tablespace:

SQL>create tablespace users1 datafile ‘/u01/oradata/orcl/users1.dbf’ size 10m;

How to add a new data file in the existing tablespace:

SQL>alter tablespace users1 add datafile ‘/u01/oradata/orcl/user1_2.dbf’ size 10m;

How to resize a datafile:

A Datafile can be resized by using following statement.

SQL>alter database datafile ‘/u01/oradata/orcl/user1_2.dbf’ resize 100m;

How to drop a tablespace:

A tablespace can be dropped by using the following command.

SQL>drop tablespace users1;

Note: This command removes the tablespace name from the Data Dictionary,but not deletes the datafile from the HardDisk.

How to reuse unused datafiles:

SQL>create tablespace users1 datafile ‘/u01/oradata/orcl/users1.dbf’ reuse;

SQL>alter tablespace users1 datafile ‘/u01/oradata/orcl/users1_2.dbf’ reuse;

SQL>select * from v$tablespace;

SQL>select name from v$datafile;

How to drop a tablespsace without contents:

SQL>drop tablespace users1;

sys>host

$cd /u01/oradata/orcl

$ls –l

How to drop a tablespace including contents and datafiles:

SQL>drop tablespace users1 including contents and datafiles;

SQL>select * from v$tablespace;

SQL>select name from v$datafile;

This command drop tablespace name and delete all datafiles of the tablespace from OS level.

How to set the default tablespace:

SQL>alter database default tablespace users1;

Database altered.

SQL>set wrap off;

SQL>select * from database_properties;

How to rename the tablespace :

SQL>alter tablespace user_PROD1 rename to demo_PROD;

SQL>select * from v$tablespace;

How to create a bigfile tablespace :

SQL>create bigfile tablespace user_big_ts

                  datafile ‘/u01/PROD/userbigPROD.dbf’ size 100m;

How to create a tablespace with auto extent on :

SQL> create tablespace user_auto_PROD datafile '/u01/PROD/userautoPROD.dbf' size 100m autoextend on next 50m maxsize 1000m;

SQL> alter database datafile '/u01/PROD/userautoPROD.dbf' autoextend on next 100m maxsize 2000m;

Auto extend off:

SQL>alter database datafile ‘/u01/PROD/userautoPROD.dbf’ autoextend off;