Hands-On Oracle Database 10g Express Edition for Windows [Knyga]

FOREWORD		xxi
ACKNOWLEDGMENTS		xxiii
INTRODUCTION		xxv
PART I Getting Started
1 Introduction to Databases and Oracle		3	(10)
Information Management		4	(3)
Databases		4	(1)
Database Management Systems		5	(1)
Database Applications		6	(1)
Oracle Database 10g		6	(1)
Oracle Fundamentals		7	(4)
Databases and Instances		7	(2)
Tables		9	(1)
SQL and Data Access		9	(1)
Database Users and Sessions		10	(1)
SQL*Plus		10	(1)
Oracle Application Express		11	(1)
Chapter Summary		11	(2)
2 Install and Start Using Oracle Database 10g Express Edition		13	(36)
Installation Overview		14	(1)
Database Server System Requirements		15	(3)
EXERCISE 2.1: Check System Requirements		15	(2)
Additional Requirements for Application Development		17	(1)
Oracle Database 10g Express Edition Software		18	(1)
Installation		18	(8)
EXERCISE 2.2: Establish a Microsoft Windows Administrator Session		18	(1)
EXERCISE 2.3: Install Oracle XE Step-by-Step		18	(8)
Post-Installation Exercises		26	(3)
EXERCISE 2.4: Accessing the Documentation		26	(1)
EXERCISE 2.5: Registering for the Discussion Forum		27	(1)
EXERCISE 2.6: Accessing the Support Forum		28	(1)
Basic Oracle XE Skills		29	(10)
Oracle Startup and Shutdown		29	(3)
EXERCISE 2.7: Check the Status of Oracle XE		30	(1)
EXERCISE 2.8: Start Oracle Manually		31	(1)
EXERCISE 2.9: Stop Oracle Manually		32	(1)
EXERCISE 2.10: Automate Oracle Startup		32	(1)
Database Connections		32	(7)
EXERCISE 2.11: Connect to Oracle with SQL*Plus		35	(1)
EXERCISE 2.12: Disconnect from Oracle and Exit SQL*Plus		36	(1)
EXERCISE 2.13: Launch the Database Home Page		37	(2)
Troubleshooting Tips		39	(4)
Confirm Existence of Oracle XE Software and Database		39	(1)
Check Oracle-Related Services		39	(1)
Permit Network Access		40	(2)
Web Browser Configuration		42	(1)
Oracle Database 10g Express Edition Updates		43	(1)
Using This Book's Support Files		44	(1)
Chapter Summary		45	(4)
PART II Fundamentals of Application Development
3 Access Database Data with SQL		49	(46)
Chapter Prerequisites		50	(1)
What Is SQL?		50	(2)
Types of SQL Commands		50	(1)
Application Portability and the ANSI/ISO SQL Standard		51	(1)
Ad Hoc SQL Tools		52	(1)
Retrieving Data with Queries		52	(28)
The Structure of a Query		53	(1)
Building Basic Queries		54	(2)
EXERCISE 3.1: Retrieving All Columns and Rows		54	(1)
EXERCISE 3.2: Retrieving Specific Columns		54	(1)
EXERCISE 3.3: Using the SQL*Plus DESCRIBE Command		55	(1)
EXERCISE 3.4: Specifying an Alias for a Column		56	(1)
Building Expressions in a Query's SELECT Clause		56	(7)
EXERCISE 3.5: Building SELECT Clause Expressions with the Concatenation String Operator		57	(1)
EXERCISE 3.6: Building SELECT Clause Expressions with Arithmetic Operators		57	(1)
EXERCISE 3.7: Building SELECT Clause Expressions with SQL Functions		58	(2)
EXERCISE 3.8: Working with Nulls in SELECT Clause Expressions		60	(1)
EXERCISE 3.9: Implementing Conditional Logic in SELECT Clause Expressions		61	(2)
Retrieving Specific Rows from Tables		63	(8)
EXERCISE 3.10: Building WHERE Clause Conditions with Relational Operators		63	(5)
EXERCISE 3.11: Building WHERE Clause Conditions with Subqueries		68	(2)
EXERCISE 3.12: Building Composite WHERE Clause Conditions with Logical Operators		70	(1)
Grouping and Sorting Data Within a Query's Result Set		71	(5)
EXERCISE 3.13: Grouping Records in a Query's Result Set		71	(1)
EXERCISE 3.14: Rolling Up Groups into Supergroups		72	(3)
EXERCISE 3.15: Ordering Records in a Query's Result Set		75	(1)
Joining Data in Related Tables		76	(6)
EXERCISE 3.16: Building an Inner Join of Two Tables		77	(1)
EXERCISE 3.17: Building an Outer Join of Two Tables		78	(2)
Inserting, Updating, and Deleting Rows in Tables		80	(1)
EXERCISE 3.18: Inserting New Rows into a Table		80	(1)
EXERCISE 3.19: Updating Rows in a Table		81	(1)
EXERCISE 3.20: Deleting Rows from a Table		81	(1)
Committing and Rolling Back Transactions		81	(1)
EXERCISE 3.21: Committing and Rolling Back Transactions		81	(1)
Transaction Design		82	(2)
Units of Work		82	(2)
Read-Write Transactions		84	(1)
Read-Only Transactions		84	(1)
Building SQL with Oracle Application Express		84	(10)
Introducing Oracle Application Express's Query Builder		85	(12)
EXERCISE 3.22: Using Query Builder		85	(9)
Chapter Summary		94	(1)
4 Code Database Access Programs with PL/SQL		95	(48)
Chapter Prerequisites		96	(1)
What Is PL/SQL?		96	(1)
PL/SQL Blocks		97	(2)
Program Declarations		98	(1)
The Program Body		98	(1)
Exception Handlers		98	(1)
Program Comments		99	(1)
The Fundamentals of PL/SQL Coding		99	(32)
Working with Program Variables		99	(7)
EXERCISE 4.1: Declaring Variables and Constants with Basic Datatypes		100	(4)
EXERCISE 4.2: Assigning Values to Variables		104	(2)
Controlling Program Flow		106	(3)
EXERCISE 4.3: Using PL/SQL Loops		106	(1)
EXERCISE 4.4: Using the PL/SQL Command IF...ELSIF...ELSE		107	(2)
Interacting with Databases		109	(2)
EXERCISE 4.5: Manipulating Table Data with DML Statements		109	(1)
EXERCISE 4.6: Assigning a Value to a Variable with a Query		110	(1)
Declaring and Using Subprograms: Procedures and Functions		111	(3)
EXERCISE 4.7: Declaring and Using a Procedure		111	(1)
EXERCISE 4.8: Declaring and Using a Function		112	(2)
Working with Record Types		114	(2)
EXERCISE 4.9: Declaring and Using Record Types		114	(2)
Using the %TYPE and %ROWTYPE Attributes		116	(2)
EXERCISE 4.10: Using the %TYPE Attribute		116	(1)
EXERCISE 4.11: Using the %ROWTYPE Attribute		117	(1)
Working with Cursors		118	(5)
EXERCISE 4.12: Declaring and Using a Cursor		119	(2)
EXERCISE 4.13: Manipulating a Cursor's Current Row		121	(2)
Working with Collections		123	(4)
EXERCISE 4.14: Declaring and Initializing a Nested Table		123	(1)
EXERCISE 4.15: Using Collection Methods with a Nested Table		124	(3)
Handling Program Exceptions		127	(4)
EXERCISE 4.16: Handling Predefined Exceptions		128	(2)
EXERCISE 4.17: Declaring and Handling User-Defined Exceptions		130	(1)
Types of PL/SQL Programs		131	(11)
Anonymous PL/SQL Blocks		131	(1)
Stored Procedures and Functions		131	(3)
EXERCISE 4.18: Creating and Using Stored Procedures		131	(2)
EXERCISE 4.19: Creating and Using Stored Functions		133	(1)
Packages		134	(5)
EXERCISE 4.20: Declaring and Using a Package		134	(5)
Database Triggers		139	(5)
EXERCISE 4.21: Creating and Using Database Triggers		140	(2)
Chapter Summary		142	(1)
5 Build a Basic Relational Schema		143	(66)
Chapter Prerequisites		144	(1)
Schemas		144	(2)
Schemas, an Entirely Logical Concept		145	(1)
The Correlation of Schemas and Database User Accounts		146	(1)
Database Tables		146	(34)
Columns and Datatypes		147	(4)
Data Integrity and Integrity Constraints		151	(3)
Creating Tables and Integrity Constraints		154	(26)
EXERCISE 5.1: Start the Object Browser		155	(1)
EXERCISE 5.2: Create the PARTS Table		156	(4)
EXERCISE 5.3: Create the CUSTOMERS Table		160	(2)
EXERCISE 5.4: Modify a Column's Datatype		162	(1)
EXERCISE 5.5: Add a Primary Key to a Table		163	(1)
EXERCISE 5.6: Add a Composite Unique Key to a Table		164	(1)
EXERCISE 5.7: Add a Not Null Constraint to a Table		164	(1)
EXERCISE 5.8: Create the SALESREPS Table		165	(1)
EXERCISE 5.9: Create the ORDERS Table		166	(3)
EXERCISE 5.10: Create the ITEMS Table		169	(3)
EXERCISE 5.11: Add a Column and Referential Integrity Constraint to a Table		172	(3)
EXERCISE 5.12: Test Integrity Constraints		175	(5)
Views		180	(8)
Creating Views		180	(8)
EXERCISE 5.13: Create and Use a Read-Only View		181	(1)
EXERCISE 5.14: Create an Updateable View		182	(2)
EXERCISE 5.15: Create an Updateable Join View		184	(2)
EXERCISE 5.16: Create an INSTEAD OF Trigger for a View		186	(2)
Sequences		188	(3)
Creating and Using Sequences		189	(2)
EXERCISE 5.17: Create a Sequence		189	(1)
EXERCISE 5.18: Use and Reuse a Sequence Number		190	(1)
Synonyms		191	(2)
Private and Public Synonyms		191	(1)
Creating Synonyms		192	(1)
EXERCISE 5.19: Create a Private Synonym		192	(1)
EXERCISE 5.20: Using a Synonym		192	(1)
Indexes		193	(4)
Normal (B*) Indexes		194	(1)
Using Normal Indexes Appropriately		194	(1)
Creating Normal Indexes		195	(2)
EXERCISE 5.21: Create a Normal Index		195	(2)
The Data Dictionary: A Unique Schema		197	(5)
Categories of Data Dictionary Views		197	(14)
EXERCISE 5.22: Query the Data Dictionary		198	(4)
Exploring Other Object Browser Features		202	(5)
EXERCISE 5.23: Reveal Object Dependencies		202	(3)
EXERCISE 5.24: Gather Object Statistics		205	(1)
EXERCISE 5.25: Generate Object DML		206	(1)
Chapter Summary		207	(2)
6 Build an Application with Oracle Application Express		209	(80)
Chapter Prerequisites		210	(1)
Introducing the Application Development Lifecycle		211	(4)
Analyzing Processes and Defining Requirements		212	(1)
Designing the Application to Meet the Requirements		213	(1)
Implementing the Application Using the Design		213	(1)
Testing That the Application Meets the Requirements		214	(1)
Deploying the Application		214	(1)
Maintaining the Application		215	(1)
Software Modeling and the Unified Modeling Language		215	(4)
Use Case Diagrams		216	(1)
Class Diagrams		217	(2)
Building the Application Schema		219	(6)
EXERCISE 6.1: Upload and Run a SQL Script		219	(2)
EXERCISE 6.2: Set User Interface Defaults		221	(4)
Loading Schema Data		225	(7)
Loading Data Manually		225	(4)
EXERCISE 6.3: Manually Enter Data		225	(1)
EXERCISE 6.4: Create Triggers to Generate Primary Keys		226	(3)
Loading Spreadsheet and Text File Data		229	(2)
EXERCISE 6.5: Load Data from a Text File		229	(2)
Loading XML Data		231	(60)
EXERCISE 6.6: Load Data from an XML File		231	(1)
Creating the SysMgmt Application		232	(6)
EXERCISE 6.7: Create the SysMgmt Application with the Create Application Wizard		233	(5)
Running and Testing the Application		238	(4)
EXERCISE 6.8: Run and Test the SysMgmt Application		238	(1)
EXERCISE 6.9: Navigate Application Report Pages		239	(1)
EXERCISE 6.10: Understand Oracle Application Express URLs		240	(1)
EXERCISE 6.11: Browse and Review Application Pages		240	(2)
Refining the Application		242	(8)
EXERCISE 6.12: Create Lists of Values (LOVs) for Forms		242	(1)
EXERCISE 6.13: Add LOVs to Forms		243	(3)
EXERCISE 6.14: Modify a Report's Query and Make New Columns Searchable		246	(4)
Maintaining the Application		250	(30)
EXERCISE 6.15: Create a New Table		252	(3)
EXERCISE 6.16: Set UI Defaults for the New Table		255	(1)
EXERCISE 6.17: Create a New Trigger		256	(1)
EXERCISE 6.18: Add Rows to the DATABASE_SERVICE_REQUESTS Table		257	(1)
EXERCISE 6.19: Add New Application Pages		258	(3)
EXERCISE 6.20: Add a Navigation Menu		261	(2)
EXERCISE 6.21: Copy Breadcrumbs to the New Report and Form Pages		263	(3)
EXERCISE 6.22: Add Search and Display Controls to the New Report		266	(9)
EXERCISE 6.23: Add an Analysis Page to the Application		275	(5)
Deploying the Application		280	(3)
EXERCISE 6.24: Modify Application Attributes for Deployment		280	(1)
EXERCISE 6.25: Deploy the Application on Another Computer		281	(2)
Managing Application Access and Application Users		283	(1)
EXERCISE 6.26: Create an Application Express User		284	(1)
Chapter Summary		284	(5)
PART III Database Administration
7 Secure Database Access		289	(44)
Chapter Prerequisites		290	(1)
User Management		291	(10)
Oracle Database Users and Oracle Application Express Users		291	(1)
Default Database User Accounts		291	(1)
Database User Properties		292	(9)
EXERCISE 7.1: Display Information About Database Users		294	(1)
EXERCISE 7.2: Create a Database User		295	(3)
EXERCISE 7.3: Test the New Database User Account		298	(1)
EXERCISE 7.4: Modify Your Own Password		299	(1)
EXERCISE 7.5: Alter or Drop a Database User		300	(1)
Privilege Management		301	(10)
Types of Database Privileges		301	(2)
Granting and Revoking Privileges		303	(17)
EXERCISE 7.6: Grant System Privileges to a User		303	(2)
EXERCISE 7.7: Revoke System Privileges from a User		305	(1)
EXERCISE 7.8: Grant Object Privileges to a User		306	(5)
EXERCISF 7.9: Revoke Object Privileges from a User		311	(1)
Privilege Management with Roles		311	(9)
EXERCISE 7.10: Create a Role		314	(1)
EXERCISE 7.11: Grant System Privileges and Roles to a Role		315	(1)
EXERCISE 7.12: Grant Object Privileges to a Role		315	(1)
EXERCISE 7.13: Grant a Role to a User		316	(1)
EXERCISE 7.14: Set a User's Default Role		317	(1)
EXERCISE 7.15: Enable and Disable a Role		318	(2)
Resource Limitation		320	(9)
Tablespace Quotas		320	(1)
EXERCISE 7.16: Providing Specific Tablespace Quotas for a User		320	(1)
Resource Limit Profiles		321	(1)
Managing Resource Limit Profiles		322	(14)
EXERCISE 7.17: Enable Resource Limitation		322	(1)
EXERCISE 7.18: Create a Profile		323	(1)
EXERCISE 7.19: Alter Profile Settings		324	(1)
EXERCISE 7.20: Manually Force a User's Password to Expire		325	(1)
EXERCISE 7.21: Use Password Complexity Checking		326	(1)
EXERCISE 7.22: Set a User's Profile		327	(1)
EXERCISE 7.23: Experiment with Password Management Settings		327	(1)
EXERCISE 7.24: Alter the Default Database Profile		328	(1)
Displaying Security Information		329	(2)
EXERCISE 7.25: Display Information About Users		329	(1)
EXERCISE 7.26: Display Information About Roles		329	(1)
EXERCISE 7.27: Display Information About Tablespace Quotas		330	(1)
EXERCISE 7.28: Display Information About Profiles		330	(1)
Chapter Summary		331	(2)
8 Manage Database Space		333	(40)
Chapter Prerequisites		334	(1)
Logical vs. Physical Storage Structures		334	(2)
Tablespaces and Data Files		336	(15)
Permanent, Temporary, and Undo Tablespaces		337	(1)
The SYSTEM Tablespace		337	(1)
The SYSAUX Tablespace		338	(1)
Other Tablespaces		338	(2)
Online and Offline Tablespaces		340	(1)
Read-Only and Read-Write Tablespaces		340	(1)
A Tablespace's Data Files		341	(1)
Use of Data File Space		342	(1)
Data File Sizes		342	(1)
Online and Offline Data Files		343	(1)
Creating and Managing Permanent Tablespaces and Data Files		343	(8)
EXERCISE 8.1: Display Database Storage Utilization		343	(1)
EXERCISE 8.2: Display Tablespace Information		344	(1)
EXERCISE 8.3: Display Data File Information		345	(1)
EXERCISE 8.4: Create a Permanent Tablespace		346	(2)
EXERCISE 8.5: Modify the Storage Properties of a Data File		348	(1)
EXERCISE 8.6: Control Tablespace Availability		348	(3)
Segments, Extents, and Data Blocks		351	(15)
Types of Segments in an Oracle Database		351	(1)
Data Blocks		352	(2)
Managing Storage for Tables and Indexes		354	(12)
EXERCISE 8.7: Display the Data Block Size Information		354	(1)
EXERCISE 8.8: Display the Segments in a Schema		355	(1)
EXERCISE 8.9: Compact (Shrink) Segments Wasting Space		356	(3)
EXERCISE 8.10: Create a Table in a Specific Tablespace		359	(1)
EXERCISE 8.11: Reserve Space for Updates to Existing Table Rows		359	(2)
EXERCISE 8.12: Manage Space for LOBs		361	(2)
EXERCISE 8.13: Perform Miscellaneous Space Management Tasks		363	(3)
Undo Tablespaces		366	(3)
Undo Segments		367	(1)
Undo Segments Are Multipurpose		367	(1)
Managing Undo Tablespaces and Retention Times		367	(2)
EXERCISE 8.14: Display and Modify Automatic Undo Management Parameters		368	(1)
Temporary Segments, Tablespaces, and Groups		369	(2)
Managing Temporary Tablespaces		370	(5)
EXERCISE 8.15: Reveal a Database's Default Temporary Tablespace		370	(1)
EXERCISE 8.16: Display Information About Temporary Tablespaces		370	(1)
EXERCISE 8.17: Display Information About Temp Files		370	(1)
Chapter Summary		371	(2)
9 Protect Your Oracle XE Database		373	(84)
About This Chapter's Practice Exercises		374	(1)
An Overview of Database Problems and Solutions		375	(6)
Instance Crashes and Crash Recovery		375	(2)
Media (Physical) Failures, Backups, and Recovery Manager		377	(1)
Logical Errors and Oracle Flashback		378	(2)
Devising a Database Protection Policy		380	(1)
Oracle Database Backup and Recovery Overview		381	(15)
The Flash Recovery Area		381	(2)
The Transaction Redo Log		383	(4)
The Database Control File		387	(1)
Database Backups		388	(2)
Recovery Manager		390	(6)
Configuring Oracle XE Database Protection		396	(23)
EXERCISE 9.1: Display Information About the Flash Recovery Area		396	(2)
EXERCISE 9.2: Display Information About the Control File		398	(1)
EXERCISE 9.3: Display Information About the Redo Log		398	(2)
EXERCISE 9.4: Create a Cold Database Backup		400	(6)
EXERCISE 9.5: Establish a Privileged Administrator Session with SQL*Plus		406	(1)
EXERCISE 9.6: Move the Flash Recovery Area and Online Redo Logs		407	(2)
EXERCISE 9.7: Enable Media Recovery		409	(1)
EXERCISE 9.8: Create a Hot Database Backup		410	(4)
EXERCISE 9.9: Confirm Successful Log Archiving		414	(1)
EXERCISE 9.10: Create Log Groups with Multiple Members		415	(2)
EXERCISE 9.11: Mirror the Database Control File		417	(2)
Backing Up Databases with Recovery Manager		419	(19)
EXERCISE 9.12: Start Recovery Manager		420	(1)
EXERCISE 9.13: Connect to the Target Database		420	(1)
EXERCISE 9.14: Display and Configure Persistent Recovery Manager Settings		421	(2)
EXERCISE 9.15: Back Up a Database		423	(2)
EXERCISE 9.16: Back Up an Individual Tablespace		425	(1)
EXERCISE 9.17: Back Up Archived Log Files		426	(2)
EXERCISE 9.18: List Information About Backup Sets		428	(2)
EXERCISE 9.19: Report Important Status Information for a Database		430	(2)
EXERCISE 9.20: Delete Obsolete Backup Sets		432	(1)
EXERCISE 9.21: Understand Flash Recovery Area Space Usage		433	(1)
EXERCISE 9.22: Build a New Backup Script		434	(4)
Additional Database Backup Topics		438	(2)
Backups of Other Database-Related Files		438	(1)
Logical Database Backups		438	(2)
More Database Recovery Options		440	(3)
Complete Recovery		440	(1)
Incomplete Recovery		441	(1)
What About Damage to Log Groups and the Control File?		442	(1)
Recovering Databases from Problems		443	(7)
EXERCISE 9.23: Simulate a System Crash and Perform Crash Recovery		443	(2)
EXERCISE 9.24: Simulate a Lost Data File and a Database Recovery		445	(5)
Using Oracle Flashback Features		450	(6)
EXERCISE 9.25: Restore a Dropped Table Using Oracle Flashback Drop		450	(1)
EXERCISE 9.26: Restore Lost Data Using Oracle Flashback Query		451	(5)
Chapter Summary		456	(1)
10 Tune Application and Database Instance Performance		457	(1)
Chapter Prerequisites		458	(1)
Oracle Tuning Concepts		458	(6)
Measures of Oracle Database Application Performance		459	(1)
Realistic and Unrealistic Performance Goals		459	(1)
Tunable Components		460	(1)
Tuning Methodologies		461	(2)
The Cost of Work		463	(1)
Tuning Statistics		464	(4)
Commonly Referenced Tuning Statistics		464	(2)
Instance Efficiency Ratios		466	(1)
The V$ Views		467	(1)
Statspack		467	(1)
Application Tuning		468	(26)
Complex SQL Statements		469	(1)
Data Access Methods and Indexes		470	(1)
Data Concurrency and Locking		470	(2)
Application-Tuning Exercises		472	(22)
EXERCISE 10.1: Install Statspack		473	(2)
EXERCISE 10.2: Take a Default Snapshot		475	(1)
EXERCISE 10.3: Simulate and Investigate the Problem		476	(4)
EXERCISE 10.4: Take Another Default Snapshot		480	(1)
EXERCISE 10.5: Review Statspack Snapshots		480	(1)
EXERCISE 10.6: Create a Statspack Report		480	(3)
EXERCISE 10.7: Analyze a Statspack Report		483	(2)
EXERCISE 10.8: Create Indexes for Filter Predicate Columns		485	(1)
EXERCISE 10.9: Explain the New Results		486	(1)
EXERCISE 10.10: Discover and Display Unindexed Foreign Keys		487	(2)
EXERCISE 10.11: Explain the New Execution Plan		489	(1)
EXERCISE 10.12: Gather New Statistics		490	(1)
EXERCISE 10.13: Analyze Other Application-Related Statistics		491	(3)
Database Instance Tuning		494	(9)
The System Global Area		494	(2)
Program Global Areas		496	(1)
Database Instance Tuning Exercises		497	(6)
EXERCISE 10.14: Display Current Overall Memory Utilization		497	(1)
EXERCISE 10.15: Display Information About the SGA		497	(3)
EXERCISE 10.16: Modify the Size of the SGA		500	(1)
EXERCISE 10.17: Display Information About PGA Memory		501	(1)
EXERCISE 10.18: Modify the Allowable PGA Memory		502	(1)
Chapter Summary		503	(2)
Index		505

Steve Bobrowski (Arroyo Grande, CA) is the CEO of Animated Learning Inc., a company that specializes in training Oracle professionals through its web-based information hub, OraWorld (www.oraworld.com), and multimedia courseware. Steve is an award-winning author who also writes and edits articles for several industry publications, including Oracle Magazine, DBMS, OReview, SELECT Magazine, and Database Programming & Design. Previously, Steve worked for Oracle Corp., where he assisted in the development of Oracle Server and Oracle's own documentation for Oracle7 and Oracle8. Steve is the author of Oracle8 Architecture.