Pro Oracle Database 11g Administration (4 page)
BasicFile Space Used ......................................................................................................................... 266
SecureFile Space Used ...................................................................................................................... 267
xiv
■ CONTENTS
Summary ..................................................................................................................... 268
■Chapter 12: Partitioning: Divide and Conquer ................................................... 269
What Tables Should Be Partitioned? ........................................................................... 270
Creating Partitioned Tables ......................................................................................... 271
Partitioning by Range ......................................................................................................................... 272
Partitioning by List ............................................................................................................................. 279
Partitioning by Hash ........................................................................................................................... 280
Blending Different Partitioning Methods ............................................................................................ 281
Creating Partitions on Demand .......................................................................................................... 282
Partitioning to Match a Parent Table .................................................................................................. 284
Partitioning on a Virtual Column ........................................................................................................ 286
Giving an Application Control over Partitioning .................................................................................. 286
Maintaining Partitions ................................................................................................. 287
Viewing Partition Metadata ................................................................................................................ 287
Moving a Partition .............................................................................................................................. 288
Automatically Moving Updated Rows ................................................................................................. 289
Partitioning an Existing Table ............................................................................................................ 290
Adding a Partition ............................................................................................................................... 291
Exchanging a Partition with an Existing Table ................................................................................... 293
Renaming a Partition .......................................................................................................................... 295
Splitting a Partition ............................................................................................................................ 295
Merging Partitions .............................................................................................................................. 296
Dropping a Partition ........................................................................................................................... 297
Generating Statistics for a Partition ................................................................................................... 298
Removing Rows from a Partition ....................................................................................................... 298
Manipulating Data within a Partition .................................................................................................. 299
Partitioning Indexes .................................................................................................... 300
Partitioning an Index to Follow Its Table ............................................................................................ 300
Partitioning an Index Differently than Its Table .................................................................................. 303
Partition Pruning ......................................................................................................... 304
xv
■ CONTENTS
Summary ..................................................................................................................... 305
■Chapter 13: Data Pump ..................................................................................... 307
Data Pump Architecture .............................................................................................. 308
Exporting Data ............................................................................................................ 310
Creating a Database Directory ........................................................................................................... 310
Granting Access to the Directory ....................................................................................................... 311
Taking an Export ................................................................................................................................ 311
Importing Data ............................................................................................................ 312
Interactive Command Mode ........................................................................................ 313
Entering Interactive Command Mode ................................................................................................. 313
Attaching to a Running Job ................................................................................................................ 314
Stopping and Restarting a Job ........................................................................................................... 315
Terminating a Data Pump Job ............................................................................................................ 316
Tips for Getting Started ............................................................................................... 316
Use a Parameter File .......................................................................................................................... 316
Estimating the Size of Export Jobs .................................................................................................... 317
Listing the Contents of Dump Files .................................................................................................... 317
Transferring Data ........................................................................................................ 318
Exporting and Importing Directly Across the Network ....................................................................... 319
Copying Datafile(s) ............................................................................................................................. 320
Exporting and Importing Tablespaces and Datafiles ................................................... 321
Exporting Tablespace Metadata ......................................................................................................... 322
Specifying Different Datafile Paths and Names ................................................................................. 322
Changing Segment and Storage Attributes ........................................................................................ 322
Importing into a Different Tablespace from the Original .................................................................... 323
Changing the Size of Datafiles ........................................................................................................... 323
Filtering Data and Objects ........................................................................................... 324
Specifying a Query ............................................................................................................................. 324
Exporting a Percentage of the Data ................................................................................................... 325
Excluding Objects from the Export File .............................................................................................. 325
xvi
■ CONTENTS
Excluding Statistics ............................................................................................................................ 327
Including Only Specific Objects in an Export File ............................................................................... 327
Exporting Table, Index, Constraint, and Trigger DDL ......................................................................... 327
Excluding Objects from Import ........................................................................................................... 328
Including Objects in Import ................................................................................................................ 328
Common Data Pump Tasks ......................................................................................... 328
Creating a Consistent Export .............................................................................................................. 329
Importing When Objects Already Exist ............................................................................................... 330
Renaming a Table .............................................................................................................................. 331
Remapping Data ................................................................................................................................. 332
Cloning a User .................................................................................................................................... 333
Suppressing a Log File ....................................................................................................................... 333
Using Parallelism ............................................................................................................................... 334
Specifying Additional Dump Files ...................................................................................................... 335
Reusing Output File Names ................................................................................................................ 335
Creating a Daily DDL File .................................................................................................................... 336
Compressing Output .......................................................................................................................... 336
Encrypting Data .................................................................................................................................. 337
Monitoring Data Pump Jobs ........................................................................................ 338
Data Pump Log File ............................................................................................................................ 338
Data-Dictionary Views ........................................................................................................................ 338
Database Alert Log ............................................................................................................................. 339
Status Table ....................................................................................................................................... 339
Interactive Command-Mode Status ................................................................................................... 340
Operating-System Utilities ................................................................................................................. 340
Data Pump Legacy Mode ............................................................................................ 341
Data Pump Mapping to the exp Utility ................................................................................................ 341
Data Pump Mapping to the imp Utility ............................................................................................... 343
Summary ..................................................................................................................... 344
xvii
■ CONTENTS
■Chapter 14: External Tables .............................................................................. 345
SQL*Loader vs. External Tables .................................................................................. 345
Loading CSV Files into the Database .......................................................................... 347
Creating a Directory Object and Granting Access .............................................................................. 348
Creating an External Table ................................................................................................................. 348
Viewing External-Table Metadata ...................................................................................................... 349
Loading a Regular Table from the External Table .............................................................................. 349
Performing Advanced Transformations ...................................................................... 350
Viewing Text Files from SQL ....................................................................................... 352
Unloading and Loading Data Using an External Table ................................................ 354