|
Oracle SQL and PL/SQL: Basic SELECT
Unit 1: Relational Database Concepts
• Define components of the relational model.
• Identify the structural elements of a relational database.
• List the properties of a relational database.
• Identify the phases of the system development life cycle.
• Identify the three components of an entity relationship model.
• Indicate the diagramming conventions used in entity relationship modeling.
• List characteristics of object relational database management systems.
• Identify the benefits of Oracle's implementation of object relational technology.
• Define the tools used to interact with the Oracle server.
• Identify the types of SQL statements.
Unit 2: Writing Basic SQL 1
• List capabilities of SQL SELECT statements.
• Identify the mandatory elements of a SELECT statement.
• Identify methods for executing SELECT statements.
• Write a SELECT statement that displays all columns and rows of a table.
• Write a SELECT statement that displays specific columns from a table.
• Identify precedence in evaluating an arithmetic expression within a SQL statement.
• Specify an alias for a column heading when extracting data from a table.
• Concatenate multiple columns that are selected from a table.
• Embed a literal character string in a SELECT clause to customize the output displayed.
• Use the DISTINCT keyword to eliminate duplicate rows from the output of a query.
• Manage null values using the NVL function in a SELECT statement.
• Identify the differences between SQL*Plus commands and SQL statements.
• Log in to SQL*Plus through a Window environment.
• Display the structure of a table using the DESCRIBE command.
• Identify SQL*Plus commands used to edit SQL statements.
• Identify SQL*Plus commands used to manage the contents of the SQL buffer.
Unit 3: Restricting and Sorting Data: Write a query that restricts rows returned by using the WHERE clause
• Identify guidelines for specifying literal strings in a WHERE clause.
• Compare one expression to another within a WHERE clause.
• Display rows within 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 containing null values by using 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 precedence rules when evaluating conditions that use comparison and logical operators.
• Sort rows retrieved in ascending order using the ORDER BY clause.
• Sort rows retrieved in descending order 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
• Identify features of a SQL function.
• Identify the two types of SQL functions.
• Identify the features of a single-row function.
• Convert alpha characters to lowercase by using the LOWER function.
• Convert alpha characters to uppercase by using the UPPER function.
• Capitalize the first letter of each work in a character value by using the INITCAP function.
• Display the number of characters in a value by using the LENGTH function.
• Round a value to a specified decimal place by using the Round function.
• Truncate a value to a specified decimal place by using the TRUNIC function.
• Return the remainder of one value divided by another by using the MOD function.
• Identify the common Oracle date formats.
• Select the current date by using the SYSDATE function.
• Perform arithmetic operations on dates.
• Identify common SQL date functions.
• Identify common data type conversion functions.
• Convert a date value to a character string by using the TO_CHAR function.
• Convert a number to a character string by using the TO_CHAR function.
• Evaluate single row functions that are nested.
Back to Oracle8 Library main page
Data Retrieval Techniques
Unit 1: Displaying Data From MultipleTables
• Identify the four 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 non-equijoin.
• Retrieve rows from two tables by using an outer join.
• Retrieve rows within the same table by using a self join.
Unit 2: Group Functions
• 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.
Unit 3: Subqueries
• Identify the requirements for using subqueries.
• Identify the characteristics of three subquery types.
• 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 guidelines for using multiple-row subqueries.
• Write a multiple-row subquery.
• Identify how a query handles null values returned from a multiple-row subquery.
• Write a multiple-column subquery.
• Identify the different output that results from pairwise and nonpairwise subqueries.
• Write a multiple-column subquery in the FROM clause of a SELECT statement.
Unit 4: Reporting with SQL*Plus 2 hours
• Identify the characteristics of substitution variables.
• Prompt the user for a number value using a variable prefixed with a single ampersand.
• Prompt the user for character and date values using a variable prefixed with a single ampersand and surrounded by single quotation marks.
• Specify column names and expressions at run time using substitution variables.
• Prompt the user for a value using a variable prefixed with a double ampersand.
• Accept user input at run time using the ACCEPT command.
• Maintain a variable at run time using the DEFINE and UNDEFINE commands.
• Match the SET command variables with their functions.
• Identify the guidelines for using the SQL*Plus format commands.
• Control the display of a column using the COLUMN command options.
• Group related rows using the BREAK command.
• Format page headers and footers using the TTITLE and BTITLE format commands.
• Run a formatted report using a SQL*Plus script file.
Back to Oracle8 Library main page
DML and DDL
Unit 1: Creating Tables and Constraints
• Identify the main database objects and their structures.
• Reference the tables of another user when appropriate permission has been granted.
• Name database objects according the Oracle naming conventions and guidelines.
• Identify the syntax of the CREATE TABLE statement.
• Identify the use of constraints as a means of enforcing data integrity.
• Prohibit null values using the NOT NULL constraint.
• Define a unique constraint for a column to ensure that two rows do not contain the same value.
• Define a PRIMARY KEY constraint using the CREATE TABLE statement.
• Create a referential integrity constraint for a foreign key column.
• Define a check constraint to ensure that appropriate values are stored in the table.
• Create a table within the SQL*Plus environment.
• Create a table using the rows from an existing table.
• Display the structure of a table, using the DESCRIBE command.
Unit 2: Manipulating Data
• Identify the DML functions and the transaction control commands.
• Insert new rows in a table.
• Insert specific date, time, and user values in a table.
• Insert rows from one table into another table.
• Update a row without violating the integrity constraints for that table.
• Update an entire column in a database table.
• Delete rows from a table.
• Identify the actions that start and end a transaction.
• Execute the COMMIT, SAVEPOINT, and ROLLBACK statements.
• Roll back pending data changes.
• Rollback pending changes to a specified savepoint.
Unit 3: Altering Tables and Constraints
• Add a new column to an Oracle database table by using the ALTER TABLE statement.
• Modify the columns of an Oracle8 table by using the ALTER TABLE statement.
• Add a constraint to an Oracle table by using the ALTER TABLE statement.
• Remove a constraint from a table by using ALTER TABLE statement.
• Manage constraints by using the DISABLE and ENABLE clauses.
• Remove a table from an Oracle database.
• Rename a table in an Oracle database.
• Remove all rows from a table without generating rollback information.
• Manage comments on a table or column in an Oracle database by using the COMMENT clause.
Unit 4: Creating Sequences
• Identify the features of a sequence.
• Create a sequence by using an SQL statement.
• Display sequence values from a data dictionary table.
• Identify the rules of using NEXTVAL and CURRVAL.
• Modify a sequence using the ALTER SEQUENCE statement.
• Remove a sequence using the DROP SEQUENCE statement.
Unit 5: Creating Views
• Identify the advantages of creating views.
• Identify CREATE VIEW statement syntax used to create a view in an Oracle database.
• Create a simple view.
• Create a complex view, to display values from two tables.
• Remove a view from an Oracle database.
• Identify the rules for performing DML operations on views in an Oracle database.
• Restrict the use of DML operations on a base table by using the WITH CHECK OPTION clause.
• Prevent the use of DML operations on a view by using the WITH READ ONLY clause.
• Display details about a user's views by selecting data from the data dictionary.
Unit 6: Creating Indexes
• Identify the characteristics of an index.
• Identify the different index types.
• Create an index.
• Display the indexes in the data dictionary view.
• Drop an index.
Unit 7: Controlling User Access
• Identify the features of database security.
• Create a user with an identifying password.
• Change the password of a user by using the ALTER USER statement.
• Grant privileges to a user by using the GRANT statement.
• Grant privileges to a role.
• Grant object privileges to a user by using the GRANT statement.
• Grant object privileges to a user by using the option WITH GRANT OPTION.
• Identify the seven privilege-related views in the data dictionary.
• Revoke the privileges granted to a user by using the REVOKE statement.
• Create a synonym for a table using the CREATE SYNONYM statement.
• Remove a synonym by using the DROP SYNONYM statement.
Basics
Unit 1: PL/SQL: An Introduction
• 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
• Identify the functions of PL/SQL variables.
• Identify the guidelines for declaring PL/SQL variables.
• Match the PL/SQL datatypes with their descriptions.
• Match the scalar datatypes with their descriptions.
• Declare a scalar variable.
• Declare a scalar variable with the %TYPE attribute.
• Write the code to assign a value a to a variable by using the assignment operator.
• Use operators with the PL/SQL variables.
• 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.
• Create a bind variable in the SQL*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
• Retrieve data from a table by using the SELECT statement..INTO 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.
Unit 4: Controlling Flow in PL/SQL Blocks
• 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.
Unit 5: Composite Datatypes
• Declare a PL/SQL record by using the RECORD datatype.
• Reference a PL/SQL record.
• Declare PL/SQL records with %ROWTYPE.
• Declare a PL/SQL table by using the TABLE datatype.
• Reference a PL/SQL table.
• Match the methods used to operate on PL/SQL tables 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
• 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
• 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 its error-handling method.
• Use the RAISE_APPLICATION_ERROR procedure to raise user-defined error codes and messages.
Back to Oracle8 Library main page
Manage Data Storage
Unit 1: Subprograms: Procedures
• Match the various sections of a subprogram with their contents.
• Identify the components of developing environments.
• Identify the steps to create a subprogram.
• Identify the features of the different types of parameter modes.
• Perform the steps to create a client-side procedure by using Procedure Builder.
• Perform the steps to create a server-side procedure by using SQL*Plus.
• Identify the different methods for passing parameters.
• Perform the steps to create a bind variable by using SQL*Plus.
• Perform the steps to invoke a procedure from SQL*Plus.
• Perform the steps to invoke a procedure from another procedure by using SQL*Plus, a debug session in Procedure Builder.
Unit 2: Subprograms: Functions
• Perform the steps to create a client-side function by using Procedure Builder.
• Perform the steps to create a server-side function by using SQL*Plus.
• Perform the steps to invoke a function using Procedure Builder.
• Perform the steps to invoke a function using SQL*Plus.
• Identify the advantages of user-defined functions in SQL expressions.
• Identify the restrictions when calling user-defined functions from SQL expressions.
• Perform the steps to remove a subprogram.
• Identify the differences between a procedure and a function.
Unit 3: PL/SQL Packages
• Identify the uses of packages.
• Identify the features of package components.
• Identify the advantages of using packages.
• Create the statement of a package specification in SQL*Plus.
• Create the statement of a package body by using SQL*Plus.
• Match the Procedure Builder built-in packages with their features.
• Create the statement to invoke package constructs by using SQL*Plus.
• Identify how to reference a global variable within a package by using SQL*Plus.
• Identify the effect of the persistent state of a package cursor on dependent processor.
• Create the statement to remove a package.
• Perform the steps to start a debug session for a subprogram by using the PL/SQL Interpreter.
• Perform the steps to set a breakpoint by using the PL/SQL Interpreter in the Procedure Builder.
• Step through the code to determine the cause of an error by using the debug command icons.
• Perform the steps to modify local variable values in a debug session by using Procedure Builder
Manage an Instance
Unit 1: Oracle Architecture Components
• Identify the tasks of a database administrator.
• 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 memory structures of the System Global Area (SGA) with the information stored by them.
• Match the mandatory background processes with their functions.
• Match the different types of files existing in a database with the information stored in them.
• Match the non-database files with the information stored in them.
• Sequence the stages in processing a query.
• Identify the combinations of the shared pool components and the information stored in them.
• Identify the characteristics of the database buffer cache.
• Match the contents of the Program Global Area (PGA) 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 (DBWR) to write to datafiles.
• Identify the events that cause the Log Writer (LGWR) to write to the redo log file.
• Identify the uses of System Change Numbers.
• Sequence the steps that occur during COMMIT processing.
• Identify the advantages of the fast COMMIT process used by Oracle.
Unit 2: Administration Tools
• Match the tools used by the DBA with the administrative tasks that they enable the DBA to perform.
• Execute a script from the Server Manager line mode.
• Specify the associated command for a given description.
• Identify the features of the Oracle Enterprise Manager (OEM) architectural components.
• Match the OEM console components with the tasks that they enable.
• Identify the OEM console service used to perform a given task.
• Match the OEM standard applications with the tasks that they enable you to perform.
• Match the components of the Performance Pack with their functions.
• Identify the steps performed to set up OEM for use.
• Match the application interface objects with their uses.
• Match the SQL Worksheet components with their uses.
Unit 3: Managing an Oracle Instance
• Identify the characteristics of the default database administrator user accounts.
• Set up the OS authentication on the NT platform.
• Set up a Password File Authentication on a NT platform by using the ORAPWD80 utility.
• Change the internal password by using the ORADIM80 utility.
• Identify the uses of the parameters included in the parameter file.
• Identify the rules that govern the parameters specified in the parameter file.
• Identify the essential parameters for a parameter file.
• Identify the commonly modified parameters of a parameter file.
• Identify the tasks performed in each stage of an instance startup.
• Start up an instance by using the Oracle Instance Manager.
• Change a database state by using the Oracle Instance Manager.
• Identify the tasks performed in each stage of instance shutdown.
• Identify the features of dynamic performance views.
• Specify the associated dynamic performance view parameter for a given description.
• Display the current parameter values by using the V$PARAMETER dynamic performance view.
• Modify a dynamic initialization parameter by using the Oracle Instance Manager.
• Set up a restricted session to a database by using the Oracle Instance Manager.
• Terminate a user session by using the Oracle Instance Manager.
• Identify the differences between an alert file and a trace file.
• Specify the parameters that are used to control trace files.
• Identify the information stored in a given alert file.
Unit 4: National Language Support (NLS)
• Identify the features of NLS.
• Match the Oracle character encoding scheme classes with their descriptions.
• Identify the reasons for choosing a national character set that is specified in a CREATE DATABASE command.
• Identify combinations of the NLS parameters used for specifying the default conventions and the session characteristics.
• Specify the derived NLS parameters given the descriptions.
• Override the default NLS behavior for an individual user by using the NLS_LANG environment variable.
• Change the NLS settings for a session by using the ALTER SESSION command.
• Specify the linguistic sort type by using the NLS_SORT parameter.
• Identify the combinations of SQL functions and their corresponding NLS Parameters.
• Retrieve the character sets for the database by using a view.
• Retrieve information about NLS settings by using views.
Back to Oracle8 Library main page
Create Database
Unit 1 - Creating a Database: Procedure
• Identify the guidelines for creating an OFA structure.
• Match the ORACLE_HOME subdirectories with their contents.
• Identify the factors to be considered when creating a database.
• Identify the requirements 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.
• Identify the registry entries that have to be specified for proper database functionality.
• Create a specified password file for a given instance by using the ORADIM80 utility.
• 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 Oracle SQL Worksheet.
• Identify the functionality of the CREATE DATABASE command options.
• Create a database with specified parameter values by using the CREATE DATABASE command.
• Identify the commonly encountered situations in which the CREATE DATABASE command fails.
• Identify the contents of a database after its creation.
Unit 2 - Data Dictionary Views and Standard Packages
• Identify the types of information stored in the data dictionary.
• Identify the features of the data dictionary components.
• Create a data dictionary view for a specific database by executing the appropriate script.
• Identify the contents of the data dictionary view categories.
• Retrieve specific information by querying the Dictionary view.
• Create PL/SQL functionality for a specific database by executing the appropriate script.
• Match the administrative script categories with the views and package components they create.
• Identify the properties of stored procedures.
• Identify the properties of packages.
• Identify the benefits of stored program units.
• Match the Oracle-supplied packages with their functions.
• Retrieve information about invalid objects in a specified database by querying the required data dictionary view.
• Retrieve information about a stored program unit specification by using the Oracle SQL Worksheet.
• Identify the common situations when dependent objects may have an invalid status.
Unit 3 - Maintaining Redo Log Files and Control Files
• Identify the functions of a control file.
• Identify the types of information stored in a control file.
• Identify the dynamic performance views and the information they contain about the control files.
• Multiplex a control file.
• Identify the uses of redo log groups.
• Identify the features of redo log members in a group.
• Match the parameters that limit the number of online redo log files with what they determine.
• Identify the situations in which a log switch occurs.
• Force a log switch by using Oracle Backup Manager.
• Identify the activities that are performed during a checkpoint.
• Force a checkpoint by using Oracle Backup Manager.
• Identify the functions of the initialization parameters that can be set to control database checkpoints.
• Identify the features of archive modes.
• Identify the command and dynamic performance views with the information they contain about the log and archive status of a database.
• Identify the dynamic performance views and the information they contain about online redo log groups and members.
• Add a redo log group by using Oracle Backup Manager.
• Add a online redo log member to a redo log group by using Oracle Backup Manager.
• Relocate the online redo log file by using Oracle SQL Worksheet.
• Drop an online redo log group by using Oracle Backup Manager.
• Drop an online redo log member by using Oracle Backup Manager.
• Reinitialize an online redo log file by using Oracle SQL Worksheet.
• 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.
• Identify the factors that influence the sizing of the online redo log files.
• Identify the Log Writer (LGWR) errors and effects when specific online redo log members are not available.
• Identify the possible LGWR errors with their solutions.
Unit 4 - Managing Tablespaces and Data Files
• Identify the logical database structure.
• 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 datafile.
• Differentiate between the types of tablespaces.
• Create a specified tablespace by using Oracle Storage Manager.
• Identify the segment storage allocation parameters and what they determine.
• Create a temporary tablespace by using Oracle Storage Manager.
• Add a datafile to a tablespace by using the Oracle Storage Manager.
• Enable automatic extension of a datafile in a tablespace by using Oracle Storage Manager.
• Resize a datafile by using Oracle SQL Worksheet.
• Change the values of the storage parameters by using Oracle Storage Manager.
• Identify the features of the modes in which a tablespace can be taken offline.
• Take a tablespace offline by using Oracle Storage Manager.
• Move a datafile of the Non-SYSTEM tablespace by using Oracle SQL Worksheet.
• Move a datafile belonging to the SYSTEM tablespace by using Oracle SQL Worksheet.
• Make a tablespace read-only by using Oracle Storage Manager.
• Sequence the steps to create a read-only tablespace on a read-only media.
• Drop a tablespace by using Oracle Storage Manager.
• Identify the effects of dropping a tablespace.
• Identify the benefits of using multiple tablespaces.
• Identify the benefits of specifying storage parameters for a tablespace.
• Identify the dynamic performance views and the information they contain.
Manage Storage Structures
Unit 1: Storage Structures
• Identify the correct order of the components of the storage hierarchy.
• Match the types of data segments with their features.
• Identify the uses of index segments.
• Identify the situations that lead to the creation of various non-schema object segments.
• Identify the situations that lead to the creation of various non-schema object segments.
• Identify the considerations behind the precedence order of storage parameters.
• Coalesce free space in a given tablespace by using Oracle Tablespace Manager.
• Identify the correct sets of objects and their fragmentation propensity levels.
• Match the data block contents with their features.
• Match the block space utilization parameters with the information that they provide.
• Sequence the steps used by the Oracle Server to manage data within a block.
• Retrieve the information to check the current number of extents and blocks allocated to a segment by using Oracle SQL Worksheet.
• Retrieve the information to view the extents for a given segment by using Oracle SQL Worksheet.
• Write a query to check the extents for a given segment by using Oracle SQL Worksheet.
Unit 2: Managing Rollback Segments
• Identify the purposes of rollback segments.
• Identify the rollback segment type given its description.
• Sequence the steps for the process in which rollback segments are used by transactions.
• Identify the conditions under which rollback segments shrink.
• Sequence the steps that Oracle Server uses to ensure read consistency when a transaction occurs.
• Identify the factors to be considered while planning rollback segments.
• Create a rollback segment by using Oracle Storage Manager.
• Bring a rollback segment online by using the Oracle Storage Manager.
• Sequence the steps that Oracle Instance uses to acquire rollback segments when a database opens.
• Change the rollback segment storage parameters by using Oracle Storage Manager.
• Deallocate space from a rollback segment by using Oracle Storage Manager.
• Take a rollback segment offline by using Oracle Storage Manager.
• Remove rollback segment by using Oracle Storage Manager.
• Display the general information about rollback segments by using Oracle Storage Manager.
• Retrieve the statistics about rollback segments by using Oracle SQL Worksheet.
• Retrieve information about rollback segments with currently active transactions by using Oracle SQL Worksheet.
• Identify the solutions to the insufficient space for transactions problem.
• Identify solutions to the read-consistency error.
• Identify the solutions to the blocking session problem.
• Sequence the steps to resolve the error in taking a tablespace offline.
Unit 3: Managing Temporary Segments
• Identify the statements that require the use of temporary segments.
• Identify the features of temporary segments in PERMANENT tablespaces.
• Identify the features of temporary segments in temporary tablespaces.
• Identify the guidelines for using temporary segments.
• Retrieve temporary segment statistics by using Oracle SQL Worksheet.
• Retrieve the information about temporary segment activity by using Oracle SQL Worksheet.
Unit 4: Managing Indexes
• Match the types of indexes with their features.
• Identify the characteristics of 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.
• Create a normal B*Tree index by using Oracle Schema Manager.
• Create a reverse key index by using Server Manager.
• Create a Bitmap index by using Oracle Schema Manager.
• Modify the index storage parameters by using Oracle Schema Manager.
• Allocate index space by using Server Manager.
• Deallocate index space by using Server Manager.
• Identify the situations in which indexes need to be rebuilt.
• Identify the characteristics of index rebuilds.
• Retrieve index statistics by using Oracle SQL Worksheet.
• Drop a given index by using Oracle Schema Manager.
• Retrieve index information by using Oracle SQL Worksheet.
Manage Data Storage
Unit 1: Managing Tables
• Identify the row storage method for various types of tables.
• Identify the information stored in the components of the Oracle row structure.
• Identify the features of the Oracle built-in scalar datatypes.
• Identify the differences between the two large object datatypes.
• Identify the features of ROWID.
• Identify the components in a ROWID format.
• Identify the characteristics that distinguish the two collection datatypes.
• Create a table by using Oracle Schema Manager.
• Identify the guidelines to be followed while creating tables.
• Identify the value for the PCTFREE parameter in a given situation.
• Match the row chaining and row migration events with the situations that trigger them.
• Copy an existing table by using Oracle Schema Manager.
• Identify the storage parameters that when modified will affect a table.
• Identify the block utilization parameters that when modified affect the table blocks.
• Modify the storage and block parameters of a table by using Oracle Schema Manager.
• Manually allocate extents by using Oracle SQL Worksheet.
• Identify the characteristics of the high water mark.
• Validate a table structure by using Oracle SQL Worksheet.
• Execute the command used to detect row migration in a table by using Oracle SQL Worksheet.
• De-allocate the unused space in a table by using Oracle SQL Worksheet.
• Truncate a table by using Oracle SQL Worksheet.
• Identify the effects of truncating a table.
• Drop a table by using Oracle Schema Manager.
• Retrieve table information by using Oracle SQL Worksheet.
• Retrieve the extent information from DBA_EXTENTS by using Oracle SQL Worksheet.
• Obtain the physical location of the rows in a table by using Oracle SQL Worksheet.
Unit 2: Maintaining Data Integrity
• Identify the features of integrity constraints.
• Identify the functions of the various types of declarative integrity constraints.
• Identify the data processing procedure for various constraint states.
• Match the deferred and immediate constraints with their features.
• Identify the procedure followed by Oracle Server to implement unique and primary keys.
• Identify the factors to be considered while performing DDL on the tables referenced by a foreign key.
• Identify the factors to be considered while performing DML on the FK-PK related tables.
• Identify the various types of triggers with the situations under which they are executed.
• Disable triggers by using Oracle SQL Worksheet.
• Enable triggers by using Oracle SQL Worksheet.
• Create an in-line constraint by using Oracle SQL Worksheet.
• Create an out-of-line constraint by using Oracle Schema Manager.
• Identify the guidelines for defining constraints.
• Disable constraints by using Oracle SQL Worksheet.
• Enable a constraint in the novalidate mode by using Oracle SQL Worksheet.
• Enable constraints in the validate mode by using Oracle SQL Worksheet.
• Sequence the steps required for detecting constraint violations by using the EXCEPTIONS table.
• Drop constraints by using Oracle SQL Worksheet.
• Drop triggers by using Oracle Schema Manager.
• Retrieve the constraint information by using Oracle SQL Worksheet.
• Retrieve the information on columns used in a constraint by using Oracle SQL Worksheet.
• Retrieve the PK-FK Relation Information.
• Retrieve the information on triggers and trigger columns by using Oracle SQL Worksheet.
Unit 3: Clusters and Index-Organized Tables
• Match the data storage methods with the type of row distribution they offer.
• Identify the features of clustered tables that distinguish them from regular, unclustered tables.
• Identify the characteristics of clusters.
• Identify the features of the different cluster types.
• Create an index cluster with a cluster index by using Oracle Schema Manager.
• Create a hash cluster by using Oracle Schema Manager.
• Create a table in a cluster by using Oracle Schema Manager.
• Alter a cluster by using Oracle Schema Manager.
• Drop a cluster by using Oracle Schema Manager.
• Identify the guidelines for implementing clusters.
• Identify the data dictionary views to be used for retrieving specified information about clusters.
• Identify the data storage and retrieval features of an index-organized table.
• Identify the features of an index-organized table that distinguish it from a regular table.
• Create an index-organized table with an overflow area in a specified tablespace.
• Identify the contents of the data dictionary views for index-organized tables.
Unit 4: Loading and Reorganizing Data
• Match the methods for loading data into tables with their functions.
• Perform a serial direct-load insert by using the APPEND hint in Oracle SQL Worksheet.
• Perform parallel direct-load inserts by using the PARALLEL hint.
• Identify the features of SQL*Loader.
• Match the input files used by SQL*Loader with their purposes.
• Match the output files used by SQL*Loader with their purposes.
• Identify the differences between the conventional and direct path load data loading methods.
• Sequence the steps that occur when parallel direct load sessions are initiated to load data into a table.
• Identify the guidelines to minimize errors and maximize performance of SQL*Loader.
• Load data into a table by using Oracle Data Manager.
• Identify the methods of troubleshooting the problems that may occur during a SQL*Loader data load.
• Identify the uses of the export and import utilities.
• Identify the objects that can be exported using different export modes.
• Identify the features of the export paths.
• Match the command line parameters for the export utility with their purposes.
• Match the command line parameters for the import utility with their purposes.
• Identify the guidelines to minimize errors and maximize performance of export and import.
• Export objects by using Oracle Data Manager.
• Import objects by using Oracle Data Manager.
• Identify the characteristics of the import process.
• Identify the NLS considerations for exporting and importing objects.
Manage Security
Unit 1: Managing Users
• Match the components of a security domain with their functions.
• Sequence the steps for creating a user.
• Create a user by using Oracle Security Manager.
• Given a value for the OS_AUTHENT_PREFIX parameter, identify the security feature available to the operating system user with a given user name.
• 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 by using Oracle SQL Worksheet.
• Display the account status of users from the data dictionary by using Oracle SQL Worksheet.
Unit 2: Managing Profiles
• Identify the uses of profiles.
• Identify the implications of exceeding each type of profile limit.
• Identify the level at which the limit of a given resource is enforced.
• Create a profile by using Oracle Security Manager.
• Assign a profile to a user by using Oracle Security Manager.
• Enable the enforcement of resource limits when the database cannot be shut down.
• Alter a profile by using Oracle Security Manager.
• Drop a profile by using Oracle Security Manager.
• Display the resource limits for users from the data dictionary by using Oracle SQL Worksheet.
• Match the features of password management available with profiles with their description.
• Create a 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 password and locking information from the data dictionary by using Oracle SQL Worksheet.
Unit 3: Managing Privileges
• Distinguish between system and object privileges.
• Match the given system privileges with their features.
• Grant system privileges by using Oracle Security Manager.
• Distinguish between SYSDBA and SYSOPER privileges.
• Sequence the steps to set up password file authentication.
• Control the dictionary protection mechanism of Oracle8 by using Server Manager.
• Revoke system privileges by using Oracle Security Manager.
• Given a scenario in which a system privilege granted with ADMIN OPTION is revoked, identify the pictoral representation of the result.
• Grant object privileges by using Oracle Security Manager.
• Revoke object privileges by using Oracle Security Manager.
• Given a scenario in which an object privilege granted with GRANT OPTION is revoked, identify the pictorial representation of the result.
• Display the system privileges granted at the database level by using Oracle SQL Worksheet.
• Display the object privileges granted to users by using Oracle SQL Worksheet.
Unit 4: Managing Roles
• Identify the characteristics of a role.
• 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 the Oracle Security Manager.
• Limit the default role of a user by using the Oracle Security Manager.
• Set role for a session by using the Oracle SQL Worksheet.
• Revoke a role from a user by using Oracle Security Manager.
• Drop a role from the database by using the Oracle Security Manager.
• Identify the guidelines for administering roles.
• Display the database role information by using Oracle SQL Worksheet.
• Display the information on active roles and privileges by using Oracle SQL Worksheet.
Unit 5: Auditing
• Match the categories of auditing with their functions.
• Sequence the steps involved in database auditing.
• Match the values of the AUDIT_TRAIL parameter with their descriptions.
• Specify the commands to audit statements by using an Oracle SQL Worksheet.
• Specify the command to audit a privilege by using an Oracle SQL Worksheet.
• Specify the command to audit a schema object by using Oracle SQL 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 Worksheet.
• Identify the auditing guidelines.
• Move the audit trail to a non-system tablespace by using Oracle SQL Worksheet.
Strategies and Backups
Unit 1: Oracle Backup and Recovery Architecture
• Identify the database administrator goals and the criteria for defining a backup and recovery strategy.
• Identify the backup and recovery goals for a database administrator.
• Identify the criteria for defining a backup and recovery strategy.
• Identify the characteristics of a user process.
• Identify the characteristics of a server process.
• Identify the functions of the key Oracle memory structures.
• Identify the components of the SGA.
• Identify the functions of the key Oracle background processes.
• Match the key Oracle file structures with their characteristics.
Unit 2: Database Archiving
• Identify the implications of operating an Oracle database in Noarchivelog mode.
• Identify the characteristics of running a database in Noarchivelog mode.
• Identify the implications of operating an Oracle database in Archivelog mode.
• Sequence the steps involved in changing the archive mode of an Oracle database.
• Set the init.ora parameters to configure the database for archiving the online redo log files.
• Manage the database archive processing.
• Identify the v$ views used for obtaining archive log information.
Unit 3: Oracle Recovery Manager
• Identify the features of Recovery Manager (RMAN).
• Identify the Recovery Manager components.
• Identify the features of Backup Manager.
• Identify the contents of a recovery catalog.
• Identify the situations when a recovery catalog is used.
• Identify the guidelines for creating a recovery catalog.
• Sequence the steps for creating a recovery catalog.
• Identify the characteristics of the control file when using Recovery Manager.
• Start the recovery manager by using the RMAN command.
• Identify the events that occur during the connection process after the RMAN command is executed.
• Match the recovery manager commands used for updating the target database information with their functions.
• Allocate a channel for Recovery Manager.
• Identify the characteristics of a channel.
• Identify the methods used to recreate a recovery catalog.
• Display backup information from the recovery catalog by using Recovery Manager commands.
• Display information from the recovery catalog using data dictionary views.
• Identify data dictionary views used for extracting recovery catalog information.
Unit 4: Oracle Backup Methods
• Sequence the steps to perform a closed database backup.
• Perform an open database backup.
• Identify the requirements to perform an open database backup.
• Write the command to create a backup of a controlfile.
• Identify the guidelines to create a backup of a controlfile.
• Identify the issues associated with backing up a read-only tablespace.
• Identify the impact of making a tablespace as read-only.
• Identify the differences between the logging and nologging options.
• Identify the characteristics of an image copy.
• Identify the steps involved in an image copy process.
• Identify the characteristics of a backup set.
• Sequence the steps followed by RMAN to create a datafile backup set.
• Identify the characteristics of the archive log backup process.
• Perform a backup using a stored script.
• Identify the features of a stored script.
• Identify the command to create a script and make a backup.
• Identify the characteristics of an incremental backup.
• Identify the characteristics of a cumulative backup.
• Identify the constraints in performing a backup when using the Recovery Manager.
• Identify the memory usage characteristics of RMAN.
• Match the RMAN dynamic performance views with the information they display.
• Identify the methods for troubleshooting RMAN.
Troubleshooting and Recovery
Unit 1: Failures and Troubleshooting
• Identify the ways of resolving statement failure.
• Identify the causes of statement failure.
• Identify the ways of resolving user process failures.
• Identify the causes of user process failures.
• Identify the ways of resolving user error failures.
• Identify the causes of user errors failures.
• Identify the recovery process in an instance failure.
• Identify the causes of instance failure.
• Identify the ways of resolving a media failure.
• Identify the causes of media failure.
• Identify a database failure error condition by diagnosing an alert
• Identify the features of an alert file.
• Identify a database failure error condition by diagnosing a trace
• Identify the features of a trace file.
• Identify the use of the checksum parameters.
• Verify the integrity of a data file by using the DBVERIFY utility.
• Identify the features of the DBVERIFY utility.
Unit 2: Oracle Recovery Methods
• 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 failed database in the noarchivelog mode by using the ServerManager.
• Restore a database in noarchivelog mode by using RMAN.
• Identify the syntax of the RESTORE command.
• Sequence the recovery steps for archivelog mode database failure.
• Identify the recovery structures for an archive mode database.
• Identify the implications of archive mode recovery.
• Restore the archived logs to a new location by using the alter system command.
• Recover from a closed database failure.
• Identify the syntax of the recover data file command.
• Locate the data files needing recovery by using the dynamic performance views.
• Recover from an open database failure.
• Recover tablespace syntax.
• Identify the use of the v$datafile view.
• Recover a database from loss of data file that does not have a backup.
• Recover a failed database that had a file in hot backup mode.
• Identify the use of the v$backup view
• Recover from the loss of an online redo log file.
• Perform a complete recovery on an initially open database by using RMAN.
• Identify the use of the Set Newname command.
• Identify the implications of incomplete recovery.
• Identify the reasons for incomplete recovery.
• Identify the different types of incomplete recovery.
• Sequence the steps to perform an incomplete recovery.
• Perform a time-based recovery operation.
• Identify the syntax of the recover command.
• Perform a cancel-based recovery operation.
• Recover using a backup control file.
• Recover from loss of current redo logs in an open database.
• Recover from loss of current redo logs in a closed database.
• Perform an incomplete recovery on an initially open database by using RMAN.
Unit 3: Additional Backup and Recovery Methods
• Identify the uses of the Export and Import utilities.
• Match the levels of full database export with their purposes.
• Identify the three levels of full database export.
• Create an export file by using the command line interface.
• Identify the compatibility issues associated with the Export utility.
• Identify the characteristics of Direct-Path export and invoke Direct-Path export.
• Identify the characteristics of Direct Path Export.
• Invoke direct path export by using the command line interface.
• Identify the uses of the Import utility for recovery.
• Identify the functions of the modes available in the Import utility.
• Identify the modes available in the Import utility.
• Import objects into a schema by using the command line interface.
• Sequence the steps in which a table is imported.
• Identify the NLS considerations for the Export and Import utilities.
• Sequence the steps to start an Oracle database with missing data files.
• Identify the situations when you can start the database with missing data files.
• Identify the methods to enable parallel recovery activities.
• Identify the internal activities involved in the parallel recovery operations.
• Identify the situations when considerations must be taken for Read-Only tablespace recovery.
• Identify the methods to recover from loss of control file.
• Identify the situations in which a DBA is confronted with recovering or recreating control files.
• Identify the recovery methods for a corrupted data file.
• Identify the methods to recreate a lost recovery catalog.
Unit 4: Oracle Standby Database
• Identify the features of a standby database.
• Identify the need of a standby database.
• Identify the guidelines for using a standby database.
• Create a standby database by using Server Manager.
• Identify the steps of synchronizing the standby and primary databases.
• Activate the standby database by using Server Manager.
• Refresh the standby database control file by using Server Manager.
• Identify the implications on the standby database when the physical structure of the primary database is altered.
• Identify the characteristics of the offline data files in the standby database.
Strategies and Techniques
Unit 1: Oracle Performance Tuning Methodology
• List the different roles associated with the tuning process.
• List examples of measurable tuning goals.
• Define the steps associated with the tuning process.
Unit 2: Oracle Alert and Trace Files
• List the diagnostic information that is provided via trace files and events.
• Identify tuning information available in the alert log file.
• View the contents of the alert log file.
• Specify the location of the alert log file.
• Specify the location of the background and user process trace files.
• Identify the contents of the user process trace files.
• Enable instance level and session level tracing.
• Define wait events.
• Describe the dynamic performance views used to identify wait events and statistics associated with wait events.
• Describe the OEM event management system.
• Set events through OEM to be alerted about predefined situations.
• Resolve problems detected by an event.
Unit 3: Utilities and Dynamic Performance Views
• List common dynamic views that provide system level and session level tuning statistics.
• Collect statistics using the dynamic troubleshooting and performance views.
• Define the scripts that gather performance statistics for tuning.
• Gather statistics by executing the UTBSTAT and UTLESTAT scripts.
• Diagnose statistics using the output report from the UTBSTAT and UTLESTAT scripts.
• Describe the OEM tools that provide monitoring and analysis of performance management areas.
• View tuning information using the OEM tuning tools.
Unit 4: Tuning Considerations for Different Applications
• Describe the available data access methods to tune the logical design of the database.
• Identify the demands of online transaction processing systems (OLTP).
• Identify the demands of decision support systems (DSS).
• Describe the data access methods to enhance performance.
• Define appropriate use of B-tree, bitmap and reverse key indexes.
• Define appropriate use of index-organized tables.
• Define appropriate use of clusters.
• Define appropriate use of histograms.
Unit 5: Generic Operating System Tuning Issues and Oracle
• List the primary steps for operating system tuning.
• Identify similarities between operating system and database tuning.
• Monitor and tune system memory.
• Identify how database tuning affects paging.
• Identify the Oracle statistics that help in tuning the operating system.
Managing Memory and Disk I/O
Unit 1: Tuning SQL
• Identify the characteristics of a star query.
• Sequence the steps of a hash join process when a hash join is built.
• Use the EXPLAIN PLAN statement to explain the plan for a query.
• Perform the procedure to set trace up at the session level to look at the performance of a plan.
• Perform the steps to use TKPROF to report the trace statistics in readable form and interpret the output file.
• Perform the procedure to generate an execution plan at the system level for a SQL statement using AUTOTRACE.
• Identify the symptoms of inefficient SQL statements.
• Identify the characteristics of optimizer modes.
• Perform the steps to set the optimizer mode at the instance level using the OPTIMIZER_MODE parameter.
• Perform the steps to register a module using the SET_MODULE procedure.
• Perform the steps to track a module using the READ_MODULE procedure.
• Perform the steps to view statistics for the current session using the V$MYSTAT view.
Unit 2: Tuning the Shared Pool
• Identify the functions of the Library Cache.
• Identify the function of the Data Dictionary Cache.
• Identify the function of the User Global Area.
• Match the diagnostic views of the Library Cache with their respective definitions.
• Perform the steps to determine the percentage of parse calls that find a cursor to share using GETHITRATIO
column of the V$LIBRARYCACHE view
• Perform the steps to determine the reloads-to-pins ratio from the V$LIBRARYCACHE view.
• Identify the steps used to size the Library Cache.
• Perform the steps to compute the amount of sharable memory that has been used using SELECT SUM query.
• Perform the steps to keep large objects using the supplied DBMS_SHARED_POOL package and the KEEP procedure.
• Perform the steps to view the reserved pool space within the shared pool using the V$SHARED_POOL_RESERVED view.
• Identify the guidelines for tuning the shared pool reserved space.
• Perform the procedure to size the user global area and view results in the V$MYSTAT and V$STATNAME views.
• Perform the steps to query the amount of cache misses in the Data Dictionary Cache using the GETMISSES
command column of the V$ROWCACHE view
• Perform the steps to determine the hit ratio for the Data Dictionary Cache using the appropriate SQL statements.
Unit 3: Tuning the Database Buffer Cache
• Identify the characteristics of the Buffer Cache.
• Sequence the steps used to manage the Buffer Cache.
• Select the tuning technique used to monitor the Buffer Cache.
• Perform the steps to view the statistics used to calculate the cache hit ratio using the V$SYSSTAT view.
• Perform the steps to calculate the hit ratio for the Buffer Cache with the hit ratio formula.
• Identify the steps for collecting statistics to estimate how many buffers to add to your cache.
• Perform the steps to calculate the impact of adding some given number of buffers by querying the V$RECENT
BUCKET view.
• Identify the steps for collecting statistics to estimate how many buffers to remove from the cache.
• Perform the procedure for predicting buffer cache performance based on a smaller cache size.
• Identify the data access methods that can cause extremes of hit ratios.
• Select the appropriate guidelines in evaluating the cache hit ratio.
• Match the buffer pool names with their functions.
• Identify the initialization parameters used when creating multiple buffer pools.
• Perform the steps to define the number of buffers for an instance using the DB_BLOCK_BUFFERS initialization parameter.
• Select the proper clause to obtain the size of each object.
• Sequence the steps to determine the number of blocks required for objects in the RECYCLE pool.
• Perform the steps to determine if there have been waits for buffers using the V$SESSION_WAIT view.
• Perform the steps to enable caching during full table scans using the CACHE clause.
• Perform the steps for calculating the hit ratio for multiple pools.
Unit 4: Tuning the Redo Log Buffer
• Identify the contents of the Redo Log Buffer.
• Match the correct function to the server process in the Redo Log Buffer.
• Perform the steps to size the Redo Log Buffer using the LOG_BUFFER parameter.
• Perform the steps to tune the Redo Log Buffer by displaying the number of all redo log space requests and redo entries using $VSYSSTAT.
• Perform the procedure to find the hit ratio of log space requests to redo entries.
• Perform the steps to specify the NOLOGGING option, as an attribute used to reduce redo operations.
• Identify the purpose of a latch.
• Match the types of latches with their definitions.
Unit 5: Configuring and I/O Issues
• Identify the recommended tablespaces and divisions of data for an Oracle database.
• Identify the advantages of partitioned tables.
• Select the two items needed to choose the correct stripe size.
• Perform the steps of a query to determine how many full table scans are taking place.
• Identify the reasons to set the DB_FILE_MULTIBLOCK_READ_COUNT parameter when tuning full table scans.
• Perform the steps of a query to find out the number of disk I/O per disk file using the V$FILESTAT query.
• Perform the steps to determine how well the I/O load is distributed across the disk devices by using the REPORT.txt file.
• Configure the Online Redo Log File and the Archive Log File.
• Identify the functions of checkpoints.
• Perform the steps to tune checkpoints by recording the beginning and end of checkpoints using the LOG_CHECKPOINTS_TO_ALERT parameter.
• Identify the functions of I/O slaves.
• Perform the steps to deploy multiple DBWR processes using the DB_WRITER_PROCESSES parameter.
Optimizing Sorts and Minimizing Contention
Unit 1: Using Oracle Data Blocks Efficiently
• Match components of database storageunits to their definition.
• Query the dba_tables to display segments with less than 10% free blocks.
• Manually allocate an extent.
• Choose the advantages of incorporating large extents into database design.
• Identify characteristics of Oracle data block size.
• Identify characteristics of small data block and large data block sizes.
• Run scripts to create and view the report.txt.
• Identify characteristics of PCTFREE and PCTUSED parameters.
• Calculate and set the PCTFREE parameter when creating a table.
• Calculate and set the PCTUSED parameter.
• Select the situations in which Oracle8 Server coalesces the free space of a data block.
• Identify characteristics of row migration and chaining.
• Query a table to display chained rows.
• Remove migrated rows from a given table.
• Reclaim space above the high water mark.
• Query the results of a table analysis to display important information.
• Use DBMS_SPACE package to display space use in segments.
• Assess index structure and display the percentage of deleted entries.
• Rebuild an index.
Unit 2: Optimizing Sort Operations
• Identify the operations that will cause a sort.
• Sequence the steps in the sort process.
• Change the SORT_AREA_SIZE and SORT_AREA_SIZE_RETAINED parameters.
• Identify goals for tuning sorts.
• Choose the actions that avoid unnecessary sorts.
• Query the V$SYSSTAT view and display memory, disk and row sort value.
• Calculate the ratio of disk sorts to memory sorts and display the results.
• Set the SORT_DIRECT_WRITES parameter to true.
• Identify situations that would improve database performance when using direct writes.
• Create a temporary tablespace.
• Query the number of extents currently allocated to sorts and the maximum number of blocks used by an individual sort.
• After analyzing temporary tablespace, determine if a second tablespace for temporary sorts is needed.
Unit 3: Tuning Rollback Segments
Match uses of rollback segments with their definitions.
• Identify where a new transaction will start placing its rollback entries given a graphic representation of rollback segment activity.
• Identify which system has well tuned rollback segments.
• Match appropriate characteristics associated with read only, read write and serializable transactions.
• Select actions that improve rollback segment performance occurring on a parallel server.
• Identify situations that contribute to optimum rollback segment performance.
• Match the dynamic view names with their definition.
• Access the report.txt file and view information associated with tuning rollback segments
• Diagnose rollback segment header contention.
• Calculate the number of waits for each of block with the total number of requests for data over the same period of time.
• Calculate the number of rollback segments required when executing OLTP transactions.
• Identify storage parameters settings that increase rollback segment efficiency.
• Query the V$TRANSACTION and V$SESSION views to help estimate the volume of rollback data.
• Estimate the volume of rollback data.
• Identify actions that reduce the amount of rollback.
• Select the appropriate actions that increase the probability of successful completion when executing a large
transaction.
• Adjust the configuration of your rollback segments to resolve the snapshot too old error.
Unit 4: Monitoring and Detecting Lock Contention
• Identify locking attributes.
• Label the list of characteristics as either DML or DDL lock attributes.
• Identify DML lock characteristics.
• Label the list of characteristics as either Row Exclusive or Row Share table lock modes.
• Lock a table.
• Identify table partition lock attributes.
• Match DDL locks with the correct definition.
• Identify situations that contribute to lock contention.
• View the name of locked table.
• Launch Oracle Performance Manager and display the locks currently held.
• Kill a session.
• Examine a trace file in the USER_DUMP_DEST directory.
Unit 5: Resolving Contention Issues
• Identify characteristics of contention.
• Identify which areas the DBA can tune.
• Identify different the characteristics of latch types.
• Select redo latch tuning goals.
• Identify latch contention situations.
• Identify courses of action that minimize redo latch contention.
• Identify LRU attributes.
• Identify goals associated with LRU tuning.
• Query V$LATCH and V$LATCHNAME to display information regarding latches.
• Calculate the number of hits on LRU latches and increase the number of latches.
• Identify free lists attributes.
• View information relevant to free list contention problems.
• Reduce free list contention.
Unit 6: Tuning with Oracle Expert
• Sequence the Oracle Expert tuning methodology process.
• Identify the different types of tuning.
• Launch Oracle Expert.
• Match the tuning session scope type with its definition.
• Set the scope of an instance tuning session.
• Set the scope of an application tuning session.
• Set the scope of a structure tuning session.
• View collected data in the Oracle Expert repository.
• Specify the options to display the database class data.
• Change the duration and sample frequency of collecting instance statistics.
• Access schema class data.
• View environment class data.
• View the workload class data.
• View the display and edit options for collected data.
• Locate the default rules.
• View the tuning analysis.
• View recommendations suggested by Oracle Expert.
• Select categories to be printed in a session data report.
• View a sample instance recommendation file.
Net8 Architecture and Basic Configuration
Unit 1: Net8 Overview
• Identify the features of a two-tier network.
• Identify the services provided by an agent in an n-tier network.
• Identify the connectivity issues that must be addressed in an enterprise network environment.
• Identify the features introduced in Net8.
• Identify the benefits of Oracle Names.
• Identify the features that can be configured for Connection Manager.
• Identify the features of the Oracle Security Server.
• Identify the services provided by the Advanced Networking Option (ANO).
• Sequence the steps involved in transferring data when using Security Services of ANO.
• Distinguish between the features of Procedural and Transparent Open Gateways.
• Sequence the steps in which a Net8 client establishes a server connection.
• Identify the methods used to disconnect from a server.
• Identify the location of different configuration files.
• Identify the features of the two types of connection breaks.
• Match the Net8 layers with their tasks.
Unit 2: Basic Net8 Configuration
• Identify the characteristics of a listener.
• Identify the alternate methods by which a listener processes incoming connection requests.
• Sequence the events that occur in a Bequeath session.
• Sequence the events that occur in a Redirect session to a dedicated server process.
• Sequence the events that occur in a Redirect session to a dispatcher.
• Identify the contents of the default listener.ora file.
• Match the optional parameters of the listener.ora file with their functions.
• Create a listener by using the Net8 Assistant.
• Configure a listener for database services by using the Net8 assistant.
• Configure the listener logging and tracing options by using the Net8 assistant.
• Control a listener by using the Listener Control utility commands.
• Match the connection errors related to a listener with their probable solutions.
• 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 by using the Net8 Assistant.
• Identify the key parameters of the client-side files generated by the Net8 Assistant.
• Match the client-side connection errors with their probably solutions.
Unit 3: Network Security
• Identify the network security risks involved in data transmission.
• Identify the network security solutions provided by the Advanced Networking Option.
• Match the encryption and checksumming parameters in the sqinet.ora file with the descriptions of their contents.
• Identify if encryption or checksumming will be turned on based on a combination of client and server configuration parameters.
• Sequence the events that occur when a client is authenticated by using an authentication mechanism.
• Identify the required parameter settings in the sqinet.ora file and the recommended settings in the init.ora file to configure authentication.
Net8 Additional Configuration and Troubleshooting Certification
Unit 1: Oracle Names
• Match the methods used to resolve service names with their features.
• Identify the steps involved in resolving a client request to connect to a service in the Oracle Names service.
• Identify the benefits of using the centralize naming method for name resolution.
• Create a Names server by using the No Region Database option in the Net8 Assistant.
• Start a Names server by using the Net8 Assistant.
• Configure a region database for a Names Server by using the Net8 Assistant.
• Configure a region database for a Names server by using the Net8 Assistant.
• Add service name to a Names server by using the Net8 Assistant.
• Delete a Names server by using the Net8 Assistant.
• Configure the Oracle Names client profile by using the Net8 Assistant.
• Specify the preferred Names server on the Oracle Names client by using the Net8 Assistant.
• Test a Names server by using Net8 Assistant.
• Identify the steps involved in discovering a Names server on the network by an Oracle Names client.
• Identify the steps that occur when used by an Oracle Names client to use its cache for retrieving information.
• Identify the functions of the primary NAMESCTL command.
• Match the additional NAMESCL commands with their functions.
• Match the NAMESCTL SET modifiers with their functions.
Unit 2: Intelligent Agent and Multi-Threaded Server (MTS)
• Identify the components, commands and configuration files for the intelligent agent.
• Create a user for the intelligent agent.
• Identify the functions of the OEM components.
• Identify the commands used to control the intelligent agent.
• Identify the contents of the files used for configuring an intelligent agent.
• Create a user for the intelligent agent to manage job and events.
• Identify the descriptions of the Oracle8 server configuration methods.
• Identify the benefits of using MTS.
• Sequence the steps by which a user process communications with a dispatcher in the MTS.
• Sequence the steps by which the dispatcher interacts with the shared servers to process a user request in the MTS.
• Match the MTS configuration parameters with their functions.
• Match the data dictionary views used to verify MTS setup with their contents.
• Sequence the steps involved in the Connection Pooling process.
• Identify the syntax of the init.ora parameter MTS_DISPATCHERS that needs to be configured to enable Connection Pooling.
Unit 3: Connection Manager
• Identify the benefits of the connection manager features.
• Identify the functions of the Connection Manager CMGW process.
• Identify the functions of the Connection Manager CMADM process.
• Identify the changes that needs to be made to the networking files when using Connection Manager for connection concentration.
• Match the parameters used to configure the Connection Manager Network access control feature with their descriptions.
• Identify the changes that need to be made to the TNSNAMES.ORA file while using the Multiple Protocol Interchange (MPI) feature.
• Match the Connection Manager control utility commands with their functions.
Unit 4: Troubleshooting Network Problems
• Identify the tasks to be performed for troubleshooting a network environment.
• Configure the tracing process by using the Net8 Assistant.
• Identify the features of the files that log the network information.
• Match the options of the Trace Assistant utility with the information they are used to display
|
Oracle 8i Certified Database Administrator CBT Exams 1Z0-001,023,024,025,026
|
n63312D
|
$699.00
|
|
|
Oracle 8i Certified Application Developer
|
n61222D
|
$699.00
|
|
|
