Oracle 9i Training Oracle9iSQL: Basic SELECT Statements Course Overview This course covers relational database principles and Oracle concepts; writing basic SQL statements; restricting and sorting data; and using single-row functions. Additionally, this course provides an introduction to relational database concepts and the use of SQL for storing, retrieving, and manipulating data in a relational database.   Learn To • Identify the components of the relational model. • Match the structural elements of a relational database table with their descriptions. • Sequence the phases of the system development life cycle. • Match the types of SQL statements with their descriptions. • Match the capabilities of SQL SELECT statements with their functions. • Identify the guidelines for constructing basic SQL SELECT statement. • Write a query that restricts rows returned by using the WHERE clause. • Write a WHERE clause to accommodate different datatypes. • Use comparison operators with the WHERE clause. • Identify the different categories of datatypes. • Match the character datatypes with their descriptions. • Identify the features of the NUMBER datatype. Total Learning Time 6 to 8 hours   Objectives Unit 1: Relational Database Concepts 1 - 2 hours • Identify the components of the relational model. • Match the structural elements of a relational database table with their descriptions. • Sequence the phases of the system development life cycle. • Identify the three components of an entity relationship model. • Match the entity relationship model components with their conventions. • Match the components of Oracle9i with their functions. • Identify the tools and languages used to communicate with the Oracle server. • Match the services provided by Oracle9iAS with their functions. • Match the tools in Oracle9iDS with their functions. • Identify the features of Oracle9i. • Identify the features of Oracle9i Database. • Match the components of Oracle Internet Platform with their features. • Identify the benefits of the Entity Relationship (ER) model. Unit 2: Writing Basic SQL Statements 1 - 2 hours • Match the capabilities of SQL SELECT statements with their functions. • Identify the guidelines for constructing basic SQL SELECT statement. • Use the SELECT statement to display rows and columns from a table. • Write a SQL statement that includes an arithmetic expression. • Assign an alias to a column heading when retrieving data from a table. • Concatenate multiple columns that are selected from a table. • Identify the uses of iSQL*Plus. • Embed a literal character string in a SELECT clause to customize the output. • Eliminate duplicate rows from the output of a query by using the DISTINCT keyword. • Log on to SQL*Plus through a Windows environment. • Display the structure of a table using the DESCRIBE command. • Match the SQL*Plus edit commands with their functions. • Match the SQL*Plus file commands with their functions. • Match the types of SQL statements with their descriptions. • Log on to the iSQL*Plus environment by using a browser. • Match the options in the iSQL*Plus window with their functions. • Display the structure of a table by using the DESCRIBE command. • Save iSQL*Plus commands to Script files by using the Save Script button of the iSQL*Plus window. • Load a previously saved iSQL*Plus command from a script file by using the Load Script button of iSQL *Plus window. • Save the output of iSQL*Plus commands to a script file by using the iSQL *Plus window. • Identify the differences between iSQL*Plus commands and SQL statements. Unit 3: Restricting and Sorting Data 2 hours • Write a query that restricts rows returned by using the WHERE clause. • Write a WHERE clause to accommodate different datatypes. • Use comparison operators with the WHERE clause. • Display rows based on a range of values by using the BETWEEN operator. • Display rows that match a list of values by using the IN operator. • Perform wildcard searches by using the LIKE operator. • Display rows that contain NULL values by using the IS NULL operator. • Write a combined expression by using the AND operator in a WHERE clause. • Write a combined expression by using the OR operator in a WHERE clause. • Restrict rows by using the NOT operator in a WHERE clause. • Identify the precedence rules when evaluating conditions that use comparison and logical operators. • Sort rows retrieved in ascending order by using the ORDER BY clause. • Sort rows retrieved in descending order by using the DESC keyword in the ORDER BY clause. • Sort rows retrieved by using a column alias in the ORDER BY clause. • Sort rows retrieved by more than one column. Unit 4: Single-Row Functions 2 hours • Match the types of character functions with their uses. • Display data in lowercase by using the LOWER function. • Display the number of characters in a value by using the LENGTH function. • Match the character datatypes with their descriptions. • Display information about a character value by using SUBSTR and INSTR functions. • Display values padded with characters using LPAD and RPAD functions. • Display rounded values to a specified decimal place by using the ROUND function. • Display truncated values to a specified decimal place by using the TRUNC function. • Return the remainder of one value divided by another by using the MOD function. • Identify the uses of the SYSDATE function. • Perform arithmetic operations on dates. • Display date information by using common SQL date functions. • Match the explicit datatype conversion functions with their uses. • Convert a date value to a character string for display by using the TO_CHAR function. • Convert a number to a character string for display by using the TO_CHAR function. • Identify functions used to convert characters to other datatypes. • Convert a NULL value to an actual value for display by using the NVL function. • Identify the features of the NUMBER datatype. • Decode an expression by using the DECODE function. • Evaluate single-row functions that are nested. • Match the number functions with their uses. • Identify the concepts related to time zones. • Concatenate character values by using the CONCAT function. • Remove characters or extra spaces from character strings by using the TRIM function. • Search for and replace a text expression by using the REPLACE function. • Return the current date and time in a session's time zone by using the CURRENT_DATE function. • Return the current date, time, and time zone of a session by using the CURRENT_TIMESTAMP function. • Return the current date and time in a session time zone by using the LOCALTIMESTAMP function. • Return the database time zone by using the DBTIMEZONE function. • Return the current session's time zone by using the SESSIONTIMEZONE function. • Return a datetime field value from a datetime or an interval value expression by using the EXTRACT function. • Convert a TIMESTAMP value to a TIMESTAMP WITH TIME ZONE value by using the FROM_TZ function. • Convert a character value to a TIMESTAMP datatype by using the TO_TIMESTAMP function. • Convert a character value to a TIMESTAMP WITH TIME ZONE value by using the TO_TIMESTAMP_TZ function. • Convert a character value to an INTERVAL YEAR TO MONTH datatype by using the TO_YMINTERVAL function. • Identify the datatypes used in implicit datatype conversion. • Return an expression based on the evaluation of a NULL value by using the NVL2 function. • Compare two expressions by using the NULLIF function. • Compare multiple expressions by using the COALESCE function. • Implement conditional processing by using the CASE expression. • Identify the different categories of datatypes. • Match the LOB datatypes with their functions. • Match the datetime datatypes with their functions. • Define the RR date format. • Return the time zone offset by using the TZ_OFFSET function. • Identify the types of SQL functions. • Match the single-row function types with their features.

Oracle9i SQL: Data Retrieval Techniques Course Overview This course covers joins, group functions, subqueries, and the iSQL*Plus report variables. Additionally, this course shows students how to access data from multiple table using joins, aggregate data using group functions, create subqueries, and use iSQL*Plus commands to create customized queries.   Learn To • Identify the four Oracle join types used to display data from more than one table. • Identify a Cartesian product. • Retrieve rows from two tables by using an equijoin. • Identify the features of a group function. • Write SQL statements that contain common group functions. • Manage null values when using group functions in a SQL statement. • Identify the requirements for using subqueries. • Identify the characteristics of each subquery type. • Write a single-row subquery to return one row in the WHERE clause of a SELECT statement. • Identify the characteristics of substitution variables. • Prompt the user for a number value at run time by using a substitution variable. • Prompt the user for a character value at run time by using a substitution variable. Total Learning Time 6 to 8 hours   Objectives Unit 1: Displaying Data from Multiple Tables 1 - 2 hours • Identify guidelines for using the SET operators. • Create a compound query that uses the UNION operator to combine data from two results sets. • Create a compound query that uses the INTERSECT operator to combine data from two results sets. • Create a compound query that uses the MINUS operator to combine data from two results sets. • Identify the four Oracle join types used to display data from more than one table. • Identify a Cartesian product. • Retrieve rows from two tables by using an equijoin. • Specify additional search conditions in an equijoin by using the AND operator in the WHERE clause. • Simplify a query by using a table alias in an equijoin. • Retrieve rows from more than two tables by using an equijoin. • Retrieve rows from two tables by using a nonequijoin. • Retrieve rows from two tables by using an outer join. • Retrieve rows from within the same table by using a self join. • Match the SQL: 1999 join types with their correct descriptions. • Retrieve rows from two tables by using a natural join. • Join data from two specific columns by using the USING clause. • Retrieve rows within the same table by using the ON clause. • Retrieve rows from two tables by using a left or right outer join. • Match the SET operators used to create compound queries with their correct descriptions. Unit 2: Group Functions 1 - 2 hours • Identify the features of a group function. • Write SQL statements that contain common group functions. • Manage null values when using group functions in a SQL statement. • Group rows retrieved by using the GROUP BY clause. • Retrieve a subgroup of rows from within a larger group retrieved by using the GROUP BY clause. • Restrict groups of rows retrieved by using the HAVING clause. • Identify illegal queries involving group functions. • Write a SQL statement that contains nested group functions. • Create a query that produces a subtotal by using the ROLLUP operator. • Create a query that produces cross-tabulation values by using the CUBE operator. • Identify the group or groups on which a subtotal is based by using the GROUPING function. • Create multiple groups of data by using the GROUPING SETS function in the GROUP BY clause. • Create a composite column in the GROUP BY clause of a SELECT statement. • Create a concatenated grouping using the ROLLUP and CUBE operators. Unit 3: Subqueries 2 hours • Identify the requirements for using subqueries. • Identify the characteristics of each subquery type. • Write a single-row subquery to return one row in the WHERE clause of a SELECT statement. • Retrieve data that is dependent upon the value of a group function in a single-row subquery. • Write a single-row subquery in the HAVING clause of a SELECT statement. • Identify two common errors that occur when using single-row subqueries. • Identify the requirements for using multiple-row subqueries. • Write a multiple-row subquery using the IN operator. • Identify how a query handles null values returned from a multiple-row subquery. • Write a multiple-column subquery using the IN operator. • Identify the different output that results from pairwise and nonpairwise subqueries. • Write a multiple-column subquery in the FROM clause of a SELECT statement. • Write a scalar subquery in the ORDER BY clause of a SELECT statement. • Write a correlated subquery in the WHERE clause of a SELECT statement. • Write a correlated subquery using the EXISTS operator. • Write a correlated subquery using the WITH clause. Unit 4: Reporting with iSQL*Plus 2 hours • Identify the characteristics of substitution variables. • Prompt the user for a number value at run time by using a substitution variable. • Prompt the user for a character value at run time by using a substitution variable. • Prompt the user for a date value at run time by using a substitution variable. • Prompt the user for column names and expressions at run time using substitution variables. • Prompt the user at run time for a value by using a substitution variable prefixed with a double ampersand. • Maintain a variable at run time by using the DEFINE and UNDEFINE commands. • Match the SET command variables with their functions. • Match the iSQL*Plus format commands with their functions. • Control the display of a column by using the COLUMN command. • Group related rows by using the BREAK command to suppress duplicate values. • Format page headers and footers by using the TTITLE and BTITLE format commands. • Run a formatted report by using an iSQL*Plus script file.

