MANAGING TABLESPACE AND DATAFILES IN ORACLE

MANAGING TABLESPACE AND DATAFILES IN ORACLE

Introduction: 

Tablespace :In this blog i am going to explain MANAGING TABLESPACE AND DATAFILES IN ORACLE,Tablespace is one or more logical storage units in oracle database  which collectively store all the database data and each tablespace has one or more datafiles, datafiles are physical structure tablespace is not visbile in the filesytem of the machine which datbase resides,The tablespace builds the bridge between the oracle database and the filesystem in which the tables or index's data stored ,The data in oracle database are stored in tablespaces, An oracledatabase can be logically grouped into smaller logical areas of space known as tablespaces,each tablespace consits of one or more operating system files,which are called datafiles

1)SYSTEM TABLESPACE: SYSTEM tablespace is used to store the sytem related data which includes tables,indexes,sequences,and other objects,Contains the data dictionary,including stored program units Contains the SYSTEM undo segment,should not contain user data,SYSTEM tablespace always online when database is open ,oracle database have a SYSTEM tablespace when database created SYSTEM is the first tablespace


2)SYSAUX TABLESPACE : SYSAUX TABLESPACE stores many databse components,always online when database is open,these SYSAUX table created when oracle database installed,if u SYSAUX table gets filled 100% none of the users will not be able to login 


3)UNDO TABLESPACE :  UNDO TABLESPACE is a kind of permanent tablespace used by oracele to manage undo data if your running your database in automatic undo management mode this undo data or undo records are generally used to roll back transations  Recover from logical corruptions using flashback feature 


4)USERS TABLESPACE: USERS TABLESPACE is used to store the user objects and data in permanently every database have a tablespace for permanent user data is assigned to users otherwise the objects will be stored SYSTEM tablespace  


5)TEMP TABLESPACE:  TEMP TABLESPACE is stores the temporary data  that only exists during the database session,oracle uses temporary tablesapce to improve the concurrency of multiple sort operations, temp tablesapce shared by multiple users


DATABASE STORAGE HIERARCHY  HOW DATA IS STORED IN LOGICAL AND PHYSICAL
--------------------------------------------------------------------------------------------------------------------------

  DATABASE
     |
     |
     v
  TABLESPACE  ---------> DATAFILE-----> PHYSICAL------> SAN(STORAGE AREA NETWORK)
     |                                    | 
     |                                    |-----------> NAS (NETWORK ATTACHED STORAGE)
     v
  SEGMENT---------->LOGICAL
     |
     |
     v
  EXTENT----------->LOGICAL
     |
     |
     v
 ORACLE DATA BLOCK------------> LOGICAL OS BLOCK



TABLESPACE------>LOGICAL STORAGE UNIT
SEGMENTS-------->SPACE ALLOCATED FOR A TABLES INFORM OF EXTENTS
EXTENTS -------->CONTIGUOS ORACLE BLOCK 



Dictionary Tables

dba_tablespaces
dba_data_files
dba_segments
dba_extents
dba_free_space


List the tablespaces

sql>desc dba_tablespaces

sql>select tablespace_name from dba_tablespaces;

sql>select tablespace_name,extent_management,allocation_type,segment_space_management from dba_tablespaces;

sql>desc dba_tablespaces

Finding tablespace for sys owned.

Sys stores objects in system tablespace.

SQL> select username,default_tablespace from dba_users where username like 'SYS';


Listing datafiles of a tablespace:

sql>select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'CHAITBS';

Creating Tablespace:

sql>create tablespace CHAITBS datafile '/oradata/prod/chaitu01.dbf' size 100m;

Adding datafile:

sql>alter tablespace CHAITBS add datafile '/oradata/prod/chaitu02.dbf' size 100m autoextend on maxsize unlimited;

Note: 32G is the max file size on unix level

Finding size of a datafile:

sql>select file_name,bytes/1024/1024,autoextensible,maxbytes/1024/1024 from dba_data_files where tablespace_name like 'CHAITBS';


Resize Datafile:

sql>alter database datafile '/oradata/prod/chaitu01.dbf' resize 200m;

Enable Autoextend on/off:

sql>alter database datafile '/oradata/prod/chaitu01.dbf' autoextend on maxsize 8192m;

Unlimited:

sql>alter database datafile '/oradata/prod/chaitu01.dbf' autoextend on maxsize unlimited;

In oracle, the maxsize of the datafile on unix level is max 32G.


Dropping tablespace :

sql>drop tablespace CHAITBS including contents and datafiles;


