OCA: Oracle Database 12c Administrator Certified Associate Study Guide Exams 1Z0-061 and 1Z0-062

An all-in-one study guide prepares you for the updated Oracle Certified Associate certification

It's been nearly six years since Oracle updated its cornerstone database software, making the demand for a comprehensive study guide for the OCA 12c certification a top priority. This resource answers that demand. Packed with invaluable insight, chapter review questions, bonus practice exams, hundreds of electronic flashcards, and a searchable glossary of terms, this study guide prepares you for the challenging Oracle certification exams.

• Provides you with a solid understanding of restricting and sorting data
• Walks you through using conversion functions and conditional expressions
• Addresses displaying data from multiple tables, manipulating data, database maintenance, and database backups and recovery
• Explores the Oracle database architecture and discusses preparing the database environment, creating an Oracle database, and managing the Oracle instance
• Focuses on administering and implementing user security

This must-have study guide thoroughly prepares you to take the dramatically updated Oracle 12c OCA exams.

Introduction  xx

Assessment Test xxxv

Part I Oracle Database 12c: SQL Fundamentals 1

Chapter 1 Introducing Oracle Database 12c RDBMS 3

Relational Database Management Systems 4

Characteristics of a Relational Database 5

Oracle’s Implementation of RDBMS and ORDBMS 8

The Oracle Database 12c 9

Oracle Database 12c Implementations 9

Connecting to Oracle Database 13

Database Management Tools 14

Oracle Database 12c in the Cloud 19

Familiarizing SQL*Plus 19

Summary 30

Exam Essentials 31

Review Questions 32

Chapter 2 Introducing SQL 35

SQL Fundamentals 36

Oracle Datatypes 38

Operators and Literals 42

Writing Simple Queries 46

Using the SELECT Statement 46

Filtering Rows 51

Sorting Rows 62

Sorting NULLs 64

Limiting Rows 67

Using Expressions 68

Accepting Values at Runtime 72

Using Ampersand Substitution Variables 73

Saving a Variable for a Session 75

Using Positional Notation for Variables 77

Summary 78

Exam Essentials 78

Review Questions 80

Chapter 3 Using Single-Row Functions 89

Single-Row Function Fundamentals 90

Functions for NULL Handling 91

Using Single-Row Character Functions 95

Character Function Overview 95

Character Function Descriptions 96

Using Single-Row Numeric Functions 108

Numeric Function Overview 108

Numeric Function Descriptions 109

Using Single-Row Date Functions 119

Date Format Conversion 119

Date Function Overview 120

Date Function Descriptions 121

Using Single-Row Conversion Functions 131

Conversion Function Overview 131

Conversion Function Descriptions 133

Using Other Single-Row Functions 154

Miscellaneous Function Overview 154

Miscellaneous Function Descriptions 155

Summary 167

Exam Essentials 168

Review Questions 169

Chapter 4 Using Group Functions 175

Group Function Fundamentals 176

Utilizing Aggregate Functions 177

Grouping Data with GROUP BY 178

Group Function Overview 182

Group Function Descriptions: Part 1 184

Group Function Descriptions: Part 2 194

Limiting Grouped Data with HAVING 207

Creating Superaggregates with CUBE and ROLLUP 208

Nesting Functions 215

Summary 218

Exam Essentials 218

Review Questions 219

Chapter 5 Using Joins and Subqueries 227

Writing Multiple-Table Queries 228

Inner Joins 229

Cartesian Joins 239

Outer Joins 240

Other Multiple-Table Queries 245

Using Set Operators 247

The UNION Operator 248

The UNION ALL Operator 249

The INTERSECT Operator 250

The MINUS Operator 250

Putting It All Together 250

Using Subqueries 252

Single-Row Subqueries 253

Multiple-Row Subqueries 254

Subquery Returns No Rows 256

Correlated Subqueries 258

Scalar Subqueries 259

Multiple-Column Subqueries 267

Subqueries in Other DML Statements 268

Summary 269

Exam Essentials 270

Review Questions 271

Chapter 6 Manipulating Data 281

Using DML Statements 282

Inserting Rows into a Table 283

Updating Rows in a Table 289

Deleting Rows from a Table 294

Merging Rows 297

Understanding Transaction Control 299

Savepoints and Partial Rollbacks 302

Data Visibility 304

Summary 306

Exam Essentials 307

Review Questions 308

Chapter 7 Creating Tables and Constraints 319

Database Objects Overview 320

Schema Objects 322

Using Sequences 324

Built-in Datatypes 325

Character Datatypes 325

Numeric Datatypes 329

Date and Time Datatypes 330

Date Arithmetic 334

Binary Datatypes 336

Row ID Datatypes 337

Creating Tables 338

Naming Tables and Columns 339

Specifying Default Values for Columns 341

Adding Comments 348

Creating a Table from Another Table 348

Modifying Tables 350

Adding Columns 350

Modifying Columns 353

Renaming Columns 354

Dropping Columns 354

Hiding Columns from Table 356

Dropping Tables 357

Renaming Tables 357

Making Tables Read-Only 358

Managing Constraints 361

