Oracle Certified Associate 12c (OCA 12c)

Oracle Database 12c SQL

Oracle and Structured Query Language (SQL)

• Identify the connection between an ERD and a Relational Database
• Explain the relationship between a database and SQL
• Describe the purpose of DDL
• Describe the purpose of DML
• Build a SELECT statement to retrieve data from an Oracle Database table

Restricting and Sorting Data

• Use the ORDER BY clause to sort SQL query results
• Limit the rows that are retrieved by a query
• Use ampersand substitution to restrict and sort output at runtime
• Use SQL row limiting clause

Using Single-Row Functions to Customise Output

• Use various types of functions available in SQL
• Use character, number, and date and analytical (PERCENTILE_CONT, STDDEV, LAG, LEAD) 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 general functions and conditional expressions in a SELECT statement

Reporting Aggregated Data Using the 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

• Describe the different types of joins and their features
• Use 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

Using Subqueries to Solve Queries

• Define subqueries
• Describe the types of problems subqueries can solve
• Describe the types of subqueries
• Query data using correlated subqueries
• Update and delete rows using correlated subqueries
• Use the EXISTS and NOT EXISTS operators
• Use the WITH clause
• Use 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

• Truncate data
• Insert rows into a table
• Update rows in a table
• Delete rows from a table
• Control transactions

Using DDL Statements to Create and Manage Tables

• Describe data types that are available for columns
• Create a simple table
• Create constraints for tables
• Drop columns and set column UNUSED
• Create and use external tables

Managing Objects with Data Dictionary Views

• Query various data dictionary views

Controlling User Access

• Differentiate system privileges from object privileges
• Grant privileges on tables and on a user
• Distinguish between privileges and roles

Managing Schema Objects

• Describe how schema objects work
• Create simple and complex views with visible/invisible columns
• Create, maintain and use sequences
• Create and maintain indexes including invisible indexes and multiple indexes on the same columns
• Perform flashback operations

Manipulating Large Data Sets

• Describe the features of multitable INSERTs
• Merge rows in a table

Oracle Database 12c Administration

Exploring the Oracle Database Architecture

• List the architectural components of Oracle Database
• Explain the memory structures
• Describe the background processes

Oracle Database Management Tools

• Use database management tools

Oracle Database Instance

• Understand initialisation parameter files
• Start up and shut down an Oracle database instance
• View the alert log and access dynamic performance views

Configuring the Oracle Network Environment

• Configure Oracle Net Services
• Use tools for configuring and managing the Oracle network
• Configure client-side network
• Configure communication between databases

Administering User Security

• Create and manage database user accounts
• Grant and revoke privileges
• Create and manage roles
• Create and manage profiles

Managing Database Storage Structures

• Describe the storage of table row data in blocks
• Create and manage tablespaces

Create and manage tablespaces

• Explain how Oracle database server automatically manages space
• Save space by using compression
• Use the Segment Advisor
• Reclaim wasted space from tables and indexes by using the segment shrink functionality

Managing Undo Data

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

Managing Data Concurrency

• Describe the locking mechanism and how Oracle manages data concurrency
• Monitor and resolve locking conflicts

Implementing Oracle Database Auditing

• Enable standard database auditing and unified auditing

Backup and Recovery Concepts

• Identify the importance of checkpoints, redo log files, and archive log files

Backup and Recovery Configuration

• Configure the fast recovery area
• Configure ARCHIVELOG mode

Performing Database Backups

• Create consistent database backups
• Back up your database without shutting it down
• Create incremental backups
• Automate database backups
• Manage backups

Performing Database Recovery

• Determine the need for performing recovery
• Use Recovery Manager (RMAN) and the Data Recovery Advisor to perform recovery of the control file, redo log file and data file

Moving Data

• Describe ways to move data
• Use SQL*Loader to load data from a non-Oracle database
• Use external tables to move data via platform-independent files
• Explain the general architecture of Oracle Data Pump
• Use Data Pump Export and Import to move data between Oracle databases

Performing Database Maintenance

• Use server-generated alerts

Managing Performance: SQL Tuning

• Use the SQL Tuning advisor

Automating Tasks by Using Oracle Scheduler

• Use Oracle Scheduler to simplify management tasks
• Use job chains to perform a series of related tasks
• Use Scheduler jobs on remote systems
• Use advanced Scheduler features to prioritise jobs

Creating an Oracle Database Using DBCA

• Generate database creation scripts by using DBCA
• Manage database design templates by using DBCA

Upgrading Oracle Database Software  

• Describe data migration methods
• Describe the upgrade process

Introduction to DBAAS

• Recognise the difference between a DBaaS Instance and a Database Instance
• Navigate with My Services Dashboard
• User authentication and roles
• Generate a Secure Shell (SSH) Public/Private Key Pair