ORACLE DBA QUESTIONS AND ANSWERS                                     

Oracle DBA Interview Question & Answers

Oracle DBA Interview Questions:-

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.

SQL> desc v$parameter

SQL> select distinct ISSYS_MODIFIABLE from v$parameter;

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 Table),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 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.

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 the statements.for example: gender column
function based index: sum(salary), upper(ename), lower(ename)
reverse index: used mostly to increase the speed of inserts (its 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?
0Sequence 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

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

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

13) Where do  you see the tablespace information?
SQL>select * from dba_tablespaces;

14) 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';

15) How to see which undo tablespace is used for database?
SQL> show parameter undo_tablespace

NAME                                 TYPE        VALUE
———————————— ———– ——————————
undo_tablespace                      string      UNDOTBS3
SQL>

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

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

18) Is it possible to have multiple blocksizes in a database if so how? Explain

Yes it is possible but we have to create multiple DB buffer pools while setting the required block size parameter.To do that it require database bounce.
For example if you want to have 2k size along with default 8k
SQL> show parameter db_2k_cache_size
SQL>alter system set db_2k_cache_size=100M scope=spfile;
SQL>shut immediate

Now we can create the table space using new 2k block size
example:SQL>
create tablespace test_2k datafile ‘/u01/oradata/paddu/test2k.dbf’ size 100M block size 2k;

19) What is the oracle block, can you explain?
Oracle block is the lowest level of storage structure where it contains the data(business/oracle data)
The block has divided into many sections starting from block header,row header,row directory,ITL list(Intrested Transaction List),free space.

20) Can you change the blocksize once the database is created?
Obsolutely no becoz once the datafiles is formatted into 8k we cannot change the database block sizes , if you need, you have to create fresh database with new block size and restore from backup or import.

21) Can you change the database name once the database is created?
Yes we can but the database has to be shutdown and also this will change all of the headers of the files
Option 1
1) Using NID utility
a)SQL>alter database close;
b) nid target=sys as sysdba dbname=ketan(this will change the control files and headers of the datafiles with the correction of new name)
c)cd $ORACLE_HOME/dbs
d) cp initaishu.ora initketan.ora
e)vi initketan.ora
find db_name parameter and change it to ketan
f)vi /etc/oratab
change the name aishu to ketan
g) . oraenv
set the variable ORACLE_SID=ketan
f) startup the database
SQL> startup (but the database open will error out since the datbase should open with resetlogs)
h) alter database open resetlogs;

Option 2
By changing the control file
a)alter database backup control file to trace;

b)go to trace directory and edit the control file trace
c)In control file trace update database name aishu to ketan
d)shut down the database
e)remove the control file
f)vi init file and change the dbname aishu to ketan
g)startup nomount
h)execute the control file trace which we modified earlier(this will create a new control file in the location with the  new dbname )
i) alter database open resetlogs;

22) Can you change the instance name once the database is created?
SQL>alter system set instance_name=’test’ scope=spfile;
shut immediate;
cp spfilepaddu.ora spfilekarthika.ora
export ORACLE_SID=karthika
startup

23) Can you rename the tablespace once it is created?
SQL>alter tablespace testtbs1 rename to testtbs3;

24) Can you rename the user once it is created?
No there is no direct command to change the user name but there is  a work around.
1) export user
exp / as sysdba owner=paddu file=/home/oracle/paddu.dmp
sqlplus / as sysdba
SQL> drop user paddu cascade;
QL>create user aishu identified by aishu;
SQL>grant connect,resource to aishu;
SQL> exit
imp file=/home/oracle/paddu.dmp fromuser=paddu touser=aishu
sqlplus / as sysdba
SQL>select username from dba_users;

25) Can you rename the table once it is created?
Yes
connect to user aishu
SQL>connect aishu/aishu
SQl>select * from tab
SQL> create table t as select * from user_tables;
(or)
SQL> create table dummy (id number,name varchar2(50),salary number);
SQL>rename t to t1

26) Can you rename the column in a table?
yes
SQL>desc t;
alter table t rename column result_cache to aishu;

27) Where you can see the datafile information?

desc dba_data_files;

SQL> select tablespace_name,file_name,autoextensible, bytes/1024/1024 SIZE_MB from dba_data_files;

28) Where you can see the tempfile or tablespace information?(for a particular database)
desc dba_temp_files;

desc dba_tablespaces;

29) What is the difference between v$ views and dba views?
dba views are static views
v$ views are dynamic views
dba views are available once the db is in open mode only
V$ can be viewed even the database is in mount state

30) What is the difference between a role and privilege , can you provide an example?
Set of priviliges is nothing but a role.
providing authorisation to an user such as create,alter,delete,drop,truncate,insert,update.

How many types of privileges are there ?
system privileges : create session/user
object privileges : insert,update,delete or create table

31) Where to view the roles and privileges assigned to a user?
For roles:-desc
dba_roles can be used to know what  are all the roles in a database.
select * from dba_roles;
role_sys_privs can used to know what are all the system privileges assigned to that role.

role_tab_privs can be used to know what are all the object privileges assigned to that role
dba_role_privs can be used to know the grantees assigned to that role

For privileges:-
dba_tab_privs: can be used to know what all privileges assigned to a user
SQL> select grantee,owner,table_name,grantor,privilege from dba_tab_privs where grantee=’AISHU';

dba_sys_privs: to know what all system privileges assigned to a user
SQL> select grantee,privilege from dba_sys_privs where grantee=’AISHU';

32) What is the difference between with grant option and with admin option while assigning privileges?
Grant option : We can grant that grant to other user
admin option : can be used for sysdba privileges to grant other

grant select on T to aishu with grant option;
Aishu can grant select on table T to any one;

grant dba to aishu with admin option;
aishu can grant or manage dba role and assign to anyone.

34) How to reovoke privilege or role?
revoke select on T from aishu;
revoke suresh from aishu;(suresh is a role here )

33) How to change the default tablespace for a user?
alter user aishu default tablespace t;

34) How to give a tablespace quota to a user?
alter user aishu grant unlimited quota on tablespace t;

36) What are constraints? Can you list them and when will you use them?
Constrains in oracle are use to protect the integrity of the data.
for example a not null constraint will not allow any null value in the column
a unique constraint will not allow any duplicate value in the column
a  primary key constraint will not allow any duplicate value and null in the column.
a foreign key constraint will be from the one of the primary key of the table which means data must resides in the primary key table(Master list table)

Master Table (a table that contains primary key)
SQL> create table pincode (area varchar2(30), pincodenum number primary key);
SQL> insert into pincode values (‘Miyapur’,500049);
SQL> insert into pincode values(‘Ameerpet’,500084);

Child Table ( aa table that is referred to primary key column)
SQL> Create table employee (empname varchar2(30),empid number unique,address1 varchar2(10) check=’Hyderabad’, address2 varchar2(20),pincode number
constraint pin_fk foreign key(pincode) references pincode (pincodenum));

SQL> Insert into employee values(‘Aishu’,1,’Ameerpet,’Line’,’500084′);

37) What is Row chaining? When does it occur? where can you find it? What is the solution?
When the row is not adequate to fit in the block while inserting oracle will insert half row in one block and half in another block leaving  a pointer between these two blocks.
select table_name,chain_cnt from dba_tables where table_name=’tablename';
Solutions:
create a table with bigger the block size
1) Create tablespace ts data file ‘/u01/oradata/aishu/paddu.dbf’ size 100m blocksize 16k;
2) alter table employee move to ts;
Here TS is the tablespace name with bigger size, before creating tablespace it is assumed that you have created a db buffer cache for it.

38) What is row migration? When does it occur? Where can you find this information?
Row migration happens when update occurs at one column and the row is not adequate to fit in the block then the entire row will be moved to the new block.

select table_name,chain_cnt from dba_tables where table_name=’tablename';
Solution:
set pct_free storage parameter for table to adequate.;

39) How to find whether the instance is using spfile or pfile?
show parameter spfile;

40) How to create password file?
orapwd file=$ORACLE_HOME/dbs/pwaishu.ora entry=5 ignore case=Y;

41) How to create a database manually , can you provide steps briefly?
1) create a parameter file in /dbs directory with necessary parameters like db_name,instance_name,control file locations,sga_max_size etc..
2)create necessary directories for datafiles,trace files,redo log files, control files according OFA
3)prepare the create db command
4)create catalog views (compile,invalid)
5)add entries in listner.ora,tnsnames.ora
6) add entry in /etc/oratab

42) What is OFA? What is the benefit of it?
Oracle Flexible Architecture
Different directory structures with diff files and we keeping the files (redolog,control etc) in the corresponding described locations which keeps the files in track and we can easily manage them,Inaddition to tha I/O will be redistributed.

43) What does system tablespace and sysaux tablespace contains?
system table space stores the system tables such as dbtables,oracle base tables,dictionary objects that related to oracle.a kind of metada(data about data)
Sysaux table space: from 10g onwards oracle has segregated some of the dictionary objects to be created in sysaux table space seperating from system table space to reduce the burden on the one table space
for example oracle session statistics,system statistics,awr data( automatic work load repository),oracle execution statistics

44) Do you know about statistics , what is the use of it? What kind of statistics exists in database?
Statistics is a collection information about data or database
There are different types of statistics that oracle maintains-
1)System-Statistics: statistics about the hardware like cpu speed,I/O speed,read time write time etc : select * from aux_stats$
2)Object statistics : For a table oracle collects the information about no.of rows,no.of blocks,avg row length etc.We can view
SQL>select table_name,num_rows,blocks,avg_row_len from dba_tables
for index oracle collect statistics on index column about no.of rows,no.of root blocks,no.of branch blocks,no.of leaf blocks,no.of distinct values etc.

46. Why you need statistics to be collected?

These statistics wil help the query execution engine called optimizer to determine how best the data can the accessed

45) Where to find the table size?
table=create segment in a tablespace, that segment contains extents and that extents contains blocks

T=100 * 8192 = 819200000
dba_segments

SQL>select segment_name,bytes/1024/1024 from dba_segments where segment_name=’T';

46> How to find the size of a database?
select sum (bytes) from dba_segments;

46) Where to find different types of segments in oracle database?
select distinct segment_type from dba_segments;

47) How to resize the datafile?
To resize a datafile the first most thing is the data file should be in auto extendable mode
ALTER DATABASE DATAFILE ‘/u02/oracle/rbdb1/stuff01.dbf’ RESIZE 100M;.

Can i resize the datafile to lesser than it has?
I have 1gb
I want to 100MB,
But the data in that datafile is upto 500MB
can i resize to 100mb?
Yes we can unless the data is not above 100MB in the datafile.

48) How to add datafile to a tablepsace?
alter tablespace t add datafile ‘/u01/oradata/paddu/tbs1′ size 100M;

49) How to delete the datafile?
alter tablespace t drop datafile ‘/u01/oradata/paddu/tbs1′

Note:The drop datafile will only works if the datafile is empty

50) How to move datafiles from one location to another location? Can you provide the steps?
1.Connect as SYS DBA with CONNECT / AS SYSDBA command.
2.Make offline the affected tablespace with ALTER TABLESPACE <tablespace name> OFFLINE; command.
3.Copy the datafiles from old location to new location using OS cp
4.Modify the name or location of datafiles in Oracle data dictionary using following command syntax:
ALTER database RENAME DATAFILE ‘<old location>’ TO ‘<new location>';
5.Bring the tablespace online again with ALTER TABLESPACE alter tablespace <tablespace name> ONLINE; command

51) What is profile? what is the benefit of profile? Where do you see the information of profiles? Provide an example of profile?
Profile is a set of properties assign to an user
For an example password complexity,password reuse,password expiry,idle time etc

SQL>desc dba_profiles;
SQL> select username,profile from dba_users;

52) How to change the profile of a user?
alter user username profile profile name

53) How to create user?
create user username identified by password default tablespace testtbs1 profile test;
ex: create user paddu identified by paddu default tablespace testtbs1 profile test;
SQL> select username,profile from dba_users;
SQL> grant connect,resource to paddu;

54) How to create schema?
schema is nothing but an user.

55) How to grant privileges to user?
using grant command

grant create table to user;

grant create table to user with grant option;

Note: with grant option provides user to grant the privilege to other users as well, kind of admin

56) Can you delete alert log while database is up and running?

show parameter background;

Yes database can create a new alert log file,but whenever any activity happens in the database it creates a new alert log file

Yes one can delete or move the alert log file while the database is up and running there will be no impact,oracle will automatically creates a new alert log if it not found any in the directory

57) What is fragmentation of table?
Fragmentation of a table is something when ever there is a purge or deletion of a table.Oracle will not use those unused blocks and always try to allocate the extents above high watermark.this leads the table to grow larger than its size.

58) What is cursor?
Cursor is some thing which resides in the PGA and dc in SGA

A cursor is a handle, or pointer, to the context area. Through the cursor, a … Cursors allow you to fetch and process rows returned by a SELECT. statement, one …
. Through the cursor, a

PL/SQL program can control the context area and what happens to it as

the statement is processed. Two important features about the cursor are implict and explict cursors

59) Can you tell various dynamic views you know about and their purpose?

v$session-shows about sessions information that logged into database
Ex:select sid,status,username,logon_time,blocking_session,module,event,sql_id  from v$session

v$process : To view the process information to attached to the session
Ex: select pid,spid,addr from v$process

v$database: To view the database information
Ex:  select dbid,name,open_mode,created from v$database;

v$instance : To view the instance information
Ex: SQL>select  INSTANCE_NAME, HOST_NAME,STATUS,STARTUP_TIME from v$instance;

v$lock : To view the locked sessions
Ex:SQL> select SID,type,id1,lmode,request from v$lock;
SID is the session ID column that is requesting or holding the lock
TYPE: Type is the column that shows about what kind of end queue or lock it has
ID1 :This column says about the object id that involved in lock.Match this object id dba_objects to get the object names
LMODE : lock mode it can be 1-6
6 is the least level of lock and an exclusive lock,when we update a row that row will be locked as exclusive so that no one will be modified
From 1-5 the locks are different types of levels which are some shared or table level locks

Row exclusive – Any DML that happens on any row locks as exclusive so that no one can modify
Row shared – Select statement ran, during that period the rows will be in shared mode so that no modification to be done until  that select retrieve all rows.
Table Lock – When an update statement ran on column , no other can moidfy the structure of table, and allow row exclusive

v$parameter – Displays information about parametters in database
SQL>select name,value,ISSYS_MODIFIABLE from v$parameter where name=’sga_target';

v$sgastat :  Displays information about sga individual pool sizes and also displays free memory in the sga
SQL> select * from v$sgastat;

v$sgainfo : Displays information about sga  pool sizes
SQL> select * from v$sgainfo;

v$transaction: Displays information about the transactions that running in the database
SQL>select * from  v$transaction;

v$pgaStat:displays about pga allocated in the database
SQL>select * from v$pgastat;

v$sga_resize_ops : Displays information about sga resize operation when sga target is set
SQL> select * from v$sga_resize_ops;

v$sysstat : Displays the systems statistics  information
SQL> select * from v$sysstat;

v$sesstat : Displays the information about session statistics
SQL> select * from v$sesstat;
Display each statistics information from sysstat but for each session, so 604 statistics X each session

v$logfile : Displays information about the redo log files
SQL> select group#,member,status from v$logfile;

v$log : Displays information about redolog groups
SQL>select group#,members,status from v$log;

v$undostat : Displays the information about undo usage in the database

v$sysaux_occupants : Displays the information about objects that resides in sysaux table space

60) Difference between v$ views and dba_views?

V$ views are dynamic and populated from base table like X$BH and USER$ etc etc

DBA_** views are the the views built on top of v$ views in combination. for example v$session has been built from v$session,user$ etc

60) Where to view the session information?

SQL> select sid,status,username,logon_time,machine,sql_id,blocking_session,event from v$session where sid=”;

If you do not know the sid replace with any column information you know in where condition.

61) Where to view the process associated with session information?
select sid,status,username,action,program,machine from v$session  where paddr in (select addr from v$process where spid=5046);

61) Where to view the locks in oracle database?
v$lock
62) What are locks?
locks are low level serialisation mechanism called end queues in oracle which protects the database of data changes

63) What latches?
latches are typically a kind of locks  but held for very short time to protect the memory structures of the instance

64) Where does Oracle latch or lock occurs?
Oracle latch occurs in the memory structures i.e, in instance ex:buffer latch,redolog latch,shared pool latch
ORacle lock occurs at block level to protect the integrity of the data as the data stored in the block only ex: row lock,table lock etc

65) Where to see the information about latches?
v$latch and v$latch_children

66) How to switch from pfile to spfile?
SQL>create spfile from pfile;
bounce the database;
Now the database will pickup the spfile automatically

67) Explain the difference between a data block, an extent and a segment.
Data block is a lowest level storage structure, a block cannot span multiple extents
Extent is a set of block which resides inside the table space, an extent cannot span multiple segments
Segment is set of  extents nothing but an object, a segment can spawn multiple datafiles

