book

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

Name: OCA Oracle® Database 11g Administrator Certified Associate: Study Guide
Author: Biju Thomas
ISBN: 9780470395127

by Biju Thomas

April 2009

Intermediate to advanced

1152 pages

25h 4m

English

Sybex

Read now

Unlock full access

Copyright
Acknowledgments
About the Author
Introduction
Why Become Oracle Certified?Oracle CertificationsOracle Database 11g Administrator Certified AssociateOracle Database 11g Administrator Certified ProfessionalOracle Database 11g Administrator Certified MasterMore Information and ResourcesOCA/OCP Study GuidesOracle Exam RequirementsOCA SQL (1Z0-051) RequirementsOCA Admin I (1Z0-052) RequirementsTips for Taking the OCA ExamsWhat Is Covered in This BookPart I: Oracle Database 11g SQL Fundamentals IPart II: Oracle Database 11g Administration IHow to Use This BookHow to Contact the AuthorSQL Fundamentals I Assessment TestAnswers to SQL Fundamentals I Assessment TestAdministration I Assessment TestAnswers to Administration I Assessment Test
I. Oracle Database 11g: SQL Fundamentals I
1. Introducing SQL
1.1. SQL Fundamentals1.1.1. SQL Tools: SQL*Plus1.1.1.1. Entering SQL Statements1.1.1.2. Entering SQL*Plus Commands1.1.1.3. Getting Information with the DESCRIBE Command1.1.1.4. Editing the SQL Buffer1.1.1.4.1. LIST1.1.1.4.2. APPEND1.1.1.4.3. CHANGE1.1.1.4.4. INPUT1.1.1.4.5. DEL1.1.1.4.6. CLEAR BUFFER1.1.1.5. Using Script Files1.1.1.5.1. Saving Query Results to a File1.1.1.5.2. Adding Comments to a Script File1.1.2. Oracle Datatypes1.1.2.1. CHAR(<size>)1.1.2.2. VARCHAR2(<size>)1.1.2.3. NUMBER (<p>, <s>)1.1.2.4. DATE1.1.2.5. TIMESTAMP [<precision>]1.1.3. Operators and Literals1.1.3.1. Arithmetic Operators1.1.3.2. Concatenation Operator1.1.3.3. Operator Precedence1.1.3.4. Literals1.1.3.4.1. Text Literals1.1.3.4.2. Numeric Literals1.1.3.4.3. Datetime Literals1.1.3.4.4. Interval Literals1.2. Writing Simple Queries1.2.1. Using the SELECT Statement1.2.1.1. Column Alias Names1.2.1.2. Ensuring Uniqueness1.2.1.3. The DUAL Table1.2.2. Limiting Rows1.2.2.1. Comparison Operators1.2.2.1.1. = (Equality)1.2.2.1.2. !=, <>, or ⁁= (Inequality)1.2.2.1.3. < (Less Than)1.2.2.1.4. > (Greater Than)1.2.2.1.5. <= (Less Than or Equal to)1.2.2.1.6. >= (Greater Than or Equal to)1.2.2.1.7. ANY or SOME1.2.2.1.8. ALL1.2.2.2. Logical Operators1.2.2.2.1. NOT1.2.2.2.2. AND1.2.2.2.3. OR1.2.2.2.4. Logical Operator Truth Tables1.2.2.3. Other Operators1.2.2.3.1. IN and NOT IN1.2.2.3.2. BETWEEN1.2.2.3.3. EXISTS1.2.2.3.4. IS NULL and IS NOT NULL1.2.2.3.5. LIKE1.2.3. Sorting Rows1.2.3.1. Sorting NULLs1.2.4. Using Expressions1.2.4.1. The CASE Expression1.3. Accepting Values at Runtime1.3.1. Using Substitution Variables1.3.2. Saving a Variable for a Session1.3.3. Using Positional Notation for Variables1.4. Summary1.5. Exam Essentials1.6. Review Questions1.7. Answers to Review Questions
2. Using Single-Row Functions
2.1. Single-Row Function Fundamentals2.1.1. Functions for NULL Handling2.1.1.1. NVL2.1.1.2. NVL22.1.1.3. COALESCE2.2. Using Single-Row Character Functions2.2.1. Character Function Overview2.2.2. Character Function Descriptions2.2.2.1. ASCII2.2.2.2. CHR2.2.2.3. CONCAT2.2.2.4. INITCAP2.2.2.5. INSTR2.2.2.6. INSTRB2.2.2.7. LENGTH2.2.2.8. LENGTHB2.2.2.9. LOWER2.2.2.10. LPAD2.2.2.11. LTRIM2.2.2.12. REPLACE2.2.2.13. RPAD2.2.2.14. RTRIM2.2.2.15. SOUNDEX2.2.2.16. SUBSTR2.2.2.17. SUBSTRB2.2.2.18. TRANSLATE2.2.2.19. TRIM2.2.2.20. UPPER2.3. Using Single-Row Numeric Functions2.3.1. Numeric Function Overview2.3.2. Numeric Function Descriptions2.3.2.1. ABS2.3.2.2. ACOS2.3.2.3. ASIN2.3.2.4. ATAN2.3.2.5. ATAN22.3.2.6. BITAND2.3.2.7. CEIL2.3.2.8. COS2.3.2.9. COSH2.3.2.10. EXP2.3.2.11. FLOOR2.3.2.12. LN2.3.2.13. LOG2.3.2.14. MOD2.3.2.15. NANVL2.3.2.16. POWER2.3.2.17. REMAINDER2.3.2.18. ROUND2.3.2.19. SIGN2.3.2.20. SIN2.3.2.21. SINH2.3.2.22. SQRT2.3.2.23. TAN2.3.2.24. TANH2.3.2.25. TRUNC2.3.2.26. WIDTH_BUCKET2.4. Using Single-Row Date Functions2.4.1. Date-Format Conversion2.4.2. Date-Function Overview2.4.3. Date-Function Descriptions2.4.3.1. SYSDATE2.4.3.2. SYSTIMESTAMP2.4.3.3. LOCALTIMESTAMP2.4.3.4. ADD_MONTHS2.4.3.5. CURRENT_DATE2.4.3.6. CURRENT_TIMESTAMP2.4.3.7. DBTIMEZONE2.4.3.8. EXTRACT2.4.3.9. FROM_TZ2.4.3.10. LAST_DAY2.4.3.11. MONTHS_BETWEEN2.4.3.12. NEW_TIME2.4.3.13. NEXT_DAY2.4.3.14. ROUND2.4.3.15. SESSIONTIMEZONE2.4.3.16. SYS_EXTRACT_UTC2.4.3.17. TRUNC2.4.3.18. TZ_OFFSET2.5. Using Single-Row Conversion Functions2.5.1. Conversion-Function Overview2.5.2. Conversion-Function Descriptions2.5.2.1. ASCIISTR2.5.2.2. BIN_TO_NUM2.5.2.3. CAST2.5.2.4. CHARTOROWID2.5.2.5. COMPOSE2.5.2.6. CONVERT2.5.2.7. DECOMPOSE2.5.2.8. HEXTORAW2.5.2.9. NUMTODSINTERVAL2.5.2.10. NUMTOYMINTERVAL2.5.2.11. RAWTOHEX2.5.2.12. ROWIDTOCHAR2.5.2.13. SCN_TO_TIMESTAMP2.5.2.14. TIMESTAMP_TO_SCN2.5.2.15. TO_BINARY_DOUBLE2.5.2.16. TO_BINARY_FLOAT2.5.2.17. TO_CHAR2.5.2.17.1. Date Conversion2.5.2.17.2. Number Conversion2.5.2.18. TO_CLOB2.5.2.19. TO_DATE2.5.2.20. TO_DSINTERVAL2.5.2.21. TO_LOB2.5.2.22. TO_MULTI_BYTE2.5.2.23. TO_NUMBER2.5.2.24. TO_SINGLE_BYTE2.5.2.25. TO_TIMESTAMP2.5.2.26. TO_TIMESTAMP_TZ2.5.2.27. TO_YMINTERVAL2.5.2.28. UNISTR2.6. Using Other Single-Row Functions2.6.1. Miscellaneous-Function Overview2.6.2. Miscellaneous-Function Descriptions2.6.2.1. BFILENAME2.6.2.2. DECODE2.6.2.3. DUMP2.6.2.4. EMPTY_BLOB2.6.2.5. EMPTY_CLOB2.6.2.6. GREATEST2.6.2.7. LEAST2.6.2.8. NULLIF2.6.2.9. ORA_HASH2.6.2.10. SYS_CONTEXT2.6.2.11. SYS_GUID2.6.2.12. UID2.6.2.13. USER2.6.2.14. USERENV2.6.2.15. VSIZE2.7. Summary2.8. Exam Essentials2.9. Review Questions2.10. Answers to Review Questions
3. Using Group Functions
3.1. Group-Function Fundamentals3.2. Utilizing Aggregate Functions3.2.1. Grouping Data with GROUP BY3.2.2. Group-Function Overview3.2.3. Group-Function Descriptions: Part 13.2.3.1. AVG3.2.3.2. COUNT3.2.3.3. MAX3.2.3.4. MIN3.2.3.5. SUM3.2.3.6. MEDIAN3.2.3.7. STDDEV3.2.3.8. VARIANCE3.2.4. Group-Function Descriptions: Part 23.2.4.1. CORR3.2.4.2. COVAR_POP3.2.4.3. COVAR_SAMP3.2.4.4. CUME_DIST3.2.4.5. DENSE_RANK3.2.4.6. FIRST3.2.4.7. GROUP_ID3.2.4.8. GROUPING3.2.4.9. GROUPING_ID3.2.4.10. KEEP3.2.4.11. LAST3.2.4.12. PERCENT_RANK3.2.4.13. PERCENTILE_CONT3.2.4.14. PERCENTILE_DISC3.2.4.15. RANK3.2.4.16. STDDEV_POP3.2.4.17. STDDEV_SAMP3.2.4.18. VAR_POP3.2.4.19. VAR_SAMP3.2.5. Limiting Grouped Data with HAVING3.2.6. Creating Superaggregates with CUBE and ROLLUP3.2.6.1. Using ROLLUP3.2.6.2. Using CUBE3.2.7. Nesting Functions3.2.7.1. Nesting Single-Row Functions with Group Functions3.2.7.2. Nesting Group Functions3.3. Summary3.4. Exam Essentials3.5. Review Questions3.6. Answers to Review Questions
4. Using Joins and Subqueries
4.1. Writing Multiple-Table Queries4.1.1. Inner Joins4.1.1.1. Simple Inner Joins4.1.1.2. Complex Inner Joins4.1.1.3. Using Table Aliases4.1.1.4. Using the ANSI Syntax4.1.1.4.1. NATURAL JOIN4.1.1.4.2. JOIN...USING4.1.1.4.3. JOIN...ON4.1.1.5. Multitable Joins4.1.2. Cartesian Joins4.1.2.1. Using the ANSI Syntax4.1.3. Outer Joins4.1.3.1. Using the ANSI Syntax4.1.3.1.1. Left Outer Joins4.1.3.1.2. Right Outer Joins4.1.3.1.3. Full Outer Joins4.1.4. Other Multiple-Table Queries4.1.4.1. Self-Joins4.1.4.2. Nonequality Joins4.2. Using Set Operators4.2.1. The UNION Operator4.2.2. The UNION ALL Operator4.2.3. The INTERSECT Operator4.2.4. The MINUS Operator4.2.5. Putting It All Together4.3. Subqueries4.3.1. Single-Row Subqueries4.3.2. Multiple-Row Subqueries4.3.3. Subquery Returns No Rows4.3.4. Correlated Subqueries4.3.5. Scalar Subqueries4.3.5.1. A Scalar Subquery in a CASE Expression4.3.5.2. A Scalar Subquery in a SELECT Clause4.3.5.3. A Scalar Subquery in SELECT and WHERE Clauses4.3.5.4. A Scalar Subquery in an ORDER BY Clause4.3.6. Multiple-Column Subqueries4.3.7. Subqueries in Other DML Statements4.4. Summary4.5. Exam Essentials4.6. Review Questions4.7. Answers to Review Questions
5. Manipulating Data
5.1. Using DML Statements5.1.1. Inserting Rows into a Table5.1.1.1. Inserting Rows from a Subquery5.1.1.2. Inserting Rows into Multiple Tables5.1.2. Updating Rows in a Table5.1.2.1. Updating Rows Using a Subquery5.1.3. Deleting Rows from a Table5.1.3.1. Truncating a Table5.1.3.1.1. TRUNCATE vs. DELETE5.1.4. Merging Rows5.2. Understanding Transaction Control5.2.1. Savepoints and Partial Rollbacks5.2.2. Data Visibility5.2.2.1. Consistency and Transactions5.2.2.2. Locking Mechanism5.3. Summary5.4. Exam Essentials5.5. Review Questions5.6. Answers to Review Questions

