Oracle8 Database Administration - Manage Data Storage

Audience

Database Administrators system administrators network administrators and technical support professionals. Prior to taking this course participants should be familiar with SQL and PL/SQL using Procedure Builder or have taken courses 61110 61111 61112 and 60113. In addition participants should have taken the first three parts of the Oracle8 Database Administration series courses 61311 61312 and 61313.

Objective

• Identify the characteristics of various tables and datatypes.
• Create and manage tables.
• Retrieve table information from the data dictionary.
• Identify the characteristics and types of triggers and constraints.
• Implement and maintain data integrity.
• Retrieve information from the data dictionary.
• Implement clusters and index organized tables (IOTs).
• Load and reorganize data in tables.

Topics Include

Unit 1: Managing Tables

• Identify the row storage method for various types of tables.
• Identify the information stored in the components of the Oracle row structure.
• Identify the features of the Oracle built-in scalar datatypes.
• Identify the differences between the two large object datatypes.
• Identify the features of ROWID.
• Identify the components in a ROWID format.
• Identify the characteristics that distinguish the two collection datatypes.
• Create a table by using Oracle Schema Manager.
• Identify the guidelines to be followed while creating tables.
• Identify the value for the PCTFREE parameter in a given situation.
• Match the row chaining and row migration events with the situations that trigger them.
• Copy an existing table by using Oracle Schema Manager.
• Identify the storage parameters that when modified will affect a table.
• Identify the block utilization parameters that when modified affect the table blocks.
• Modify the storage and block parameters of a table by using Oracle Schema Manager.
• Manually allocate extents by using Oracle SQL Worksheet.
• Identify the characteristics of the high water mark.
• Validate a table structure by using Oracle SQL Worksheet.
• Execute the command used to detect row migration in a table by using Oracle SQL Worksheet.
• De-allocate the unused space in a table by using Oracle SQL Worksheet.
• Truncate a table by using Oracle SQL Worksheet.
• Identify the effects of truncating a table.
• Drop a table by using Oracle Schema Manager.
• Retrieve table information by using Oracle SQL Worksheet.
• Retrieve the extent information from DBA_EXTENTS by using Oracle SQL Worksheet.
• Obtain the physical location of the rows in a table by using Oracle SQL Worksheet.

Unit 2: Maintaining Data Integrity

• Identify the features of integrity constraints.
• Identify the functions of the various types of declarative integrity constraints.
• Identify the data processing procedure for various constraint states.
• Match the deferred and immediate constraints with their features.
• Identify the procedure followed by Oracle Server to implement unique and primary keys.
• Identify the factors to be considered while performing DDL on the tables referenced by a foreign key.
• Identify the factors to be considered while performing DML on the FK-PK related tables.
• Identify the various types of triggers with the situations under which they are executed.
• Disable triggers by using Oracle SQL Worksheet.
• Enable triggers by using Oracle SQL Worksheet.
• Create an in-line constraint by using Oracle SQL Worksheet.
• Create an out-of-line constraint by using Oracle Schema Manager.
• Identify the guidelines for defining constraints.
• Disable constraints by using Oracle SQL Worksheet.
• Enable a constraint in the novalidate mode by using Oracle SQL Worksheet.
• Enable constraints in the validate mode by using Oracle SQL Worksheet.
• Sequence the steps required for detecting constraint violations by using the EXCEPTIONS table.
• Drop constraints by using Oracle SQL Worksheet.
• Drop triggers by using Oracle Schema Manager.
• Retrieve the constraint information by using Oracle SQL Worksheet.
• Retrieve the information on columns used in a constraint by using Oracle SQL Worksheet.
• Retrieve the PK-FK Relation Information.
• Retrieve the information on triggers and trigger columns by using Oracle SQL Worksheet.

Unit 3: Clusters and Index-Organized Tables

• Match the data storage methods with the type of row distribution they offer.
• Identify the features of clustered tables that distinguish them from regular unclustered tables.
• Identify the characteristics of clusters.
• Identify the features of the different cluster types.
• Create an index cluster with a cluster index by using Oracle Schema Manager.
• Create a hash cluster by using Oracle Schema Manager.
• Create a table in a cluster by using Oracle Schema Manager.
• Alter a cluster by using Oracle Schema Manager.
• Drop a cluster by using Oracle Schema Manager.
• Identify the guidelines for implementing clusters.
• Identify the data dictionary views to be used for retrieving specified information about clusters.
• Identify the data storage and retrieval features of an index-organized table.
• Identify the features of an index-organized table that distinguish it from a regular table.
• Create an index-organized table with an overflow area in a specified tablespace.
• Identify the contents of the data dictionary views for index-organized tables.

Unit 4: Loading and Reorganizing Data

• Match the methods for loading data into tables with their functions.
• Perform a serial direct-load insert by using the APPEND hint in Oracle SQL Worksheet.
• Perform parallel direct-load inserts by using the PARALLEL hint.
• Identify the features of SQL*Loader.
• Match the input files used by SQL*Loader with their purposes.
• Match the output files used by SQL*Loader with their purposes.
• Identify the differences between the conventional and direct path load data loading methods.
• Sequence the steps that occur when parallel direct load sessions are initiated to load data into a table.
• Identify the guidelines to minimize errors and maximize performance of SQL*Loader.
• Load data into a table by using Oracle Data Manager.
• Identify the methods of troubleshooting the problems that may occur during a SQL*Loader data load.
• Identify the uses of the export and import utilities.
• Identify the objects that can be exported using different export modes.
• Identify the features of the export paths.
• Match the command line parameters for the export utility with their purposes.
• Match the command line parameters for the import utility with their purposes.
• Identify the guidelines to minimize errors and maximize performance of export and import.
• Export objects by using Oracle Data Manager.
• Import objects by using Oracle Data Manager.
• Identify the characteristics of the import process.
• Identify the NLS considerations for exporting and importing objects.

Duration

8

Minimum Requirements

The CDROM version of this course requires:

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).
• At least a double speed CDROM drive.
• An MPC compliant sound card with attached speakers or headphones is recommended (Currently only the CDROM version supports audio).

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM and 22MB available hard disk space or file server space.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).

Media

CDROM