Create tablespace
add datafile
resize
autoextend on
drop tablespaces
finding free space
find datafile location/size/max/autoextnd
list of tablespaces /managed type/segment space management

Dictionary tables/views

    dba_tablespaces
    dba_data_files
    dba_free_space
    dba_segments
    dba_extents


Find the usage

sql>select file_name,bytes/1024/1024,autoextensible,maxbytes/1024/1024 from dba_data_files where tablespace_name like 'SYSTEM';


Finding free space.of a particular /specific tablespace

sql>select tablespace_name,sum(bytes/1024/1024) from dba_free_space where tablespace_name like 'SYSTEM' group by tablespace_name;

For all tablespaces - using group by

sql>select tablespace_name,sum(bytes/1024/1024) from dba_free_space group by tablespace_name;


--------------------90%(7GB)-------limi(8G)----------------------max(32G)
New --------------------------------------------------------------max(32G)
existing limit ------------(8G)

Assume - Limit - 8GB
        threshold reached 90% (7GB)
            but Max size 32GB
Now action:
        Add new datafile with unlimited
        then limit existing datafile to 8GB


sql>alter tablespace CHAITBS add datafile '/oradata/prod/chaitu02.dbf' size 100m autoextend on maxsize unlimited;

Now limit existing

sql>alter database datafile '/oradata/prod/chaitu01.dbf' autoextend on maxsize 8192m;

Temp Tablespace (non system Tablespace)

    purpose : for sorting,analyze,index rebuild,group by

    default - temp(one)

dba_temp_files
v$temp_space_header

we can create many
sql>create temporary tablespace chaitempfile '/oradata/prod/chaitutemp01.dbf' size 100m autoextend on maxsize unlimited;

Adding temp file:

sql>alter tablespace cctemp add tempfile '/oradata/prod/cctemp02.dbf' size 100m autoextend on maxsize unlimited;

sql>select file_name,bytes/1024/1024,autoextensible,maxbytes/1024/1024 from dba_temp_files where tablespace_name like 'CCTEMP';

Finding free usage:

sql>desc v$temp_space_header

SQL> select tablespace_name,sum(bytes_used/1024/1024),sum(bytes_free/1024/1024) from v$temp_space_header group by tablespace_name;

Error:

unable to extend an extent in temp segment of temporary tablespace
find the usage and add tempfile

Datafile monitoring,check freespace,add , limit,autoextend.

V$sort_usage - inactive - kill - support - approval


Dictionary Tables :

dba_tablespaces
dba_data_files
dba_free_space
dba_temp_files
v$temp_space_header


User management


Create user Chaitanya identified by chaitu123 default tablespace chaitbs temporary tablespace cctemp;

application users ---> application - -->chaitanya- - ->db

schema- - ->collection of objects ---> acces to db users


file limit - 8G
--------------------7G---->8G(Max)-----------setto(32G-YES)
  90% reached - alert received action

add datafile New - -32G
limit existing to 8G - autoextend on maxsize 8G.


resize - if disable autoextend on ,then limit by resize - physically allocate
maxsize- only allocate on growth


Managing Tablespaces and data files locally managed tablespace


sql>create  tablespace tbs1 datafile '/oradata/prod/data01.dbf'
    size=50m
    extent management local autoallocate;

Alternative to Autoallocate is uniform

sql>create tablespace tbs2 datafile' '/oradata/prod/data01.dbf'
   size=50m
  extent management local uniform size 256k;

Dictionary managed tablespace


sql>create tablespace tbs1 datafile '/oradata/prod/data01.dbf'
    size 50m
   extent management dictionary;


Big File Tablespace


sql>create bigfile tablespace tbsbf1 datafile '/oradata/prod/databf01.dbf'
    size 50g;

Coalesce statement

sql>alter tablespace tbs1 coalesce;


Viewing information about tablespace and datafiles

sql>select * from dba_tablespaces;
sql>select * from v$tablespace;


To view information about datafiles


sql>select * from dba_data_files;
sql>select * from v$datafiles;

To view information about tempfiles


sql>select * from dba_temp_files;
sql>select * from v$tempfile;


To view information about freespace in tempfiles

sql>select * from v$temp_space_header;


To view information about free space in datafiles

sql>select * from dba_free_space;


To view the value and type of the blocksize

sql>show parameter db_block_size;

Note : Info on MANAGING TABLESPACE AND DATAFILES IN ORACLE it may be differ in your environment like production,testing,development,etc




THANKS FOR VIEWING MY BLOG FOR MORE UPDATES SUBSCRIBE ME AND FOLLOW ME