Creating Constraints 362

Dropping Constraints 367

Enabling and Disabling Constraints 367

Deferring Constraint Checks 369

Summary 373

Exam Essentials 374

Review Questions 375

Part II Oracle Database 12c: Installation and Administration 381

Chapter 8 Introducing Oracle Database 12c Components and Architecture 383

Oracle Database Fundamentals 385

Relational Databases 385

Oracle Database 12c Objects 386

Interacting with Oracle Database 12c 388

Oracle Database 12c Architecture 391

User and Server Processes 394

The Oracle Instance 396

Oracle Storage Structures 410

Summary 424

Exam Essentials 425

Review Questions 426

Chapter 9 Creating and Operating Oracle Database 12c 431

Oracle Database 12c Software Installation 432

Planning the Oracle Database 12c Software Install 433

Using the Oracle Universal Installer 440

Using DBCA to Create an Oracle 12c Database 447

Invoking the Database Configuration Assistant 447

Configuring an Oracle Database Using the DBCA 472

Deleting an Oracle Database Using the DBCA 472

Managing Database Templates Using the DBCA 473

Working with Oracle Database Metadata 475

Data Dictionary Views 476

Dynamic Performance Views 477

Managing Initialization-Parameter Files 479

Locating the Default Parameter File 484

Modifying Initialization-Parameter Values 485

Starting Up and Shutting Down an Oracle Instance 490

Starting Up an Oracle Database 12c Instance 491

Shutting Down an Oracle Database 12c Instance 495

Monitoring the Database Alert Log 497

Summary 503

Exam Essentials 504

Review Questions 506

Chapter 10 Understanding Storage and Space Management 511

Understanding the Physical and Logical Storage 512

Contents of a Data Block 514

Managing Tablespaces 516

Identifying Default Tablespaces 516

Creating and Maintaining Tablespaces 516

Obtaining Tablespace Information 531

Managing Data Files 534

Performing Operations on Data Files 535

Using the Oracle Managed Files Feature 539

Querying Data File Information 544

Managing Space 547

Automatic Space Management Features 547

Monitoring Tablespace Free Space 551

Using Segment Advisor 554

Avoiding Out-of-Space Errors 559

Summary 562

Exam Essentials 563

Review Questions 565

Chapter 11 Managing Data Concurrency and Undo 569

Managing Data Changes Using DML 570

Understanding “Change” 571

Differentiating Undo and Redo 572

Configuring and Monitoring Undo 576

Managing Data Concurrency 584

Understanding Locks and Transactions 585

Maximizing Data Concurrency 587

Detecting and Resolving Lock Conflicts 590

Summary 595

Exam Essentials 595

Review Questions 597

Chapter 12 Understanding Oracle Network Architecture 603

Introducing Network Configurations 604

Single-Tier Architecture 605

Two-Tier Architecture 605

n-Tier Architecture 606

An Overview of Oracle Net Features 607

Connectivity 608

Manageability 608

Scalability 609

Security 611

Accessibility 613

Configuring Oracle Net on the Server 613

Understanding the Oracle Listener 614

Managing Oracle Listeners 618

Dynamically Registering Services 634

Oracle Net Logging and Tracing on the Server 635

Configuring Oracle Net for the Client 637

Client-Side Names Resolution Options 637

The Host Naming Method 638

The Oracle Easy Connect Method 639

The Local Naming Method 641

Troubleshooting Client-Side Connection Problems 646

An Overview of Oracle Shared Server 648

Dedicated Server vs. Shared Server 648

Advantages and Disadvantages of Shared Server 650

Oracle Shared Server Infrastructure 651

Configuring the Oracle Shared Server 653

Managing a Shared Server 658

Understanding Database Resident Connection Pooling 661

Configuring DRCP 662

Comparing Connection Architectures 663

Communicating Between Databases 666

Introduction to Database Links 666

Creating Database Links 667

Summary 669

Exam Essentials 670

Review Questions 672

Chapter 13 Implementing Security and Auditing 677

Creating and Managing User Accounts 678

Configuring Authentication 679

Assigning Tablespaces and Quotas 681

Assigning a Profile and Account Settings 684

Removing a User from the Database 686

Managing Default User Accounts 687

Granting and Revoking Privileges 687

Granting Object Privileges 688

Granting System Privileges 692

Role Privileges 701

Applying the Principle of Least Privilege 705

Controlling Resource Usage by Users 711

Implementing Password Security Features 714

Auditing Database Activity 718

Managing Statement Auditing 720

Managing Privilege Auditing 724

Managing Object Auditing 725

Using SQL Developer for Audit Management 727

Purging the Audit Trail 729

Managing Fine-Grained Auditing 732

Implementing Unified Auditing 735

Summary 745

Exam Essentials 746

Review Questions 747

Chapter 14 Maintaining the Database and Managing Performance 753

Proactive Database Maintenance 754

Managing Optimizer Statistics 755

Gathering Performance Statistics 775

Automatic Database Diagnostic Monitoring 782

The Advisory Framework 793

Monitoring Server-Generated Alerts 798

Understanding Automatic Diagnostic Repository 801

