Oracle DBA Interview Questions & Answers(Part-1)

1) How to set pga size, can you change it while the database is running?

show parameter pga_aggregate_target;

alter system set pga_aggregate_target=100m;

Yes the pga can be changed while the database is up and running.

2) What is Automatic segment space management? and how to find the tablespace in ASSM?

Oracle will allocates the extents automatically to the table or segment depending upon the size of the table.We need not to give the storage parameters.

SQL>desc dba_tablespaces;

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

3) How to know how much free memory available in sga?

select * from v$sgastat where name =’free memory';

4) What are oracle storage structures?

Oracle storage structures are tablespace,segment,extent,oracle block

5) List types of Oracle objects

table,index,cluster table,IOT (Index Organisation able),function,package,synonym,trigger,sequence

6) What is an index, how many types of indexes you know? Why you need an index

Index is an oracle object which is used to retrieve the data much faster rather than scanning entire table. Typically this is like an index page in a book which contains the links to the pages, where we can go through easily throughout the book.

If index page is not there, we have to search each and every page for our need, so we use indexes in oracle also to retrieve the data quickly.

Types of indexes:

Btree index: Used for searches mostly when used select statements(Ex:pincode)

bit map index: when having low cardinolity (low priority) columns used in thestatements.for example: gender column

function based index: sum(salary), upper(ename), lower(ename)

reverse index: used mostly to increase the speed of inserts (it's like btree only but the key is reverse).

7) What is synonym ?

Synonym is used to hide the complexity of the original object.

for example user ‘a’ has table ‘t’ which user ‘a’ wants to hide the name but user ‘b’ has to access it.

In this case user ‘a’ can create a synonym on table ‘t’ and give a select priviledge to user ‘b’.

SQL> create synonym aishu on t;

SQL> grant select on aishu to b;

View: DBA_SYNONYMS

Query: Select synonym_name from dba_synonyms;

8) What is sequence?

Sequence is a oracle object which used to create the unique and sequential numbering for a column

example: employee num,account id

create table employee(id number ,name varchar2(10),salary number);

create sequence aishu_seq start with 1 increment by 1;

insert into employee values (aishu_seq.nextval,’paddu’,120000);

9) Define different types of tablespaces you know?

Permanent: system table space,sys aux,user

undo: to store undo segments

temp: to store the sort segments

10) What is the difference between Locally managed tablespace and dictionary managed tablespace?

LMT: Locally Managed Table space stores all the extent mapping or allocation details in the header of the data file

DMT:Dictionary Managed Table space stores all the extent mapping or allocation details in the dictionary table called UET$ and FET$

Since everytime an allocation of extents generate some recursive sql on UET$ and FET$ this is contention in dictionary cache, hence this is not good for performance of database, but LMT can store this outside of dictionary , coz it stores in header of the data file.

By Default from 10g the management is LMT only