Oracle9i SQL: DML and DDL Course Overview The Oracle SQL DML and DDL course is the third in a three-part series covering the Data Manipulation and Data Definition language statements supported by Oracle9i. This course introduces the participants to various objects in a database. The participants learn to create, update, and delete the database objects. The participants also learn to add rows, update, and delete existing rows from a table. The course also explains the use of external tables and how to create the external tables.   Learn To • Identify the data structures in an Oracle server. • Identify the rules for naming tables in a database. • Identify the DML and transaction control statements. • Insert rows in a table by using the INSERT statement. • Add a column to a table in an Oracle database. • Change the table structure by modifying the characteristics of an existing column. • Identify the features of an Oracle sequence. • Create a sequence by using the CREATE SEQUENCE statement. • Identify the properties of views. • Match the clauses of the CREATE VIEW statement with their functions. • Create a public synonym by using the CREATE PUBLIC SYNONYM statement. • Remove a synonym by using the DROP PUBLIC SYNONYM statement. • Identify the features of Oracle database security. • Create users in an Oracle database. • Match the types of multitable INSERT statements with their uses. • Insert rows into multiple tables by using the INSERT INTO statement unconditionally. Total Learning Time 6 to 8 hours   Objectives Unit 1: Creating Tables and Constraints 0.5 - 1 hour • Identify the data structures in an Oracle server. • Identify the rules for naming tables in a database. • Sequence the steps to create a table. • Identify the rules for referencing a table in another user's schema. • Match the Oracle datatypes with their definitions. • Identify the properties of constraints. • Match the constraint types with their definitions. • Identify the characteristics of the different levels of constraints. • Sequence the steps performed by the Oracle server during a primary key lookup with a foreign key value insert. • Identify the features of the data dictionary. • Create a table by using the CREATE TABLE statement. • Create a table based on an existing table. • Confirm the table that you created. • View details of the tables created and owned by you by using the USER_CATALOG data dictionary table. • Define the NOT NULL constraint by using the CONSTRAINT keyword. • Define the UNIQUE constraint by using the CONSTRAINT keyword. • Define the PRIMARY KEY constraint by using the CONSTRAINT keyword. • Define the FOREIGN KEY constraint by using the CONSTRAINT keyword. • Define a CHECK constraint by using the CONSTRAINT keyword. Unit 2: Manipulating Data 0.5 - 1 hour • Identify the DML and transaction control statements. • Insert rows in a table by using the INSERT statement. • Insert special values into existing tables by using the INSERT INTO statement. • Add rows to an existing table based on values from another table. • Use the DEFAULT keyword in an INSERT statement. • Restrict the rows added by the INSERT command by using the WITH CHECK OPTION clause in the subquery. • Update existing rows in a table by using the UPDATE statement. • Update all rows in a table by using the UPDATE statement. • Modify values in a table based on values from another table by using a subquery. • Update a table based on values from another table by using correlated subqueries. • Delete rows from a table by using the DELETE statement. • Delete rows from a table based on values from another table by using a subquery. • Delete rows from a table by using correlated subqueries. • Identify the causes that begin and end a transaction. • Identify the SQL statements for controlling transactions. • Identify the state of the data before and after a COMMIT operation. • Use the ROLLBACK statement to discard pending changes in a transaction. • Create a savepoint and use it as a marker. • Identify the features of read consistency implemented by the Oracle server. • Match the locking mechanisms with their features. • Conditionally update and insert rows by using the MERGE command. Unit 3: Altering Tables and Constraints 0.5 - 1 hour • Add a column to a table in an Oracle database. • Change the table structure by modifying the characteristics of an existing column. • Drop an existing column by using the DROP COLUMN clause in the ALTER TABLE statement. • Add a constraint to an existing column by using the ALTER TABLE statement. • Identify the information displayed by the data dictionary views. • Identify the guideline to follow when dropping a PRIMARY KEY constraint. • Manage existing constraints using the DISABLE and ENABLE keywords. • Drop a table by using the DROP TABLE statement. • Rename an existing table by using the RENAME statement. • Remove all rows from a table by using the TRUNCATE TABLE statement. • Add comments for a table in the data dictionary by using the COMMENT statement. Unit 4: Implementing Sequences 0.5 - 1 hour • Identify the features of an Oracle sequence. • Create a sequence by using the CREATE SEQUENCE statement. • View information on sequences by using the USER_SEQUENCES data dictionary view. • Use the NEXTVAL pseudocolumn to add values to rows. • Identify the features of a cached sequence. • Modify a sequence by using the ALTER SEQUENCE statement. • Remove a sequence by using the DROP SEQUENCE statement. Unit 5: Implementing Views 1 hour • Identify the properties of views. • Match the clauses of the CREATE VIEW statement with their functions. • Create simple views by using the CREATE VIEW statement. • Create views based on two tables by using the CREATE VIEW statement. • Drop a view by using the DROP VIEW statement. • Add the primary key constraint to a view by using the CREATE VIEW statement. • Add the UNIQUE constraint to an existing view by using the ALTER VIEW statement with the ADD CONSTRAINT clause. • Identify the restrictions on implementing constraints on views. • Identify the rules that restrict DML operations on views. • Create a view by using the WITH CHECK OPTION clause. • Create views that prevent DML operations on the base table. • Display information on views by using the data dictionary. Unit 6: Implementing Synonyms and Indexes 1 hour • Create a public synonym by using the CREATE PUBLIC SYNONYM statement. • Remove a synonym by using the DROP PUBLIC SYNONYM statement. • Identify the characteristics of indexes. • Match the types of indexes with their use. • Create new indexes by using the CREATE INDEX statement. • Create an index on the primary key by using the CREATE INDEX clause in the CREATE TABLE statement. • Create a function-based index by using the UPPER function. • Display data dictionary information about indexes created by you. • Remove an existing index by using the DROP INDEX statement. • Drop a primary key constraint while retaining the index by using the KEEP INDEX clause in the ALTER TABLE statement. Unit 7: Controlling User Access 1 hour • Identify the features of Oracle database security. • Create users in an Oracle database. • Change the user password by using the ALTER USER statement. • Grant a system privilege to a user. • Create roles by using the CREATE ROLE statement. • Grant object privileges by using the GRANT statement. • Grant the WITH GRANT OPTION privilege to users. • Match the data dictionary views with their description. • Revoke privileges from users. • Identify the properties of a database link. • Create a public database link by using the CREATE PUBLIC DATABASE LINK statement. Unit 8: Advanced DDL and DML Statements 1 hour • Match the types of multitable INSERT statements with their uses. • Insert rows into multiple tables by using the INSERT INTO statement unconditionally. • Insert rows conditionally in two tables by using the WHEN clause in the INSERT statement. • Insert data into two tables by using the FIRST clause in the INSERT statement. • Insert a single row as multiple rows in a table by using the INSERT INTO statement. • Match the clauses used for creating an external table with their uses. • Create an external table by using the ORGANIZATION EXTERNAL clause of the CREATE TABLE statement. • Add rows to an existing table by using an external table in the INSERT INTO statement. • Query an external table by using the SELECT statement.

Oracle9i PL/SQL: Basics Course Overview The Oracle9i PL/SQL: Basics course introduces the learner to the basics of PL/SQL. The course builds the necessary skills for using Oracle PL/SQL. It will help the learner acquire the expertise to design, develop, and implement Oracle Server applications by using Oracle Structured Query Language (SQL) and PL/SQL. The learner is guided step-by-step through the various aspects of PL/SQL programming. Numerous easy-to-understand examples ensure that the concepts are amply illustrated.   Learn To • Identify the features of PL/SQL. • Identify the benefits of PL/SQL. • Select the actions by which a PL/SQL engine processes a block of code. • Identify the functions of PL/SQL variables. • Identify the guidelines for declaring PL/SQL variables. • Match the PL/SQL datatypes with their descriptions. • Retrieve data from a table by using the SELECT statement. • Identify the conditions in which various SELECT exceptions are raised. • Write the code to add rows to a table by using the INSERT statement. • Write PL/SQL code using the IF-THEN-ELSE statement. • Write PL/SQL code using the IF-THEN-ELSIF statement. • Write PL/SQL code using the CASE expression. • Declare a PL/SQL record by using the RECORD datatype. • Reference a PL/SQL record by using the dot notation. • Declare PL/SQL records with %ROWTYPE. • Identify the declaration methods of cursors, both implicit and explicit. • Sequence the steps for controlling explicit cursors. • Write the code to declare a cursor by using the CURSOR statement. • Match the types of exceptions with their properties. • Match some common predefined Oracle server exceptions with their descriptions. • Identify the guidelines to trap exceptions.   Total Learning Time 6 to 8 hours   Objectives Unit 1: PL/SQL: An Introduction 1 hour • Identify the features of PL/SQL. • Identify the benefits of PL/SQL. • Select the actions by which a PL/SQL engine processes a block of code. • Identify the features of the sections of a basic PL/SQL block. • Match the PL/SQL program constructs with their features. • Identify the syntax rules to be applied in creating a PL/SQL block. Unit 2: Developing a Simple PL/SQL Block 1 hour • Identify the functions of PL/SQL variables. • Identify the guidelines for declaring PL/SQL variables. • Match the PL/SQL datatypes with their descriptions. • Match scalar datatypes with their descriptions. • Declare a scalar variable in a PL/SQL block. • Declare a scalar variable with the %TYPE attribute. • Write the code to assign a value a to a variable by using the assignment operator. • Manipulate data in PL/SQL variables by using operators. • Manipulate data in PL/SQL variables by using SQL functions. • Convert the datatype of a variable by using a data conversion function. • Identify the features of variable scoping in nested PL/SQL blocks. • Declare a bind variable in the iSQL*Plus environment. • Identify the case conventions for writing PL/SQL code. • Match the identifiers with their naming conventions. Unit 3: Accessing the Database Using PL/SQL 1 hour • Retrieve data from a table by using the SELECT statement. • Identify the conditions in which various SELECT exceptions are raised. • Write the code to add rows to a table by using the INSERT statement. • Write the code to modify the existing data in a table by using the UPDATE statement. • Write the code to delete data from a table by using the DELETE statement. • Identify the features of a SQL cursor. • Match the SQL cursor attributes with their descriptions. • Write the code to confirm the current transaction by using the COMMIT command. • Write the code to discard the changes made to the table by using the ROLLBACK command. • Write the code to control the transaction at the intermediate point by using the SAVEPOINT command. • Merge data from one table with that of another table by using the MERGE statement. Unit 4: Controlling Flow in PL/SQL Blocks 1 hour • Write PL/SQL code using the IF-THEN-ELSE statement. • Write PL/SQL code using the IF-THEN-ELSIF statement. • Match a condition that uses logical operators with its result. • Write the code for a basic loop to insert records into a table by using the LOOP keyword. • Write the code to execute a set of statements repeatedly by using the FOR LOOP keyword. • Write the code to execute a set of statements repeatedly by using the WHILE LOOP. • Identify the features of a nested loop. • Write PL/SQL code using the CASE expression. Unit 5: Composite Datatypes 1 hour • Declare a PL/SQL record by using the RECORD datatype. • Reference a PL/SQL record by using the dot notation. • Declare PL/SQL records with %ROWTYPE. • Declare an INDEX BY table by using the TABLE datatype. • Reference an INDEX BY table by using a primary_key_value. • Match the INDEX BY table methods with their descriptions. • Identify the syntax to reference a table of records. • Match the LOB datatypes with their descriptions. • Identify the features of LOB variables in PL/SQL. Unit 6: Explicit Cursors 0.5 - 1 hour • Identify the declaration methods of cursors, both implicit and explicit. • Sequence the steps for controlling explicit cursors. • Write the code to declare a cursor by using the CURSOR statement. • Write the code to open a cursor by using the OPEN statement. • Retrieve rows from a cursor by using the FETCH statement. • Close a cursor by using the CLOSE statement. • Check the status of a cursor by using the %ISOPEN attribute. • Check the status of a cursor by using the %FOUND attribute. • Check the status of a cursor by using the %NOTFOUND attribute. • Write the code to fetch a specified number of rows from a cursor by using the %ROWCOUNT attribute. • Write the code to process the rows of the active set conventionally by fetching values into a PL/SQL record. • Write the code to process rows in an explicit cursor using cursor FOR loops. • Pass parameters to a cursor when a cursor is opened by using the cursor_name parameter. • Lock the records by using the FOR UPDATE clause. • Write the code to update the latest fetched row by using the WHERE CURRENT OF clause. Unit 7: Handling Exceptions 0.5 - 2 hours • Match the types of exceptions with their properties. • Match some common predefined Oracle server exceptions with their descriptions. • Identify the guidelines to trap exceptions. • Complete the code to trap predefined exceptions. • Complete the code to trap nonpredefined exceptions. • Complete the code to trap user-defined exceptions. • Match the functions for identifying the associated error message or error code with their descriptions. • Match each calling environment with the error-handling method. • Raise user-defined error codes and messages by using the RAISE_APPLICATION_ERROR procedure.

