Oracle Database MCQs (FREE Multiple Choice Questions)

1: What does the acronym RDBMS mean?

A. Relational Database Manager System

B. Rational Database Management System

C. Rational Database Manager System

D. Relational Database Management System

E. Ratio Database Management System

2: What are the parts of a Package?

A. Header, Body and Footer

B. Header and Footer

C. Main, Content and Footer

D. Spec and Body

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

A. lock

B. commit

C. savepoint

D. rollback

4: Which statement about views are true?

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

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

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

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

5: What will the shortcut "Ctrl + F11" do?

A. Update Record

B. Enter Query

C. Clear Record

D. Execute Query

E. Exit

6: Which naming rules are applied to tables?

A. Start with a letter

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

C. All of these

D. Can contain characters such as $, _ and #

7: Which syntax turns an existing constraint on?

A. LTER TABLE table_name STATUS ENABLE CONSTRAINT constraint_name;

B. ALTER TABLE table_name TURN ON CONSTRAINT constraint_name;

C. ALTER TABLE table_name ENABLE constraint_name;

D. ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

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

A. DBA_SOURCE

B. All of these

C. USER_SOURCE

D. ALL_SOURCE

9: In a sequence, Curval returns:

A. Less then current value

B. More than current value

C. Present Value

D. Zero

10: Remote access of database can be made by:

A. SGA

B. PMON

C. SQL star plus

D. Database Link

11: What view would you use to look at the size of a data file?

A. DBA_FREE_SPACE

B. DBA_DATA_FILES

C. FILES_DBA_SPACE

D. FREE_DATA_FILES

12: Which of the following statement correctly decribes how to rebuild an index?

A. ALTER INDEX index_name REBUILD

B. ALTER index_name REBUILD

C. ALTER INDEX REBUILD index_name

D. index_name REBUILD

13: Which of the following statements is true about rebuilding indexes?

A. They can be rebuilt online

B. Performed by issuing the command ALTER INDEX

C. All of these

D. Rebuilding indexes doesn't need a lock on the table

14: How many columns are there in a Dual table?

A. 7

B. 1

C. 8

D. 9

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

A. 0

B. 6

C. 4

D. 2

16: Which of these is not a language element of SQL?

A. Queries

B. Clauses

C. Expressions

D. None. All are language elements of SQL.

E. Statements

17: 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?

A. Makes your environment the same across the enterprise

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

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

D. All of these

18: Which of the following are system privileges?

A. SELECT and DELETE

B. CREATE TABLE and DROP TABLE

C. DELETE and UPDATE

D. CREATE TABLE and SELECT

19: Which of these is not a real Oracle suite option?

A. Oracle E-Business Suite

B. Oracle Enterprise Manager

C. Oracle Developer Suite

D. Oracle Customer Service Suite

E. Oracle Application Server

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

A. IS NOTHING

B. IS NULL

C. = NULL

D. IS EMPTY

21: In business, what is the most common definition for CRM?

A. Contact Relationship Marketing

B. Customer Relationship Management

C. Client Relationship Management

D. Customer Relationship Marketing

E. Customer Resource Management

22: Define Index:

A. Create another column

B. Random form of data

C. Create another table

D. Ordered form of data

23: What is the purpose of the Rollback command?

A. Undo the last uncommitted transactions

B. Save the last uncommitted transactions

C. Maintains Log of the last uncommitted transactions

D. Redo the last uncommitted transactions

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

A. >=

B. AND

C. BETWEEN ... AND

D. <=

25: Which of the following is(are) physical storage file(s) of Oracle?

A. Redo log files

B. Control files

C. All of these

D. Data files

26: Which of the following is not DML (Data Manipulation Language)?

A. Rename

B. Delete

C. Insert

D. Update

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

A. True

B. False

28: The DBA assigns permission to the user using this command:

A. Table

B. Lock

C. Role

29: 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?

A. IN

B. BETWEEN

C. LIKE

D. EXISTS

30: What clause follows "group by" in SQL?

A. Having

B. When

C. Not in

D. In

31: In terms of Oracle, what is the most common meaning of OEM?

A. Order Entry Module

B. Oracle Enterprise Manager

C. Oracle Equipment Model

D. Oracle Entry Manager

E. Original Equipment Manufacturer

32: Which operator will help to filter out all duplicate records?

A. distinct

B. join

C. order by

D. delete

33: In business, what is the most common definition for ERP?

A. Electronic Resource Planning

B. Electronic Resource Pricing

C. Early Retirement Plan

D. Employee Referral Program

E. Enterprise Resource Planning

