Oracle Multiple Choice Questions
https://www.computersprofessor.com/2017/08/oracle-multiple-choice-questions.html
Top 200 Oracle Multiple Choice Questions
1. In your Oracle 10g database , you have scheduled a job to update the optimizer statistics at 05:00 pm every Friday. The job has successfully completed. Which three pieces of information would you check to confirm that the statistics have been collected? (Choose three.)
A. average row size
B. last analyzed date
C. size of table in bytes
D. size of table in database blocks
E. number of free blocks in the free list
F. number of extents present in the table
B. last analyzed date
C. size of table in bytes
D. size of table in database blocks
E. number of free blocks in the free list
F. number of extents present in the table
Answer: ABD
2. You are in the middle of a transaction and very crucial data has been modified. Because of a hardware failure, the instance has shut down before synchronizing all the database files. Which two statements are true? (Choose two.)
A. On startup, SMON coordinates instance recovery.
B. On startup, CKPT coordinates instance recovery.
C. On startup, use RMAN to perform instance recovery.
D. Uncommitted changes will be rolled back after the database is opened.
E. On startup, perform media recovery and then instance recovery.
F. On startup, all the files will be synchronized and you get both committed and uncommitted data.
B. On startup, CKPT coordinates instance recovery.
C. On startup, use RMAN to perform instance recovery.
D. Uncommitted changes will be rolled back after the database is opened.
E. On startup, perform media recovery and then instance recovery.
F. On startup, all the files will be synchronized and you get both committed and uncommitted data.
Answer: AD
3. You are using the backup scheduler in Enterprise Manager (EM) to schedule a backup of your database.
Which type of script does the backup scheduler generate?
A. SQL script
B. PL/SQL script
C. Operating System (OS) script
D. Enterprise Manager (EM) script
E. Recovery Manager (RMAN) script
B. PL/SQL script
C. Operating System (OS) script
D. Enterprise Manager (EM) script
E. Recovery Manager (RMAN) script
Answer: E
4. You executed the following command to export the EMPLOYEES table from a remote machine:
$ EXPDP hr/hr@data.us.oracle.com
DUMPFILE=my_dir:exp_hr.log
LOGFILE=data_pump_dir:log_hr.log
TABLES=employees
DUMPFILE=my_dir:exp_hr.log
LOGFILE=data_pump_dir:log_hr.log
TABLES=employees
What would be the outcome of this command?
A. The command would execute successfully and the export dump file would be created in the destination of the directory object MY_DIR.
B. The command would execute successfully. But log file would not be created as DATA_PUMP_DIR directory is only accessible to user with SYSDBA privilege.
C. The command fails with an error because DATA_PUMP_DIR directory have higher precedence over the per-file directory.
D. The command fails with an error because no absolute path is specified for log file and dumpfile.
B. The command would execute successfully. But log file would not be created as DATA_PUMP_DIR directory is only accessible to user with SYSDBA privilege.
C. The command fails with an error because DATA_PUMP_DIR directory have higher precedence over the per-file directory.
D. The command fails with an error because no absolute path is specified for log file and dumpfile.
Answer: A
5. Which step do you need to perform to enable a user with the SYSDBA privilege to log in as SYSDBA in iSQL*Plus?
A. The user must be granted the database administrator (DBA) privilege.
B. The user must be listed in the password file for the authentication.
C. No special setup is needed for the user to connect as SYSDBA in iSQL*Plus.
D. Set up a user in the Oracle Application Server Containers for J2EE (OC4J) user manager, and grant the webDba role to the user.
B. The user must be listed in the password file for the authentication.
C. No special setup is needed for the user to connect as SYSDBA in iSQL*Plus.
D. Set up a user in the Oracle Application Server Containers for J2EE (OC4J) user manager, and grant the webDba role to the user.
Answer: D
6. Which two statements regarding the LOGGING clause of the CREATE TABLESPACE. .. statement are correct? (Choose two.)
A. This clause is not valid for a temporary or undo tablespace.
B. If the tablespace is in the NOLOGGING mode, no operation on the tablespace will generate redo.
C. The tablespace will be in the NOLOGGING mode by default, if not specified while creating a tablespace.
D. The tablespace-level logging attribute can be overridden by logging specifications at the table, index, materialized view, materialized view log, and partition levels.
B. If the tablespace is in the NOLOGGING mode, no operation on the tablespace will generate redo.
C. The tablespace will be in the NOLOGGING mode by default, if not specified while creating a tablespace.
D. The tablespace-level logging attribute can be overridden by logging specifications at the table, index, materialized view, materialized view log, and partition levels.
Answer: AD
7. Which two statements are true about the primary key constraint in a table? (Choose two.)
A. It is not possible to disable the primary key constraint.
B. It is possible to have more than one primary key constraint in a single table.
C. The primary key constraint can be referred by only one foreign key constraint.
D. The primary key constraint can be imposed by combining more than one column.
E. The non-deferrable primary key constraint creates an unique index on the primary key column if it is not already indexed.
B. It is possible to have more than one primary key constraint in a single table.
C. The primary key constraint can be referred by only one foreign key constraint.
D. The primary key constraint can be imposed by combining more than one column.
E. The non-deferrable primary key constraint creates an unique index on the primary key column if it is not already indexed.
Answer: DE
8. You want the user APP_DBA to administer the Oracle database from a remote machine. APP_DBA is granted the SYSDBA privilege to perform administrative tasks on the database. Which file is used by the Oracle database server to authenticate APP_DBA?
A. control file
B. password file
C. listener controller file
D. control file and password file
B. password file
C. listener controller file
D. control file and password file
Answer: B
9. Which three statements are true regarding the logical structure of the Oracle database? (Choose three.)
A. Each segment contains one or more extents.
B. Multiple tablespaces can share single data file.
C. A data block is the smallest unit of I/O for data files.
D. It is possible to have tablespaces of different block sizes in a database.
E. Each data block in the database always corresponds to one OS block.
B. Multiple tablespaces can share single data file.
C. A data block is the smallest unit of I/O for data files.
D. It is possible to have tablespaces of different block sizes in a database.
E. Each data block in the database always corresponds to one OS block.
Answer: ACD
10. Which is the memory area that is created when a dedicated server process is started, and contains data and control information for that server process?
A. SGA
B. PGA
C. Shared Pool
D. Streams Pool
B. PGA
C. Shared Pool
D. Streams Pool
Answer: B
11. You find that the database performance degrades while you backup the PROD database using Recovery Manager (RMAN). The PROD database is running in shared server mode. The database instance is currently using 60% of total operating system memory. You suspect the shared pool fragmentation to be the reason.
Which action would you consider to overcome the performance degradation?
A. Configure Java Pool to cache the java objects.
B. Configure Streams Pool to enable parallel processing.
C. Increase Shared Pool size to cache more PL/SQL objects.
D. Increase Database Buffer Cache size to increase cache hits.
E. Configure Large Pool to be used by RMAN and shared server.
F. Increase the total System Global Area (SGA) size to increase memory hits.
B. Configure Streams Pool to enable parallel processing.
C. Increase Shared Pool size to cache more PL/SQL objects.
D. Increase Database Buffer Cache size to increase cache hits.
E. Configure Large Pool to be used by RMAN and shared server.
F. Increase the total System Global Area (SGA) size to increase memory hits.
Answer: E
12. You execute the following command to audit the database activities:
SQL> AUDIT DROP ANY TABLE BY scott BY SESSION WHENEVER SUCCESSFUL;
What is the effect of this command?
A. One audit record is created for the whole session if user SCOTT successfully drops one or more tables in his session.
B. One audit record is created for every session when any user successfully drops a table owned by SCOTT.
C. One audit record is created for each successful DROP TABLE command executed by any user to drop tables owned by SCOTT.
D. One audit record is generated for the session when SCOTT grants the DROP ANY TABLE privilege to other users in his session.
E. One audit record is created for each successful DROP TABLE command executed in the session of SCOTT.
B. One audit record is created for every session when any user successfully drops a table owned by SCOTT.
C. One audit record is created for each successful DROP TABLE command executed by any user to drop tables owned by SCOTT.
D. One audit record is generated for the session when SCOTT grants the DROP ANY TABLE privilege to other users in his session.
E. One audit record is created for each successful DROP TABLE command executed in the session of SCOTT.
Answer: A
13. The SAVE_AMT column in the ACCOUNTS table contains the balance details of customers in a bank.
As part of the year-end tax and interest calculation process, all the rows in the table need to be accessed.
The bank authorities want to track access to the rows containing balance amounts exceeding $200,000, and then send an alert message to the administrator.
The bank authorities want to track access to the rows containing balance amounts exceeding $200,000, and then send an alert message to the administrator.
Which method would you suggest to the bank for achieving this task?
A. implementing value-based auditing by using triggers
B. implementing fine-grained auditing with audit condition and event handler
C. performing standard database auditing to audit object privileges by setting the AUDIT_TRAIL parameter to EXTENDED
D. performing standard database auditing to audit SQL statements with granularity level set to ACCESS
B. implementing fine-grained auditing with audit condition and event handler
C. performing standard database auditing to audit object privileges by setting the AUDIT_TRAIL parameter to EXTENDED
D. performing standard database auditing to audit SQL statements with granularity level set to ACCESS
Answer: B
14. Which three statements are true regarding the fine-grained auditing (FGA)? (Choose three.)
A. FGA is possible on SELECT statements only.
B. The audit trail for FGA is stored in the FGA_LOG$ table.
C. The audit trail for FGA is stored in the AUD_LOG$ table.
D. FGA enables a SQL predicate to define when to audit an event.
E. FGA audits DELETE statements only when audit columns are specified.
F. FGA includes the SQL statement used by the user as part of the audit event entry.
B. The audit trail for FGA is stored in the FGA_LOG$ table.
C. The audit trail for FGA is stored in the AUD_LOG$ table.
D. FGA enables a SQL predicate to define when to audit an event.
E. FGA audits DELETE statements only when audit columns are specified.
F. FGA includes the SQL statement used by the user as part of the audit event entry.
Answer: BDF
15. You plan to use static database registration for a new listener when you create it. What could be the two reasons for this? (Choose two.)
A. More than one database is to be registered with the listener.
B. The users will connect the database by using the host naming method.
C. The Oracle Enterprise Manager is to be used to monitor an Oracle9i database.
D. The database that is to be registered with the listener is configured in shared server mode.
E. The listener is not configured on the default port of 1521 and the instance is not configured to register
with a nondefault port.
B. The users will connect the database by using the host naming method.
C. The Oracle Enterprise Manager is to be used to monitor an Oracle9i database.
D. The database that is to be registered with the listener is configured in shared server mode.
E. The listener is not configured on the default port of 1521 and the instance is not configured to register
with a nondefault port.
Answer: CE
16. Which three pieces of information are to be mandatorily provided while creating a new listener using Enterprise Manager Database Control? (Choose three.)
A. the port used by the listener
B. the protocol used by the listener
C. the server name where the listener runs
D. the log file and trace file destination for the listener
E. the database services to be registered with the listener
B. the protocol used by the listener
C. the server name where the listener runs
D. the log file and trace file destination for the listener
E. the database services to be registered with the listener
Answer: ABC
17. You have been recently hired as a database administrator. Your senior manager asks you to study the production database server and submit a report on the settings done by the previous DBA. While observing the server settings, you find that the following parameter has been set in the parameter file of the database:
REMOTE_OS_AUTHENT = TRUE
What could have been the reason to set this parameter as TRUE?
A. to enable operating system authentication for a remote client
B. to restrict the scope of administration to identical operating systems
C. to allow the start up and shut down of the database from a remote client
D. to enable the administration of the operating system from a remote client
E. to disable the administration of the operating system from a remote client
B. to restrict the scope of administration to identical operating systems
C. to allow the start up and shut down of the database from a remote client
D. to enable the administration of the operating system from a remote client
E. to disable the administration of the operating system from a remote client
Answer: A
18. Which naming method uses the tnsnames.ora file to store the connect descriptor used by the client while connecting to the database instance from a remote machine?
A. host naming method
B. local naming method
C. external naming method
D. directory naming method
B. local naming method
C. external naming method
D. directory naming method
Answer: B
19. Your tnsnames.ora file has the following entry for the service alias ORCL:
ORCL =
( DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.156.24.216)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
The TNSPING command executes successfully when tested with ORCL but you are not able to connect to the database instance with the following command:
SQL> CONNECT scott/tiger@orcl
( DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.156.24.216)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
The TNSPING command executes successfully when tested with ORCL but you are not able to connect to the database instance with the following command:
SQL> CONNECT scott/tiger@orcl
What could be the reason for this?
A. The listener is not running at the server.
B. The TNS_ADMIN environmental variable is set to a wrong value.
C. The orcl.oracle.com database service is not registered with the listener.
D. The DEFAULT_DOMAIN parameter is set to a wrong value in the SQLNET.ORA file.
B. The TNS_ADMIN environmental variable is set to a wrong value.
C. The orcl.oracle.com database service is not registered with the listener.
D. The DEFAULT_DOMAIN parameter is set to a wrong value in the SQLNET.ORA file.
Answer: C
20. Your database is open and users are connected using the LISTENER listener. The new DBA of the system stops the listener by using the following command:
LSNRCTL> STOP
What would happen to the sessions that are presently connected to the database instance?
A. The sessions are able to perform only queries.
B. The sessions are not affected and continue to function normally.
C. The active transactions are rolled back and the sessions get terminated.
D. The sessions are not allowed to perform any operations till the listener is started.
B. The sessions are not affected and continue to function normally.
C. The active transactions are rolled back and the sessions get terminated.
D. The sessions are not allowed to perform any operations till the listener is started.
Answer: B
21. You need to extract details of those products in the SALES table where the PROD_ID column contains the string ‘_D123’.
Which WHERE clause could be used in the SELECT statement to get the required output?
A. WHERE prod_id LIKE ‘%_D123%’ ESCAPE ‘_’
B. WHERE prod_id LIKE ‘%\_D123%’ ESCAPE ‘\’
C. WHERE prod_id LIKE ‘%_D123%’ ESCAPE ‘%_’
D. WHERE prod_id LIKE ‘%\_D123%’ ESCAPE ‘\_’
B. WHERE prod_id LIKE ‘%\_D123%’ ESCAPE ‘\’
C. WHERE prod_id LIKE ‘%_D123%’ ESCAPE ‘%_’
D. WHERE prod_id LIKE ‘%\_D123%’ ESCAPE ‘\_’
Answer: B
22. Which two statements are true regarding single row functions? (Choose two.)
A. They a ccept only a single argument.
B. They c an be nested only to two levels.
C. Arguments can only be column values or constants.
D. They a lways return a single result row for every row of a queried table.
E. They c an return a data type value different from the one that is referenced.
B. They c an be nested only to two levels.
C. Arguments can only be column values or constants.
D. They a lways return a single result row for every row of a queried table.
E. They c an return a data type value different from the one that is referenced.
Answer: DE
23. Which SQL statements would display the value 1890.55 as $1,890.55? (Choose three .)
A. SELECT TO_CHAR(1890.55,’$0G000D00′) FROM DUAL;
B. SELECT TO_CHAR(1890.55,’$9,999V99′) FROM DUAL;
C. SELECT TO_CHAR(1890.55,’$99,999D99′) FROM DUAL;
D. SELECT TO_CHAR(1890.55,’$99G999D00′) FROM DUAL;
E. SELECT TO_CHAR(1890.55,’$99G999D99′) FROM DUAL;
B. SELECT TO_CHAR(1890.55,’$9,999V99′) FROM DUAL;
C. SELECT TO_CHAR(1890.55,’$99,999D99′) FROM DUAL;
D. SELECT TO_CHAR(1890.55,’$99G999D00′) FROM DUAL;
E. SELECT TO_CHAR(1890.55,’$99G999D99′) FROM DUAL;
Answer: ADE
24. Examine the structure of the SHIPMENTS table:
name Null Type
PO_ID NOT NULL NUMBER(3)
PO_DATE NOT NULL DATE
SHIPMENT_DATE NOT NULL DATE
SHIPMENT_MODE VARCHAR2(30)
SHIPMENT_COST NUMBER(8,2)
You want to generate a report that displays the PO_ID and the penalty amount to be paid if the SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day.
PO_ID NOT NULL NUMBER(3)
PO_DATE NOT NULL DATE
SHIPMENT_DATE NOT NULL DATE
SHIPMENT_MODE VARCHAR2(30)
SHIPMENT_COST NUMBER(8,2)
You want to generate a report that displays the PO_ID and the penalty amount to be paid if the SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day.
Evaluate the following two queries:
SQL> SELECT po_id, CASE
WHEN MONTHS_BETWEEN (shipment_date,po_date)>1 THEN
TO_CHAR((shipment_date – po_date) * 20) ELSE ‘No Penalty’ END PENALTY
FROM shipments;
SQL>SELECT po_id, DECODE
(MONTHS_BETWEEN (po_date,shipment_date)>1,
TO_CHAR((shipment_date – po_date) * 20), ‘No Penalty’) PENALTY
FROM shipments;
WHEN MONTHS_BETWEEN (shipment_date,po_date)>1 THEN
TO_CHAR((shipment_date – po_date) * 20) ELSE ‘No Penalty’ END PENALTY
FROM shipments;
SQL>SELECT po_id, DECODE
(MONTHS_BETWEEN (po_date,shipment_date)>1,
TO_CHAR((shipment_date – po_date) * 20), ‘No Penalty’) PENALTY
FROM shipments;
Which statement is true regarding the above commands?
A. Both execute successfully and give correct results.
B. Only the first query executes successfully but gives a wrong result.
C. Only the first query executes successfully and gives the correct result.
D. Only the second query executes successfully but gives a wrong result.
E. Only the second query executes successfully and gives the correct result.
B. Only the first query executes successfully but gives a wrong result.
C. Only the first query executes successfully and gives the correct result.
D. Only the second query executes successfully but gives a wrong result.
E. Only the second query executes successfully and gives the correct result.
Answer: C
25. Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)
A. Both USING and ON clauses can be used for equijoins and nonequijoins.
B. A maximum of one pair of columns can be joined between two tables using the ON clause.
C. The ON clause can be used to join tables on columns that have different names but compatible data types.
D. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.
B. A maximum of one pair of columns can be joined between two tables using the ON clause.
C. The ON clause can be used to join tables on columns that have different names but compatible data types.
D. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.
Answer: CD
26. Which statement is true regarding the INTERSECT operator?
A. It ignores NULL values.
B. Reversing the order of the intersected tables alters the result.
C. The names of columns in all SELECT statements must be identical.
D. The number of columns and data types must be identical for all SELECT statements in the query.
B. Reversing the order of the intersected tables alters the result.
C. The names of columns in all SELECT statements must be identical.
D. The number of columns and data types must be identical for all SELECT statements in the query.
Answer: D
27. You need to produce a report where each customer’s credit limit has been incremented by $1000. In the output, t he customer’s last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit. The column headings should have only the first letter of each word in uppercase .
Which statement would accomplish this requirement?
A. SELECT cust_last_name Name, cust_credit_limit + 1000 “New Credit Limit” FROM customers;
B. SELECT cust_last_name AS Name, cust_credit_limit + 1000 AS New Credit Limit FROM customers;
C. SELECT cust_last_name AS “Name”, cust_credit_limit + 1000 AS “New Credit Limit” FROM customers;
D. SELECT INITCAP(cust_last_name) “Name”, cust_credit_limit + 1000 INITCAP(“NEW CREDIT LIMIT”) FROM customers;
B. SELECT cust_last_name AS Name, cust_credit_limit + 1000 AS New Credit Limit FROM customers;
C. SELECT cust_last_name AS “Name”, cust_credit_limit + 1000 AS “New Credit Limit” FROM customers;
D. SELECT INITCAP(cust_last_name) “Name”, cust_credit_limit + 1000 INITCAP(“NEW CREDIT LIMIT”) FROM customers;
Answer: C
28. Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level.
Which query would give the required result?
A. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers;
B. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers;
C. SELECT DISTINCT cust_income_level ‘ ‘ cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers;
D. SELECT cust_income_level ‘ ‘ cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers;
B. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers;
C. SELECT DISTINCT cust_income_level ‘ ‘ cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers;
D. SELECT cust_income_level ‘ ‘ cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers;
Answer: C
29. Evaluate the following query:
SQL> SELECT promo_name q'{‘s start date was }’ promo_begin_date
AS “Promotion Launches”
FROM promotions;
AS “Promotion Launches”
FROM promotions;
What would be the outcome of the above query?
A. It produces an error because flower braces have been used.
B. It produces an error because the data types are not matching.
C. It executes successfully and introduces an ‘s at the end of each promo_name in the output.
D. It executes successfully and displays the literal ” {‘s start date was } ” for each row in the output.
B. It produces an error because the data types are not matching.
C. It executes successfully and introduces an ‘s at the end of each promo_name in the output.
D. It executes successfully and displays the literal ” {‘s start date was } ” for each row in the output.
Answer: C
30. Examine the structure of the PROMOTIONS table:
name Null Type
PROMO_ID NOT NULL NUMBER(6)
PROMO_NAME NOT NULL VARCHAR2(30)
PROMO_CATEGORY NOT NULL VARCHAR2(30)
PROMO_COST NOT NULL NUMBER(10,2)
The management wants to see a report of unique promotion costs in each promotion category.
PROMO_ID NOT NULL NUMBER(6)
PROMO_NAME NOT NULL VARCHAR2(30)
PROMO_CATEGORY NOT NULL VARCHAR2(30)
PROMO_COST NOT NULL NUMBER(10,2)
The management wants to see a report of unique promotion costs in each promotion category.
Which query would achieve the required result?
A. SELECT DISTINCT promo_cost, promo_category FROM promotions;
B. SELECT promo_category, DISTINCT promo_cost FROM promotions;
C. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
B. SELECT promo_category, DISTINCT promo_cost FROM promotions;
C. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
Answer: D
31. Evaluate the following query:
SELECT INTERVAL ‘300’ MONTH,
INTERVAL ’54-2′ YEAR TO MONTH,
INTERVAL ’11:12:10.1234567′ HOUR TO SECOND
FROM dual;
INTERVAL ’54-2′ YEAR TO MONTH,
INTERVAL ’11:12:10.1234567′ HOUR TO SECOND
FROM dual;
What is the correct output of the above query?
A. +25-00 , +54-02, +00 11:12:10.123457
B. +00-300, +54-02, +00 11:12:10.123457
C. +25-00 , +00-650, +00 11:12:10.123457
D. +00-300 , +00-650, +00 11:12:10.123457
B. +00-300, +54-02, +00 11:12:10.123457
C. +25-00 , +00-650, +00 11:12:10.123457
D. +00-300 , +00-650, +00 11:12:10.123457
Answer: A
32. Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.)
A. Only one LONG column can be used per table.
B. A TIMESTAMP data type column stores only time values with fractional seconds.
C. The BLOB data type column is used to store binary data in an operating system file.
D. The minimum column width that can be specified for a VARCHAR2 data type column is one.
E. The value for a CHAR data type column is blank-padded to the maximum defined column width.
B. A TIMESTAMP data type column stores only time values with fractional seconds.
C. The BLOB data type column is used to store binary data in an operating system file.
D. The minimum column width that can be specified for a VARCHAR2 data type column is one.
E. The value for a CHAR data type column is blank-padded to the maximum defined column width.
Answer: ADE
33. What does the FORCE option for creating a view do?
A.creates a view with constraints
B.creates a view even if the underlying parent table has constraints
C.creates a view in another schema even if you don’t have privileges
D.creates a view regardless of whether or not the base tables exist
B.creates a view even if the underlying parent table has constraints
C.creates a view in another schema even if you don’t have privileges
D.creates a view regardless of whether or not the base tables exist
Answer: D
34. What are two reasons to create synonyms? (Choose two.)
A.You have too many tables.
B.Your tables are too long.
C.Your tables have difficult names.
D.You want to work on your own tables.
E.You want to use another schema’s tables.
F.You have too many columns in your tables.
B.Your tables are too long.
C.Your tables have difficult names.
D.You want to work on your own tables.
E.You want to use another schema’s tables.
F.You have too many columns in your tables.
Answer: CE
35. The STUDENT_GRADES table has these columns:
STUDENT_ID NUMBER(12)
SEMESTER_END DATE
GPA NUMBER(4,3)
The registrar requested a report listing the students’ grade point averages (GPA) sorted from highest grade point average to lowest.
SEMESTER_END DATE
GPA NUMBER(4,3)
The registrar requested a report listing the students’ grade point averages (GPA) sorted from highest grade point average to lowest.
Which statement produces a report that displays the student ID and GPA in the sorted order requested by the registrar?
A.SELECT student_id, gpa FROM student_grades ORDER BY gpa ASC;
B.SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa ASC;
C.SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa;
D.SELECT student_id, gpa FROM student_grades ORDER BY gpa;
E.SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa DESC;
F.SELECT student_id, gpa FROM student_grades ORDER BY gpa DESC;
B.SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa ASC;
C.SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa;
D.SELECT student_id, gpa FROM student_grades ORDER BY gpa;
E.SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa DESC;
F.SELECT student_id, gpa FROM student_grades ORDER BY gpa DESC;
Answer: F
36. In which three cases would you use the USING clause? (Choose three.)
A.You want to create a nonequijoin.
B.The tables to be joined have multiple NULL columns.
C.The tables to be joined have columns of the same name and different data types.
D.The tables to be joined have columns with the same name and compatible data types.
E.You want to use a NATURAL join, but you want to restrict the number of columns in the join condition.
B.The tables to be joined have multiple NULL columns.
C.The tables to be joined have columns of the same name and different data types.
D.The tables to be joined have columns with the same name and compatible data types.
E.You want to use a NATURAL join, but you want to restrict the number of columns in the join condition.
Answer: CDE
37. The CUSTOMERS table has these columns:
CUSTOMER_ID NUMBER(4) NOT NULL
CUSTOMER_NAME VARCHAR2(100) NOT NULL
STREET_ADDRESS VARCHAR2(150)
CITY_ADDRESS VARCHAR2(50)
STATE_ADDRESS VARCHAR2(50)
PROVINCE_ADDRESS VARCHAR2(50)
COUNTRY_ADDRESS VARCHAR2(50)
POSTAL_CODE VARCHAR2(12)
CUSTOMER_PHONE VARCHAR2(20)
The CUSTOMER_ID column is the primary key for the table.
CUSTOMER_NAME VARCHAR2(100) NOT NULL
STREET_ADDRESS VARCHAR2(150)
CITY_ADDRESS VARCHAR2(50)
STATE_ADDRESS VARCHAR2(50)
PROVINCE_ADDRESS VARCHAR2(50)
COUNTRY_ADDRESS VARCHAR2(50)
POSTAL_CODE VARCHAR2(12)
CUSTOMER_PHONE VARCHAR2(20)
The CUSTOMER_ID column is the primary key for the table.
You need to determine how dispersed your customer base is. Which expression finds the number of different countries represented in the CUSTOMERS table?
A.COUNT(UPPER(country_address))
B.COUNT(DIFF(UPPER(country_address)))
C.COUNT(UNIQUE(UPPER(country_address)))
D.COUNT DISTINCT UPPER(country_address)
E.COUNT(DISTINCT (UPPER(country_address)))
B.COUNT(DIFF(UPPER(country_address)))
C.COUNT(UNIQUE(UPPER(country_address)))
D.COUNT DISTINCT UPPER(country_address)
E.COUNT(DISTINCT (UPPER(country_address)))
Answer: E
38. A SELECT statement can be used to perform these three functions:
1. Choose rows from a table.
2. Choose columns from a table.
3. Bring together data that is stored in different tables by creating a link between them.
2. Choose columns from a table.
3. Bring together data that is stored in different tables by creating a link between them.
Which set of keywords describes these capabilities?
A.difference, projection, join
B.selection, projection, join
C.selection, intersection, join
D.intersection, projection, join
E.difference, projection, product
B.selection, projection, join
C.selection, intersection, join
D.intersection, projection, join
E.difference, projection, product
Answer: B
39. Evaluate this SQL statement:
SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROM EMPLOYEES e, DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;
FROM EMPLOYEES e, DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;
In the statement, which capabilities of a SELECT statement are performed?
A.selection, projection, join
B.difference, projection, join
C.selection, intersection, join
D.intersection, projection, join
E.difference, projection, product
B.difference, projection, join
C.selection, intersection, join
D.intersection, projection, join
E.difference, projection, product
Answer: A
40. Evaluate this SQL statement:
SELECT e.employee_id, (.15* e.salary) + (.5 * e.commission_pct) + (s.sales_amount * (.35 * e.bonus)) AS CALC_VALUE FROM employees e, sales s
WHERE e.employee_id = s.emp_id;
WHERE e.employee_id = s.emp_id;
What will happen if you remove all the parentheses from the calculation?
A.The value displayed in the CALC_VALUE column will be lower.
B.The value displayed in the CALC_VALUE column will be higher.
C.There will be no difference in the value displayed in the CALC_VALUE column.
D.An error will be reported.
B.The value displayed in the CALC_VALUE column will be higher.
C.There will be no difference in the value displayed in the CALC_VALUE column.
D.An error will be reported.
Answer: C
41. Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12?
A.SELECT ename, salary*12 ‘Annual Salary’ FROM employees;
B.SELECT ename, salary*12 “Annual Salary” FROM employees;
C.SELECT ename, salary*12 AS Annual Salary FROM employees;
D.SELECT ename, salary*12 AS INITCAP(“ANNUAL SALARY”) FROM employees
B.SELECT ename, salary*12 “Annual Salary” FROM employees;
C.SELECT ename, salary*12 AS Annual Salary FROM employees;
D.SELECT ename, salary*12 AS INITCAP(“ANNUAL SALARY”) FROM employees
Answer: B
42. Evaluate this SQL statement:
SELECT ename, sal, 12*sal+100 FROM emp;
The SAL column stores the monthly salary of the employee. Which change must be made to the above syntax to calculate the annual compensation as “monthly salary plus a monthly bonus of $100, multiplied by 12”?
The SAL column stores the monthly salary of the employee. Which change must be made to the above syntax to calculate the annual compensation as “monthly salary plus a monthly bonus of $100, multiplied by 12”?
A.No change is required to achieve the desired results.
B.SELECT ename, sal, 12*(sal+100) FROM emp;
C.SELECT ename, sal, (12*sal)+100 FROM emp;
D.SELECT ename, sal+100,*12 FROM emp;
B.SELECT ename, sal, 12*(sal+100) FROM emp;
C.SELECT ename, sal, (12*sal)+100 FROM emp;
D.SELECT ename, sal+100,*12 FROM emp;
Answer: B
43. The CUSTOMERS table has these columns:
CUSTOMER_ID NUMBER(4) NOT NULL
CUSTOMER_NAME VARCHAR2(100) NOT NULL
CUSTOMER_ADDRESS VARCHAR2(150)
CUSTOMER_PHONE VARCHAR2(20)
You need to produce output that states “Dear Customer customer_name, “.
CUSTOMER_NAME VARCHAR2(100) NOT NULL
CUSTOMER_ADDRESS VARCHAR2(150)
CUSTOMER_PHONE VARCHAR2(20)
You need to produce output that states “Dear Customer customer_name, “.
The customer_name data values come from the CUSTOMER_NAME column in the CUSTOMERS table.
Which statement produces this output?
A.SELECT dear customer, customer_name,
B.SELECT “Dear Customer”, customer_name || ‘,’ FROM customers;
C.SELECT ‘Dear Customer ‘ || customer_name ‘,’ FROM customers;
D.SELECT ‘Dear Customer ‘ || customer_name || ‘,’ FROM customers;
E.SELECT “Dear Customer ” || customer_name || “,” FROM customers;
F.SELECT ‘Dear Customer ‘ || customer_name || ‘,’ || FROM customers;
B.SELECT “Dear Customer”, customer_name || ‘,’ FROM customers;
C.SELECT ‘Dear Customer ‘ || customer_name ‘,’ FROM customers;
D.SELECT ‘Dear Customer ‘ || customer_name || ‘,’ FROM customers;
E.SELECT “Dear Customer ” || customer_name || “,” FROM customers;
F.SELECT ‘Dear Customer ‘ || customer_name || ‘,’ || FROM customers;
Answer: D
44. Which two are attributes of iSQL*Plus? (Choose two.)
A.iSQL*Plus commands cannot be abbreviated.
B.iSQL*Plus commands are accessed from a browser.
C.iSQL*Plus commands are used to manipulate data in tables.
D.iSQL*Plus commands manipulate table definitions in the database.
E.iSQL*Plus is the Oracle proprietary interface for executing SQL statements.
B.iSQL*Plus commands are accessed from a browser.
C.iSQL*Plus commands are used to manipulate data in tables.
D.iSQL*Plus commands manipulate table definitions in the database.
E.iSQL*Plus is the Oracle proprietary interface for executing SQL statements.
Answer: BE
45. Which is an iSQL*Plus command?
A.INSERT
B.UPDATE
C.SELECT
D.DESCRIBE
E.DELETE
F.RENAME
B.UPDATE
C.SELECT
D.DESCRIBE
E.DELETE
F.RENAME
Answer: D
46. Which are iSQL*Plus commands? (Choose all that apply.)
A.INSERT
B.UPDATE
C.SELECT
D.DESCRIBE
E.DELETE
F.RENAME
B.UPDATE
C.SELECT
D.DESCRIBE
E.DELETE
F.RENAME
Answer: D
47. Which two statements are true about constraints? (Choose two.)
A.The UNIQUE constraint does not permit a null value for the column.
B.A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints.
C.The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index.
D.The NOT NULL constraint ensures that null values are not permitted for the column.
B.A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints.
C.The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index.
D.The NOT NULL constraint ensures that null values are not permitted for the column.
Answer: BD
48. Which three statements correctly describe the functions and use of constraints? (Choose three.)
A.Constraints provide data independence.
B.Constraints make complex queries easy.
C.Constraints enforce rules at the view level.
D.Constraints enforce rules at the table level.
E.Constraints prevent the deletion of a table if there are dependencies.
F.Constraints prevent the deletion of an index if there are dependencies.
B.Constraints make complex queries easy.
C.Constraints enforce rules at the view level.
D.Constraints enforce rules at the table level.
E.Constraints prevent the deletion of a table if there are dependencies.
F.Constraints prevent the deletion of an index if there are dependencies.
Answer: CDE
49. Which SQL statement defines a FOREIGN KEY constraint on the DEPTNO column of the EMP table?
A.CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk FOREIGN KEY deptno REFERENCES dept deptno);
B.CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
C.CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno));
D.CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) FOREIGN KEY CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
B.CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
C.CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno));
D.CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) FOREIGN KEY CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
Answer: B
50. Which view should a user query to display the columns associated with the constraints on a table owned by the user?
A.USER_CONSTRAINTS
B.USER_OBJECTS
C.ALL_CONSTRAINTS
D.USER_CONS_COLUMNS
E.USER_COLUMNS
Answer: D
B.USER_OBJECTS
C.ALL_CONSTRAINTS
D.USER_CONS_COLUMNS
E.USER_COLUMNS
Answer: D
51. You need to design a student registration database that contains several tables storing academic information.
The STUDENTS table stores information about a student. The STUDENT_GRADES table stores information about the student’s grades. Both of the tables have a column named STUDENT_ID. The STUDENT_ID column in the STUDENTS table is a primary key.
You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table that points to the STUDENT_ID column of the STUDENTS table. Which statement creates the foreign key?
You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table that points to the STUDENT_ID column of the STUDENTS table. Which statement creates the foreign key?
A.CREATE TABLE student_grades (student_id NUMBER(12), semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk REFERENCES (student_id) FOREIGN KEY students(student_id));
B.CREATE TABLE student_grades (student_id NUMBER(12), semester_end DATE, gpa NUMBER(4,3), student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));
C.CREATE TABLE student_grades (student_id NUMBER(12), semester_end DATE, gpa NUMBER(4,3), CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(student_id));
D.CREATE TABLE student_grades (student_id NUMBER(12), semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));
B.CREATE TABLE student_grades (student_id NUMBER(12), semester_end DATE, gpa NUMBER(4,3), student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));
C.CREATE TABLE student_grades (student_id NUMBER(12), semester_end DATE, gpa NUMBER(4,3), CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(student_id));
D.CREATE TABLE student_grades (student_id NUMBER(12), semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));
Answer: D
52. You need to load information about new customers from the NEW_CUST table into the tables CUST and CUST_SPECIAL. If a new customer has a credit limit greater than 10,000, then the details have to be inserted into CUST_SPECIAL. All new customer details have to be inserted into the CUST table. Which technique should be used to load the data most efficiently?
A. external table
B. the MERGE command
C. the multitable INSERT command
D. INSERT using WITH CHECK OPTION
B. the MERGE command
C. the multitable INSERT command
D. INSERT using WITH CHECK OPTION
Answer: C
53. Which three tasks can be performed using regular expression support in Oracle Database 10g? (Choose three.)
A. It can be used to concatenate two strings.
B. It can be used to find out the total length of the string.
C. It can be used for string manipulation and searching operations.
D. It can be used to format the output for a column or expression having string data.
E. It can be used to find and replace operations for a column or expression having string data.
B. It can be used to find out the total length of the string.
C. It can be used for string manipulation and searching operations.
D. It can be used to format the output for a column or expression having string data.
E. It can be used to find and replace operations for a column or expression having string data.
Answer: CDE
54. Evaluate the CREATE TABLE statement:
CREATE TABLE products (product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY, product_name VARCHAR2(15));
Which statement is true regarding the PROD_ID_PK constraint?
Which statement is true regarding the PROD_ID_PK constraint?
A. It would be created only if a unique index is manually created first.
B. It would be created and would use an automatically created unique index.
C. It would be created and would use an automatically created nonunique index.
D. It would be created and remains in a disabled state because no index is specified in the command.
B. It would be created and would use an automatically created unique index.
C. It would be created and would use an automatically created nonunique index.
D. It would be created and remains in a disabled state because no index is specified in the command.
Answer: B
55. Which two statements are true? (Choose two.)
A. The USER_SYNONYMS view can provide information about private synonyms.
B. The user SYSTEM owns all the base tables and user-accessible views of the data dictionary.
C. All the dynamic performance views prefixed with V$ are accessible to all the database users.
D. The USER_OBJECTS view can provide information about the tables and views created by the user only.
E. DICTIONARY is a view that contains the names of all the data dictionary views that the user can access.
B. The user SYSTEM owns all the base tables and user-accessible views of the data dictionary.
C. All the dynamic performance views prefixed with V$ are accessible to all the database users.
D. The USER_OBJECTS view can provide information about the tables and views created by the user only.
E. DICTIONARY is a view that contains the names of all the data dictionary views that the user can access.
Answer: AE
56. Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seq1
START WITH 100
INCREMENT BY 10
MAXVALUE 200
CYCLE
NOCACHE;
The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL
statement:
SELECT seq1.nextval FROM dual;
START WITH 100
INCREMENT BY 10
MAXVALUE 200
CYCLE
NOCACHE;
The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL
statement:
SELECT seq1.nextval FROM dual;
What is displayed by the SELECT statement?
A. 1
B. 10
C. 100
D. an error
B. 10
C. 100
D. an error
Answer: A
57. Which two statements are true regarding the execution of the correlated subqueries? (Choose two.)
A. The nested query executes after the outer query returns the row.
B. The nested query executes first and then the outer query executes.
C. The outer query executes only once for the result returned by the inner query.
D. Each row returned by the outer query is evaluated for the results returned by the inner query.
B. The nested query executes first and then the outer query executes.
C. The outer query executes only once for the result returned by the inner query.
D. Each row returned by the outer query is evaluated for the results returned by the inner query.
Answer: AD
58. OE and SCOTT are the users in the database. The ORDERS table is owned by OE. Evaluate the statements issued by the DBA in the following sequence:
CREATE ROLE r1;
GRANT SELECT, INSERT ON oe.orders TO r1;
GRANT r1 TO scott;
GRANT SELECT ON oe.orders TO scott;
REVOKE SELECT ON oe.orders FROM scott;
GRANT SELECT, INSERT ON oe.orders TO r1;
GRANT r1 TO scott;
GRANT SELECT ON oe.orders TO scott;
REVOKE SELECT ON oe.orders FROM scott;
What would be the outcome after executing the statements?
A. SCOTT would be able to query the OE.ORDERS table.
B. SCOTT would not be able to query the OE.ORDERS table.
C. The REVOKE statement would remove the SELECT privilege from SCOTT as well as from the role R1.
D. The REVOKE statement would give an error because the SELECT privilege has been granted to the role R1.
B. SCOTT would not be able to query the OE.ORDERS table.
C. The REVOKE statement would remove the SELECT privilege from SCOTT as well as from the role R1.
D. The REVOKE statement would give an error because the SELECT privilege has been granted to the role R1.
Answer: A
59. Evaluate the following SQL statement:
ALTER TABLE hr.emp
SET UNUSED (mgr_id);
SET UNUSED (mgr_id);
Which statement is true regarding the effect of the above SQL statement?
A. Any synonym existing on the EMP table would have to be re-created.
B. Any constraints defined on the MGR_ID column would be removed by the above command.
C. Any views created on the EMP table that include the MGR_ID column would have to be dropped and re-created.
D. Any index created on the MGR_ID column would continue to exist until the DROP UNUSED COLUMNS command is executed.
B. Any constraints defined on the MGR_ID column would be removed by the above command.
C. Any views created on the EMP table that include the MGR_ID column would have to be dropped and re-created.
D. Any index created on the MGR_ID column would continue to exist until the DROP UNUSED COLUMNS command is executed.
Answer: B
60. EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in relation to the EMPDET table?
A. UPDATE empdet SET ename = ‘Amit’ WHERE empno = 1234;
B. DELETE FROM empdet WHERE ename LIKE ‘J%’;
C. CREATE VIEW empvu AS SELECT * FROM empdept;
D. CREATE INDEX empdet_idx ON empdet(empno);
B. DELETE FROM empdet WHERE ename LIKE ‘J%’;
C. CREATE VIEW empvu AS SELECT * FROM empdept;
D. CREATE INDEX empdet_idx ON empdet(empno);
Answer: C
61. In which scenario would you use the ROLLUP operator for expression or columns within a GROUP BY clause?
A. to find the groups forming the subtotal in a row
B. to create group-wise grand totals for the groups specified within a GROUP BY clause
C. to create a grouping for expressions or columns specified within a GROUP BY clause in one direction, from right to left for calculating the subtotals
D. to create a grouping for expressions or columns specified within a GROUP BY clause in all possible directions, which is cross-tabular report for calculating the subtotals
B. to create group-wise grand totals for the groups specified within a GROUP BY clause
C. to create a grouping for expressions or columns specified within a GROUP BY clause in one direction, from right to left for calculating the subtotals
D. to create a grouping for expressions or columns specified within a GROUP BY clause in all possible directions, which is cross-tabular report for calculating the subtotals
Answer: C
62. What are the two subtypes of the IF conditional statement in PL/SQL?
A. if-then-endif and if-then-else
B. if-then-else and if-then-elseif
C. if-then-else and if-then-elseif-endif
D. if-then-else and if-then-elseif-then-else
B. if-then-else and if-then-elseif
C. if-then-else and if-then-elseif-endif
D. if-then-else and if-then-elseif-then-else
Answer: D
63. What are the two variables supported by PL/SQL?
A. Explicit and implicit variables
B. Scalar and composite variables
C. Primary and default variables
D. Scalar and user-defined variables
B. Scalar and composite variables
C. Primary and default variables
D. Scalar and user-defined variables
Answer: B
64. Oracle supports all of the following types of collections except for which one?
A. VARRAYS
B. Nested array
C. Nested table
D. Associative array
B. Nested array
C. Nested table
D. Associative array
Answer: B
65. Which of the following identifiers can include any printable characters, including spaces?
A. Quoted identifiers
B. Predefined identifiers
C. Valid identifiers
D. Invalid identifiers
B. Predefined identifiers
C. Valid identifiers
D. Invalid identifiers
Answer: A
66. Which of the following types of message output contains a chronological log of errors, initialization parameter settings, and administration operations, and also records values for overwritten control filerecords?
A. RMAN messages
B. alert_SID.log
C. sbtio.log
D. Oracle trace file
B. alert_SID.log
C. sbtio.log
D. Oracle trace file
Answer: B
67. Which of the following statements is used to insert, update, or delete bulk data?
A. FOR Loop statement
B. BULK COLLECT INTO statement
C. BULK statement
D. FORALL statement
B. BULK COLLECT INTO statement
C. BULK statement
D. FORALL statement
Answer: D
68. Which of the following exceptions is raised when a user tries to divide a number by zero?
A. VALUE_ERROR
B. OUT_OF_RANGE
C. ZERO_DIVIDE
D. SELF_IS_NULL
B. OUT_OF_RANGE
C. ZERO_DIVIDE
D. SELF_IS_NULL
Answer: C
69. All of the following can be the causes of raising an exception except for which one?
A. A throw statement has executed.
B. An asynchronous exception has occurred.
C. An abnormal execution condition has been synchronously detected.
D. A catch statement has executed.
B. An asynchronous exception has occurred.
C. An abnormal execution condition has been synchronously detected.
D. A catch statement has executed.
Answer: D
70. Which of the following built-in packages supports dynamic SQL?
A. DBMS_SQLB. DBMS_SCHEDULER
C. DBMS_MONITOR
D. DBMS_CRYPTO
C. DBMS_MONITOR
D. DBMS_CRYPTO
Answer: A
71. From which of the following programming languages are the features of PL/SQL programming fetched?
A. C++ programming
B. Java programming
C. C programming
D. Ada programming
B. Java programming
C. C programming
D. Ada programming
Answer: D
72. Which of the following is ideal for querying a database table or view?
A. Range FOR Loop statement
B. Cursor FOR Loop statement
C. While Loop statement
D. Simple Loop statement
B. Cursor FOR Loop statement
C. While Loop statement
D. Simple Loop statement
Answer: B
73. Which of the following errors maps to the INVALID_CURSOR predefined exception in PL/SQL?
A. ORA-10100
B. ORA-10001
C. ORA-01000
D. ORA-01001
B. ORA-10001
C. ORA-01000
D. ORA-01001
Answer: D
74. Which of the following is a sequentially control statement?
A. GOTO
B. LOOPC. EXIT
D. CASE
B. LOOPC. EXIT
D. CASE
Answer: A
75. Which of the following cannot be created and stored in the database?
A. NESTED TABLE
B. VARRAY
C. RECORD
D. ASSOCIATIVE ARRAY
B. VARRAY
C. RECORD
D. ASSOCIATIVE ARRAY
Answer: C
76. In which of the following naming methods are the connect identifiers stored in a localized configuration file named tnsnames.ora?
A. Oracle Names
B. Directory naming
C. Local naming
D. External naming
E. Host naming
B. Directory naming
C. Local naming
D. External naming
E. Host naming
Answer: C
77. You perform differential incremental level 1 backups of your database on each working day and level 0 backup on Sundays, to tape. Which two statements are true about differential incremental backups? (Choose two.)
A. The backup performed on Sundays contains all the blocks that have ever been used in the database.
B. The backup performed on Sundays contains all the blocks that have changed since the last level 1 backup.
C. The backup performed on each working day contains all the blocks that have changed since the last level 0 backup.
D. The backup performed on Monday contains all the blocks that have changed since the level 0 backup, and every other working day contains all the blocks that have changed since the level 1 backup
B. The backup performed on Sundays contains all the blocks that have changed since the last level 1 backup.
C. The backup performed on each working day contains all the blocks that have changed since the last level 0 backup.
D. The backup performed on Monday contains all the blocks that have changed since the level 0 backup, and every other working day contains all the blocks that have changed since the level 1 backup
Answer: AD
78. Identify two situations in which you can use Data Recovery Advisor for recovery. (Choose two.)
A. The database files are corrupted when the database is open.
B. The user has dropped an important table that needs to be recovered.
C. The archived redo log files are missing for which the backup is not available.
D. The database is not opening because the required database files are missing.
B. The user has dropped an important table that needs to be recovered.
C. The archived redo log files are missing for which the backup is not available.
D. The database is not opening because the required database files are missing.
Answer: AD
79. Your database is open and the LISTENER listener is running. The new DBA of the system stops the listener by using the command:
LSNRCTL> STOP
What happens to the sessions that are presently connected to the database instance?
A. The sessions are able to perform only queries.
B. The sessions are not affected and continue to function normally.
C. The sessions are terminated and the active transactions are rolled back.
D. The sessions are not allowed to perform any operations till the listener is started.
What happens to the sessions that are presently connected to the database instance?
A. The sessions are able to perform only queries.
B. The sessions are not affected and continue to function normally.
C. The sessions are terminated and the active transactions are rolled back.
D. The sessions are not allowed to perform any operations till the listener is started.
Answer: B
80. You executed this command to create a temporary table:
SQL> CREATE GLOBAL TEMPORARY TABLE report_work_area (startdate DATE, enddate DATE, class CHAR(20)) ON COMMIT PRESERVE ROWS;
Which statement is true about the rows inserted into the REPORT_WORK_AREA table during a transaction?
A. The rows stay in the table only until session termination.
B. The rows stay in the table only until the next transaction starts on the table.
C. The rows are visible to all current sessions after the transaction is committed.
D. The rows stay available for subsequent sessions after the transaction is committed.
B. The rows stay in the table only until the next transaction starts on the table.
C. The rows are visible to all current sessions after the transaction is committed.
D. The rows stay available for subsequent sessions after the transaction is committed.
Answer: A
81. In which of the scenarios will the DBA perform recovery? (Choose all that apply.)
A. The alert log file is corrupted.
B. A tablespace is accidentally dropped.
C. One of the redo log members is corrupted.
D. A database user terminates the session abnormally.
E. The hard disk on which the data file is stored is corrupted.
B. A tablespace is accidentally dropped.
C. One of the redo log members is corrupted.
D. A database user terminates the session abnormally.
E. The hard disk on which the data file is stored is corrupted.
Answer: BE
82. Note the following structures in your database server:
1. Extents
2. OS Blocks
3. Tablespace
4. Segments
5. Oracle Data Block
2. OS Blocks
3. Tablespace
4. Segments
5. Oracle Data Block
Which option has the correct arrangement of these structures from the smallest to the largest?
A. 2, 5, 1, 4, 3
B. 1, 2, 3, 4, 5
C. 5, 2, 1, 3, 4
D. 2, 1, 5, 4, 3
B. 1, 2, 3, 4, 5
C. 5, 2, 1, 3, 4
D. 2, 1, 5, 4, 3
Answer: A
83. You are working on an instance started using the SPFILE. You want to move the Flash Recovery Area of your database to a new location. You want the Flashback log files to be stored in the new location. Given below are the steps to accomplish the task in random order:
1) Shut down the instance.
2) Change the value of the DB_RECOVERY_FILE_DEST initialization parameter to a new value.
3) Execute the ALTER DATABASE FLASHBACK OFF command.
4) Start up the instance and mount the database.
5) Execute the ALTER DATABASE FLASHBACK ON command.
6) Open the database.
2) Change the value of the DB_RECOVERY_FILE_DEST initialization parameter to a new value.
3) Execute the ALTER DATABASE FLASHBACK OFF command.
4) Start up the instance and mount the database.
5) Execute the ALTER DATABASE FLASHBACK ON command.
6) Open the database.
Select the correct order in which these tasks need to be performed.
A. 2, 1, 4, 3, 5, 6
B. 1, 4, 3, 2, 6, 5
C. 1, 4, 2, 6, 3, 5
D. 3, 2, 1, 4, 5, 6
B. 1, 4, 3, 2, 6, 5
C. 1, 4, 2, 6, 3, 5
D. 3, 2, 1, 4, 5, 6
Answer: A
84. A user, who is authenticated externally, logs in to a remote machine and connects to the database instance. What action would you take to ensure that a user cannot connect to the database instance by merely logging in to a remote machine?
A. Set REMOTE_OS_ROLES to FALSE.
B. Set the OS_ROLES parameter to FALSE.
C. Set the REMOTE_OS_AUTHENT parameter to FALSE.
D. Set the REMOTE_LOGIN_PASSWORD_FILE parameter to NONE.
B. Set the OS_ROLES parameter to FALSE.
C. Set the REMOTE_OS_AUTHENT parameter to FALSE.
D. Set the REMOTE_LOGIN_PASSWORD_FILE parameter to NONE.
Answer: C
85. Your database instance is started using the server parameter file (SPFILE). You executed a command to change the value of the LOG_BUFFER initialization parameter:
ALTER SYSTEM SET LOG_BUFFER=32M SCOPE=BOTH;
What would be the outcome of this command?
A. The command succeeds only if Automatic Memory Management is not enabled.
B. The command succeeds, but you need to restart the database for changes to take effect.
C. The command returns an error because the size of the redo log buffer cannot be changed dynamically.
D. The parameter value is changed and it comes into effect as soon as space becomes available in the System Global Area (SGA).
B. The command succeeds, but you need to restart the database for changes to take effect.
C. The command returns an error because the size of the redo log buffer cannot be changed dynamically.
D. The parameter value is changed and it comes into effect as soon as space becomes available in the System Global Area (SGA).
Answer: C
86. The database instance is currently using SPFILE. View the Exhibit and examine the error that you received while running the DB Structure Integrity check.
Given below are the steps to recover from the error in random order:
1. Shut down the instance, if not already done.
2. Copy one of the remaining control files to a new location.
3. Change the value of the CONTROL_FILES initialization parameter to correspond to the new location
of the control files.
4. Start up the database instance to the NOMOUNT stage.
5. Recover the database to the point of failure of the control file.
6. Open the database.
2. Copy one of the remaining control files to a new location.
3. Change the value of the CONTROL_FILES initialization parameter to correspond to the new location
of the control files.
4. Start up the database instance to the NOMOUNT stage.
5. Recover the database to the point of failure of the control file.
6. Open the database.
Identify the correct sequence of steps?
A. 1, 2, 4, 3, 5, 6
B. B. 2, 4, 3, 5, 6; 1 not required
C. C. 4, 5, 6, 2, 3; 1 not required
D. D. 5, 2, 3, 4; 1 and 6 not required
B. B. 2, 4, 3, 5, 6; 1 not required
C. C. 4, 5, 6, 2, 3; 1 not required
D. D. 5, 2, 3, 4; 1 and 6 not required
Answer: A
87. User A executes the following command to drop a large table in your database:
SQL> DROP TABLE trans;
while the drop table operation is in progress, user B executes the following command on the same table:
SQL> DELETE FROM trans WHERE tr_type=’SL’;
while the drop table operation is in progress, user B executes the following command on the same table:
SQL> DELETE FROM trans WHERE tr_type=’SL’;
Which statement is true regarding the DELETE command?
A. It fails to delete the records because the records are locked in SHARE mode.
B. It deletes the rows successfully because the table is locked in SHARE mode.
C. It fails to delete the records because the table is locked in EXCLUSIVE mode.
D. It deletes the rows successfully because the table is locked in SHARE ROW EXCLUSIVE mode.
B. It deletes the rows successfully because the table is locked in SHARE mode.
C. It fails to delete the records because the table is locked in EXCLUSIVE mode.
D. It deletes the rows successfully because the table is locked in SHARE ROW EXCLUSIVE mode.
Answer: C
88. Which is the correct description of a pinned buffer in the database buffer cache?
A. The buffer is currently being accessed.
B. The buffer is empty and has not been used.
C. The contents of the buffer have changed and must be flushed to the disk by the DBWn process.
D. The buffer is a candidate for immediate aging out and its contents are synchronized with the block contents on the disk.
B. The buffer is empty and has not been used.
C. The contents of the buffer have changed and must be flushed to the disk by the DBWn process.
D. The buffer is a candidate for immediate aging out and its contents are synchronized with the block contents on the disk.
Answer: A
89. You are working on a new Oracle Database 11g server, where only the software is installed and no database is created. You plan to create a database on this server using the Database Configuration Assistant (DBCA).
Some of the requirements set for your database creation task are:
1. Configure the database to operate in shared server mode.
2. Disable automatic maintenance tasks such as optimizer statistics collection.
3. Configure a naming method to help the remote user connect to the database instance.
4. Use Automatic Storage Management (ASM) for storing the database files.
5. Configure daily database backup to flash recovery area.
6. Configure Enterprise Manager Database Control to manage the database.
2. Disable automatic maintenance tasks such as optimizer statistics collection.
3. Configure a naming method to help the remote user connect to the database instance.
4. Use Automatic Storage Management (ASM) for storing the database files.
5. Configure daily database backup to flash recovery area.
6. Configure Enterprise Manager Database Control to manage the database.
Which of these requirements can be met while creating the database?
A. 4 and 6
B. 2, 3, 4, and 6
C. 1, 2, 4, 5, and 6
D. 1, 2, 3, 4, 5, and 6
B. 2, 3, 4, and 6
C. 1, 2, 4, 5, and 6
D. 1, 2, 3, 4, 5, and 6
Answer: C
90. Which two are the prerequisites to enable Flashback Data Archive? (Choose two.)
A.Undo retention guarantee must be enabled.
B.Database must be running in archivelog mode.
C.Automatic undo management must be enabled.
D.The tablespace on which the Flashback Data Archive is created must be managed with Automatic Segment Space Management (ASSM).
B.Database must be running in archivelog mode.
C.Automatic undo management must be enabled.
D.The tablespace on which the Flashback Data Archive is created must be managed with Automatic Segment Space Management (ASSM).
Answer: CD
91. Identify two situations in which you can use Data Recovery Advisor for recovery. (Choose two.)
A.The database files are corrupted when the database is open.
B.The archived log files are missing for which backup is not available.
C.The user has dropped an important table that needs to be recovered.
D.You are not able to start up the database instance because the required database files are missing.
B.The archived log files are missing for which backup is not available.
C.The user has dropped an important table that needs to be recovered.
D.You are not able to start up the database instance because the required database files are missing.
Answer: AD
92. Which dependent object will get invalidated even if it is not affected by the table redefinition?
A.views
B.triggers
C.packages
D.synonyms
B.triggers
C.packages
D.synonyms
Answer: B
93. You are managing an Oracle 11g database with ASM storage, for which the COMPATIBLE initialization parameter is set to 11.1.0. In the ASM instance, the COMPATIBLE.RDBMS attribute for the disk group is set to 10.2 and the COMPATIBLE.ASM attribute is set to 11.1.
Which two statements are true in this scenario for the features enabled for ASM? (Choose two.)
A.The ASM-preferred mirror read feature is enabled.
B.The ASM supports variable sizes for extents of 1, 8, and 64 allocation units.
C.The ASM disk is dropped immediately from a disk group when it becomes unavailable.
D.The RDBMS always reads the primary copy of a mirrored extent of the ASM disk group.
B.The ASM supports variable sizes for extents of 1, 8, and 64 allocation units.
C.The ASM disk is dropped immediately from a disk group when it becomes unavailable.
D.The RDBMS always reads the primary copy of a mirrored extent of the ASM disk group.
Answer: AB
94. Which statement describes the effect of the execution of the above PL/SQL block?
A.The plan baselines are verified with the SQL profiles.
B.All fixed plan baselines are converted into nonfixed plan baselines.
C.All the nonaccepted SQL profiles are accepted into the plan baseline.
D.The nonaccepted plans in the SQL Management Base are verified with the existing plan baselines.
B.All fixed plan baselines are converted into nonfixed plan baselines.
C.All the nonaccepted SQL profiles are accepted into the plan baseline.
D.The nonaccepted plans in the SQL Management Base are verified with the existing plan baselines.
Answer: D
95. Which is the source used by Automatic SQL Tuning that runs as part of the AUTOTASK framework?
A.SQL statements that are part of the AWR baseline only
B.SQL statements based on the AWR top SQL identification
C.SQL statements that are part of the available SQL Tuning Set (STS) only
D.SQL statements that are available in the cursor cache and executed by a user other than SYS
B.SQL statements based on the AWR top SQL identification
C.SQL statements that are part of the available SQL Tuning Set (STS) only
D.SQL statements that are available in the cursor cache and executed by a user other than SYS
Answer: B
96. Which two statements are true with respect to the maintenance window? (Choose two.)
A.A DBA can enable or disable an individual task in all maintenance windows.
B.A DBA cannot change the duration of the maintenance window after it is created.
C.In case of a long maintenance window, all Automated Maintenance Tasks are restarted every four hours.
D.A DBA can control the percentage of the resource allocated to the Automated Maintenance Tasks in each window.
B.A DBA cannot change the duration of the maintenance window after it is created.
C.In case of a long maintenance window, all Automated Maintenance Tasks are restarted every four hours.
D.A DBA can control the percentage of the resource allocated to the Automated Maintenance Tasks in each window.
Answer: AD
97. What are the recommendations for Oracle Database 11g installation to make it Optimal Flexible Architecture (OFA)-compliant? (Choose all that apply.)
A.ORACLE_BASE should be set explicitly.
B.An Oracle base should have only one Oracle home created in it.
C.Flash recovery area and data file location should be on separate disks.
D.Flash recovery area and data file location should be created under Oracle base in a non-Automatic Storage Management (ASM) setup.
B.An Oracle base should have only one Oracle home created in it.
C.Flash recovery area and data file location should be on separate disks.
D.Flash recovery area and data file location should be created under Oracle base in a non-Automatic Storage Management (ASM) setup.
Answer: ACD
98. During the installation of Oracle Database 11g, you do not set ORACLE_BASE explicitly. You selected the option to create a database as part of the installation. How would this environment variable setting affect the installation?
A.The installation terminates with an error.
B.The installation proceeds with the default value without warnings and errors.
C.The installation proceeds with the default value but it would not be an OFA-compliant database.
D.The installation proceeds with the default value but a message would be generated in the alert log file.
B.The installation proceeds with the default value without warnings and errors.
C.The installation proceeds with the default value but it would not be an OFA-compliant database.
D.The installation proceeds with the default value but a message would be generated in the alert log file.
Answer: D
99. Which two statements are true regarding the starting of the database instance using the following command? (Choose two.)
SQL>STARTUP UPGRADE
A.It enables all system triggers.
B.It allows only SYSDBA connections.
C.It ensures that all job queues remain active during the upgrade process.
D.It sets system initialization parameters to specific values that are required to enable database upgrade scripts to be run.
A.It enables all system triggers.
B.It allows only SYSDBA connections.
C.It ensures that all job queues remain active during the upgrade process.
D.It sets system initialization parameters to specific values that are required to enable database upgrade scripts to be run.
Answer: BD
100. Your organization decided to upgrade the existing Oracle 10g database to Oracle 11g database in a multiprocessor environment. At the end of the upgrade, you observe that the DBA executes the following script:
SQL> @utlrp.sql
What is the significance of executing this script?
A.It performs parallel recompilation of only the stored PL/SQL code.
B.It performs sequential recompilation of only the stored PL/SQL code.
C.It performs parallel recompilation of any stored PL/SQL as well as Java code.
D.It performs sequential recompilation of any stored PL/SQL as well as Java code.
B.It performs sequential recompilation of only the stored PL/SQL code.
C.It performs parallel recompilation of any stored PL/SQL as well as Java code.
D.It performs sequential recompilation of any stored PL/SQL as well as Java code.
Answer: C
101. You installed Oracle Database 11g and are performing a manual upgrade of the Oracle9i database. As a part of the upgrade process, you execute the following script:
SQL>@utlu111i.sql
Which statement about the execution of this script is true?
A.It must be executed from the Oracle Database 11g environment.
B.It must be executed only after the SYSAUX tablespace has been created.
C.It must be executed from the environment of the database that is being upgraded.
D.It must be executed only after AUTOEXTEND is set to ON for all existing tablespaces
E.It must be executed from both the Oracle Database 11g and Oracle Database 9i environments.
B.It must be executed only after the SYSAUX tablespace has been created.
C.It must be executed from the environment of the database that is being upgraded.
D.It must be executed only after AUTOEXTEND is set to ON for all existing tablespaces
E.It must be executed from both the Oracle Database 11g and Oracle Database 9i environments.
Answer: C
102. Which two statements about Oracle Direct Network File System (NFS) are true? (Choose two.)
A.It bypasses the OS file system cache.
B.A separate NFS interface is required for use across Linux, UNIX, and Windows platforms.
C.It uses the operating system kernel NFS layer for user tasks and network communication modules.
D.File systems need not be mounted by the kernel NFS system when being served through Direct NFS.
E.Oracle Disk Manager can manage NFS on its own, without using the operating system kernel NFS driver.
B.A separate NFS interface is required for use across Linux, UNIX, and Windows platforms.
C.It uses the operating system kernel NFS layer for user tasks and network communication modules.
D.File systems need not be mounted by the kernel NFS system when being served through Direct NFS.
E.Oracle Disk Manager can manage NFS on its own, without using the operating system kernel NFS driver.
Answer: AE
103. You decided to use Direct NFS configuration in a non-RAC Oracle installation and created the oranfstab file in /etc.
Which two statements are true regarding this oranfstab file? (Choose two.)
A.Its entries are specific to a single database.
B.It contains file systems that have been mounted by Direct NFS.
C.It is globally available to all Oracle 11g databases on the machine.
D.It contains file systems that have been mounted by the kernel NFS system.
B.It contains file systems that have been mounted by Direct NFS.
C.It is globally available to all Oracle 11g databases on the machine.
D.It contains file systems that have been mounted by the kernel NFS system.
Answer: CD
104. Which two statements are true regarding hot patching? (Choose two.)
A.It requires relinking of the Oracle binary.
B.It does not require database instance shutdown.
C.It can detect conflicts between two online patches.
D.It is available for installing all patches on all platforms.
E.It works only in a single database instance environment.
B.It does not require database instance shutdown.
C.It can detect conflicts between two online patches.
D.It is available for installing all patches on all platforms.
E.It works only in a single database instance environment.
Answer: BC
105. In which two aspects does hot patching differ from conventional patching? (Choose two.)
A.It consumes more memory compared with conventional patching.
B.It can be installed and uninstalled via OPatch unlike conventional patching.
C.It takes more time to install or uninstall compared with conventional patching.
D.It does not require down time to apply or remove unlike conventional patching.
E.It is not persistent across instance startup and shutdown unlike conventional patching.
B.It can be installed and uninstalled via OPatch unlike conventional patching.
C.It takes more time to install or uninstall compared with conventional patching.
D.It does not require down time to apply or remove unlike conventional patching.
E.It is not persistent across instance startup and shutdown unlike conventional patching.
Answer: AD
106. Which alternative command could you use to run the startmydb.sql script with SQL*Plus to start your Oracle 10g database?
A.sqlplus @startmydb.sql
B.sqlplus -Sstartmydb.sql
C.sqlplus -S @startmydb.sql
D.sqlplus /NOLOG @startmydb.sql
B.sqlplus -Sstartmydb.sql
C.sqlplus -S @startmydb.sql
D.sqlplus /NOLOG @startmydb.sql
Answer: D
107. Examine the following parameter settings from an initialization (init.ora) file:
DB_CREATE_FILE_DEST = ‘/u01/oradata/’
DB_CREATE_ONLINE_LOG_DEST_1 = ‘/u02/oradata/’
DB_CREATE_ONLINE_LOG_DEST_2 = ‘/u03/oradata/’
If you create an Oracle Managed Files (OMF) database using these settings, what is the result?
DB_CREATE_ONLINE_LOG_DEST_1 = ‘/u02/oradata/’
DB_CREATE_ONLINE_LOG_DEST_2 = ‘/u03/oradata/’
If you create an Oracle Managed Files (OMF) database using these settings, what is the result?
A.The data files, temp files, and control file will be on device /u01; one online redo log group will be on device /u02;
another redo log group will be on device /u03.
B.The data files and temp files will be on device /u01; one copy of the control file and one online redo log group will be
on device /u02; another copy of the control file and a second redo log group will be on device /u03.
C.The data files, temp files, online redo log files, and control file will be on device /u01; multiplexed copies of the
archive log files will be created: one set on device /u02, and another set on device /u03.
D.The data files and temp files will be on device /u01; one copy of the control file and the first member in each online
redo log group will be on device /u02; another copy of the control file and a second member of each redo log group will
be on device /u03.
another redo log group will be on device /u03.
B.The data files and temp files will be on device /u01; one copy of the control file and one online redo log group will be
on device /u02; another copy of the control file and a second redo log group will be on device /u03.
C.The data files, temp files, online redo log files, and control file will be on device /u01; multiplexed copies of the
archive log files will be created: one set on device /u02, and another set on device /u03.
D.The data files and temp files will be on device /u01; one copy of the control file and the first member in each online
redo log group will be on device /u02; another copy of the control file and a second member of each redo log group will
be on device /u03.
Answer: D
108. What is true regarding a shared, server-side parameter file for a Real Application Clusters database?
A. It can contain parameters with distinct values for each instance.
B.It can contain only parameters with identical values for each instance.
C.It must contain an IFILE parameter for each instance’s individual parameter file.
D.It must be located in the default location for the primary instance’s parameter file.
B.It can contain only parameters with identical values for each instance.
C.It must contain an IFILE parameter for each instance’s individual parameter file.
D.It must be located in the default location for the primary instance’s parameter file.
Answer: A
109. After changing the PCTFREE value of the automatic space management table EMPLOYEES, which task can you perform in order for your change to take effect immediately?
B.Execute the command ANALYZE TABLE employees COMPUTE STATISTICS.
C.Execute the command DBMS_STATS.GATHER_TABLE_STATISTICS on the EMPLOYEES table.
D.Execute the DBMS_REPAIR.SEGMENT_FIX_STATUS procedure on the EMPLOYEES table.
C.Execute the command DBMS_STATS.GATHER_TABLE_STATISTICS on the EMPLOYEES table.
D.Execute the DBMS_REPAIR.SEGMENT_FIX_STATUS procedure on the EMPLOYEES table.
Answer: D
110. You can use Oracle Flashback technology to look at past activity in your database.
What are two other characteristics of Oracle Flashback technology? (Choose two.)
A.Oracle Flashback technology uses undo information to construct consistent data.
B.You can use a PL/SQL cursor opened while using an Oracle Flashback image to perform DML once you deactivate
the Flashback option in your session.
C.You can only use an Oracle Flashback view of the data if the required records are still in the online redo log files.
D.Oracle Flashback lists the DML that was executed during the period you identify when initiating your session.
B.You can use a PL/SQL cursor opened while using an Oracle Flashback image to perform DML once you deactivate
the Flashback option in your session.
C.You can only use an Oracle Flashback view of the data if the required records are still in the online redo log files.
D.Oracle Flashback lists the DML that was executed during the period you identify when initiating your session.
Answer: AB
111.What are the two subtypes of the IF conditional statement in PL/SQL?
A. if-then-endif and if-then-else
B. if-then-else and if-then-elseif
C. if-then-else and if-then-elseif-endif
D. if-then-else and if-then-elseif-then-else
B. if-then-else and if-then-elseif
C. if-then-else and if-then-elseif-endif
D. if-then-else and if-then-elseif-then-else
Answer: D
112.What are the two variables supported by PL/SQL?
A. Explicit and implicit variables
B. Scalar and composite variables
C. Primary and default variables
D. Scalar and user-defined variables
B. Scalar and composite variables
C. Primary and default variables
D. Scalar and user-defined variables
Answer: B
113.Oracle supports all of the following types of collections except for which one?
A. VARRAYS
B. Nested array
C. Nested table
D. Associative array
B. Nested array
C. Nested table
D. Associative array
Answer: B
114.Which of the following identifiers can include any printable characters, including spaces?
A. Quoted identifiers
B. Predefined identifiers
C. Valid identifiers
D. Invalid identifiers
B. Predefined identifiers
C. Valid identifiers
D. Invalid identifiers
Answer: A
115.Which of the following types of message output contains a chronological log of errors, initialization parameter settings, and administration operations, and also records values for overwritten control file records?
A. RMAN messages
B. alert_SID.log
C. sbtio.log
D. Oracle trace file
B. alert_SID.log
C. sbtio.log
D. Oracle trace file
Answer: B
116.Which of the following statements is used to insert, update, or delete bulk data?
A. FOR Loop statement
B. BULK COLLECT INTO statement
C. BULK statement
D. FORALL statement
B. BULK COLLECT INTO statement
C. BULK statement
D. FORALL statement
Answer: D
117.Which of the following exceptions is raised when a user tries to divide a number by zero?
A. VALUE_ERROR
B. OUT_OF_RANGE
C. ZERO_DIVIDE
D. SELF_IS_NULL
B. OUT_OF_RANGE
C. ZERO_DIVIDE
D. SELF_IS_NULL
Answer: C
118.All of the following can be the causes of raising an exception except for which one?
A. A throw statement has executed.
B. An asynchronous exception has occurred.
C. An abnormal execution condition has been synchronously detected.
D. A catch statement has executed.
B. An asynchronous exception has occurred.
C. An abnormal execution condition has been synchronously detected.
D. A catch statement has executed.
Answer: D
119.Which of the following built-in packages supports dynamic SQL?
A. DBMS_SQL
B. DBMS_SCHEDULER
C. DBMS_MONITOR
D. DBMS_CRYPTO
B. DBMS_SCHEDULER
C. DBMS_MONITOR
D. DBMS_CRYPTO
Answer: A
120.From which of the following programming languages are the features of PL/SQL programming fetched?
A. C++ programming
B. Java programming
C. C programming
D. Ada programming
B. Java programming
C. C programming
D. Ada programming
Answer: D
121.Which of the following is ideal for querying a database table or view?
A. Range FOR Loop statement
B. Cursor FOR Loop statement
C. While Loop statement
D. Simple Loop statement
B. Cursor FOR Loop statement
C. While Loop statement
D. Simple Loop statement
Answer: B
122.Which of the following errors maps to the INVALID_CURSOR predefined exception in PL/SQL?
A. ORA-10100
B. ORA-10001
C. ORA-01000
D. ORA-01001
B. ORA-10001
C. ORA-01000
D. ORA-01001
Answer: D
123.Which of the following is a sequentially control statement?
A. GOTO
B. LOOP
C. EXIT
D. CASE
B. LOOP
C. EXIT
D. CASE
Answer: A
124.Which of the following cannot be created and stored in the database?
A. NESTED TABLE
B. VARRAY
C. RECORD
D. ASSOCIATIVE ARRAY
B. VARRAY
C. RECORD
D. ASSOCIATIVE ARRAY
Answer: C
125.In which of the following naming methods are the connect identifiers stored in a localized Configuration file named tnsnames.ora?
A. Oracle Names
B. Directory naming
C. Local naming
D. External naming
E. Host naming
B. Directory naming
C. Local naming
D. External naming
E. Host naming
Answer: C
126.Which of the following correctly represents floating point number literals?
A. F6.34
B. 6.34
C. 6.F34
D. 6.34F
B. 6.34
C. 6.F34
D. 6.34F
Answer: D
127.Which of the following clauses indicates that a function is cached only once in the SGA and is available across sessions?
A. PIPELINED
B. PARALLEL_ENABLE
C. RESULT_CACHE
D. DETERMINISTIC
B. PARALLEL_ENABLE
C. RESULT_CACHE
D. DETERMINISTIC
Answer: C
128.Which of the following is another name for bind variable?
A. User-defined variable
B. System variable
C. Session-level variable
D. Default variable
B. System variable
C. Session-level variable
D. Default variable
Answer: C
129.Which of the following exceptions is raised when the value of amount plus the offset exceeds the maximum size of LOB allowed by the database?
A. Procedure
B. Function
C. Cursor
D. Access
B. Function
C. Cursor
D. Access
Answer: D
130.Which of the following terms is used for the declaration section of the named-block programs?
A. Wrapper
B. Header
C. Declarer
D. Cursor
B. Header
C. Declarer
D. Cursor
Answer: B
131.All of the following statements about a named system exception are true except for which one?
A. These exceptions are caught by referencing the standard name within an exception-handling routine.
B. These exceptions are declared explicitly.
C. These exceptions are raised when a predefined Oracle error occurs.
D. NO_DATA_FOUND is an example of a named system exception.
B. These exceptions are declared explicitly.
C. These exceptions are raised when a predefined Oracle error occurs.
D. NO_DATA_FOUND is an example of a named system exception.
Answer: B
132.All of the following statements are true about an explicit cursor except for which one?
A. An explicit cursor can be declared in any declaration section of a PL/SQL block.
B. A cursor name is not a PL/SQL variable.
C. The name of an explicit cursor can be up to 30 characters in length.
D. Values can be assigned to a cursor.
B. A cursor name is not a PL/SQL variable.
C. The name of an explicit cursor can be up to 30 characters in length.
D. Values can be assigned to a cursor.
Answer: D
133.Which of the following errors is raised when placeholders are put inside the overriding signature of an INSERT statement?
A.ORA-01006
B. ORA-06502
C. ORA-00928
D. PLS-00049
B. ORA-06502
C. ORA-00928
D. PLS-00049
Answer: C
134.Which of the following is the first step in using a cursor?
A. Open the cursor
B. Fetch from the cursor
C. Define the cursor
D. Close the cursor
B. Fetch from the cursor
C. Define the cursor
D. Close the cursor
Answer: C
135.All of the following statements about the RAISE_APPLICATION_ERROR procedure are true except for which one?
A. RAISE_APPLICATION_ERROR is used to create a unique id for a user-defined exception.
B. RAISE_APPLICATION_ERROR raises an exception and handles it.
C. RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the userdefined error messages along with the error number.
D. Whenever a message is displayed using RAISE_APPLICATION_ERROR, all previous transactions which are not committed within the PL/SQL block are rolled back automatically.
B. RAISE_APPLICATION_ERROR raises an exception and handles it.
C. RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the userdefined error messages along with the error number.
D. Whenever a message is displayed using RAISE_APPLICATION_ERROR, all previous transactions which are not committed within the PL/SQL block are rolled back automatically.
Answer: B
136.Which of the following operators allows users to check whether a VARRAY or NESTED TABLE collection is a subset of a mirrored datatype?
A. SUBMULTISET
B. MEMBER OF
C. EMPTY
D. ISA SET
B. MEMBER OF
C. EMPTY
D. ISA SET
Answer: A
137.Which of the following correctly represents the default format mask for dates in PL/SQL programming?
A. DD-MON-YY
B. MON-DD-YY
C. MON-DD-YYYY
D. DD-MON-YYYY
B. MON-DD-YY
C. MON-DD-YYYY
D. DD-MON-YYYY
Answer: D
138.What are the two types of CASE statements in PL/SQL?
A. Simple and compound
B. Simple and iterative
C. Direct and indirect
D. Simple and searched
B. Simple and iterative
C. Direct and indirect
D. Simple and searched
Answer: D
139.Which of the following is NOT a part of a PL/SQL exception?
A. A return value
B. A message
C. Type of exception
D. An Error Code
B. A message
C. Type of exception
D. An Error Code
Answer: A
140.All of the following statements are true about an exception except for which one?
A. The scope of an exception declared within a block is global for the block and local for all of its sub blocks.
B. The sub-block can refer to the global exception only when the exception name is qualified with the block label (block_label.exception_name).
C. When a global exception is declared again within a sub-block, the local declaration takes priority over the global declaration.
D. An exception cannot be declared more than once in a single block.
B. The sub-block can refer to the global exception only when the exception name is qualified with the block label (block_label.exception_name).
C. When a global exception is declared again within a sub-block, the local declaration takes priority over the global declaration.
D. An exception cannot be declared more than once in a single block.
Answer: A
