Pro Oracle Database 11g Administration (2 page)

users.sql .............................................................................................................................................. 67

Organizing Scripts ......................................................................................................... 68

Step 1: Create Directories .................................................................................................................... 68

vii

■ CONTENTS

Step 2: Copy Files to Directories .......................................................................................................... 69

Step 3: Configure the Startup File ........................................................................................................ 69

Summary ....................................................................................................................... 70

■Chapter 4: Tablespaces and Datafiles ................................................................. 71

Understanding the First Five ......................................................................................... 72

Understanding the Need for More ................................................................................. 72

Creating Tablespaces .................................................................................................... 73

Renaming a Tablespace ................................................................................................ 76

Controlling the Generation of Redo ............................................................................... 76

Changing a Tablespace’s Write Mode ........................................................................... 77

Dropping a Tablespace ................................................................................................. 78

Using Oracle Managed Files ......................................................................................... 80

Creating a Bigfile Tablespace ....................................................................................... 81

Displaying Tablespace Size .......................................................................................... 81

Altering Tablespace Size ............................................................................................... 82

Toggling Datafiles Offline and Online ............................................................................ 83

Renaming or Relocating a Datafile ............................................................................... 85

Summary ....................................................................................................................... 90

■Chapter 5: Managing Control Files and Online Redo Logs .................................. 91

Managing Control Files ................................................................................................. 91

Viewing Control File Names and Locations .......................................................................................... 93

Adding a Control File ............................................................................................................................ 94

Moving a Control File ........................................................................................................................... 96

Removing a Control File ....................................................................................................................... 97

Managing Online Redo Logs ......................................................................................... 98

Displaying Online Redo-Log Information ............................................................................................ 100

Determining the Optimal Size of Online Redo-Log Groups ................................................................ 102

Determining the Optimal Number of Redo-Log Groups ...................................................................... 103

viii

■ CONTENTS

Adding Online Redo-Log Groups ........................................................................................................ 105

Resizing Online Redo-Log Groups ...................................................................................................... 105

Adding Online Redo-Log Files to a Group .......................................................................................... 106

Removing Online Redo-Log Files from a Group ................................................................................. 107

Moving or Renaming Redo-Log Files ................................................................................................. 107

Summary ..................................................................................................................... 108

■Chapter 6: Users and Basic Security ................................................................. 111

Understanding Schemas vs. Users ............................................................................. 111

Managing Default Users .............................................................................................. 112

Creating Users ............................................................................................................ 115

Choosing a User Name and Authentication Method ........................................................................... 115

Assigning Default Permanent and Temporary Tablespaces .............................................................. 116

Modifying Passwords .................................................................................................. 118

Enforcing Password Security ...................................................................................... 119

Logging On as a Different User ................................................................................... 120

Modifying Users .......................................................................................................... 122

Dropping Users ........................................................................................................... 122

Enforcing Password Security and Resource Limits .................................................... 123

Implementing Password Security ...................................................................................................... 124

Limiting Database Resource Usage ................................................................................................... 126

Managing Privileges .................................................................................................... 128

Assigning Database System Privileges .............................................................................................. 128

Assigning Database Object Privileges ................................................................................................ 129

Grouping and Assigning Privileges ............................................................................. 130

Summary ..................................................................................................................... 131

■Chapter 7: Tables and Constraints .................................................................... 133

Understanding Table Types ........................................................................................ 133

Creating a Table .......................................................................................................... 135

Creating a Heap-Organized Table ...................................................................................................... 135

ix

■ CONTENTS

Implementing Virtual Columns . ........................................................................................................... 138

Making Read-Only Tables .................................................................................................................... 140

Understanding Deferred Segment Creation . ....................................................................................... 141

Allowing for Parallel SQL Execution . ................................................................................................... 142

Compressing Table Data ...................................................................................................................... 143

Avoiding Redo Creation ........................................................................................................................ 144

Creating a Table from a Query . ........................................................................................................... 146

Modifying a Table ....................................................................................................... 146

Obtaining the Needed Lock . ................................................................................................................ 147

Renaming a Table ................................................................................................................................ 147

Adding a Column .................................................................................................................................. 147

Altering a Column ................................................................................................................................. 148

Renaming a Column ............................................................................................................................. 149

Dropping a Column .............................................................................................................................. 149

Displaying Table DDL .................................................................................................. 150

Dropping a Table . ....................................................................................................... 151

Undropping a Table . ................................................................................................... 152

Removing Data from a Table ...................................................................................... 153

Using DELETE ....................................................................................................................................... 153

Using TRUNCATE .................................................................................................................................. 153

Viewing and Adjusting the High-Water Mark .............................................................. 154

Tracing to Detect Space Below the High-Water Mark ........................................................................ 155

Using DBMS_SPACE to Detect Space Below the High-Water Mark . .................................................. 156

Creating a Temporary Table ....................................................................................... 159

Creating an Index-Organized Table ............................................................................ 161

Managing Constraints ................................................................................................. 161

Creating Primary-Key Constraints . ...................................................................................................... 162

Enforcing Unique Key Values ............................................................................................................... 163

Creating Foreign-key Constraints . ...................................................................................................... 164

Checking for Specific Data Conditions . ............................................................................................... 165

x

■ CONTENTS

Enforcing Not Null Conditions ............................................................................................................ 166

Disabling Constraints ......................................................................................................................... 167

Enabling Constraints .......................................................................................................................... 168

Summary ..................................................................................................................... 170

■Chapter 8: Indexes ............................................................................................ 171

Deciding When to Create an Index .............................................................................. 171

What to Think About .................................................................................................... 172

Index-Management Guidelines ................................................................................... 173

Creating Indexes ......................................................................................................... 175

Creating B-tree Indexes ..................................................................................................................... 175

Creating Concatenated Indexes ......................................................................................................... 176

Implementing Function-Based Indexes .............................................................................................. 177

Creating Unique Indexes .................................................................................................................... 178

Using Bitmap Indexes ........................................................................................................................ 179

Creating Bitmap Join Indexes ............................................................................................................ 180