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.***

Thursday, 5 December 2013

Oracle DBA interview Question with Answer Part 21

How to change SQL Prompt in Oracle?
Go to ORACLE_HOME\sqlplus\admin and copy the glogin.sql to some other place for backup purpose.
Now Edit glogin.sql and add these lines
set sqlprompt "_user '@' _connect_identifier > "
Now try to connect using sqlplus.
What is Oracle Node Eviction?
A node is evicted from the cluster after it kills itself because it is not able to service the applications. It generally happens during the communication failure between the instances, when the instance is not able to send the information to the control file.
Oracle Clusterware is designed to perform a node eviction by removing one or more nodes from the cluster if some critical problem is detected. The node eviction process is report with the error: ORA-29740 in the alert log and LMON trace files.
How to extend the VMware root disk (C: Drive) after OS installed.
·        Open VMware Infrastructure client and connect to Virtual Center or the ESX host.
·        Right-click the virtual machine.
·        Click Edit Settings.
·        Select Virtual Disk.
·        Increase the size of the disk.
Note: You can extend the root disk only when the Virtual Machine should SCSI disk. If this option display is gray then either the disk may be running on snapshots or the disk may be at the maximum allowed size depending on the block size of the data store or Disk Type is IDE. In that case first remove all the snapshots running on the VM or change the Disk Type from IDE to SCSI.
How to disable the firewall in Linux?
Stop the ipchains service:
# service ipchains stop
Stop the iptables service:
# service iptables stop
Stop the ipchains service from starting when you restart the server:
# chkconfig ipchains off
Stop the iptables service from starting when you restart the server:
# chkconfig iptables off
What are the methods to upgrade the database latest version? How would you decide the best method?
There are different ways of upgrading to the latest release of oracle database or oracle provides multiple methods to upgrade:
Database Upgrade Assistant (DBUA)
Manual Upgrade
Transportable Tablespace
Datapump or Export/Import
Oracle Streams
Oracle GoldenGate.
Using DBUA to upgrade the existing database is the simple and quickest method.
For step by step details: Upgrade Oracle Database 11g to 12c
Is it possible to connect oracle database if all of its BG process is killed?
Yes, you can connect to the database and also able to query with database view and other application schema views/tables.
Even you can update/select any record but when to try to commit/rollback; the instance gets terminated with the following error:
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 8917
Session ID: 63 Serial number: 9
And following error message recorded in database alert.log
Wed Jun 23 02:37:14 2013
USER (ospid: 8917): terminating the instance due to error 472
Instance terminated by USER, pid = 8917
The user (client) session was able to retrieve data from the database as the shared memory was still available and the client session does not need background process for this task.
What is the difference between Shared SQL and Cursor?
Shared SQL is the SQL residing in shared pool. The SQL statement can be shared among all the database sessions. The shared SQL is at the database level, where all session can see and use them.
A cursor points to some shared SQL in shared pool residing in the shared pool. You may have more than one cursor pointing to the same shared SQL. A cursor is at the session level, so many database sessions may point to the same shared SQL.
How will you find current and maximum utilization of process/session details?
Using the below SQL you can find current number of process and session details
Select resource_name, current_utilization, max_utilization from v$resource_limit where resource_name in ('processes', 'sessions');
Can you explain the difference between database and instance?
The database and instance are closely related but not the same things. The database is a set of files where application data and metadata is stored where as instance is a set of memory structure that oracle uses to manipulate the data in database.
A database can be mounted by more than one instance where as an instance can open at most one database.
What is difference between Translate and Replace?
Translate substitute character by character where as Replace is used to substitute a single character with a word.
How to schedule task in UNIX platform?
Use DBMS_JOB/DBMS_SCHEDULAR and give the job_type executable and provide the proper path name for shell script to execute.
Explain about dual table?
DUAL is built in relation in oracle which servers as a dummy relation to put in the FROM clause. The built in function SYSDATE returns a DATE value containing the current date and time on your system. For example: ‘SELECT 1+2 from DUAL’ where as ‘select sysdate from emp’ will return sysdate in all rows.
Which tables involved in producing start schema and the type of data they hold?
‘Fact’ and ‘Dimension’ table. The FACT table contains measurement while DIMENSION table will contain data that will help to describe the fact tables. The fact tables contain the real values that are going to be used as metrics. The dimension table is the one that classify and categorize the facts and help us to infer more info on the overall schema related scenarios.
What is the use of setting GLOBAL_NAMES = TRUE?
Setting GLOBAL_NAMES is indicating how you might connect to the database. The variable is either ‘TRUE’ or ‘FALSE’ and if it is true then it enforces database links to have the same name as the remote database to which they are linking.
Which background process refreshes materialized view?
The Job queue process.
What is rolling upgrade?
It is one of the ASM feature for database 11g. This enables to patch and upgrade ASM node in a clustered environment without affecting database availability. During a rolling upgrade we can maintain a functional cluster while one or more of the nodes in the cluster are running in different software version.
What is difference between startup Upgrade and startup Migrate?
Both are having the same effect (it will adjust few database parameters automatically to certain values in order to run upgrade script) the only difference is for oracle version. Startup Migrate is used to upgrade the database till oracle 9i. From 10g onwards we are using startup upgrade to upgrade the database.
Which oracle utility will be used to make useable to new ORACLE_HOME while you trying to move it from old ORACLE_HOME?
Re-link all
What do you mean by defining Quota on Tablespace?
Defining quota on tablespace means allotting amount of tablespace to the object in a schema for that particular tablespace.
How do you find used and free space in a Temporary Tablespace?
As we know unlike normal tableapace temporary tablespace information is not listed in v$datafile or dba_data_files instead you can query with v$tempfile or dba_temp_files.
Select * from v$tempfile;
Select * from dba_temp_files
SELECT tablespace_name, SUM (bytes_used)/1024/1024 "Used in MB", SUM (bytes_free)/1024/1024 "Free in MB"
FROM V$temp_space_header
GROUP BY tablespace_name;
Can we Upgrade database directly from 9i to 11g?
Yes, you can upgrade directly from 9i to 11g if your current database version is onwards. If you are having the same it is better to upgrade directly from 9i to 11g as oracle extended support for 10gR2 will ends on 31-Jul-2013 and also there are more features available in oracle 11g.
You can use any of the method to upgrade your database:
Manual Upgradation
Upgradation using DBUA
Using Export/Import
Using Data Copying.


  1. Hello sir,
    This will help me to tell my student what type of question asked in Database interwiev.
    Regard :
    Qadir Shaikh

  2. Dear Qadir,
    I already mention the general as well as technical question in this blog which may be asked by the interviewer generally in any interview.
    Tell him to go through all the question & answer one by one.
    Hope it will help.

    Shahid Ahmed

  3. Thank you for sharing thats Oracle Questions and Answers.

  4. After coming this blog, I have learned so many things. Thank you friend keep posting like this.
    Oracle DBA Online Training

  5. How it will affect on password file after upgrade from 10 g to 11v