Oracle Database Test

1. What does the acronym RDBMS mean?

Answers:

• Relational Database Management System

• Rational Database Management System

• Ratio Database Management System

• Rational Database Manager System

• Relational Database Manager System

2. What will the shortcut "Ctrl + F11" do?

Answers:

• Exit

• Update Record

• Clear Record

• Execute Query

• Enter Query

3. How many columns are there in a Dual table?

Answers:

• 9

• 8

• 1

• 7

4. A rollback segment can be specified as either PUBLIC or PRIVATE. The value of PCTINCREASE of a rollback segment is always set to:

Answers:

• 2

• 4

• 6

• 0

5. In business, what is the most common definition for CRM?

Answers:

• Customer Relationship Marketing

• Client Relationship Management

• Customer Resource Management

• Customer Relationship Management

• Contact Relationship Marketing

6. You need to search for text data in a column, but you only remember part of the string. Which of the following SQL operations allows the use of wildcard comparisons?

Answers:

• EXISTS

• LIKE

• BETWEEN

• IN

7. In terms of Oracle, what is the most common meaning of OEM?

Answers:

• Oracle Equipment Model

• Original Equipment Manufacturer

• Oracle Enterprise Manager

• Oracle Entry Manager

• Order Entry Module

8. SQL stands for:

Answers:

• Standard Query Language

• Symmetric Query Language

• Structured Query Language

• Sequential Query Language

9. In business, what is the most common definition for ERP?

Answers:

• Enterprise Resource Planning

• Employee Referral Program

• Early Retirement Plan

• Electronic Resource Planning

• Electronic Resource Pricing

10. What clause follows "group by" in SQL?

Answers:

• When

• Not in

• In

• Having

11. The DBA assigns permission to the user using this command:

Answers:

• Table

• Lock

• Role

12. What is the purpose of the Rollback command?

Answers:

• Save the last uncommitted transactions

• Undo the last uncommitted transactions

• Redo the last uncommitted transactions

• Maintains Log of the last uncommitted transactions

13. Which of the following is not DML (Data Manipulation Language)?

Answers:

• Rename

• Update

• Insert

• Delete

14. Which of the following is(are) physical storage file(s) of Oracle?

Answers:

• Control files

• Data files

• All of these

• Redo log files

15. Define Index:

Answers:

• Ordered form of data

• Create another column

• Random form of data

• Create another table

16. True or False: "Select for Update" performs the function "Lock the record on result set."

Answers:

• False

• True

17. SQL statement operator that tests whether a field value is unavailable, unassigned or unknown is:

Answers:

• = NULL

• IS NULL

• IS NOTHING

• IS EMPTY

18. Which of the following are system privileges?

Answers:

• SELECT and DELETE

• CREATE TABLE and DROP TABLE

• DELETE and UPDATE

• CREATE TABLE and SELECT

19. Which of the following operators can be used to substitute the 'IN' operator in a SELECT statement?

Answers:

• AND

• >=

• BETWEEN ... AND

• <=

20. The transaction control that prevents more than one user from updating data in a table is which of the following (choose one)?

Answers:

• rollback

• savepoint

• commit

• lock

21. DBMS_SCHEDULER, along with the power of PL/SQL, provides a mechanism for automating some of the daily tasks. Which of the following is an advantage of using  DBMS_SCHEDULER?

Answers:

• Makes your environment the same across the enterprise

• Is capable of running a program, anonymous PL/SQL blocks, stored procedure, executables or even a chain of commands

• All of these

• Is relatively easy to grant or revoke access for each specific job to other user

22. Remote access of database can be made by:

Answers:

• PMON

• SQL star plus

• SGA

• Database Link

23. Which of these is not a language element of SQL?

Answers:

• Clauses

• Statements

• None. All are language elements of SQL.

• Queries

• Expressions

24. In a sequence, Curval returns:

Answers:

• Zero

• More than current value

• Present Value

• Less then current value

25. The source code of procedure, function and package bodies can be queried from which  data dictionary?

Answers:

• ALL_SOURCE

• DBA_SOURCE

• USER_SOURCE

• All of these

26. Which of the following statements is true about rebuilding indexes?

Answers:

• All of these

• They can be rebuilt online

• Performed by issuing the command ALTER INDEX

• Rebuilding indexes doesn't need a lock on the table

