Establishing a Connection
Establishing a Connection
Network Resolution Methodologies
Local Naming Methodology
Client and Server network configuration files
The sqlnet.ora File
The tnsnames.ora File
The Listener Process
The listener.ora File
SQL*Plus command line tool
Calling a SQL Script from SQL*Plus
The Describe Command
Formatting Output in SQL*Plus
Preserving SQL*Plus Settings
Oracle Architecture
Oracle Architecture Overview
Program Global Area
Oracle Database
Physical Database Structure
Tablespaces and Data Files
Logical and Physical Database Structures
Segments, Extents and Blocks
SYSTEM and SYSAUX Tablespaces
Oracle Instance
System Global Area
Processing SQL Statements
Shared Pool
Library Cache
Data Dictionary Cache
Database Buffer Cache
Server Process and the Data Buffer Cache
Redo Log Buffer
Large Pool
Background Processes
Log Writer (LGWR)
Database Writer (DBWn)
Checkpoint (CKPT)
System Monitor (SMON)
Process Monitor (PMON)
Archiver (ARCn)
Getting Started with the Oracle Server
Database Administrator Users
Using SQL*Plus to Start Up and Shut Down
The Oracle Start up Sequence
The PFILE: initSID.ora
Initialization Parameter Examples
PFILE Example
The SPFILE: spfileSID.ora
Modifying Parameters in SPFILE
Creating an SPFILE
STARTUP Command Behaviour
Starting Up a Database NOMOUNT
Starting Up a Database MOUNT
STARTUP Command
The ALTER DATABASE Command
Opening a Database in Restricted Mode
Shutdown Modes
Clean Shutdown
Dirty Shutdown
Database Information sources
Dynamic Performance Views
Usage Examples
Considerations
Dynamic Performance Tables
Dynamic Performance Examples
Data Dictionary View Categories
Data Dictionary Examples
V$ Views verses DBA Views
Monitoring an Instance Using Diagnostic Files
Alert Log File
Background Trace Files
User Trace Files
Enabling or Disabling User Tracing
Creating a Database
Planning and Organizing a Database
Optimal Flexible Architecture (OFA)
Oracle Software and File Locations
Creation Prerequisites
Windows: Creating a Service
Authentication Methods for Database Administrators
Using Password File Authentication
Operating System Environment
Creating a Database Manually
CREATE DATABASE Command
Troubleshooting
After Database Creation
Data Dictionary
Data Dictionary: Overview
Data Dictionary Views
Data Dictionary: Usage Examples
Built-In Database Objects
The Data Dictionary
Base Tables and Data Dictionary Views
Creating Data Dictionary Views
Data Dictionary Contents
How the Data Dictionary Is Used
Administrative Script Naming Conventions
Maintaining the Control File
Function of the Control File
Control File Contents
Why Multiplex the Control File
Multiplexing the Control File When Using SPFILE
Multiplexing the Control File When Using PFILE
Obtaining Control File Information
Maintaining Online Redo Log Files
Using Online Redo Log Files
Structure of Online Redo Log Files
How Online Redo Log Files Work
Forcing Log Switches and Checkpoints
Adding Online Redo Log File Groups
Adding Online Redo Log File Members
Dropping Online Redo Log File Groups
Dropping Online Redo Log File Members
Relocating or Renaming Online Redo Log Files
Clearing Online Redo Log Files
Online Redo Log File Configuration
Obtaining Group and Member Information
Archivelog Mode
Managing Tablespaces and Data Files
Storage Concepts: Physical Relations
Tablespaces and Data Files
Creating Tablespaces
Resizing a Tablespace
Enabling Automatic Extension of Data Files
Manually Resizing a Data File
Adding Data Files to a Tablespace
Bigfile Tablespaces
Space Management in Tablespaces
Locally Managed Tablespaces
Dictionary-Managed Tablespaces
Multiple Block Size Support
Standard Block Size
Non-standard Block Size
Creating Non-standard Block Size Tablespaces
Read-Only Tablespaces
Taking a Tablespace Offline
Methods for Moving Data Files
Dropping Tablespaces
Temporary Tablespaces
Default Temporary Tablespace
Restrictions on Default Temporary Tablespace
Undo Tablespace
Obtaining Tablespace Information
Storage Structure and Relationships
Storage Concepts
How Table Data Is Stored
Types of Segments
Extent Allocation and De-allocation
The Database Block
Database Block Contents
Data Block Utilization
Row Migration and Row Chaining
Block Space Utilization Parameters
Block Space Usage
Automatic Segment-Space Management
Configuring Automatic Segment-Space Management
Obtaining Storage Information
Managing Undo Data
Managing Undo Data
Objectives
Data Manipulation
Undo Segment
Undo Segments: Purpose
Read Consistency
Transactions and Undo Data
Automatic Undo
Management: Concepts
Administering Undo
Monitoring Undo
UNDO Tablespace
Altering an UNDO Tablespace
Switching UNDO Tablespaces
Dropping an UNDO Tablespace
Configuring Undo Retention
Guaranteeing Undo Retention
Undo Data Statistics
Sizing an UNDO Tablespace
Undo Data Versus Redo Data
Flashback Query
Flashback Versions Query:
Flashback_Transaction_Query
Obtaining Undo Segment Information
Managing Tables
What Is a Schema?
Naming Database Objects
Storing User Data
Oracle Built-in Data Types
Specifying Data Types in Tables
ROWID Format
Structure of a Row
Setting PCTFREE and PCTUSED
Row Migration and Chaining
Creating a Table
Creating a Table: Guidelines
Extents Utilization
Block utilization
Adding a Column
Renaming a Column
Dropping a Column
Using the UNUSED Option
Truncating a Table
Dropping a Table
Flashback Drop and the Recycle Bin
Recycle Bin
Restoring Tables from the Recycle Bin
Space Reclamation
Recycle Bin: Manual Space Reclamation
Bypassing the Recycle Bin
Querying the Recycle Bin
Querying Data from Dropped Tables
Temporary Tables
Temporary Tables: Considerations
Obtaining Table Information
Managing Indexes
Classification of Indexes
B-Tree Index
Bitmap Indexes
Comparing B-Tree and Bitmap Indexes
Creating B-Tree Indexes
Creating Indexes: Guidelines
Creating Bitmap Indexes
Allocating and De-allocating Index Space
Coalescing Indexes
Rebuilding Indexes
Rebuilding Indexes Online
Checking Indexes
Dropping Indexes
Identifying Unused Indexes
Obtaining Index Information
Maintaining Data Integrity
Types of Constraints
Understanding Data Integrity
Constraint States
Constraint Checking
Defining Constraints
Immediate or Deferred
Primary and Unique Key Enforcement
Foreign Key Considerations
Defining Constraints While Creating a Table
Adding Constraints: Alter Table Statement
Guidelines for Defining Constraints
Enabling Constraints
Renaming Constraints
Using the EXCEPTIONS Table
Obtaining Constraint Information
Managing Passwords and Resources
Profiles
Password Management
Enabling Password Management
Password Account Locking
Password Expiration and Aging
Password History
Password Verification
User-Provided Password Function
Oracle's Password Verification Function VERIFY_FUNCTION
Creating a Profile:
Altering a Profile
Dropping a Profile
Profile Enabled Resource Limits
Enabling Resource Limits
Setting Resource Limits
Obtaining Password and Resource Limit Information
Managing Users
Users and Security
Database Schema
Checklist for Creating Users
Creating a New User:
Database Authentication
Operating System Authentication
Changing User Quota on Tablespaces
Dropping a User
Obtaining User Information
Managing Privileges
Managing Privileges
System Privileges
Granting System Privileges
SYSDBA and SYSOPER
System Privilege Restrictions
Revoking System Privileges
System Privileges with the ADMIN OPTION
Object Privileges
Granting Object Privileges
Revoking Object Privileges
Revoking Object Privileges with GRANT OPTION
Obtaining Privileges Information
Managing Roles
Benefits of Roles
Creating Roles
Predefined Roles
Modifying Roles
Assigning Roles
Establishing Default Roles
Application Roles
Enabling and Disabling Roles
Revoking Roles from Users
Removing Roles
Guidelines for Creating Roles
Guidelines for Using Passwords and Default Roles
Obtaining Role Information
Globalization Support Features
Encoding Schemes
Database Character Sets and National Character Sets
Guidelines for Choosing an Oracle Database Character Set
Guidelines for Choosing an Oracle National Character Set
Specifying Language-Dependent Behaviour
Specifying Language-Dependent Behaviour for the Server
Dependent Language and Territory Default Values
Specifying Language-Dependent Behaviour for the Session
Linguistic Sorting
NLS Sorting
Using NLS Parameters in SQL Functions
Linguistic Index Support
Import and Loading Data Using NLS
Obtaining Character Set Information
Obtaining NLS Settings Information
Data Pump
General Architecture
Directory Objects
Creating Directory Objects
Data Pump: Benefits
Data Pump Export and Import
Data Pump Interfaces and Modes
Fine-Grained Object Selection.
Advanced Feature: Sampling
Data Pump File Locations
Data Pump Import
Data Pump Import: Transformations
Data Pump: Performance Consideration
Data Pump Access Path: Considerations
External Table Population
Using External Tables
External Table Population with ORACLE_DATAPUMP
External Table Population with ORACLE_LOADER