《oracle数据库11G初学者指南》(Oracle Database 11g, A Beginner's Guide)(Ian Abramson Michael Abbey Michael J, Corey Michelle Malcher)文字版[PDF]本书是针对Oracle公司最新推出的大型数据库管理系统Oracle 11g编写的，循序渐进地介绍了Oracle的基本知识。在本书中将对Oracle的众多概念进行介绍，并介绍其在实际应用中的使用。* Oracle has a 47.1 percent share of the relational database market, growing at rate of 14.9 percent* Previous editions have sold more than 125,000 units* The ideal resource for entry-level database administrators and developersAuthor Biography#Ian Abramson is the executive vice president for the Independent Oracle User Group (IOUG).Michael Abbey serves on the board of directors of the IOUG and is a frequent speaker at user group and vendor conferences.Michael J. Corey is a past president of the IOUG and a frequent speaker at technology conferences. He is the coauthor, with Abramson and Abbey, of Oracle Database 10g: A Beginner's Guide. 目录: ContentsACKNOWLEDGMENTS ..... . . xiiiINTRODUCTION .....xv1 Database Fundamentals ..... . . . 1Critical Skill 1.1 Define a Database .... . . 2Critical Skill 1.2 Learn the Oracle Database 11g Architecture . . . . . . . 4The Control Files ..... . . . . . 5The Online Redo Logs ..... . 5The System Tablespace ..... 5The Sysaux Tablespace ..... 6Default Temporary Tablespace .... . . 6Undo Tablespace ..... . . . . 6The Server Parameter File .... . . . . . . 6Background Processes ..... . 7Project 1-1 Review the Oracle Database 11g Architecture .. 9The Database Administrator .... . . . . 10Critical Skill 1.3 Learn the Basic Oracle Database 11g Data Types . . . 11varchar2 ...... . . . 12number ...... . . . . 12date ......13timestamp ...... . . 13clob ......13blob ......14Critical Skill 1.4 Work with Tables .... . . . 14Tables Related to part_master .... . . . 14Critical Skill 1.5 Work with Stored Programmed Objects .. . . 16Views ...... . . . . . 16Triggers ...... . . . . 18Procedures ...... . 18Functions ...... . . . 18Packages ...... . . . 19vvi Oracle Database 11g: A Beginner’s GuideCritical Skill 1.6 Become Familiar with Other Important Itemsin Oracle Database 11g ..... . 21Indexes ...... . . . . 21Users ...... . . . . . . 22Tablespace Quotas ..... . . . 22Synonyms ...... . . 23Roles ...... . . . . . . 24Default User Environments .... . . . . . 24Critical Skill 1.7 Work with Object and System Privileges .. . 25Select ...... . . . . . 25Insert ...... . . . . . . 26Update ...... . . . . 26Delete ...... . . . . . 26System Privileges ..... . . . . . 26Critical Skill 1.8 Introduce Yourself to the Grid ... . 27Critical Skill 1.9 Tie It All Together .... . . 31Chapter 1 Mastery Check..... . . . 322 Installing Oracle ...... . 35Critical Skill 2.1 Research and Plan the Installation .. . . . . . . 36Define System Requirements .... . . . . 37Linux Installation ..... . . . . . 37Critical Skill 2.2 Set Up the Operating System ... . . 42Project 2-1 Configure Kernel Parameters ... . . . . . 44Critical Skill 2.3 Get Familiar with Linux ... . . . . . . 47Critical Skill 2.4 Choose Components to Install ... . 48Critical Skill 2.5 Install the Oracle Software ... . . . . 49Database Configuration Assistant .... . 57Verify the Installation ..... . . 61Chapter 2 Mastery Check..... . . . 633 Connecting to Oracle ..... . . . . . 65Critical Skill 3.1 Use Oracle Net Services ... . . . . . 66Network Protocols ..... . . . . 67Optimize Network Bandwidth .... . . . 67Connections ...... 68Maintain Connections ..... . 69Define a Location ..... . . . . 70Critical Skill 3.2 Learn the Difference Between Dedicatedand Shared Server Architectures .... . . 71Dedicated Server ..... . . . . . 71Shared Server .....72Set Dispatchers ..... . . . . . . 74Views to Monitor the Shared Server ...76Critical Skill 3.3 Define Connections .... . 77viiContentsA Connect Descriptor ..... . 77Define a Connect Descriptor .... . . . . 77The Oracle Connection Manager .... . 78Session Multiplexing ..... . . 79Firewall Access Control ..... 79Critical Skill 3.4 Use the Oracle Net Listener ... . . . 80Password Authentication ....82Multiple Listeners ..... . . . . 82Connection Pooling ..... . . . 83Critical Skill 3.5 Learn Naming Methods ... . . . . . . 83Directory Naming Method .... . . . . . . 83Directory Information Trees .... . . . . . 84Distinguished Names ..... . . 85How to Find the Directory Naming Information .. . . . . . 85Net Service Alias Entries ....86The Local Naming Method .... . . . . . 86The Easy Naming Method .... . . . . . . 87The External Naming Method .... . . . 87Which Naming Method to Use .... . . 87Critical Skill 3.6 Use Oracle Configuration Files ... 87Critical Skill 3.7 Use Administration Tools ... . . . . . 89The Oracle Enterprise Manager/Grid Control ... 89The Oracle Net Manager ....90The OEM Console ..... . . . . 91The OEM Components ..... 91The Oracle Net Configuration Assistant ... . . . . 91The Oracle Internet Directory Configuration Assistant .. . 92Command-Line Utilities ..... 92The Oracle Advanced Security Option ... . . . . 94Dispatchers ...... . 94Project 3-1 Test a Connection .... . . . . . 95Critical Skill 3.8 Use Profiles ....97Critical Skill 3.9 Network in a Multi-tiered Environment .. . . 98Critical Skill 3.10 Install the Oracle 11g Client Software .. . . 99Chapter 3 Mastery Check..... . . . 1044 SQL: Structured Query Language .... . . 105Critical Skill 4.1 Learn the SQL Statement Components .. . . . 106DDL ...... . . . . . . 106DML ...... . . . . . . 107Critical Skill 4.2 Use Basic Insert and Select Statements .. . . . 108Insert ...... . . . . . . 108Select ...... . . . . . 109Critical Skill 4.3 Use Simple Where Clauses ... . . . 111A Where Clause with and/or .... . . . . 113viii Oracle Database 11g: A Beginner’s GuideThe Where Clause with NOT .... . . . 115The Where Clause with a Range Search ... . . . 115The Where Clause with a Search List ... . . . . . . 116The Where Clause with a Pattern Search ... . . . 116The Where Clause: Common Operators ... . . . 117Critical Skill 4.4 Use Basic Update and Delete Statements .. . 118Update ...... . . . . 119Delete ...... . . . . . 120Critical Skill 4.5 Order Data ..... 122Critical Skill 4.6 Employ Functions: String, Numeric, Aggregate(No Grouping) ...... 124String Functions ..... . . . . . . 124Numeric Functions ..... . . . 124Aggregate Functions ..... . . 124Critical Skill 4.7 Use Dates and Data Functions (Formatting andChronological) ...... 126Date Functions ..... . . . . . . 126Special Formats with the Date Data Type ... . . 127Nested Functions ..... . . . . . 128Critical Skill 4.8 Employ Joins (ANSI vs. Oracle): Inner, Outer, Self . . . 129Inner Joins ...... . . 129Outer Joins ...... . 134Project 4-1 Join Data Using Inner and Outer Joins .. . . . . . . 134Project 4-2 Join Data Using ANSI SQL Joins ... . . 137Self-Joins ...... . . . 139Critical Skill 4.9 Learn the Group By and Having Clauses .. . 140Group By ...... . . . 140Having ...... . . . . 141Project 4-3 Group Data in Your Select Statements .. . . . . . . 141Critical Skill 4.10 Learn Subqueries: Simple and CorrelatedComparison with Joins ..... . . 145Simple Subquery ..... . . . . . 145Correlated Subqueries with Joins .... . 146Critical Skill 4.11 Use Set Operators: Union, Intersect, Minus . . . . . . . 147Union ...... . . . . . 147Union All ...... . . 148Intersect ...... . . . . 148Minus ...... . . . . . 149Project 4-4 Use the Union Function in Your SQL ..149Critical Skill 4.12 Use Views ....150Critical Skill 4.13 Learn Sequences: Just Simple Stuff .. . . . . . 152Critical Skill 4.14 Employ Constraints: Linkage to Entity Models, Types,Deferred, Enforced, Gathering Exceptions ... . . . 153Linkage to Entity Models ....154ixContentsTypes ...... . . . . . . 154Deferred ...... . . . 156Critical Skill 4.15 Format Your Output with SQL*Plus .. . . . . 156Page and Line Size ..... . . . 157Page Titles ...... . . 157Page Footers ...... 157Formatting Columns ..... . . 157Project 4-5 Format Your SQL Output .... 157Writing SQL*Plus Output to a File .... 160Chapter 4 Mastery Check..... . . . 1605 PL/SQL ....... 163Critical Skill 5.1 Define PL/SQL and Learn Why We Use It .. 164Critical Skill 5.2 Describe the Basic PL/SQL Program Structure . . . . . . 166Critical Skill 5.3 Define PL/SQL Data Types ... . . . . 168Valid Characters ..... . . . . . 168Arithmetic Operators ..... . . 168The varchar2 Data Type ....171The Number Data Type ..... 171The Date Data Type ..... . . 172The Boolean Data Type ..... 173Critical Skill 5.4 Write PL/SQL Programs in SQL*Plus .. . . . . 174Project 5-1 Create a PL/SQL Program .... 176SQL in Your PL/SQL Programs .... . . . 177PL/SQL Cursors ..... . . . . . . 177The Cursor FOR Loop ..... . 179Critical Skill 5.5 Handle Error Conditions in PL/SQL .. . . . . . 181Error Handling Using Oracle-Supplied Variables .. . . . . . 185Critical Skill 5.6 Include Conditions in Your Programs .. . . . . 187Program Control ..... . . . . . 187Project 5-2 Use Conditions and Loops in PL/SQL ..195Critical Skill 5.7 Create Stored Procedures―How and Why .. 196Critical Skill 5.8 Create and Use Functions ... . . . . 201Project 5-3 Create and Use a Function ...201Critical Skill 5.9 Call PL/SQL Programs ...203Chapter 5 Mastery Check..... . . . 2046 The Database Administrator .... . . . . . . 207Critical Skill 6.1 Learn What a DBA Does ... . . . . . 208Critical Skill 6.2 Perform Day-to-Day Operations ... 209Architecture and Design ....209Capacity Planning ..... . . . . 209Backup and Recovery ..... . 210Security ...... . . . . 210Performance and Tuning ....210x Oracle Database 11g: A Beginner’s GuideManaging Database Objects .... . . . . 210Storage Management ..... . . 211Change Management ..... . . 211Schedule Jobs .....211Network Management ..... . 211Troubleshooting ..... . . . . . 211Critical Skill 6.3 Understand the Oracle Database 11g Infrastructure . . 212Schemas ...... . . . 212Storage Structures ..... . . . . 215Critical Skill 6.4 Operate Modes of an Oracle Database 11g .216Modes of Operation ..... . . 216Database and Instance Shutdown .... 217Critical Skill 6.5 Get Started with Oracle Enterprise Manager .219Instance Configuration ..... . 219User Sessions ...... 220Resource Consumer Groups .... . . . . 220Schema, Security, and Storage Management ... 221Distributed Management ....222Warehouse Features ..... . . 222Other Tools ...... . 222Critical Skill 6.6 Manage Database Objects ... . . . . 223Control Files ...... 223Redo Logs ...... . . 223Undo Management ..... . . . 224Schema Objects ..... . . . . . 225Critical Skill 6.7 Manage Space .... . . . . . 226Archive Logs ...... 227Tablespaces and Data Files .... . . . . . 227Critical Skill 6.8 Manage Users .... . . . . . 229Create a User ...... 229Edit Users ...... . . 230Critical Skill 6.9 Manage Privileges for Database Users .. . . . 231Grant Authority ..... . . . . . . 232Roles ...... . . . . . . 233Profiles ...... . . . . 234Project 6-1 Create Essential Objects .... . 235Chapter 6 Mastery Check..... . . . 2377 Backup and Recovery ..... . . . . . . 239Critical Skill 7.1 Understand Oracle Backup and RecoveryFundamentals ...... . 240Where Do I Start? ..... . . . . . 240Backup Architecture ..... . . . 241Oracle Binaries .....242Parameter Files .....242xiContentsControl Files ...... . 242Redo Logs ...... . . . 243Undo Segments .....243Checkpoints ...... . 244Archive Logs ...... . 244Data Files, Tablespaces, Segments, Extents, and Blocks .. . 245Dump Files ...... . . 247Critical Skill 7.2 Learn about Oracle User-Managed Backupand Recovery...... . . 248Types of User-Managed Backups.... . . 248Cold Backups ...... 248Hot Backups ...... . 249Recovery from a Cold Backup .... . . . . 251Recovery from a Hot Backup.... . . . . . 252Seven Steps to Recovery ..... 252Recovery Using Backup Control Files ... . . . . . . 253Critical Skill 7.3 Write a Database Backup... . . . . . . 254Critical Skill 7.4 Back Up Archived Redo Logs... . . . 256Critical Skill 7.5 Get Started with Oracle Data Pump .. . . . . . . 257Critical Skill 7.6 Use Oracle Data Pump Export ... . . 258Critical Skill 7.7 Work with Oracle Data Pump Import.. . . . . . 264Critical Skill 7.8 Use Traditional Export and Import ..269Critical Skill 7.9 Get Started with Recovery Manager .. . . . . . . 270RMAN Architecture ..... . . . . 271Set Up a Recovery Catalog and Target Database .. . . . . . . 274Key RMAN Features ..... . . . 274Backups...... . . . . . 277RMAN Using Enterprise Manager .... . 278Performing Backups..... . . . . 281Restore and Recovery ..... . . 282Project 7-1 RMAN End to End .... . . . . . . 283Chapter 7 Mastery Check..... . . . 2858 High Availability: RAC, ASM, and Data Guard ... 287Critical Skill 8.1 Define High Availability ... . . . . . . 288Critical Skill 8.2 Understand Real Application Clusters .. . . . 289Critical Skill 8.3 Install RAC ..... 290Critical Skill 8.4 Test RAC ..... . 295Workload Manager ..... . . . 296ASM ...... . . . . . . 297Critical Skill 8.5 Set Up the ASM Instance ... . . . . . 297Project 8-1 Install ASMLib ..... 301Critical Skill 8.6 Create ASM Disk Groups ... . . . . . 302Project 8-2 Create Disk Groups .... . . . . 303Critical Skill 8.7 Use ASMCMD and ASMLIB ... . . . 304xii Oracle Database 11g: A Beginner’s GuideCritical Skill 8.8 Convert an Existing Database to ASM .. . . . . 306Critical Skill 8.9 Understand Data Guard ... . . . . . . 308Critical Skill 8.10 Explain Data Guard Protection Modes .. . . 309Critical Skill 8.11 Create a Physical Standby Server .. . . . . . . 312Project 8-3 Create a Physical Standby Server ... . . 313Chapter 8 Mastery Check..... . . . 3159 Large Database Features ..... . . 317Critical Skill 9.1 Learn to Identify a Very Large Database .. . . 318Critical Skill 9.2 Why and How to Use Data Partitioning .. . . 319Why Use Data Partitioning .... . . . . . 319Implement Data Partitioning .... . . . . 323Project 9-1 Create a Range-Partitioned Table anda Local-Partitioned Index ....340Critical Skill 9.3 Compress Your Data .... 344Data Compression ..... . . . . 344Index Key Compression ..... 346Critical Skill 9.4 Use Parallel Processing to Improve Performance . . . . 347Parallel Processing Database Components ... . . 347Parallel Processing Configuration .... 348Invoke Parallel Execution .... . . . . . . 350Critical Skill 9.5 Use Materialized Views ... . . . . . . 351Uses for Materialized Views .... . . . . 352Query Rewrite .....353When to Create Materialized Views ... . . . . . . 354Create Materialized Views .... . . . . . . 355Critical Skill 9.6 Use SQL Aggregate and Analysis Functions .356Aggregation Functions ..... . 356Analysis Functions ..... . . . . 359Other Functions ..... . . . . . . 367Critical Skill 9.7 Create SQL Models .... . 367Project 9-2 Use Analytic SQL Functions and Models .. . . . . 370Chapter 9 Mastery Check..... . . . 372A Mastery Check Answers ..... . . . 375Chapter 1: Database Fundamentals .... . . 376Chapter 2: Installing Oracle ..... 379Chapter 3: Connecting to Oracle .... . . . . 380Chapter 4: SQL: Structured Query Language ... . . . 381Chapter 5: PL/SQL .....384Chapter 6: The Database Administrator ...385Chapter 7: Backup and Recovery .... . . . . 387Chapter 8: High Availability: RAC, ASM, and Data Guard .. . 390Chapter 9: Large Database Features .... . . 391Index ....... . 395 ……