6. Creating Tables and Constraints
6.1. Database Objects Overview6.1.1. Schema Objects6.2. Built-in Datatypes6.2.1. Character Datatypes6.2.1.1. CHAR6.2.1.2. NCHAR6.2.1.3. VARCHAR2 or VARCHAR6.2.1.4. NVARCHAR26.2.1.5. CLOB6.2.1.6. NCLOB6.2.1.7. LONG6.2.2. Numeric Datatypes6.2.2.1. NUMBER6.2.2.2. BINARY_FLOAT6.2.2.3. BINARY_DOUBLE6.2.2.4. FLOAT6.2.3. Date and Time Datatypes6.2.3.1. DATE6.2.3.2. TIMESTAMP6.2.3.3. TIMESTAMP WITH TIME ZONE6.2.3.4. TIMESTAMP WITH LOCAL TIME ZONE6.2.3.5. INTERVAL YEAR TO MONTH6.2.3.6. INTERVAL DAY TO SECOND6.2.4. Date Arithmetic6.2.5. Binary Datatypes6.2.5.1. RAW6.2.5.2. LONG RAW6.2.5.3. BLOB6.2.5.4. BFILE6.2.6. Row ID Datatypes6.2.6.1. ROWID6.2.6.2. UROWID6.3. Creating Tables6.3.1. Naming Tables and Columns6.3.2. Specifying Default Values for Columns6.3.3. Adding Comments6.3.4. Creating a Table from Another Table6.4. Modifying Tables6.4.1. Adding Columns6.4.2. Modifying Columns6.4.3. Renaming Columns6.4.4. Dropping Columns6.4.5. Dropping Tables6.4.6. Renaming Tables6.4.7. Making Tables Read-Only6.5. Managing Constraints6.5.1. Creating Constraints6.5.1.1. NOT NULL Constraint6.5.1.2. Check Constraints6.5.1.3. Unique Constraints6.5.1.4. Primary Key Constraints6.5.1.5. Foreign Key Constraints6.5.1.6. Disabled Constraints6.5.2. Dropping Constraints6.5.3. Enabling and Disabling Constraints6.5.3.1. Validated Constraints6.5.4. Deferring Constraint Checks6.6. Summary6.7. Exam Essentials6.8. Review Questions6.9. Answers to Review Questions
7. Creating Schema Objects
7.1. Creating and Modifying Views7.1.1. Using Defined Column Names7.1.2. Creating Views with Errors7.1.3. Creating Read-Only Views7.1.4. Creating Constraints on Views7.1.5. Modifying Views7.1.5.1. Changing a View's Definition7.1.5.2. Recompiling a View7.1.6. Dropping a View7.1.7. Using Views7.1.7.1. Using Views in Queries7.1.7.2. Inserting, Updating, and Deleting Data through Views7.1.7.3. Using Join Views7.1.7.4. Viewing Allowable DML Operations7.1.7.5. Using Inline Views7.1.7.6. Performing Top-n Analysis7.2. Creating and Managing Sequences7.2.1. Creating and Dropping Sequences7.2.2. Using Sequences7.2.2.1. Sequence Initialization7.2.2.2. Missing Sequence Values7.2.2.3. Maximum and Minimum Values7.2.3. Altering Sequences7.3. Creating and Managing Synonyms7.3.1. Creating and Dropping Synonyms7.3.1.1. Public Synonyms7.3.1.2. Private Synonyms7.3.2. Resolving Object References7.4. Creating and Managing Indexes7.4.1. How Indexes Work7.4.2. Using B-Tree Indexes7.4.3. Using Bitmap Indexes7.4.4. Dropping Indexes7.5. Summary7.6. Exam Essentials7.7. Review Questions7.8. Answers to Review Questions
II. Oracle Database 11g: Administration I
8. Introducing Oracle Database 11g Components and Architecture
8.1. Oracle Database Fundamentals8.2. Relational Databases8.3. Oracle Database 11g Objects8.4. Interacting with Oracle 11g8.4.1. SQL*Plus8.4.2. SQL Developer8.4.3. Enterprise Manager Database Control8.5. Oracle 11g Architecture8.6. User Processes8.7. The Oracle Instance8.7.1. Oracle Memory Structures8.7.1.1. Database Buffer Cache8.7.1.2. Redo Log Buffer8.7.1.3. Shared Pool8.7.1.4. Large Pool8.7.1.5. Java Pool8.7.1.6. Streams Pool8.7.2. Oracle Processes Structures8.7.2.1. Database Writer (DBWn)8.7.2.2. Checkpoint (CKPT)8.7.2.3. Log Writer (LGWR)8.7.2.4. Process Monitor (PMON)8.7.2.5. System Monitor (SMON)8.8. Oracle Storage Structures8.8.1. Control Files8.8.2. Data Files8.8.3. Redo Log Files8.8.4. The Logical Structure8.9. Installing Oracle 11g8.10. Review the Documentation8.11. Review the System Requirements8.12. Plan Your Install8.12.1. Creating the Oracle User Account8.12.2. Naming Volumes and Mount Points8.12.3. Creating OFA Directory Paths8.13. Using the Oracle Universal Installer8.13.1. Mounting the CD and Starting the OUI8.13.2. Performing Preinstallation Checks8.13.3. Responding to OUI Prompts8.13.4. Selecting Products to Install8.13.5. Copying and Compiling Files8.13.6. Performing Postinstall Tasks8.14. Summary8.15. Exam Essentials8.16. Review Questions8.17. Answers to Review Questions
9. Creating an Oracle 11g Database
9.1. Using DBCA to Create Oracle 11g Databases9.1.1. Invoking the Database Configuration Assistant9.1.1.1. Database Templates9.1.1.2. Database Identification9.1.1.3. Management Options9.1.1.4. Database Credentials9.1.1.5. Storage Options9.1.1.5.1. File System Storage9.1.1.5.2. ASM Storage9.1.1.5.3. Raw Devices9.1.1.6. Database File Locations9.1.1.6.1. Use Database File Locations from Template9.1.1.6.2. Use Common Location for all Database Files9.1.1.6.3. Use Oracle-Managed Files9.1.1.7. Recovery Configuration9.1.1.7.1. Flash Recovery9.1.1.7.2. Enable Archive Logging9.1.1.8. Database Content9.1.1.9. Sample Schemas and Custom Scripts9.1.1.10. Initialization Parameters9.1.1.10.1. The Memory Tab9.1.1.10.2. The Sizing Tab9.1.1.10.3. The Character Sets Tab9.1.1.10.4. The Connection Mode Tab9.1.1.11. Security and Maintenance Settings9.1.1.12. Database Storage9.1.1.13. Creation Options9.1.2. Configuring an Oracle Database Using the DBCA9.1.3. Deleting an Oracle Database Using the DBCA9.1.4. Managing Database Templates Using the DBCA9.1.4.1. Creating Template Definitions Using the DBCA9.1.4.2. Deleting Template Definitions Using the DBCA9.2. Working with Oracle 11g Metadata9.2.1. Data Dictionary Views9.2.2. Dynamic Performance Views9.3. Managing Initialization-Parameter Files9.3.1. Locating the Default Parameter File9.3.2. Modifying Initialization-Parameter Values9.3.2.1. Using EM Database Control9.3.2.2. Using SQL*Plus9.3.2.2.1. V$PARAMETER9.3.2.2.2. V$SPPARAMETER9.3.2.2.3. V$PARAMETER vs. V$SPPARAMETER9.4. Starting Up and Shutting Down an Oracle Instance9.4.1. Starting Up an Oracle 11g Database9.4.1.1. Starting Up Oracle Using EM Database Control9.4.1.2. Starting Oracle Using SQL*Plus9.4.1.3. Changing Database Startup States Using SQL9.4.2. Shutting Down an Oracle 11g Database9.4.2.1. Shutting Down Oracle Using EM Database Control9.4.2.2. Shutting Down Oracle Using SQL*Plus9.5. Monitoring the Database Alert Log9.6. Summary9.7. Exam Essentials9.8. Review Questions9.9. Answers to Review Questions
10. Allocating Database Storage and Creating Schema Objects
10.1. Tablespaces and Data Files Overview10.2. Managing Tablespaces10.2.1. Identifying Default Tablespaces10.2.2. Creating and Maintaining Tablespaces10.2.2.1. Creating Bigfile and Smallfile Tablespaces10.2.2.2. Working with Oracle Managed File Tablespaces10.2.2.3. Choosing Extent Management10.2.2.4. Choosing Segment Space Management10.2.2.5. Choosing Other Tablespace Options10.2.2.6. Using Nonstandard Block Sizes10.2.2.7. Creating Temporary Tablespaces10.2.2.8. Creating Undo Tablespaces10.2.2.9. Removing Tablespaces10.2.2.10. Modifying Tablespaces10.2.2.10.1. Changing Storage Defaults10.2.2.10.2. Adding a Data File to a Tablespace10.2.2.10.3. Taking a Tablespace Offline or Online10.2.2.10.4. Making a Tablespace Read-Only10.2.2.10.5. Putting a Tablespace in Backup Mode10.2.3. Obtaining Tablespace Information10.2.3.1. Obtaining Tablespace Information Using SQL*Plus10.2.3.2. Obtaining Tablespace Information Using the EM Database Control10.3. Managing Data Files10.3.1. Performing Operations on Data Files10.3.1.1. Sizing Files10.3.1.2. Making Files Online and Offline10.3.1.3. Renaming Files10.3.1.3.1. Renaming a Data File10.3.1.3.2. Relocating a Tablespace10.3.1.3.3. Renaming or Relocating Files Belonging to Multiple Tablespaces10.3.1.3.4. Moving Read-Only Tablespaces10.3.2. Using the Oracle Managed Files Feature10.3.2.1. Enabling the Oracle Managed Files Feature10.3.2.2. Creating Databases Using the OMF Feature10.3.2.3. Creating Tablespaces Using the OMF Feature10.3.2.4. Overriding the Default File Size10.3.3. Querying Data File Information10.3.3.1. V$DATAFILE10.3.3.2. V$TEMPFILE10.3.3.3. DBA_DATA_FILES10.3.3.4. DBA_TEMP_FILES10.4. Working with Schema Objects10.4.1. A Little Background on Creating Tables10.4.1.1. Creating a Table10.4.1.1.1. Storing LOB Structures10.4.1.1.2. Using Other Create Clauses10.4.1.2. Creating a Table Using a Subquery10.4.1.3. Creating a Temporary Table10.4.1.4. Reorganizing Tables10.4.1.5. Truncating a Table10.4.1.6. Using Namespaces10.4.2. Working with Constraints10.4.2.1. NOT NULL10.4.2.2. CHECK10.4.2.3. UNIQUE10.4.2.4. PRIMARY KEY10.4.2.5. FOREIGN KEY10.4.2.6. Deferred Constraint Checking10.4.3. Working with Indexes10.4.3.1. Specifying Storage10.4.3.2. Using Other Create Clauses10.4.3.3. Reverse Key Indexes10.4.3.4. Function-Based Indexes10.4.3.5. Index-Organized Tables10.5. Summary10.6. Exam Essentials10.7. Review Questions10.8. Answers to Review Questions
11. Understanding Network Architecture
11.1. Introducing Network Configurations11.1.1. Single-Tier Architecture11.1.2. Two-Tier Architecture11.1.3. n-Tier Architecture11.2. An Overview of Oracle Net Features11.2.1. Connectivity11.2.2. Manageability11.2.2.1. Web Applications11.2.2.2. Location Transparency11.2.2.3. Directory Naming11.2.3. Scalability11.2.3.1. Oracle Shared Server11.2.3.2. Connection Manager11.2.4. Security11.2.4.1. Advanced Security11.2.4.2. Firewall Support11.2.5. Accessibility11.2.5.1. Heterogeneous Services11.2.5.2. External Procedures11.3. Configuring Oracle Net on the Server11.3.1. Understanding the Oracle Listener11.3.1.1. How Do Listeners Respond to Connection Requests?11.3.1.1.1. Dedicated Connections: Direct Handoff Method11.3.1.1.2. Dedicated Connections: Redirect Method11.3.1.1.3. Oracle Shared Server: Direct Handoff Method11.3.1.1.4. Oracle Shared Server: Redirect Method11.3.2. Managing Oracle Listeners11.3.2.1. Managing Listeners with Oracle Net Manager11.3.2.1.1. Configuring Listener Services Using Oracle Net Manager11.3.2.1.2. Creating the Listener11.3.2.1.3. Adding Service-Name Information to the Listener11.3.2.2. Optional listener.ora Parameters11.3.2.3. Managing Listeners with Oracle Enterprise Manager11.3.2.3.1. Adding a Listener Using Enterprise Manager Database Control11.3.2.3.2. Editing Existing Listeners Using EM Database Control11.3.2.4. Managing Listeners with lsnrctl11.3.2.4.1. Starting the Listener11.3.2.4.2. Reloading the Listener11.3.2.4.3. Showing the Status of the Listener11.3.2.4.4. Listing the Services for the Listener11.3.2.4.5. Other Commands in lsnrctl11.3.2.4.6. Using the set Commands in lsnrctl11.3.2.4.7. Stopping the Listener11.3.3. Dynamically Registering Services11.3.4. Oracle Net Logging and Tracing on the Server11.3.4.1. Server Logging11.3.4.2. Server Tracing11.3.4.3. Enabling Server Tracing11.4. Configuring Oracle Net for the Client11.4.1. Client-Side Names Resolution Options11.4.2. The Host Naming Method11.4.2.1. Configuring the Host Naming Method11.4.3. The Oracle Easy Connect Method11.4.4. The Local Naming Method11.4.4.1. Configuring the Local Naming Method Using Oracle Net Manager11.4.4.2. Contents and Structure of the tnsnames.ora File11.4.4.3. Configuring Local Naming Using Enterprise Manager11.4.5. Troubleshooting Client-Side Connection Problems11.4.5.1. Checking Network File Locations11.4.5.2. Checking NAMES.DIRECTORY_PATH11.4.5.3. Checking NAMES.DEFAULT_DOMAIN11.4.5.4. Checking for Client-Side Error Codes11.5. An Overview of Oracle Shared Server11.5.1. Dedicated Server vs. Shared Server11.5.2. Advantages and Disadvantages of Shared Server11.6. Oracle Shared Server Infrastructure11.6.1. PGA and SGA Changes When Using Oracle Shared Server11.6.2. The Role of the Listener in an Oracle Shared Server Environment11.6.3. Configuring the Oracle Shared Server11.6.3.1. Defining the Shared Server Parameters11.6.3.1.1. Using the DISPATCHERS Parameter11.6.3.1.2. Using the MAX_DISPATCHERS Parameter11.6.3.1.3. Using the SHARED_SERVERS Parameter11.6.3.1.4. Using the SHARED_SERVER_SESSIONS Parameter11.6.3.1.5. Using the MAX_SHARED_SERVERS Parameter11.6.3.1.6. Using the CIRCUITS Parameter11.6.4. Managing a Shared Server11.6.4.1. Displaying Information about Shared Server Connections Using the Listener Utility11.6.4.2. Requesting a Dedicated Connection in a Shared Server Environment11.6.4.2.1. Configuring Dedicated Connections Manually11.6.4.2.2. Configuring Dedicated Connections Using Oracle Net Manager11.7. Summary11.8. Exam Essentials11.9. Review Questions11.10. Answers to Review Questions
12. Implementing Security and Auditing
12.1. Creating and Managing User Accounts12.1.1. Configuring Authentication12.1.1.1. Password-Authenticated Users12.1.1.2. Externally Authenticated Users12.1.1.3. Globally Authenticated Users12.1.2. Assigning Tablespaces and Quotas12.1.2.1. Assigning a Temporary Tablespace12.1.2.2. Assigning Space Quotas12.1.3. Assigning a Profile and Account Settings12.1.4. Removing a User from the Database12.1.5. Managing Default User Accounts12.2. Granting and Revoking Privileges12.2.1. Granting Object Privileges12.2.1.1. Table Object Privileges12.2.1.1.1. Commonly Granted Privileges12.2.1.1.2. Powerful Administrative Privileges on Tables12.2.1.2. View Object Privileges12.2.1.3. Sequence Object Privileges12.2.1.4. Stored Functions, Procedures, Packages, and Java Object Privileges12.2.1.5. How to Grant Privileges12.2.2. Granting System Privileges12.2.2.1. Database12.2.2.2. Debugging12.2.2.3. Indexes12.2.2.4. Job Scheduler12.2.2.5. Procedures12.2.2.6. Profiles12.2.2.7. Roles12.2.2.8. Sequences12.2.2.9. Sessions12.2.2.10. Synonyms12.2.2.11. Tables12.2.2.12. Tablespaces12.2.2.13. Triggers12.2.2.14. Users12.2.2.15. Views12.2.2.16. Others12.2.2.17. How to Grant System Privileges12.2.3. Role Privileges12.2.3.1. Creating and Managing Roles12.2.3.2. Granting Role Privileges12.2.3.2.1. Enabling Roles12.2.3.2.2. Identifying Enabled Roles12.2.3.2.3. Disabling Roles12.2.3.2.4. Setting Default Roles12.2.3.3. Default Database Roles12.2.4. Applying the Principle of Least Privilege12.3. Controlling Resource Usage by Users12.3.1. Implementing Password Security Features12.4. Auditing Database Activity12.4.1. Managing Statement Auditing12.4.1.1. Enabling Statement Auditing12.4.1.2. Identifying Enabled Statement-Auditing Options12.4.1.3. Disabling Statement Auditing12.4.1.4. Examining the Audit Trail12.4.2. Managing Privilege Auditing12.4.2.1. Enabling Privilege Auditing12.4.2.2. Identifying Enabled Privilege-Auditing Options12.4.2.3. Disabling Privilege Auditing12.4.3. Managing Object Auditing12.4.3.1. Enabling Object Auditing12.4.3.2. Identifying Enabled Object-Auditing Options12.4.3.3. Disabling Object Auditing12.4.4. Purging the Audit Trail12.4.5. Managing Fine-Grained Auditing12.4.5.1. Creating an FGA Policy12.4.5.2. Enabling an FGA Policy12.4.5.3. Disabling an FGA Policy12.4.5.4. Dropping an FGA Policy12.4.5.5. Identifying FGA Policies in the Database12.4.5.6. Reporting on the FGA Audit Trail Entries12.5. Summary12.6. Exam Essentials12.7. Review Questions12.8. Answers to Review Questions
13. Managing Data and Undo
13.1. Manipulating Data through SQL13.1.1. Using the INSERT Statement13.1.1.1. Inserting into a Single Table13.1.1.2. Inserting into Multiple Tables13.1.2. Using the UPDATE Statement13.1.3. Using the MERGE Statement13.1.4. Using the DELETE Statement13.2. Identifying PL/SQL Objects13.2.1. Working with Functions13.2.2. Working with Procedures13.2.3. Working with Packages13.2.3.1. Creating a Package Body13.2.4. Working with Triggering Events and Managing Triggers13.2.4.1. DML Trigger Events13.2.4.2. DDL Trigger Events13.2.4.3. Database Trigger Events13.2.4.4. Enabling and Disabling Triggers13.2.5. Using and Administering PL/SQL Programs13.3. Monitoring Locks and Resolving Lock Conflicts13.3.1. Understanding Locks and Transactions13.3.2. Maximizing Data Concurrency13.3.2.1. Lock Modes13.3.2.2. NOWAIT Mode13.3.2.3. WAIT Mode13.3.2.4. DDL Lock Waits13.3.3. Detecting and Resolving Lock Conflicts13.3.3.1. Understanding Lock Conflicts13.3.3.2. Detecting Lock Conflicts13.3.3.3. Understanding and Resolving Deadlocks13.4. Leveraging Undo Management13.4.1. Understanding Undo Segments13.4.2. Using Undo Data13.4.2.1. User Transaction Rollback13.4.2.2. Read Consistency13.4.3. Monitoring, Configuring, and Administering Undo13.4.3.1. Configuring the Undo Tablespace13.4.3.1.1. UNDO_MANAGEMENT13.4.3.1.2. UNDO_TABLESPACE13.4.3.1.3. UNDO_RETENTION13.4.3.2. Monitoring the Undo Tablespace13.4.3.3. Sizing the Undo Tablespace Using the Undo Advisor13.4.3.4. Guaranteeing Undo Retention13.5. Summary13.6. Exam Essentials13.7. Review Questions13.8. Answers to Review Questions
14. Maintaining the Database and Managing Performance
14.1. Proactive Database Maintenance14.1.1. Managing Optimizer Statistics14.1.1.1. Collecting Statistics14.1.1.1.1. Manually Collecting Stats Using SQL*Plus14.1.1.1.2. Manually Collecting Stats Using EM Grid Control14.1.1.2. Defining Statistics Preferences14.1.1.2.1. Pending Statistics14.1.1.2.2. Extended Statistics14.1.1.3. Configuring Automated Maintenance Tasks Using EM14.1.2. Gathering Performance Statistics14.1.2.1. Using Automatic Workload Repository14.1.2.1.1. Managing AWR Snapshots Manually14.1.2.2. Active Session History14.1.2.3. AWR Baselines14.1.2.3.1. Managing AWR Baselines Using SQL*Plus14.1.2.3.2. Managing AWR Baselines Using EM Database Control14.1.3. Automatic Database Diagnostic Monitoring14.1.3.1. Using EM Database Control to View ADDM Analysis14.1.3.1.1. The EM Database Control Performance Findings Link14.1.3.1.2. The EM Database Control Performance Tab14.1.3.1.3. The Advisor Central Screen14.1.3.2. Using Data Dictionary Views to View ADDM Analysis14.1.4. The Advisory Framework14.1.5. Monitoring Server-Generated Alerts14.1.5.1. Viewing and Configuring Alerts Using EM Database Control14.1.5.2. Viewing and Configuring Alerts Using SQL14.1.6. Understanding Automatic Diagnostic Repository14.1.6.1. Using ADRCI to View the Alert Log File14.1.6.2. Using EM to View the Alert Log File14.2. Managing Performance14.2.1. Sources of Tuning Information14.2.1.1. The Alert Log14.2.1.2. Background and User Trace Files14.2.1.3. Dynamic Performance Views14.2.1.4. Data Dictionary Views14.2.2. Compiling Invalid and Unusable Objects14.2.2.1. Identifying Unusable Objects Using the Data Dictionary14.2.2.2. Identifying Unusable Objects Using EM14.2.3. Tuning Memory14.2.3.1. Memory Components14.2.3.2. Automatic Shared Memory Management14.2.3.3. Automatic SQL Execution Memory Management14.2.3.4. Automatic Memory Management14.2.3.5. Managing Memory Using EM Database Control14.2.4. Important Performance Metrics14.2.4.1. Using EM Database Control to View Performance Metrics14.3. Summary14.4. Exam Essentials14.5. Review Questions14.6. Answers to Review Questions
15. Implementing Database Backups
15.1. Understanding and Configuring Recovery Components15.1.1. Understanding Control Files15.1.1.1. Control File Architecture15.1.1.2. Multiplexing Control Files15.1.1.2.1. Multiplexing Control Files Using init.ora15.1.1.2.2. Multiplexing Control Files Using an Spfile15.1.2. Understanding Checkpoints15.1.3. Understanding Redo Log Files15.1.3.1. Redo Log File Architecture15.1.3.2. Log Switch Operations15.1.3.3. Multiplexing Redo Log Files15.1.3.3.1. Creating New Groups15.1.3.3.2. Adding New Members15.1.3.3.3. Renaming Log Members15.1.3.3.4. Dropping Redo Log Groups15.1.3.3.5. Dropping Redo Log Members15.1.3.3.6. Clearing Online Redo Log Files15.1.4. Understanding Archived Redo Log (ARCHIVELOG) Files15.1.4.1. Archived Redo Log File Architecture15.1.4.2. Setting the Archive Destination15.1.4.2.1. LOG_ARCHIVE_DEST_n15.1.4.2.2. LOG_ARCHIVE_MIN_SUCCEED_DEST15.1.4.2.3. LOG_ARCHIVE_FORMAT15.1.4.3. Setting ARCHIVELOG15.1.5. Understanding the Flash Recovery Area15.1.5.1. Flash Recovery Area Occupants15.1.5.2. The Flash Recovery Area and SQL Commands15.1.5.3. The Flash Recovery Area and EM Database Control15.1.5.4. Flash Recovery Area Management15.2. Performing Backups15.2.1. Understanding Backup Terminology15.2.2. Backing Up the Control File15.2.2.1. Text Backup of Control File15.2.2.2. Binary Backup of Control File15.2.2.3. RMAN Backup of Control File15.2.3. Backing Up the Database15.2.3.1. User-Managed Cold Backups15.2.3.2. User-Managed Hot Backups15.2.4. Using RMAN to Create Backups15.2.4.1. Configuring RMAN Backup Settings15.2.4.2. Understanding Image Copies and Backup Sets15.2.4.3. Creating Full and Incremental Backups15.2.5. Managing Backups15.2.5.1. Catalog Maintenance15.2.5.2. Viewing Backup Reports15.3. Summary15.4. Exam Essentials15.5. Review Questions15.6. Answers to Review Questions
16. Recovering the Database
16.1. Understanding Types of Database Failure16.1.1. Statement Failures16.1.2. User-Process Failures16.1.3. Network Failures16.1.4. User-Error Failures16.1.5. Instance Failures16.1.6. Media Failures16.2. Performing Recovery Operations16.2.1. Understanding Instance Startup16.2.2. Keeping an Instance from Failing16.2.3. Recovering from Instance Failure16.2.4. Tuning Instance Recovery16.2.5. Recovering from User Errors16.2.5.1. Using Flashback Query16.2.5.2. Using Flashback Drop and the Recycle Bin16.2.5.2.1. Recycle-Bin Concepts16.2.5.2.2. Retrieving Dropped Tables from the Recycle Bin16.2.5.2.3. Recycle-Bin Considerations and Limitations16.2.5.3. Using Flashback Table16.2.5.4. Using EM Database Control to Perform Table Recovery16.2.5.5. Using Flashback Transaction16.2.5.6. Using LogMiner16.2.6. Recovering from Loss of a Control File16.2.7. Using the Data Recovery Advisor16.2.8. Recovering from the Loss of a Redo Log File16.2.9. Recovering from the Loss of a Non–System-Critical Data File16.2.9.1. Loss of a Non-System-Critical Data File in NOARCHIVELOG Mode16.2.9.2. Loss of a Non-System-Critical Data File in ARCHIVELOG Mode16.2.10. Recovering from the Loss of a System-Critical Data File16.2.10.1. Loss of a System-Critical Data File in NOARCHIVELOG Mode16.2.10.2. Loss of a System-Critical Data File in ARCHIVELOG Mode16.3. Summary16.4. Exam Essentials16.5. Review Questions16.6. Answers to Review Questions
17. Moving Data and Using EM Tools
17.1. Understanding Data Pump17.1.1. Architecture of Data Pump17.1.1.1. Data Pump Components17.1.1.2. Data Pump Processes17.1.1.3. Data Access Methods17.1.2. Using Data Pump Clients17.1.2.1. Setting Up the Dump Location17.1.2.2. Specifying Export and Import Modes17.1.2.3. Using expdp17.1.2.3.1. Data Pump Export Parameters17.1.2.4. Using impdp17.1.2.4.1. Data Pump Import Parameters17.1.2.4.2. Import Transformations17.1.2.5. Network-Mode Import17.1.2.6. Data and Metadata Filters17.1.2.7. Managing Data Pump Jobs17.1.3. Using the Data Pump Wizard17.1.3.1. Import Using EM Database Control17.2. Loading Data with SQL*Loader17.2.1. Specifying SQL*Loader Command-Line Parameters17.2.2. Specifying Control File Options17.2.2.1. Using Direct Path Loading17.2.3. Using EM to Load Data17.3. Populating External Tables17.3.1. Loading External Tables Using Data Pump17.3.2. Loading External Tables Using Loader17.4. Using EM Support Workbench17.4.1. Identifying a Problem17.4.2. Gathering Additional Diagnostic Information17.4.3. Creating a Service Request17.4.4. Packaging Diagnostic Data17.4.4.1. Configuring Incident Packaging17.4.5. Tracking and Closing the Incident17.5. Using EM to Manage Patches17.5.1. Using the Patch Advisor17.5.2. Viewing the Patch Cache17.5.3. Finding the Patch Prerequisites17.5.4. Staging a Patch17.5.5. Applying a Patch17.6. Summary17.7. Exam Essentials17.8. Review Questions17.9. Answers to Review Questions
A. About the Companion CD
A.1. What You'll Find on the CDA.1.1. Sybex Test EngineA.1.2. PDF of the BookA.1.3. Adobe ReaderA.1.4. Electronic FlashcardsA.2. System RequirementsA.2.1. Using the CDA.3. TroubleshootingA.3.1. Customer Care
Glossary

Content preview from OCA Oracle® Database 11g Administrator Certified Associate: Study Guide

Chapter 3. Using Group Functions

ORACLE DATABASE 11g: SQL FUNDAMENTALS I EXAM OBJECTIVES COVERED IN THIS CHAPTER:

Reporting Aggregated Data Using the Group Functions
- Identify the available group functions
- Describe the use of group functions
- Group data by using the GROUP BY clause
- Include or exclude the grouped rows by using the HAVING clause

As explained in the previous chapter, functions are programs that take zero or more arguments and return a single value. The exam focuses on two types of functions: single-row and aggregate (group) functions. Single-row functions were covered in Chapter 2, "Using Single-Row Functions." Group functions are covered in this chapter.

Group functions differ from single-row functions in how they are evaluated. Single-row ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

OCP: Oracle Database 11g Administrator Certified Professional Study Guide

Publisher Resources

ISBN: 9780470395127Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

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

by Biju Thomas

Chapter 3. Using Group Functions

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.