Oracle PL/SQL Fundamentals

Scenario: You have a database that uses a computationally intensive calculation that is accessed often in a SELECT query. You want to improve the execution speed of this calculation.

Given the scenario described above, which one of the following do you use to accomplish this?
Choice 1 : Global and local index
Choice 2 : B-tree cluster index
Choice 3 : Function-based index
Choice 4 : Real Application Cluster with load balancing enabled
Choice 5 : The parallel computing package

===============================================================

When tracing is enabled, which one of the following scripts do you run in order to create the tables that collect trace information generated by DBMS_TRACE?

Choice 1 : TRACETAB.sql
Choice 2 : UTLXTRC.sql
Choice 3 : TRCTABLE.sql
Choice 4 : DBMSTRC.sql
Choice 5 : PLSQLTRC.sql

===============================================================

Scenario : You need to create 50 tables. The names are based on parameters stored in an existing table. You have created a PL/SQL program that retrieves the data from the table and loops through each item. The program outputs the correct data when you use dbms_output.put_line('CREATE TABLE ' || X || '(a number)'), but when you try to execute CREATE TABLE X (A number) it does not function.

Given the scenario described above, which one of the following do you do in order for the CREATE TABLE statement to function properly?
Choice 1 : Precede the statement with EXECUTE IMMEDIATE.
Choice 2 : Pipe the output to the DBMS.SQL function.
Choice 3 : Update the program state to DDL mode before executing the statement.
Choice 4 : Clear the statement cache prior to executing the statement.
Choice 5 : Close any existing open cursors before running the statement.
==========================================================

When you store a PL/SQL table (Table type) in the database, how many columns does the table have?
Choice 1 : 1
Choice 2 : 2
Choice 3 : 3
Choice 4 : As many as are necessary in the program
Choice 5 : One plus the number of columns in the nested table
============================================================

Scenario : You have created a new database. You want to use DBMS_PROFILER to measure execution time of a pl/sql program unit. When you try to use DBMS_PROFILER, you find that it is not available.
Given the scenario described above, how do you make DBMS_PROFILER available for use?
Choice 1 : Create the stats collection table in your database.
Choice 2 : Execute dbms_profiler.flush_data.
Choice 3 : Run profload.sql as sys and proftab.sql as the profile user.
Choice 4 : Increase the system global area heap size to at least two gigabytes.
Choice 5 : Set serverprofiler on enabled.
============================================================================================

Which one of the following is a common reason for slow PL/SQL program execution?
Choice 1 : Setting garbage collection intervals
Choice 2 : Forgetting to SET SERVEROUTPUT ON
Choice 3 : Disabling the Oracle Trace facility
Choice 4 : Writing SQL statements that are not optimized
Choice 5 : Neglecting to specify as many columns as possible to be NOT NULL during table creation
===========================================================

For which one of the following purposes do you use regular expressions?
Choice 1 : Matching text patterns
Choice 2 : Balancing binary trees
Choice 3 : Tracking changes over time
Choice 4 : Improving readability of code
Choice 5 : Describing the purpose of a function or statement
============================================================

Why do you use cursor attributes?
Choice 1 : To open and close cursors
Choice 2 : To populate cursors
Choice 3 : To record cursor execution time
Choice 4 : To control cursors
Choice 5 : To get information about cursors
=============================================================

Scenario : You have the following line of code in a program:

statement := "SELECT * FROM users WHERE name = '" + Name + "';"

The line is called from an application running on a public facing Web server. Given the scenario described above, what is the security issue with this code?

Choice 1 : SQL injection
Choice 2 : Fragment attack
Choice 3 : Buffer overflow
Choice 4 : Heap exhaustion
Choice 5 : Spoofing attack
===============================================================