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,

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.

Oracle DBA FAQs

 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;


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

———————————— ———– ———
undo_tablespace string UNDOTBS3

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%';


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

SQL> show parameter block_size;

———————————— ———–
db_block_size integer 8192
VirtualNuggets 5264340066245425717

Post a Comment


Home item

Popular Posts


Random Posts

Flickr Photo