Oracle9i PL/SQL: Procedures Functions and Packages Course Overview This course introduces users to the basics of PL/SQL subprograms. Users will learn how to build and invoke procedures and functions. This course also covers debugging PL/SQL subprograms using Procedure Builder. Additionally, users will learn how to trap and handle errors in a PL/SQL block. This course also covers the basics of packages and how to create and use them.   Learn To • Match the various sections of a subprogram with their contents. • Identify the benefits of the PL/SQL subprograms. • Identify the features of the development environments. • Identify the features of a function. • Match the clauses of the CREATE FUNCTION statement with their descriptions. • Create a function by using iSQL*Plus. • Identify the features of packages. • Identify the features of package components. • Identify the advantages of using packages. • Identify the features of overloaded subprograms. • Overload a procedure by using iSQL*Plus. • Identify the guidelines that you follow to call private procedures within a package. Total Learning Time 6 to 8 hours   Objectives Unit 1: Subprograms: Procedures 1 - 2 hours • Declare a default parameter to a procedure by using iSQL*Plus. • Identify the features of a procedure that is declared within another procedure. • Sequence the steps that are followed to handle an exception. • Match the CREATE PROCEDURE statement clauses with their description. • Match the various sections of a subprogram with their contents. • Identify the features of the development environments. • Identify the features of the different types of parameter modes. • Match the different methods for passing parameters with their descriptions. • Invoke a procedure from an anonymous block by using iSQL*Plus. • Perform the steps to invoke a procedure from another procedure by using iSQL*Plus. • Identify the benefits of the PL/SQL subprograms. • Sequence the steps that are used to develop a subprogram. • Create a subprogram by using iSQL*Plus. • Declare an IN parameter for a procedure by using iSQL*Plus. • Declare an OUT parameter for a procedure by using iSQL*Plus. • Declare an IN OUT parameter for a procedure by using iSQL*Plus. Unit 2: Subprograms: Functions 1 - 2 hours • Identify the features of a function. • Match the clauses of the CREATE FUNCTION statement with their descriptions. • Create a function by using iSQL*Plus. • Invoke a function by using iSQL*Plus. • Identify the uses of user-defined functions in SQL expressions. • Identify the restrictions when calling user-defined functions from SQL expressions. • Remove subprograms by using iSQL*Plus. • Identify the differences between a procedure and a function. Unit 3: PL/SQL Packages 2 hours • Sequence the steps that are involved in developing a package. • Identify the rules that you follow when developing packages. • Declare a bodiless package by using iSQL*Plus. • Identify the scopes of the constructs within a package. • Match the CREATE PACKAGE clauses with their descriptions. • Match the CREATE PACKAGE BODY clauses with their descriptions. • Identify the features of packages. • Identify the features of package components. • Identify the advantages of using packages. • Create a package specification by using iSQL*Plus. • Create a package body by using iSQL*Plus. • Invoke a package construct by using iSQL*Plus. • Identify the methods used to reference a global variable within a package. • Drop a package by using iSQL*Plus. Unit 4: Advanced Package Concepts 2 hours • Identify the effect of the persistent state of a package cursor on a dependent processor. • Identify the features of persistent package variables. • Identify the features of overloaded subprograms. • Overload a procedure by using iSQL*Plus. • Identify the guidelines that you follow to call private procedures within a package. • Forward declare a procedure in a package by using iSQL*Plus. • Define a package function by using iSQL*Plus. • Invoke a packaged function from SQL statements by using iSQL*Plus. • Declare the index by table of records parameter to the function declared within a package by using iSQL*Plus. • Identify the restrictions imposed on a function declared within a package.

Oracle9i PL/SQL: Database Programming Course Overview This course discusses the features of stored subprograms and packages. Participants will learn how to create and invoke stored procedures and functions as well as how to create stored packages. This course also discusses built-in server-side packages and their use in debugging stored subprograms. Participants will learn how to manage stored subprograms and packages. This course also introduces participants to database triggers: how to create them and their application to complement the capabilities of the Oracle base product.   Learn To • Sequence the steps used to create stored subprograms by using iSQL*Plus. • Identify the effects of storing code separately on the server and on the client. • Perform the steps needed to create a stored procedure using iSQL*Plus. • Match the Oracle server supplied packages with their uses. • Identify the correct combinations of DBMS_PIPE subprograms and their uses. • Identify the uses of the DBMS_DDL package. • Identify the guidelines for using privileges that are related to a stored subprogram. • Grant privileges on a stored subprogram by using SQL commands. • Match the facilities available to manage stored subprograms with their application. • Match the components of a database trigger with their descriptions. • Identify the guidelines for designing triggers. • Create a statement • level database trigger by using iSQL*Plus. Total Learning Time 6 to 8 hours   Objectives Unit 1: Developing Program Units 1 - 2 hours • Sequence the steps used to create stored subprograms by using iSQL*Plus. • Identify the effects of storing code separately on the server and on the client. • Perform the steps needed to create a stored procedure using iSQL*Plus. • Perform the steps needed to create a stored function using iSQL*Plus. • Match run-time exceptions with their descriptions and the directions for their handling. • Perform the steps needed to create a stored package using iSQL*Plus. • Perform the steps to invoke stored procedures in iSQL*Plus using IN and OUT parameters. • Perform the steps needed to invoke a stored function using iSQL*Plus. • Perform the steps needed to invoke a stored subprogram with multiple arguments in iSQL*Plus. • Identify the rules for calling user-defined functions in SQL statements. Unit 2: Oracle Server Supplied Packages 1 - 2 hours • Identify the features of the UTL_FILE package. • Identify the features of the DBMS_LOB package when selecting, reading, writing, and updating LOBs. • Identify the features of using SQL to create, insert, update, select, and remove LOBs. • Sequence the steps to use a BFILE within an Oracle table. • Match the Oracle server supplied packages with their uses. • Identify the correct combinations of DBMS_PIPE subprograms and their uses. • Identify the uses of the DBMS_DDL package. • Identify the uses of the DBMS_JOB package. • Match the subprograms of the DBMS_OUTPUT package with their uses. • Create an appropriate statement to display messages on the screen using DBMS_OUTPUT. • Identify the uses of dynamic SQL. • Match the subprograms of the DBMS_SQL Oracle server supplied package with their uses. • Match the parameters of the EXECUTE IMMEDIATE statement with their uses. • Identify the distinctive properties of a LOB datatype. • Sequence the steps involved in managing an internal LOB datatype. • Sequence the steps involved in managing an external LOB datatype. • Identify the features of the BFILENAME function. • Identify the features of the FILEEXISTS function. • Identify the features of migrating data from the LONG datatype to the LOB datatype. • Identify the features of the packages used to interact with operating system files. • Match the subprograms of the DBMS_JOB package with their uses. • Match the subprograms of the UTL_FILE package with their uses. • Match the exceptions of the UTL_FILE package with their uses. • Sequence the steps to use the UTL_FILE package for file processing. • Identify the features of the UTL_HTTP package. • Identify the features of the UTL_TCP package. • Identify the guidelines for creating DIRECTORY objects. • Match the subprograms of the DBMS_LOB package with their uses. Unit 3: Managing Subprograms and Packages 2 hours • Identify the guidelines for using privileges that are related to a stored subprogram. • Identify the methods used to debug stored subprograms. • Match the facilities available to manage stored subprograms with their application. • Display details about stored subprograms from the Oracle data dictionary. • Perform the steps to display compile time errors stored in the Oracle data dictionary. • Grant privileges on a stored subprogram by using SQL commands. • Match the different types of dependencies with their descriptions. • Perform the steps to display dependencies stored in the Oracle data dictionary. • Identify the situations in which a dependent subprogram can be successfully recompiled. • Identify the features of the Oracle server remote dependency mechanism. • Match the different tasks involved in managing packages with the methods of implementing them. • Identify the package dependency rules implemented by the Oracle server. Unit 4: Developing Database Triggers 2 hours • Identify the features of Form Builder triggers. • Identify the guidelines for designing triggers. • Perform the steps needed to manage database triggers using iSQL*Plus. • Drop a trigger by using the DROP TRIGGER statement. • Sequence the steps followed by the Oracle server while firing triggers. • Match the system privileges that you need to manage triggers with their functions. • Call a stored procedure from a trigger by using the CALL statement. • Match the components of a database trigger with their descriptions. • Create a statement-level database trigger by using iSQL*Plus. • Create a row-level database trigger by using iSQL*Plus. • Identify the features of an INSTEAD OF trigger. • Identify the features of DBA triggers. • Match the tasks performed in an Oracle database with the role that triggers have in achieving them. • Match the data dictionary views that store the trigger information with their contents.

