Oracle9i Database Administration: Manage Data Storage

This course is the fourth in the series covering the database administration strategies and procedures provided in Oracle9. This course covers the concepts associated with tables maintaining data integrity clusters and index-organized tables and methods for loading and reorganizing data.

This is primarily online training This is an online eLearning or CBT training program Contact Serebra Learning Corporation for more information

Duration: 8 hours Training Presented in: English

Audience

The intended audience for this course include Database administrators System administrators Network administrators and Technical support professionals. Additionally learners should have taken the first three parts of this series 64311-64313. Suggested prerequisites include Oracle SQL: Basic SELECT Statements (61110) Oracle SQL: Data Retrieval Techniques (61111) Oracle SQL: DML and DDL (61112) Oracle PL/SQL: PL/SQL Basics (61113) Oracle PL/SQL: Procedures Functions and Packages (61114) Oracle PL/SQL: Database Programming (61115).

Objective

• Identify the row storage method used by the various tables and the components of the row structure in an Oracle database.
• Identify the features of the Oracle datatypes and ROWID.
• Identify the guidelines for creating tables and the situations resulting in row chaining and create tables.
• Identify the characteristics of table parameters and highwater mark change the parameter value manually allocate extents validate table structure and detect row migration.
• Identify the effects of truncating a table drop and truncate a table and deallocate free apace.
• Retrieve the table extent and row location information from data dictionary.
• Identify the features of indexorganized tables and alter tables.
• Identify the various types of database triggers and the methods to disable and enable the triggers.
• Identify the guidelines for implementing constraints and create the two types of constraints.
• Disable enable and drop constraints and triggers in the validate and the novalidate mode and identify the steps for using the EXCEPTIONS table.
• Retrieve constraint constraint columns PKFK relationship information and trigger information by using Oracle SQL*Plus Worksheet.
• Load data by using directload inserts identify the concepts associated with SQL*Loader and use SQL*Loader to load data into tables.
• Identify the uses of the export and import utilities the export modes and the export paths.
• Reorganize data using export and import identify the features and process of transportable tablespaces and the restrictions while using them.

Topics Include

Unit 1: Managing Tables

• Identify the information stored in a table row.
• Identify the features of the built-in scalar datatypes.
• Match the new Oracle9i datatypes with their features.
• Label the components of a ROWID with their respective component names.
• Identify the characteristics that distinguish VARRAYs from nested tables.
• Identify the features of Unicode data.
• Match the structures used to store data with their features.
• Create a table by using Oracle Enterprise Manager.
• Identify the guidelines to be followed while creating tables.
• Identify the value for the PCTFREE parameter in a given situation.
• Identify the situations in which the row chaining and row migration events are triggered.
• Copy a table by using Oracle Enterprise Manager.
• Identify the features of the cost-based optimizer.
• Create a temporary table by using the CREATE command.
• Identify the storage parameters that affect tables.
• Identify the block utilization parameters that affect table blocks.
• Modify the storage and block parameters of a table by using Oracle Enterprise Manager.
• Allocate extents manually by using Oracle SQL*Plus Worksheet.
• Identify the characteristics of the high-water mark of a table.
• Deallocate the unused space in a table by using Oracle SQL*Plus Worksheet.
• Truncate a table by using Oracle SQL*Plus Worksheet.
• Identify the effects of truncating a table.
• Drop a table by using Oracle Enterprise Manager.
• Identify the features of dropping unused columns from a table.
• Retrieve table information by using Oracle SQL Worksheet.
• Retrieve the extent information from the DBA_EXTENTS view by using Oracle SQL*Plus Worksheet.
• Obtain the physical location of the rows in a table by using Oracle SQL*Plus Worksheet.
• Identify the features of index organized tables.
• Identify the features of IOTs not available in regular tables.
• Create an index-organized table by using the CREATE TABLE statement.
• Maintain IOTs using the ALTER TABLE...MOVE command.
• Analyze index-organized tables by using the ANALYZE statement.
• Match the application types of index-organized tables with their features.