68) How to get the DDL of a table or index? i.e create statement?
SQL> select dbms_metadata.get_ddl(‘AISHU’,’T’,’TABLE’) from dual
SQL>  select dbms_metadata.get_ddl(‘TABLE’,’T’,’AISHU’)  from dual;

DBMS_METADATA.GET_DDL(‘TABLE’,’T’,’AISHU’)
——————————————————————————–

CREATE TABLE “AISHU”.”T”
(    “X” VARCHAR2(100)
) SEGMENT CREATION IMMEDI

SQL> set long 1000
SQL> /

DBMS_METADATA.GET_DDL(‘TABLE’,’T’,’AISHU’)
——————————————————————————–

CREATE TABLE “AISHU”.”T”
(    “X” VARCHAR2(100)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “TESTTBS2″

For user

SQL> select dbms_metadata.get_ddl(‘USER’,’AISHU’)  from dual;

DBMS_METADATA.GET_DDL(‘USER’,’AISHU’)
——————————————————————————–

CREATE USER “AISHU” IDENTIFIED BY VALUES ‘S:02BB0F7450ED93B75191C06AD3CD1E1E7
DB11803941FB7B885803634D39F;F8EF185F1D85D4B3′
DEFAULT TABLESPACE “TESTTBS1″
TEMPORARY TABLESPACE “TEMP2″

69) What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
Temporary table space for sorting and also used for temporary tables
Permanent table space contains the permanent or business data

70) What is checkpoint? why database need it?
checkponit  which occurs when ever the redolog switch happens,during this ckpt process writes the check point information to control file and the data file header and tells to dbwr to flush the dirty buffer from buffer cache to disk until that check point.

71) What is log switch, when does it occurs?

Log switch occurs when the current redo log is full and the log writer has to go to next redo log group.

71) Where to view the undo usage information?
v$undostat

72) How to set the log archive destination? can we have multiple destinations for archivelogs?
Yes we can have multiple destination for achivelogs
we can have 30 destination from 11g onwards
to see destiantion you can use follow and set accordingly

show parameter log_archive

SQL> alter system set log_archive_dest=”  scope=memory;

System altered.

SQL> alter system set log_archive_dest_1=’location=/u02/archives/paddu’ scope=memory;

System altered.

SQL> alter system set log_archive_dest_2=’location=/u01/archives/paddu’ scope=memory;

System altered.

SQL>

73) Can you rename a database? Provide steps?
Yes, we can rename a database, we have two topins
Until 10g,
As the database name is written in control file we have to change the database name in control file and in init file.
1. Alter Database backup control file to trace;
2. Above step will create a text control file in user_dump_dest directory.
3. Change name of the Database in above file and in init.ora file.
4. STARTUP NOMOUNT
5. Run the script that was modified in step 3
6. ALTER DATABASE OPEN RESETLOGS;
From 10g onwards
Using NID utility
If I am changing the database name , does mu backup are valid?
Invalid, Your database name is changed so the old backup backup in invalid as the name is old, rman check with dbid and name.

74) Why you need to do open resetlogs, what does it?
Whenever there is recovery operation performed specifically incomplete media recovery , the database must be open with reset logs since we dont have the archives or redo information until point of failure, hence this is required. further this will reset the archive log sequence

75) How to multiplex controlfiles?
if we are using pfile:
show parameter control_files;
Note down the  control file location
shut down the database
copy the control file old location to newer location
add the new control file location in parameter file
startup the database
show parameter control_files;(this shows the old and new location as well)

if we are using spfile:
show parameter control_files;
alter system set controlfile=’oldlocation,newlocation’ scope=’spfile’
shut down the database
copy the control file from old location to new location
startup the database

76) How to multiplex redo log files?
alter database add log ‘  ‘ to group3;

77) How to add redo log groups to a database?
alter database add log ‘ ‘ size 50m group6;
v$log or v$logfiles;

78) Can you drop the redo log groups while the database is up and running?
Yes we can drop the redolog group but the redo log should be inactive

79) Can you drop the system tablespace, if so what happened to database?
No we can’t drop  the system tablespace.Oracle will not allow it

80) Can you drop the normal tablespace, if so what happened to database?
Yes we can drop the normal table spaces but the associated objects will be dropped
but the table space should be empty if not we have to use
SQL>drop tablespace tablespace name including contents;
if you want to drop the associated datafiles also with table space we should use
SQL>drop tablespace tablespace name including contents and datafiles;

81) What is the difference between Oracle home and oracle base
ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

82) Where do you check the free space of objects?
SQL>dba_free_space;
select * from dba_free_space;

83) How to kill the blocking session, how to find the blocking session?
We have to find the blocking session information by using
SQL>select sid,username,serial#,status,event,blocing_session from v$session where username=’SYS';
Now check the blocking_session column for the sid that is blocking and confirm with the application team to kill
Now  execute
SQL> alter system kill session ‘sid,serial#’ immediate;
alternatively we can also find the lock informatin in v$lock

84) Can you kill the pmon or smon or ckpt ? what happens to database?
These are all the mandatory process to run the database.if we kill any of the process the DB will be crash.

85) Define the parameters for different pools of oracle instance?
shared pool:shared_pool_size;
DB buffer cache:db_cache_size;
java pool:java_pool_size;
largepool:large_pool_size;
stream pool:stream_pool_size;
Redolog buffer:log_buffer;

alternatively sga_max_size and sga_target should set to manage this pools automatically.

86) Consider the scenario below,

shared pool:shared_pool_size; 100m
DB buffer cache:db_cache_size; 100m
java pool:java_pool_size; 100m
largepool:large_pool_size; 100m
stream pool:stream_pool_size; 10m
Redolog buffer:log_buffer; 5m

Total SGA manually allocated in pools: 410M

I have also kept SGA_MAX_SIZE=400M in pfile and started the database which one the Oracle consider, 410M or 400M

410M, if the sga_max_size is lesser than the all pools total if specified in pfile then sga_max_size parameter is ignored.

86) List Process you follow to start looking into Performance issue at database level (If the application is running very slow, at what points do you need to go about the database in order to improve the performance?)

Answer ( Although i have never worked directly on performance issues, the below can be steps)

Run a TOP command in Unix to see CPU usage (identify CPU killer processes)
Run VMSTAT, SAR, and PRSTAT command to get more information on CPU and memory usage and possible blocking
Run AWR report to identify:
1. TOP 5 WAIT EVENTS
2. RESOURCE intensive SQL statements
See if STATISTICS on affected tables needs to be re-generated
IF poorly written statements are culprit, run a EXPLAIN PLAN on these statements and see whether new index or use of HINT brings the cost of SQL down.

87) Can you explain different modes of startup of oracle database?
No mount:starts the instance and bg process only
Mount:Oracle reads the control file and identify all the datafiles and kept them ready.
Open:The datafiles marked as read/write and database is now ready for operation.

88) Can you explain different modes of shutdown of oracle database?
close:all the changes in the buffer cache will be pushed to datafiles and existed session will be disconnected and no new sessions will be permitted.
dismount: All the datafiles will be closed
instance shut down:bg wil be stopped and memory pools will be cleared from OS.

89) How to know how many oracle homes or oracle instances exists in database host?
Once the oracle installation is completed the installer will update the file called /etc/oratab with new home with this file we can find how many homes are existed
To find how many instances are running use
ps -eaf | grep pmon

90) What is the difference between putty and sqlplus?
Putty is an ssh client to connect to the database host from remotely.
SQL database connectivity tool to connect to the database using tns names entry

91) What is tns string?
tns string is an entry to identify the database host,db port and the db name.Oracle will use oracle sqlplus will use this entries appropriate or right database host.

92) What is tnsentry?

Tns entry is a address to the database host and database written in the tnsnames.ora, generally tnsnames.ora located at $ORACLE_HOME/network/admin

92) How to change the database into archivelog mode?
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/archives/paddu
Oldest online log sequence     24
Next log sequence to archive   26
Current log sequence           26

We have to bring the db to mount mode and then use
SQL>alter database close;(This brings the db to mount mode)
SQL>alter database archive log;
SQL>startup force;

To disable the archive log just use
SQL>alter database noarchivelog;

93) How to set the password to expiry of 90 days?
identify the profile for that user
SQL> select username,default_profile from dba_user where username=’user';
SQL> select * from dba_profiles where profile=’DEFAULT';
change the profile for password life time
SQL> alter profile default set limit=’PASSWORD_LIFE_TIME=90′;

94) How to set the new password for Oracle user?
alter user username identified by newpassword;

95) How to set the same password to oracle user when the password is expired?
select username,password from dba_users where username=’username';
SQL>alter user username identified by values ‘above password';

96) Where do you find the password for oracle user?
select username,password from dba_users where username=’username';

97)How to set new undo tablespace in oracle database?
create a new undo tablespace
SQL> create undo tablespace undotbs4 datafile ‘/u01/oradata/paddu/undotbs4.dbs’ size 100m;

Tablespace created.

SQL> show parameter undo_tablespace;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
undo_tablespace                      string      UNDOTBS3
SQL> alter system set undo_tablespace=’UNDOTBS4′ scope=spfile;

System altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  263049216 bytes
Fixed Size                  2212448 bytes
Variable Size             167775648 bytes
Database Buffers           88080384 bytes
Redo Buffers                4980736 bytes
Database mounted.
Database opened.
SQL> show parameter undo_tablespace;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
undo_tablespace                      string      UNDOTBS4
SQL>

100) Renaming schema 

Fastest Way, since the original import will not happen only metadata creation will happen, as the transportable import has been performed, In TTS the associated datafiles will be attached to new user , hence the datafiles with existing object(tables/indexex etc) will be point to new user.

1.    create user new_user…
2. grant … to new_user;
3. execute dbms_tts.transport_set_check(…);
4. alter tablespace … read only;
5. exp transport_tablespace=y tablespaces=…
6. drop tablespace … including contents;
7. imp transport_tablespace=y tablespaces=… datafiles=… fromuser=old_user touser=newuser
8. create nondata objects in new_user schema
9. [drop user old_user cascade;]
10. alter tablespace … read write;

Oracle Concepts and Architecture

Database Structures

More Questions with Practical way explained here in this blog, Please read
1. What are the components of physical database structure of Oracle database?
Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.
2. What are the components of logical database structure of Oracle database?
There are tablespaces and database’s schema objects.
3. What is a tablespace?
A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.
4. What is SYSTEM tablespace and when is it created?
Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.
5. Explain the relationship among database, tablespace and data file.
Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.
6. What is schema?
A schema is collection of database objects of a user.
7. What are Schema Objects?
Schema objects are the logical structures that directly refer to the database’s data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.
8. Can objects of the same schema reside in different table spaces?
Yes.
9. Can a tablespace hold objects from different schemes?
Yes.
10. What is Oracle table?
A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.
11. What is an Oracle view?
A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)
12. Do a view contain data?
Views do not contain or store data.
13. Can a view based on another view?
Yes.
14. What are the advantages of views?
– Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
– Hide data complexity.
– Simplify commands for the user.
– Present the data in a different perspective from that of the base table.
– Store complex queries.
15. What is an Oracle sequence?
A sequence generates a serial list of unique numbers for numerical columns of a database’s tables.
16. What is a synonym?
A synonym is an alias for a table, view, sequence or program unit.
Read this blog for more core practical dba questions
17. What are the types of synonyms?
There are two types of synonyms private and public.
18. What is a private synonym?
Only its owner can access a private synonym.
19. What is a public synonym?
Any database user can access a public synonym.
20. What are synonyms used for?
– Mask the real name and owner of an object.
– Provide public access to an object
– Provide location transparency for tables, views or program units of a remote database.
– Simplify the SQL statements for database users.
21. What is an Oracle index?
An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.
22. How are the index updates?
Indexes are automatically maintained and used by Oracle. Changes to table data are automatically incorporated into all relevant indexes.
23. What are clusters?
Clusters are groups of one or more tables physically stores together to share common columns and are often used together.
24. What is cluster key?
The related columns of the tables in a cluster are called the cluster key.
25. What is index cluster?
A cluster with an index on the cluster key.
26. What is hash cluster?
A row is stored in a hash cluster based on the result of applying a hash function to the row’s cluster key value. All rows with the same hash key value are stores together on disk.
27. When can hash cluster used?
Hash clusters are better choice when a table is often queried with equality queries. For such queries the specified cluster key value is hashed. The resulting hash key value points directly to the area on disk that stores the specified rows.
28. What is database link?
A database link is a named object that describes a “path” from one database to another.
29. What are the types of database links?
Private database link, public database link & network database link.
30. What is private database link?
Private database link is created on behalf of a specific user. A private database link can be used only when the owner of the link specifies a global object name in a SQL statement or in the definition of the owner’s views or procedures.
31. What is public database link?
Public database link is created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.
32. What is network database link?
Network database link is created and managed by a network domain service. A network database link can be used when any user of any database in the network specifies a global object name in a SQL statement or object definition.
33. What is data block?
Oracle database’s data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk.
34. How to define data block size?
A data block size is specified for each Oracle database when the database is created. A database users and allocated free database space in Oracle data blocks. Block size is specified in init.ora file and cannot be changed latter.
35. What is row chaining?
In circumstances, all of the data for a row in a table may not be able to fit in the same data block. When this occurs, the data for the row is stored in a chain of data block (one or more) reserved for that segment.
36. What is an extent?
An extent is a specific number of contiguous data blocks, obtained in a single allocation and used to store a specific type of information.
37. What is a segment?
A segment is a set of extents allocated for a certain logical structure.
38. What are the different types of segments?
Data segment, index segment, rollback segment and temporary segment.
39. What is a data segment?
Each non-clustered table has a data segment. All of the table’s data is stored in the extents of its data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster’s data segment.
40. What is an index segment?
Each index has an index segment that stores all of its data.
41. What is rollback segment?
A database contains one or more rollback segments to temporarily store “undo” information.
42. What are the uses of rollback segment?
To generate read-consistent database information during database recovery and to rollback uncommitted transactions by the users.
43. What is a temporary segment?
Temporary segments are created by Oracle when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the temporary segment extents are released to the system for future use.
44. What is a datafile?
Every Oracle database has one or more physical data files. A database’s data files contain all the database data. The data of logical database structures such as tables and indexes is physically stored in the data files allocated for a database.
45. What are the characteristics of data files?
A data file can be associated with only one database. Once created a data file can’t change size. One or more data files form a logical unit of database storage called a tablespace.
46. What is a redo log?
The set of redo log files for a database is collectively known as the database redo log.
47. What is the function of redo log?
The primary function of the redo log is to record all changes made to data.
48. What is the use of redo log information?
The information in a redo log file is used only to recover the database from a system or media failure prevents database data from being written to a database’s data files.
49. What does a control file contains?
– Database name
– Names and locations of a database’s files and redolog files.
– Time stamp of database creation.
50. What is the use of control file?
When an instance of an Oracle database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.
Data Base Administration
51. What is a database instance? Explain.
A database instance (Server) is a set of memory structure and background processes that access a set of database files. The processes can be shared by all of the users.
The memory structure that is used to store the most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed against data file.
52. What is Parallel Server?
Multiple instances accessing the same database (only in multi-CPU environments)
53. What is a schema?
The set of objects owned by user account is called the schema.
54. What is an index? How it is implemented in Oracle database?
An index is a database structure used by the server to have direct access of a row in a table. An index is automatically created when a unique of primary key constraint clause is specified in create table command
55. What are clusters?
Group of tables physically stored together because they share common columns and are often used together is called cluster.
56. What is a cluster key?
The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stored only once for multiple tables in the cluster.
57. What is the basic element of base configuration of an Oracle database?
It consists of
one or more data files.
one or more control files.
two or more redo log files.
The Database contains
multiple users/schemas
one or more rollback segments
one or more tablespaces
Data dictionary tables
User objects (table,indexes,views etc.,)
The server that access the database consists of
SGA (Database buffer, Dictionary Cache Buffers, Redo log buffers, Shared SQL pool)
SMON (System MONito)
PMON (Process MONitor)
LGWR (LoG Write)
DBWR (Data Base Write)
ARCH (ARCHiver)
CKPT (Check Point)
RECO
Dispatcher
User Process with associated PGS
58. What is a deadlock? Explain.
Two processes waiting to update the rows of a table, which are locked by other processes then deadlock arises.
In a database environment this will often happen because of not issuing the proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce drastically.
These locks will be released automatically when a commit/rollback operation performed or any one of this processes being killed externally.
More practical questions here in this blog

Memory Management

59. What is SGA?
The System Global Area in an Oracle database is the area in memory to facilitate the transfer of information between users. It holds the most recently requested structural information between users. It holds the most recently requested structural information about the database. The structure is database buffers, dictionary cache, redo log buffer and shared pool area.
DBA Interview Question & Answers with practical examples
60. What is a shared pool?
The data dictionary cache is stored in an area in SGA called the shared pool. This will allow sharing of parsed SQL statements among concurrent users.
61. What is mean by Program Global Area (PGA)?
It is area in memory that is used by a single Oracle user process.
62. What is a data segment?
Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.
63. What are the factors causing the reparsing of SQL statements in SGA?
Due to insufficient shared pool size.
Monitor the ratio of the reloads takes place while executing SQL statements. If the ratio is greater than 1 then increase the SHARED_POOL_SIZE.