Oracle9i New Features: Database Administration Course Overview This course introduces learners to the Oracle9i enhancements in server-side manageability, Oracle Enterprise Manager, and globalization.   Learn To • Identify memory and space management improvements. • Describe segment and index management improvements. • Identify improvements in database operations and performance. • Recognize database resource manager enhancements. • Identify enhancements in using Oracle Names Servers as LDAP proxies. • Explain Oracle Enterprise Manager installation and configuration features in Oracle9i. • Identify Oracle Enterprise Manager console database and enterprise administration features in Oracle9i. • Describe Oracle Enterprise Manager events and jobs features in Oracle9i. • Recognize national language support enhancements. • Identify extended Unicode enhancements. • Describe database character set migration enhancements. • Identify linguistic sort enhancements. • Describe Oracle Locale Builder enhancements. • Identify data time, interval, and DST enhancements. Total Learning Time 6 to 8 hours   Objectives Unit 1: Server-side Manageability Improvements 1 - 2 hours • Identify the benefits of the automated SQL execution feature. • Identify the characteristics of the PGA memory management feature. • Identify the new data dictionary enhancements that support the new memory management features. • Identify the benefits of the dynamic SGA feature. • Identify the characteristics of the dynamic SGA components. • Match the database parameters with their buffer cache sizing attributes. • Identify the features of Oracle Managed Files. • Identify the operational considerations of implementing Oracle Managed Files. • Identify the benefits of the data deletion feature. • Identify the characteristics of the default temporary tablespace. • Identify the characteristics of bitmapped blocks used by the automatic segment free space usage feature. • Identify the operational considerations of implementing automatic segment free space management. • Identify the implementations of the high water mark in the Oracle9i server. • Identify the modified procedures and parameters of the DBMS_SPACE package. • Identify the two methods of fixing bitmap entries by using the SEGMENT_FIX_STATUS procedure of the DBMS_REPAIR package. • Identify the characteristics of global indexes in the Oracle9i server. • Identify the characteristics of the Oracle9i feature that monitors index usage. • Identify the operational considerations of implementing the automatic undo management feature. • Identify the operational considerations of implementing the resumable space allocation feature. • Identify the features of multiple block size support in the Oracle9i server. • Identify the enhancements that have been made to support cached execution plans. • Identify the operational considerations of implementing a persistent parameter file. • Identify the features of the active session pool. • Identify the characteristics of the maximum estimated execution time feature. • Match the resource plan directives that administer the automatic consumer group switching feature with its description. • Identify the operational considerations of the undo quota in the Oracle9i server. • Match the data dictionary objects with their new columns to support resource manager enhancements. • Identify the operational considerations associated with implementing a names server as an LDAP proxy. • Sequence the steps in configuring the Oracle names server as an LDAP proxy server. • Match the CKPTOP.ora components with their descriptions. Unit 2: Oracle Enterprise Manager: New Features 2 - 3 hours • Match the OEM component their minimum machine requirements. • Identify the features of the database detect functionality of Configuration Assistant. • Identify the two supported migration steps for the Oracle Enterprise Manager repository. • Identify the features of the Oracle9i intelligent agent. • Identify the resource manager enhancements in Oracle Enterprise Manager for the Oracle9i server. • Identify the report generation enhancements in Oracle Enterprise Manager. • Identify the reverse engineering DDL features of Oracle Enterprise Manager. • Identify the new Oracle9i features supported by the OEM console. • Identify the facilities provided by the OEM console to support advanced queuing and replication. • Match the Oracle Enterprise Manager component with its functionality in supporting materialized views. • Identify OEM enhancements that support OLAP management. • Identify the available services of the OEM console running in standalone mode. • Identify the controlled areas of the customizable console. • Identify the guidelines for implementing management regions. • Identify reporting framework features employed by Oracle Enterprise Manager. • Identify the benefits of the event creation enhancements in Oracle Enterprise Manager. • Identify the guidelines in dynamically modifying events. • Identify the benefits of user defined events. • Identify the benefits of the event handler. • Identify the features of the blackout facility in Oracle Enterprise Manager. • Identify the job system enhancements in Oracle Enterprise Manager. Unit 3: Globalization Enhancements 3 hours • Identify the differences between byte and character semantics. • Identify the data dictionary views that have been modified to support the new NLS features. • Match the new NLS character sets with their descriptions. • Identify the new language and territory definitions. • Identify the advantages of Unicode. • Match the encoding schemes with their descriptions. • Identify the two methods that implement a Unicode solution. • Match the character sets with their descriptions. • Identify the type conversion considerations for the NCHAR data type. • Identify the possible issues related to character set migration. • Identify the tests performed by the character set scanner utility. • Sequence the steps for performing a character set data conversion. • Identify the features of multilingual linguistic sorts. • Match the linguistic sorts with their support descriptions. • Identify the features of the Oracle Locale Builder. • Identify the guidelines associated with creating new globalization support locale data files. • Identify the datetime and interval data types in the Oracle9i server. • Match the settings used to support time zones and DST with their descriptions. • Identify the guidelines for using the NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT parameters. • Match the built-in SQL functions that support datetime, interval, and DST features with their descriptions.

Oracle9i New Features: Backup, Recovery, and Performance Tuning Course Overview This course introduces students to high availability and fault recovery, data protection and disaster recovery, LogMiner enhancements, online operations, and Recovery Manager enhancements. Students also learn about schema improvements, system improvements, and scalable session state management.   Learn To • Identify the new features designed to reduce unplanned downtime, improve data protection, and provide faster disaster recovery. • Use LogMiner enhancements. • Describe the new features used in online operations. • Identify backup and recovery strategy improvements. • Recognize Recovery Manager improvements. • Identify schema and system improvements. • Describe scalable session state management improvements. Total Learning Time 6 to 8 hours   Objectives Unit 1: Backup and Recovery Enhancements 3 - 4 hours • Identify the two major time elements in the new two-pass instance and crash recovery processes. • Identify the features of Fast-Start Time-Based Recovery. • Match the functionalities of Oracle Flashback and Failsafe. • Identify the advantages of Fast Failover in improving data protection and disaster recovery. • Identify the components which the Data Guard automates using the graphical-user interface. • Identify the features of the Data Guard Architecture that improve data protection. • Identify the characteristics of a Physical Standby Database. • Identify the different log transport modes. • Match the new parameter options used in LOG_ARCHIVE_DEST_N to their description. • Match a data protection technology to its feature. • Identify the new features of the LogMiner. • Identify the new options of the DBMS_LOGMNR package. • Identify the considerations applied when implementing the SKIP_CORRUPTION procedure. • Identify the features of the LogMiner viewer. • Identify the indexes that support online index rebuild. • Identify the new functionalities of index-organized tables. • Sequence the steps in performing an online redefinition of a table. • Identify the considerations applied when the database is in a quiesced state. • Identify the RMAN manageability features. • Match persistent configuration parameter used in RMAN to its functionality. • Match the backup and restore type to its option. • Identify the considerations applied when implementing archive log failover and the backup piece failover. • Identify the benefits of BMR. • Identity the features of the trial recovery capability of Recovery Manager. Unit 2: Performance and Tuning Enhancements 3 - 4 hours • Identify the benefits of IOTs. • Identify the characteristics of the skip scan algorithm. • Identify the benefits of the GET_DDL function in the DBMS_METADATA package. • Identify the benefits of the SIMILAR parameter option used in cursor sharing. • Match the performance topics to their focus areas. • Identify the four new features of the Oracle Shared Server. • Identify the considerations applied in using the external procedure agent. • Identify the three kinds of threads used in a multi-threaded agent HS architecture. • Identify the features of OCI Connection Pooling. • Sequence the steps in using connection pooling in your application. • Identify the characteristics of the Virtual Interface Protocol Adapter.

Oracle9i Database Administration: Manage an Instance Course Overview The Oracle9i Database Administration: Manage An Instance course is the first in a five-part series covering the database administration strategies and procedures provided in Oracle9i. This course introduces the participants to the concepts and procedures associated with Oracle architectural components. The participants learn the various tasks of a database administrator that are performed by using OEM and the steps to start and shut down an instance. This course also explains the various dynamic performance views and initialization parameters and the globalization support that is provided.   Learn To • Identify the memory structures of the SGA and the information stored in the database and nondatabase files. • Identify the connection procedures and the processes generated during database connection. • Identify the stages in processing a query and the memory structures used in query processing. • Identify the steps of DML processing and the memory structures involved in DML processing. • Identify the uses of SCN and the features of a process commit. • Identify the tasks of a DBA and the Oracle Enterprise Manager tools used by a DBA. • Identify the OEM console components, steps to set up these components, and the functions of the OEM DBA tools. • Identify the default DBA user accounts and set up authentication methods to validate users. • Identify the parameters stored in the parameter file, their uses, and the rules followed while specifying the parameters. • Identify the tasks performed in the stages of instance startup and shutdown. • Identify the characteristics and parameters of dynamic performance views and display the current parameter values by using V$PARAMETER. • Modify a dynamic initialization parameter by using the Oracle Instance Manager and manage user sessions by using the Oracle Instance Manager. • Identify the characteristics of trace and alert files, the parameters controlling the trace files, and the contents of alert files. • Identify the character-encoding schemes and the reasons for choosing the national character sets for the database. • Identify the database initialization parameters, the derived NLS parameters, and the way to control the sort sequence. • Retrieve the data dictionary information about the character sets and the NLS settings. Total Learning Time 6 to 8 hours   Objectives Unit 1: Oracle Architecture Components 1 - 2 hours • Match the memory structures of the SGA with the information that they store. • Match the mandatory background processes with their functions. • Match the different types of files existing in a database with the information that they store. • Match database-related files with the information that they store. • Match the terms associated with establishing a database connection with their descriptions. • Identify the characteristics of a user process. • Identify the characteristics of a server process. • Match the Oracle security types with their functions. • Sequence the stages in processing a query. • Identify the information stored in the shared pool components. • Identify the characteristics of the database buffer cache. • Identify the features of buffer cache advisory. • Match the contents of the Program Global Area with their uses. • Identify the steps in the execute phase of a given DML statement. • Identify the uses of a rollback segment. • Identify the characteristics of a redo log buffer. • Identify the events that cause the Database Writer to write to the data files. • Identify the events that cause the Log Writer to write to the redo log files. • Identify the uses of system change numbers during commit processing. • Sequence the steps that occur during the process of commit processing. • Identify the advantages of the fast commit process used by the Oracle server. Unit 2: Administration Tools 1 - 2 hours • Identify the tasks of a database administrator. • Match the utilities used by the DBA with the administrative tasks that they enable the DBA to perform. • Identify the features of the Universal Installer. • Match the features of the OEM architectural components with their functions. • Match the OEM Console components with the tasks that they perform. • Match the OEM services with their tasks. • Sequence the steps performed to set up OEM for use. • Match the OEM DBA Management Pack applications with the tasks that they enable you to perform. • Match the components in the additional management packs with their functions. Unit 3: Managing an Oracle Instance 2 hours • Identify the characteristics of the default DBA user accounts. • Set up a NT log on to the Oracle database without a password by using NT User Manager. • Match the REMOTE_LOGIN_PASSWORDFILE parameters with their functions. • Set up a password file authentication on an NT platform by using the ORAPWD utility. • Change the SYS password by using the ORAPWD utility. • Identify the uses of the parameters included in the parameter file. • Identify the rules that govern the parameter values specified in the parameter file. • Identify the essential parameters for an Oracle instance. • Match the undo mode initialization parameters with their functions. • Match the license parameters with their functions. • Identify the commonly modified parameters of a parameter file. • Identify the features of SPFILE. • Identify the guidelines that you use to create a SPFILE. • Identify the features of SPFILE in Real Application Clusters. • Match the stage of instance startup with the correct description. • Identify the methods that are used to start an instance. • Start an Oracle instance by using the OEM Console. • Change a database state by using the OEM Console. • Identify the features of a quiesced database state. • Match the shutdown stages with the tasks performed in each stage. • Identify the conditions with which the instance is shutdown in each shutdown mode. • Identify the features of dynamic performance views. • Match the columns of the V$SPPARAMETER view with the data they store. • Match the associated dynamic performance view with a given description. • Display the current parameter values by using the V$PARAMETER dynamic performance view. • Match the dynamic buffer cache parameters with the values they store. • Modify a dynamic initialization parameter by using the OEM Console. • Change the database mode by using the OEM Console. • Terminate a user session by using the OEM Console. • Identify the differences between an alert file and a trace file. • Match the trace file parameters with the values that they store. • Identify the information stored in a given alert file. Unit 4: Globalization Support 2 hours • Identify the features of Globalization Support. • Match the character-encoding scheme classes with their descriptions. • Identify the features of the character sets. • Identify the combinations of the Globalization Support parameters used for specifying the default conventions and session characteristics. • Match the derived Globalization Support parameters with their descriptions. • Override the default Globalization Support behavior for an individual session by using the NLS_LANG environment variable. • Change the Globalization Support settings for a session by using the ALTER SESSION command. • Specify the linguistic sort type by using the NLS_SORT parameter. • Identify the SQL statement that uses an SQL function and a Globalization Support parameter to generate the given output on its execution. • Identify the features of various semantics. • Select the tests that are performed on data during the scan. • Identify the steps that are used in the scanning process. • Retrieve the character sets for the database by using the nls_database_parameters view. • Retrieve information about Globalization Support settings by using views.