27. What view would you use to look at the size of a data file?

Answers:

• DBA_DATA_FILES

• FREE_DATA_FILES

• FILES_DBA_SPACE

• DBA_FREE_SPACE

28. Which of these is not a real Oracle suite option?

Answers:

• Oracle E-Business Suite

• Oracle Application Server

• Oracle Developer Suite

• Oracle Customer Service Suite

• Oracle Enterprise Manager

29. Which naming rules are applied to tables?

Answers:

• All of these

• Start with a letter

• Can have the same name as any database object if that object is not owned by the same user

• Can contain characters such as $, _ and #

30. Which statement about views are true?

Answers:

• A view must have aliases defined for the column names in the SELECT statement.

• A view cannot be created with a GROUP BY clause in the SELECT statement.

• A view cannot have an ORDERBY clause in the SELECT statement.

• A view can be created as a join on two or more tables.

31. Which syntax turns an existing constraint on?

Answers:

• ALTER TABLE table_name TURN ON CONSTRAINT constraint_name;

• ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

• ALTER TABLE table_name ENABLE constraint_name;

• LTER TABLE table_name STATUS ENABLE CONSTRAINT constraint_name;

32. Which of the following statement correctly decribes how to rebuild an index?

Answers:

• ALTER index_name REBUILD

• index_name REBUILD

• ALTER INDEX index_name REBUILD

• ALTER INDEX REBUILD index_name

33. A block is the smallest unit of logical storage that the Relational Database Management System (RDBMS) can manipulate. Block size is determined by which of the following database parameter?

Answers:

• DB_SYSTEM_CLUSTER_SIZE

• DB_BLOCK_SIZE

• DB_SYSTEM_BLOCK_SIZE

• DB_CLUSTER_SIZE

34. REPLACE ('CUCKOO AND CARLIE', 'C', 'CH') will return:

Answers:

• CUCKOO AND CARLIE

• CHUCHKOO AND CARLIE

• CHUCKOO AND CHARLIE

• CHUCHKOO AND CHARLIE

35. Regarding Oracle, what does DES most commonly mean?

Answers:

• Data Entry Sheet

• Digital Encryption System

• Data Extraction Segment

• Data Encryption Standard

• Dynamic Energy Saver

36. Which one of the following is not a DDL (Data Definition Language) command?

Answers:

• Drop

• Create

• Update

• Alter

37. What is the definition of data buffer cache?

Answers:

• An area in SGA taht contains the database checkpoints

• An area that is used to allocate the I/O buffers from shared memory

• Circular buffer that holds information about changes made to the database

• An area in SGA that is used to store the most recently used data block

38. Which data dictionary table should you query to view the object privileges granted to the user on specific columns?

Answers:

• USER_COL_PRIVS_MADE

• USER_COL_PRIVS

• USER_TAB_PRIVS_MADE

• USER_TAB_PRIVS

39. In Oracle "view"  consists of:

Answers:

• The output of the table

• Indexed output of the table

• The definition only

• The whole table

40. Which /SQL*Plus feature can be used to replace values in the WHERE clause?

Answers:

• Replacementvariables

• Substitution variables

• This feature cannot be implemented through /SQL*Plus.

• Instead-of variables

41. "Select for Update" performs:

Answers:

• Lock the record on result set

• Unlock the record on result set

• Update the record set

• Select the record and deelete

42. The Oracle Certification Program contains three levels. Which of these is not one of them?

Answers:

• Oracle Certified Professional (OCP)

• Oracle Certified Developer (OCD)

• Oracle Certified Master (OCM)

• Oracle Certified Associate (OCA)

43. When a user process fails, this background process will clean up after it:

Answers:

• SESSIONMON

• PMON

• WRAP

• SQLMON

44. What command is used to restore an  "accidentally" dropped table?

Answers:

• alter database cancel drop <tablename>

• rollback to segment

• rollback table <tablename> to before drop

• flashback table <tablename> to before drop

45. Order the following entities from the smallest to the largest:

Answers:

• Segments, extents, data blocks, tablespaces

• Extents, data blocks, segments, tablespaces

• Table spaces, data blocks, extents, segments

• Data blocks, extents, segments, table spaces

46. The database administrator of your company created a public synonym called HR for the HUMAN_RESOURCES table of the GENERAL schema, because many users frequently use this table. As a user of the database, you created a table called HR in your schema. What happens when you execute this query?  select * from HR