Database Logical & Physical Architecture

64. What is Database Buffers?
Database buffers are cache in the SGA used to hold the data blocks that are read from the data segments in the database such as tables, indexes and clusters DB_BLOCK_BUFFERS parameter in INIT.ORA decides the size.
65. What is dictionary cache?
Dictionary cache is information about the database objects stored in a data dictionary table.
66. What is meant by recursive hints?
Number of times processes repeatedly query the dictionary table is called recursive hints. It is due to the data dictionary cache is too small. By increasing the SHARED_POOL_SIZE parameter we can optimize the size of data dictionary cache.
67. What is redo log buffer?
Changes made to the records are written to the on-line redo log files. So that they can be used in roll forward operations during database recoveries. Before writing them into the redo log files, they will first brought to redo log buffers in SGA and LGWR will write into files frequently. LOG_BUFFER parameter will decide the size.
Read here DBA Questions and answers with practical examples
68. How will you swap objects into a different table space for an existing database?
– Export the user
– Perform import using the command imp system/manager file=export.dmp indexfile=newrite.sql. This will create all definitions into newfile.sql.
– Drop necessary objects.
– Run the script newfile.sql after altering the tablespaces.
– Import from the backup for the necessary objects.
69. List the Optional Flexible Architecture (OFA) of Oracle database? How can we organize the tablespaces in Oracle database to have maximum performance?
SYSTEM – Data dictionary tables.
DATA – Standard operational tables.
DATA2- Static tables used for standard operations
INDEXES – Indexes for Standard operational tables.
INDEXES1 – Indexes of static tables used for standard operations.
TOOLS – Tools table.
TOOLS1 – Indexes for tools table.
RBS – Standard Operations Rollback Segments,
RBS1,RBS2 – Additional/Special Rollback segments.
TEMP – Temporary purpose tablespace
TEMP_USER – Temporary tablespace for users.
USERS – User tablespace.
70. How will you force database to use particular rollback segment?
SET TRANSACTION USE ROLLBACK SEGMENT rbs_name.
71. What is meant by free extent?
A free extent is a collection of continuous free blocks in tablespace. When a segment is dropped its extents are reallocated and are marked as free.
72.Which parameter in Storage clause will reduce number of rows per block?
PCTFREE parameter
Row size also reduces no of rows per block.
73. What is the significance of having storage clause?
We can plan the storage for a table as how much initial extents are required, how much can be extended next, how much % should leave free for managing row updating, etc.,
74. How does Space allocation table place within a block?
Each block contains entries as follows
Fixed block header
Variable block header
Row Header, row date (multiple rows may exists)
PCTEREE (% of free space for row updating in future)
75. What is the role of PCTFREE parameter is storage clause?
This is used to reserve certain amount of space in a block for expansion of rows.
Read also: Here
76. What is the OPTIMAL parameter?
It is used to set the optimal length of a rollback segment.
77. What is the functionality of SYSTEM table space?
To manage the database level transactions such as modifications of the data dictionary table that record information about the free space usage.
78. How will you create multiple rollback segments in a database?
– Create a database, which implicitly creates a SYSTEM rollback segment in a SYSTEM tablespace.
– Create a second rollback segment name R0 in the SYSTEM tablespace.
– Make new rollback segment available (after shutdown, modify init.ora file and start database)
– Create other tablespaces (RBS) for rollback segments.
– Deactivate rollback segment R0 and activate the newly created rollback segments.
79. How the space utilization takes place within rollback segments?
It will try to fit the transaction in a cyclic fashion to all existing extents. Once it found an extent is in use then it forced to acquire a new extent (number of extents is based on the optimal size)
80. Why query fails sometimes?
Rollback segment dynamically extent to handle larger transactions entry loads.
A single transaction may wipeout all available free space in the rollback segment tablespace. This prevents other user using rollback segments.
81. How will you monitor the space allocation?
By querying DBA_SEGMENT table/view
82. How will you monitor rollback segment status?
Querying the DBA_ROLLBACK_SEGS view
IN USE – Rollback Segment is on-line.
AVAILABLE – Rollback Segment available but not on-line.
OFF-LINE – Rollback Segment off-line
INVALID – Rollback Segment Dropped.
NEEDS RECOVERY – Contains data but need recovery or corrupted.
PARTLY AVAILABLE – Contains data from an unresolved transaction involving a distributed database.
83. List the sequence of events when a large transaction that exceeds beyond its optimal value when an entry wraps and causes the rollback segment to expand into another extend.
Transaction Begins.
An entry is made in the RES header for new transactions entry
Transaction acquires blocks in an extent of RBS
The entry attempts to wrap into second extent. None is available, so that the RBS must extent.
The RBS checks to see if it is part of its OPTIMAL size.
RBS chooses its oldest inactive segment.
Oldest inactive segment is eliminated.
RBS extents
The data dictionary tables for space management are updated.
Transaction Completes.
84. How can we plan storage for very large tables?
Limit the number of extents in the table
Separate table from its indexes.
Allocate sufficient temporary storage.
85. How will you estimate the space required by a non-clustered table?
Calculate the total header size
Calculate the available data space per data block
Calculate the combined column lengths of the average row
Calculate the total average row size.
Calculate the average number rows that can fit in a block
Calculate the number of blocks and bytes required for the table.
After arriving the calculation, add 10 % additional space to calculate the initial extent size for a working table.
86. It is possible to use raw devices as data files and what are the advantages over file system files?
Yes.
The advantages over file system files are that I/O will be improved because Oracle is bye-passing the kernel which writing into disk. Disk corruption will be very less.
87. What is a Control file?
Database’s overall physical architecture is maintained in a file called control file. It will be used to maintain internal consistency and guide recovery operations. Multiple copies of control files are advisable.
88. How to implement the multiple control files for an existing database?
Shutdown the database
Copy one of the existing control file to new location
Edit Config ora file by adding new control filename
Restart the database.
89. What is redo log file mirroring? How can be achieved?
Process of having a copy of redo log files is called mirroring.
This can be achieved by creating group of log files together, so that LGWR will automatically writes them to all the members of the current on-line redo log group. If any one group fails then database automatically switch over to next group. It degrades performance.
90. What is advantage of having disk shadowing / mirroring?
Shadow set of disks save as a backup in the event of disk failure. In most operating systems if any disk failure occurs it automatically switchover to place of failed disk.
Improved performance because most OS support volume shadowing can direct file I/O request to use the shadow set of files instead of the main set of files. This reduces I/O load on the main set of disks.
91. What is use of rollback segments in Oracle database?
They allow the database to maintain read consistency between multiple transactions.
92. What is a rollback segment entry?
It is the set of before image data blocks that contain rows that are modified by a transaction.
Each rollback segment entry must be completed within one rollback segment.
A single rollback segment can have multiple rollback segment entries.
93. What is hit ratio?
It is a measure of well the data cache buffer is handling requests for data.
Hit Ratio = (Logical Reads – Physical Reads – Hits Misses)/ Logical Reads.
94. When will be a segment released?
When Segment is dropped.
When Shrink (RBS only)
When truncated (TRUNCATE used with drop storage option)
95. What are disadvantages of having raw devices?
We should depend on export/import utility for backup/recovery (fully reliable)
The tar command cannot be used for physical file backup, instead we can use dd command, which is less flexible and has limited recoveries.
96. List the factors that can affect the accuracy of the estimations?
– The space used transaction entries and deleted records, does not become free immediately after completion due to delayed cleanout.
– Trailing nulls and length bytes are not stored.
– Inserts of, updates to and deletes of rows as well as columns larger than a single data block, can cause fragmentation a chained row pieces.

Database Security & Administration

97. What is user Account in Oracle database?
A user account is not a physical structure in database but it is having important relationship to the objects in the database and will be having certain privileges.
98. How will you enforce security using stored procedures?
Don’t grant user access directly to tables within the application.
Instead grant the ability to access the procedures that access the tables.
When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.
99. What are the dictionary tables used to monitor a database space?
DBA_FREE_SPACE
DBA_SEGMENTS
DBA_DATA_FILES.

SQL*Plus Statements

100. What are the types of SQL statement?
Data Definition Language: CREATE, ALTER, DROP, TRUNCATE, REVOKE, NO AUDIT & COMMIT.
Data Manipulation Language: INSERT, UPDATE, DELETE, LOCK TABLE, EXPLAIN PLAN & SELECT.
Transactional Control: COMMIT & ROLLBACK
Session Control: ALTERSESSION & SET ROLE
System Control: ALTER SYSTEM.
101. What is a transaction?
Transaction is logical unit between two commits and commit and rollback.
Read here More DBA Questions
102. What is difference between TRUNCATE & DELETE?
TRUNCATE commits after deleting entire table i.e., cannot be rolled back.
Database triggers do not fire on TRUNCATE
DELETE allows the filtered deletion. Deleted records can be rolled back or committed.
Database triggers fire on DELETE.
103. What is a join? Explain the different types of joins?
Join is a query, which retrieves related columns or rows from multiple tables.
Self Join – Joining the table with itself.
Equi Join – Joining two tables by equating two common columns.
Non-Equi Join – Joining two tables by equating two common columns.
Outer Join – Joining two tables in such a way that query can also retrieve rows that do not have corresponding join value in the other table.
104. What is the sub-query?
Sub-query is a query whose return values are used in filtering conditions of the main query.
105. What is correlated sub-query?
Correlated sub-query is a sub-query, which has reference to the main query.
106. Explain CONNECT BY PRIOR?
Retrieves rows in hierarchical order eg.
select empno, ename from emp where.
107. Difference between SUBSTR and INSTR?
INSTR (String1, String2 (n, (m)),
INSTR returns the position of the m-th occurrence of the string 2 in string1. The search begins from nth position of string1.
SUBSTR (String1 n, m)
SUBSTR returns a character string of size m in string1, starting from n-th position of string1.
108. Explain UNION, MINUS, UNION ALL and INTERSECT?
INTERSECT – returns all distinct rows selected by both queries.
MINUS – returns all distinct rows selected by the first query but not by the second.
UNION – returns all distinct rows selected by either query
UNION ALL – returns all rows selected by either query, including all duplicates.
109. What is ROWID?
ROWID is a pseudo column attached to each row of a table. It is 18 characters long, blockno, rownumber are the components of ROWID.
110. What is the fastest way of accessing a row in a table?
Using ROWID.

CONSTRAINTS

111. What is an integrity constraint?
Integrity constraint is a rule that restricts values to a column in a table.
112. What is referential integrity constraint?
Maintaining data integrity through a set of rules that restrict the values of one or more columns of the tables based on the values of primary key or unique key of the referenced table.
113. What is the usage of SAVEPOINTS?
SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed.
114. What is ON DELETE CASCADE?
When ON DELETE CASCADE is specified Oracle maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.
115. What are the data types allowed in a table?
CHAR, VARCHAR2, NUMBER, DATE, RAW, LONG and LONG RAW.
116. What is difference between CHAR and VARCHAR2? What is the maximum SIZE allowed for each type?
CHAR pads blank spaces to the maximum length.
VARCHAR2 does not pad blank spaces.
For CHAR the maximum length is 255 and 2000 for VARCHAR2.
117. How many LONG columns are allowed in a table? Is it possible to use LONG columns in WHERE clause or ORDER BY?
Only one LONG column is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.
118. What are the pre-requisites to modify datatype of a column and to add a column with NOT NULL constraint?
– To modify the datatype of a column the column must be empty.
– To add a column with NOT NULL constrain, the table must be empty.
119. Where the integrity constraints are stored in data dictionary?
The integrity constraints are stored in USER_CONSTRAINTS.
120. How will you activate/deactivate integrity constraints?
The integrity constraints can be enabled or disabled by ALTER TABLE ENABLE CONSTRAINT / DISABLE CONSTRAINT.
121. If unique key constraint on DATE column is created, will it validate the rows that are inserted with SYSDATE?
It won’t, Because SYSDATE format contains time attached with it.
122. What is a database link?
Database link is a named path through which a remote database can be accessed.
123. How to access the current value and next value from a sequence? Is it possible to access the current value in a session before accessing next value?
Sequence name CURRVAL, sequence name NEXTVAL. It is not possible. Only if you access next value in the session, current value can be accessed.
124. What is CYCLE/NO CYCLE in a Sequence?
CYCLE specifies that the sequence continue to generate values after reaching either maximum or minimum value. After pan-ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum.
NO CYCLE specifies that the sequence cannot generate more values after reaching its maximum or minimum value.
125. What are the advantages of VIEW?
– To protect some of the columns of a table from other users.
– To hide complexity of a query.
– To hide complexity of calculations.
126. Can a view be updated/inserted/deleted? If Yes – under what conditions?
A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.
More DBA Questions here in this blog
127. If a view on a single base table is manipulated will the changes be reflected on the base table?
If changes are made to the tables and these tables are the base tables of a view, then the changes will be reference on the view.

DBA Interview Questions : Backup & Recovery Related with examples

Step by Step Backup related questions for a Core DBA
Backup Questions
1) What is a backup? how many types of backups you can take in Oracle?
For any database to avoid the points of failures we need to take a backup. It can be a personal file or the database related.
In oracle there are two types of backup
1) User managed backup:-
Physical backup
Where User(DBA) will take manually by using OS commands cp
In USer managed backup there are again two types
Offline backup: (Cold backup)
Database shutdown
Datafiles,ControlFiles,Redologfiles,Temp Files,Undo Files, Archiveslogs, Pfile(spfile) and also pwd file
       How to take cold backup and which all files should be taken?
SQL> select file_name from dba_data_files
2  union all
3  select member from v$logfile
4  union all
5  select file_name from dba_temp_files
6  union all
7  select value from v$parameter where name=’control_files’
8  union all
9  select value from v$parameter where name=’spfile’
10  ;
FILE_NAME
——————————————————————————–
/u01/oradata/test/undotbs4.dbs
/u01/oradata/test/system.dbf
/u01/oradata/test/sysaux.dbf
/u01/oradata/test/undotbs1.dbf
/u01/oradata/test/undotbs2.dbf
/u01/oradata/test/undotbs3.dbf
/u01/oradata/test/redo05a.log
/u01/oradata/test/redo06a.log
/u01/oradata/test/redo07a.log
/u01/oradata/test/temp01.dbf
/u01/oradata/test/temp02.dbf
/u01/oradata/test/temp03.dbf
/u01/oradata/test/control01.ctl, /u01/oradata/test/control02.ctl, /u01/oradata
/test/control03.ctl
/u01/app/oracle/product/11.2.0/db_1/dbs/spfiletest.ora
14 rows selected.
Hot backup
Physical Backup
        Take backup of datafiles only when the database is up and running by using begin backup and end backup
SQL> alter database begin backup;
CP all afiles
SQL> alter database end bakcup;
Logical Backup:- (metadata create statement and insert statements)
Export/ – Database/Schema/Table
2) Oracle Managed Backup using RMAN:-
RMAN is the tool to take backup of database when the database is up and running, in order to take the database backup the database must be  in archivelog mode
It takes the backup as two types
1) Backup pieces: Reads the blocks and take the backups of used blocks only, the size of backups can be less than datafile/database
    2) Backup copies: Take the copy of the datafiles (image) as is, so the size of backup is same as datafile
