here are some general questions

PL/SQL Questions:

1. Describe the difference between a procedure, function and anonymous pl/sql block.

A function must return a value while a procedure doesn’t have to. Functions can be called from SQL.

anonymous pl/sql block begins with a DECLARE statement.

2. Describe the use of %ROWTYPE and %TYPE in PL/SQL

%TYPE associates a variable with a single column type.

%ROWTYPE associates a variable with an entire table row.

% TYPE is used to declare a field with the same type as that of a specified table’s column.

% ROWTYPE is used to declare a record with the same types as found in the specified database table, view and cursor.

3. What packages (if any) has Oracle provided for use by developers?

Oracle provides the DBMS_ series of packages. There are many which developers should be aware of such as DBMS_SQL, DBMS_OUTPUT, DBMS_DDL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_JOB, DBMS_UTILITY, UTL_FILE.

4. Describe the use of PL/SQL tables

PL/SQL tables are scalar arrays that can be referenced by a binary integer. They can be used to hold values for use in later queries or calculations.

5. What are SQLCODE and SQLERRM

SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered.

6. How can you find within a PL/SQL block, if a cursor is open?

Use the %ISOPEN cursor status variable.

7. How can you generate debugging output from PL/SQL?

Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can also be used.

8. What are the types of triggers?

There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL key words:

Ø BEFORE ALL ROW INSERT

Ø AFTER ALL ROW INSERT

Ø BEFORE INSERT

Ø AFTER INSERT etc.

DBA:

4. If the DEFAULT and TEMPORARY tablespace clauses are left out of a CREATE USER command what happens? Is this bad or good? Why?

The user is assigned the SYSTEM tablespace as a default and temporary tablespace. This is bad because it causes user objects and temporary segments to be placed into the SYSTEM tablespace resulting in fragmentation and improper table placement

(only data dictionary objects and the system rollback segment should be in SYSTEM).

How can you tell if a database object is invalid?

By checking the status column of the DBA_, ALL_ or USER_OBJECTS views, depending upon whether you own or only have permission on the view or are using a DBA account.

If you have an example table, what is the best way to get sizing data for the production table implementation?

The best way is to analyze the table and then use the data provided in the DBA_TABLES view to get the average row length and other pertinent data for the calculation. The quick and dirty way is to look at the number of blocks the table is actually using and ratio the number of rows in the table to its number of blocks against the number of expected rows.

17. How can you find out how many users are currently logged into the database? How can you find their operating system id?

There are several ways. One is to look at the v$session or v$process views. Another way is to check the current_logins parameter in the v$sysstat view. Another if you are on UNIX is to do a "ps -ef|grep oracle|wc -l? command, but this only works against a single instance installation.

19. How can you determine if an index needs to be dropped and rebuilt?

Run the ANALYZE INDEX command on the index to validate its structure and then calculate the ratio of LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it isn?t near 1.0 (i.e. greater than 0.7 or so) then the index should be rebuilt. Or if the ratio

BR_BLK_LEN/ LF_BLK_LEN+BR_BLK_LEN is nearing 0.3.

8. What special Oracle feature allows you to specify how the cost based system treats a SQL statement?

The COST based system allows the use of HINT to control the optimizer path selection.

10. What is a Cartesian product?

A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.

18. How do you generate file output from SQL?

By use of the SPOOL command

14. If you see contention for library caches how can you fix it?

Increase the size of the shared pool.