Certificate in Oracle Database Administration

Course Description | Outline | IT Training Home

Course Outline

RDBMS Concepts

• Define a Database
• Database Management System
• Relational Database Management System
• Major Features of Oracle
• Common Terminology for a RDBMS
• How is Tables Related?
• Entity Relationship Modeling Concepts
• Overview of Object Types
• What is SQL?

Overview of SQL and SQL*Plus tools

• What is SQL*Plus?
• Create a Table
• Insert Data
• Select Statement
• WHERE Clause
• Logging in to SQL*Plus
• Describe Command
• SQL*Plus Editing
• Overview of the Data Dictionary
• Data Dictionary Views

The SQL SELECT Statement

• Using DISTINCT
• The WHERE Clause
• Using the IN Operator
• Using the LIKE Operator
• Using the BETWEEN Operator
• Working with NULL Values
• Sorting the Output

Functions

• Number Functions
• Character Functions
• Date Functions
• Format Models Used With Dates
• Comparing and Calculating Dates
• Using TO_DATE and ADD_MONTHS
• Conversion Functions
• Miscellaneous Functions
• Group Functions
• Using GROUP BY
• Eliminating Groups With HAVING

Combining Data: Joins

• Joining Tables
• Equi-Join / Natural Join
• Outer Joins / left outer / right outer /full outer
• Self Joins

Combining Data: Subqueries

• Sub-queries
• Using EXISTS

SQL*Plus Reporting

• The COLUMN Command
• Displaying Titles
• Using SQL*Plus Parameters
• Overview of the BREAK and COMPUTE Command
• Setting Page Size, Line Size, and Other Options
• Capturing Output to a File (SPOOL)
• Storing SQL Statements (SAVE)
• Creating and Executing Command Files in SQL*Plus

DML Commands

• Inserting Rows in a Table
• Updating Rows in a Table
• Deleting Rows from a Table
• COMMIT
• ROLLBACK
• Save-points
• Virtual Columns
• Using the TRUNCATE Command
• DDL Commands
• Creating Tables
• Oracle Data-types
• Storage Parameters
• Integrity Constraints
• ALTER Statement
• DROP Statement
• RENAME Statement
• Indexes :Invisible Indexes
• Types of Indexes
• Sequences
• Synonyms

Declaring Integrity Constraints

• Overview of Constraints
• PRIMARY KEY Constraint
• FOREIGN KEY Constraint
• UNIQUE Constraint
• CHECK Constraint
• NOT NULL Constraint
• Add a Constraint
• Drop a Constraint
• Enable and Disable a Constraint
• View Constraint Information in the Data Dictionary

Controlling User Access

• Privileges
• System Privileges
• Object Privileges
• Grant Command
• Revoke Command
• Roles
• Using WITH GRANT OPTION
• Viewing Privileges in the Data Dictionary

Views

• Overview of Views
• Create a View
• Query a View
• Using DML with Views
• Using the WITH CHECK OPTION

Using Set Operators

• UNION
• UNION ALL
• INTERSECT
• MINUS

Using Date-time Functions

• Time Zones
• Timestamps

Using Additional Group Functions

• ROLLUP
• CUBE
• GROUPING
• GROUPING SETS

Introduction to PL/SQL

• What is PL/SQL
• PL/SQL Environment
• Benefits of PL/SQL
• Overview of the Types of PL/SQL blocks
• Create and Execute a Simple Anonymous Block
• Generate Output from a PL/SQL Block

Declaring PL/SQL Identifiers

• Identify the Different Types of Identifiers in a PL/SQL subprogram
• Use the Declarative Section to Define Identifiers
• List the Uses for Variables
• Store Data in Variables
• Declare PL/SQL Variables

Writing Executable Statements

• Describe Basic Block Syntax Guidelines
• Use Literals in PL/SQL
• Customize Identifier Assignments with SQL Functions
• Use Nested Blocks as Statements
• Reference an Identifier Value in a Nested Block
• Qualify an Identifier with a Label
• Use Operators in PL/SQL
• Use Proper PL/SQL Block Syntax and Guidelines

Interacting with the Oracle Server

