Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Wednesday, 30 May 2012

Interview Question with Answer Part 10

How can I check if there is anything rolling back?
It depends on how you killed the process. If you did and alter system kill session you should be able to look at the used_ublk block in v$transaciton to get an estimate for the rollback being done. If you killed to server process in the OS and PMON is recovering the transaction you can look at V$FAST_START_TRANSACTIONS view to get the estimate
How to find out how much rollback a session has to do
select time_remaining from v$session_longops
where sid =<sid of the session doing the rollback>;
How to Drop a column of a Table?
Consider the below Example
Create table x(a date, b date, c date);
Now to drop column B:
Alter table x set unused column b;    -- it will mark column as UNUSED
Select * from sys.dba_unused_col_tabs;
Alter table x drop unused columns;
Alternative method to drop column:
Alter table x drop column c cascade constraints;
How can we see the oldest flashback available?
You can use the following query to see the flashback data available.
SELECT to_char(sysdate,'YYYY-MM-DD HH24:MI') current_time, to_char(f.oldest_flashback_time, 'YYYY-MM-DD HH24:MI') OLDEST_FLASHBACK_TIME,
(sysdate - f.oldest_flashback_time)*24*60 HIST_MIN FROM v$database d, V$FLASHBACK_DATABASE_LOG f;
How to get current session id, process id, client process id?
select b.sid, b.serial#, a.spid processid, b.process clientpid from v$process a, v$session b
where a.addr = b.paddr
and b.audsid = userenv('sessionid');
V$SESSION.SID and V$SESSION.SERIAL# are database process id
V$PROCESS.SPID – Shadow process id on the database server
V$SESSION.PROCESS – Client process id, on windows it is “:” separated the first # is the process id on the client and 2nd one is the thread id.
What is MRC ? What you do as application DBA for MRC?
MRC also called as Multiple Reporting Currency in oracle application. Default you have currency in US Dollars but if your organization operating books are in other currency then you as application DBA need to enable MRC in applications.
How will you find Invalid Objects in database?
select count(*) from dba_objects where status like 'INVALID';
select * from dba_objects where status like 'INVALID';
Can you use both ADPATCH and OPATCH in application?
Yes you have to use both in application , for application patches you will use ADPATCH UTILITY and for applying database patch in application you will use opatch UTILITY.
Do you have idea how to trace a running process on Linux?
Using strace you can trace the system calls being executed by a running process
$ strace -p 1435
Process 1435 attached – interrupt to quit
Pressed <control-C>‎  - press control-C to stop the strace
$ strace -cfo smon_strace.log -p 1435
Process 1435 attached – interrupt to quit
Process 1435 detached
What are database link? Differenciate the use of each of them?
A database link is a named object that describes a "path" from one database to another. There are different types of database link such as: Private database link, public database link & network 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.
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.
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.
How to know which version of database you are working?
select * from v$version;                                                       
In Reference to Rman point in time Recovery which scenario is better for you (Until time or until sequence)?
I am practicing various scenarios for backup and recovery using RMAN. I find until SCN better than until time, with log_seq in the middle. Until time is still going to use (ultimately) an SCN to recover, so if you know the SCN it would be preferred if not then time is fine.
If you have forgotten the root password on CentOS then what you will do?
If you are on CentOS then follow these steps:
- At the splash screen during boot time, press any key which will take you an interactive menu.
- Then select a Linux version you wish to boot and press “a” to append option to the line this will bring you to a line with the boot command
- Next at the end of that line type “single” as an option/parameter and then Press “Enter” to exit and execute the boot this will start the OS with single user mode which allow you to reset the root password by typing passwd and you can set new password for “root”.
How to determine whether the datafiles are synchronized or not?
select status, checkpoint_change#, to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time, count(*)
from v$datafile_header
group by status, checkpoint_change#, checkpoint_time
order by status, checkpoint_change#, checkpoint_time;
Check the results of the above query if it returns one and only one row for the online datafiles, means they are already synchronized in terms of their SCN. Otherwise the datafiles are still not synchronized yet.
You have just restored from backup and do not have any control files. How would you go about bringing up this database?
If you do not have a control file, you can create one from scratch in SQL*Plus as follows:
1. sqlplus /nolog
2. connect / as sysdba
3. Startup nomount;
4. the either create controlfile or restore it from the backup (if you have)
5. alter dataase mount;
6. Recover database using backup controlfile;
7. Alter database open;
From more details follow my blog post "Disaster Recovery from the scratch":
Is there any way to find the last record from the table?
select * from employees where rowid in(select max(rowid) from employees);
select * from employees minus select * from employees where rownum < (select count(*) from employees);
How you will find Oracle timestamp from current SCN?
select dbms_flashback.get_system_change_number scn from dual; -- Oracle Ver. 9i
SQL> Select to_char(CURRENT_SCN) from v$database;  -- oracle Ver. 10g or above
SQL> select current_scn, dbms_flashback.get_system_change_number from v$database;  --standby case
SQL> select scn_to_timestamp(8843525) from dual;
How to suspend/resume a process using oradebug?
SQL> oradebug setorapid 14
Unix process pid: 14962, image: oracle@localhost.localdomain (TNS V1-V3)
SQL> oradebug suspend
Statement processed.
SQL> oradebug resume
Statement processed.
How to find the last time a session performed any activity?
In v$session the column last_call_et has value which tells us the last time (seconds) ago when the session performed any activity within the database.
select username, floor(last_call_et / 60) "Minutes", status
from v$session
where username is not null order by last_call_et;
How to find parameters that will take into effect for new sessions?
Using the following query one can find the list of parameters that will take info effect for new sessions if the value of the parameter is changed.
SQL> SELECT name FROM v$parameter WHERE issys_modifiable = 'DEFERRED';
You can change the parameter using the deferred option:
SQL> alter system set sort_area_size=65538 deferred;
System altered
How to free (Flush) buffer cache?
How to free buffer cache?
Note: you may only want to do this on Dev or Test environment as it would affect performance on production. I already written on my earlier post  in real life; the cache would never be empty
-- displays the status and number of pings for every buffer in the SGA
SQL> select distinct status from v$bh;
-- flush buffer cache for 10g and upwards
SQL> alter system flush buffer_cache;
System altered.
-- flush buffer cache for 9i and upwards
SQL> alter session set events ‘immediate trace name flush_cache’;
Session altered.
-- Shows buffer cache was freed after flushing buffer cache
SQL> select distinct status from v$bh;
How to suspend all jobs from executing in dba_jobs?
By setting the value of 0 to the parameter “job_queue_processes” you can suspend all jobs from executing in DBA_JOBS. The value of this parameter can be changed without instance restart.
SQL> show parameter job_queue_processes;
———————————— ———– ———–
job_queue_processes integer 400
Now set the value of the parameter in memory, which will suspend jobs from starting
SQL> alter system set job_queue_processes=0 scope=memory;
System altered.
How to see the jobs currently being executed?
By using dba_jobs_running to can see all the job currently executing
SQL> select djr.sid, djr.job, djr.failures, djr.this_date, djr.this_sec, dj.what from dba_jobs_running djr, dba_jobs dj where djr.job = dj.job;
What is GSM in Oracle application E-Business Suite?
GSM stands for Generic Service Management Framework. Oracle E-Business Suite consist of various compoennts like Forms, Reports, Web Server, Workflow, Concurrent Manager. Earlier each service used to start at their own but managing these services (given that) they can be on various machines distributed across network. So Generic Service Management is extension of Concurrent Processing which manages all your services , provide fault tolerance (If some service is down ICM through FNDSM and other processes will try to start it even on remote server) With GSM all services are centrally managed via this Framework.
How can you license a product after installation?
You can use ad utility adlicmgr to licence product in Oracle application.
In a situation when you want to know which was the last query fired by the user. How to check?
Select S.USERNAME||'('||s.sid||')-'||s.osuser UNAME
,s.sid||'/'||s.serial# sid,s.status "Status",p.spid,sql_text sqltext
from v$sqltext_with_newlines t,V$SESSION s , v$process p
where t.address =s.sql_address and p.addr=s.paddr(+) and t.hash_value = s.sql_hash_value
order by s.sid,t.piece;
Can one copy Oracle software from one machine to another?
Yes, one can copy or FTP the Oracle Software between similar machines. Look at the following example:
# use tar to copy files and directorys with permissions and ownership
tar cf – $ORACLE_HOME | rsh “cd $ORACLE_HOME; tar xf –“
To copy the Oracle software to a different directory on the same server:
cd /new/oracle/dir/
(cd $ORACLE_HOME; tar cf – . ) | tar xvf -
NOTE: Remember to relink the Intelligent Agent on the new machine to prevent messages like “Encryption key supplied is not the one used to encrypt file”:
cd /new/oracle/dir/
cd network/lib
make -f install
A single transaction can have multiple deletes and a single SCN number identifying all of these deletes. What if I want to flash back only a single individual delete?
You would flash back to the SYSTEM (not your transactions) SCN at that point in time. The SYSTEM has an SCN, your transaction has an SCN.  You care about the SYSTEM SCN with flashback, not your transactions SCN.
Are flash back queries useful for the developer or the DBA both? How can I as a developer and DBA get to know the SCN number of a transaction?
Oracle Flashback is a tool is useful for both either DBA and Developer. If you deleted data accidently then either DBA or Developer both can flashback, recover and fix this problem. As a developer you can use "dbms_flashback.get_system_change_number" to returns the current system SCN and as DBA you can use Log Miner utility to to look back in time at various events to find SCN's as well.
After Performing DML operation you are using flashback query to retun back your committed data can you use flashback concept after Truncating any data?
In version 9i, Flashback is limited to Data Manipulation Language (DML) commands such as SELECT,INSERT, UPDATE, and DELETE. Truncate doesn't generate any undo for the table truncate just cuts it all loses where as delete puts the deleted data into undo. Flashback query works on undo.


  1. eToro is the ultimate forex trading platform for novice and established traders.