Oracle9i Database Administration: Create a Database Course Overview The Oracle9i Database Administration: Create a Database course is the second in a five-part series covering the database administration strategies and procedures provided in Oracle9i. Participants learn about the concepts associated with storage structure components, management of rollback segments, temporary segments, and indexes. In addition, this course discusses how to create and maintain databases. The course also covers the new features of Oracle9i, such as Oracle Managed Files and Undo tablespaces, and the new features of the LogMiner utility.   Learn To • Discuss the guidelines for creating an OFA structure and the information in the ORACLE_HOME subdirectories. • Identify the requirements and factors considered while creating a database, the guidelines for planning the physical database design, the registry entries that must be specified for proper database functionality, and create a password file. • Identify the types of parameter files, create a parameter file by editing settings in the init.ora file, and start an instance in the NOMOUNT mode. • Identify the command options of the CREATE DATABASE command, the situations in which it fails, create a database by using the CREATE DATABASE command and identify its content after creation. • Identify the benefits of using Oracle-Managed Files and manage data files, control files, and online redo log files using OMF. • Describe the data dictionary structure, identify the data dictionary categories, and retrieve data dictionary information. • Execute catproc.sql and identify the uses of administrative scripts categories. • Identify the properties of stored procedures and packages, the benefits of using stored program units, and the functions of Oracle-supplied packages. • Retrieve information about stored objects, identify the common situations when dependent objects may have an INVALID status, and the functions of the control file with the types of information stored. • Retrieve control file information, multiplex a control file, and identify the uses of redo log groups, the features of members, and the parameter values of redo log files. • Identify the situations in which log switches and checkpoints occur, force log switches and checkpoints, and identify the functions of parameters that control the checkpoints. • Identify the features of archive modes and retrieve information about the archive modes and online redo log groups and members. • Plan the number and size of online redo log files, maintain redo log members and groups, and troubleshoot LGWR errors. • Identify the logical database structure and the types of tablespaces, create, resize, and change the properties of a tablespace by using Oracle Enterprise Manager. • Create read-only tablespaces and drop tablespaces, identify the benefits of multiple tablespaces, and specify storage parameters and retrieve data dictionary information. • Identify the features of undo tablespaces and manage undo tablespaces. Total Learning Time 6 to 8 hours   Objectives Unit 1: Creating a Database: Procedure 1 - 2 hours • Identify the guidelines for creating an OFA-compliant directory structure for Windows NT. • Match the ORACLE_HOME subdirectories with their contents. • Identify the factors to be considered when creating a database. • Identify the prerequisites for creating a database. • Identify the guidelines to be followed for planning a physical database design. • Identify the steps involved in manually creating a database. • Create a database by using the Oracle Database Configuration Assistant. • Identify the registry entries that must be specified for a database to function efficiently. • Specify a password file for an instance by using the ORAPWD utility. • Identify the features of Oracle9i parameter files. • Create a specified parameter file by editing specific settings in the default INIT.ORA file. • Start a specified instance in the NOMOUNT mode by using the Oracle SQL*Plus Worksheet. • Identify the functionality of the CREATE DATABASE command options. • Create a database with the specified parameter values by using the CREATE DATABASE command. • Identify the commonly encountered situations in which the CREATE DATABASE command fails. • Identify the database components that are created upon successful execution of the CREATE DATABASE command. • Identify the characteristics associated with the Oracle-Managed Files feature in Oracle9i. • Identify the benefits of using the OMF feature of Oracle9i. • Select the features of the Oracle-Managed Files initialization parameters. • Set the OMF initialization parameters by using the INIT.ORA file. • Match the Oracle file types with their OMF-compliant filenames. • Identify the clauses that can be omitted during database creation by specifying the DB_CREATE_FILE_DEST initialization parameter. Unit 2: Data Dictionary and Standard Packages 1 - 2 hours • Identify the types of information stored in the data dictionary. • Identify the features of the data dictionary components. • Create data dictionary views for a specific database by executing the appropriate script. • Identify the contents of data dictionary view categories. • Retrieve specific information by querying data dictionary views. • Identify the features of dynamic performance tables. • Create PL/SQL functionality for a specified database by using the Oracle SQL*Plus Worksheet. • Match the administrative scripts categories with their functions. • Identify the properties of stored procedures. • Identify the properties of packages. • Identify the benefits of using stored program units. • Match the Oracle-supplied packages with their functions. • Retrieve information about the status of objects in a specified database by querying the DBA_OBJECTS data dictionary view. • Retrieve information about a stored program unit specification by using the DESCRIBE command. • Identify the common situations when a dependent object may have an INVALID status. Unit 3: Maintaining Redo Logs and Control Files 2 hours • Identify the functions of a control file. • Identify the types of information stored in a control file. • Match the dynamic performance views with the information they contain about the control files. • Multiplex a control file by using the OEM Console. • Identify the uses of redo log groups. • Identify the features of redo log members in a group. • Identify the features of the parameters that limit the number of online redo log files. • Identify the situations in which a log switch occurs. • Force a log switch by using the OEM Console. • Identify the situations in which a checkpoint occurs. • Force a checkpoint by using the OEM Console. • Select the features of the initialization parameters that control database checkpoints. • Select the features of the ARCHIVELOG and NOARCHIVELOG archive modes. • Match the command and dynamic performance views with the information they access about the log and archive status of a database. • Match the dynamic performance views with the information they provide about online redo log groups and members. • Identify the factors to be considered while planning for the number of online redo log files. • Identify the reasons for storing the online redo log files on different disks and separating them from data files. • Identify the factors that influence the sizing of the online redo log files. • Add a redo log group by using the OEM Console. • Add an online redo log member to a redo log group by using the OEM Console. • Sequence the steps you use to relocate an online redo file. • Drop an online redo log group by using the OEM Console. • Drop an online redo log member by using the OEM Console. • Reinitialize an online redo log file by using the Oracle SQL*Plus Worksheet. • Identify the new features of LogMiner for redo log files generated by Oracle9i. • Match the LogMiner views with their contents. • Sequence the steps in a typical LogMiner session. • Match the situations in which specific online redo log members are not available with the resulting LGWR behavior. • Match the possible LGWR error situations with their solutions. Unit 4: Managing Tablespaces and Data Files 2 hours • Identify the components of the logical and physical structure of a database. • Identify the relationships between database components. • Identify the features of a tablespace. • Identify the uses of a tablespace. • Identify the features of a data file. • Identify the features of SYSTEM and non-SYSTEM tablespaces. • Identify the features of locally-managed tablespaces. • Identify the features of multiple nonstandard block size tablespaces. • Identify the features of temporary tablespaces. • Identify the restrictions on default temporary tablespaces. • Identify the features of undo tablespaces in Oracle9i. • Identify the factors to be considered while planning for a tablespace. • Create a specified tablespace by using the OEM Console. • Match the parameters for segment storage allocation with what these parameters determine. • Create a temporary tablespace by using the OEM Console. • Set a temporary tablespace as the default temporary tablespace by using the OEM Console. • Create a tablespace with a nonstandard block size by using the BLOCKSIZE clause. • Add a data file to a tablespace by using the OEM Console. • Enable automatic extension of a data file in a tablespace by using the OEM Console. • Resize a data file by using the Oracle SQL*Plus Worksheet. • Drop a tablespace by using the OEM Console. • Identify the effects of dropping a tablespace. • Change the values of the default storage parameters by using the OEM Console. • Match the tablespace offline modes with their features. • Take a tablespace offline by using the OEM Console. • Move a data file from a non-SYSTEM tablespace by using the OEM Console. • Move a data file belonging to the SYSTEM tablespace by using the Oracle SQL*Plus Worksheet. • Make a read-only tablespace by using the OEM Console. • Sequence the steps to create a read-only tablespace on read-only media. • Create an undo tablespace by using the OEM Console window. • Drop an undo tablespace by using the OEM Console. • Alter an undo tablespace by using the OEM Console. • Identify the benefits of using multiple tablespaces. • Match the dynamic performance and data dictionary views with the information they contain.

