Pro Oracle Database 11g Administration (3 page)

Implementing Reverse-Key Indexes .................................................................................................. 181

Creating Key-Compressed Indexes .................................................................................................... 182

Parallelizing Index Creation ............................................................................................................... 183

Avoiding Redo Generation When Creating an Index ........................................................................... 183

Implementing Invisible Indexes ......................................................................................................... 183

Using Index-Naming Standards ......................................................................................................... 185

Specifying Index Tablespaces ..................................................................................... 186

Placing Indexes in Tablespaces Separate from Tables ...................................................................... 186

Placing Indexes in Tablespaces Based on Extent Size ...................................................................... 187

Maintaining Indexes .................................................................................................... 187

Renaming an Index ............................................................................................................................ 188

Displaying Code to Re-create an Index .............................................................................................. 188

Rebuilding an Index ........................................................................................................................... 188

Making Indexes Unusable .................................................................................................................. 190

Monitoring Index Usage ..................................................................................................................... 190

xi

■ CONTENTS

Dropping an Index .............................................................................................................................. 191

Summary ..................................................................................................................... 191

■Chapter 9: Views, Synonyms, and Sequences .................................................. 193

Implementing Views ................................................................................................... 193

Creating a View .................................................................................................................................. 193

Checking Updates .............................................................................................................................. 194

Creating Read-Only Views ................................................................................................................. 195

Updatable Join Views ......................................................................................................................... 195

Creating an INSTEAD OF Trigger ........................................................................................................ 197

Modifying a View Definition ............................................................................................................... 198

Displaying the SQL Used to Create a View ......................................................................................... 198

Renaming a View ............................................................................................................................... 200

Dropping a View ................................................................................................................................. 200

Managing Synonyms ................................................................................................... 200

Creating a Synonym ........................................................................................................................... 201

Creating Public Synonyms ................................................................................................................. 201

Dynamically Generating Synonyms .................................................................................................... 202

Displaying Synonym Metadata ........................................................................................................... 202

Renaming a Synonym ........................................................................................................................ 203

Dropping a Synonym .......................................................................................................................... 204

Managing Sequences .................................................................................................. 204

Creating a Sequence .......................................................................................................................... 204

Using Sequence Pseudo-columns ..................................................................................................... 205

Autoincrementing Columns ................................................................................................................ 206

Using Multiple Sequences that Generate Unique Values ................................................................... 207

Using One Sequence or Many ............................................................................................................ 208

Viewing Sequence Metadata ............................................................................................................. 208

Renaming a Sequence ....................................................................................................................... 209

Dropping a Sequence ......................................................................................................................... 209

Resetting a Sequence ........................................................................................................................ 210

xii

■ CONTENTS

Summary ..................................................................................................................... 212

■Chapter 10: Data Dictionary Basics ................................................................... 213

Data-Dictionary Architecture ...................................................................................... 213

Static Views ....................................................................................................................................... 213

Dynamic Performance Views ............................................................................................................. 215

Derivable Documentation ................................................................................................................... 216

Logical and Physical Database Structures .................................................................. 218

Displaying User Information ........................................................................................ 220

Currently Connected User .................................................................................................................. 220

Users Currently Logged In .................................................................................................................. 221

Currently Executing SQL .................................................................................................................... 222

User Accounts in the Database .......................................................................................................... 223

Viewing Table Information .......................................................................................... 223

Viewing Accessible Tables ................................................................................................................. 224

Displaying Object Disk-Space Usage ................................................................................................. 224

Displaying Table Row Counts ............................................................................................................. 225

Displaying Index Information ...................................................................................... 227

Displaying Indexes for a Table ........................................................................................................... 227

Showing Foreign-Key Columns Not Indexed ...................................................................................... 228

Displaying Constraint Information ............................................................................... 229

Displaying Table Constraints .............................................................................................................. 229

Showing Primary-Key and Foreign-Key Relationships ....................................................................... 230

Viewing Basic Security Information ............................................................................ 232

Displaying Granted Roles ................................................................................................................... 232

Displaying System Privileges ............................................................................................................. 233

Displaying Object Privileges ............................................................................................................... 235

Displaying Object Dependencies ................................................................................. 236

Displaying Differences in Schemas ............................................................................ 238

Summary ..................................................................................................................... 241

xiii

■ CONTENTS

■Chapter 11: Large Objects ................................................................................. 243

Describing Current LOB Types .................................................................................... 243

Illustrating LOB Locators, Indexes, and Chunks .......................................................... 244

Distinguishing Between BasicFiles and SecureFiles .................................................. 246

BasicFile ............................................................................................................................................. 246

SecureFile .......................................................................................................................................... 246

Prerequisites for SecureFiles ............................................................................................................. 246

Creating a Table with a LOB Column .......................................................................... 248

Creating a BasicFile LOB Column ....................................................................................................... 248

Creating a LOB in a Specific Tablespace ........................................................................................... 249

Creating a SecureFile LOB Column .................................................................................................... 250

Creating a Partitioned LOB ................................................................................................................. 251

Maintaining LOB Columns ........................................................................................... 253

Moving a LOB Column ........................................................................................................................ 253

Adding a LOB Column ........................................................................................................................ 253

Removing a LOB Column .................................................................................................................... 253

Caching LOBs ..................................................................................................................................... 254

Storing LOBs In and Out of Line ......................................................................................................... 255

Using SecureFile Features .......................................................................................... 256

Compressing LOBs ............................................................................................................................. 256

Deduplicating LOBs ............................................................................................................................ 257

Encrypting LOBs ................................................................................................................................. 258

Migrating BasicFiles to SecureFiles ................................................................................................... 260

Viewing LOB Metadata ................................................................................................ 262

Loading LOBs .............................................................................................................. 263

Loading a CLOB .................................................................................................................................. 263

Loading a BLOB .................................................................................................................................. 265

Measuring LOB Space Consumed ............................................................................... 265