Oracle DBA and PL/SQL Practice Test
Test your knowledge of Oracle database administration, SQL, PL/SQL programming, and Oracle Forms with this comprehensive practice quiz.
Questions
In a client-server application connection with no middle-tier, what are the five server stack components?
- Oracle database, TTC, Oracle Net, OPS, and Protocol
- Oracle database, Oracle Net, OSI, TTC, and Protocol
- Forms/SQL*Plus, OPS, TTC, Oracle Net, and Protocol
- Oracle Net, TTC, OPS, OCI, and Protocol
In which clauses of a SELECT statement can substitution variables be used?
- The SELECT and FROM clauses, but not the WHERE clause
- The SELECT, FROM, WHERE, and GROUP BY clauses, but not the ORDER BY clause
- The SELECT, WHERE, GROUP BY, and ORDER BY clauses, but not the FROM clause
- The SELECT and FROM clauses, but not the WHERE clause
- The SELECT, FROM, WHERE, GROUP BY, ORDER BY, and HAVING clauses
What does the initialization parameter FAST_START_MTTR_TARGET determine?
- The instance recovery time
- The undo retention period
- The database restore time
- The number of redo log groups
_____________ can be declared by us for the queries that return more than one row.
- Implicit cursors
- Explicit cursors
- REF cursor
- Normal cursor
What should be done to import data and to avoid migration?
- Use the CASCADE option during import.
- Use the COMPRESS option during export.
- Use the RECORDLENGTH option during export.
- Coalesce free space.
You are declaring a variable of data type NUMBER in a PL/SQL block. During the execution of the PL/SQL block, a query returns seven rows, and the next FETCH statement is issued. If the variable was assigned a value by using %ROWCOUNT attribute, which of the following values will be stored in it?
- 0
- 7
- 8
- NULL
What will be the result of the given query?
SELECT NULLIF(4,4) from dual
- 4
- NULL
- 1
- NONE
Three possible credit ratings are assigned to customers: Poor, Good, and Excellent (represented numerically by 1, 2, and 3). A RATING column was added to the CUSTOMERS table and the DBA was asked to add an item to the form so that data entry clerk can populate the RATING column. To restrict data entry clerks to one of these three values, the DBA decided to create a radio group for the Rating item. With the Customers block selected in the Layout Editor, he used the radio button to create three radio buttons on the canvas. He then set the labels (Poor, Good, and Excellent) and values (1, 2, and 3) for the radio buttons. He set the name of the radio group to RATING, its Data Type to Number, and its Database column to Rating. He then ran the form to test it. When he executed a query, however, no records were returned, although there were many customer records in the database. What can he do to make the query return the records?
- Set the Mapping of Other Values property for the radio group to Null.
- Create a fourth radio button for an undetermined credit rating and leave its value blank.
- Create a fourth radio button for an undetermined credit rating and explicitly set its value to Null.
- Choose a different type of input, because radio groups do not allow query of Null values.
- None of the above
The local naming method is being used to connect to a remote server and the service name is being provided in the connection request. This service name is mapped to a network address contained in a _________.
- Connect Descriptor
- Connect Identifier
- Connect String
- Connected Directory
What should be done to import data and to avoid migration?
- Use the CASCADE option during import.
- Use the COMPRESS option during export.
- Use the RECORDLENGTH option during export.
- Coalesce free space.
Which three definitions are associated with implicit cursors?
- %rowtype, %toomanyrows, %found
- %found, %notfound, %rowcount
- %rowtype, %rowcount, %notfound
- None of the above
- All of the above
What is a correlated sub query?
- The result retrieved by the inner query is fed to the outer query.
- A correlated sub-query is dependent upon the outer query.
- The outer query gives a reference to the sub query.
- Both (1) and (2)
A team is designing the backup and recovery strategy for a company. Management's priority is to minimize downtime. Which of the following business requirements should they focus on?
- MTBF
- RMAN
- MTTR
- Evolutionary Process
What type of failure occurs when Oracle fails due to an operating system or computer hardware failure?
- Application failure
- Instance failure
- Media failure
- Rollback failure
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
EMPLOYEES:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2 (25)
LAST_NAME VARCHAR2 (25)
HIRE_DATE DATE
NEW_EMPLOYEES:
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2 (60)
Which MERGE statement is valid?
- MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);
- MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);
- MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);
- MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees VALUES (e.employee_id, e.first_name ||', '||e.! last_name);
The CKPT process is optional. Which process assumes these duties if CKPT is not active?
- PMON
- SMON
- ARCH
- LGWR
Which privilege can only be granted to a user and not to a role?
- DELETE
- ALTER
- INSERT
- EXECUTE
- REFERENCES
Examine the syntax for creating a DEPARTMENT table:
CREATE TABLE department(
Deptno NUMBER(4),
Dname VARCNAR2(30),
mgr NUMBER(6),
Loc NUMBER(4))
STORAGE(INITIAL 200K NEXT 200K
PCTINCREASE 50 MINEXTENTS 1 MAXEXTENTS 5)
TABLESPACE userdata;
What is the size defined for the fifth extent?
- 200 K
- 300 K
- 450 K
- 675 K
Which command would revoke the ROLE_DATA role from all users?
- REVOKE role_data FROM ALL;
- REVOKE role_data FROM PUBLIC;
- REVOKE role_data FROM default;
- REVOKE role_data FROM ALL_USERS;
MARY is writing a Forms application, and plans to include a Hierarchical Tree Item. What should she do to facilitate this functionality?
- Write Java wrapper classes to integrate the Hierarchical Tree item into Forms.
- Add a special layer of Java code to register the properties and methods of the Hierarchical Tree Item.
- Use the FTree PL/SQL Package and its built-ins to register and invoke the Hierarchical Tree Item.
For which of the following tasks would it be most appropriate to use a PL/SQL WHILE loop?
- To add six new services to the SERVICE table
- To change MACHINE_ID 123456 to 456789 and change all others to 900000
- To change MACHINE_ID 123456 to 456789
- To add new services to the SERVICE table until finished
TKPROF is ________.
- a utility that enables SQL code to run faster
- used to format the trace file generated by SQL_TRACE
- a diagnostic tool used to capture operating system statistics
- a utility used to capture the statistics of all SQL currently running in the database