7.8. Exam Essentials

Know how to create database directory objects. Directory objects are required for use in the Data Pump Export and and Data Pump Import programs.

Know the syntax for how to insert data with either a list of values or a subquery. A list of values requires the keyword VALUES, while a subquery does not.

Know the difference between DELETE TABLE and TRUNCATE TABLE. DELETE TABLE generates undo and must be committed for the changes to be made permanent. TRUNCATE TABLE does not generate undo and issues an implicit commit.

Know that PL/SQL functions have a RETURN clause. Functions have a datatype and a RETURN clause. The other PL/SQL programs do not.

Know how to enable and disable triggers. Use the ALTER TRIGGER statement to enable or disable any individual trigger and the ALTER TABLE ENABLE ALL TRIGGERS or ALTER TABLE DISABLE ALL TRIGGERS statement to enable or disable triggers en masse.

Be aware of the default settings for the PL/SQL initialization parameters. By default, native compilation, PL/SQL warnings, and debug are all disabled, while optimization is set to maximum.

Know that directory objects are not owned by individual schema. Directory objects are not schema objects. Instead, they are owned by the database like roles or profiles.

Be aware of the Data Pump export and import modes. Data Pump export has database, schema, table, and tablespace modes, and Data Pump import has full, schema, table, and tablespace modes. Although these modes sound similar, they differ between the two tools.

Be familiar with the Data Pump options that let you transfer both data and metadata from one schema to another. The content= parameter controls whether data, metadata, or both are copied. The remap_schema parameter allows you to transfer data from one schema to another.

Be aware of the limitations of SQL*Loader direct path mode, including unusable indexes. SQL*Loader direct path mode has several limitations, the most prominent being that it locks the table in exclusive mode for the duration of the load. Unique indexes are marked unusable if unique violations are found after a direct path load. These unique violations must be resolved before the index can be rebuilt.