Unit 2: Maintaining Data Integrity

• Identify the features of triggers and integrity constraints.
• 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 the Oracle server to implement unique and primary keys.
• Identify the factors to be considered while performing DDL operations on the tables referenced by a foreign key.
• Identify the factors to be considered while performing DML operations on the Foreign Key-Primary Key related tables.
• Match the types of database triggers with the situations in which they are executed.
• Disable triggers by using Oracle SQL*Plus Worksheet.
• Enable triggers by using Oracle SQL*Plus Worksheet.
• Drop triggers by using Oracle Enterprise Manager Console.
• Create a constraint when creating a table using Oracle SQL*Plus Worksheet.
• Create a constraint after a table has been created by using Oracle SQL*Plus Worksheet.
• Identify the guidelines for defining constraints.
• Disable constraints by using Oracle SQL*Plus Worksheet.
• Disable constraints in the validate mode by using Oracle SQL*Plus 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 needed for detecting constraint violations by using the EXCEPTIONS table.
• Drop constraints by using Oracle SQL*Plus Worksheet.
• Identify the contents of the data dictionary views used for retrieving constraint information.
• Identify the data dictionary views to be used for retrieving specified information about triggers.

Unit 3: Loading Data

• Match the methods for loading data into tables with their functions.
• Perform a serial direct load insert by using Oracle SQL*Plus Worksheet.
• Perform parallel direct-load inserts by using the PARALLEL hint.
• Identify the features of SQL*Loader.
• Identify typical SQL*Loader concepts.
• 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 features of logical records.
• Identify the guidelines you follow to minimize errors and maximize the performance of SQL*Loader.
• Load data into a table by using Oracle Enterprise Manager.
• Identify the methods of troubleshooting the problems that can occur during a SQL*Loader data load.

Unit 4: Managing Indexes

• Match the types of indexes with their features.
• Identify the characteristics of a B-tree index.
• Identify the functions of a reverse key index.
• Match the components of a bitmap index with their contents.
• Differentiate between B-tree indexes and bitmap indexes.
• Identify the characteristics of a function-based index.
• Identify the guidelines for creating indexes.
• Create a normal B-tree index by using Oracle Enterprise Manager.
• Create a reverse key index by using Oracle SQL*Plus Worksheet.
• Create a bitmap index by using Oracle Enterprise Manager.
• Create a function-based index by using Oracle SQL*Plus Worksheet.
• Drop a given index by using Oracle Enterprise Manager.
• Identify the situations in which indexes need to be rebuilt.
• Identify the characteristics of index rebuilds.
• Rebuild indexes online using the ONLINE keyword.
• Identify the features of coalescing indexes.
• Identify features of indexes on index-organized tables.
• Identify features of secondary indexes on index-organized tables.

Unit 5: Reorganizing Data

• 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 data paths.
• Match the command line parameters for the EXPORT command with their purposes.
• Match the command line parameters for the IMPORT command with their purposes.
• Identify the guidelines for using the Export and Import utilities.
• Export objects by using Oracle Enterprise Manager.
• Import objects by using Oracle Enterprise Manager.
• Identify the characteristics of the import process.
• Identify the NLS considerations for exporting and importing objects.
• Identify the export and import strategies.
• Identify the features of a transportable tablespace.
• Sequence the steps needed to transport a tablespace to another database.
• Identify the restrictions for transporting a tablespace.

Duration

6

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

• Oracle9i Database Administration: Create a Database
• Oracle9i Database Administration: Manage an Instance
• Oracle9i Database Administration: Manage Security
• Oracle9i Database Administration: Manage Storage Structures
• Oracle9i Database Administration: Recover Databases
• Oracle9i Database Administration: Set Up Oracle Net Services
• Oracle9i Database Administration: Strategies and Backups