Oracle9i Database Administration: Manage Storage Structures Course Overview The Oracle 9i Database Administration: Manage Storage Structures course is the third in a five-part series covering the database administration strategies and procedures provided in Oracle9i. In this course, participants learn the concepts associated with storage structure components. In addition, this course teaches users the procedures for managing segment free space, undo space, temporary segments, and indexes.   Learn To • Identify the types of segments in the storage hierarchy. • Identify the benefits, working, and packages of automatic management of segment free space. • Identify the ways in which extents are controlled by segments. • Identify the block space utilization parameters. • Retrieve storage structure information. • Identify the purposes and the types of rollback segments. • Explain the processes that rollback segments follow for transactions. • Plan, create, and acquire rollback segments. • Modify and remove rollback segments. • Retrieve rollback segment information from the data dictionary. • Describe the solutions to rollback segment problems. • Explain the concepts of automatic management of undo space. • Identify the purpose and the features of the various types of temporary segments. • Retrieve temporary segment information from the data dictionary. • Maintain indexes. • Monitor indexes. Total Learning Time 6 to 8 hours   Objectives Unit 1: Storage Structures 1 - 2 hours • Identify the correct order of the components in the storage hierarchy. • Match the segment types with their features. • Identify the factors that determine the order of precedence for controlling extent allocation. • Coalesce free space in a given tablespace by using the Oracle SQL*Plus Worksheet. • Match the data block contents with their features. • Match the block space utilization parameters with their uses. • Select the features of a bitmapped block. • Identify the benefits of auto-management of free space. • Match the columns that have been modified to support auto-management of segment free space with the existing views that contain them. • Retrieve the information to determine the number of extents and blocks allocated to a segment by using the Oracle SQL*Plus Worksheet. • Retrieve the physical location information for a given segment by using the Oracle SQL*Plus Worksheet. • Retrieve the free space information for a given tablespace by using the Oracle SQL*Plus Worksheet. • Match the procedures of the DBMS_SPACE package with their uses. • Match the DBMS_REPAIR package procedures with their descriptions. Unit 2: Undo Space Management 1 - 2 hours • Identify the purposes of undo segments. • Select the features of automatic undo management. • Sequence the steps that the Oracle server uses to ensure read consistency when a transaction occurs. • Match the undo segments types with their features. • Select the features of undo retention control. • Identify the rules for automatic undo mode configuration. • Identify the features of switching undo tablespaces. • Switch undo tablespaces by using the OEM Console. • Identify the factors to be considered while planning rollback segments. • Identify the guidelines for creating a rollback segment. • Create a rollback segment by using the OEM Console. • Bring a rollback segment online by using the OEM Console. • Identify the steps that an Oracle instance uses to acquire rollback segments when a database opens. • Sequence the steps in which transactions use rollback segments. • Identify the conditions under which rollback segments shrink. • Change the rollback segment storage parameters by using the OEM Console. • Deallocate unused space from a rollback segment by using the OEM Console. • Take a rollback segment offline by using the OEM Console. • Remove a rollback segment by using the OEM Console. • Identify the solutions to the problem of insufficient space for transactions. • Identify the preventive measures for the read consistency error. • Identify the solutions to the blocking session problem. • Sequence the steps to resolve an error in taking a tablespace offline. • Obtain undo statistics by using the Oracle SQL*Plus Worksheet. • Display the general information about rollback segments by using the OEM Console. • Retrieve the statistics about rollback segments by using the Oracle SQL*Plus Worksheet. • Retrieve information about rollback segments that currently have active transactions by using the Oracle SQL*Plus Worksheet. Unit 3: Managing Temporary Segments 2 hours • Identify the features of the various types of temporary segments. • Identify the guidelines for using temporary segments. • Retrieve the temporary segment statistics by using the Oracle SQL*Plus Worksheet. • Retrieve information about temporary segment activity by using the Oracle SQL*Plus Worksheet. Unit 4: Managing Indexes 2 hours • Modify the index storage parameters by using the OEM Console window. • Allocate index space by using the Oracle SQL*Plus Worksheet. • Deallocate the unused space from an index by using the Oracle SQL*Plus Worksheet. • Retrieve index information by using the Oracle SQL*Plus Worksheet. • Monitor index space usage by using the Oracle SQL*Plus Worksheet. • Identify unused indexes by using the Oracle SQL*Plus Worksheet.

Oracle9i Database Administration: Manage Data Storage Course Overview 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.   Learn To • 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 high-water 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 index-organized 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, PK-FK relationship information and trigger information by using Oracle SQL*Plus Worksheet. • Load data by using direct-load 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. Total Learning Time 6 to 8 hours   Objectives Unit 1: Managing Tables 1 hour • 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 1 hour • 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 1 - 2 hours • 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 1 - 2 hours • 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 2 hours • 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.

Oracle9i Database Administration: Manage Security Course Overview This course is the last in a five-part series covering the database administration strategies and procedures provided in Oracle9i. In this course you will learn the concepts and methods associated with implementing security and resource consumption in an Oracle9i database.   Learn To • Define and maintain the five components of the security domain of a user in a given environment. • Define and maintain a named set of resource and password limits. • Demonstrate the use of resource limits and how to set them. • Manage system and object privileges. • Control the administration of privileges in a database. • Demonstrate the use of concepts and skills to keep track of the various events occurring in an Oracle9i database.   Total Learning Time 6 to 8 hours   Objectives Unit 1: Managing Users 1 - 2 hours • Match the components of a security domain with their functions. • Identify the steps for creating a user. • Create a user by using Oracle Security Manager. • Identify the security feature available to an operating system user when a value for the OS_AUTHENT_PREFIX parameter is given. • Identify the guidelines for creating a user. • Maintain passwords by using Oracle Security Manager. • Maintain account locks by using Oracle Security Manager. • Change the user quota on a tablespace by using Oracle Security Manager. • Drop a user by using Oracle Security Manager. • Display the tablespace quotas for users from the data dictionary using Oracle SQL*Plus Worksheet. • Display the account status of users from the data dictionary by using Oracle SQL*Plus Worksheet. Unit 2: Managing Resources and Password Security 1 - 2 hours • Identify the uses of profiles. • Create a user profile. • Assign a profile to an existing user by using Oracle Security Manager. • Alter a user profile to modify resource limits using Oracle Security Manager. • Drop a user profile using Oracle Security Manager. • Display the resource limits for a user by using Oracle SQL*Plus Worksheet. • Match the features of password management offered by profiles with their descriptions. • Create a new profile with password settings by using Oracle Security Manager. • Identify the restrictions that are to be considered while adding a user-defined password function • Display the password and locking information from the data dictionary using Oracle SQL*Plus Worksheet. Unit 3: User Resource Consumption 1 hour • Identify the implications of exceeding the profile limits. • Enable the enforcement of resource limits by using Oracle Instance Manager. • Display the resource limits for users from the data dictionary by using Oracle SQL*Plus Worksheet. • Identify the resources whose limits are enforced at the session level. • Match the main components of the Resource Manager to their description. • Identify the process necessary to manage Resource Manager components. • Identify Resource plan directives with their uses. Unit 4: Managing Privileges 1 hour • Identify the two types of privileges. • Match the given system privileges with their features. • Grant system privileges by using Oracle Security Manager. • Distinguish between the SYSDBA and SYSOPER privileges. • Sequence the steps to set up password file authentication. • Control the dictionary protection mechanism of Oracle9i. • Revoke system privileges by using Oracle Security Manager. • Identify the implication of revoking a system privilege that is granted using the WITH ADMIN OPTION clause. • Grant object privileges by using Oracle Security Manager. • Revoke object privileges by using Oracle Security Manager. • Identify the implication of revoking an object privilege that is granted using the WITH GRANT OPTION. • Display the system privileges that are granted at the database level by using Oracle SQL*Plus Worksheet. • Display the object privileges granted to users by using Oracle SQL*Plus Worksheet. • Identify features of Oracle Enterprise Security. • Identify the features of the single sign-on functionality. Unit 5: Managing Roles 1 hour • Identify the characteristics of roles. • Identify the benefits of roles. • Create a role by using Oracle Security Manager. • Match the predefined roles with their descriptions. • Modify a role by using Oracle Security Manager. • Assign a role to a user by using Oracle Security Manager. • Specify a role as a non-default role for a user by using Oracle Security Manager. • Set a role for a session by using Oracle SQL*Plus Worksheet. • Revoke a role from a user by using Oracle Security Manager. • Drop a role from the database by using Oracle Security Manager. • Identify the guidelines for creating roles. • Display the role information from the data dictionary by using Oracle SQL*Plus Worksheet. • Display the information on active roles and privileges by using Oracle SQL*Plus Worksheet. Unit 6: Auditing 1 hour • Match the categories of auditing with their functions. • Sequence the phases involved in database auditing. • Match the values of the AUDIT_TRAIL parameter with their descriptions. • Specify the command to audit statements by using an Oracle SQL*Plus Worksheet. • Specify the command to audit a privilege by using an Oracle SQL*Plus Worksheet. • Specify the command to audit a schema object by using Oracle SQL*Plus Worksheet. • Specify the command to disable an audit option by using Oracle SQL Worksheet. • Match the audit option views with the information they display. • Display the audit results by using Oracle SQL*Plus Worksheet. • Identify the auditing guidelines. • Move the AUD$ table to a non-SYSTEM tablespace by using Oracle SQL*Plus Worksheet.

Oracle9i Database Administration: Perform Database Backups Course Overview This course introduces students to backup methods for Oracle9i database administration. The backup concepts covered by this course include backup strategies and requirements, Oracle Architecture, archiving data, backup methods, and working with a standby database. Students perform backup procedures using operating system utilities, Oracle9i utilities such as RMAN, standard SQL*Plus commands, and the Oracle Enterprise Manager Console. Students assess the status of critical database files using SQL*Plus commands. In addition, students create a standby database, identify the steps involved in synchronizing a standby database, and activate, refresh, and update a standby database.   Learn To • Identify the factors to consider when defining a backup and recovery strategy. • Multiplex control files by copying them with an operating system utility. • Place a log member from two log groups in a specific directory. • Identify the implications of operating an Oracle database in NOARCHIVELOG mode. • Change the state of an archiving location using the Enterprise Manager Console. • Sequence the steps to perform a closed database backup. • Identify the parameter used to multiplex files with the backup command. • Create a standby database by using SQL* Plus. Total Learning Time 2 to 4 hours   Objectives Unit 1: Oracle Backup Architecture 1 - 2 hours • Select the backup and recovery goals of a database administrator. • Identify the factors to consider when defining a backup and recovery strategy. • Match the DBA consideration to the backup and recovery results. • Multiplex control files by copying them with an operating system utility. • Match the key Oracle server file structures with their uses. • Differentiate between the user and server processes for user interaction with a database. • Match the key Oracle memory structures with their functions. • Match the functions of the key background processes with their functions. • Place a log member from two log groups in a specific directory. Unit 2: Database Archiving 1 - 2 hours • Identify the implications of operating an Oracle database in NOARCHIVELOG mode. • Identify the implications of operating an Oracle database in ARCHIVELOG mode. • Sequence the steps required to change the archive mode of an Oracle database. • Set the LOCATION parameter for the log archive destination in the Enterprise Manager Console. • Set the log_archive_dest to optional at the command prompt. • Change the state of an archiving location using the Enterprise Manager Console. • Configure a secondary archive destination using the LOG_ARCHIVE_DUPLEX_DEST parameter. • Manage the database archive process. • Configure the database for archiving the online redo log files by setting parameters in the init.ora file. • Identify the data dictionary views that are used to obtain archive log information.