Answers:

• You obtain the results retrieved from the public synonym HR created by the database administrator

• You obtain the results retrieved from the HR table that belongs to your schema.

• You obtain the results retrieved from both the public synonym HR and the HR table that belongs to your schema, as a Cartesian product.

• You get an error message because you cannot retrieve from a table that has the same name as a public synonym

47. What is true about this set of statements? CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13)); ROLLBACK; DESCRIBE DEPT;

Answers:

• The ROLLBACK statement frees the storage space occupied by the DEPT table.

• The DESCRIBE DEPT statement displays the structure of the DEPT table.

• The DESCRIBE DEPT statement returns an error ORA-04043:object DEPT does not exist

• The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.

48. The "materialized view" in Oracle stores:

Answers:

• Output in logical space

• No output only the definition

• Output in different physical space

• No output

49. Which of the following actions are performed by the MINUS operator?

Answers:

• Returns the result of one numeric value minus another.

• Returns all of the columns for the tables specified in the FROM clause minus those specified after the MINUS operator.

• Returns the output of top query minus the output of the query below the MINUS operator.

50. You need to give the MANAGER role the ability to select from, insert into, and modify existing rows in the STUDENT_GRADES table. Anyone given this MANAGER role should be able to pass those privileges on to others. Which statement accomplishes this?

Answers:

• GRANT select, insert, update ON student_grades TO manager;

• GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION;

• GRANTselect,insert,modify ON student_grades TO manager WITH GRANT OPTION;

• .GRANT select, insert, modify ON student_grades TO ROLE manager WITH GRANT OPTION;

51. The IN operator is used when you are using a subquery which returns more than one record. What is the operator used in a correlated subquery?

Answers:

• None of these

• UNION

• EXISTS

• INTERSECT

52. What operator would you choose to prevent this Oracle error message? ORA-01427:single -row subquery returns more than one row

Answers:

• Use the IN operator

• Use the >= operator

• Use the = operator

• Use the <= operator

• Use the CAN EXIST operator

53. Which of these statement is NOT true?

Answers:

• A tablespace is a logical unit of database storage formed by one or more data files

• A datafile can be associated with one or more database

• A database has at least one datafile

• Control file is used to identify the data files and the redo log files

54. How would you display a listing of the sums of employee salaries for those employees not making a commission, for each job type, including only those sums greater than 2500?

Answers:

• none of the above

• select job, sum(sal) from emp where sum(sal) > 2500 and comm is null;

• select job, sum(sal) from emp group by job having sum(sal) > 2500 and comm is not null;

• select job, sum(sal) from emp where comm is null group by job having sum(sal) > 2500;

• select job, sum(sal) from emp where sum(sal) > 2500 and comm is null group by job;

55. Which of these is NOT an Oracle "database option?"

Answers:

• Oracle Real Application Testing

• Database Vault

• Active Data Guard

• Oracle Questions

• Total Recall

56. This protects the library cache from becoming corrupted by concurrent modifications by two sessions or by one session trying to read information that is being modified by another one.

Answers:

• Pitch

• Latch

• Power lock

• Cache

57. Which of these programs is the most popular choice to load data directly into Oracle E-Business Suite?

Answers:

• Cognos

• DataLoad

• Access

• Forms Data Loader

• Excel

58. What will happen if PL/SQL variables occur in SQL statements and have the same name as a table column

Answers:

• Oracle will assume that it is the column that is being referenced

• Oracle will ignore the column name

• Oracle will popup an execution error

• None of these

59. A "virtual" table name to query the current time is:

Answers:

• V$PARAMETERS

• DUAL

• SYSDATE

• ALL_PARAMS

60. This type of error can be solved by increasing the undo retention of increasing the size of rollbacks.

Answers:

• ORA-01555

• ORA-03620

• ORA-02511

• ORA-01155

61. The most effective way to re-order the columns in a table is:

Answers:

• Use ALTER TABLE MODIFY COLUMN command

• Drop the table and re-create it

• Write a stored procedure to manipulate the columns

• By using CREATE TABLE AS SELECT

62. When you issue the command "ALTER DATABASE BACKUP CONTROLFILE TO TRACE", a text script version of backup control file will be created. Where is this file located?

