Sql Server Interview Questions Part -2

DBA QUESTION BANK CATEGORY: 5+ YAERS Objective Questions 1.What are the various ways to defragment an Index? A. Rebuild B. Analyze C. Rename D. Drop and create Ans : A , D 2.What is the impact of pctused and freelists when segment space management is auto while creating a segment? A. Parameters are taken into consideration B. Parameters are ignored C. Syntax gives an error D. None of above Ans : B 3.How can one delete archived log files? (Choose the best answer) A. Using SQL prompt B. Using RMAN C. Using OS command D. Using OEM Ans : B 4. Which are the correct oracle naming methods (Choose all that apply) A. Local naming B.Global naming C.Directory naming D.Server naming Ans : A 5.When is the best time to tune a database application? A. At Development time B. At Design time C. Before going live D.When the problems are experienced

Ans : B 6.You plan to execute UTLBSTAT and UTLESTAT twice. What do you need to do between the First and the second run to prevent the output from being overwritten? A. Rename the output file after the first run B. ALTER SYSTEM SET NUMBER_STATISTICS=2 C Set the initialization parameter NUMBER_STATISTICS 2 D.Use two different names for the output file. Ans : A 7.Which of the following approaches can the DBA take to improve the hit statistics for the library cache A. Increase the DB_BLOCK_BUFFERS B. Increase the SHARED_POOL_SIZE C. Use as many identical SQL statements in the application as possible. D. Issue long running SQL statements first. Ans : C 8.How does one calculate the database hit ratio A. Physical_reads/Logical_reads B. 1- (Logical_reads/Physical_reads) C. Logical_reads/Physical_reads D. (Logical_reads – Physical_reads)/Logical_reads Ans: D ANSWERS:1.(A),(D)2.(B) 3.(B) 4.(A),(C) 5.(B) 6.(A) 7.(B),(C) 8.(D) Descriptive Questions:

1.Mention the steps to configure RMAN using catalog Database? Step1-In catalog Database create user with default tablespace and temporary tablespace Step2Grant connect, resource and recovery_catalog_owner roles to that user Step3Connect to recovery catalog and target database Step4Execute create catalog command

Step5Register the database 2. (On UNIX) when should more than one DB writer process be used? How many should be used? If the UNIX system being used is capable of asynchronous IO then only one is required, if the system is not capable of asynchronous IO then up to twice the number of disks used by Oracle number of DB writers should be specified by use of the db_writers initialization parameter. 3. When is cost based optimization triggered? It's important to have statistics on all tables for the CBO (Cost Based Optimizer) to work correctly. If one table involved in a statement does not have statistics, Oracle has to revert to rule-based optimization for that statement. 4.Explain the difference between SHARED SERVER and DEDICATED SERVER configuration. In dedicated server connection request is received by listener and passed on to dedicated server process. Later on user process and server process communicates with each other. In shared server configuration listener receives request from user process and passes it to least loaded dispatcher. Dispatcher puts request to request queue in SGA. Shared server process picks up the request from request queue and executes the same with server. Response received by server process is kept into dispatcher specific response queue. Dispatcher passes that response to user process. Few server process works for many user process in case of shared server configuration to save memory kind of resources.