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
Post a Comment