So what does this course have to offer? Quite simply, a lot! You can choose from first learning the Oracle 10g Database Structure, to practising complex SQL statements, a database with SQL*Plus and iSQL*Plus, indexing organized tables, creating a job classes and lots more packed neatly in 16 course sessions of 3 hours each.
Course Contents (16 sessions of 3hrs each)
• SQL Statements • SELECT statement • Differentiate between SQL statements and iSQL*Plus commands
• Restricting and Sorting Data • Limit the rows retrieved by a query • Sort the rows retrieved by a query
• Single-Row Functions • Functions available in SQL • Use character, number, conversion functions, and date functions in SELECT statements
• Data from Multiple Tables • Write SELECT statements to access data from more than one table using equality and nonequality joins • View data that generally does not meet a join condition by using outer joins • Join a table to itself using a self-join
• Aggregating Data using Group Functions • Group functions • GROUP BY clause • Include or exclude grouped rows by using the HAVING clause, Subqueries • Describe the types of problems that subqueries can solve • Define subqueries • List the types of subqueries • Write single-row and multiple-row subqueries
• Producing Readable Output with iSQL*Plus • Produce queries that require a substitution variable • Produce more readable output • Create and execute script files
• Manipulating Data • Describe each DML statement • Insert rows into a table • Update, Delete, Merge rows in a table
• Creating and Managing Tables • Database objects, Create tables • Datatypes that can be used when specifying column definition • Alter table definitions • Drop, rename and truncate tables
• Constraints • Introduction to constraints • Create and maintain constraints
• Views • Introduction to view • Create, alter the definition, and drop a view • Retrieve data through a view • Insert, update and delete data through a view
• Database Objects • Create, maintain and use sequences • Create and maintain indexes • Create private and public synonyms
• Oracle 10g Database Structure • Introduction to Oracle 10g DBA course. • Discuss the contents and topics, • Identify the student knowledge levels. • Introduction to databases, • explain the database terminology. • Introduction to SQL Language, • explain simple SQL statements • practice using iSQL*Plus.
• Explain and practice complex SQL statements. • (Table joins, sub query, outer joins, set operations, etc. • Explore the Oracle 10g database architecture, • Explain core DBA tasks and tools, • Plan an Oracle installation, • Install software with the Oracle Universal Installer (OUI), • Create a database with the Database Configuration Assistant (DBCA).
• Start and stop the Oracle database and components, • Access a database with SQL*Plus and iSQL*Plus, • explain the use of SPFILE and PFILE, • Modify database initialization parameters, • Understand the stages of database startup, • Use DDL statements to create, alter, drop tables • practice DML statements (insert, update, delete), • describe table constraints, views, sequences, synonyms and indexes. • 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, • understand different types of tablespaces and create them. • Understand temporary tablespaces, • undo tablespace, • default tablesspaces in the database.
• Create and manage database user accounts, • Authenticate users in 3 different methods (password, externally, globally), • Grant and revoke privileges, • Create and manage roles, • Create and manage profiles, • Implement standard password security features, • Control resource usage by users using profiles.
• Define schema objects and data types, • Create and modify tables, • Define constraints, • Use the Data Dictionary to find object information. • Explain DML and undo data generation, • Configure undo retention, • Guarantee undo retention, • Describe table locks and different types of locks. • Understand the data dictionary tables related to the locks. • Discuss the general concurrency issues and explain the deadlocks.
• Enable standard database auditing, • Specify audit options, Review audit information, • Understand Oracle’s Network Architecture, • Create additional listeners, • Create Net Service aliases, • Configure connect-time failover, • Identify when to use shared versus dedicated servers. • Set up shared server and monitor shared connections to the database. • Manage the Automatic Workload Repository (AWR), • Use the Automatic Database Diagnostic Monitor (ADDM). • Describe advisory framework.
• Identify the types of failure that may occur in an Oracle Database, • Identify the importance of checkpoints, • redo log files, and archived log files. • Describe Flashback database, • Recover from a dropped table, • See versions of a row over time with Flashback Versions Query, • View the transaction history of a row with Flashback Transaction Query • 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.
• Database Architecture Review, • Recovery Manager Features and Components, • Using a Flash Recovery Area with RMAN, Configuring RMAN, • Control File Autobackups, • Retention Policies and Channel Allocation. • Displaying the current RMAN configuration settings, • Altering the backup retention policy for a database. • RMAN Command Overview, • Parallelization of Backup Sets, • Compressed Backups, Image Copy, • Whole Database and Incremental Backups, • LIST and REPORT commands.
• Recovery of Non-Critical Files, • Creating New Temporary Tablespace Recreating Redo Log Files, • Index Tablespaces, and Indexes, • Read-Only Tablespace Recovery, • Authentication Methods for Database Administrators, • Loss of Password Authentication File,
• Creating a new temporary tablespace, • Altering the default temporary tablespace for a database. • Describe Incomplete recovery, • Server and User Managed Recovery commands, • Recovering a Control File Autobackup, • Creating a New Control File, • Simplified, • Point-in-time recovery using RMAN.
• Describe Flashback Database Architecture, • Configuring and Monitoring Flashback Database, • Backing Up the Flash Recovery Area, • Using V$FLASH_RECOVERY_AREA_USAGE, • Flashback Database Considerations, • Using the Flashback Database RMAN interface, • Using Flashback Database EM Interface • Managing and monitoring Flashback Database operations. • How to deal with block corruptions. • Block Corruption Symptoms: ORA-1578, • DBVERIFY Utility and the ANALYZE command, • Initialization parameter DB_BLOCK_CHECKING, • Segment Metadata Dump and Verification, • Flashback for Logical Corruption and using DBMS_REPAIR, • Lock Media Recovery • RMAN BMR Interface, • dumping and Verifying Segment Metadata
• Memory tuning, Oracle Memory Structures, • Automatic Shared Memory Management, • SGA Tuning Principles, Resizing SGA_TARGET, • PGA Management Resources, • Using the Memory Advisor, • 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 • Monitor and manage Database Storage Structures, • Space Management Overview, • Oracle-Managed Files (OMF), • Row Chaining and Migrating, • Proactive Tablespace Monitoring, • Monitoring table and index space usage
