Table Of ContentOracle DBA Code Examples
Version Date: 30 July 2011
Editor: Ahmed Baraka
Page 1 Oracle DBA Code Examples
Document Purpose
This document is edited to be a quick reference of code examples used to achieve specific
Oracle DBA tasks. No explanation on any subject is presented.
The document is simply oriented based on the required task, the code to perform the task and
any precautions or warnings when using the code. Also, it will be specified if the code is
version specific. The document mainly demonstrates using SQL and PL/SQL code to achieve
any task. It does not concentrate on using OEM to perform a task. However, if there will be a
significant advantage, there could be just some hints on using OEM for some tasks.
Prerequisites
The document assumes that the reader has already the knowledge of Oracle database
administration.
How to Use the Document
1. Go to Contents section
2. Search the required task
3. Click on the required task link
4. Read the warnings and/or usage guideline, if any.
5. Make any modification in the code to match your case.
Oracle Database Versions
The code presented in the document is to operate on Oracle database versions 10g and 11g. It
will be stated, if the code is version specific.
Obtaining Latest Version of the Document
Latest version can be obtained from my site or by emailing me at
Document Parts
Part 1 Oracle DBA Fundamentals _______________________ 36
Part 2 Oracle Database Net Services ___________________ 238
Part 3 Oracle Database Backup and Recovery ____________ 247
Part 4 Oracle Database Security _______________________ 311
Part 5 Oracle Database Performance Tuning _____________ 340
Part 6 Oracle Automatic Storage Management (ASM) ______ 396
Part 7 Oracle Real Application Cluster __________________ 410
Part 8 Oracle RAC One Node __________________________ 529
Part 9 Oracle Warehousing ___________________________ 551
Part 10 Oracle Database Utilities _______________________ 556
Part 11 Miscellaneous Oracle Database Topics _____________ 582
Part 12 PL/SQL Samples ______________________________ 596
Part 13 Appendixes__________________________________ 674
Page 3 Oracle DBA Code Examples
Contents
Part 1 Oracle DBA Fundamentals _______________________ 36
DBA: Best Practices ____________________________________ 37
DBA: Best Practice Guidelines for Standalone and RAC Databases____________37
Oracle Database Installation Consideration __________________ 38
Estimating Disk and Memoery Requirements ____________________________38
Optimal Flexible Architecture_________________________________________38
Oracle Products Installed with the 11.1 Release __________________________39
Installing Oracle 10g R2 on Enterprise Linux 4 _______________ 40
Installation Environment ____________________________________________40
Required Software _________________________________________________40
Used Hardware ___________________________________________________40
Installation Plan___________________________________________________40
1. Preinstallation tasks __________________________________________________ 40
2. Oracle Database 10g Software Installation_________________________________ 44
3. Apply Patchset 3 (10.2.0.4) for Clusterware and Database Software_____________ 44
4. Configure Listeners ___________________________________________________ 45
5. Create Database _____________________________________________________ 45
6. Postinstallation Tasks _________________________________________________ 45
Installing Oracle 11g R2 on Enterprise Linux 5 _______________ 47
Installation Environment ____________________________________________47
Required Software _________________________________________________47
Used Hardware ___________________________________________________47
Installation Plan___________________________________________________47
1. Preinstallation tasks __________________________________________________ 47
2. Oracle Database 11g Software Installation_________________________________ 53
3. Apply Patchset_______________________________________________________ 53
4. Configure Listeners ___________________________________________________ 53
5. Create Database _____________________________________________________ 53
6. Postinstallation Tasks _________________________________________________ 53
Page 4 Oracle DBA Code Examples
Installing Oracle 11g R2 on Enterprise Linux 5.5 with ASM ______ 54
Installation Environment ____________________________________________54
Required Software _________________________________________________54
Used Hardware ___________________________________________________54
Installation Plan___________________________________________________54
1. Preinstallation tasks __________________________________________________ 55
2. Oracle Grid Infrastructure installation_____________________________________ 60
3. Oracle Grid Infrastructure Patching_______________________________________ 61
4. Oracle Database 11g R2 Software Installation ______________________________ 61
5. Oracle Database 11g R2 Software Patching ________________________________ 62
6. Install EM Agent in cluster nodes (if required) ______________________________ 62
7. ASM Diskgroups Creation ______________________________________________ 62
8. Database Creation____________________________________________________ 62
9. Postinstallation tasks__________________________________________________ 62
10. General Useful Postinstallation Tasks in Linux _____________________________ 62
Managing Oracle Database Instance _______________________ 64
Product Release Number ____________________________________________64
Oracle Database Release Number Format____________________________________ 64
Obtaining License Information________________________________________64
Managing the Instance Architecture ___________________________________64
Obtaining Information about the Instance Processes ___________________________ 64
Obtaining Information about the SGA _______________________________________ 65
Clearing the Buffer Cache ________________________________________________ 65
Database Administration Authentication ________________________________65
Using Operating System Authentication _____________________________________ 65
Using Password File Authentication_________________________________________ 66
Identifying Users SYSDBA or SYSOPER Users_________________________________ 66
Data Dictionary and Dynamic Performance Views_________________________66
Data Dictionary Creation _________________________________________________ 66
Startup and Shutdown______________________________________________66
Startup Levels _________________________________________________________ 66
Shutdown Levels _______________________________________________________ 66
Autostart of Database in Windows _________________________________________ 66
Automatically Starting Databases in Unix ____________________________________ 66
Page 5 Oracle DBA Code Examples
Quiescing a Database ______________________________________________71
Suspending a Database _____________________________________________71
Dropping a Database _______________________________________________71
Initialization Files__________________________________________________71
Managing Initialization Files ______________________________________________ 71
Managing Parameters in SPFILE ___________________________________________ 72
Alert and Trace Files _______________________________________________72
Monitoring Alert and Trace Files ___________________________________________ 72
Managing Oracle Database Physical Structure ________________ 74
Managing Control Files______________________________________________74
Obtaining Control File information__________________________________________ 74
Creating Additional Copies, Renaming, and Relocating Control Files _______________ 74
Creating New Control Files _______________________________________________ 74
Backing Up Control Files _________________________________________________ 75
Manage the Size of Control Files ___________________________________________ 75
Multiplexing the Control File ______________________________________________ 76
Maintaining Online Redo Log Files _____________________________________76
Forcing Log Switches and Checkpoints ______________________________________ 76
Adding Online Redo Log File Groups ________________________________________ 76
Adding Online Redo Log File Members ______________________________________ 76
Dropping Online Redo Log File Groups ______________________________________ 76
Dropping Online Redo Log File Members_____________________________________ 76
Relocating and Renaming Redo Log Members_________________________________ 76
Verifying Blocks in Redo Log Files __________________________________________ 77
Clearing a Redo Log File _________________________________________________ 77
Viewing Redo Log Information_____________________________________________ 77
Managing Archived Redo Logs ________________________________________77
Obtaining Information about Archive Log ____________________________________ 77
Changing the Database Archiving Mode _____________________________________ 78
Specifying Archive Destinations and their Options _____________________________ 78
Specifying the Minimum Number of Successful Destinations _____________________ 79
Controlling Archiving to a Destination_______________________________________ 79
Controlling Trace Output Generated by the Archivelog Process ___________________ 79
Managing Tablespaces______________________________________________79
Obtaining Tablespace Information__________________________________________ 79
Page 6 Oracle DBA Code Examples
Creating a Locally Managed Tablespace _____________________________________ 83
Specifying Segment Space Management_____________________________________ 83
Adding Space to Tablespace ______________________________________________ 83
Specifying Nonstandard Block Sizes for Tablespaces ___________________________ 83
Using Bigfile Tablespace (BFT) ____________________________________________ 83
Using Temporary Tablespace______________________________________________ 84
Renaming a Tempfile____________________________________________________ 84
Shrinking Temporary Tablespace __________________________________________ 84
Using Default Temporary Tablespace _______________________________________ 85
Using Temporary Tablespace Groups _______________________________________ 85
Suppressing Redo Generation for a Tablespace _______________________________ 85
Controlling Tablespaces Availability_________________________________________ 85
Using Read-Only Tablespaces _____________________________________________ 85
Renaming Tablespaces __________________________________________________ 85
Default Permanent Tabelspace ____________________________________________ 85
Dropping Tablespaces ___________________________________________________ 86
Managing the SYSAUX Tablespace _________________________________________ 86
Diagnosing and Repairing Locally Managed Tablespace Problems _________________ 86
Verifying the Integrity of Segments Created in ASSM Tablespaces.________________ 86
Checking Consistency of Segment Extent Map with Tablespace File Bitmaps_________ 87
Verifying the Integrity of ASSM Tablespaces__________________________________ 88
Marking the Segment Corrupt or Valid ______________________________________ 89
Dropping a Corrupted Segment____________________________________________ 89
Dumping a Segment Header and Bitmap Blocks_______________________________ 90
Marking a DBA Range in Bitmap as Free or Used ______________________________ 90
Rebuilding the Appropriate Bitmap _________________________________________ 91
Rebuilding Quotas for Given Tablespace _____________________________________ 91
Migrating from a Dictionary-Managed to a Locally Managed Tablespace ____________ 91
Fixing the State of the Segments in A Tablespace _____________________________ 91
Scenario 1: Fixing Bitmap When Allocated Blocks are Marked Free (No Overlap) _____ 91
Scenario 2: Dropping a Corrupted Segment __________________________________ 92
Scenario 3: Fixing Bitmap Where Overlap is Reported __________________________ 92
Scenario 4: Correcting Media Corruption of Bitmap Blocks_______________________ 92
Transporting Tablespaces Between Databases ________________________________ 92
Managing Alert Thresholds __________________________________________94
Getting the Current Threshold Setting ______________________________________ 94
Setting Tablespace Alert Thresholds ________________________________________ 95
Restoring a Tablespace to Database Default Thresholds_________________________ 96
Page 7 Oracle DBA Code Examples
Modifying Database Default Thresholds _____________________________________ 97
Viewing Alerts _________________________________________________________ 97
Managing Datafiles and Tempfiles _____________________________________98
Creating Datafiles ______________________________________________________ 98
Enabling and Disabling Automatic Extension for a Datafile_______________________ 98
Manually Resizing a Datafile ______________________________________________ 98
Bringing Datafiles Online or Taking Offline in ARCHIVELOG Mode _________________ 98
Taking Datafiles Offline in NOARCHIVELOG Mode______________________________ 98
Renaming and Relocating Datafiles in a Single Tablespace_______________________ 99
Dropping Datafiles______________________________________________________ 99
Copying a File on a Local File System _______________________________________ 99
Transferring a File to a Different Database __________________________________ 100
Dumping a Data Block__________________________________________________ 100
Managing Undo Tablespaces ________________________________________101
Obtaining Information on Undo___________________________________________ 101
Enabling Automatic Undo Management_____________________________________ 102
Creating an Undo Tablespace ____________________________________________ 102
Setting Startup Undo Tablespace _________________________________________ 102
Tuning Undo Retention _________________________________________________ 102
Using Undo Advisor ____________________________________________________ 103
Setting the Undo Retention Period ________________________________________ 103
Enabling Retention Guarantee____________________________________________ 103
Dropping an Undo Tablespace____________________________________________ 103
To Drop a Corrupt UNDO Tablespace ______________________________________ 104
Using Oracle Managed Files (OMF) ___________________________________104
Managing Schema Objects ______________________________ 106
Chaching Small Tables in Memory____________________________________106
Creating Virtual Columns___________________________________________106
Creating Partitioned Tables _________________________________________106
Partition Maintenance Operations ____________________________________111
Setting Deferred Segment Creation___________________________________112
Creating Multiple Tables and Views in a Single Operation__________________112
Collecting Object Statistics _________________________________________113
Collecting Index Statistics _______________________________________________ 113
Collecting Table Statistics _______________________________________________ 113
Page 8 Oracle DBA Code Examples
Collecting Schema Statistics _____________________________________________ 114
Validating Tables, Indexes, Clusters, and Materialized Views _______________115
Listing Chained and Migrated Rows of Tables and Clusters_________________115
Truncating Tables or Clusters _______________________________________116
Enabling and Disabling Triggers _____________________________________116
Managing Integrity Constraints ______________________________________116
Setting Constraint States and Deferability __________________________________ 116
Modifying, Renaming, or Dropping Existing Integrity Constraints ________________ 117
Reporting Constraint Exceptions __________________________________________ 117
Obtaining Information on Constraints ______________________________________ 118
Renaming Schema Objects _________________________________________118
Managing Object Dependencies______________________________________118
Manually Recompiling Views Procedures and Packages ________________________ 118
Switching to a Different Schema _____________________________________118
Using DBMS_METADATA to Display Information About Schema Objects ______118
Specifying Storage Parameters at Object Creation _______________________118
Managing Resumable Space Allocation ________________________________119
Enabling Resumable Space Allocation ______________________________________ 119
Detecting Suspended Statements _________________________________________ 119
Obtaining Information about Suspended Statements __________________________ 121
Reclaiming Wasted Space __________________________________________121
Displaying Information About Space Usage for Schema Objects _________________ 121
Segment Advisor ______________________________________________________ 124
Shrinking Database Segments Online ______________________________________ 126
Deallocating Unused Space ______________________________________________ 126
Capacity Planning for Database Objects _______________________________127
Estimating the Space Use of a Table_______________________________________ 127
Obtaining Object Growth Trends __________________________________________ 128
Using the SQL Access Advisor _______________________________________129
Estimating the Space Use of a Table_______________________________________ 129
Managing Tables______________________________________ 134
Obtaining Information about Tables __________________________________134
Creating Tables with some Options ___________________________________134
Page 9 Oracle DBA Code Examples
DML Error Logging ________________________________________________135
Enabling Direct-Path INSERT________________________________________135
Automatically Collecting Statistics on Tables____________________________136
Altering Tables___________________________________________________136
Performing Online Redefinition with DBMS_REDEFINITION ________________137
Redefining a Table _____________________________________________________ 137
Redefining a Single Partition _____________________________________________ 139
Migrating BasicFile LOBs to SecureFiles ____________________________________ 140
Using Flashback Drop and Managing the Recycle Bin _____________________141
Managing Index-Organized Tables ___________________________________141
Managing External Tables __________________________________________142
Managing Indexes ____________________________________ 145
Using Indexes ___________________________________________________145
Using Bitmap Join Indexes (BJI) _____________________________________146
Partitioned Indexes _______________________________________________147
Managing Materialized Views ____________________________ 148
Obtaining Information about Materialized Views______________________________ 148
Monitoring the Progress of a Materialized View Refresh ________________________ 148
Materialized View Typical Refresh Errors____________________________________ 152
Using Materialized Views ________________________________________________ 152
Using Query Rewriting__________________________________________________ 155
ReWrite Hints_________________________________________________________ 157
Using EXPLAIN_MVIEW Procedure: Viewing Materialized View Capabilities _________ 157
Using DBMS_ADVISOR.TUNE_MVIEW ______________________________________ 157
Registering a User-defined Table as Materialized View _________________________ 158
Managing Clusters and Hash Clusters______________________ 159
Managing Views, Sequences, and Synonyms ________________ 161
Managing Transactions_________________________________ 162
Implementing Oracle’s Concurrency Control ____________________________162
Oracle Isolaction Levels_________________________________________________ 162
Oracle Lock Types _____________________________________________________ 162
Page 10 Oracle DBA Code Examples
