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 beyond doubt a blog significant to follow. You’ve dig up a great deal to say about this topic, and so much awareness. I believe that you recognize how to construct people pay attention to what you have to pronounce, particularly with a concern that’s so vital. I am pleased to suggest this blog.
    python training in tambaram | python training in annanagar | python training in jayanagar

  8. Nice post. By reading your blog, i get inspired and this provides some useful information. Thank you for posting this exclusive post for our vision. 
    Online DevOps Certification Course - Gangboard
    Best Devops Training institute in Chennai

  9. Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog.

    rpa training in velachery| rpa training in tambaram |rpa training in sholinganallur | rpa training in annanagar| rpa training in kalyannagar

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

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

  12. I read this post two times, I like it so much, please try to keep posting & Let me introduce other material that may be good for our community.

    devops online training

    aws online training

    data science with python online training

    data science online training

    rpa online training

  13. Well somehow I got to read lots of articles on your blog. It’s amazing how interesting it is for me to visit you very often.
    Microsoft Azure online training
    Selenium online training
    Java online training
    Python online training
    uipath online training

  14. Your info is really amazing with impressive content..Excellent blog with informative concept. Really I feel happy to see this useful blog, Thanks for sharing such a nice blog..
    If you are looking for any python Related information please visit our website Python Training In Bangalore page!

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

  16. IEEE Final Year projects Project Centers in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes. IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble.Final Year Projects for CSE

    Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining .

    Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai

    The Angular Training covers a wide range of topics including Angular Directives, Angular Services, and Angular programmability.Angular Training