OCA Oracle ® Database 11g Administrator Certified Associate Study Guide Topics

Contents
Introduction  xxv
SQL Fundamentals I Assessment Test  xxxv
Administration I Assessment Test  li
Part I  Oracle Database 11g: SQL Fundamentals I  1
Chapter 1  Introducing SQL  3
SQL Fundamentals  4
SQL Tools: SQL*Plus  6
Oracle Datatypes  15
Operators and Literals  20
Writing Simple Queries  23
Using the SELECT Statement  24
Limiting Rows  28
Sorting Rows  38
Using Expressions  43
Accepting Values at Runtime  47
Using Substitution Variables  47
Saving a Variable for a Session  49
Using Positional Notation for Variables  50
Summary  51
Exam Essentials  52
Review Questions  53
Answers to Review Questions  61
Chapter 2  Using Single-Row Functions  63
Single-Row Function Fundamentals  64
Functions for NULL Handling  65
Using Single-Row Character Functions  68
Character Function Overview  68
Character Function Descriptions  70
Using Single-Row Numeric Functions  80
Numeric Function Overview  80
Numeric Function Descriptions  82
Using Single-Row Date Functions  90
Date-Format Conversion  91
Date-Function Overview  91
Date-Function Descriptions  92
Using Single-Row Conversion Functions  101
Conversion-Function Overview  102
Conversion-Function Descriptions  103
xvi  Contents
Using Other Single-Row Functions  123
Miscellaneous-Function Overview  123
Miscellaneous-Function Descriptions  124
Summary  136
Exam Essentials  137
Review Questions  138
Answers to Review Questions  144
Chapter 3  Using Group Functions  147
Group-Function Fundamentals  148
Utilizing Aggregate Functions  149
Grouping Data with GROUP BY  150
Group-Function Overview  154
Group-Function Descriptions: Part 1  156
Group-Function Descriptions: Part 2  163
Limiting Grouped Data with HAVING  176
Creating Superaggregates with CUBE and ROLLUP  177
Nesting Functions  184
Summary  187
Exam Essentials  187
Review Questions  188
Answers to Review Questions  195
Chapter 4  Using Joins and Subqueries  197
Writing Multiple-Table Queries  198
Inner Joins   199
Cartesian Joins  208
Outer Joins  210
Other Multiple-Table Queries  214
Using Set Operators  216
The UNION Operator   217
The UNION ALL Operator   218
The INTERSECT Operator   219
The MINUS Operator   219
Putting It All Together  219
Subqueries  221
Single-Row Subqueries  222
Multiple-Row Subqueries  223
Subquery Returns No Rows  226
Correlated Subqueries  227
Scalar Subqueries  228
Multiple-Column Subqueries  235
Subqueries in Other DML Statements  236
Summary  238
Exam Essentials  238
Contents  xvii
Review Questions  240
Answers to Review Questions  249
Chapter 5  Manipulating Data  251
Using DML Statements  252
Inserting Rows into a Table  253
Updating Rows in a Table  259
Deleting Rows from a Table  263
Merging Rows  265
Understanding Transaction Control  267
Savepoints and Partial Rollbacks  269
Data Visibility  271
Summary  274
Exam Essentials  274
Review Questions  276
Answers to Review Questions  285
Chapter 6  Creating Tables and Constraints  287
Database Objects Overview  288
Schema Objects  289
Built-in Datatypes  290
Character Datatypes  291
Numeric Datatypes  294
Date and Time Datatypes  295
Date Arithmetic  299
Binary Datatypes  301
Row ID Datatypes  302
Creating Tables  303
Naming Tables and Columns  303
Specifying Default Values for Columns  306
Adding Comments  308
Creating a Table from Another Table  308
Modifying Tables  310
Adding Columns  310
Modifying Columns  313
Renaming Columns  314
Dropping Columns  314
Dropping Tables  316
Renaming Tables  316
Making Tables Read-Only  317
Managing Constraints  319
Creating Constraints  319
Dropping Constraints  324
Enabling and Disabling Constraints  325
Deferring Constraint Checks  327
xviii  Contents
Summary  331
Exam Essentials  332
Review Questions  333
Answers to Review Questions  338
Chapter 7  Creating Schema Objects  341
Creating and Modifying Views  342
Using Defined Column Names  344
Creating Views with Errors  345
Creating Read-Only Views  346
Creating Constraints on Views  347
Modifying Views  347
Dropping a View  349
Using Views  350
Creating and Managing Sequences  360
Creating and Dropping Sequences  360
Using Sequences  361
Altering Sequences  365
Creating and Managing Synonyms  366
Creating and Dropping Synonyms  367
Resolving Object References  369
Creating and Managing Indexes  371
How Indexes Work  371
Using B-Tree Indexes  372
Using Bitmap Indexes  373
Dropping Indexes  373
Summary  378
Exam Essentials  379
Review Questions  380
Answers to Review Questions  386
Part II  Oracle Database 11g: Administration I  389
Chapter 8  Introducing Oracle Database 11g
Components and Architecture  391
Oracle Database Fundamentals  392
Relational Databases  393
Oracle Database 11gObjects  394
Interacting with Oracle 11g  395
Oracle 11gArchitecture  398
User Processes  400
The Oracle Instance  402
Oracle Storage Structures  415
Contents  xix
Installing Oracle 11g  424
Review the Documentation  424
Review the System Requirements  424
Plan Your Install  425
Using the Oracle Universal Installer  430
Summary  440
Exam Essentials  441
Review Questions  442
Answers to Review Questions  446
Chapter 9  Creating an Oracle 11gDatabase  449
Using DBCA to Create Oracle 11gDatabases  450
Invoking the Database Configuration Assistant  451
Configuring an Oracle Database Using the DBCA  481
Deleting an Oracle Database Using the DBCA  482
Managing Database Templates Using the DBCA  483
Working with Oracle 11gMetadata  485
Data Dictionary Views  485
Dynamic Performance Views  487
Managing Initialization-Parameter Files  488
Locating the Default Parameter File  493
Modifying Initialization-Parameter Values  493
Starting Up and Shutting Down an Oracle Instance  498
Starting Up an Oracle 11gDatabase  498
Shutting Down an Oracle 11gDatabase   503
Monitoring the Database Alert Log  506
Summary  514
Exam Essentials  515
Review Questions  516
Answers to Review Questions  520
Chapter 10  Allocating Database Storage and Creating Schema
Objects  523
Tablespaces and Data Files Overview  524
Managing Tablespaces  526
Identifying Default Tablespaces  526
Creating and Maintaining Tablespaces  527
Obtaining Tablespace Information  541
Managing Data Files  546
Performing Operations on Data Files  546
Using the Oracle Managed Files Feature  550
Querying Data-File Information  555
xx  Contents
Working with Schema Objects  557
A Little Background on Creating Tables  557
Working with Constraints  568
Working with Indexes  572
Summary  582
Exam Essentials  583
Review Questions  584
Answers to Review Questions  588
Chapter 11  Understanding Network Architecture  591
Introducing Network Configurations  592
Single-Tier Architecture  593
Two-Tier Architecture  593
n-Tier Architecture  594
An Overview of Oracle Net Features  595
Connectivity  596
Manageability  597
Scalability  598
Security  598
Accessibility  601
Configuring Oracle Net on the Server  601
Understanding the Oracle Listener  602
Managing Oracle Listeners  605
Dynamically Registering Services  623
Oracle Net Logging and Tracing on the Server  624
Configuring Oracle Net for the Client  626
Client-Side Names Resolution Options  626
The Host Naming Method  627
The Oracle Easy Connect Method  628
The Local Naming Method  629
Troubleshooting Client-Side Connection Problems  635
An Overview of Oracle Shared Server  637
Dedicated Server vs. Shared Server  638
Advantages and Disadvantages of Shared Server  640
Oracle Shared Server Infrastructure  641
PGA and SGA Changes When Using Oracle Shared Server  641
The Role of the Listener in an Oracle Shared Server
Environment  642
Configuring the Oracle Shared Server  644
Managing a Shared Server  649
Summary  652
Exam Essentials  653
Review Questions  655
Answers to Review Questions  659
Contents  xxi
Chapter 12  Implementing Security and Auditing  661
Creating and Managing User Accounts  662
Configuring Authentication  663
Assigning Tablespaces and Quotas  664
Assigning a Profile and Account Settings  666
Removing a User from the Database  668
Managing Default User Accounts  669
Granting and Revoking Privileges  670
Granting Object Privileges  670
Granting System Privileges  674
Role Privileges  681
Applying the Principle of Least Privilege  686
Controlling Resource Usage by Users  688
Implementing Password Security Features  691
Auditing Database Activity  695
Managing Statement Auditing  696
Managing Privilege Auditing  701
Managing Object Auditing  702
Purging the Audit Trail  704
Managing Fine-Grained Auditing  705
Summary  708
Exam Essentials  709
Review Questions  710
Answers to Review Questions  715
Chapter 13  Managing Data and Undo  717
Manipulating Data through SQL  718
Using the INSERT Statement  719
Using the UPDATE Statement  721
Using the MERGE Statement  722
Using the DELETE Statement  723
Identifying PL/SQL Objects  724
Working with Functions  725
Working with Procedures  726
Working with Packages  727
Working with Triggering Events and Managing Triggers  729
Using and Administering PL/SQL Programs  733
Monitoring Locks and Resolving Lock Conflicts  735
Understanding Locks and Transactions  735
Maximizing Data Concurrency  736
Detecting and Resolving Lock Conflicts  739
Leveraging Undo Management  743
Understanding Undo Segments  743
Using Undo Data  745
Monitoring, Configuring, and Administering Undo  747
xxii  Contents
Summary  755
Exam Essentials  756
Review Questions  757
Answers to Review Questions  762
Chapter 14  Maintaining the Database and
Managing Performance  765
Proactive Database Maintenance  766
Managing Optimizer Statistics  767
Gathering Performance Statistics   784
Automatic Database Diagnostic Monitoring  792
The Advisory Framework  800
Monitoring Server-Generated Alerts  802
Understanding Automatic Diagnostic Repository  805
Managing Performance  810
Sources of Tuning Information  811
Compiling Invalid and Unusable Objects  815
Tuning Memory  819
Important Performance Metrics  827
Summary  830
Exam Essentials  831
Review Questions  832
Answers to Review Questions  836
Chapter 15  Implementing Database Backups  839
Understanding and Configuring Recovery Components  840
Understanding Control Files  841
Understanding Checkpoints  846
Understanding Redo Log Files  846
Understanding Archived Redo Log (ARCHIVELOG) Files  854
Understanding the Flash Recovery Area  859
Performing Backups  862
Understanding Backup Terminology  862
Backing Up the Control File  864
Backing Up the Database  868
Using RMAN to Create Backups  869
Managing Backups  876
Summary  879
Exam Essentials  880
Review Questions  882
Answers to Review Questions  886
Contents  xxiii
Chapter 16  Recovering the Database  889
Understanding Types of Database Failure  890
Statement Failures  891
User-Process Failures  892
Network Failures  892
User-Error Failures  892
Instance Failures  893
Media Failures  894
Performing Recovery Operations  894
Understanding Instance Startup  895
Keeping an Instance from Failing  896
Recovering from Instance Failure  897
Tuning Instance Recovery  897
Recovering from User Errors  899
Recovering from Loss of a Control File  913
Using the Data Recovery Advisor  915
Recovering from the Loss of a Redo Log File  917
Recovering from the Loss of a Non–System-Critical
Data File  920
Recovering from the Loss of a System-Critical Data File  926
Summary  927
Exam Essentials  928
Review Questions  929
Answers to Review Questions  933
Chapter 17  Moving Data and Using EM Tools   935
Understanding Data Pump  936
Architecture of Data Pump  937
Using Data Pump Clients  940
Using the Data Pump Wizard  962
Loading Data with SQL*Loader  967
Specifying SQL*Loader Command-Line Parameters  968
Specifying Control File Options  970
Using EM to Load Data  973
Populating External Tables  974
Loading External Tables Using Data Pump   975
Loading External Tables Using Loader  977
Using EM Support Workbench  978
Identifying a Problem  979
Gathering Additional Diagnostic Information   981
Creating a Service Request  981
Packaging Diagnostic Data  983
Tracking and Closing the Incident  985
xxiv  Contents
Using EM to Manage Patches  986
Using the Patch Advisor  988
Viewing the Patch Cache  990
Finding the Patch Prerequisites  991
Staging a Patch  991
Applying a Patch  993
Summary  995
Exam Essentials  995
Review Questions  997
Answers to Review Questions  1001
Appendix  About the Companion CD  1003
What You’ll Find on the CD  1004
Sybex Test Engine  1004
PDF of the Book  1004
Adobe Reader  1005
Electronic Flashcards  1005
System Requirements  1005
Using the CD  1005
Troubleshooting  1006
Customer Care  1006
Glossary    1007
Index  1029