Managing Performance 806

Sources of Tuning Information 807

Tuning Memory 812

Automatic Shared Memory Management 813

Automatic SQL Execution Memory Management 815

Managing Memory Using EM Cloud Control 817

Summary 819

Exam Essentials 820

Review Questions 821

Chapter 15 Using Backup and Recovery 825

Understanding and Configuring Recovery Components 827

Understanding Control Files 827

Understanding Checkpoints 831

Understanding Redo Log Files 832

Understanding Archived Redo Log (ARCHIVELOG) Files 839

Understanding the Fast Recovery Area 844

Performing Backups 848

Understanding Backup Terminology 848

Backing Up the Control File 850

Backing Up the Database 851

Using RMAN to Perform Backups 852

Managing Backups 861

Understanding Types of Database Failures 864

Statement Failures 865

User-Process Failures 865

Network Failures 866

User-Error Failures 866

Instance Failures 867

Media Failures 868

Performing Recovery Operations 868

Understanding Instance Startup 868

Keeping an Instance from Failing 870

Recovering from Instance Failure 870

Tuning Instance Recovery 871

Recovering from User Errors 873

Recovering from the Loss of a Control File 881

Using the Data Recovery Advisor 883

Recovering from the Loss of a Redo Log File 884

Recovering from the Loss of a Non-System-Critical Data File 886

Recovering from the Loss of a System-Critical Data File 892

Table Recovery Using RMAN 893

Summary 894

Exam Essentials 895

Review Questions 897

Chapter 16 Controlling Resources and Jobs 903

Resource Management with the Resource Manager 904

Functions of the Resource Manager 904

Understanding Resource Manager Components 905

Configuring the Database Resource Manager 911

Accessing and Creating Resource Plans 916

Updating and Deleting Resources 924

Monitoring the Resource Manager 927

Task Automation with Oracle Scheduler 930

Scheduler Architecture 930

Using Oracle Scheduler 934

Relating Tasks with Job Chains 943

Scheduling Jobs on Remote Systems 947

Prioritizing Jobs with Oracle Scheduler 948

Summary 951

Exam Essentials 951

Review Questions 952

Chapter 17 Upgrading to Oracle Database 12c 957

Determining the Database Upgrade Method 958

Using Direct Upgrade 959

Migrating to Oracle Database 12c 961

Preparing for a Database Upgrade 963

Running the Pre-Upgrade Information Tool 964

Preparing the Oracle Home 974

Reducing Upgrade Downtime 975

Upgrading the Database 976

Using Database Upgrade Assistant 976

Performing a Manual Upgrade 987

Completing the Post-Upgrade Tasks 991

Migrating to Unified Auditing 994

Downgrading to an Earlier Release 995

Summary 997

Exam Essentials 997

Review Questions 999

Chapter 18 Using Grid Infrastructure and Data Movement Tools 1005

Tools for Moving Data 1006

Migrating Data Using Data Pump 1008

Data Pump Architecture 1009

Using Data Pump Clients 1012

Using the Data Pump Wizard 1033

Upgrading an 11g R2 Database to 12c

Using Full Transportable Export 1037

Loading Data with SQL*Loader 1039

Specifying SQL*Loader Command-Line Parameters 1040

Specifying Control File Options 1042

Populating External Tables 1046

Loading External Tables Using Data Pump 1046

Loading External Tables Using Loader 1048

Introducing Grid Infrastructure 1050

Installing Oracle Grid Infrastructure 1051

Managing Oracle ASM Storage 1061

Using Oracle Restart 1068

Summary 1076

Exam Essentials 1077

Review Questions 1078

Appendix A Answers to Review Questions 1083

Chapter 1: Introducing Oracle Database 12c RDBMS 1084

Chapter 2: Introducing SQL 1085

Chapter 3: Using Single-Row Functions 1087

Chapter 4: Using Group Functions 1089

Chapter 5: Using Joins and Subqueries 1090

Chapter 6: Manipulating Data 1092

Chapter 7: Creating Tables and Constraints 1094

Chapter 8: Introducing Oracle Database 12c Components and Architecture 1096

Chapter 9: Creating and Operating Oracle Database 12c 1098

Chapter 10: Understanding Storage and Space Management 1100

Chapter 11: Managing Data Concurrency and Undo 1102

Chapter 12: Understanding Oracle Network Architecture 1104

Chapter 13: Implementing Security and Auditing 1106

Chapter 14: Maintaining the Database and Managing Performance 1108

Chapter 15: Using Backup and Recovery 1110

Chapter 16: Controlling Resources and Jobs 1112

Chapter 17: Upgrading to Oracle Database 12c 1114

Chapter 18: Using Grid Infrastructure and Data Movement Tools 1116

Appendix B About the Additional Study Tools 1119

Additional Study Tools 1120

Sybex Test Engine 1120

Electronic Flashcards 1120

Bonus Author Materials 1120

PDF of Glossary of Terms 1120

Adobe Reader 1121

System Requirements 1121

Using the Study Tools 1121

Troubleshooting 1121

Customer Care 1122

Index 1123

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.