2) What is the advantage of rman backup over user managed backup?
In user Managed backup the DBA has to manually take the records of the backup by issuing os cp command.
In Rman backup all the information of the backups will stores in control files.
2) What is Consistent hot backup? What ways you can take hot backup?
The backup will be taken when the database is up and running.Here we have to take backup of datafiles using CP command (oracle will not take)
by using
Option : 1
SQL>alter database begin backup;
CP datafiles;redolog,archives etc etc
SQL> alter database end backup
Another way is RMAN backup
RMAN> Connect target /
RMAN> backup database;
3) What is export backup?
Export backup is a logical backup which creates the statements in the dumpfile.(create table,alter table)
5) What is cold backup or offline backup, how you take it and what are all files to be included?
Cold backup is a consistent backup where we can take the back up while the database is down.Generally when the database is in the no archive log mode.
SQL>shut immediate;
cp all files
SQL>startup;
6) What is the advantage and disadvantage of having rman backup?
RMAN will update the control file about backup information and locations etc once the backup completes, DBA does not need to remember it unlike user managed recovery.
Only the disadvantage is table level recovery is not possible or its not pretty straight.
8) What is the prerequisite to take a rman backup?
The database must be in the archive log mode
9) How does rman backup will run?
we have to connect to database using  rman tool , rman will create a server session in the  database
10) How to take full database backup using rman?
rman target /
rman>backup database;
RMAN> backup database format disk ‘location'(specific location)
11) How many types of rman backups available? Differentiate them
Backup pieces and backup copies
These are al will be in the full backup,incremental or cummulative incremental
12) My database is generating archives hugely, how to take a backup and clean up?
Take archive backups periodically say 1 hour and delete them
rman>backup archivelog all delete input
13) Does controlfile and spfile includes in backup?
Yes
and the location of the files will be located in the by default at ORACLE_HOME/dbs directory
14) Does redolog files includes in rman backup?
No
becoz rman will take the archive log files rather than the redo log files.
15) What are all the files included in rman backup?
archive log files ,datafiles,control files,undo files and spfile
16) I want to speed up the rman backup what i can do?
Increase more backup channels
rman> configure device type disk parallelism 4;
17) Suppose i have taken archive backup at 6.00 AM and scheduled this job every hour, when taking backup at 7.00 AM the backup of archives generated 6.00AM also comes into 7.00AM? how to avoid this?
or
I dont want to backup archives duplicates as my job is scheduled every hour what i can do?
RMAN> configure backup optimization on;
Turning opitimize on will not take the backups to tape again if that backup is already there in the tape.
18) Suppose i have /u02/backup as my backup location and it is reached 90% , what the next step you take?
its depends on the retention, suppose u have 7 days retention, now u r in 8th day
RMAN> crosscheck backup;
RMAN> Report obsolete;
RMAN> Delete obsolete;
it will delete the the 7th day backup as its obsoleted now.
19) what are the types of export backups you can take
database/full
user/schema
object/table
20) Differentiate incremental/full/cumulative incremental backups
Full-backup of whole database
Incremental backup – The changes that happen from last full backup will be taken (or) The changes that contains after the full backup will take
Today Full backup
TOmorrow – incremental (contain only the changes that happens after full backup) so the size is very less.
Day after – incremental
Day aftet – cumulative increment(changes that happen after last full backup)so this backup contains two days changes(rather 1 day), this is use ful when recovery, recovery will be faster.
21) Whatis your companies backup strategy?
we have 2 strategies
logical back up via export , just in case if need of any table level recovery. though up to date recovery is not possible.
rman based backup every sunday we will take full backup and every day we will take incremental backup and every once in 4 hours we will take archive backup
22) What is retention?
This will defines,how many days that backup can be retained
23) what is redundancy?
How many copies of the same backup can be maintained
24) Differentiate retention and redundancy?
Various Recovery Scenarious
1) User managed recovery (in case of no rman backups only manual backups taken)
Database is in no archivelog mode = Incomplete recovery
a) Missing of system datafile
b) Missing of normal datafile
c) Missing of control file
d) missing of redolog file
e) Missing of undo file
g) Missing of temp file
Database is in archivelog mode and all archives exists until point of failure (Complete recovery)
    a) Missing of system datafile and sysaux and undo
Your database will crash (db is down)
copy the backup of failed file to the original location using OS CP
startup mount
recover datafile 1;
alter database open;
    b) Missing of normal datafile