Oracle9i Database Administration: Recover Databases Course Overview This course introduces students to Oracle9i database recovery concepts. The recovery concepts course includes recovery with and without archiving, recovery of offline and online databases, recovery with missing data files, and incomplete recovery with and without archiving. Additional concepts covered by this course include time-based recovery, cancel-based recovery, recovery manager components, recovery manager commands, export and import utilities, user managed recovery, fast-start on demand rollback, recovery manager report generations commands, maintaining the recovery manager catalog along with failure points, and troubleshooting recovery problems.   Learn To • Identify the files to be restored for a database in the NOARCHIVELOG mode. • Store the archived logs in a new location using the ALTER SYSTEM command. • Perform a offline database recovery. • Recover a database that failed due to the loss of a data file without a backup. • Perform a time-based recovery operation. • Recover a database that has failed due to the loss of current redo logs in an online database. • Create an export file using the command line interface. • Import objects into a schema by using the command line interface function. Total Learning Time 6 to 8 hours   Objectives Unit 1: Oracle Recovery Methods 2 hours • Identify the files to be restored for a database in the NOARCHIVELOG mode. • Identify the implications of recovering a database in the NOARCHIVELOG mode. • Recover a database in the NOARCHIVELOG mode by using SQL*Plus. • Restore a database in the NOARCHIVELOG mode by using RMAN. • Sequence the steps to recover a failed database when it is in the ARCHIVELOG mode. • Store the archived logs in a new location by using the ALTER SYSTEM command. • Perform a offline database recovery by using SQL*Plus. • Perform an online database recovery by using SQL*Plus. • Recover a database that failed due to the loss of a data file that does not have a backup by using SQL*Plus. • Recover a database that contained a file in the hot backup mode by using SQL*Plus. • Recover a database that failed due to the loss of an online redo log file by using SQL*Plus. • Perform a complete recovery on an online database by using RMAN. • Identify the implications of an incomplete recovery. • Sequence the steps to perform an incomplete recovery. • Perform a time-based recovery operation by using SQL*Plus. • Perform a cancel-based recovery operation by using SQL*Plus. • Recover from a backup control file by using SQL*Plus. • Recover a database that has failed due to the loss of current redo logs in an online database by using SQL*Plus. • Recover a offline database that has failed due to the loss of current redo logs, by using SQL*Plus. • Identify the key features of RMAN. • Match the Recovery Manager command to its corresponding description. • Identify the naming structure used by RMAN when automatic channels are created. • Identify Oracle compliant media management software. • Match the recovery manager database with its corresponding description. • Identify the difference between the RMAN batch mode and interactive mode. • Identify the packages RMAN uses to maintain and query information in the recovery catalog and control file. • Enter the appropriate stand-alone command at the RMAN prompt. • Recover a specific data file using the UNTIL TIME method. • Perform a cancel-based recovery using the UNTIL TIME method. Unit 2: Additional Backup and Recovery Methods 2 - 3 hours • Identify the uses of the Export and Import utilities. • Identify the functions of the modes available in the Export utility. • Match the full database export levels with their purposes. • Create an export file by using the command line interface. • Identify the compatibility issues associated with the Export utility when used with other versions. • Perform a tablespace point in time recovery (TSPITR) using a transportable tablespace. • Match the recovery command with its description. • Identify the characteristics of the Direct-Path export. • Invoke the Direct-Path export by using the command line interface. • Identify the uses of the Import utility for recovering data. • Identify the functions of the Import utility modes. • Import objects into a schema by using the command line interface function. • Sequence the steps in which a table is imported. • Identify National Language Set considerations for the Import utility. • Set the fast-start rollback parameter to HIGH using the Enterprise Manager Console. • Sequence the steps to start an Oracle database with a missing data file. • Identify the methods to begin parallel recovery operations. • Identify the considerations to be taken when performing a read-only tablespace recovery. • Identify the methods used to recover the control file. • Identify the recovery methods used to recover a corrupted data file. • Identify the methods used to re-create a lost recovery catalog. • Generate a report with the given requirements by using the REPORT command. • Generate a report with the given requirements by using the LIST command. • Select the situations in which a recovery catalog is used. • Identify the guidelines for creating a recovery catalog. • Sequence the steps to create a recovery catalog. • Select the control file characteristics that help in backup and recovery. • Register a database in the recovery catalog by using RMAN. • Reset the recovery catalog information to reflect the current state of the target database by using RMAN. • Select the situations in which the recovery catalog must be resynchronized. • Record information in the recovery catalog by using the CATALOG command. • Maintain a recovery catalog by using the CHANGE command. • Perform the operations to manage channel allocations. • Identify the options for re-creating a recovery catalog. • Write the commands to display information from the recovery catalog by using data dictionary views. Unit 3: Failures and Troubleshooting 2 - 3 hours • Identify the methods for resolving statement failure. • Identify the manner in which a user process failure is resolved. • Identify the methods for resolving user error failures. • Identify the recovery process for an instance failure. • Set the FAST_START_MTTR_TIME and query the results. • Identify the tasks to be performed to ensure easy and quick recovery from media failure. • Identify a database failure error condition. • Diagnose a trace file to identify a database failure error condition. • Identify the characteristics of the DB_BLOCK_CHECKSUM parameter. • Verify the integrity of a data file by using the DBVERIFY utility. • Match the DBMS_REPAIR utility procedures to their role in the recovery of a table with block corruption. • Analyze a log file by using the LOGMINER utility.

Oracle9i Database Administration: Set Up Oracle Net Services Course Overview This course covers the architecture and configuration of Oracle Net Services. It details client-server and web implementations, in addition to additional features such as Dynamic Service Registration and the use of the Oracle Names Server as an LDAP Proxy. This course also discusses the configuration of the Oracle Shared Server and Oracle Names Server.   Learn To • Sequence the steps required for a client to establish a connection to an Oracle server. • Match the client and server network components required to perform specific tasks on a Web application client implementation. • Identify the features of automatic instance registration. • Configure a local net service name by using the Oracle Net Configuration Assistant. • Identify the tasks performed to implement an LDAP Proxy naming method. • Identify the benefits of using the Oracle Shared Server configuration. Total Learning Time 6 to 8 hours   Objectives Unit 1: Oracle Net Services Overview 1 - 2 hours • Identify the features of a two-tier network. • Identify the services provided by the middle tier in an n-tier network. • Identify the connectivity issues that must be addressed in an enterprise network environment. • Identify the connectivity features of Oracle Net Services. • Identify the directory services features of Oracle Net Services. • Identify the scalability features of Oracle Net Services. • Identify the security features of Oracle Net Services. • Identify the accessibility features of Oracle Net Services. • Sequence the steps required for a client to establish a connection to an Oracle server. • Match the client and server network components required to perform specific tasks on a Web application client implementation. Unit 2: Oracle Net Services Configuration 1 - 2 hours • Identify the characteristics of a listener. • Sequence the events that occur in a Bequeath session. • Sequence the events that occur in a Redirect session to a dispatcher. • Sequence the steps performed in a direct hand-off of a connection request. • Create a listener. • Configure a listener for database services. • Configure the listener logging and tracing options. • Control a listener. • Identify the features of automatic instance registration. • Identify the benefits of the methods used for client-server connections. • Identify the requirements for making a connection by using the host-naming method. • Configure local naming. • Match key parameters with the client-side files generated by Oracle Net Manager. • Match the client-side connection errors with their probable solutions. • Configure a local net service name. Unit 3: Oracle Names Server 2 hours • Match the service name resolution method with its definition. • Match the Oracle Names directory object with the appropriate definition. • Identify the features of two domain naming models. • Sequence the steps involved in discovering a Names server on the network by an Oracle Names client. • Sequence the events that occur when an Oracle Names client uses its cache to retrieve Names server information. • Identify two approaches to configuring an Oracle Names server. • Create a Names server by using the No Region Database option in Oracle Net Manager. • Start a Names server by using Oracle Net Manager. • Configure a region database for a Names server by using Oracle Net Manager. • Add a service name to a Names server by using Oracle Net Manager. • Delete a Names server by using Oracle Net Manager. • Identify the functions of the primary NAMESCTL commands. • Match the additional NAMESCTL commands with their functions. • Match the NAMESCTL SET modifier commands with their functions. • Configure the Oracle Names client profile by using Oracle Net Manager. • Specify the preferred Names server on the Oracle Names client by using Oracle Net Manager. • Test a Names server by using Oracle Net Manager. • Identify the features of Oracle Names as an LDAP Proxy server. • Identify the tasks performed to implement an LDAP Proxy naming method. • Identify the advanced features of an LDAP Proxy implementation. Unit 4: Oracle Shared Server 2 hours • Match the server configuration method with its description. • Identify the benefits of using a Multithreaded server configuration. • Sequence the steps by which a user process communicates with a dispatcher in a Multithreaded server environment. • Sequence the steps by which a dispatcher interacts with a shared server in a Multithreaded server environment. • Match the OSS configuration parameter with the appropriate function. • Match the data dictionary view used to verify OSS setup with the appropriate description. • Sequence the steps involved in the connection pooling process. • Identify the MTS_DISPATCHERS parameter that contains the correct attributes to enable connection pooling.

Oracle9i Performance Tuning: Strategies and Techniques Course Overview In this course, you are introduced to the strategies and techniques used for tuning the Oracle9i server.   Learn To • Match the tuning goals with the appropriate job role. • Identify common tuning problems that directly affect database performance. • Identify appropriate tuning goals. • Identify key information recorded by the alert log. • Identify the parameters that control the location and format of the alert log file. • Identify the different methods available for generating user process trace files. • Identify the functions of the DBA views used to provide data storage statistics. • Match the dynamic performance views with the statistics they provide. • Match the V$ views used to collect system-wide statistics with their functions. • Identify the factors taken into consideration in selecting the physical structure for data. • Match the index types with their benefits. • Identify the key usage of index organized tables. • Identify the features of the Database Resource Manager. • Identify the guidelines used to administer resource consumer groups. • Identify the processes implemented by resource plans and resource plan directives. • Identify the measures that can be taken to tune memory performance. • Identify the I/O tuning areas that can improve overall system performance. • Identify the CPU tuning practices that can improve overall system performance. Total Learning Time 6 to 8 hours   Objectives Unit 1: Tuning the Oracle9i Server: Overview 1 - 2 hours • Match the tuning goals with the appropriate job role. • Identify common tuning problems that directly affect database performance. • Identify appropriate tuning goals. • Sequence the steps associated with tuning an Oracle9i database. Unit 2: Alert Log, Trace Files, and Events 1 - 2 hours • Identify key information recorded by the alert log. • Identify the parameters that control the location and format of the alert log file. • Identify the different methods available for generating user process trace files. • Identify the parameters that control the location and format of user process trace files. • Identify the V$ views used to obtain wait event information. • Identify key statistics found in event views. Unit 3: Utilities and Dynamic Performance Views 1 hour • Identify the functions of the DBA views used to provide data storage statistics. • Match the dynamic performance views with the statistics they provide. • Match the V$ views used to collect system-wide statistics with their functions. • Match the session-related views with the statistics they provide. • Identify the functions performed by the utlbstat.sql and utlestat.sql scripts. • Match sections of the report.txt output with potential problems and areas of focus for tuning. • Identify the functions of the STATSPACK package. • Locate Oracle supplied scripts used to provide additional statistics. • Order the key steps in the Oracle Expert Tuning Methodology. • Match the performance manager charts with their monitored information. Unit 4: Considerations for Tuning Applications 1 hour • Identify the factors taken into consideration in selecting the physical structure for data. • Match the index types with their benefits. • Identify the key usage of index organized tables. • Match the cluster types with their implementation. • Identify the key usage of histograms. • Identify the functions of materialized views. • Identify the features of OLTP. • Identify appropriate storage allocation requirements for an OLTP system. • Identify appropriate index and cluster implementations for an OLTP system. • Identify application issues associated with an OLTP implementation. • Identify the features of DSS. • Identify appropriate storage allocation requirements for a DSS implementation. • Identify appropriate index and cluster implementations for a DSS implementation. • Identify considerations associated with access path optimization for DSS implementation. • Identify the features of hybrid systems. • Identify appropriate parameters required for hybrid systems. • Identify application issues associated with hybrid systems. Unit 5: Managing a Mixed Workload 1 hour • Identify the features of the Database Resource Manager. • Identify the guidelines used to administer resource consumer groups. • Identify the processes implemented by resource plans and resource plan directives. • Grant resource privileges required to administer the Database Resource Manager. • Identify the features of the temporary workspace used by the Database Resource Manager. • Create the resource consumer groups used by the Database Resource Manager. • Create resource plans and resource plan directives used to distribute processing resources among different users. • Retrieve specific information on existing objects being used by the Database Resource Manager. Unit 6: Other Tuning Considerations 1 hour • Identify the measures that can be taken to tune memory performance. • Identify the I/O tuning areas that can improve overall system performance. • Identify the CPU tuning practices that can improve overall system performance. • Identify the benefits of the GET_DDL function of the DBMS_METADATA package. • Match the performance areas with their focus areas.

