Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Oracle Tablespaces Auditing,Add,Delete,Rename,Drop,Views,Interview Questions & Answer

  • A database is divided  into One or more logical storage units called tablespace.
  •  collection of datafiles is called as tablespace.
TYPES OF TABLESPACE:
  • Permanent  Tablespaces
  • Undo Tablespaces
  • Temporary Tablespaces
  • PERMANENT TABLESPACE:
(Users data files and normally contains the system (data dictionary) and users data) 
  • System 
  • Is  a permanent tablespace and contains the vital data dictionary (metadata about the database).
  • It created when database is created.
  • It always in online.
  • It stores tables that support the core functionality of the database such as the data dictionary tables.
  • Cannot rename or drop the system tablespace
  • Sysaux 
  • Its introduce in oracle 10g.
  • Is an auxiliary tablespace to the system tablespace and contains performance statistics collected by the database.(ex:logminar,oracle streams)
  • It cannot be dropped or renamed.
  • Sysaux tablespace may be taken offline for performing tablespace recovery.
  • USER DEFINED TABLESPACES:
  • Is used to store organizational data in table accessed by application programs.
  • Flexibility in database administration.
  • Separate  data by backup reruitrmrnts.
  • Separate dynamic and static data to enable database tuning.
  • Control space allocation for both applications and system users.
  • Reduce contention for input/output path access.
Create tablespace:
Syntax:
     Create  tablespace   data_tbs  datafile  ‘/home/oracle/data/data_tbs.dbf’ size 200m;
Adding  datafile :
Syntax:
    Alter  tablespace  data_tbs  add  datafile  ‘/home/oracle/data/data02_tbs.dbf’ size 200m;
Renaming  tablespace:
Syntax:
  Alter  tablespace  data_tbs   rename  to  data_old_tbs;
Dropping tablespace:
Syntax:
  Drop tablespace  data_tbs;
 Resize the datafile :
Syntax:
 Alter database  datafile  ‘/home/oracle/data/data_tbs.dbf’  resize  size 300m;

Relocating  tablespace/datafile:
Syntax:
 Step 1 shut the database
Step 2 use copy command to move the phy file to new location
Step 3 mount the database
Step 4 excute this command
Alter tablespcae data_tbs rename datafile ‘/home/oracle/data/data_tbs.dbf’ to ‘/home/oracle/king/data_tbs.dbf’;

Quotas on tablespces:
Syntax:
Alter user schema01 quota 50m on data_tbs;
Read only tablespace:
Syntax:
Alter tablespace data_tbs read only;
Offline/online  tablespace:
Syntax:
Alter tablespace data_tbs offline;
Alter tablespace data_tbs online;
Assing tablespace to user:
Syntax:
Alter user schema01 default tablespace data_tbs;

Bigfile tablespace:
  • It is new feature of oracle 10g and it contain the single datafile and allocate large size up to 4g.
  • It reduce the disk I/O.
Syntax:
Create  bigfile tablespace  userdata datafile  ‘/home/oracle/data/userdata.dbf’ size  10g;
Resize the bigfile tablespace:
Syntax:
Alter tablespace userdata resize 20g;
Tablespace creation in ASM:
 syntax:
 create tablespace sainora datafile '+dg1' size 200m;
Adding datafile in ASM:
syntax:
alter tablespace sainora add datafile '+dg1' size 200m;
Droping tablespace in ASM:
syntax:
drop tablespace sainora;
Drop datafile in ASM:
syntax:
alter tablespace sainora drop datafile '+dg1/sainora.dbf ';
VIEWS:
DBA_DATA_FILES
V$DATAFILE
DBA_SEGMENTS
DBA_USERS
DBA_FREE_SPACE
DBA_TABLESPACES
V$TABLESPACE
ASSIGNMENT QUESTIONS:
1.create tablespace size with 2m and insert the values above 10,00,000.
2.add the datafile in tablespace.
3.create tablespace without giving the datafile location.
4.rename the tablespace name give syntax.
5.find the default location of tablespace.

INTERVIEW QUESTIONS:                                                                               
1.create the tablespace size with 1gb.
2.How to assign quota to more then one user for same tablespace.
3.How will u drop the system tablespace.
4.resize the sysaux tablespace give syntax.
5.find the tablespace size and status.
6.find the datafile location and size.
7.find how many datafile is there in a single tablespace.
8.how to check the used space of tablespace.
9.find the free space of tablespace.
10.use of bigfile tablespace.


Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

7 Steps to configure BDR replication in postgresql

Postgresql Database Tutorial android app