User will get an application error
copy the backup file to the original location
make the datafile offline
recover the datafile`
make the datafile online
    c) Missing of control file (you have multiplexed controlfiles)
Though we have multiple  control files if once control file will lost then the DB will crash
copy of control file from one of the location where the control file exist to the failed location
startup mount
alter database open
    d) Missing of controlfile (backup exists 1 day before)
copy the backup of controlfile to the failed control file location
startup mount
recover database using backup controlfile
alter database open resetlogs;
    e) Missing of controlfile (no backup exists)
If you know the datafiles/redolog locations
Create the controlfile manually by keeping location
Create controfile reuse DBNAME
logfile group 1 ‘/u01/
datafile 1 ‘/u01/test/system01.dbf’
datafile 2 ‘/u11/test/sysaux01.dbf’
datafile 3 ‘/u09/test/user01.dbf’
datafile 4 ‘/u04/test/undo01.dbf';
SQL> startup nomount
SQL> run the above statement it will create the controlf file
SQL> recover database using backup controlfile;
SQL> alter database open resetlogs;
    d) missing of redolog file (backups are not applicable)
If the redo log file is current status (DB will crash)
If the redo log file is active (that means db will not crash since archiver keep retrying, you may lost that archives)
If the redo log file in inactive(means db will not crash, and there is no harm, since this redolog is already copied as archive)
All solutions should be add the redo log group in mount state.
depends on the status you will have to act accordingly.
SQL> alter database add logfile ‘location’ size 100m to group 5; example
    e) Missing of undo file (undo file belfongs to ? undo tablepsace ? undo image of block before it get modified)
SQL> create undo tablepsace undotbs2 datafle ‘location’ size 1000m ;
SQL> alter system set undo_tablespace=undotbs2 scope=spfile;
SQL> shut immeidate (if its up and running)
SQL> startup
Db will now pick new undo tablespace
g) Missing of temp file (DB will not crash , statements that use sorting will fail with temp not availble)
Create a temporary tablespace new
SQL> alter database default temporary tablespace temp3;
Database is in archivelog mode and all archives DOES NOT exists until point of failure (Incomplete recovery)
a) Missing of system datafile
b) Missing of normal datafile
c) Missing of control file
d) missing of redolog file
e) Missing of undo file
g) Missing of temp file
2) RMAN based recovery
Case : 1 (Latest backup exists and archives exists in backup) (Complete recovery)
a) Missing of system datafile
SQL> startup nomount
RMAN> restore datafile ‘/u01/test/system01.dbf';
SQL> alter database mount
SQL> recover datafile 1;
SQL> alter database open;
    b) Missing of normal datafile
RMAN> restore datafile ‘/u01/test/users01.dbf’
SQL> alter datafile ‘/u01/test/users01.dbf’ offline;
SQL> recover datafile ‘/u01/test/users01.dbf';
SQL> alter datafile ‘/u01/test/user01.dbf’ online;
    c) Missing of control file (no multiplex only rman backup exists)
RMAN> restore controfile from ‘backuplocation';
SQL> startup mount
SQL> recover database using backup controlfile;
SQL> alter database open resetlogs;
    d) missing of redolsog file
Not applicable, manual creation of redolog groups appropriately
e) Missing of undo file
Not applicable, ust create new undo tbs and assign to database
g) Missing of temp file
Not applicable, create new temp tablespace
Case : 2 (Latest backup exists but archives missing or corrupted) (Incomplete recovery)
    a) Missing of system datafile
Backup exists until 8.30 evening
Datfile missed at 8.30 morning
Archives available morning 6.30
Rest of the datafiles are available in locations
As you cannot restore or bring the system datafile until 8.30, you will have to take your entire database to 6.30 (upto archives avaibale)
If not the db is up and running , stop it
Delete all files, datafiles, controlfiles, redologs etc from OS location
Copy all the files datafiles,controlfiles,redologs from backup location to their respective locations
Startup mount
recover database until ‘2013-10-06 06:30:00′;
alternatively if you dont know time
recover database until cancel; (oracle read archives how many it can i.e 6.30 and then fails)
alter database open resetlogs;
b) Missing of normal datafile
Same as above,
c) Missing of control file
d) missing of redolog file
e) Missing of undo file
g) Missing of temp file
3) Block corruption  (Only a block in a file corrupted)
User Managed – block level not applicable
a)
Rman Managed
RMAN> recover block blocknum:
4) Table missing (one table is missing or dropped by mistake)
Export backup exists? Yes, you can import a single table from export dump, but the data is not until the table dropped, If no ask next
RMAN Backup Exists? Yes, You will need to create a seperate database and then log to that database, export that table and import in the old database to get that table. If no ask next
Flashback enabled? (10g onwards) Yes, If flashback is enabled and undo retention is good enough, you can just issue flashback table to before drop. If no ask next
Recycle bin on? (10g onwards) Yes, If no ask next

Oracle Recovery Cases – User Managed Recovery

User Managed Recovery Scenarios And Configuration

1. Complete Closed Database Recovery. System tablespace is missing

2. Complete Open Database Recovery. Non system tablespace is missing

3. Complete Open Database Recovery (when the database is initially closed). Non system tablespace is missing

4. Recovery of a Missing Datafile that has no backups.

5. Restore and Recovery of a Datafile to a different location.

6. Control File Recovery

7. Incomplete Recovery, Until Time/Sequence/Cancel

1. Complete Closed Database Recovery. System tablespace is missing

User managed recovery scenarios do require that the database is in archive log mode, and that backups of all datafiles and control files are made with the tablespaces set to begin backup, if the database is open while the

copy is made. At the end of the copy of each tablespace it is necessaire to take it out of backup mode. Alternatively complete backups can be made with the database shutdown. Online redologs can

be optionally backed up.

Files to be copied:

select name from v$datafile;

select member from v$logfile; # optional

select name from v$controlfile;

go up

Complete Closed Database Recovery. System tablespace is missing

If the system tablespace is missing or corrupted the database cannot be started up

so a complete closed database recovery must be performed.

Pre requisites: A closed or open database backup and archived logs.

1. Use OS commands to restore the missing or corrupted system datafile to its

original location, ie:

cp -p /user/backup/uman/system01.dbf

/user/oradata/u01/dbtst/system01.dbf

2. startup mount;

3. recover datafile 1;

4. alter database open;

2. Complete Open Database Recovery. Non system tablespace is missing

If a non system tablespace is missing or corrupted while the database is open, recovery can be performed while the database remain open.

Pre requisites: A closed or open database backup and archived logs.

1. Use OS commands to restore the missing or corrupted datafile to its original location, ie:

cp -p /user/backup/uman/user01.dbf /user/oradata/u01/dbtst/user01.dbf

2. alter tablespace <tablespace_name> offline immediate;

3. recover tablespace <tablespace_name>;

4. alter tablespace <tablespace_name> online;

3. Complete Open Database Recovery (when the database is initially closed).

Non system tablespace is missing

If a non system tablespace is missing or corrupted and the database crashed, recovery can be performed after the database is open.

Pre requisites: A closed or open database backup and archived logs.

1. startup; (you will get ora-1157 ora-1110 and the name of the missing datafile, the database will remain mounted)

2. Use OS commands to restore the missing or corrupted datafile to its original location, ie:

cp -p /user/backup/uman/user01.dbf /user/oradata/u01/dbtst/user01.dbf

alter database datafile3 offline; (tablespace cannot be used because the database is not open)

3. alter database open;

4. recover datafile 3;

5. alter tablespace <tablespace_name> online;

4. Recovery of a Missing Datafile that has no backups (database is open).

If a non system datafile that was not backed up since the last backup is missing,

recovery can be performed if all archived logs since the creation

of the missing datafile exist.

Pre requisites: All relevant archived logs.

1. alter tablespace <tablespace_name> offline immediate;

2. alter database create datafile ‘/user/oradata/u01/dbtst/newdata01.dbf';

3. recover tablespace <tablespace_name>;

4. alter tablespace <tablespace_name> online;

If the create datafile command needs to be executed to place the datafile on a location different than the original use:

alter database create datafile ‘/user/oradata/u01/dbtst/newdata01.dbf’ as ‘/user/oradata/u02/dbtst/newdata01.dbf’

5. Restore and Recovery of a Datafile to a different location.

If a non system datafile is missing and its original location not available, restore

can be made to a different location and recovery performed.

Pre requisites: All relevant archived logs.

1. Use OS commands to restore the missing or corrupted datafile to the new location, ie:

cp -p /user/backup/uman/user01.dbf /user/oradata/u02/dbtst/user01.dbf

2. alter tablespace <tablespace_name> offline immediate;

3. alter tablespace <tablespace_name> rename datafile ‘/user/oradata/u01/dbtst/user01.dbf’ to ‘/user/oradata/u02/dbtst/user01.dbf';

4. recover tablespace <tablespace_name>;

5.    alter tablespace <tablespace_name> online;

6. Control File Recovery

Always multiplex your controlfiles. Controlfiles are missing, database crash.

Pre requisites: A backup of your controlfile and all relevant archived logs.

startup; (you get ora-205, missing controlfile, instance start but database is not

mounted)

1. Use OS commands to restore the missing controlfile to its original location:

cp -p /user/backup/uman/control01.dbf /user/oradata/u01/dbtst/control01.dbf

cp -p /user/backup/uman/control02.dbf /user/oradata/u01/dbtst/control02.dbf

2. alter database mount;

3. recover automatic database using backup controlfile;

4. alter database open resetlogs;

make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.

7. Incomplete Recovery, Until Time/Sequence/Cancel

Incomplete recovery may be necessaire when an archived log is missing, so recovery can only be made until the previous sequence, or when an important object was dropped, and recovery needs to be made until

before the object was dropped.

Pre requisites: A closed or open database backup and archived logs, the time or sequence that the ‘until’ recovery needs to be performed.

1. If the database is open, shutdown abort

Use OS commands to restore all datafiles to its original locations:

cp -p /user/backup/uman/u01/*.dbf /user/oradata/u01/dbtst/

cp -p /user/backup/uman/u02/*.dbf /user/oradata/u01/dbtst/

cp -p /user/backup/uman/u03/*.dbf /user/oradata/u01/dbtst/

cp -p /user/backup/uman/u04/*.dbf /user/oradata/u01/dbtst/

etc…

2. startup mount;

3. recover automatic database until time ‘2004-03-31:14:40:45′;

4. alter database open resetlogs;

make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.

Alternatively you may use instead of until time, until sequence or until cancel:

SQL> recover automatic database until sequence 120 thread 1; OR

SQL> recover database until cancel;

5. SQL> alter database open;

Oracle Recovery Cases – Using RMAN

Rman Recovery Scenarios

Rman recovery scenarios require that the database is in archive log mode, and that backups of datafiles, control files and archived redolog files are made using Rman. Incremental Rman backups may be used also.

Rman can be used with the repository installed on the archivelog, or with a recovery catalog that may be installed in the same or other database.

Following are the maximum cases for RMAN based recovery.

1. Complete Closed Database Recovery. System tablespace is missing

2. Complete Open Database Recovery. Non system tablespace is missing

3. Complete Open Database Recovery (when the database is initially closed). Non

system tablespace is missing

4. Recovery of a Datafile that has no backups.

5. Restore and Recovery of a Datafile to a different location.

6. Control File Recovery

7. Incomplete Recovery, Until Time/Sequence/Cancel

RMAN Configuration and operation recommendations:

Set the parameter controlfile autobackup to ON to have with each backup a controlfile backup also:

configure controlfile autobackup on;

Set the parameter retention policy to the recovery window you want to have ie redundancy 2 will keep the last two backups

available, after executing delete obsolete commands:

configure retention policy to redundancy 2;

Execute your full backups with the option ‘plus archivelogs’ to include your archivelogs with every backup:

backup database plus archivelog;

Perform daily maintenance routines to maintain on your backup directory the number of backups you need only:

crosscheck backup;

crosscheck archivelog all;

delete noprompt obsolete backup;

Creating RMAN Catalog

1. sqlplus /

2. create tablespace repcat;

3. create user rcuser identified by rcuser default tablespace repcat temporary tablespace temp;

4. grant connect, resource, recovery_catalog_owner to rcuser

5. exit

6. rman catalog rcuser/rcuser

# connect to rman catalog as the rcuser

7. create catalog

# create the catalog

8. connect target /

Complete Closed Database Recovery. System tablespace is missing

In this case complete recovery is performed, only the system tablespace is missing,

so the database can be opened without reseting

the redologs.

1. rman target /

2. startup mount;

3. restore database;

4. recover database;

5. alter database open;

Complete Open Database Recovery. Non system tablespace is missing, database is up

1. 1. rman target /

2. sql ‘alter tablespace <tablespace_name> offline immediate';

3. restore datafile 3;

4. recover datafile 3;

5. sql ‘alter tablespace <tablespace_name> online';

Complete Open Database Recovery (when the database is initially closed).

Non system tablespace is missing

A user datafile is reported missing when tryin to startup the database. The datafile can be turned offline and the database started up. Restore and recovery are performed using Rman. After recovery is performed the datafile can

be turned online again.

1. sqlplus /nolog

2. connect / as sysdba

3. startup mount

4. alter database datafile ‘<datafile_name>’ offline;

5. alter database open;

exit;

1. rman target /

2. restore datafile ‘<datafile_name>';

3. recover datafile ‘<datafile_name>';

4. sql ‘alter tablespace <tablespace_name> online';

Recovery of a Datafile that has no backups (database is up).

If a non system datafile that was not backed up since the last backup is missing, recovery can be performed if all archived logs since the creation of the missing datafile exist. Since the database is up you can check the tablespace name and put it offline. The option offline immediate is used to avoid that the update of the datafile header.

Pre requisites: All relevant archived logs.

1. sqlplus ‘/ as sysdba’

2. alter tablespace <tablespace_name> offline immediate;

3. alter database create datafile ‘/user/oradata/u01/dbtst/newdata01.dbf;

exit

1. rman target /

2. recover tablespace <tablespace_name>;

3. sql ‘alter tablespace <tablespace_name> online';

If the create datafile command needs to be executed to place the datafile on a location different than the original use:

SQL> alter database create datafile ‘/user/oradata/u01/dbtst/newdata01.dbf’ as ‘/user/oradata/u02/dbtst/newdata01.dbf’

Restore and Recovery of a Datafile to a different location. Database is up.

If a non system datafile is missing and its original location not available, restore can be made to a different location and recovery performed.

Pre requisites: All relevant archived logs, complete cold or hot backup.

Use OS commands to restore the missing or corrupted datafile to the new location, ie:

1. cp -p /user/backup/uman/user01.dbf /user/oradata/u02/dbtst/user01.dbf

2. alter tablespace <tablespace_name> offline immediate;

3. alter tablespace <tablespace_name> rename datafile ‘/user/oradata/u01/dbtst/user01.dbf’ to ‘/user/oradata/u02/dbtst/user01.dbf';

4. rman target /

5. recover tablespace <tablespace_name>;

6. sql ‘alter tablespace <tablespace_name> online';

Control File Recovery

Always multiplex your controlfiles. If you loose only one controlfile you can replace it with the one you have in place, and startup the Database. If both controlfiles are missing, the database will crash.

Pre requisites: A backup of your controlfile and all relevant archived logs. When using Rman alway set configuration parameter autobackup of controlfile to ON. You will need the dbid to restore the controlfile, get it from the name of the backed up controlfile. It is the number following the ‘c-‘ at the start of the name.

1. rman target /

2. set dbid <dbid#>

3. startup nomount;

4. restore controlfile from autobackup;

5. alter database mount;

6. recover database;

7. alter database open resetlogs;

make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.

Incomplete Recovery, Until Time/Sequence/Cancel

Incomplete recovery may be necessaire when the database crash and needs to be recovered, and in the recovery process you find that an archived log is missing. In this case recovery can only be made until the sequence before the one that is missing.

Another scenario for incomplete recovery occurs when an important object was dropped or incorrect data was committed on it. In this case recovery needs to be performed until before the object was dropped.

Pre requisites: A full closed or open database backup and archived logs, the time or sequence that the ‘until’ recovery needs to be performed.

If the database is open, shutdown it to perform full restore.

1. rman target

2. startup mount;

3. restore database;

4. recover database until sequence 8 thread 1; # you must pass the thread, if a single instance will always be 1.

5. alter database open resetlogs;

make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.

Alternatively you may use instead of until sequence, until time, ie: ‘2004-12- 28:01:01:10

MID LEVEL ORACLE DBA INTERVIEW QUESTIONS

Question 1: what is difference between Co-related sub query and nested sub query?
Answer:
Co-related sub query is one in which inner query is evaluated only once and from that result outer
query is evaluated.
Nested query is one in which Inner query is evaluated for multiple times for getting one row of that
outer query.
ex. Query used with IN() clause is Co-related query.
Query used with = operator is Nested query
Question 2: How do I write a cron which will run a SQL query and mail the results to a
group?
Answer:
Use DBMS_JOB for scheduling a cron job and DBMS_MAIL to send the results throught email.
Question 3: What operator performs pattern matching
Answer:
LIKE operator
Question 4: What is the use of the DROP option in the ALTER TABLE command
Answer:
It is used to drop constraints specified on the table.
Question 5: Which date function is used to find the difference between two dates
Answer:
MONTHS_BETWEEN
Question 6: What is the advantage of specifying WITH GRANT OPTION in the GRANT
command?
Answer:
The privilege receiver can further grant the privileges he/she has obtained from the owner to any
other user.
Question 7: What is the use of CASCADE CONSTRAINTS
Answer:
When this clause is used with the DROP command, a parent table can be dropped even when a
child table exists.
Question 8: Which function is used to find the largest integer less than or equal to a
specific value


Answer:
FLOOR
Question 9: What operator tests column for the absence of data
Answer:
IS NULL operator
Question 10: What are the steps involved in Database Startup?
Answer:
Start an instance, Mount the Database and Open the Database.
Question 11: What are the steps involved in Database Shutdown?
Answer:
Close the Database; Dismount the Database and Shutdown the Instance.
Question 12: What is Restricted Mode of Instance Startup?
Answer:
An instance can be started in (or later altered to be in) restricted mode so that when the
database is open connections are limited only to those whose user accounts have been
granted the RESTRICTED SESSION system privilege.
Question 13: What are the different modes of mounting a Database with the Parallel
Server?
Answer:
Exclusive Mode If the first instance that mounts a database does so in exclusive mode, only
that Instance can mount the database.
Parallel Mode If the first instance that mounts a database is started in parallel mode, other
instances that are started in parallel mode can also mount the database.
Question 14: What is Full Backup?
Answer:
A full backup is an operating system backup of all data files, on-line redo log files and control
file that constitute ORACLE database and the parameter.
Question 15: Can Full Backup be performed when the database is open?
Answer:
No.
Question 16: What is Partial Backup?


Answer:
A Partial Backup is any operating system backup short of a full backup, taken while the
database is open or shut down.
Question 17: 144. What is On-line Redo Log?
Answer:
The On-line Redo Log is a set of tow or more on-line redo files that record all committed changes
made to the database. Whenever a transaction is committed, the corresponding redo entries
temporarily stores in redo log buffers of the SGA are written to an on-line redo log file by
the background process LGWR. The on-line redo log files are used in cyclical fashion.
Question 18: What is Mirrored on-line Redo Log?
Answer:
A mirrored on-line redo log consists of copies of on-line redo log files physically located on
separate disks; changes made to one member of the group are made to all members.
Question 19: What is Archived Redo Log?
Answer:
Archived Redo Log consists of Redo Log files that have archived before being reused.
Question 20: What are the advantages of operating a database in ARCHIVELOG mode
over operating it in NO ARCHIVELOG mode?
Answer:
Complete database recovery from disk failure is possible only in ARCHIVELOG mode.
Online database backup is possible only in ARCHIVELOG mode.
Question 21: What is Log Switch?
Answer:
The point at which ORACLE ends writing to one online redo log file and begins writing to another
is called a log switch.
Question 22: What are the steps involved in Instance Recovery?
Answer:
Rolling forward to recover data that has not been recorded in data files, yet has been recorded
in the on-line redo log, including the contents of rollback segments.
Rolling back transactions that have been explicitly rolled back or have not been committed as
indicated by the rollback segments regenerated in step a.
Releasing any resources (locks) held by transactions in process at the time of the failure.
Resolving any pending distributed transactions undergoing a two-phase commit at the time of
the instance failure.


Question 23: Do you need a commit after DDL statements?
Answer:
DDL IS A AUTO COMMIT YOU DON T NEED TO COMMIT AGAIN
Question 24: How would you pass hints to the SQL processor?
Answer:
USING COMMENT LINES WITH (+) SIGN YOU CAN PASS THE HINTS TO SQL ENGINE
For example /* +PARALLEL( ) */
Question 25: what is the difference between group by and order by?
Answer:
Group by is used when we use aggregate functions on the columns in a query the other columns
should be in group by query
eg:
select empno,ename,sum(sal) from emp
group by empno,ename
Order by is used to sort values either in ascending or descending order
Question 26: What are the Large object types supported by Oracle?
Answer:
BLOB
CLOB
NLOB
These usually support data up to 4GB
Question 27: What are the Characteristics of Data Files?
Answer:
A data file can be associated with only one database. Once created a data file can't change size.
One or more data files form a logical unit of database storage called a tablespace
Question 28: What is the difference between SID and Global Database Name?
Answer: SID (System Identifier) : A SID (almost) uniquely identifies an instance. Actually,
$ORACLE_HOME, $ORACLE_SID and $HOSTNAME identify an instance uniquely. The SID is
64 characters, or less; at least on Oracle 9i. The system identifier is included in the
CONNECT_DATA parts of the connect descriptors in a tnsnames.ora file. The SID defaults to the
database name.
Global Database Name : A database is uniquely identified by a global database name. Usually, a
global database name has the form somename.domain. The global database name is the
composit of db_domain and db_name.
Question 29: What is the difference between materialized view and snapshot?


Answer:
A materialized view is a replica of a target master from a single point in time. The concept was
first introduced with Oracle7 termed as SNAPSHOT. In Oracle release 7.1.6 snapshots were
enhanced to enable DMLs along with a new terminology, updatable snapshots. With Oracle8.1.6
snapshots started to be used in data warehouse environments so anew terminology materialized
view was introduced to address both distributed and data warehouse materialized views.
Question 30: What are the components of Physical database structure of Oracle
Database?
Answer:
ORACLE database is comprised of three types of files. One or more Data files, two are more
Redo Log files, and one or more Control files.
Question 31: What is a Temporary Segment?
Answer:
Temporary segments are created by ORACLE when a SQL statement needs a temporary work
area to complete execution. When the statement finishes execution, the temporary segment
extents are released to the system for future use.
Question 32: What is a Data File?
Answer:
Every ORACLE database has one or more physical data files. A database's data files contain all
the database data. The data of logical database structures such as tables and indexes is
physically stored in the data files allocated for a database.
Question 33: What is a Redo Log?
Answer:
The set of Redo Log files for a database is collectively known as the database's redo log.
Question 34: What is the function of Redo Log?
Answer:
The Primary function of the redo log is to record all changes made to data.
Question 35: What is the use of Redo Log Information?
Answer:
The Information in a redo log file is used only to recover the database from a system or media
failure prevents database data from being written to a database's data files.
Question 36: What does a Control file Contain?
Answer:
A Control file records the physical structure of the database. It contains the following information.
Database Name
Names and locations of a database's files and redo log files.


Time stamp of database creation.
Question 37: What is the use of Control File?
Answer:
When an instance of an ORACLE database is started, its control file is used to identify the
database and redo log files that must be opened for database operation to proceed. It is also
used in database recovery.
Question 38: What is a Data Dictionary?
Answer:
The data dictionary of an ORACLE database is a set of tables and views that are used as a read-
only reference about the database.
It stores information about both the logical and physical structure of the database, the valid users
of an ORACLE database, integrity constraints defined for tables in the database and space
allocated for a schema object and how much of it is being used.
Question 39: What is an Integrity Constrains?
Answer:
An integrity constraint is a declarative way to define a business rule for a column of a table.
Question 40: Describe the different type of Integrity Constraints supported by ORACLE?
Answer:
NOT NULL Constraint - Disallows Nulls in a table's column.
UNIQUE Constraint - Disallows duplicate values in a column or set of columns.
PRIMARY KEY Constraint - Disallows duplicate values and Nulls in a column or set of columns.
FOREIGN KEY Constrain - Require each value in a column or set of columns match a value in a
related table's UNIQUE or PRIMARY KEY.
CHECK Constraint - Disallows values that do not satisfy the logical expression of the constraint.
Question 41: What is difference between UNIQUE constraint and PRIMARY KEY
constraint?
Answer:
A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can't
contain Nulls.
Question 42: Describe Referential Integrity?
Answer:
A rule defined on a column (or set of columns) in one table that allows the insert or update of a
row only if the value for the column or set of columns (the dependent value) matches a value in a
column of a related table (the referenced value). It also specifies the type of data manipulation
allowed on referenced data and the action to be performed on dependent data as a result of any
action on referenced data.


Question 43: What are the Referential actions supported by FOREIGN KEY integrity
constraint?
Answer:
UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of
referenced data.
DELETE Cascade - When a referenced row is deleted all associated dependent rows are deleted
Question 44: What is a Tablespace?
Answer:
A database is divided into Logical Storage Unit called tablespace. A tablespace is used to
grouped related logical structures together.
Question 45: What is SYSTEM tablespace and when is it Created?
Answer:
Every ORACLE database contains a tablespace named SYSTEM, which is automatically created
when the database is created. The SYSTEM tablespace always contains the data dictionary
tables for the entire database.
Question 46: Explain the relationship among Database, Tablespace and Data file.
Answer:
Each databases logically divided into one or more tablespaces. One or more data files are
explicitly created for each tablespace.
Question 47: What are synonyms used for?
Answer:
Synonyms are used to:
Mask the real name and owner of an object.
Provide public access to an object
Provide location transparency for tables, views or program units of a remote database.
Simplify the SQL statements for database users.
Question 48: What are Clusters?
Answer:
Clusters are groups of one or more tables physically stores together to share common columns
and are often used together.
Question 49: When can Hash Cluster used?
Answer:
Hash clusters are better choice when a table is often queried with equality queries. For such
queries the specified cluster key value is hashed. The resulting hash key value points directly to
the area on disk that stores the specified rows.


Question 50: What is Row Chaining?
Answer:
In Circumstances, all of the data for a row in a table may not be able to fit in the same data block.
When this occurs, the data for the row is stored in a chain of data block (one or more) reserved
for that segment.
Question 51: What is Data Concurrency and Consistency?
Answer:
Data Concurrency => Means that many users can access data at the same time.
Data Consistency => Means that each user sees a consistent view of the data, including visible
changes made by the user's own transactions and transactions of other users.
Question 52: What is Overloading of procedures?
Answer:
The Same procedure name is repeated with parameters of different datatypes and parameters in
different positions, varying number of parameters is called overloading of procedures.
e.g. DBMS_OUTPUT.put_line
Question 53: What are % TYPE and % ROWTYPE ? What are the advantages of using
these over datatypes?
Answer:
% TYPE provides the data type of a variable or a database column to that variable.
% ROWTYPE provides the record type that represents a entire row of a table or view or columns
selected in the cursor.
The advantages are: You don t need to know about variable's data type
ii. If the database definition of a column in a table changes, the data type of a variable changes
accordingly.
Question 54: Explain the two type of Cursors?
Answer:
There are two types of cursors, Implicit Cursor and Explicit Cursor.
PL/SQL uses Implicit Cursors for queries.
User defined cursors are called Explicit Cursors. They can be declared and used.
Question 55: What are the PL/SQL Statements used in cursor processing?
Answer:
DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record
types, CLOSE cursor name
Question 56: What is a database trigger? Name some usages of database trigger?


Answer:
Database trigger is stored PL/SQL program unit associated with a specific database table.
Usages are Audit data modifications, Log events transparently, Enforce complex
business rules Derive column values automatically, Implement complex security
authorizations. Maintain replicate tables.
Question 57: What are the return values of functions SQLCODE and SQLERRM?
Answer:
SQLCODE returns the latest code of the error that has occurred.
SQLERRM returns the relevant error message of the SQLCODE.
Question 58: Explain UNION, MINUS, UNION ALL and INTERSECT
Answer:
UNION - the values of the first query are returned with the values of the second query eliminating
duplicates.
MINUS - the values of the first query are returned with duplicates values of the second query
removed from the first query.
UNION ALL - the values of both queries are returned including all duplicates
Question 59: How do I eliminate the duplicate rows?
Answer:
Delete from emp a where a.rowid > ( select min(rowid) from emp b
where a.empno = b.empno group by empno)
Question 60: Explain CONNECT BY PRIOR TO
Answer:
The start with .. connect by clause can be used to select data that has a hierarchical relationship
(usually some sort of parent->child (boss->employee or thing->parts).
Question 61: What is correlated sub-query?
Answer: A correlated sub-query is a subquery that references the value/s from the main query.
Question 62: What is the use of SAVEPOINTS?
Answer: SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling
back part of a transaction. Maximum of five save points are allowed
Question 63: What is normalization? What is the advantage of normalization (briefly)
Answer: Database normalization is a series of steps followed to obtain a database design that
allows for consistent storage and efficient access of data in a relational database .These steps
reduce data redundancy and the risk of data becoming inconsistent


Question 64: What are the components of physical database structure of Oracle database
Answer: ORACLE database is comprised of three types of files. One or more Data files, two or
more Redo Log files, and one or more Control files.
Question 65: What are different types of segments?
Answer:
temp segment,
undo segment,
table segment,
index segment
Question 66: Can you name few DBMS packages and their use?
Answer:
DBMS_OUTPUT.PUT_LINE('strings');
print out the strings
DBMS_UTILITY.get_time()
get the current time
DBMS_JOBS
to schedule jobs in database
DBMS_STATS
To gather database statistics
Question 67: How can you determine the size of the database?
Answer:
You can query dba_data_files and dba_temp_files
Question 68: How do you find whether the instance was started with pfile or spfile
Answer:
There are 3 different ways :-
1) SELECT name, value FROM v$parameter WHERE name = 'SPFILE'; //This query will return
NULL if you are using PFILE
2) SHOW PARAMETER spfile // This query will returns NULL in the value column if you are using
pfile and not spfile
3) SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL; // if the count is non-
zero then the instance is using a spfile, and if the count is zero then it is using a pfile:
By Default oracle will look into the default location depends on the o/s. Like in unix, oracle will
check in $oracle_home/dbs directory and on windows it will check in oracle_home/database
directory, and the content of pfile is just text based, but spfile content is in binary format, that is
understandable by oracle very well.
Also oracle server always checks the spfile or pfile with these sequences:-


SPFILE<SID>.ORA
SPFILE.ORA
PFILE<SID>.ORA
PFILE.ORA
Question 69: How do you pin an object.
Answer: Use dbms_shared_pool procedure.
EXECUTE DBMS_SHARED_POOL.KEEP(OBJECTNAME);
Question 70: What is the use of COMPRESS option in EXP command?
Answer:
Flag to indicate whether export should compress fragmented segments into single extents.
Question 71: How can you find out within a PL/SQL block if a CURSOR is open or not
Answer: By using %ISOPEN cursor status variable
Question 72: Explain three methods of transferring a table between two schema
Answer:
EXPORT-IMPORT
CREATE TABLE .. AS SELECT
COPY command

SENIOR ORACLE DBA INTERVIEW QUESTIONS


Question 1. Explain the difference between a hot backup and a cold backup and the benefits
associated with each.
Answer:
A hot backup is basically taking a backup of the database while it is still up and running and it must be in
archive log mode. A cold backup is taking a backup of the database while it is shut down and does not
require being in archive log mode. The benefit of taking a hot backup is that the database is still available
for use while the backup is occurring and you can recover the database to any point in time. The benefit
of taking a cold backup is that it is typically easier to administer the backup and recovery process. In
addition, since you are taking cold backups the database does not require being in archive log mode and
thus there will be a slight performance gain as the database is not cutting archive logs to disk.
Question 2. You have just had to restore from backup and do not have any control files. How
would you go about bringing up this database?
Answer:
I would create a text based backup control file, stipulating where on disk all the data files where and then
issue the recover command with the using backup control file clause.
Question 3. How do you switch from an init.ora file to a spfile?
Answer:
Issue the create spfile from pfile command.
Question 4. Explain the difference between a data block, an extent and a segment.
Answer:
A data block is the smallest unit of logical storage for a database object. As objects grow they take
chunks of additional storage that are composed of contiguous data blocks. These groupings of
contiguous data blocks are called extents. All the extents that an object takes when grouped together are
considered the segment of the database object.
Question 5. Give two examples of how you might determine the structure of the table DEPT.
Answer:
Use the describe command or use the dbms_metadata.get_ddl package.
Question 6. Where would you look for errors from the database engine?
Answer:
In the alert log.


Question 7. Compare and contrast TRUNCATE and DELETE for a table.
Answer:
Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table.
The difference between the two is that the truncate command is a DDL operation and just moves the high
water mark and produces a now rollback. The delete command, on the other hand, is a DML operation,
which will produce a rollback and thus take longer to complete.
Question 8. Give the reasoning behind using an index.
Answer:
Faster access to data blocks in a table.
Question 9. Give the two types of tables involved in producing a star schema and the type of data
they hold.
Answer:
Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain
data that will help describe the fact tables.
Question 10. What type of index should you use on a fact table?
Answer:
A Bitmap index.
Question 11. Give two examples of referential integrity constraints.
Answer:
A primary key and a foreign key.
Question 12. A table is classified as a parent table and you want to drop and re-create it. How
would you do this without affecting the children tables?
Answer:
Disable the foreign key constraint to the parent, drop the table, re-create the table, and enable the foreign
key constraint.
Question 13. Explain the difference between ARCHIVELOG mode and NOARCHIVELOG
mode and the benefits and disadvantages to each.
Answer:
ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions
that have occurred in the database so that you can recover to any point in time. NOARCHIVELOG mode
is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to
any point in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to
an archive log and thus increases the performance of the database slightly.
Question 14. What command would you use to create a backup control file?
Answer:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE


Question 15. Give the stages of instance startup to a usable state where normal users may access
it.
Answer:
     STARTUP NOMOUNT - Instance startup
     ALTER DATABASE MOUNT - The database is mounted
     ALTER DATABASE OPEN - The database is opened
Question 16. What column differentiates the V$ views to the GV$ views and how?
Answer:
The INST_ID column which indicates the instance in a RAC environment the information came from.
Question 17. How would you go about generating an EXPLAIN plan?
Answer:
Create a plan table with utlxplan.sql.
Use the explain plan set statement_id = 'tst1' into plan_table for a SQL statement
Look at the explain plan with utlxplp.sql or utlxpls.sql
Question 18. How would you go about increasing the buffer cache hit ratio?
Answer:
Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a
change was necessary then I would use the alter system set db_cache_size command.
Question 19. Explain an ORA-01555
Answer:
You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing
the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the
application getting the error message.
Question 20. Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
Answer:
ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is
where the oracle products reside.
Question 21. How would you determine the time zone under which a database was operating?
Answer:
select DBTIMEZONE from dual;
Question 22. Explain the use of setting GLOBAL_NAMES equal to TRUE.
Answer:
Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or
FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database
to which they are linking.


Question 23. What command would you use to encrypt a PL/SQL application?
Answer:
WRAP
Question 24. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
Answer:
A function and procedure are the same in that they are intended to be a collection of PL/SQL code that
carries a single task. While a procedure does not have to return any values to the calling application, a
function will return a single value. A package on the other hand is a collection of functions and procedures
that are grouped together based on their commonality to a business function or application.
Question 25. Explain the use of table functions.
Answer:
Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as
a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.
Question 26. Name three advisory statistics you can collect.
Answer:
Buffer Cache Advice, Segment Level Statistics, & Timed Statistics
Question 27. Where in the Oracle directory tree structure are audit traces placed?
Answer:
In unix $ORACLE_HOME/rdbms/audit,
in Windows the event viewer
Question 28. Explain materialized views and how they are used.
Answer:
Materialized views are objects that are reduced sets of information that have been summarized, grouped,
or aggregated from base tables. They are typically used in data warehouse or decision support systems.
Question 29. When a user process fails, what background process cleans up after it?
Answer:
PMON
Question 30. What background process refreshes materialized views?
Answer:
The Job Queue Processes.
Question 31. How would you determine what sessions are connected and what resources they
are waiting for?
Answer:


Use of V$SESSION and V$SESSION_WAIT
Question Question 32. Describe what redo logs are.
Answer:
Redo logs are logical and physical structures that are designed to hold all the changes made to a
database and are intended to aid in the recovery of a database.
Question 33. How would you force a log switch?
Answer:
ALTER SYSTEM SWITCH LOGFILE;
Question 34. Give two methods you could use to determine what DDL changes have been made.
Answer:
You could use Logminer or Streams
Question 35. What does coalescing a tablespace do?
Answer:
Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining
neighboring free extents into large single extents.
Question 36. What is the difference between a TEMPORARY tablespace and a PERMANENT
tablespace?
Answer:
A temporary tablespace is used for temporary objects such as sort structures while permanent
tablespaces are used to store those objects meant to be used as the true objects of the database.
Question 37. Name a tablespace automatically created when you create a database.
Answer:
The SYSTEM tablespace.
Question 38. When creating a user, what permissions must you grant to allow them to
connect to the database?
Answer:
Grant the CONNECT to the user.
Question 39. How do you add a data file to a tablespace?
Answer:
ALTER TABLESPACE <tablespace_name> ADD DATAFILE <datafile_name> SIZE <size>
Question 40. How do you resize a data file?
Answer:


ALTER DATABASE DATAFILE <datafile_name> RESIZE <new_size>;
Question 41. What view would you use to look at the size of a data file?
Answer:
DBA_DATA_FILES
Question 42. What view would you use to determine free space in a tablespace?
Answer:
DBA_FREE_SPACE
Question 43. How would you determine who has added a row to a table?
Answer:
Turn on fine grain auditing for the table.
Question 44. How can you rebuild an index?
Answer:
ALTER INDEX <index_name> REBUILD ONLINE;
Question 45. Explain Partitioning and benefits of using Partioning.
Answer:
Partitioning is a method of taking large tables and indexes and splitting them into smaller, more
manageable pieces.
Question 46. You have just compiled a PL/SQL package but got errors, how would you view the
errors?
Answer:
SHOW ERRORS
Question 47. How can you gather statistics on a table?
Answer:
The ANALYZE command or DBMS_STAT package.
Question 48. How can you enable a trace for a session?
Answer:
Use the DBMS_SESSION.SET_SQL_TRACE or Use
ALTER SESSION SET SQL_TRACE = TRUE;
Question 49. What is the difference between the SQL*Loader and IMPORT utilities?
Answer:
These two Oracle utilities are used for loading data into the database. The difference is that the import
utility relies on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility


allows data to be loaded that has been produced by other utilities from different data sources just so long
as it conforms to ASCII formatted or delimited files.
Question 50. Name two files used for network connection to a database.
Answer:
TNSNAMES.ORA and SQLNET.ORA
Question 51. Explain difference between Oracle Database and Instance
Answer:
Oracle database consist of following:
      Datafiles, Control Files, Redo Log Files
Oracle instance consist of following:
        SGA and Oracle processes (DBWR, LGWR, SMON, PMON, ARCH, CKPT, etc.)
Question 52. What is Mutating Table error?
Answer:
Mutating table error happens when you try to modify the same row you are using it. Use temp table to
avoid this error.
Question 53. What is correlated sub-query?
Answer:
Correlated sub-query is a sub-query, which has reference to the main query
Question 54. Which one of the below change will decrease PAGING/SWAPPING
Answer:
   1. INCREASE SORT_AREA_RETAINED_SIZE
   2. DECREASE SHARED_POOL_SIZE (correct answer)
   3. DECREASE OPEN_CURSORS parameters
   4. INCREASE DB_CACHE_SIZE
Question 55. Explain steps involved in Migration from Oracle 9.0.1 to 9.2.0.6
Answer:
   1. Shutdown 9.0.1 database
   2. Apply software patch to new/old ORACLE_HOME
   3. Change ORACLE_HOME to point to new 9.2.0.6 binaries
   4. STARTUP MIGRATE
   5. @ORACLE_HOME/rdbms/admin/catpatch.sql
   6. @ORACLE_HOME/rdbms/admin/utlrp.sql
   7. Change init.ora parameter COMPATIBLE = 9.2.0.6
   8. Shutdown and startup normal
Question 56. Can you create database under any Unix userid besides oracle?
Answer:
Yes. You can create database user other then oracle as long as that user is part of DBA group in unix


Question 57. Locally Managed TEMP Tablespace is 100% FULL and there is no space available to
add datafile to increase TEMP tablespace. What can you do that might free up TEMP space?
Answer:
You can try one of the following to free up TEMP space
   1. Issue ALTTER TABLESPACE PCTINCREASE 1 followed by
       ALTTER TABLESPACE PCTINCREASE 0 command;
   2. Close some of the idle sessions connected to the database
Question 58. List five most important features of Oracle 9i
Answer:
   1. AUTOMATIC UNDO MANAGEMEND
   2. AUTOMATICE PGA MEMORY MANAGEMENT
   3. MULTI-TABLE INSERT statements
   4. EXTERNAL TABLES
   5. DYNAMIC MEMORY MANAGEMENT
Question 59. Name five top 9I init.ora parameters affecting performance
Answer:
   1. CURSOR_SHARING
   2. DB_CACHE_SIZE
   3. PGA_AGGREGATE_TARGET
   4. WORKAREA_SIZE_POLICY
   5. DB_16K_CACHE_SIZE, DB_8K_CACHE_SIZE, DB_2K_CACHE_SIZE
Question 60. How do you recover database when you lost all of your control files?
Answer:
   In case of loss of loss of all control files, you can still recover database as long as you have all
   archivelog files. You can issue following command to recover the database.
       RECOVER DATABASE USING BACKUP CONTROLFILES UNTIL CANCEL;
               Apply all archivelog files
       ALTER DATABASE OPEN RESETLOGS;
       SHUTDOWN IMMEDIATE;
                 Backup database (COLD)
       STARTUP;
Question 61. What is main purpose of CHECKPOINT?
Answer:
A Checkpoint is a database event, which synchronizes the data blocks in memory with the datafiles on disk.
A checkpoint has two purposes:
    1. to establish data consistency
    2. Enable faster database recovery.
Question 62. You got a call from Application team saying Application is running very SLOW.
Where do you start looking first?
Answer:
Below are some of very important you should gather to identify the root cause of slowness in
application/database.
             Run a TOP command in unix to see CPU usage (identify CPU killer processes)


Run VMSTAT, SAR, and PRSTAT command to get more information on CPU and
memory usage and possible blocking
Run STATSPACK report to identify:
    a. TOP 5 WAIT EVENTS
    b. RESOURCE intensive SQL statements
See if STATISTICS on affected tables needs to be re-generated
IF poorly written statements are culprit, run a EXPLAIN PLAN on these statements and
see whether new index or use of HINT brings the cost of SQL down.
Question 63. How to SWITCH from PRIMARY to PHYSICAL STANDBY in 9i
Answer:
Perform below on Primary DB:
           1. ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH
               SESSION SHUTDOWN;
           2. SHUTDOWN IMMEDIATE;
           3. STARTUP NOMOUNT;
           4. ALTER DATABASE MOUNT STANDBY DATABASE;
           5. RECOVER MANAGED STANDBY DATABASE DICONNECT FROM SESSION;
           6. ALTER SYSEM SET LOG_ARCHIVE_DEST_2_STATUS= DEFER SCOPE=SPFILE;
Perform below on STANDBY DB:
           7. ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
           8. SHUTDOWN IMMEDIATE;
           9. STARTUP;
           10. ALTER SYSEM SET LOG_ARCHIVE_DEST_2_STATUS= ENABLE SCOPE=SPFILE;
At this time Standby becomes Primary and Primary becomes new Standby.
Question 64. How to ACTIVATE PHYSICAL STANDBY database in 9i
Answer:
Perform below on Primary DB if available to transfer all pending archive logs to standby:
           1. ALTER SYSTEM SWITCH LOGFILE;
           2. ALTER SYSTEM SWITCH LOGFILE;
Perform below on STANDBY DB:
             1. ALTER DATABASE ACTIVATE STANDBY DATABASE;
             2. SHUTDOWN IMMEDIATE;
             3. STARTUP
             4. Add TEMP file if needed.
At this time Standby database becomes PRIMARY database and relationship between Primary and
Standby has been lost by activating standby database.
Question 65. Explain different protection mode in DATAGURD
Answer:
      MAXIMUM PROTECTION
      MAXIMUM AVAILIBILITY
      MAXIMUM PERFORMANCE
Question 66. What is CASCADING STANDBY database?
Answer:


A CASCADING STANDBY is a standby database that receives its REDO information from another
STANDBY database (not from Primary database).
Question 67. What are some of the dba tables you query to find out about UNDO segments?
Answer:
You would query:
   DBA_UNDO_SEGS
   V$UNDOSTAT
Question 68. What does Block Media Recovery (BMR) do?
Answer:
BMR is responsible for restore and recover of specified BLOCK
Question 69. What is the difference between Fine Grained Audit (FGA) and Fine Grained Access
Control (FGAC)?
Answer:
FGA tracks when sensitive rows have been accesses, where FGAC prevents access to sensitive rows
Question 70. Which dynamic performance view DBA can query to see who deleted data from a
particular table sometime back?
Answer:
V$LOGMNR_CONTENTS
Question 71. What do you use DBMS_REDEFINITION package for?
Answer:
DBMS_REDEFINITION package is used to perform an ONLINE REBUILD of a table
Question 72. How do you add second or subsequent BLOCK SIZE to an existing database?
Answer:

Re-Create the CONTROLFILE to specify the new BLOCK SIZE for specific data files
Or Take the database OFFLINE, and the bring back online with a new BLOCK SIZE
specification
Question 73. What are new RMAN features in 9i?
Answer:
It allows you to set retention period of backups
It enables you to store the CHANNEL attributes so you do not have to specify in each
BACKUP or RESTORE command
Question 74, Explain different PARTITIONING options available in 9i
Answer:
Range Partitioning Used when there are logical ranges of data. Possible usage: dates, part numbers,
and serial numbers.


Hash Partitioning Used to spread data evenly over partitions. Possible usage: data has no logical
groupings.
List Partitioning Used to list together unrelated data into partitions. Possible usage: a number of states
list partitioned into a region.
Composite Range-Hash Partitioning Used to range partition first, then spreads data into hash partitions.
Possible usage: range partition by date of birth then hash partition by name; store the results into the
hash partitions.
Composite Range-List Partitioning Used to range partition first, then spreads data into list partitions.
Possible usage: range partition by date of birth then list partition by state, then store the results into the
list partitions.
Question 75. How to convert a Single Instance 9i database to a Real Application Clusters 9i (RAC)
database without using export/import tools.
Answer:
      1. Make a full database backup before you change anything
      2. Copy the existing $ORACLE_HOME/dbs/init<SID1>.ora to
         $ORACLE_HOME/dbs/init<db_name>.ora. Add the following parameters to
         $ORACLE_HOME/dbs/init<db_name>.ora:
             a. *.cluster_database = TRUE
             b. *.cluster_database_instances = 2
             c. *.undo_management=AUTO (Add if you don't have it )
             d. <SID1>.undo_tablespace=undotbs (undo tablespace which already exists)
             e. <SID1>.instance_name=RAC1
             f. <SID1>.instance_number=1
             g. <SID1>.thread=1
             h. <SID1>.local_listener=LISTENER_RAC1
      3. Open your database and run $ORACLE_HOME/rdbms/admin/catclust.sql to create cluster
         database specific views within the existing instance
      4. Recreate control file if you defined maxinstances to be 1 when you created the single instance
         database
      5. Add instance specific parameters in the init<db_name>.ora for the second instance on the
         second node and set appropriate values for it
             a. <SID2>.instance_name=RAC2
             b. <SID2>.instance_number=2
             c. <SID2>.local_listener=LISTENER_RAC2
             d. <SID2>.thread=2
             e. <SID2>.undo_tablespace=UNDOTBS2
             f. <SID2>.cluster_database = TRUE
             g. <SID2>.cluster_database_instances = 2
      6. From the first instance, mount the database and run the following command
             alter database
             add logfile thread 2
             group 3 ('/dev/RAC/redo2_01_100.dbf') size 100M,
             group 4 ('/dev/RAC/redo2_02_100.dbf') size 100M;
             alter database enable public thread 2;
      7. Create a second Undo Tablespace from the existing instance
      8. Set ORACLE_SID and ORACLE_HOME environment variables on the second node and
         START second instance.
Question 76. Explain RAC and advantages of using RAC
Answer:
In Real Application Clusters environments, all nodes concurrently execute transactions against the same
database. Real Application Clusters coordinates each node's access to the shared data to provide
consistency and integrity.
Advantages of RAC:

Improved throughput
Scalability over single instance systems
Improved response time
High availability
Transparency
Question 77. Explain Row Locking and Multi-version Read Consistency in RAC
Answer:
Oracle's row locking feature allows multiple transactions from separate nodes to lock and update
different rows of the same data block. This is done without any of the transactions waiting for the others
to commit. If a row has been modified but not yet committed, then the original row values are available to
all instances for read access. This is called multi-version read consistency.
Question 78. What is an External table introduced in 9i?
Answer:
Oracle9i introduces external tables, which provide a mechanism to view data stored in external sources
as if it were a table in the database. This ability to read external data provides a more straightforward
method of loading and transforming data from external sources. Administrators no longer need to reserve
space inside the database for staging tables or write external programs to transform the data outside of
the database environment. By making it no longer necessary to stage data in the Oracle database,
Oracle9i's external tables have essentially streamlined the ETL function by merging the transformation
and loading processes.
Question 79. What is SPFILE?
Answer:
The SPFILE (server parameter file) is Oracle's new method of maintaining database parameters. The old
method of editing a text based parameter file (INIT.ORA) has given way to the new method of maintaining
persistent parameters. By this Oracle means that you can change a system parameter and have its value
be maintained across shutdown and startup. This is a great savings from the past where you had to issue
the ALTER SYSTEM command and then remember to edit the INIT.ORA parameter file. Here is how to
create SPFILE:
     SQL> CREATE SPFILE [='spfile_name'] FROM PFILE [='pfile_name'];
     SQL> CREATE PFILE [='pfile_name'] FROM SPFILE [='spfile_name'];
     SQL> create spfile from pfile;
Usage:
1. SQL> ALTER SYSTEM SET DB_CACHE_SIZE=1000M SCOPE=SPFILE;
Question 80. How do you backup and restore using Transportable Tablespaces
Answer:
      1.
      2.
      3.
      4.
      5.
      6.
      7.
Run DBMS_TTS against the tablespace to see if tablespace is self contained or not.
Make tablespace Read Only.
Export Metadata (EXP TRANSPORT_TABLESPACES=Y TABLESPACE=ts1)
Copy data file to target host
Copy export dump to target
Import the Metadata (IMP TRANSPORT_TABLESPACES=Y DATAFILES (file1,file2) )
Bring Tablespace ONLINE and enable SOURCE tablespace to READ WRITE
Question 81. Explain different type of Database Failures
Answer:
-+
Statement failure failed SQL is automatically rolled back and an error is returned to user.
User Process failure abnormal disconnect PMON detects and rolls back and releases
locks.
User Error (drop table, data) DBA is required to recover data (import or incomplete
recovery)
Media Failure Loss or corruption of files DBA needs to apply appropriate recovery.
Instance Failure Abnormal shutdown Instance simply needs restarted, SMON auto
recovers by:
 Rolling forward changes in the redo log not recorded in the data files before Open of
        database.
Rollbacks can occur after the database is open, when block data is requested.
Question 82. What does RESETLOGS option do?
Answer:
1. Creates a new incarnation of the database, putting a new SCN in all data file headers.
2. Reset Log Sequence number to 1
3. Reformats ONLINE REDO LOGFILES if they exists
Question 83. Name 5 system views that can be used to retrieve information about backup and
recovery
Answer:
1.
2.
3.
4.
5.
V$BACKUP
V$DATAFILE_HEADER
V$RECOVER_FILE
V$RECOVERY_LOG
V$RECOVERY_STATUS
Question 84. What is the quickest way to clone a database give your backup is done via RMAN?
Answer:
Using RMAN command DUPLICATE DATABASE
Question 85. What is the use of the RESUMABLE parameter in EXPORT?
Answer:
The RESUMABLE parameter allows the export to
      1. Suspend if a space allocation issue occurs
      2. Wait until the space allocation issue is solved
      3. Then resume and therefore not to abort, provided a timeout is set appropriately.
Question 86. Name init.ora parameters need to set for Advanced Replication
Answer:
      1.
      2.
      3.
      4.
      5.
      6.
DISTRIBUTED_TRANSACTIONS
GLOBAL_NAMES
JOB_QUEUE_PROCESSES
OPEN_LINKS
PARALLEL_MAX_SERVERS
PARALLEL_MIN_SERVERS


Question 87. Name 5 init.ora parameters that needs to set in Primary database for DataGuard
configuration
Answer:
      1.
      2.
      3.
      4.
      5.
      6.
LOG_ARCHIVE_DEST_2
LOG_ARCHIVE_DEST_2_STATE
FAL_CLIENT
FAL_SERVER
STANDBY_ARCHIVE_DEST
STANDBY_FILE_MANAGEMENT
Question 88. What is the use of FORCE LOGGING option?
Answer:
By setting FORCE LOGGING to TRUE, all transactions will generate REDO. This is used for Data Guard,
so no data will be missed (i.e. transactions that were run with NOLOGGING option) in Standby database.
Question 89. (On UNIX) When should more than one DB writer process be used? How many
should be used?
Answer:
If the UNIX system being used is capable of asynchronous IO then only one is required, if the system is
not capable of asynchronous IO then up to twice the number of disks used by Oracle number of DB
writers should be specified by use of the db_writers initialization parameter.
Question 90. If you have an example table, what is the best way to get sizing data for the
production table implementation?
Answer:
The best way is to analyze the table and then use the data provided in the DBA_TABLES view to get the
average row length and other pertinent data for the calculation. The quick and dirty way is to look at the
number of blocks the table is actually using and ratio the number of rows in the table to its number of
blocks against the number of expected rows.
Question 91. What special Oracle feature allows you to specify how the cost based system treats a
SQL statement?
Answer:
The COST based system allows the use of Hints to control the optimizer path selection. If they can give
some example hints such as FIRST ROWS, ALL ROWS, USING INDEX, STAR, even better.
Question 92. You want to determine the location of identical rows in a table before attempting to
place a unique index on the table, how can this be done?
Answer:
select rowid from emp e
where e.rowid > (select min(x.rowid)
from emp x
where x.emp_no = e.emp_no);
Question 93. You are joining a local and a remote table, the network manager complains about the
traffic involved, how can you reduce the network traffic?


Answer:
Push the processing of the remote data to the remote instance by using a view to pre-select the
information for the join. This will result in only the data required for the join being sent across.
Question 94. How do you prevent output from coming to the screen?
Answer:
The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns off screen output.
This option can be shortened to TERM.
Question 95. You see multiple fragments in the SYSTEM tablespace, what should you check first?
Answer:
Ensure that users don¡¯t have the SYSTEM tablespace as their TEMPORARY or DEFAULT tablespace
assignment by checking the DBA_USERS view.
Question 96. What are some indications that you need to increase the SHARED_POOL_SIZE
parameter?
Answer:
Poor data dictionary or library cache hit ratios, getting error ORA-04031. Another indication is steadily
decreasing performance with all other tuning parameters the same.
Question 97. When should you increase copy latches? What parameters control copy latches?
Answer:
When you get excessive contention for the copy latches as shown by the ¡°redo copy latch hit ratio. You
can increase copy latches via the initialization parameter LOG_SIMULTANEOUS_COPIES to twice the
number of CPUs on your system.
Question 98. Describe hit ratio as it pertains to the database buffers. What is the difference
between instantaneous and cumulative hit ratio and which should be used for tuning?
Answer:
The hit ratio is a measure of how many times the database was able to read a value from the buffers
verses how many times it had to re-read a data value from the disks. A value greater than 80-90% is
good, less could indicate problems. If you simply take the ratio of existing parameters this will be a
cumulative value since the database started. If you do a comparison between pairs of readings based on
some arbitrary time span, this is the instantaneous ratio for that time span. Generally speaking an
instantaneous reading gives more valuable data since it will tell you what your instance is doing for the
time it was generated over.
Question 99. What can cause a high value for recursive calls? How can this be fixed?
Answer:
A high value for recursive calls is cause by improper cursor usage, excessive dynamic space
management actions, and or excessive statement re-parses. You need to determine the cause and
correct it By either re-linking applications to hold cursors, use proper space management techniques
(proper storage and sizing) or ensure repeat queries are placed in packages for proper reuse.
Question 100. You look at the dba_rollback_segs view and see that there is a large number of
shrinks and they are of relatively small size, is this a problem? How can it be fixed if it is a
problem?


Answer:
A large number of small shrinks indicates a need to increase the size of the rollback segment extents.
Ideally you should have no shrinks or a small number of large shrinks. To fix this just increase the size of
the extents and adjust optimal accordingly.
Question 101. You look at the dba_rollback_segs view and see that you have a large number of
wraps is this a problem?
Answer:
A large number of wraps indicates that your extent size for your rollback segments are probably too small.
Increase the size of your extents to reduce the number of wraps. You can look at the average transaction
size in the same view to get the information on transaction size.
Question 102. How many redo logs should you have and how should they be configured for
maximum recoverability?
Answer:
You should have at least three groups of two redo logs with the two logs each on a separate disk spindle
(mirrored by Oracle). The redo logs should not be on raw devices on UNIX if it can be avoided.
Question 103. If the database cannot be bounced, how would you kill a user?
Answer:
Use command:
       ALTER SYSTEM KILL SESSION SID,SERIAL# ;
Question 104: What is the frequency of log Updated..?
Answer:
On commit or
On checkpoint or
Redolog buffer is 1/3rd full
Question 105: How do you rename a database?
Answer:
You can change Database name by following below procedure:
1. Alter Database backup control file to trace;
2. Above step will create a text control file in user_dump_dest directory.
3. Change name of the Database in above file and in init.ora file.
4. STARTUP NOMOUNT
5. Run the script that was modified in step 3
6. ALTER DATABASE OPEN RESETLOGS;
Question 106: Is it possible to configure primary server and stand by server on different OS?
Answer:
Answer for this questions is NO. Standby database must be on same version of database and same
version of Operating system.


Question 107: What does database do during mounting process?
Answer:
while mounting the database oracle reads the data from controlfile which is used for verifying physical
database files during sanity check. Background processes are started before mounting the database only.
Question 108: What is a deadlock and Explain
Answer:
A deadlock is a condition where two or more users are waiting for data locked by each other. Oracle
automatically detects a deadlock and resolves them by rolling back one of the statements involved in the
deadlock, thus releasing one set of data locked by that statement. Statement rolled back is usually the
one which detects the deadlock. Deadlocks are mostly caused by explicit locking because oracle does
not do lock escalation and does not use read locks. Multi-table deadlocks can be avoided by locking the
tables in same order in all the applications, thus precluding a deadlock.
Question 109: What are the options available to refresh snapshots?
Answer:
COMPLETE - Tables are completely regenerated using the snapshots query and the master tables
every time the snapshot referenced.
FAST - If simple snapshot used then a snapshot log can be used to send the changes to the snapshot
tables.
FORCE - Default value. If possible it performs a FAST refresh; otherwise it will perform a complete
refresh.
Question 110: What is snapshot log?
Answer:
It is a table that maintains a record of modifications to the master table in a snapshot. It is stored in the
same database as master table and is only available for simple snapshots. It should be created before
creating snapshots.
Question 111: What is Two-Phase Commit?
Answer:
Two-phase commit is mechanism that guarantees a distributed transaction either commits on all
involved nodes or rolls back on all involved nodes to maintain data consistency across the global
distributed database. It has two phase, a Prepare Phase and a Commit Phase.
Question 112: Describe two phases of Two-phase commit?
Answer:
Prepare phase - The global coordinator (initiating node) ask a participants to prepare (to promise to
commit or rollback the transaction, even if there is a failure)
Commit - Phase - If all participants respond to the coordinator that they are prepared, the coordinator
asks all nodes to commit the transaction, if all participants cannot prepare, the coordinator asks all
nodes to roll back the transaction.
Question 113: What are the factors causing the reparsing of SQL statements in SGA
Answer:


There are main two causes for reparsing:
1. The objects which the query is referencing has been modified
2. The parsed version of sql text has been aged out of the library cache.
Question 114: How to implement the multiple control files for an existing database
Answer:
1. Edit init.ora file, set control_files parameter with multiple location
2. shutdown immediate
3. copy control file to multiple locations & confirm from init.ora contol_files parameter
4. start the database.
5. run this query for changes confirmation - select name from v$controlfile;
Question 115: What is mean by Program Global Area (PGA)
Answer:
PGA - Program Global Area
or the Process Global Area is a memory region that contains data and control information for a single
server process or a single background process.
The PGA is allocated when a process is created and de-allocated when the process is terminated. PGA is
an area that is used by only one process.
Question 116: What is meant by recursive hints?
Answer:
Number of times processes repeatedly query the dictionary table is called recursive hints. It is due to
the data dictionary cache is too small. By increasing the SHARED_POOL_SIZE parameter we can
optimize the size of Data Dictionary Cache.
Question 117: Is it possible to use raw devices as data files and what is the advantages over
filesystem files ?
Answer:
Yes.
The advantages over file system files:
I/O will be improved because Oracle will bypass the OS. Disk Corruption will be very less.
Question 118: What are disadvantages of having raw devices?
Answer:
We have to depend on export/import utility for backup/recovery
The tar command cannot be used for physical file backup, instead we have to use dd command which is
less flexible and has limited recoveries.
Question 119. What are the system resources that can be controlled by profile?
Answer:
1. Number of concurrent sessions by user
2. CPU processing time
3. Amount of Logical I/O
4. Amount of Idle time


Question 120. Explain different level of Auditing
Answer:
Statement Auditing
Privilege Auditing
Object Auditing
Question 121. Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
Answer:
ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is
where the oracle products reside.
Question 122. Name three advisory statistics you can collect.
Answer:
Database Cache Advisory
Shared Pool Advisory
PGA Advisory
Question 123. When a user process fails, what background process cleans up after it?
Answer:
PMON
Question 124. How would you force a log switch?
Answer:
ALTER SYSTEM SWITCH LOGFILE;
Question 125. When creating a user, what permissions must you grant to allow them to connect to
the database?
Answer:
CREATE SESSION
Question 126. What view would you use to determine free space in a tablespace?
Answer:
DBA_FREE_SPACE
Question 127. How would you determine who has added a row to a table?
Answer:
If database auditing is turned ON, query SYS.AUD$ table
Question 128. You have just compiled a PL/SQL package but got errors, how would you view the
errors?
Answer:
SHOW ERRORS


Question 129. How can you enable a trace for a session?
Answer:
ALTER SESSION SET TRACING ON
Question 130. A DBA had to remove some Archivelogs to free up space in filesystem. Now when
the RMAN job starts to backup Archivelogs, it complains about missing Archivelogs that were deleted by
DBA. To resolve the issue and continue backing up remainder of Archivelogs, which RMAN command
can be used so it won't complain about missing Archivelogs.
Answer:
Crosscheck command
Question 131. Which RMAN command is used to create an exact replica of a database in new
host?
Answer:
DUPLICATE DATABASE
Question 132. How do you install STATSPACK?
Answer:
By running $ORACLE_HOME/rdbms/admin/spcreate.sql script
Question 133. Process you follow to start looking into Performance issue at database level (If the
application is running very slow, at what points do you need to go about the database in order to improve
the performance?)
Answer:
Run a TOP command in Unix to see CPU usage (identify CPU killer processes)
Run VMSTAT, SAR, and PRSTAT command to get more information on CPU and memory usage
   and possible blocking
Run STATSPACK report to identify:
      1. TOP 5 WAIT EVENTS
      2. RESOURCE intensive SQL statements
See if STATISTICS on affected tables needs to be re-generated
IF poorly written statements are culprit, run a EXPLAIN PLAN on these statements and see whether
   new index or use of HINT brings the cost of SQL down.
Question 134. Which is most important v$ view to see performance related information?
Answer:
V$WAITSTAT
Question 135. Explain below wait events in STATSPACK report
DB SCATTERED READ, DB SEQUENTIAL REAL, ENQUEUE
Answer:
DB SCATTERED READ
DB SEQUENTIAL REAL
ENQUEUE
- FULL TABLE SCAN
- IO
- LOCKING


Question 135. List five most important parameter in 9i affecting performance
Answer:
CURSOR_SHARING
DB_CACHE_SIZE
PGA_AGGREGATE_TARGET
DB_16K_CACHE_SIZE, DB_8K_CACHE_SIZE, DB_2K_CACHE_SIZE
Question 136. What is PGA_AGGREGATE_TARGET?
Answer:
This parameter controls the maximum amount of memory PGA which can be used by the queries when
WORKAREA_SIZE_POLICY is set to Auto.
The value you can be set in Bytes, kilobytes (K), megabytes (M) or gigabytes (G). The default value is 0
This parameter also has an effect on the execution plans of the cost based optimizer. The optimizer uses
the value of the parameter PGA_AGGREGATE_TARGET to derive an estimate for the minimum and
maximum amount of memory which should be available at run-time for each sort, hash-join and bitmap
operator in the query. Based on this minimum and maximum value, the optimizer selects the best plan.
Question 137. How do you analyze table partition using Oracle provided package?
Answer:
DBMS_STATS.GATHER_TABLE_STATS with GRANULARITY => 'PARTITION' OPTION
Question 138. You see a wait on LMS process in statspack, what does that mean?
Answer:
A. Wait is due to Data Guard Broker.
Question 139. Name three advisory statistics you can collect.
Answer:
SHARED_POOL_ADVICE
PGA_TARGET_ADVICE
DB_CACHE_ADVICE
Question 140. Explain procedure to Change CHARACTERSET of a database.
Answer:
Can't change CHARACTERSET of a database, you will need to re-create the database with appropriate
CHARACTERSET.
Question 141. If you had a tablespace, TEST_TABLESPACE, which consists of three files:
TEST01.dbf, TEST02.dbf, and TEST03.dbf, and someone accidentally used the Unix command "rm" to
delete the file TEST02.dbf, what else would you need in order to recover all the data that was present in
TEST_TABLESPACE at the time that TEST02.dbf was deleted?
Answer:
All Archivelogs


Question 142. How do you put database is ARCHIVELOG mode, explain procedure
Answer:
1. Modify init.ora parameter START_ARCHIVE=TRUE
2. SQL> SHUTDOWN IMMEDIATE;
3. STARTUP MOUNT;
4. ALTER DATAVASE ARCHIVELOG;
5. ALTER DATABASE OPEN;
Question 143. How do you create PASSWORD FILE?
Answer:
using orapwd utility
Question 144. How can you tell if an index on particular table is USED or NOT USED in 9i?
Answer:
By turning MONITORING ON that index and querying into INDEX_USAGE table
Question 145. How do you switch from an init.ora file to a spfile?
Answer:
SQL> CREATE SPFILE FROM PFILE;
Question 146. Explain FORCE LOGGING feature in 9i.
Answer:
By setting FORCE LOGGING to TRUE, all transactions will generate REDO. This is used for Data Guard,
so no data will be missed (i.e. transactions that were run with NOLOGGING option) in Standby database
Question 147. Explain the use of setting GLOBAL_NAMES equal to TRUE.
Answer:
Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or
FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database
to which they are linking.
Question 148. How do you set up 9i Data Guard?
Answer:
Take a full hot backup of Primary database
Create standby control file
Transfer full backup, init.ora, standby control file to standby node.
Modify init.ora file on standby node.
Restore database
Recover Standby database
        (Alternatively, RMAN command DUPLICATE DATABASE FOR STANDBY DO RECOVERY can
        be also used)
Setup FAL_CLIENT and FAL_SERVER parameters on both sides
Put Standby database in Managed Recover mode


Question 149. How do you create Physical Standby database?
Answer:
Take a full hot backup of Primary database
Create standby control file
Transfer full backup, init.ora, standby control file to standby node.
Modify init.ora file on standby node.
Restore database
Recover Standby database
        (Alternatively, RMAN command DUPLICATE DATABASE FOR STANDBY DO RECOVERY can
        be also used)
Setup FAL_CLIENT and FAL_SERVER parameters on both sides
Put Standby database in Managed Recover mode
Question 150. Explain LOG_ARCHIVE_DEST_2 parameter and Dataguard related parameters.
Answer:
log_archive_dest_2='SERVICE=ORACLE_SID_STBY optional lgwr async=20480 noaffirm reopen=15
max_failure=10 net_timeout=30 delay=0'
Question 151. Explain Database SWITCH OVER PROCEDURE
Answer:
On Primary:
alter database commit to switchover to physical standby with session shutdown;
shutdown;
startup nomount;
alter database mount standby database;
recover managed standby database disconnect from session;
On Standby:
alter database commit to switchover to primary;
shutdown;
startup;
Question 152. Exaplain How to Activate STANDBY Database
Answer:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE ACTIVATE STANDBY DATABASE;
SHUTDOWN IMMEDIATE;
STARTUP;
Question 153. What do you know about VCS?
Answer:
VCS stands for Veritas Cluster Software.
VCS is used as a High Availability solution in case of host failure. There will be typically two nodes in a
cluster and database will be running on one node. Incase of host failure, VCS will failover database
(service group) to second node.
Question 154. What are typical resources in VCS service group?


Answer:
In typical VCS configuration, there are three main resources required for Oracle database failover.
1. Oracle Database
2. Listener
3. Filesystems
Question 155. Which file contains VCS service group and resource information?
Answer:
Main.cf
Question 156. What database related information we need to set in VCS?
Answer:
ORACLE_SID
LISTENER name
LISTENER password (if used)
Oracle filesystems that will be failed over
Location to init.ora/spfile if not available at default location
Question 157. What is VCS in-depth monitoring?
Answer:
In VCS in-depth monitoring, a database user (VCSMON) will be created which will login to database at
certain interval (i.e. every 5 minute) to see if database connectivity is OK. For some reason, if this user
can t login VCS will fail over the database to second node.
Question 158. In ACTIVE VCS environment, you need to recycle the database but doesn’t want
database to failover to other node. What you must do before shutting down the database, so it doesn t
failover to other node?
Answer:
You need to FREEZE the service group.
Alternatively, you can make Oracle resource NON-CRITICAL (not recommended)
Question 159. What if you accidentally shutdown the LISTENER in VCS environment?
Answer:
If Listener Resource is marked CRITICAL, and Restart Attempt is set to 0, then Service Group will failover
to second node.
It is recommended for Listener resource to set RESTART ATTEMPTS value to 3.
Question 160. Explain Automatic Segment Space Management
Answer:
Automatic Segment Space Management features was introduced in 9i which simplifies management of
free space usage within object for below parameter:
FREELIST
FREELIST GROUPS
PCTUSED


Question 161. How do you REBUILD index online and Compute Statistics at the same time?
Answer:
ALTER INDEX INDEX_NAME REBUILD COMPUTE STATISTICS ONLINE;
Question 162. Explain Bitmap Join Indexes
Answer:
Bitmap Join Indexes pre-stores results of a join and can avoid an expensive join operation at runtime.
Question 162. Which parameters can be modified dynamically as part of 9i Dynamic Memory
Management feature?
Answer:
Below parameters can be modified dynamically as part of 9i Dynamic Memory Management feature
DB_CACHE_SIZE
SHARED_POOL_SIZE
LARGE_POOL_SIZE
JAVA_POOL_SIZE
Question 163. What additional statistics are gathered by DBMS_STATS that were not available in
earlier version or by using ANALYE command?
Answer:
In 9i DBMS_STATS package now collects below statistics as well:
CPU Usage
System I/O
Question 163. Explain Different values supported by CURSOR_SHARING parameter and its
explanation
Answer:
Below values are supported by CURSOR_SHARING init.ora parameter:
FORCE - Literals will be replaced by system generated bind variables where possible
SIMILAR - Oracle determines which literals are "safe" for substitution with bind variables. This will result
in some SQL not being shared in an attempt to provide a more efficient execution plan.
EXACT - Only allows statements with identical text to share the same cursor
Question 163. Name three very important Hit Ratios in database
Answer:
Buffer Cache Hit Ratio
Data Dictionary Hit Ratio
Library Cache Hit Ratio
(Use V$SYSSTAT)
(Use V$ROWCACHE)
(Use V$LIBRARYCACHE, V$SGASTAT)
Question 164. Name Different Latches in database and Dynamic Performance Views to get more
information about Latches


Answer:
Database Latches:
Redo Allocation Latch
Redo Copy Latch
Row Cache Latch
Use V$LATCH, V$LATCHHOLDER, V$LATCHNAME to get more information about the latches
Question 165. In which scenarios you need to set Large Pool?
Answer:
You need to set Large Pool if you are using below:
MTS (Multithreaded Server)
RMAN backups
Question 166. Explain Tuning Process that involves Application, Database, OS, Network
Answer:
In such a scenario, tuning should perform in following order:
    1. Business Rules
    2. Data Design
    3. Application Design
    4. Logical Structure of the Database
    5. Database Operations
    6. Access Path
    7. Memory Allocation
    8. I/O and Physical Structure of the Database
    9. Resource Allocation
    10. OS
Question 167. Explain Different Tuning Areas in Database
Answer:
Following areas within database can be tuned:
Memory -
I/O -
CPU
Space Management
Redo & Checkpoint
Rollback -
Shared Pool, Buffer Cache, Redo Buffer, Sort Area Size, PGA, Large Pool
Multiple Database Writer Processes, Distributing I/O, RAID
Extent Allocation, Oracle Block Efficiency
Redo log file configuration, checkpoints
Retention, number of Rollback Segments, Optimal
Question 168. How do you setup Auditing in Database?
Answer:
If audit packages are not installed, run $ORACLE_HOME/rdbms/admin/cataudit.sql script
Modify initialization parameter AUDIT_TRAIL=DB and setup AUDIT_DUMP_DEST
Select what type of operations needs to be audited
View Audit results from SYS.AUD$ table
Question 169. Can you Audit System Operations? If Yes, how?
Answer:
SYS connections can be audited by setting init.ora parameter AUDIT_SYS_OPERATIONS=TRUE


Question 170. How can you setup Encryption in Database?
Answer:
Data within Database can be encrypted and decrypted using package:
DBMS_OBFUSCATION_TOOLKIT
Question 171. Name five parameters can be used for Password Management?
Answer:
Following parameters can be used to manage user password:
1.
2.
3.
4.
5.
FAILED_LOGIN_ATTEMPTS
PASSWORD_GRACE_TIME
PASSWORD_LIFE_TIME
PASSWORD_REUSE_MAX
PASSWORD_REUSE_TIME
Question 172: What is difference between Logical Standby Database and Physical Standby
database?
Answer:
The primary functional difference between logical and physical standby database setups is that logical
standby permits you to add additional objects (tables, indexes, etc) to the database, while physical
standby is always an exact structural duplicate of the master database. The downside, though, is that
logical standby is based on newer technologies (logical standby is new in Oracle 9.2) and tends to be
generally regarded as more temperamental than physical standby.


Technical – UNIX
Every DBA should know something about the operating system that the database will be running on. The
questions here are related to UNIX but you should equally be able to answer questions related to
common Windows environments.
Question 1. How do you list the files in an UNIX directory while also showing hidden files?
Answer:
ls -ltra
Question 2. How do you execute a UNIX command in the background?
Answer:
Use the "&" at the end of command
Question 3. What UNIX command will control the default file permissions when files are created?
Answer:
Umask
Question 4. Explain the read, write, and execute permissions on a UNIX directory.
Answer:
Read allows you to see and list the directory contents.
Write allows you to create, edit and delete files and subdirectories in the directory.
Execute gives you the previous read/write permissions plus allows you to change into the directory and
execute programs or shells from the directory.
Question 5. The difference between a soft link and a hard link?
Answer:
A symbolic (soft) linked file and the targeted file can be located on the same or different file system while
for a hard link they must be located on the same file system.
Question 6. Give the command to display space usage on the UNIX file system.
Answer:
df -lk
Question 7. Explain iostat, vmstat and netstat.
Answer:
Iostat reports on terminal, disk and tape I/O activity.
Vmstat reports on virtual memory statistics for processes, disk, tape and CPU activity.
Netstat reports on the contents of network data structures.
Question 8. How would you change all occurrences of a value using VI?
Answer:
Use:%s/<old>/<new>/g


Question 9. Give two UNIX kernel parameters that effect an Oracle install
Answer:
SHMMAX & SHMMNI
Question 10. Briefly, how do you install Oracle software on UNIX.
Answer:
Basically, set up disks, kernel parameters, create oracle user and dba group, and run runinstaller.
Question 11. How do you create a decision tree in a shell script?
Answer:
Depending on shell, usually a case-esac or an if-endif or fi structure
Question 12. What is a pipe and give an example?
Answer:
A pipe is two or more commands separated by pipe char '|'. That tells the shell to arrange for the output of
the preceding command to be passed as input to the following command.
Example : ls -l | pr
The output for a command ls is the standard input of pr.
When a sequence of commands are combined using pipe, then it is called pipeline.
Question 13. What is the difference between > and >> redirection operators?
Answer:
> is the output redirection operator when used it overwrites while >> operator appends into the file.
Question 14. What is the difference between process and thread.
Answer:
Creation of new process requires new resources and Address space whereas the thread can be created
in the same address space of the process which not only saves space and resources but are also easy to
create and delete, and many threads can exists in a process.
Question 15. What is the difference between a shell variable that is exported and the one that is
not exported?
Answer:
export LANG=C
will make the variable LANG the global variable, put it into the global environment. all other processes
can use it.
LANG=C
will change the value only in the current script.
Question 16. How will you list only the empty lines in a file (using grep)
Answer:
grep "^$" filename.txt


Question 17. What is Semaphore?
Answer:
A data object that represents the right to use a limited resource, used for synchronization and
communication between asynchronous processes.
Question 18. How do you execute a UNIX command in the background?
Answer:
You can use & at the end of command or use nohup command
Question 19. How do you check active shared memory segments?
Answer:
ipcs -a
Question 20. How do you check Paging/Swapping in Unix?
Answer:
You can check Paging/Swapping using below commands
vmstat s
prstat s
swap l
sar p
Question 21. How do you check number of CPU installed on Unix server?
Answer:
psrinfot v
Question 22. How do you check Paging/Swapping in Unix?
Answer:
Vmstat s
Prstat s
Swap l
Sar p


PRACTICAL ORACLE DBA QUESTIONS:
A DBA interview can be difficult both for the interviewer and the candidate. The role involves technical
skills, process skills, and personal communications skills. Although those three skill sets are all critical,
most "DBA interview questions" articles in the past have focused only on the questions you can use to
judge a person s technical qualifications. In this article, you will see questions an interviewer can pose to
the DBA candidate to judge the other attributes of the job.
In addition, this article includes a set of questions DBAs should ask at some point during their interviews.
If the interviewer stops and asks "Do you have any questions for me?" don t sit there quietly. The
emphasis of the DBA questions provided in this article is the organization s process focus and its
technical career path for DBAs. All of the questions provided here are intended as starting points; based
on the answers to the questions, you should ask follow-ups to understand how the answer will affect your
day-to-day activities and your long-term prospects. Of course, if you ask a question to the interviewer, you
should be prepared to answer the same question concerning your present work environment.
Questions to ask the interviewer:
Process control issues:
What processes do you follow while implementing changes in production?
Beside the DBAs and system administrators, who has access to the "Oracle" operating system account?
How often is the oracle operating system account password changed?
Are the DBAs co-located with the teams they support? How is capacity planning performed?
Is there adequate capacity already in place to support the expected growth over the next year? Future
opportunities/organization issues:
Is there a formal job definition for the DBA role? Is there a defined technical career path?
How is IT aligned with the business areas?
How many employees report to more than one manager? How do you determine if a DBA has been
successful?
How are the application DBAs and production control DBAs organized?
Questions that will be asked to you
Writing skills:
Please bring along a copy of the last status report you ve submitted, as well as any articles. If you haven t
brought one along, please mail me one after the interview.
Perseverance in technical expertise:
What errors did you hit during your most recent database recovery?
What was the most difficult technical obstacle you encountered during your last project?
Perseverance in relationships:
What negative "group relationship" issues exist in your current working environment? How are you
addressing them?
What is your most difficult set of users, and how do you manage that relationship? What do you want to
learn in the next 12 months?
What communications method do you usually use when dealing with users? Can you provide examples?
What communications method is most effective when customers need your help?
Technical Experience:
(Use hypothetical questions rather than straight technical questions)
What database and overall architecture would you suggest for testing new middleware without impacting
production?
How do you assess my database s health?
How would you approach a performance problem with a three-tier application? How do you test your
backup/recovery procedures?
How would you support the upgrade process for multiple applications, with different application rollout
cycles, in the same instance?


These are samples; use a question from each category to guide a discussion of the way the
communications skills, process knowledge, and technical aspects of the job are interrelated in the role. If
the interviewer and the candidate can agree on what a successful DBA looks like in the environment,
there is a strong foundation for a future working relationship.