ORACLE INTERVIEW QUESTIONS

  1. List out 10 memory level parameters
  2. Which commands leads to sorting? What memory area is used for sorting ? How will you increase the performance of sorting ? Name the parameters?
  3. what will happen if you give commit? What are the wait events that affect log buffer?
  4. How will you increase the size of library cache? What is hard and soft parse?
  5. What is checkpoint?How will reduce the checkpoint frequency?
  6. Explain the steps that involved when bringing up the database? How to drop the database? how to change the mode of database?
  7. Explain instance recovery? Who does instance recvoery? Where will you check whether the instance recovery happened or not? What files were involved in the Instance recovery?
  8. Who read the data into memory? How will you increaase the performance of write from DBBC to datafiles?
  9. What is inittrans, maxtrans. what is the default values for table and index?
  10. what is logging ad nologging? What is use of pctfree/pctused and pct increase?
  11. How will you find fragmentation? How will you rectify it?
  12. What is row chain and row migration? How will you find it and rectify it?
  13. Why we should analyze a table?How to check if the table/index is analyzed? What is the command to gather stats/analyze?
  14. What is differnce between?
    • spfile/pfile
    • pinned buffer/dirty buffer
    • latch /enqueue
    • LMT/DMT
    • btree/bitmap
  15. When and how to rebuild index?
  16. What are the types of partitions? Explain composit partitons?
  17. What is different between system/hash partition?
  18. what is partiton PRUNING?
  19. How will you check the following?
    • Table/index/view size
    • free space in database/tablespace
    • used space in database/tablespace
    • how many indexes present for a table? check indexes columns and its positon? on what function the index is created?
    • How many constraints are there in the table and on what columns? What are the constraint types?
  20. What are the types of grants ? Difference between GRANT/ADMIN Option?
  21. what is password verification function? Steps to implement it.
  22. Will discuss about profiles? Try to learn Password Resources
  23. what is snapshot too old error?
  24. what is different between lock and deadlock?
  25. How will you multiplex controlfile and logfile? How to increase the size of logfiles and datafiles?
  26. What is the use of UNDO tablespace? How to hange the name of undo tablespace and location?
  27. What is the use of temp tablespcae? how to change the location of temp tablespace?
  28. Steps to create dbms scheduler? How to check the failed job / status?
  29. What is use of flashback query /version/transaction/table?
  30. What is UMASK?
  31. How will find available port ?
  32. How to delete files that are older than 10 days?
  33. Explain the logic for monitoring alert.log , filesystem check.
  34. How will you pass arguments to a shell scrits? How will you call them inside the shell?ow to check how many number of arguments passed and list out them?
  35. How to check the previos command that was execute is successful?
  36. How to check the following?
    • ipaddress
    • servername
    • OS release
    • CPU count
    • Ram size
    • users groupname
    • CPU utilization
    • IO utilization
    • virtual memory utilization
  37. Which command is used to find the process that was locking/using the file?
  38. which command is used to find the user who is using the file?
  39. How to change the ownership of a file?
  40. Prerequisite to install oracle on Linux? Expain kernel parameters.
  41. Steps to configure silent mode installation?
  42. Expain PATH, ORACLE_HOME,ORACLE_BASE,DISPLAY variables
  43. How to create database using dbca using putty with GUI termical.
  44. What is OMF? How will you use it?
  45. what is oratab? What is the use of it?
  46. Steps to configure automatic start and shut options? What is the use of chkconfig command?
  47. what will happen if you execute root.sh?
  48. What is rpm? How will you install rpm? How to check the existance of rpm?
  49. Explain all the linux compression commands?
  50. Explain crontab and its parameters?
  51. What is logical and physical backup? hot and cold backup?
  52. Explain the internals of hot backup in UMB?
  53. Explain the steps to take hot backup in UMB?
  54. how to recover a system datafile/ non system datafile that is running in a Production env?
  55. how to recover a UNDO datafile/ tempfile that is running in a Production env?
  56. How to recover a corrupted logfile,controlfile,spfile
  57. How to take a backup using rman and location should be in "/backup"
  58. How to increase the performance of RMan backup and explain it advantages over UMB?
  59. What is the use of crosscheck , obsolete, expired commands
  60. Difference between differential and cumulative backup?
  61. What is the use of BCT?
  62. How to increase the performance of RMAN nackup?
  63. How will recover a table sing RMAN? Explain the stepswith commands?
  64. Whats is schema refresh?what are the post refresh activities after schema refresh?
  65. Given a exp/expdp full dump (with out export logfile) how will you create a database with it?
  66. What is the use of compress , consistent for exp?
  67. What is teh performance parameters for exp/imp/expdp/impdp?
  68. What are the performance parameters for SQL loader?
  69. How will you take export of 20 tables? explain steps with commands?
  70. Given a 100gb table dump, but having 5 gb of undo, how will you import the table?
  71. On which oracle utility you will get snapshot too old error?
  72. What is use of resumable_timeout parameter in DB as well as EXP/expdp?
  73. How will you do schema refresh without removing grants from target schema?
  74. How to export set of record from a table?
  75. A datapump export is running with parllel=2 . Now you are asked to increase the parallel=4 from another terminal, how will you do it?
  76. Having the DB size ad 600GB. How will you take a database dumpfiles and copy that to another server. Explain the steps
  77. Is it possible to do a schema copy to another db with out dumpfile ?
  78. Explain the steps to transport tablespace?
  79. What is the advantage of datapump over exp/imp?
  80. Need to export a database using exp from putty even we get a network issue.
  81. Import a schema which is in tablespace USERS to another schema which is in tablespace USERS2.
  82. how to goback to client mode from interactive mode in datapump?
  83. How will you trouble shoot a client server connetion issue? Expplain all the possible issues?
  84. If listerner is down, what will happen to the existeing connetion? what will happen to new connection?
  85. How to check how many listeners are running on the server? How tio check their status?
  86. Is it possible to change the listener and tnsnames location ? If so how to do that?
  87. Expain the steps to recover a database using a backup from a location?
  88. Explain teh steps Rman cloning and active cloning?
  89. Can we have 2 listeners for a single database and 1 listener for multiple database? If so How?
  90. How to register a database to a listener?
  91. How to setup a recover catalog?explain with steps?
  92. Find the user who is locking a table?
  93. How to find if a query is using index?
  94. What are the possible chances that a index is not used by a query?How to force a index to be used?
  95. How to intrepret execution pan?
  96. If a user reports that a query hangs , how willyou trouble shoot?
  97. If a query run yesterday for 2 mins and it takes more tha 1 hr, what might be the possible issue?
  98. How will you kill a session even after it marked for killing in DB?
  99. How will you check how many plans exists for a query?
  100. How to check the duration of current running SQLS?
  101. How to get the execution plan for a Query?
  102. How will you trace a session? explain the steps? What will you check in it?
  103. What is AWR? When will you go for AWR? how to take it and what will you look in to it?
  104. Name some wait events (atleast 10) and explain hot to resolve it?
  105. What is ASH? Give the views related to ASH?
  106. Explain joining methods with examples.
  107. Name some hints (10 hints)and explain when that will be used?
  108. How to make the query not to a the index.
  109. What is the prerequisite to do upgrade / patching?
  110. How to check the conflict of a patch?
  111. How to install / rollback a patch?
  112. How to list the patches installed? What does ./datapatch -verbose do?
  113. What are the type of patches?
  114. what is opatch and opatchauto?
  115. How will you monitor while patching? Where the logs will be stored?
  116. How to check how many users are connect to database from linux command?
  117. How will remove kernal memory (shared/semaphores) from unix?
  118. How will estimate the remaining time of running rman backup / gather stats.
  119. What is a database?
  120. Explain about an instance and storage.
  121. What is the use of SGA, and what are the components of the SGA?
  122. What is the difference between SGA and PGA, and when are they used? Give an example query.
  123. Explain about DBBC, RLBC, and the Shared Pool. What is the default size of the Shared Pool?
  124. In case the Shared Pool has no free space, then from where does it take memory?
  125. What is the difference between soft parsing and hard parsing?
  126. What is the use of the Result Cache and Reserved Pool?
  127. What is the use of the Server Process, and what are its types?
  128. What are the mandatory background processes in Oracle?
  129. How do you check whether processes are running or not?
  130. Explain about DBWR.
  131. What is the use of the ARCH, SMON, and PMON processes?
  132. Explain about checkpoints. When do checkpoints occur, and what are the types of checkpoints?
  133. What are the full forms of SCN and LSN?
  134. Explain about the Stream Pool, Java Pool, and Large Pool.