OCA: Oracle Database 12c Administrator Certified Associate Study Guide: Exams 1Z0-061 and 1Z0-062 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 Contents xv 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 Ma.