Oracle DBA Code Examples - Ahmed Baraka

707 Pages·2011·1.91 MB·English

by  Ahmed Baraka

Oracle 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 [email protected]
Usage Terms
 Anyone is authorized to copy this document to any means of storage and present it in any
format to any individual or organization for non-commercial purpose free.
 No individual or organization may use this document for commercial purpose without a
written permission from the editor.
 This document is for informational purposes only, and may contain typographical errors and
technical inaccuracies.
 There is no warranty of any type for the code or information presented in this
document. The editor is not responsible for any loses or damage resulted from
using the information or executing the code in this document.
 If any one wishes to correct a statement or a typing error or add a new piece of information,
please send the request to [email protected]

Page 2 Oracle DBA Code Examples
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