Answers:

• In a folder pointed by SYSTEM_DUMP_DEST

• In the ORACLE_HOME/backup folder

• In the same folder as the running command

• In a folder pointed by USER_DUMP_DEST

63. What happends when you execute one transcaction and then truncate another table?

Answers:

• Transaction will rollback

• No effect on transaction

• Transcation will commit automatically

• Truncate will not work

64. Given the following data in the emp table: ENAME SALARY : PING 5000  AILYN 4999  SAM 1000  LESLIE 3000  TOM 2500  RAVI 10000  What will the following select statement produce?  SELECT ename FROM emp WHERE salary BETWEEN 3000 AND 5000;

Answers:

• PING AILYN LESLIE

• an error

• AILYN

• None of these

65. Implicit Cursor works when:

Answers:

• User logs in

• Executing a procedure

• DML operation is done

• Executing a function

66. The background process that checks for consistency of the database is called:

Answers:

• DBW0

• SMON

• PMON

• LGWR

67. Which of the following is a good suggestion for avoiding unnecessary extension of a rollback segment?

Answers:

• Set OPTIMAL value to minimize the allocation or deallocation of rollback segment extents

• Always place rollback segment in a separate exclusive tablespace

• Always use INITIAL = NEXT for rollback segments

• Avoid setting MAXEXTENT to UNLIMITED

68. The init.ora parameter that controls the location of the Alert log file is:

Answers:

• LOG_BUFFER

• BACKGROUND_DUMP_DEST

• UTL_FILE_DIR

• USER_DUMP_DEST

69. Which function is not in DBMS_OUTPUT?

Answers:

• NEW_LINE

• PREV_LINE

• PUT_LINE

• PUT

70. What happens if the command ALTER TABLE <tablename> DEALLOCATE UNUSED is used without the KEEP clause?

Answers:

• Oracle will deallocate all unused space without any condition

• Oracle will deallocate all unused space above the high water mark

• None of these

• Oracle will deallocate all unused space less than the high water mark

71. One of these datatypes is NOT a base scalar datatype?

Answers:

• INT

• VARCHAR2

• BOOLEAN

• NUMBER

72. When executing the command: "show parameter xxxx", ORA-00942: table or view does not exist appears. Which of the following best explains this error?

Answers:

• The parameter has been changed but the transaction is never committed

• Parameter table does not exist

• The statement is executed using "normal" user

• The database is not started up

73. This structure is not included in the Shared Pool contents.

Answers:

• Data dictionary cache

• Library cache

• User Global Area

• Database buffer cache

74. You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty. Which statement accomplishes this task?

Answers:

• ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;

• ALTER TABLE students ADD PRIMARY KEY student_id;

• ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

• ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);

75. Before any SQL statement is parsed, Oracle will check the _______ to see if that same statement already exists there.

Answers:

• Library cache

• Shared pool

• Dictionary cache

• None of these

76. Which of the following SQL functions can operate on any datatype?

Answers:

• CEIL

• CEIL

• TO_CHAR

• LOWER

• MAX

77. How does PL/SQL allow programmers to use it on any host environment?

Answers:

• It processes Oracle errors with exception-handling routines

• It improves process performance

• It is portable

• It modularizes program development

78. The Oracle server provides a number of standard data dictionary views to obtain information on database and instance. These views are:

Answers:

• V$DATATABLE, V$USERS, V$SESSIONS

• V$DATAFILE, V$DATABASE, V$SESSIONS

• V$SGA, V$INSTANCE, V$PROCESS

• V$SGA, V$BGPROCESS,V$USERS

79. Oracle default optimization follows:

Answers:

• Rule based optimization

• Both Cost and Rule

• Cost based optimiztion

80. There are 2 classifications of an index: by logical design and by physical implementation. From its logical design, indexes can have the following types, except:

Answers:

• Function-based

• B-Tree

• Single column or concatenated

• Unique or non unique

81. What is Extended Rowid?

Answers:

• datafile number+data block+row+data object number

• datafile number+data block+row

• datafile number+data object number+data block+row

• data object number+datafile number+data block+row

• data object number+datafile number

82. Which of the following best describes how to have requests serviced by an Oracle server (using either dedicated or shared server)?

Answers:

• For each open session, a new dedicated server will be created separately from the instance in a one-to-one mapping

