Oracle 9i


Apply Now

Content

Preface

Introduction

1 Oracle Architectural Components

Objectives 1-2

Overview of Primary Components 1-3

Oracle Server 1-5

Oracle Instance 1-6

Establishing a Connection and Creating a Session 1-7

Oracle Database 1-9

Physical Structure 1-10

Memory Structure 1-11

System Global Area 1-12

Shared Pool 1-15

Library Cache 1-16

Data Dictionary Cache 1-17

Database Buffer Cache 1-18

Redo Log Buffer 1-21

Large Pool 1-22

Java Pool 1-24

Program Global Area 1-25

Process Structure 1-28

User Process 1-29

Server Process 1-30

Background Processes 1-31

Database Writer (DBWn) 1-32

Log Writer (LGWR) 1-33

System Monitor (SMON) 1-34

Process Monitor (PMON) 1-35

Checkpoint (CKPT) 1-36

Archiver (ARCn) 1-37

Logical Structure 1-39

Processing SQL Statements 1-42

Summary 1-44

Practice 1 Overview 1-45

2 Getting Started with the Oracle Server

Objectives 2-2

Database Administration Tools 2-3

Oracle Universal Installer 2-4

Starting the Universal Installer 2-5

Non-Interactive Installation Using Response Files 2-6

Oracle Database Configuration Assistant 2-9

Database Administrator Users 2-10

SQL*Plus 2-12

Oracle Enterprise Manager 2-13

Contents

iii

Oracle Enterprise Manager: Architecture 2-14

Console 2-16

Summary 2-18

Practice 2 Overview 2-19

3 Managing an Oracle Instance

Objectives 3-2

Initialization Parameter Files 3-3

PFILE initSID.ora 3-6

Creating a PFILE 3-7

PFILE Example 3-8

SPFILE spfileSID.ora 3-9

Creating an SPFILE 3-10

SPFILE Example 3-13

Modifying Parameters in SPFILE 3-14

STARTUP Command Behavior 3-17

Starting Up a Database NOMOUNT 3-19

Starting Up a Database MOUNT 3-20

Starting Up a Database OPEN 3-21

STARTUP Command 3-22

ALTER DATABASE Command 3-25

Opening a Database in Restricted Mode 3-26

Opening a Database in Read-Only Mode 3-29

Shutting Down the Database 3-31

Shutdown Options 3-32

Monitoring an Instance Using Diagnostic Files 3-36

Alert Log File 3-37

Background Trace Files 3-39

User Trace Files 3-40

Enabling or Disabling User Tracing 3-41

Summary 3-43

Practice 3 Overview 3-44

4 Creating a Database

Objectives 4-2

Planning and Organizing a Database 4-3

Optimal Flexible Architecture (OFA) 4-4

Oracle Software and File Locations 4-5

Creation Prerequisites 4-6

Authentication Methods for Database Administrators 4-7

Using Password File Authentication 4-8

iv

Creating a Database 4-10

Operating System Environment 4-11

Database Configuration Assistant 4-12

Creating a Database Using Database Configuration Assistant 4-13

Creating a Database Manually 4-16

Creating a Database Using Oracle Managed Files (OMF) 4-19

CREATE DATABASE Command 4-23

Troubleshooting 4-26

After Database Creation 4-27

Summary 4-28

Practice 4 Overview 4-29

5 Using Data Dictionary and Dynamic Performance Views

Objectives 5-2

Built-In Database Objects 5-3

Data Dictionary 5-4

Base Tables and Data Dictionary Views 5-5

Creating Data Dictionary Views 5-6

Data Dictionary Contents 5-7

How the Data Dictionary Is Used 5-8

Data Dictionary View Categories 5-9

Data Dictionary Examples 5-11

Dynamic Performance Tables 5-12

Dynamic Performance Examples 5-13

Administrative Script Naming Conventions 5-15

Summary 5-17

Practice 5 Overview 5-18

6 Maintaining the Control File

Objectives 6-2

Control File 6-3

Control File Contents 6-5

Multiplexing the Control File 6-7

Multiplexing the Control File When Using SPFILE 6-8

Multiplexing the Control File When Using PFILE 6-9

Managing Control Files with OMF 6-10

Obtaining Control File Information 6-11

Summary 6-14

Practice 6 Overview 6-15

v

7 Maintaining Online Redo Log Files

Objectives 7-2

Using Online Redo Log Files 7-3

Structure of Online Redo Log Files 7-4

How Online Redo Log Files Work 7-6

Forcing Log Switches and Checkpoints 7-8

Adding Online Redo Log File Groups 7-9

Adding Online Redo Log File Members 7-10

Dropping Online Redo Log File Groups 7-12

Dropping Online Redo Log File Members 7-13

Relocating or Renaming Online Redo Log Files 7-15

Clearing Online Redo Log Files 7-17

Online Redo Log File Configuration 7-18

Managing Online Redo Log Files with OMF 7-20

Obtaining Group and Member Information 7-21

Archived Redo Log Files 7-23

