Wednesday, December 29, 2010

Database Interview Questions - Basics I


Q 1: What are the steps involved in database startup?
Ans: Start an Instance, Mount the database and Open the Database.

Q2: What are the steps involved in database shutdown?
Ans : Close the database ; Dismount the database and Shutdown the instance.

Q3: What is Archived Redo log?
Ans: Acrhived Redo Log consists of Redo Log files that have archived before being reused

Q4: What is a temporary segment?
Ans: 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.

Q5: What does a control file contain?
Ans: A control file records the physical structure of the database, database name,time stamp of database creation,max datafile limit,max redo log group,max log file members, database archive information,backup information..

Q6:What is a Tablespace?
Ans: A database is divided into Logical storage unit called tablespace. A tablespace used to grouped related logical structures together

Q7: Explain the relation ship among the database,tablespace and datafiles?
Ans: Each database logically divided in to one or more tablespaces. One or more datafiles are explicitly created for each tablespace.

Q8:What is Logical structure of the database?
Ans: Database àTablespacesàsegmentsàExtentsàOracle Blocks

Q9: What is the physical structure of the database?
Ans: Datafiles,Control File and redo log files.

Q10: What are Different types of segments?
Ans: Temp Segement, Undo Segment, Table Segment and Index Segment.

Q11: Can you name few DBMS Package names and their use?

Q12: How Do you find whether the instance was started with PFILE or SPFILE?
Ans: Sho parameter spfile or query from v$parameter.

Q13: What Column differentiates the v$views to the gv$views and how?
Ans: The INST_ID Column which indicates the instance in a RAC environment the information came from ..

Q14: Explain an Ora -1555
Ans: You get this error when you get a snap shot too old within roolback.It can usually be solved by increasing the undo retention or increasing the size of rollbacks

Q15:What is Diffeernce between a temporary tablespace and a permanent tablespace?
Ans: A temporary tablespace is used for Sort Oprations of no sufficient space found in PGA. Permanent tablespace stores the true objects of dataase ..

Q16:How Do you add a datafile to a tablespace?
Ans: alter tablespace add datafile size ;

Q17: How Do you resize a datafile?
Ans: alter database datafile resize ;

Q18: What view Would you use to look at th size of a datafile?
Ans: dba_data_files,V$datafile

Q19: What view Would you use to determine free space in a tablespace?
Ans: dba_free_space

Q20: How can you gather statistics on a table?
Ans: The analyze command or using DBMS_STATS package.

Q21: List out the views names we can use to find about undo usage?
Ans: V$undostat,dba_undo_segs

Q22: What is view we can use to find out database locks?
Ans: v$lock

Q23: Using which view will check for session and process details?
Ans: V$session,v$process

Q24: Using which view will you find out , how much % Operation or job completed sofar it is running in database?
Ans: v$session_longops

Q25: How will you find out running job names from database?
Ans: Querying from dba_jobs,Dba_jobs_running views

Q26:How will find out Database creation time details? What is the column name?
Ans: v$database; created is column

Q27: How will you find out instance startup time?
Ans: v$instance,startup_time

Q28: How do you find archive is enabled for database?
Ans: archive log list or v$database ,log_mode Cloumn

Q29: How will you find active/inactive session count from database?
Ans: select status,count(*) from v$session group by status;

Q30: explain about temporary tablespace groups?
Ans: It is 10g new feature . it is a group of temporary tablespaces. We can get more details from dba_tablespace_groups if it is enabled.

Q31: What is undo_retention?
Ans : Time specification for undo segments hold the data ..and these reusable are reusable..

Q32: How will you find undo management is auto or manual?
Ans : Sho parameter undo_management

Q33: If your database contain 4 undo will you find out which undo tablespace is used by database?
Ans: Sho parameter undo_tablespace

Q34: If you database contain 5 temporary tablespaces how will you find out what is default temporary tablespace to databse?
Ans: By querying database_properties

