Oracle Database 10g: Complete Self-Study Course

Oracle DBAs manage the industry's most advanced information systems and command some of the highest salaries. This course is your first step towards success as an Oracle professional, designed to give you a firm foundation in basic database administration, SQL and PL/SQL database reporting and programming. In this class, you'll learn how to install and maintain an Oracle instance, access data, and write procedures, functions, and database triggers to access information and automate functions within the database.

Students will gain a conceptual understanding of the Oracle database architecture and how its components work and interact with one another. Students will also learn how to create an operational database and properly manage the various structures in an effective and efficient manner including performance monitoring, database security, user management, and backup/recovery techniques. This self-study CD (SSCD) course will teach learners to configure an Oracle database for multilingual applications and distributed database access. Students will also practice various methods of recovering the database, using RMAN, SQL, and Flashback technology, use RMAN and explain the various RMAN commands. Diagnostic sources, non-critical recovery, and incomplete recovery concepts are also covered in this course. Learners will also learn how to perform incomplete recovery. In addition, this course covers various functions of the Flashback technology. In addition to performing flashback operations at the database level, you can flash back a table or recover a table that has been dropped by mistake. The course also covers the tools used to monitor and improve database performance as well as detect and repair database corruption. Finally, learners will learn how to use various database technologies, such as Resource Manager, the Scheduler, and materialized views. This SSCD course is designed to provide material for you to prepare for Oracle Certified Associate and Oracle Certified Professional exams.

For detailed discussion and in-depth coverage of Database and SQL tuning topics please refer the courses listed under the Next Courses section.

Audience

• System Analysts
• Technical Consultant
• Database Administrators

Course Topics Database Management in Oracle 10g

• Database Management: Introduction
• Oracle 10g

Basic SQL Statements

• Retrieving Data
• Restricting Data

SQL Functions

• Single-Row Functions
• Group Functions

Advanced SQL Statements

• Joins
• Sub-queries
• Set Operators

DML and Schema Objects

• Manipulating Data
• Creating and Maintaining Tables
• Other Schema Objects
• Data Dictionary

Objects and Data Management

• Controlling User Access
• Maintaining Schema Objects
• Manipulating Large Data Sets
• Multi-table Inserts
• Grouping Data
• Manipulating Data Across Time zones
• Using Advanced Sub-queries

Data Retrieval

• Hierarchical Data Retrieval
• Regular Expression Support

Programming with PL/SQL

• Introduction to PL/SQL
• Declarative Section
• Executable Section
• Data Retrieval, DML, and Cursors
• Control Structures
• Explicit Cursors
• Exceptions Section
• Stored Glossary and Functions

PL/SQL Blocks, PL/SQL Packages & Triggers

• PL/SQL Block Constructs
• Stored Procedures
• Stored Functions
• PL/SQL Package: Basics
• PL/SQL Package: Advanced Concepts
• Oracle-Supplied Packages
• Creating Triggers
• Trigger Applications

Dynamic SQL Statements

• Dynamic SQL and MetaData
• Manipulating Large Objects

PL/SQL Design Considerations

• Standardizing PL/SQL Code
• Managing Dependencies

Compilation Tools and Options

• Compilation

Oracle Database 10g Workshop

• Oracle Database: Overview
• Installing an Oracle Database
• Oracle Database Structure
• Accessing Oracle Database
• Managing Oracle Database
• Oracle Database Table spaces
• Managing Users

Data - Managing

• Managing Database Objects
• Managing Data
• PL/SQL

Working with Databases

• Defining Database Security
• Auditing
• Database Listeners and Services
• Shared and Dedicated Servers
• Performance Tuning and Monitoring
• Maintaining Databases
• Undo and Lock Management
• Backup, Recovery, Database Failures & Recovering Databases

Performance Management

• Use Enterprise Manager pages to monitor performance
• Use the SQL Tuning Advisor
• Use the SQL Access Advisor
• Use Automatic Shared Memory Management
• Use the Memory Advisor to size memory buffers
• Use performance related dynamic views
• Troubleshoot invalid or unusable objects

Scheduler, Advanced Tools & Resource Manager

• Scheduler Components: Creation
• Scheduler Components: Managing
• Advanced Tools
• Resource Manager: Concepts
• Resource Manager: Configuration
• Resource Manager: Using

ASM and Materialized Views

• ASM
• Materialized Views

Flashback Technology & Database Corruption

• Flashback Concepts
• Recovering from User Errors
• Flashback Queries
• Flashback Table
• Dealing With Database Corruption

Automatic Performance Management

• Identifying Tunable Components
• Oracle Wait Events and System Statistics
• Troubleshooting and Tuning Views
• Direct Attach to SGA for Statistic Collection
• Workload Repository & Advisory Framework
• ADDM Scenarios and Usage Tips
• Using the SQL Tuning and SQL Access Advisor
• Memory Advisors & Segment Advisors

Using Recovery Manager & Diagnostic Sources and Recovery

• RMAN Commands & Features of Recovery Manager
• Backup Concepts
• RMAN - Executing Backups
• RMAN - Reporting and Monitoring
• Configuring Recovery Manager
• Incomplete Recovery: Concepts & Performing Incomplete Recovery
• Diagnostic Sources
• Recovering Non-Critical Losses

Using Globalization Support

• Globalization Basics
• Date time with Time Zones
• NLS Parameters
• Linguistic Interoperability
• Migrating Globalized Data

Course Objectives

• SQL and PL/SQL Fundamentals
• Database Administration