OCA: Oracle Database 12c Administrator Certified Associate Study Guide

Biju Thomas OCA, OCP, is a Senior Oracle Solutions Architect with more than 20 years of Oracle development and database management experience. He has strong skills in database design and administration, performance tuning, and solution architecture. He has coauthored six Oracle Sybex certification books.

• Introduction  xxAssessment Test xxxvPart I Oracle Database 12c: SQL Fundamentals 1Chapter 1 Introducing Oracle Database 12c RDBMS 3Relational Database Management Systems 4Characteristics of a Relational Database 5Oracle’s Implementation of RDBMS and ORDBMS 8The Oracle Database 12c 9Oracle Database 12c Implementations 9Connecting to Oracle Database 13Database Management Tools 14Oracle Database 12c in the Cloud 19Familiarizing SQL*Plus 19Summary 30Exam Essentials 31Review Questions 32Chapter 2 Introducing SQL 35SQL Fundamentals 36Oracle Datatypes 38Operators and Literals 42Writing Simple Queries 46Using the SELECT Statement 46Filtering Rows 51Sorting Rows 62Sorting NULLs 64Limiting Rows 67Using Expressions 68Accepting Values at Runtime 72Using Ampersand Substitution Variables 73Saving a Variable for a Session 75Using Positional Notation for Variables 77Summary 78Exam Essentials 78Review Questions 80Chapter 3 Using Single-Row Functions 89Single-Row Function Fundamentals 90Functions for NULL Handling 91Using Single-Row Character Functions 95Character Function Overview 95Character Function Descriptions 96Using Single-Row Numeric Functions 108Numeric Function Overview 108Numeric Function Descriptions 109Using Single-Row Date Functions 119Date Format Conversion 119Date Function Overview 120Date Function Descriptions 121Using Single-Row Conversion Functions 131Conversion Function Overview 131Conversion Function Descriptions 133Using Other Single-Row Functions 154Miscellaneous Function Overview 154Miscellaneous Function Descriptions 155Summary 167Exam Essentials 168Review Questions 169Chapter 4 Using Group Functions 175Group Function Fundamentals 176Utilizing Aggregate Functions 177Grouping Data with GROUP BY 178Group Function Overview 182Group Function Descriptions: Part 1 184Group Function Descriptions: Part 2 194Limiting Grouped Data with HAVING 207Creating Superaggregates with CUBE and ROLLUP 208Nesting Functions 215Summary 218Exam Essentials 218Review Questions 219Chapter 5 Using Joins and Subqueries 227Writing Multiple-Table Queries 228Inner Joins 229Cartesian Joins 239Outer Joins 240Other Multiple-Table Queries 245Using Set Operators 247The UNION Operator 248The UNION ALL Operator 249The INTERSECT Operator 250The MINUS Operator 250Putting It All Together 250Using Subqueries 252Single-Row Subqueries 253Multiple-Row Subqueries 254Subquery Returns No Rows 256Correlated Subqueries 258Scalar Subqueries 259Multiple-Column Subqueries 267Subqueries in Other DML Statements 268Summary 269Exam Essentials 270Review Questions 271Chapter 6 Manipulating Data 281Using DML Statements 282Inserting Rows into a Table 283Updating Rows in a Table 289Deleting Rows from a Table 294Merging Rows 297Understanding Transaction Control 299Savepoints and Partial Rollbacks 302Data Visibility 304Summary 306Exam Essentials 307Review Questions 308Chapter 7 Creating Tables and Constraints 319Database Objects Overview 320Schema Objects 322Using Sequences 324Built-in Datatypes 325Character Datatypes 325Numeric Datatypes 329Date and Time Datatypes 330Date Arithmetic 334Binary Datatypes 336Row ID Datatypes 337Creating Tables 338Naming Tables and Columns 339Specifying Default Values for Columns 341Adding Comments 348Creating a Table from Another Table 348Modifying Tables 350Adding Columns 350Modifying Columns 353Renaming Columns 354Dropping Columns 354Hiding Columns from Table 356Dropping Tables 357Renaming Tables 357Making Tables Read-Only 358Managing Constraints 361Creating Constraints 362Dropping Constraints 367Enabling and Disabling Constraints 367Deferring Constraint Checks 369Summary 373Exam Essentials 374Review Questions 375Part II Oracle Database 12c: Installation and Administration 381Chapter 8 Introducing Oracle Database 12c Components and Architecture 383Oracle Database Fundamentals 385Relational Databases 385Oracle Database 12c Objects 386Interacting with Oracle Database 12c 388Oracle Database 12c Architecture 391User and Server Processes 394The Oracle Instance 396Oracle Storage Structures 410Summary 424Exam Essentials 425Review Questions 426Chapter 9 Creating and Operating Oracle Database 12c 431Oracle Database 12c Software Installation 432Planning the Oracle Database 12c Software Install 433Using the Oracle Universal Installer 440Using DBCA to Create an Oracle 12c Database 447Invoking the Database Configuration Assistant 447Configuring an Oracle Database Using the DBCA 472Deleting an Oracle Database Using the DBCA 472Managing Database Templates Using the DBCA 473Working with Oracle Database Metadata 475Data Dictionary Views 476Dynamic Performance Views 477Managing Initialization-Parameter Files 479Locating the Default Parameter File 484Modifying Initialization-Parameter Values 485Starting Up and Shutting Down an Oracle Instance 490Starting Up an Oracle Database 12c Instance 491Shutting Down an Oracle Database 12c Instance 495Monitoring the Database Alert Log 497Summary 503Exam Essentials 504Review Questions 506Chapter 10 Understanding Storage and Space Management 511Understanding the Physical and Logical Storage 512Contents of a Data Block 514Managing Tablespaces 516Identifying Default Tablespaces 516Creating and Maintaining Tablespaces 516Obtaining Tablespace Information 531Managing Data Files 534Performing Operations on Data Files 535Using the Oracle Managed Files Feature 539Querying Data File Information 544Managing Space 547Automatic Space Management Features 547Monitoring Tablespace Free Space 551Using Segment Advisor 554Avoiding Out-of-Space Errors 559Summary 562Exam Essentials 563Review Questions 565Chapter 11 Managing Data Concurrency and Undo 569Managing Data Changes Using DML 570Understanding “Change” 571Differentiating Undo and Redo 572Configuring and Monitoring Undo 576Managing Data Concurrency 584Understanding Locks and Transactions 585Maximizing Data Concurrency 587Detecting and Resolving Lock Conflicts 590Summary 595Exam Essentials 595Review Questions 597Chapter 12 Understanding Oracle Network Architecture 603Introducing Network Configurations 604Single-Tier Architecture 605Two-Tier Architecture 605n-Tier Architecture 606An Overview of Oracle Net Features 607Connectivity 608Manageability 608Scalability 609Security 611Accessibility 613Configuring Oracle Net on the Server 613Understanding the Oracle Listener 614Managing Oracle Listeners 618Dynamically Registering Services 634Oracle Net Logging and Tracing on the Server 635Configuring Oracle Net for the Client 637Client-Side Names Resolution Options 637The Host Naming Method 638The Oracle Easy Connect Method 639The Local Naming Method 641Troubleshooting Client-Side Connection Problems 646An Overview of Oracle Shared Server 648Dedicated Server vs. Shared Server 648Advantages and Disadvantages of Shared Server 650Oracle Shared Server Infrastructure 651Configuring the Oracle Shared Server 653Managing a Shared Server 658Understanding Database Resident Connection Pooling 661Configuring DRCP 662Comparing Connection Architectures 663Communicating Between Databases 666Introduction to Database Links 666Creating Database Links 667Summary 669Exam Essentials 670Review Questions 672Chapter 13 Implementing Security and Auditing 677Creating and Managing User Accounts 678Configuring Authentication 679Assigning Tablespaces and Quotas 681Assigning a Profile and Account Settings 684Removing a User from the Database 686Managing Default User Accounts 687Granting and Revoking Privileges 687Granting Object Privileges 688Granting System Privileges 692Role Privileges 701Applying the Principle of Least Privilege 705Controlling Resource Usage by Users 711Implementing Password Security Features 714Auditing Database Activity 718Managing Statement Auditing 720Managing Privilege Auditing 724Managing Object Auditing 725Using SQL Developer for Audit Management 727Purging the Audit Trail 729Managing Fine-Grained Auditing 732Implementing Unified Auditing 735Summary 745Exam Essentials 746Review Questions 747Chapter 14 Maintaining the Database and Managing Performance 753Proactive Database Maintenance 754Managing Optimizer Statistics 755Gathering Performance Statistics 775Automatic Database Diagnostic Monitoring 782The Advisory Framework 793Monitoring Server-Generated Alerts 798Understanding Automatic Diagnostic Repository 801Managing Performance 806Sources of Tuning Information 807Tuning Memory 812Automatic Shared Memory Management 813Automatic SQL Execution Memory Management 815Managing Memory Using EM Cloud Control 817Summary 819Exam Essentials 820Review Questions 821Chapter 15 Using Backup and Recovery 825Understanding and Configuring Recovery Components 827Understanding Control Files 827Understanding Checkpoints 831Understanding Redo Log Files 832Understanding Archived Redo Log (ARCHIVELOG) Files 839Understanding the Fast Recovery Area 844Performing Backups 848Understanding Backup Terminology 848Backing Up the Control File 850Backing Up the Database 851Using RMAN to Perform Backups 852Managing Backups 861Understanding Types of Database Failures 864Statement Failures 865User-Process Failures 865Network Failures 866User-Error Failures 866Instance Failures 867Media Failures 868Performing Recovery Operations 868Understanding Instance Startup 868Keeping an Instance from Failing 870Recovering from Instance Failure 870Tuning Instance Recovery 871Recovering from User Errors 873Recovering from the Loss of a Control File 881Using the Data Recovery Advisor 883Recovering from the Loss of a Redo Log File 884Recovering from the Loss of a Non-System-Critical Data File 886Recovering from the Loss of a System-Critical Data File 892Table Recovery Using RMAN 893Summary 894Exam Essentials 895Review Questions 897Chapter 16 Controlling Resources and Jobs 903Resource Management with the Resource Manager 904Functions of the Resource Manager 904Understanding Resource Manager Components 905Configuring the Database Resource Manager 911Accessing and Creating Resource Plans 916Updating and Deleting Resources 924Monitoring the Resource Manager 927Task Automation with Oracle Scheduler 930Scheduler Architecture 930Using Oracle Scheduler 934Relating Tasks with Job Chains 943Scheduling Jobs on Remote Systems 947Prioritizing Jobs with Oracle Scheduler 948Summary 951Exam Essentials 951Review Questions 952Chapter 17 Upgrading to Oracle Database 12c 957Determining the Database Upgrade Method 958Using Direct Upgrade 959Migrating to Oracle Database 12c 961Preparing for a Database Upgrade 963Running the Pre-Upgrade Information Tool 964Preparing the Oracle Home 974Reducing Upgrade Downtime 975Upgrading the Database 976Using Database Upgrade Assistant 976Performing a Manual Upgrade 987Completing the Post-Upgrade Tasks 991Migrating to Unified Auditing 994Downgrading to an Earlier Release 995Summary 997Exam Essentials 997Review Questions 999Chapter 18 Using Grid Infrastructure and Data Movement Tools 1005Tools for Moving Data 1006Migrating Data Using Data Pump 1008Data Pump Architecture 1009Using Data Pump Clients 1012Using the Data Pump Wizard 1033Upgrading an 11g R2 Database to 12cUsing Full Transportable Export 1037Loading Data with SQL*Loader 1039Specifying SQL*Loader Command-Line Parameters 1040Specifying Control File Options 1042Populating External Tables 1046Loading External Tables Using Data Pump 1046Loading External Tables Using Loader 1048Introducing Grid Infrastructure 1050Installing Oracle Grid Infrastructure 1051Managing Oracle ASM Storage 1061Using Oracle Restart 1068Summary 1076Exam Essentials 1077Review Questions 1078Appendix A Answers to Review Questions 1083Chapter 1: Introducing Oracle Database 12c RDBMS 1084Chapter 2: Introducing SQL 1085Chapter 3: Using Single-Row Functions 1087Chapter 4: Using Group Functions 1089Chapter 5: Using Joins and Subqueries 1090Chapter 6: Manipulating Data 1092Chapter 7: Creating Tables and Constraints 1094Chapter 8: Introducing Oracle Database 12c Components and Architecture 1096Chapter 9: Creating and Operating Oracle Database 12c 1098Chapter 10: Understanding Storage and Space Management 1100Chapter 11: Managing Data Concurrency and Undo 1102Chapter 12: Understanding Oracle Network Architecture 1104Chapter 13: Implementing Security and Auditing 1106Chapter 14: Maintaining the Database and Managing Performance 1108Chapter 15: Using Backup and Recovery 1110Chapter 16: Controlling Resources and Jobs 1112Chapter 17: Upgrading to Oracle Database 12c 1114Chapter 18: Using Grid Infrastructure and Data Movement Tools 1116Appendix B About the Additional Study Tools 1119Additional Study Tools 1120Sybex Test Engine 1120Electronic Flashcards 1120Bonus Author Materials 1120PDF of Glossary of Terms 1120Adobe Reader 1121System Requirements 1121Using the Study Tools 1121Troubleshooting 1121Customer Care 1122Index 1123