Q35. What is alert log file and how will you find out the location of file from database?
Ans : Sho parameter dump


Q36: How can you determine if an Oracle Instance is up from the Operating system level?
Ans: ps –efgrep smon/pmon

Q37: How can u find dead processes?
Ans: ps –efgrep zombie or who –d or ps –efgrep defunc

Q38: Give the command to display space usage on the UNIX File system?
Ans: df –lk or df –lh or df –k/h

Q39: Explain sar and vmstat?
Ans: sar used for to get system activity report and Vmstat reports on virtuval memory statistics for processes .

Q40: How do you find the Load average of a server?
Ans: using uptime command.

Q41: How do you find out swap usage on a server?
Ans: free –g

Q42: How will you kill a process id at OS level ?
Ans: kill -9

Q43: How will you find out the count of Oracle processes running on server?
Ans: ps –efgrep wc –l

Q44: How will you check if any Rman backup is running?
Ans: ps –efgrep rman

Q45: How will check cron schedule job details?
Ans: Crontab –l

Q46 : How will find ASM instance is running on server?
Ans : ps –efgrep +ASM

Q47 : I have a very big file at OS and I want see last 50 lines of the file only..Which command will you use?
Ans : tail -50

Q48: How can you find , file last accessed ( modified )date?
Ans : ls –l Q49: How will you find out howmany instances are running on a server?
Ans : ps –efgrep smon (gives all instance names which are running)

Q50 : What is the command we can use to find trace of process at OS level?(means what process is doing)
And : strace –p


  1. Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end.
    Selenium Training in ChennaiAWS Training in ChennaiData Base Developer Training in Chennai

  2. Your new valuable key points imply much a person like me and extremely more to my office workers. With thanks from every one of us.

    Best AWS Training in Chennai | Amazon Web Services Training in Chennai


  3. This is quite educational arrange. It has famous breeding about what I rarity to vouch. Colossal proverb. This trumpet is a famous tone to nab to troths. Congratulations on a career well achieved. This arrange is synchronous s informative impolites festivity to pity. I appreciated what you ok extremely here 
    Click here:
    angularjs training in sholinganallur
    Click here:
    angularjs training in btm
    Click here:
    angularjs training in rajajinagar
    Click here:
    angularjs training in marathahalli
    Click here:
    angularjs training in bangalore
    Click here:
    angularjs training in pune

  4. It has been simply incredibly generous with you to provide openly what exactly many individuals would’ve marketed for an eBook to end up making some cash for their end, primarily given that you could have tried it in the event you wanted.

    digital marketing training in tambaram

    digital marketing training in annanagar

    digital marketing training in marathahalli

    digital marketing training in rajajinagar

    Digital Marketing online training


  5. * Write more; that’s all I have to say. It seems as though you relied on the video to make your point. You know what you’re talking about, why waste your intelligence on just posting videos to your blog when you could be giving us something enlightening to read?

    MEAN stack training in Chennai

    MEAN stack training in bangalore

    MEAN stack training in tambaram

    MEAN stack training in annanagar

    MEAN stack training in Velachery

  6. Great post! I am actually getting ready to across this information, It’s very helpful for this blog.Also great with all of the valuable information you have Keep up the good work you are doing well.
    Devops training in velachry
    Devops training in OMR
    Deops training in annanagar
    Devops training in chennai
    Devops training in marathahalli
    Devops training in rajajinagar
    Devops training in BTM Layout

  7. This is most informative and also this post most user friendly and super navigation to all posts... Thank you so much for giving this information to me.. 
    Java training in Marathahalli | Java training in Btm layout

    Java training in Jaya nagar | Java training in Electronic city

  8. Nice post, Thanks for sharing this with us.This information is very useful for the interview.For more information.
    aws online training
    aws training in hyderabad
    amazon web services(AWS) online training

  9. Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end.
    AWS training in chennai | AWS training in annanagar | AWS training in omr | AWS training in porur | AWS training in tambaram | AWS training in velachery