Oracle Database Foundations : Technology Fundamentals for IT Success

by Bryla, Bob

• ISBN13: 9780782143720
• ISBN10: 0782143725
• Format: Paperback
• Copyright: 2004-10-12
• Publisher: Sybex

Summary

The world of IT is always evolving, but in every area there are stable, core concepts that anyone just setting out needed to know last year, needs to know this year, and will still need to know next year. The purpose of the Foundations series is to identify these concepts and present them in a way that gives you the strongest possible starting point, no matter what your endeavor. Oracle Database Foundations provides essential knowledge about installing, configuring, maintaining, and querying Oracle 9i and 10g databases. What you learn here will benefit you in the short term, as you acquire and practice your skills, and in the long term, as you use them. Topics covered include: Basic relational database concepts Reporting and querying using SQL*Plus and iSQL*Plus Creating and maintaining a database Managing user access and security Understanding Oracle database functions Using multiple tables in a query Restricting, sorting, and grouping data Optimizing database performance Creating backups Troubleshooting database errors

Author Biography

Bob Bryla, OCP, has worked with Oracle databases for over 10 years. He currently works as an Oracle DBA and Analyst for Lands' End in Dodgeville, Wisconsin.

Table of Contents

Introduction	xiii
Chapter 1 Relational Database Concepts	1	(12)
Are Spreadsheets Like Databases?	2	(1)
Relational Databases	3	(5)
Tables, Rows, and Columns	4	(1)
Primary Keys, Datatypes, and Foreign Keys	4	(2)
Data Modeling	6	(2)
Object-Relational Databases	8	(2)
Abstraction	9	(1)
Methods and Encapsulation	9	(1)
Inheritance	9	(1)
Object-Relational Support	10	(1)
Terms to Know	10	(1)
Review Questions	11	(2)
Chapter 2 SQL*Plus and iSQL*Plus Basics	13	(30)
Some SQL Formalities	14	(1)
Tools for Running SQL	14	(9)
SQL*Plus	14	(3)
iSQL*Plus	17	(3)
SQL*Plus Worksheet	20	(1)
Third-Party Tools	21	(1)
ODBC/JDBC	21	(2)
OCI	23	(1)
The Ubiquitous SELECT Statement	23	(6)
Column Specification	24	(2)
Column Renaming	26	(1)
Duplicate Removal	27	(1)
Expressions	28	(1)
DML for Making Changes	29	(5)
The UPDATE Statement	29	(2)
The INSERT Statement	31	(1)
The DELETE Statement	32	(1)
The MERGE Statement	33	(1)
DDL for Handling Database Objects	34	(5)
The CREATE Statement	34	(2)
The ALTER Statement	36	(1)
The DROP Statement	37	(1)
The RENAME Statement	37	(1)
The TRUNCATE Statement	38	(1)
DCL for Handling Privileges	39	(1)
The GRANT Statement	39	(1)
The REVOKE Statement	40	(1)
Terms to Know	40	(1)
Review Questions	41	(2)
Chapter 3 Oracle Database Functions	43	(26)
Query Basics	44	(5)
The DUAL Table	44	(2)
NULLs: What, When, Why, and How	46	(1)
String Literals and Concatenating Strings	47	(1)
Numeric Literals	48	(1)
Operators and Operator Precedence	48	(1)
Built-In Single-Row Functions	49	(14)
String Functions	50	(3)
Numeric Functions	53	(3)
Date Functions	56	(2)
Conversion Functions	58	(3)
General Functions	61	(2)
User-Defined Functions	63	(3)
Terms to Know	66	(1)
Review Questions	67	(2)
Chapter 4 Restricting, Sorting, and Grouping Data	69	(24)
The WHERE Clause	70	(11)
Comparison Conditions	71	(1)
AND, OR, and NOT	72	(3)
BETWEEN, IN, and LIKE	75	(4)
IS NULL and IS NOT NULL	79	(2)
The ORDER BY Clause	81	(2)
Group Functions and the GROUP BY Clause	83	(7)
Group Functions	84	(1)
The GROUP BY Clause	85	(2)
Using NVL with Group Functions	87	(1)
The HAVING Clause	88	(2)
Terms to Know	90	(1)
Review Questions	91	(2)
Chapter 5 Using Multiple Tables	93	(24)
Join Syntax: Out with the Old and In with the New (SQL:1999)	94	(1)
Equijoins	94	(9)
Pre-Oracle9i Equijoin Syntax	94	(3)
Oracle9i Equijoin Syntax	97	(4)
Non-equijoins	101	(1)
Pre-Oracle9i Non-equijoin Syntax	101	(1)
Oracle9i Non-equijoin Syntax	102	(1)
Outer Joins	103	(7)
Pre-Oracle9i Outer Join Syntax	103	(4)
Oracle9i Outer Join Syntax	107	(3)
Self-Joins	110	(2)
Pre-Oracle9i Self-Join Syntax	110	(1)
Oracle9i Self-Join Syntax	111	(1)
Cartesian Products: The Black Sheep of the Family	112	(2)
Pre-Oracle9i Cartesian Product Syntax	112	(1)
Oracle9i Cartesian Product Syntax	113	(1)
Terms to Know	114	(1)
Review Questions	115	(2)
Chapter 6 Advanced SQL Queries	117	(22)
Subqueries	118	(6)
Single-Row Subqueries	118	(1)
Multiple-Row Subqueries	119	(2)
Correlated Subqueries	121	(2)
Multiple-Column Subqueries	123	(1)
Set Operators	124	(9)
UNION and UNION ALL	124	(5)
INTERSECT	129	(1)
MINUS	130	(3)
ROLLUP and CUBE	133	(4)
ROLLUP	134	(1)
CUBE	135	(2)
Terms to Know	137	(1)
Review Questions	138	(1)
Chapter 7 Logical Consistency	139	(16)
Constraints	140	(10)
NOT NULL	140	(2)
CHECK	142	(2)
UNIQUE	144	(1)
PRIMARY KEY	145	(2)
FOREIGN KEY	147	(3)
Transaction Processing	150	(3)
The COMMIT Statement	151	(1)
The ROLLBACK Statement	152	(1)
The SAVEPOINT Statement	152	(1)
Terms to Know	153	(1)
Review Questions	154	(1)
Chapter 8 Installing Oracle and Creating a Database	155	(26)
Oracle Components Overview	156	(7)
Logical Storage Structures	156	(2)
Physical Storage Structures	158	(2)
Oracle Memory Structures	160	(2)
Background Processes	162	(1)
Installing Oracle Software	163	(6)
Using the Oracle Universal Installer	163	(4)
Using the Oracle Enterprise Manager Tools	167	(2)
Creating an Oracle Database	169	(10)
Disk and Memory Requirements	169	(1)
Using the Database Configuration Assistant	169	(10)
Terms to Know	179	(1)
Review Questions	180	(1)
Chapter 9 Reporting Techniques	181	(26)
ISQL*Plus Configuration	182	(8)
Interface Configuration	184	(1)
Script Formatting and System Variables	184	(5)
Change Password	189	(1)
Report Formatting	190	(8)
Headers and Footers	191	(3)
Column Formatting	194	(1)
BREAK Processing	195	(1)
Summary Operations (Totals)	196	(2)
Substitution Variables	198	(3)
Saving and Running Scripts	201	(3)
Terms to Know	204	(1)
Review Questions	205	(2)
Chapter 10 Creating and Maintaining Database Objects	207	(22)
Creating Tables	208	(7)
Relational Tables	208	(1)
Create Table As Select (CTAS)	209	(2)
External Tables	211	(3)
Temporary Tables	214	(1)
Creating Indexes	215	(1)
Creating and Using Views	216	(7)
User-Defined Views	216	(2)
Data Dictionary Views	218	(4)
Dynamic Performance Views	222	(1)
Creating Sequences and Synonyms	223	(3)
Sequences	223	(2)
Synonyms	225	(1)
Terms to Know	226	(1)
Review Questions	227	(2)
Chapter 11 Users and Security	229	(16)
Creating User Accounts	230	(4)
Assigning Passwords	230	(1)
Creating and Assigning Profiles	231	(1)
Assigning Default Tablespaces and Quotas	232	(2)
Granting and Revoking Privileges	234	(6)
System Privileges	234	(2)
Object Privileges	236	(2)
Creating and Assigning Roles	238	(2)
Auditing	240	(3)
Statement Auditing	240	(2)
Object Auditing	242	(1)
Terms to Know	243	(1)
Review Questions	244	(1)
Chapter 12 Making Things Run Fast (Enough)	245	(28)
Oracle's Tuning Methodology	246	(1)
Indexes	247	(8)
When to Create Indexes	247	(1)
Index Types	247	(3)
Creating, Dropping, and Maintaining Indexes	250	(2)
Monitoring Indexes	252	(2)
Data Dictionary Index Information	254	(1)
Data Design Tuning	255	(4)
Partitioned Tables	255	(3)
Materialized Views	258	(1)
SQL Application Tuning	259	(8)
Top SQL Tool	260	(2)
Explain Plan Graphical Tool	262	(2)
The Oracle Optimizer	264	(3)
Memory Tuning	267	(3)
Terms to Know	270	(1)
Review Questions	271	(2)
Chapter 13 Saving Your Stuff (Backups)	273	(22)
Database Failures	274	(1)
User Backup and Recovery Methods	274	(8)
Export and Import for Users	275	(5)
Flashback Query	280	(2)
DBA Backup and Recovery Methods	282	(10)
Export and Import for DBAs	282	(5)
Cold Backups	287	(1)
Hot Backups	287	(2)
Log Miner	289	(1)
Recovery Manager	290	(2)
Terms to Know	292	(1)
Review Questions	293	(2)
Chapter 14 Troubleshooting	295	(18)
The Alert Log File	296	(2)
Locating the Alert Log File	296	(1)
Viewing the Alert Log File	296	(1)
Maintaining the Alert Log File	297	(1)
Event Notification	298	(5)
Oracle9i OEM Event Manager	299	(3)
Oracle 10g Advisory Framework	302	(1)
System Trace Files	303	(2)
User Trace Files	305	(5)
Enabling Tracing	305	(1)
Locating the User Trace Files	306	(3)
Converting the Trace File	309	(1)
Terms to Know	310	(1)
Review Questions	311	(2)
Appendix A Answers to Review Questions	313	(14)
Chapter 1	313	(1)
Chapter 2	314	(1)
Chapter 3	315	(1)
Chapter 4	316	(1)
Chapter 5	317	(1)
Chapter 6	318	(1)
Chapter 7	319	(1)
Chapter 8	320	(1)
Chapter 9	321	(1)
Chapter 10	322	(1)
Chapter 11	323	(1)
Chapter 12	324	(1)
Chapter 13	325	(1)
Chapter 14	326	(1)
Appendix B Common Database Platforms	327	(4)
Enterprise Databases	327	(1)
Oracle	327	(1)
IBM DB2/UDB	327	(1)
Sybase	328	(1)
Microsoft SQL Server	328	(1)
Personal and Freeware Databases	328	(3)
Microsoft Access	328	(1)
MySQL	329	(2)
Glossary	331	(8)
Index	339