Oracle DBA Question & Answer

Oracle DBA
1. SNAPSHOT is used for
[DBA] a] Synonym, b] Table space, c] System server, d] Dynamic data
replication Ans : D
2. We can create SNAPSHOTLOG for
[DBA] a] Simple snapshots, b] Complex snapshots, c] Both A & B, d]
Neither A nor B Ans : A
3. Transactions per rollback segment is derived from
[DBA] a] Db_Block_Buffers, b] Processes, c] Shared_Pool_Size, d] None
of the above Ans : B
4. ENQUEUE resources parameter information is derived from
[DBA] a] Processes or DDL_LOCKS and DML_LOCKS, b] LOG_BUFFER,
c] DB__BLOCK_SIZE.. Ans : A
5. LGWR process writes information into
a] Database files, b] Control files, c] Redolog files, d] All the
above. Ans : C
6. SET TRANSACTION USE ROLLBACK SEGMENT is used to create user
objects in a particular Tablespace
a] True, b] False Ans : False
7. Databases overall structure is maintained in a file called
a] Redolog file, b] Data file, c] Control file, d] All of the above. Ans : C
8. These following parameters are optional in init.ora parameter file DB_BLOCK_SIZE, PROCESSES a] True, b] False
Ans : False
9. Constraints cannot be exported through EXPORT command a] True, b] False
Ans : False
10. It is very difficult to grant and manage common privileges needed by different groups of database users using the roles a] True, b] False
Ans : False
33. What is a trigger
a] A piece of logic written in PL/SQL b] Executed at the arrival of a SQL*FORMS event c] Both A & B d] None of the above
Ans : C
34. Which of the folowing is TRUE for a ERASE packaged procedure
1] ERASE removes an indicated Global variable & releases the memory
associated with it 2] ERASE is used to remove a field from a page 1] Only 1 is TRUE 2] Only 2 is TRUE 3] Both 1 & 2 are TRUE 4] Both 1 & 2 are FALSE
Ans : 1
35. All datafiles related to a Tablespace are removed when the Tablespace is dropped
a] TRUE b] FALSE
Ans : B
36. Size of Tablespace can be increased by
a] Increasing the size of one of the Datafiles b] Adding one or more Datafiles c] Cannot be increased d] None of the above
Ans : B
37. Multiple Tablespaces can share a single datafile
a] TRUE b] FALSE
Ans : B
38. A set of Dictionary tables are created
a] Once for the Entire Database b] Every time a user is created c] Every time a Tablespace is created d] None of the above
Ans : A
39. Datadictionary can span across multiple Tablespaces a] TRUE b] FALSE
Ans : B
40. What is a DATABLOCK
a] Set of Extents b] Set of Segments c] Smallest Database storage unit d] None of the above
Ans : C
41. Can an Integrity Constraint be enforced on a table if some existing table data does not satisfy the constraint a] Yes b] No
Ans : B
42. A column defined as PRIMARY KEY can have NULL's a] TRUE b] FALSE
Ans : B
43. A Transaction ends a] Only when it is Committed b] Only when it is Rolledback c] When it is Committed or Rolledback d] None of the above
Ans : C
44. A Database Procedure is stored in the Database
a] In compiled form b] As source code c] Both A & B d] Not stored
Ans : C
45. A database trigger doesnot apply to data loaded before the definition of the trigger a] TRUE b] FALSE
Ans : A
46. Dedicated server configuration is
a] One server process - Many user processes b] Many server processes - One user process c] One server process - One user process d] Many server processes - Many user processes
Ans : C
47. Which of the following does not affect the size of the SGA
a] Database buffer b] Redolog buffer c] Stored procedure d] Shared pool
Ans : C
48. What does a COMMIT statement do to a CURSOR
a] Open the Cursor b] Fetch the Cursor c] Close the Cursor d] None of the above
Ans : D