• In dedicated server, Oracle uses a pool of shared processes for a large community of users

• A big difference between shared and dedicated server connections is that the client process connected to the database never talks directly to a dedicated server, as it would to a shared server

• The client process will be in direct communication with this shared server over some networking conduit such as TCP/IP socket.

83. Which of the following parameters specifies whether Oracle checks for a password file?

Answers:

• REMOTE_LOGIN_PASSWORDFILE

• LOGIN_PASSWORDFILE

• REMOTE_LOGIN_PASSWORD_FILE

• None of these

84. Which of the following cannot be placed in the declaration part of PL/SQL?

Answers:

• Constants

• Variables

• SQL statements

• User-defined exceptions

85. In an Oracle RAC environment, a physical standby database will be registered with the clusterware. When creating the clusterware resource for the standby database, which of the following commands do you use to perform this registration?

Answers:

• srvctl register database

• srvctl add database

• srvctl alter database

• srvctl add database register

86. The basic units that make up a PL/SQL are called:

Answers:

• Sub-routines

• Executable blocks

• Logical blocks

• Nested blocks

87. In Forms, what keyboard shortcut will access the shortcut menu?

Answers:

• F11

• F5

• Ctrl + K

• Ctrl + S

• Ctrl + M

88. Which one of these statements is true about a concatenated index?

Answers:

• It is created on multiple columns in a table.

• It is created when using functions or expressions that involve one or more columns in a table

• There is no limit of how many columns can be included in a composite key

• Columns in concatenated index need to be in the same order as the columns in the table

89. The only users recognized by the password file are:

Answers:

• SYSMAN and SYSTEM

• SYS and SYSTEM

• SYS and INTERNAL

• SYSTEM and INTERNAL

90. Which of the following statements are true about deleting or updating a statement?

Answers:

• For a delete, the row will not be removed from the data block until the end user execute commit command

• The undo log buffer will contain the corresponding delete or update statement

• The data block is read, loading it into a memory structure called a buffer cache

• No entry in the undo segment header block is created for this transaction

91. In what value do these 3 statements differ from one another?  select * from employees where department_id = 60;  SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 60;  select /* a_comment */ * from employees where department_id = 60;

Answers:

• The last statement contains syntax error

• The execution result

• All statements don't differ from one another

• Hash values

92. How do you switch from an init.ora file to a spfile?

Answers:

• Delete init.ora file, 10g and greater automatically creates an SPFILE if init.ora is missing

• update parameter USE_SPFILE to TRUE

• ALTER SYSTEM USE SPFILE;

• CREATE SPFILE FROM PFILE;

• ALTER DATABASE USE SPFILE;

93. What is the number of user defined triggers in Oracle?

Answers:

• 8

• 2

• 16

• 12

94. The default value of SHARED_POOL_SIZE  is:

Answers:

• 3500000 bytes

• 2048000 bytes

• 1024000 bytes

• 16000000 bytes

95. Does "Not in" use an index?

Answers:

• Yes

• No

96. Which of the following falsely sums up Oracle’s locking policy?

Answers:

• Oracle never locks data just to read it. There are no locks placed on rows of data by simple reads

• Oracle locks data at the row level on modification. There is no lock escalation to a block or table level

• A reader of data never blocks a writer of data

• A writer of data is blocked only when a reader of data has already locked the row it was going after.

97. Which of these tasks is NOT performed during the database mounting process?

Answers:

• Reading the control files to obtain the names and status of the data files

• Associating the database with a previously started instance

• Allocating the SGA

• Locating and opening the control files

98. Rowid on oracle stores:

Answers:

• Log stored position

• Logical position of the data

• Indexed position of data

• Physical position of the data

99. Users that can see any changed data are:

Answers:

• DBAs and users in the same group

• No one except the default user for the corresponding schema

• Only DBAs

• Only the ones making the changes

100. Which DATETIME data types cannot be used when specifying column definitions?

Answers:

• INTERVAL MONTH TO DAY

• INTERVAL YEAR TO MONTH

• TIMESTAMP

• INTERVAL DAY TO SECOND

101. To truncate table changes, use DATA_OBJECT_ID.

Answers:

• False

• True

102. Which of the follwing is NOT  associated with an instance?

Answers:

• Trace files

• Parameter files

• Alert files

• Control files