34: SQL stands for:

A. Standard Query Language

B. Sequential Query Language

C. Structured Query Language

D. Symmetric Query Language

35: 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?

A. DB_SYSTEM_CLUSTER_SIZE

B. DB_BLOCK_SIZE

C. DB_SYSTEM_BLOCK_SIZE

D. DB_CLUSTER_SIZE

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

A. Drop

B. Update

C. Alter

D. Create

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

A. CUCKOO AND CARLIE

B. CHUCKOO AND CHARLIE

C. CHUCHKOO AND CARLIE

D. CHUCHKOO AND CHARLIE

38: Regarding Oracle, what does DES most commonly mean?

A. Digital Encryption System

B. Data Entry Sheet

C. Dynamic Energy Saver

D. Data Encryption Standard

E. Data Extraction Segment

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

A. Oracle Certified Developer (OCD)

B. Oracle Certified Master (OCM)

C. Oracle Certified Associate (OCA)

D. Oracle Certified Professional (OCP)

40: What is the definition of data buffer cache?

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

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

C. Circular buffer that holds information about changes made to the database

D. An area in SGA taht contains the database checkpoints

41: 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

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

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

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

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

42: "Select for Update" performs:

A. Lock the record on result set

B. Unlock the record on result set

C. Select the record and deelete

D. Update the record set

43: What command is used to restore an "accidentally" dropped table?

A. flashback table <tablename> to before drop

B. rollback table <tablename> to before drop

C. rollback to segment

D. alter database cancel drop <tablename>

44: Order the following entities from the smallest to the largest:

A. Segments, extents, data blocks, tablespaces

B. Data blocks, extents, segments, table spaces

C. Extents, data blocks, segments, tablespaces

D. Table spaces, data blocks, extents, segments

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

A. PMON

B. SQLMON

C. WRAP

D. SESSIONMON

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

A. Use the >= operator

B. Use the IN operator

C. Use the CAN EXIST operator

D. Use the <= operator

E. Use the = operator

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

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

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

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

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

48: In Oracle "view" consists of:

A. The whole table

B. Indexed output of the table

C. The definition only

D. The output of the table

49: 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?

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

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

C. GRANT select, insert, update ON student_grades TO manager;

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

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

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

B. Substitution variables

C. Replacementvariables

D. Instead-of variables

51: Which of these statement is NOT true?

A. A datafile can be associated with one or more database

B. A database has at least one datafile

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

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

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

A. USER_TAB_PRIVS_MADE

B. USER_TAB_PRIVS

C. USER_COL_PRIVS

D. USER_COL_PRIVS_MADE

53: 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?

A. EXISTS

B. None of these

C. UNION

D. INTERSECT

54: The "materialized view" in Oracle stores:

A. No output only the definition

B. Output in logical space

C. No output

D. Output in different physical space

55: Which of the following actions are performed by the MINUS operator?

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

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

C. Returns the result of one numeric value minus another.

56: 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?

A. none of the above

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

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

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

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

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

A. None of these

B. Oracle will popup an execution error

C. Oracle will assume that it is the column that is being referenced

D. Oracle will ignore the column name

58: Which of these is NOT an Oracle "database option?"

A. Active Data Guard

B. Oracle Real Application Testing

C. Oracle Questions

D. Total Recall

E. Database Vault

59: Implicit Cursor works when:

A. Executing a procedure

B. DML operation is done

C. User logs in

D. Executing a function

60: What happends when you execute one transcaction and then truncate another table?

A. Transcation will commit automatically

B. No effect on transaction

C. Transaction will rollback

D. Truncate will not work

61: 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?

A. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;

B. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);

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

D. ALTER TABLE students ADD PRIMARY KEY student_id;

62: 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.

A. Cache

B. Power lock

C. Latch

D. Pitch

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

A. DUAL

B. ALL_PARAMS

C. SYSDATE

D. V$PARAMETERS

64: Which function is not in DBMS_OUTPUT?

A. NEW_LINE

B. PUT_LINE

C. PUT

D. PREV_LINE

65: 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?

A. In a folder pointed by SYSTEM_DUMP_DEST

B. In the same folder as the running command

C. In a folder pointed by USER_DUMP_DEST

D. In the ORACLE_HOME/backup folder

66: 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;

A. an error

B. None of these

C. AILYN

D. PING AILYN LESLIE

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

A. Forms Data Loader

B. Access

C. Excel

D. Cognos

E. DataLoad

68: Which of the following is a good suggestion for avoiding unnecessary extension of a rollback segment?