Summary 7-27

Practice 7 Overview 7-28

8 Managing Tablespaces and Data Files

Objectives 8-2

Tablespaces and Data Files 8-3

Types of Tablespaces 8-4

Creating Tablespaces 8-5

Space Management in Tablespaces 8-9

Locally Managed Tablespaces 8-10

Dictionary-Managed Tablespaces 8-12

Migrating a Dictionary-Managed SYSTEM Tablespace 8-13

Undo Tablespace 8-14

Temporary Tablespaces 8-15

Default Temporary Tablespace 8-18

Creating a Default Temporary Tablespace 8-19

Restrictions on Default Temporary Tablespace 8-22

Read-Only Tablespaces 8-23

Taking a Tablespace Offline 8-26

Changing Storage Settings 8-29

Resizing a Tablespace 8-31

Enabling Automatic Extension of Data Files 8-33

Manually Resizing a Data File 8-36

Adding Data Files to a Tablespace 8-37

vi

Methods for Moving Data Files 8-39

Dropping Tablespaces 8-42

Managing Tablespaces Using OMF 8-45

Obtaining Tablespace Information 8-47

Summary 8-48

Practice 8 Overview 8-49

9 Storage Structure and Relationships

Objectives 9-2

Storage and Relationship Structure 9-3

Types of Segments 9-4

Storage Clause Precedence 9-8

Extent Allocation and Deallocation 9-9

Used and Free Extents 9-10

Database Block 9-11

Multiple Block Size Support 9-12

Standard Block Size 9-13

Nonstandard Block Size 9-15

Creating Nonstandard Block Size Tablespaces 9-17

Multiple Block Sizing Rules 9-19

Database Block Contents 9-20

Block Space Utilization Parameters 9-21

Data Block Management 9-23

Automatic Segment-Space Management 9-24

Configuring Automatic Segment-Space Management 9-26

Manual Data Block Management 9-27

Block Space Usage 9-28

Obtaining Storage Information 9-29

Summary 9-32

Practice 9 Overview 9-33

10 Managing Undo Data

Objectives 10-2

Managing Undo Data 10-3

Undo Segment 10-4

Undo Segments: Purpose 10-5

Read Consistency 10-6

Types of Undo Segments 10-7

Automatic Undo Management: Concepts 10-9

vii

Automatic Undo Management: Configuration 10-10

Automatic Undo Management: Initialization Parameters 10-11

Automatic Undo Management: UNDO Tablespace 10-12

Automatic Undo Management: Altering an UNDO Tablespace 10-14

Automatic Undo Management: Switching UNDO Tablespaces 10-16

Automatic Undo Management: Dropping an UNDO Tablespace 10-18

Automatic Undo Management: Other Parameters 10-21

Undo Data Statistics 10-23

Automatic Undo Management: Sizing an UNDO Tablespace 10-24

Automatic Undo Management: Undo Quota 10-26

Obtaining Undo Segment Information 10-27

Summary 10-29

Practice 10 Overview 10-30

11 Managing Tables

Objectives 11-2

Storing User Data 11-3

Oracle Built-in Data Types 11-6

ROWID Format 11-10

Structure of a Row 11-12

Creating a Table 11-13

Creating a Table: Guidelines 11-17

Creating Temporary Tables 11-18

Setting PCTFREE and PCTUSED 11-19

Row Migration and Chaining 11-20

Changing Storage and Block Utilization Parameters 11-21

Manually Allocating Extents 11-24

Nonpartitioned Table Reorganization 11-25

Truncating a Table 11-26

Dropping a Table 11-27

Dropping a Column 11-29

Renaming a Column 11-31

Using the UNUSED Option 11-33

Obtaining Table Information 11-35

Summary 11-37

Practice 11 Overview 11-38

12 Managing Indexes

Objectives 12-2

Classification of Indexes 12-3

B-Tree Index 12-5

viii

Bitmap Indexes 12-7

Comparing B-Tree and Bitmap Indexes 12-9

Creating B-Tree Indexes 12-10

Creating Indexes: Guidelines 12-13

Creating Bitmap Indexes 12-15

Changing Storage Parameters for Indexes 12-18

Allocating and Deallocating Index Space 12-20

Rebuilding Indexes 12-21

Rebuilding Indexes Online 12-23

Coalescing Indexes 12-24

Checking Index Validity 12-25

Dropping Indexes 12-27

Identifying Unused Indexes 12-29

Obtaining Index Information 12-30

Summary 12-31

Practice 12 Overview 12-32

13 Maintaining Data Integrity

Objectives 13-2

Data Integrity 13-3

Types of Constraints 13-5

Constraint States 13-6

Constraint Checking 13-8

Defining Constraints Immediate or Deferred 13-9

Primary and Unique Key Enforcement 13-10

Foreign Key Considerations 13-11

Defining Constraints While Creating a Table 13-13

Guidelines for Defining Constraints 13-17

Enabling Constraints 13-18

Renaming Constraints 13-23

