Oracle Database 10g Administrator Certified Associate
Required two exam to become Oracle Database 10g Administrator Certified Associate
EXAM 1 – 1Z0-051 Oracle Database 11g: SQL Fundamentals
EXAM 2 – 1Z0-042 Oracle Database 10g: Administration
EXAM 1 – Oracle Database 11g: SQL Fundamentals
Course Curriculum
Retrieving Data Using the SQL SELECT Statement
- List the capabilities of SQL SELECT statements
- Execute a basic SELECT statement
Restricting and Sorting Data
- Limit the rows that are retrieved by a query
- Sort the rows that are retrieved by a query
- Use ampersand substitution to restrict and sort output at runtime
Using Single-Row Functions to Customize Output
- Describe various types of functions available in SQL
- Use character, number, and date functions in SELECT statements
Using Conversion Functions and Conditional Expressions
- Describe various types of conversion functions that are available in SQL
- Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
- Apply conditional expressions in a SELECT statement
Reporting Aggregated Data Using the Group Functions
- Identify the available group functions
- Describe the use of group functions
- Group data by using the GROUP BY clause
- Include or exclude grouped rows by using the HAVING clause
Displaying Data from Multiple Tables
- Write SELECT statements to access data from more than one table using equijoins and nonequijoins
- Join a table to itself by using a self-join
- View data that generally does not meet a join condition by using outer joins
- Generate a Cartesian product of all rows from two or more tables
Using Subqueries to Solve Queries
- Define subqueries
- Describe the types of problems that the subqueries can solve
- List the types of subqueries
- Write single-row and multiple-row subqueries
Using the Set Operators
- Describe set operators
- Use a set operator to combine multiple queries into a single query
- Control the order of rows returned
Manipulating Data
- Describe each data manipulation language (DML) statement
- Insert rows into a table
- Update rows in a table
- Delete rows from a table
- UControl transactions
Using DDL Statements to Create and Manage Tables
- Categorize the main database objects
- Review the table structure
- List the data types that are available for columns
- Create a simple table
- Explain how constraints are created at the time of table creation
- Describe how schema objects work
Creating Other Schema Objects
- Create simple and complex views
- Retrieve data from views
- Create, maintain, and use sequences
- Create and maintain indexes
- Create private and public synonyms
EXAM 2 – Oracle Database 10g: Administration
Course Curriculum
Architecture
- Outline the Oracle Architecture and its main Components
- Explain the Oracle instance architecture
Installing the Oracle Database Software
- Identify common database administrative tools available to a DBA
- Use optimal flexible architecture
- Install software with Oracle Universal Installer
- Identify and configure commonly used environment variables
- Use Installer Log
Creating an Oracle Database
- Use DBCA to Create a database
- Use DBCA to Delete a database
- Use DBCA to manage a database
Managing the Oracle Instance
- Use Enterprise Manager
- Use SQL*Plus and iSQL*Plus to access the Oracle Database
- Modify database initialization parameters
- Describe the stages of database startup
- Describe the database shutdown options
- View the database alert log
- Use dynamic performance views
Managing Database Storage Structures
- Describe how table row data is stored in blocks
- Define the purpose of tablespaces and data files
- Explain space management in tablespaces
- Create tablespaces
- Manage tablespaces: alter, drop, take offline, put online, add data files, make read-only or read-write, generate DDL
- Obtain tablespace information
- Explain key features and benefits of ASM
Administering User Security
- Create and manage database user accounts
- Create and manage roles
- Grant and revoke privileges
- Create and manage profiles
Managing Schema Objects
- Create and modify tables
- Define constraints and states of constraints
- Dropping and truncating tables
- Create and use B-Tree and Bitmap indexes
- Create Views
- Create sequences
- Use data dictionary
Managing Data and Concurrency
- Manipulate data through the use of SQL
- Identify and administer PL/SQL objects
- Describe triggers and triggering events
- Define levels of locking
- List possible causes of lock conflict
- Monitor and resolve lock conflicts
Managing Undo Data
- Monitor and administer undo
- Configure undo retention
- Describe the relationship between undo and transactions
- Size the undo tablespace
Implementing Oracle Database Security
- Apply the principle of least privilege
- Audit database activity
- Implement Fine-Grained Auditing
Configuring the Oracle Network Environment
- Use Database Control to Create additional listeners
- Use Database Control to Create Oracle Net service aliases
- Control Oracle Net Listeners
- Identify when to use shared servers versus dedicated servers
Proactive Maintenance
- Gather optimizer statistics
- Manage the Automatic Workload Repository
- Use the Automatic Database Diagnostic Monitor (ADDM)
- Set warning and critical alert thresholds
- React to performance issues
Performance Management
- Use enterprise manager to view performance
- Tune SQL by using SQL tuning advisor
- Tune SQL by using SQL access advisor
- Use automatic shared memory management
- Use the memory advisor to size memory buffer
Backup and Recovery Concepts
- Describe the types of failure that may occur in an Oracle Database
- Identify the importance of checkpoints, redo log files, and archived log files
- Tuning instance recovery
- Configure a database for recoverability
- Configure ARCHIVELOG mode
Performing Database Backup
- Create consistent database backups
- Back up your database without shutting it down
- Create incremental backups
- Automate database backups
- Backup a control file to trace
- Monitor flash recovery area
Performing Database Recovery
- Recover from loss of a Control file
- Recover from loss of a Redo log file
- Recover from loss of a system-critical data file
- Recover from loss of a non system-critical data file
Performing Flashback
- Describe flashback database
- Resotore the table contents to a specific point in time
- Recover from a dropped table
- Use Flashback Query to view the contents of the database as of any single point of time
- View transaction history or row with flashback transaction query
Moving Data
- Describe the general architecture of Data Pump
- Use Data Pump export and import to move data between Oracle databases
- Load data with SQL Loader
- Use external tables to move data