49. Which of the following is TRUE
1] Host variables are declared anywhere in the program
2] Host variables are declared in the DECLARE section
a] Only 1 is TRUE b] Only 2 is TRUE c] Both 1 & 2are TRUE d] Both are FALSE
Ans : B
50. Which of the following is NOT VALID is PL/SQL
a] Bool boolean; b] NUM1, NUM2 number; c] deptname dept.dname%type; d] date1 date := sysdate
Ans : B
51. Declare fvar number := null; svar number := 5 Begin goto << fproc>> if fvar is null then << fproc>>
svar := svar + 5 end if; End; What will be the value of svar after the execution ?
a] Error b] 10 c] 5 d] None of the above
Ans : A
52. Which of the following is not correct about an Exception ?
a] Raised automatically / Explicitly in response to an ORACLE_ERROR b] An exception will be raised when an error occurs in that block c] Process terminates after completion of error sequence. d] A Procedure or Sequence of statements may be processed.
Ans : C
53. Which of the following is not correct about User_Defined Exceptions ?
a] Must be declared b] Must be raised explicitly c] Raised automatically in response to an Oracle error d] None of the above
Ans : C
54. A Stored Procedure is a
a] Sequence of SQL or PL/SQL statements to perform specific function b] Stored in compiled form in the database c] Can be called from all client environments d] All of the above
Ans : D
55. Which of the following statement is false
a] Any procedure can raise an error and return an user message and error number b] Error number ranging from 20000 to 20999 are reserved for user defined messages c] Oracle checks Uniqueness of User defined errors d] Raise_Application_error is used for raising an user defined error.
Ans : C
56. Is it possible to open a cursor which is in a Package in another procedure ? a] Yes b] No
Ans : A
57. Is it possible to use Transactional control statements in Database Triggers ? a] Yes b] No
Ans : B
58. Is it possible to Enable or Disable a Database trigger ? a] Yes b] No
Ans : A
59. PL/SQL supports datatype(s)
a] Scalar datatype b] Composite datatype c] All of the above d] None of the above
Ans C
60. Find the ODD datatype out
a] VARCHAR2 b] RECORD c] BOOLEAN d] RAW
Ans : B
61. Which of the following is not correct about the "TABLE" datatype ?
a] Can contain any no of columns b] Simulates a One-dimensional array of unlimited size c] Column datatype of any Scalar type d] None of the above
Ans : A
62. Find the ODD one out of the following
a] OPEN b] CLOSE c] INSERT d] FETCH
Ans C
63. Which of the following is not correct about Cursor ?
a] Cursor is a named Private SQL area b] Cursor holds temporary results c] Cursor is used for retrieving multiple rows d] SQL uses implicit Cursors to retrieve rows
Ans : B
64. Which of the following is NOT VALID in PL/SQL ?
a] Select ... into b] Update c] Create d] Delete
Ans : C
65. What is the Result of the following 'VIK'||NULL||'RAM' ?
a] Error b] VIK RAM c] VIKRAM d] NULL
Ans : C
66. Declare a number := 5; b number := null; c number := 10; Begin if a > b AND a < c then a := c * a; end if; End; What will be the value of 'a' after execution ? a] 50 b] NULL c] 5 d] None of the above Ans : C 67. Does the Database trigger will fire when the table is TRUNCATED ? a] Yes b] No Ans : B 68. SUBSTR(SQUARE ANS ALWAYS WORK HARD,14,6) will return a] ALWAYS b} S ALWA c] ALWAYS Ans : C 69. REPLACE('JACK AND JUE','J','BL') will return a] JACK AND BLUE b] BLACK AND JACK c] BLACK AND BLUE d] None of the above Ans : C 70. TRANSLATE('333SQD234','0123456789ABCDPQRST','0123456789') will return a] 333234 b] 333333 c] 234333 d] None of the above Ans : A 71. EMPNO ENAME SAL A822 RAMASWAMY 3500 A812 NARAYAN 5000 A973 UMESH 2850 A500 BALAJI 5750 Use these data for the following Questions Select SAL from EMP E1 where 3 > ( Select count(*) from Emp E2 where E1.SAL > E2.SAL ) will retrieve
a] 3500,5000,2500 b] 5000,2850 c] 2850,5750 d] 5000,5750
Ans : A
72. Is it possible to modify a Datatype of a column when column contains data ? a] Yes b] No
Ans B
73. Which of the following is not correct about a View ?
a] To protect some of the columns of a table from other users b] Ocuupies data storage space c] To hide complexity of a query d] To hide complexity of a calculations
Ans : B
74. Which is not part of the Data Definiton Language ? a] CREATE b] ALTER c] ALTER SESSION
Ans : C
75. The Data Manipulation Language statements are a] INSERT b] UPDATE c] SELECT d] All above
Ans : D
76. EMPNO ENAME SAL A822 RAMASWAMY 3500 A812 NARAYAN 5000 A973 UMESH A500 BALAJI 5750 Using the above data Select count(sal) from Emp will retrieve a] 1 b] 0 c] 3 d] None of the above
Ans : C
77. If an UNIQUE KEY constraint on DATE column is created, will it accept the rows that are inserted with SYSDATE ? a] Will b] Won't
Ans : B
78. What are the different events in Triggers ? a] Define, Create b] Drop, Comment c] Insert, Update, Delete d] All of the above
Ans : C

121. Sequence of events takes place while starting a Database is a] Database opened, File mounted, Instance started b] Instance started, Database mounted & Database opened c] Database opened, Instance started & file mounted d] Files mounted, Instance started & Database opened
Ans : B
122. SYSTEM TABLESPACE can be made off-line a] Yes b] No
Ans : B
123. ENQUEUE_RESOURCES parameter information is derived from
a] PROCESS or DDL_LOCKS & DML_LOCKS b] LOG BUFFER c] DB_BLOCK_SIZE d] DB_BLOCK_BUFFERS
Ans : A
124. SMON process is used to write into LOG files a] TRUE b] FALSE
Ans : B
125. EXP command is used a] To take Backup of the Oracle Database b] To import data from the exported dump file c] To create Rollback segments d] None of the above
Ans : A
126. SNAPSHOTS cannot be refreshed automatically a] TRUE b] FALSE
Ans : B
127. The User can set Archive file name formats a] TRUE b] FALSE
Ans : A
128. The following parameters are optional in init.ora parameter file DB_BLOCK_SIZE, PROCESS a} TRUE b] FALSE
Ans : B
129. NOARCHIEVELOG parameter is used to enable the database in Archieve mode
a] TRUE b] FALSE
Ans : B
130. Constraints cannot be exported through Export command? a] TRUE b] FALSE
Ans : B
131. It is very difficult to grant and manage common priveleges needed by different groups of database users using roles a] TRUE b] FALSE
Ans : B
132. The status of the Rollback segment can be viewed through
a] DBA_SEGMENTS b] DBA_ROLES c] DBA_FREE_SPACES d] DBA_ROLLBACK_SEG
Ans : D
133. Explicitly we can assign transaction to a rollback segment a] TRUE B] FALSE
Ans : A
134. What file is read by ODBC to load drivers ?
a] ODBC.INI b] ODBC.DLL c] ODBCDRV.INI d] None of the above
Ans : A