Oracle DBA Interview Questions (Part-2)

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

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

12) What are oracle storage structures? 

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

13) List types of Oracle objects 

table, index, cluster table, IOT (Index Organisation Table), function, package, synonym,

trigger,sequence

14) 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 through out 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 retrive the data quickly.

 15) 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 king on t;

SQL> grant select on king to b;

View: DBA_SYNONYMS

Query: Select synonym_name from dba_synonyms;

16) 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 king_seq start with 1 increment by 1;

insert into employee values (king_seq.nextval,’test’,120000);

17) 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

18) 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

19) 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;

20) What is uniform segment? How to find it?

In uniform segment every extent will have same size.

SQL>desc dba_tablespaces;

SQL>select tablespace_name,

allocation_type,segment_space_management,extent_management from dba_tablespaces;

21) Where do you see the tablespace information?

SQL>select * from dba_tablespaces;

22) How to find the datafiles that associated with particular tablespace?

 Ex: System

SQL> desc dba_data_files

SQL> select * from dba_data_files where tablespace_name=’SYSTEM';

23) How to see which undo tablespace is used for database?

SQL> show parameter undo_tablespace

NAME TYPE VALUE

———————————— ———– ———

undo_tablespace string UNDOTBS3

SQL>

24) How to see the default temporary tablespace for a database?

SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME like ‘%TEMP%';

PROPERTY_NAME

——————————

PROPERTY_VALUE

——————————————————

DEFAULT_TEMP_TABLESPACE

TEMP2

25) How to see what is the default block size for a database ?

SQL> show parameter block_size;

NAME TYPE VALUE

———————————— ———–

db_block_size integer 8192