Using the EXCEPTIONS Table 13-25

Obtaining Constraint Information 13-28

Summary 13-31

Practice 13 Overview 13-32

14 Managing Password Security and Resources

Objectives 14-2

Profiles 14-3

Password Management 14-5

Enabling Password Management 14-6

Password Account Locking 14-7

Password Expiration and Aging 14-8

Password History 14-9

ix

Password Verification 14-10

User-Provided Password Function 14-11

Password Verification Function VERIFY_FUNCTION 14-12

Creating a Profile: Password Settings 14-13

Altering a Profile: Password Setting 14-17

Dropping a Profile: Password Setting 14-19

Resource Management 14-21

Enabling Resource Limits 14-22

Setting Resource Limits at Session Level 14-23

Setting Resource Limits at Call Level 14-24

Creating a Profile: Resource Limit 14-25

Managing Resources Using the Database Resource Manager 14-28

Resource Plan Directives 14-31

Obtaining Password and Resource Limit Information 14-33

Summary 14-35

Practice 14 Overview 14-36

15 Managing Users

Objectives 15-2

Users and Security 15-3

Database Schema 15-5

Checklist for Creating Users 15-6

Creating a New User: Database Authentication 15-7

Creating a New User: Operating System Authentication 15-10

Changing User Quota on Tablespaces 15-12

Dropping a User 15-14

Obtaining User Information 15-16

Summary 15-17

Practice 15 Overview 15-18

16 Managing Privileges

Objectives 16-2

Managing Privileges 16-3

System Privileges 16-4

System Privileges: Examples 16-5

Granting System Privileges 16-6

SYSDBA and SYSOPER Privileges 16-9

System Privilege Restrictions 16-10

Revoking System Privileges 16-11

Revoking System Privileges with the ADMIN OPTION 16-13

Object Privileges 16-14

Granting Object Privileges 16-15

Revoking Object Privileges 16-18

Revoking Object Privileges with GRANT OPTION 16-21

x

Obtaining Privileges Information 16-22

Summary 16-23

Practice 16 Overview 16-24

17 Managing Roles

Objectives 17-2

Roles 17-3

Benefits of Roles 17-4

Creating Roles 17-5

Predefined Roles 17-7

Modifying Roles 17-8

Assigning Roles 17-10

Establishing Default Roles 17-13

Application Roles 17-15

Enabling and Disabling Roles 17-16

Revoking Roles from Users 17-19

Removing Roles 17-21

Guidelines for Creating Roles 17-23

Guidelines for Using Passwords and Default Roles 17-24

Obtaining Role Information 17-25

Summary 17-26

Practice 17 Overview 17-27

18 Auditing

Objectives 18-2

Auditing 18-3

Auditing Guidelines 18-4

Auditing Categories 18-6

Database Auditing 18-8

Auditing Options 18-10

Auditing User SYS 18-12

Obtaining Auditing Information 18-13

Obtaining Audit Records Information 18-14

Summary 18-15

Practice 18 Overview 18-16

19 Loading Data into a Database

Objectives 19-2

Data Loading Methods 19-3

Direct Load 19-4

Serial Direct Load 19-6

Parallel Direct Load 19-7

SQL*Loader 19-9

Using SQL*Loader 19-11

xi

SQL*Loader Control File 19-13

Control File Syntax Considerations 19-17

Input Data and Data Files 19-18

Logical Records 19-22

Loading Methods 19-23

Comparing Direct and Conventional Path Loads 19-26

Parallel Direct Path Load 9-28

Data Conversion 19-29

Discarded or Rejected Records 19-30

Log File Contents 19-34

SQL*Loader Guidelines 19-36

Summary 19-37

Practice 19 Overview 19-38

20 Using Globalization Support

Objectives 20-2

Globalization Support Features 20-3

Encoding Schemes 20-5

Database Character Sets and National Character Sets 20-8

Guidelines for Choosing an Oracle Database Character Set 20-9

Guidelines for Choosing an Oracle National Character Set 20-11

Choosing a Unicode Solution:Unicode Database 20-12

Choosing a Unicode Solution:Unicode Data Type 20-13

Specifying Language-Dependent Behavior 20-15

Specifying Language-Dependent Behavior for the Server 20-16

Dependent Language and Territory Default Values 20-17

Specifying Language-Dependent Behavior for the Session 20-19

Linguistic Sorting 20-23

NLS Sorting 20-24

Using NLS Parameters in SQL Functions 20-27

Linguistic Index Support 20-31

Import and Loading Data Using NLS 20-32

Using NLS Parameters in SQL Functions 20-33

Obtaining Character Set Information 20-34

Obtaining NLS Settings Information 20-35

Summary 20-39

Practice 20 Overview 20-40

xii

Appendix A: How to Create an Oracle9i Database in a UNIX Environment

Appendix B: Manually Managing Undo Data (Rollback Segments)

Appendix C: Practice Solutions for SQL*Plus

Appendix D: Practice Solutions for Oracle Enterprise Manager