Oracle9i Performance Tuning: Managing Memory and Disk I/O Course Overview This course introduces students to the importance of good initial database design, and the methods used to tune an Oracle9i production database. Students learn how to use the available Oracle tools to recognize, troubleshoot, and resolve common performance-related problems in administering an Oracle database.   Learn To • Match the optimizer modes with their descriptions. • Match the OPTIMIZER_MODE parameter values with their description. • Identify the methods of creating stored outlines. • Identify the three major areas of the Shared Pool. • Identify the parameters used to tune the Shared Pool. • Identify the goals for tuning the Library Cache. • Identify the descriptions of redo entries. • Match the statistics used to diagnose disk I/O contention with their descriptions. • Identify the two parameters that allow the DBA to limit the amount of memory used for each Java session. • Identify the performance guidelines to reduce the activity on an overloaded disk. • Identify the methods of striping. • Identify the initialization parameter that indicates the size of read calls during full table scans.   Total Learning Time 6 to 8 hours   Objectives Unit 1: SQL Statement Tuning 1 - 2 hours • Match the optimizer modes with their descriptions. • Match the OPTIMIZER_MODE parameter values with their description. • Identify the methods of creating stored outlines. • Set the value of the USE_STORED_OUTLINES parameter. • Match the values of the USE_PRIVATE_OUTLINES parameter with their descriptions. • Identify the procedures in the DBMS_OUTLN package used to manage stored outlines. • Identify the diagnostic tools available for evaluating the performance of SQL statements and PL/SQL modules. • Identify the sections of the STATSPACK SQL report. • Designate the execution plan Oracle follows for executing a SQL statement by creating an EXPLAIN PLAN. • Sequence the steps to diagnose SQL statement performance with SQL TRACE and TKPROF. • Identify the advantages of using SQL*PLUS AUTOTRACE instead of SQL TRACE. • Identify the two methods of collecting statistics. • Identify the data dictionary views populated by gathering table, index, and column statistics. • Sequence the steps in copying statistics by using the DBMS_STATS package. Unit 2: Tuning the Shared Pool and Buffer Cache 1 - 2 hours • Identify the three major areas of the Shared Pool. • Identify the parameters used to tune the Shared Pool. • Identify the goals for tuning the Library Cache. • Match the V$ views used as diagnostic tools in the Library Cache with their description. • Match the parameters used to tune shared cursors with their description. • Sequence the steps for sizing the Library Cache. • Identify the view used to compute the amount of shareable memory that has been used. • Match the columns in the V$SHARED_POOL_RESERVED view used in helping tune the reserved pool with their description. • Identify which objects should be kept in memory to prevent slow user response time. • Identify the solutions for eliminating large anonymous PL/SQL blocks. • Identify the two keywords used in the Data Dictionary Cache. • Match the three columns in the V$ROWCACHE view used to monitor the Data Dictionary Cache with their description. • Identify the tuning goals for achieving an ideal Data Dictionary Cache ratio. • Match the different block states in the Buffer Cache with their description. • Sequence the steps that the server process performs when managing the Buffer Cache. • Identify the options available to improve the cache hit ratio. • Identify dynamic performance views used to tune the Buffer Cache. • Sequence the steps for measuring the cache hit ratio. • Match the buffer pools with their descriptions. • Identify the clause used to define the default buffer pool for an object. • Identify the tuning goal of the KEEP buffer pool. • Sequence the steps in sizing the RECYCLE buffer pool. • Match the V$ views used in tuning buffer pools with their description. • Sequence the steps for resolving freelist contention. Unit 3: Dynamic SGA and Other SGA Structures 2 hours • Identify the descriptions of redo entries. • Match the statistics used to diagnose disk I/O contention with their descriptions. • Identify the two parameters that allow the DBA to limit the amount of memory used for each Java session. • Identify the init.ora parameter that contains the fixed allocated amount of memory for the Java pool. • Match methods for computing allocated User Global Area space with the appropriate example. • Identify the capabilities of the Large Pool. • Identify the four parameters that most affect SGA size. • Identify the ways to change the size of the Shared Pool. • Match parameters that define the buffer cache sizes for the primary block with their descriptions. • Identify the description of a granule. • Identify the parameters used with I/O slaves. • Identify the parameter used to specify multiple DBWR processes. Unit 4: Configuration and I/O Issues 2 hours • Identify the performance guidelines to reduce the activity on an overloaded disk. • Identify the methods of striping. • Identify the initialization parameter that indicates the size of read calls during full table scans. • Identify the factors used by the Cost-Based Optimizer to determine the cost of full table scans. • Match the types of checkpoints with their description. • Match the parameters used to control the checkpoint queue with their description.

Oracle9i Performance Tuning: Optimizing Sorts and Minimizing Contention Course Overview The content of this course includes details about each component of an Oracle database instance, such as data blocks, disks, memory, CPU, and dynamic views. Students learn about migration and chaining, reorganizing indexes, optimizing sort operations, tuning rollback segments, managing automatic undo, monitoring activities, implementing the Oracle shared server, and troubleshooting.   Learn To • Match the component of database storage with its definition. • Avoid dynamic allocation of extents by using the ALTER TABLE command. • Avoid the dynamic allocation disadvantages by creating a locally managed tablespace. • Sequence the steps in the sort process. • Set the SORT_AREA_SIZE parameter by using the ALTER SESSION command. • Identify the tuning sort goals. • Match the uses of rollback segments with their definitions. • Select the characteristics of rollback segment activity. • Identify the characteristics of rollback segment growth. • Identify the locking mechanism characteristics. • Select the statements that characterize DML and DDL locks. • Identify the DML lock characteristics. • Sequence the process steps of the Oracle shared server configuration. • Identify the characteristics of the Oracle shared server. • Query a dispatcher process to determine the process status using the DISPATCHER view. Total Learning Time 6 to 8 hours   Objectives Unit 1: Using Oracle Data Blocks Efficiently 1.5 - 2 hours • Match the component of database storage with its definition. • Avoid dynamic allocation of extents by using the ALTER TABLE command. • Avoid the dynamic allocation disadvantages by creating a locally managed tablespace. • Select the advantages of incorporating large extents into the database design. • Reclaim the space from the high water mark by using an ALTER TABLE command. • Query the results of an ANALYZE command to evaluate Oracle data block usage. • Match the actions for recovering space with their description. • Identify the characteristics of the Oracle block size. • Identify the advantages and disadvantages of small data block and large data block sizes. • Identify the functions of the PCTFREE and PCTUSED parameters. • Identify the guidelines for setting the PCTFREE parameter. • Identify the guidelines for setting the PCTUSED parameter. • Identify the characteristics of row migration and chaining. • Query a table to detect chained and migrated rows by using the ANALYZE TABLE command. • Remove the chained and migrated rows from a given table by using a four-step procedure. • Monitor the space used for an index by using the ANALYZE INDEX command. • Rebuild an index without writing the redo log entries by using the ALTER INDEX command. Unit 2: Optimizing Sort Operations 1.5 - 2 hours • Sequence the steps in the sort process. • Set the SORT_AREA_SIZE parameter by using the ALTER SESSION command. • Identify the tuning sort goals. • Match the properties of temporary space segments with their descriptions. • Identify the SQL operations that require sorts. • Identify the SQL operations that avoid sorts. • Query the system to diagnose the ratio of disk-sorts to memory-sorts by using the SORTS() statistic. • Query the number of extents and the maximum number of blocks for sorts using V$SORT_SEGMENT. • Join the V$SESSION and V$SORT_USAGE views to obtain information on currently active disk sorts. Unit 3: Tuning Rollback Segments 1 - 2 hours • Match the uses of rollback segments with their definitions. • Select the characteristics of rollback segment activity. • Identify the characteristics of rollback segment growth. • Match the transaction-level type with its definition. • Identify the goals of tuning rollback segments. • Match the dynamic view name with its content. • Diagnose rollback segment header contention by using the V$ROLLSTAT view. • Assign a rollback segment to a transaction by using the SET TRANSACTION command. • Select the guidelines for sizing efficient rollback segments. • Estimate the volume of rollback data by querying the V$TRANSACTION and SESSION views. • Identify the actions that reduce the amount of rollback. • Identify the actions that reduce the amount of rollback. • Identify the causes of problems associated with rollback segments. • Identify the SQL clauses that alter an undo tablespace. • Identify guidelines for switching undo tablespaces. • Identify the properties of the DROP TABLESPACE command. • Identify the properties of the parameters supported by the automatic undo management feature. • Monitor undo segments using the V$UNDOSTAT view. Unit 4: Monitoring and Detecting Lock Contention 1 hour • Identify the locking mechanism characteristics. • Select the statements that characterize DML and DDL locks. • Identify the DML lock characteristics. • Identify the characteristics of the automatic table lock modes. • Match the DDL lock types with their associated characteristics. • Display the object ID of a locked object by querying the V$LOCKED_OBJECT view. • Kill a session to resolve lock contention by using the ALTER SYSTEM KILL SESSION SQL command. • Identify the characteristics of deadlocks. Unit 5: Oracle Shared Server 1 hour • Sequence the process steps of the Oracle shared server configuration. • Identify the characteristics of the Oracle shared server. • Query a dispatcher process to determine the process status using the DISPATCHER view. • Query the database to determine the shared server status using the SHARED_SERVER view. • Query a current process to determine the usage status using the V$SESSION view. • Identify guidelines for resolving possible problems with the Oracle shared server. • Match dynamic performance views with their view contents.