• Identify the SQL Statements You Can Use in PL/SQL
• Include SELECT Statements in PL/SQL
• Retrieve Data in PL/SQL with the SELECT Statement
• Avoid Errors by Using Naming Conventions When Using Retrieval and DML Statements
• Manipulate Data in the Server Using PL/SQL
• The SQL Cursor concept
• Use SQL Cursor Attributes to Obtain Feedback on DML
• Save and Discard Transactions

Writing Control Structures

• Control PL/SQL Flow of Execution
• Conditional processing Using IF Statements
• Conditional Processing CASE Statements
• Handle Nulls to Avoid Common Mistakes
• Build Boolean Conditions with Logical Operators
• Use Iterative Control with Looping Statements

Working with Composite Data Types & Using Explicit Cursors

• Learn the Composite Data Types of PL/SQL Records and Tables
• Use PL/SQL Records to Hold Multiple Values of Different Types
• Inserting and Updating with PL/SQL Records
• Use INDEX BY Tables to Hold Multiple Values of the Same Data Type
• Cursor FOR Loops Using Subqueries
• Increase the Flexibility of Cursors By Using Parameters
• Use the FOR UPDATE Clause to Lock Rows
• Use the WHERE CURRENT Clause to Reference the Current Row
• Use Explicit Cursors to Process Rows
• Explicit Cursor Attributes
• Cursors and Records

Handling Exceptions

• Handling Exceptions with PL/SQL
• Predefined Exceptions
• Trapping Nonpredefined Oracle Server Errors
• Functions that Return Information on Encountered Exceptions
• Trapping User-Defined Exceptions
• Propagate Exceptions
• Use The RAISE_APPLICATION_ERROR Procedure To Report Errors To Applications

Creating Stored Procedures Describe PL/SQL blocks and subprograms

• Describe the uses of procedures
• Create procedures
• Differentiate between formal and actual parameters
• List the features of different parameter modes
• Create procedures with parameters and invoke a procedure
• Handle exceptions in procedures
• View source code in the data dictionary

Creating Stored Functions

• Describe stored functions
• List the CREATE OR REPLACE FUNCTION syntax
• Identify the steps to create a stored function
• Create a stored function in iSQL*Plus and execute a stored function
• Identify the advantages of using stored functions in SQL statements
• Identify the restrictions of calling functions from SQL statements
• Describe how procedures and functions differ

Creating Packages

• List the benefits or using PL/SQL packages
• Differentiate between a package specification and a package body
• Create packages
• Include public and private constructs in a package
• Call public and private constructs in a package
• Remove packages

Using More Package Concepts Overload procedure and function definitions

• Use forward declarations
• Create a one-time package initialization block
• Follow the persistent state of constructs in packages
• Use PL/SQL tables and records in packages
• Wrap code to hide the source
• List the various uses for the Oracle supplied packages
• Reuse pre-packaged code to complete various tasks from developer to DBA purposes
• Use the DESCRIBE command to view the package specifications and overloading
• Describe how DBMS_OUTPUT works
• Use UTL_FILE to direct output to operating system files
• Use the HTP package to generate a simple web page
• Describe the main features of UTL_MAIL
• Call the DBMS_SCHEDULER package to schedule PL/SQL code to run

Dynamic SQL and Metadata

• Describe using native dynamic SQL
• List the execution flow of SQL
• Write dynamic SQL using the EXECUTE IMMEDIATE syntax
• Write dynamic SQL with the DBMS_SQL package
• Generate DDL from metadata using the DBMS_METADATA package

Managing Dependencies & LOBS

• Describe dependent and referenced objects
• Track procedural dependencies with dictionary views
• Predict the effect of changing a database object upon stored procedures and functions
• Manage local and remote procedural dependencies
• Describe a LOB object
• Create and maintain LOB data types
• Differentiate between internal and external LOBs
• Use the DBMS_LOB PL/SQL package to control LOBs
• Describe the use of temporary LOBs

Creating Triggers

• Describe different types of triggers
• Describe database triggers and their use
• Create database triggers
• Describe database trigger firing rules
• Remove database triggers

Applications for Triggers

• Create database and system event triggers
• Create triggers on DDL statements
• Use the CALL statement in triggers to invoke procedures
• Explain the rules for reading and writing to tables with triggers
• Describe business application scenarios for implementing with triggers
• Manage trigger code