A. Always use INITIAL = NEXT for rollback segments

B. Avoid setting MAXEXTENT to UNLIMITED

C. Always place rollback segment in a separate exclusive tablespace

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

69: Oracle default optimization follows:

A. Rule based optimization

B. Cost based optimiztion

C. Both Cost and Rule

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

A. Use ALTER TABLE MODIFY COLUMN command

B. By using CREATE TABLE AS SELECT

C. Write a stored procedure to manipulate the columns

D. Drop the table and re-create it

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

A. PMON

B. DBW0

C. SMON

D. LGWR

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

A. ORA-03620

B. ORA-01155

C. ORA-02511

D. ORA-01555

73: How does PL/SQL allow programmers to use it on any host environment?

A. It improves process performance

B. It processes Oracle errors with exception-handling routines

C. It modularizes program development

D. It is portable

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

A. USER_DUMP_DEST

B. UTL_FILE_DIR

C. LOG_BUFFER

D. BACKGROUND_DUMP_DEST

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

A. Oracle will deallocate all unused space without any condition

B. Oracle will deallocate all unused space above the high water mark

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

D. None of these

76: Which of the following cannot be placed in the declaration part of PL/SQL?

A. SQL statements

B. Variables

C. User-defined exceptions

D. Constants

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

A. None of these

B. Dictionary cache

C. Library cache

D. Shared pool

78: Which of the following SQL functions can operate on any datatype?

A. LOWER

B. TO_CHAR

C. CEIL

D. CEIL

E. MAX

79: One of these datatypes is NOT a base scalar datatype?

A. NUMBER

B. INT

C. VARCHAR2

D. BOOLEAN

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

A. The parameter has been changed but the transaction is never committed

B. The statement is executed using "normal" user

C. The database is not started up

D. Parameter table does not exist

81: Which one of these statements is true about a concatenated index?

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

B. It is created on multiple columns in a table.

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

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

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

A. V$DATAFILE, V$DATABASE, V$SESSIONS

B. V$SGA, V$BGPROCESS,V$USERS

C. V$DATATABLE, V$USERS, V$SESSIONS

D. V$SGA, V$INSTANCE, V$PROCESS

83: This structure is not included in the Shared Pool contents.

A. Database buffer cache

B. Library cache

C. User Global Area

D. Data dictionary cache

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

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

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

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

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

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

A. ALTER DATABASE USE SPFILE;

B. update parameter USE_SPFILE to TRUE

C. ALTER SYSTEM USE SPFILE;

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

E. CREATE SPFILE FROM PFILE;

86: 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:

A. B-Tree

B. Single column or concatenated

C. Function-based

D. Unique or non unique

87: Which of the following parameters specifies whether Oracle checks for a password file?

A. None of these

B. LOGIN_PASSWORDFILE

C. REMOTE_LOGIN_PASSWORD_FILE

D. REMOTE_LOGIN_PASSWORDFILE

88: What is Extended Rowid?

A. data object number+datafile number

B. datafile number+data block+row

C. data object number+datafile number+data block+row

D. datafile number+data block+row+data object number

E. datafile number+data object number+data block+row

89: 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?

A. srvctl register database

B. srvctl add database

C. srvctl alter database

D. srvctl add database register

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

A. Logical blocks

B. Executable blocks

C. Nested blocks

D. Sub-routines

A. F11

B. Ctrl + S

C. Ctrl + K

D. F5

E. Ctrl + M

92: The only users recognized by the password file are:

A. SYSTEM and INTERNAL

B. SYS and INTERNAL

C. SYS and SYSTEM

D. SYSMAN and SYSTEM

93: Which of the following statements are true about deleting or updating a statement?

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

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

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

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

94: What is the number of user defined triggers in Oracle?

A. 16

B. 8

C. 2

D. 12

95: 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;

A. Hash values

B. The last statement contains syntax error

C. All statements don't differ from one another

D. The execution result

96: Does "Not in" use an index?

A. No

B. Yes

97: Rowid on oracle stores:

A. Log stored position

B. Logical position of the data

C. Indexed position of data

D. Physical position of the data

98: Which of the following falsely sums up Oracle’s locking policy?

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

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

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

D. A reader of data never blocks a writer of data

99: Which of these tasks is NOT performed during the database mounting process?

A. Allocating the SGA

B. Reading the control files to obtain the names and status of the data files

C. Locating and opening the control files

D. Associating the database with a previously started instance

100: The default value of SHARED_POOL_SIZE is:

A. 16000000 bytes

B. 1024000 bytes

C. 3500000 bytes

D. 2048000 bytes