|
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
|
|
|