Understanding and Influencing the PL/SQL Compiler

• Describe native compilation and interpreted compilation
• List the features of native compilation
• Switch between native and interpreted compilation for compiled PL/SQL code
• Set the parameters to control aspects of PL/SQL compilation
• Write a query to retrieve information from the dictionary views on how the PL/SQL code is compiled
• Explain the compiler warning mechanism
• List the steps to use the compiler warnings
• Use DBMS_WARNING to implement compiler warnings

Introduction (Database Architecture)

• Describe course objectives
• Explore the Oracle 10g database architecture

Installing the Oracle Database Software

• Explain core DBA tasks and tools
• Plan an Oracle installation
• Use optimal flexible architecture
• Install software with the Oracle Universal Installer (OUI)

Creating an Oracle Database

• Create a database with the Database Configuration Assistant (DBCA)
• Create a database design template with the DBCA
• Generate database creation scripts with the DBCA

Managing the Oracle Instance

• Start and stop the Oracle database and components
• Use Enterprise Manager (EM)
• Access a database with SQL*Plus and iSQL*Plus
• Modify database initialization parameters
• Understand the stages of database startup
• View the Alert log
• Use the Data Dictionary

Managing Database Storage Structures

• Describe table data storage (in blocks)
• Define the purpose of tablespaces and data files
• Understand and utilize Oracle Managed Files (OMF)
• Create and manage tablespaces
• Obtain tablespace information
• Describe the main concepts and functionality of Automatic Storage Management (ASM)

Administering User Security

• Create and manage database user accounts
• Authenticate users
• Assign default storage areas (tablespaces)
• Grant and revoke privileges
• Create and manage roles
• Create and manage profiles
• Implement standard password security features
• Control resource usage by users

Managing Schema Objects

• Define schema objects and data types
• Create and modify tables
• Define constraints
• View the columns and contents of a table
• Create indexes, views and sequences
• Explain the use of temporary tables
• Use the Data Dictionary

Managing Data and Concurrency

• Manage data through SQL
• Identify and administer PL/SQL Objects
• Describe triggers and triggering events
• Monitor and resolve locking conflicts

Managing Undo Data

• Explain DML and undo data generation
• Monitor and administer undo
• Describe the difference between undo and redo data
• Configure undo retention
• Guarantee undo retention
• Use the undo advisor

Implementing Oracle Database Security

• Describe DBA responsibilities for security
• Apply the principal of least privilege
• Enable standard database auditing
• Specify audit options
• Review audit information
• Maintain the audit trail

Configuring the Oracle Network Environment

• Use Enterprise Manager for configuring the Oracle network environment
• Create additional listeners
• Create Net Service aliases
• Configure connect-time failover
• Control the Oracle Net Listener
• Test Oracle Net connectivity
• Identify when to use shared versus dedicated servers

Proactive Maintenance

• Use statistics
• Manage the Automatic Workload Repository (AWR)
• Use the Automatic Database Diagnostic Monitor (ADDM)
• Describe advisory framework
• Set alert thresholds
• Use server-generated alerts
• Use automated tasks

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

Backup and Recovery Concepts

• Identify the types of failure that may occur in an Oracle Database
• Describe ways to tune instance recovery
• Identify the importance of checkpoints, redo log files, and archived log files
• Configure ARCHIVELOG mode

Performing Database Backups

• Create consistent database backups
• Back your database up without shutting it down
• Create incremental backups
• Automate database backups
• Monitor the flash recovery area

Performing Database Recovery

• Recover from loss of a control file
• Recover from loss of a redo log file
• Perform complete recovery following the loss of a data file

Performing Flashback

• Describe Flashback database
• Restore the table content to a specific point in the past with Flashback Table
• Recover from a dropped table
• View the contents of the database as of any single point in time with Flashback Query
• See versions of a row over time with Flashback Versions Query
• View the transaction history of a row with Flashback Transaction Query

Moving Data

• Describe available ways for moving data
• Create and use directory objects
• Use SQL*Loader to load data from a non-Oracle database (or user files)
• Explain the general architecture of Data Pump
• Use Data Pump Export and Import to move data between Oracle databases
• Use external tables to move data via platform-independent files