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

Saturday, 23 February 2013

DBA Interview Questions with Answer Part14

Why drop table is not going into Recycle bin?
If you are using SYS user to drop any table then user’s object will not go to the recyclebin as there is no recyclebin for SYSTEM tablespace, even we have already SET recycle bin parameter TRUE.
Select * from v$parameter where name = 'recyclebin';
Show parameter recyclebin;
How to recover password in oracle 10g?
You can query with the table user_history$. The password history is store in this table.
How to detect inactive session to kill automatically?
You can use the SQLNET.EXPIRE_TIME for the dead connections (for abnormal disconnections) by specifying a time interval in minute to send a problem message that verify client/server connections are active. Setting the value greater than 0 to this parameter ensures that connection is not left open indefinitely, due to abnormal client termination. If probe finds a terminated connection, or connection that is no longer in use, it returns an error, causing the server process to exit.
Why we need CASCADE option with DROP USER command whenever dropping a user and why "DROP USER" commands fails when we don't use it?
If a user having any object then ‘YES’ in that case you are not able to drop that user without using CASCADE option. The DROP USER with CASCADE option command drops user along with its all associated objects. Remember it is a DDL command after the execution of this command rollback cannot be performed.
Can you suggest the best steps to refresh a Database?
Refreshing the database is nothing but applying the change on one database (PROD) to another (Test). You can use import/export and RMAN method for this purpose.
Import/Export Method: If you database is small and if you need to refresh particular schema only then it is always better to use this method.
  1. Export the dump file from source DB
  2. Drop and recreate Test environment User.
  3. Import the dump to destination DB.
RMAN Method: Now days RMAN is most likely to be used for backup and recovery. It is relatively easier and better method for full database refresh to be refreshed. It is taking less time as compare to import/export method. Here also you can use particular SCN based refreshing.
export ORAENV_ASK='NO'
export NLS_LANG=American_america.us7ascii;
$ORACLE_HOME/bin/rman target / nocatalog log=/tmp/duplicate_tape_TEST.log connect auxiliary sys/PASSWORD@TEST;
allocate auxiliary channel aux1 device type disk;
set until SCN 42612597059;
duplicate target database to "TEST" pfile='/u01/app/xxxx/product/10.2.0/db_1/dbs/initTEST.ora' NOFILENAMECHECK;
How will we know the IP address of our system in Linux environment?
Either use ipconfig command or ip addr show
It will give you all IP address and if you have oracle 9i you can query from SQL prompt.
Can we create Bigfile Tablespace for all databases?
Infact your question do we create bigfile tablespace for every database is not clear for me. If you are asking can we create bigfile for every database?
Yes you can but it is not ideal for every datafile if your work is suitable for small file then why you create bigfile but if your mean is impact of bigfile that depends on your requirements and storage.
A bigfile tablespace is having single very big datafile which can store 4GB to 128 TB.
Creating single large datafile reducing the requirement of SGA and also it will allow you modification at tablespace level. In fact it is ideal for ASM, logical device supporting stripping.
Avoid using bigfile tablespace where there is limited space availability. For more details impact, advantage, disadvantage of bigfile on my blog.
Can you gice more explanation on logfile states?
“CURRENT” state means that redo records are currently being written to that group. It will be until a log switch occurs. At a time there can be only one redo group current.
If a redo group containing redo’s of a dirty buffer that redo group is said to be ‘ACTIVE’ state. As we know log file keep changes made to the data blocks then data blocks are modified in buffer cache (dirty blocks). These dirty blocks must be written to the disk (RAM to permanent media).
And when a redolog group contains no redo records belonging to a dirty buffer it is in an "INACTIVE" state. These inactive redolog can be overwritten.
One more state ‘UNUSED’ initially when you create new redo log group its log file is empty on that time it is unused. Later it can be any of the above mentioned state.
What is difference between oracle SID and Oracle service name?
Oracle SID is the unique name that uniquely identifies your instance/database where as the service name is the TNS alias can be same or different as SID.
How to find session for Remote users?
-- To return session id on remote session:
SELECT distinct sid FROM v$mystat;
-- Return session id of you in remote Environment:
Select sid from v$mystat@remot_db where rownum=1;
We have a complete cold Backup taken on Sunday. The database crashed on Wednesday. None of the database files are available. The only files we have are the taped backup archive files till Wednesday. Is there a possibility of recovering the database until the recent archive which we have in the tape using the cold backup.
Yes, if you have all the archive logs since the cold backup then you can recover to your last log
1) Restore all backup datafiles, and controlfile. Also restore the password file and init.ora if you lost those too. Don't restore your redo logs if you backed them up.
2) Make sure that ORACLE_SID is set to the database you want to recover
3) startup mount;
4) Recover database using backup controlfile;
At this point Oracle should start applying all your archive logs, assuming that they're in log_archive_dest
5) alter database open resetlogs;
How to check RMAN version in oracle?
If you want to check RMAN catalog version then use the below query from SQL*plus
SQL> Select * from rcver;
If you want to check simply database version.
SQL> Select * from v$version;
What is the minimum size of Temporary Tablespace?
1041 KB
Difference b/w image copies and backup sets?
An image copy is identical, byte by byte, to the original datafile, control file, or archived redo log file. RMAN can write blocks from many files into the same backup set but can’t do so in the case of an image copy.
An RMAN image copy and a copy you make with an operating system copy command such as dd (which makes image copies) are identical. Since RMAN image copies are identical to copies made with operating system copy commands, you may use user-made image copies for an RMAN restore and recovery operation after first making the copies “known” to RMAN by using the catalog command.
You can make image copies only on disk but not on a tape device. "backup as copy database;" Therefore, you can use the backup as copy option only for disk backups, and the backup as backupset option is the only option you have for making tape backups.
How can we see the C:\ drive free space capacity from SQL?
create an external table to read data from a file that will be as below
create BAT file free.bat as
@setlocal enableextensions enable delayedexpansion
@echo off
for /f "tokens=3" %%a in ('dir c:\') do (
set bytesfree=%%a
set bytesfree=%bytesfree:,=%
echo %bytesfree%
endlocal && set bytesfree=%bytesfree%
You can create a schedular to run the above free.bat, free_space.txt inside the oracle directory.
Differentiate between Tuning Advisor and Access Advisor?
The tuning Advisor:
        It suggests indexes that might be very useful.
        It suggests query rewrites.
        It suggests SQL profile
The Access Advisor:
        It suggest indexes that may be useful
        Suggestion about materialized view.
        Suggestion about table partitions also in latest version of oracle.
How to give Access of particular table for particular user?
The Below command checks the SELECT privilege on the table PAY_PAYMENT_MASTER on the HRMS schema (if connected user is different than the schema)
What are the problem and complexities if we use SQL Tuning Advisor and Access Advisor together?
I think both the tools are useful for resolving SQL tuning issues. SQL Tuning Advisor seems to be doing logical optimization mainly by checking your SQL structure and statistics and the SQL Access Advisor does suggest good data access paths, that is mainly work which can be done better on disk.
Both SQL Tuning Advisor and SQL Access Advisor tools are quite powerful as they can source the SQL they will tune automatically from multiple different sources, including SQL cache, AWR, SQL tuning Sets and user defined workloads.
Related with the argument complexity and problem of using these tools or how you can use these tools together better to check oracle documentation.


  1. nice work

    For more ORACLE Database Interview Questions and Answers visit:DBA interview questions

  2. I have just downloaded iStripper, and now I can watch the sexiest virtual strippers on my desktop.