Page 1 Oracle DBA Code Examples

Or acl e DBA Code Ex ampl es

Version Dat e: Dec 2010
Edit or: Ahmed Baraka
Page 2 Oracle DBA Code Examples

Document Pur pose
This document is edit ed t o be a quick reference of code examples used t o achieve specific
Oracle DBA t asks. No explanat ion on any subj ect is present ed.
The document is simply orient ed based on t he required t ask, t he code t o perform t he t ask and
any precaut ions or warnings when using t he code. Also, it will be specified if t he code is
version specific. The document mainly demonst rat es using SQL and PL/ SQL code t o achieve
any t ask. I t does not concent rat e on using OEM t o perform a t ask. However, if t here will be a
significant advant age, t here could be j ust some hint s on using OEM for some t asks.
Pr er equi si t es
The document assumes t hat t he reader has already t he knowledge of Oracle dat abase
administ rat ion.
How t o Use t he Document
1. Go t o Cont ent s sect ion
2. Search t he required t ask
3. Click on t he required t ask link
4. Read t he warnings and/ or usage guideline, if any.
5. Make any modificat ion in t he code t o mat ch your case.
Or acl e Dat abase Ver si ons
The code present ed in t he document is t o operat e on Oracle dat abase versions 10g and 11g. I t
will be st at ed, if t he code is version specific.
Obt ai ni ng Lat est Ver si on of t he Document
Lat est version can be obt ained from my sit e or by emailing me at info@ahmedbaraka. com
Usage Ter ms
Anyone is aut horized t o copy t his document t o any means of st orage and present it in any
format t o any individual or organizat ion for non- commercial purpose free.
No individual or organizat ion may use t his document for commercial purpose wit hout a
writ t en permission from t he edit or.
This document is for informat ional purposes only, and may cont ain t ypographical errors and
t echnical inaccuracies.
Ther e i s no w ar r ant y of any t y pe f or t he code or i nf or mat i on pr esent ed i n t hi s
document . The edi t or i s not r esponsi bl e f or any l oses or damage r esul t ed f r om
usi ng t he i nf or mat i on or ex ecut i ng t he code i n t hi s document .
I f any one wishes t o correct a st at ement or a t yping error or add a new piece of informat ion,
please send t he request t o i nf o@ahmedbar ak a. com

Page 3 Oracle DBA Code Examples

Document Par t s
Par t 1 Or acl e DBA Fundament al s _______________________ 34
Par t 2 Or acl e Dat abase Net Ser v i ces ___________________ 223
Par t 3 Or acl e Dat abase Back up and Recover y ____________ 232
Par t 4 Or acl e Dat abase Secur i t y ______________________ 292
Par t 5 Or acl e Dat abase Per f or mance Tuni ng _____________ 321
Par t 6 Or acl e Aut omat i c St or age Management ( ASM) ______ 373
Par t 7 Or acl e Real Appl i cat i on Cl ust er __________________ 386
Par t 8 Or acl e War ehousi ng __________________________ 505
Par t 9 Or acl e Dat abase Ut i l i t i es _______________________ 510
Par t 10 Mi scel l aneous Or acl e Dat abase Topi cs _____________ 534
Par t 11 PL/ SQL Sampl es ______________________________ 544
Par t 12 Appendi x es _________________________________ 613

Page 4 Oracle DBA Code Examples

Cont ent s
Par t 1 Or acl e DBA Fundament al s _______________________ 34
DBA: Best Pr act i ces ____________________________________ 35
DBA: Best Pract ice Guidelines for St andalone and RAC Dat abases ___________ 35
Or acl e Dat abase I nst al l at i on Consi der at i on __________________ 36
Est imat ing Disk and Memoery Requirement s ___________________________ 36
Opt imal Flexible Archit ect ure _______________________________________ 36
Oracle Product s I nst alled wit h t he 11.1 Release _________________________ 37
I nst al l i ng Or acl e 10g R2 on Ent er pr i se Li nux 4 _______________ 38
I nst allat ion Environment ___________________________________________ 38
Required Soft ware _______________________________________________ 38
Used Hardware __________________________________________________ 38
I nst allat ion Plan _________________________________________________ 38
1. Preinst allat ion t asks __________________________________________________ 38
2. Oracle Dat abase 10g Soft ware I nst allat ion ________________________________ 42
3. Apply Pat chset 3 ( 10. 2. 0. 4) for Clust erware and Dat abase Soft ware ____________ 42
4. Configure List eners __________________________________________________ 43
5. Creat e Dat abase _____________________________________________________ 43
6. Post inst allat ion Tasks _________________________________________________ 43
I nst al l i ng Or acl e 11g R2 on Ent er pr i se Li nux 5 _______________ 45
I nst allat ion Environment ___________________________________________ 45
Required Soft ware _______________________________________________ 45
Used Hardware __________________________________________________ 45
I nst allat ion Plan _________________________________________________ 45
1. Preinst allat ion t asks __________________________________________________ 45
2. Oracle Dat abase 11g Soft ware I nst allat ion ________________________________ 51
3. Apply Pat chset ______________________________________________________ 51
4. Configure List eners __________________________________________________ 51
5. Creat e Dat abase _____________________________________________________ 51
6. Post inst allat ion Tasks _________________________________________________ 51
Page 5 Oracle DBA Code Examples

I nst al l i ng Or acl e 11g R2 on Ent er pr i se Li nux 5.5 w i t h ASM ______ 53
I nst allat ion Environment ___________________________________________ 53
Required Soft ware _______________________________________________ 53
Used Hardware __________________________________________________ 53
I nst allat ion Plan _________________________________________________ 53
1. Preinst allat ion t asks __________________________________________________ 54
2. Oracle Grid I nfrast ruct ure inst allat ion ____________________________________ 59
3. Oracle Grid I nfrast ruct ure Pat ching ______________________________________ 60
4. Oracle Dat abase 11g R2 Soft ware I nst allat ion ______________________________ 60
5. Oracle Dat abase 11g R2 Soft ware Pat ching ________________________________ 61
6. I nst all EM Agent in clust er nodes ( if required) ______________________________ 61
7. ASM Diskgroups Creat ion ______________________________________________ 61
8. Dat abase Creat ion ___________________________________________________ 61
9. Post inst allat ion t asks _________________________________________________ 61
10. General Useful Post inst allat ion Tasks in Linux _____________________________ 61
Managi ng Or acl e Dat abase I nst ance _______________________ 63
Product Release Number ___________________________________________ 63
Oracle Dat abase Release Number Format ___________________________________ 63
Obt aining License I nformat ion ______________________________________ 63
Managing t he I nst ance Archit ect ure __________________________________ 63
Obt aining I nformat ion about t he I nst ance Processes ___________________________ 63
Obt aining I nformat ion about t he SGA ______________________________________ 64
Clearing t he Buffer Cache ________________________________________________ 64
Dat abase Administ rat ion Aut hent icat ion _______________________________ 64
Using Operat ing Syst em Aut hent icat ion _____________________________________ 64
Using Password File Aut hent icat ion ________________________________________ 65
I dent ifying Users SYSDBA or SYSOPER Users ________________________________ 65
Dat a Dict ionary and Dynamic Performance Views ________________________ 65
Dat a Dict ionary Creat ion ________________________________________________ 65
St art up and Shut down ____________________________________________ 65
St art up Levels ________________________________________________________ 65
Shut down Levels ______________________________________________________ 65
Aut omat ically St art ing Dat abases__________________________________________ 65
Quiescing a Dat abase _____________________________________________ 70
Page 6 Oracle DBA Code Examples

Suspending a Dat abase ___________________________________________ 70
Dropping a Dat abase _____________________________________________ 70
I nit ializat ion Files ________________________________________________ 70
Managing I nit ializat ion Files ______________________________________________ 70
Managing Paramet ers in SPFI LE ___________________________________________ 71
Alert and Trace Files ______________________________________________ 71
Monit oring Alert and Trace Files ___________________________________________ 71
Managi ng Or acl e Dat abase Phy si cal St r uct ur e ________________ 73
Managing Cont rol Files ____________________________________________ 73
Obt aining Cont rol File informat ion _________________________________________ 73
Creat ing Addit ional Copies, Renaming, and Relocat ing Cont rol Files _______________ 73
Creat ing New Cont rol Files _______________________________________________ 73
Backing Up Cont rol Files _________________________________________________ 74
Manage t he Size of Cont rol Files __________________________________________ 74
Mult iplexing t he Cont rol File ______________________________________________ 74
Maint aining Online Redo Log Files ____________________________________ 75
Forcing Log Swit ches and Checkpoint s ______________________________________ 75
Adding Online Redo Log File Groups ________________________________________ 75
Adding Online Redo Log File Members ______________________________________ 75
Dropping Online Redo Log File Groups ______________________________________ 75
Dropping Online Redo Log File Members ____________________________________ 75
Relocat ing and Renaming Redo Log Members ________________________________ 75
Verifying Blocks in Redo Log Files _________________________________________ 76
Clearing a Redo Log File _________________________________________________ 76
Viewing Redo Log I nformat ion ____________________________________________ 76
Managing Archived Redo Logs_______________________________________ 76
Obt aining I nformat ion about Archive Log ____________________________________ 76
Changing t he Dat abase Archiving Mode _____________________________________ 77
Specifying Archive Dest inat ions and t heir Opt ions _____________________________ 77
Specifying t he Minimum Number of Successful Dest inat ions _____________________ 77
Cont rolling Archiving t o a Dest inat ion ______________________________________ 78
Cont rolling Trace Out put Generat ed by t he Archivelog Process ___________________ 78
Managing Tablespaces ____________________________________________ 78
Obt aining Tablespace I nformat ion _________________________________________ 78
Creat ing a Locally Managed Tablespace _____________________________________ 81
Page 7 Oracle DBA Code Examples

Specifying Segment Space Management ____________________________________ 81
Adding Space t o Tablespace ______________________________________________ 81
Specifying Nonst andard Block Sizes for Tablespaces ___________________________ 81
Using Bigfile Tablespace ( BFT) ____________________________________________ 81
Using Temporary Tablespace _____________________________________________ 82
Renaming a Tempfile ___________________________________________________ 82
Shrinking Temporary Tablespace __________________________________________ 82
Using Default Temporary Tablespace _______________________________________ 82
Using Temporary Tablespace Groups _______________________________________ 82
Suppressing Redo Generat ion for a Tablespace _______________________________ 83
Cont rolling Tablespaces Availabilit y ________________________________________ 83
Using Read-Only Tablespaces _____________________________________________ 83
Renaming Tablespaces __________________________________________________ 83
Default Permanent Tabelspace ____________________________________________ 83
Dropping Tablespaces___________________________________________________ 83
Managing t he SYSAUX Tablespace _________________________________________ 83
Diagnosing and Repairing Locally Managed Tablespace Problems _________________ 84
Verifying t he I nt egrit y of Segment s Creat ed in ASSM Tablespaces.________________ 84
Checking Consist ency of Segment Ext ent Map wit h Tablespace File Bit maps ________ 85
Verifying t he I nt egrit y of ASSM Tablespaces _________________________________ 86
Marking t he Segment Corrupt or Valid ______________________________________ 86
Dropping a Corrupt ed Segment ___________________________________________ 87
Dumping a Segment Header and Bit map Blocks ______________________________ 88
Marking a DBA Range in Bit map as Free or Used ______________________________ 88
Rebuilding t he Appropriat e Bit map _________________________________________ 88
Rebuilding Quot as for Given Tablespace ____________________________________ 89
Migrat ing from a Dict ionary- Managed t o a Locally Managed Tablespace ____________ 89
Fixing t he St at e of t he Segment s in A Tablespace _____________________________ 89
Scenario 1: Fixing Bit map When Allocat ed Blocks are Marked Free ( No Overlap) _____ 89
Scenario 2: Dropping a Corrupt ed Segment __________________________________ 89
Scenario 3: Fixing Bit map Where Overlap is Report ed __________________________ 90
Scenario 4: Correct ing Media Corrupt ion of Bit map Blocks ______________________ 90
Transport ing Tablespaces Bet ween Dat abases ________________________________ 90
Managing Alert Thresholds _________________________________________ 92
Get t ing t he Current Threshold Set t ing ______________________________________ 92
Set t ing Tablespace Alert Thresholds________________________________________ 93
Rest oring a Tablespace t o Dat abase Default Thresholds ________________________ 94
Modifying Dat abase Default Thresholds _____________________________________ 94
Page 8 Oracle DBA Code Examples

Viewing Alert s _________________________________________________________ 95
Managing Dat afiles and Tempfiles ____________________________________ 96
Creat ing Dat afiles ______________________________________________________ 96
Enabling and Disabling Aut omat ic Ext ension for a Dat afile ______________________ 96
Manually Resizing a Dat afile ______________________________________________ 96
Bringing Dat afiles Online or Taking Offline in ARCHI VELOG Mode _________________ 96
Taking Dat afiles Offline in NOARCHI VELOG Mode _____________________________ 96
Renaming and Relocat ing Dat afiles in a Single Tablespace ______________________ 96
Dropping Dat afiles _____________________________________________________ 97
Copying a File on a Local File Syst em_______________________________________ 97
Transferring a File t o a Different Dat abase___________________________________ 97
Dumping a Dat a Block __________________________________________________ 98
Managing Undo Tablespaces ________________________________________ 99
Obt aining I nformat ion on Undo ___________________________________________ 99
Enabling Aut omat ic Undo Management ____________________________________ 100
Creat ing an Undo Tablespace ____________________________________________ 100
Set t ing St art up Undo Tablespace _________________________________________ 100
Tuning Undo Ret ent ion _________________________________________________ 100
Using Undo Advisor ___________________________________________________ 100
Set t ing t he Undo Ret ent ion Period ________________________________________ 101
Enabling Ret ent ion Guarant ee ___________________________________________ 101
Dropping an Undo Tablespace ___________________________________________ 101
Using Oracle Managed Files (OMF) __________________________________ 101
Managi ng Schema Obj ect s ______________________________ 102
Chaching Small Tables in Memory ___________________________________ 102
Creat ing Virt ual Columns _________________________________________ 102
Creat ing Part it ioned Tables ________________________________________ 102
Part it ion Maint enance Operat ions ___________________________________ 107
Creat ing Mult iple Tables and Views in a Single Operat ion _________________ 108
Collect ing Obj ect St at ist ics ________________________________________ 108
Collect ing I ndex St at ist ics ______________________________________________ 108
Collect ing Table St at ist ics _______________________________________________ 109
Collect ing Schema St at ist ics _____________________________________________ 109
Validat ing Tables, I ndexes, Clust ers, and Mat erialized Views ______________ 110
List ing Chained and Migrat ed Rows of Tables and Clust ers ________________ 111
Page 9 Oracle DBA Code Examples

Truncat ing Tables or Clust ers ______________________________________ 111
Enabling and Disabling Triggers ____________________________________ 111
Managing I nt egrit y Const raint s _____________________________________ 112
Set t ing Const raint St at es and Deferabilit y __________________________________ 112
Modifying, Renaming, or Dropping Exist ing I nt egrit y Const raint s ________________ 112
Report ing Const raint Except ions _________________________________________ 113
Obt aining I nformat ion on Const raint s _____________________________________ 113
Renaming Schema Obj ect s ________________________________________ 113
Managing Obj ect Dependencies ____________________________________ 113
Manually Recompiling Views Procedures and Packages ________________________ 113
Swit ching t o a Different Schema ____________________________________ 113
Using DBMS_METADATA t o Display I nformat ion About Schema Obj ect s ______ 113
Specifying St orage Paramet ers at Obj ect Creat ion ______________________ 114
Managing Resumable Space Allocat ion _______________________________ 114
Enabling Resumable Space Allocat ion _____________________________________ 114
Det ect ing Suspended St at ement s ________________________________________ 115
Obt aining I nformat ion about Suspended St at ement s __________________________ 116
Reclaiming Wast ed Space _________________________________________ 116
Displaying I nformat ion About Space Usage for Schema Obj ect s _________________ 116
Segment Advisor _____________________________________________________ 120
Shrinking Dat abase Segment s Online _____________________________________ 121
Deallocat ing Unused Space _____________________________________________ 122
Capacit y Planning for Dat abase Obj ect s ______________________________ 122
Est imat ing t he Space Use of a Table ______________________________________ 122
Obt aining Obj ect Growt h Trends _________________________________________ 123
Using t he SQL Access Advisor ______________________________________ 124
Est imat ing t he Space Use of a Table ______________________________________ 124
Managi ng Tabl es _____________________________________ 130
Obt aining I nformat ion about Tables _________________________________ 130
Creat ing Tables wit h some Opt ions __________________________________ 130
DML Error Logging ______________________________________________ 131
Enabling Direct - Pat h I NSERT ______________________________________ 131
Aut omat ically Collect ing St at ist ics on Tables ___________________________ 132
Page 10 Oracle DBA Code Examples

Alt ering Tables _________________________________________________ 132
Performing Online Redefinit ion wit h DBMS_REDEFI NI TI ON ________________ 133
Redefining a Table ____________________________________________________ 133
Redefining a Single Part it ion ____________________________________________ 135
Migrat ing BasicFile LOBs t o SecureFiles ____________________________________ 136
Using Flashback Drop and Managing t he Recycle Bin ____________________ 137
Managing I ndex- Organized Tables __________________________________ 137
Managing Ext ernal Tables _________________________________________ 138
Managi ng I ndex es ____________________________________ 141
Using I ndexes __________________________________________________ 141
Using Bit map Join I ndexes ( BJI ) ____________________________________ 142
Part it ioned I ndexes ______________________________________________ 143
Managing Mat erialized Views ______________________________________ 143
Using Mat erialized Views _______________________________________________ 143
Using Query Rewrit ing _________________________________________________ 145
ReWrit e Hint s ________________________________________________________ 147
Using EXPLAI N_MVI EW Procedure: Viewing Mat erialized View Capabilit ies _________ 147
Regist ering a User- defined Table as Mat erialized View_________________________ 148
Managi ng Cl ust er s and Hash Cl ust er s _____________________ 149
Managi ng Vi ew s, Sequences, and Synony ms ________________ 151
Managi ng Tr ansact i ons ________________________________ 152
I mplement ing Oracles Concurrency Cont rol ___________________________ 152
Oracle I solact ion Levels ________________________________________________ 152
Oracle Lock Types ____________________________________________________ 152
I dent ifying Blocking Sessions ____________________________________________ 153
Using Aut onomous Transact ion _____________________________________ 153
Managing Long Transact ions wit h Workspace Manager ___________________ 154
Repai r i ng Cor r upt ed Dat a ______________________________ 162
Opt ions for Repairing Dat a Block Corrupt ion ___________________________ 162
Det ect ing Corrupt ions Met hods _____________________________________ 162
Page 11 Oracle DBA Code Examples

Using dbv ( DBVerify) Ut ilit y _______________________________________ 162
Set t ing t he I nit ializat ion Paramet ers for Det ect ing Corrupt ion _____________ 162
Verifying Block I nt egrit y in Real Time: DB_BLOCK_CHECKI NG __________________ 162
Verifying Block I nt egrit y in Real Time: DB_BLOCK_CHECKSUM __________________ 163
Det ect ing lost writ e: DB_LOST_WRI TE_PROTECT ____________________________ 163
Set t ubg t he DB_ULTRA_SAFE Paramet er ( I n Oracle 11g) ______________________ 163
Using ANALYZE Command ________________________________________ 163
Using EXP t o Det ect Corrupt ion _____________________________________ 163
Using DBMS_REPAI R _____________________________________________ 164
DBMS_REPAI R Limit at ions and Rest rict ions _________________________________ 164
Evaluat e t he Cost s and Benefit s of Using DBMS_REPAI R _______________________ 164
Det ect and Report Corrupt ions using DBMS_REPAI R __________________________ 165
Managi ng Aut omat ed Dat abase Mai nt enance Task s __________ 167
Predefined Aut omat ed Maint enance Tasks ____________________________ 167
Predefined Maint enance Windows ___________________________________ 167
Obt aining I nformat ion about Predefined Maint enance Tasks _______________ 167
Enabling and Disabling Maint enance Tasks ____________________________ 168
Configuring Maint enance Windows __________________________________ 168
Managi ng Resour ces __________________________________ 170
Obt aining I nformat ion on Dat abase Resource Manager___________________ 181
Monit oring Oracle Dat abase Resource Manager ________________________ 183
Usi ng Or acl e Schedul er ________________________________ 185
Using Jobs _____________________________________________________ 185
Using Programs _________________________________________________ 191
Using Schedules ________________________________________________ 193
Using Job Classes _______________________________________________ 194
Using Windows _________________________________________________ 194
Using Window Groups ____________________________________________ 196
Using Event s Raised by t he Scheduler _______________________________ 197
Using Event s Raised by an Applicat ion ( Event s-Based Jobs) _______________ 200
Using Chains ___________________________________________________ 202
Page 12 Oracle DBA Code Examples

Allocat ing Resources Among Jobs ___________________________________ 206
Administ ering Oracle Scheduler ____________________________________ 206
Configuring Oracle Scheduler ____________________________________________ 206
Monit oring and Managing t he Scheduler ___________________________________ 207
Enabling, Using and DisablingRemot e Ext ernal Jobs __________________________ 208
I mport / Export and t he Scheduler _________________________________________ 210
Scheduler Privileges ___________________________________________________ 210
Scheduler Dat a Dict ionary Views _________________________________________ 210
Using t he UTL_FI LE Package ____________________________________________ 211
Dat a Loadi ng and Tr ansf or mi ng Tool s _____________________ 213
Usi ng Dat abase Li nk s _________________________________ 214
Managi ng Di agnost i c Dat a ______________________________ 215
Set t ing t he Aut omat ic Diagnost ic Reposit ory Direct ory ___________________ 215
Using adrci Tool ________________________________________________ 215
General usage of adrci _________________________________________________ 215
Using adrci t o Package I ncident s _________________________________________ 216
Managing Dat abase Healt h Monit or __________________________________ 216
Managing Dat a Recovery Advisor ___________________________________ 218
Using SQL Test Case Builder _______________________________________ 219
Pat chi ng Or acl e Pr oduct s ______________________________ 220
Using Oracle Opat ch _____________________________________________ 220
Par t 2 Or acl e Dat abase Net Ser v i ces ___________________ 223
Connect i vi t y Nami ng Met hods ___________________________ 224
The Local Naming Met hod _________________________________________ 224
The Easy Connect Naming Met hod __________________________________ 224
The Ext ernal Naming Met hod ______________________________________ 224
The Direct ory Naming Met hod ______________________________________ 225
Dat abase Resident Connect ion Pooling ( DRCP) _________________________ 225
Or acl e and Java Dat abase Connect i v i t y ____________________ 227
Page 13 Oracle DBA Code Examples

Est ablishing Dat abase Connect ivit y __________________________________ 227
Mi scel l aneous Connect i v i t y Opt i ons _______________________ 229
Set t ing t he Default Connect St ring __________________________________ 229
I nst alling t he I nst ant Client _______________________________________ 229
Set t ing List ener Opt ions __________________________________________ 229
Set t ing Access Cont rols___________________________________________ 229
Changing Windows Host name ______________________________________ 230
Par t 3 Or acl e Dat abase Back up and Recover y ____________ 232
Back up Gui del i nes ____________________________________ 233
Causes of Unplanned Down Time ___________________________________ 233
Causes of Planned Down Time _____________________________________ 233
Oracles Solut ion t o Down Time ____________________________________ 234
Minimizing Unplanned Downt ime Guidelines ___________________________ 234
SLA Sample ___________________________________________________ 235
Planning a Backup St rat egy Guidelines _______________________________ 235
Ex ampl es of Back up Schedul es f or a Dat abase ______________ 236
User - Managed Back ups ________________________________ 237
Obt aining Dat abase File I nformat ion _________________________________ 237
Making Whole Closed Dat abase Backups ___________________________________ 237
Making a Whole Open Backup ___________________________________________ 237
Making Tablespace Backups _____________________________________________ 238
Obt aining Backup St at us I nformat ion ________________________________ 238
Checking Dat afiles Taken as Backup ________________________________ 238
Handling Crash Before User- Manged Backup Ends ______________________ 238
Backing up Cont rol File ___________________________________________ 239
Backing Up I nit ializat ion Files ______________________________________ 239
User - Managed Compl et e Recover y _______________________ 240
User- Managed Recovery in NOARCHI VELOG Mode ______________________ 240
Page 14 Oracle DBA Code Examples

User- Managed Recovery in NOARCHI VELOG Mode Wit hout Redo Log File _____ 240
User- Managed Complet e Recovery in ARCHI VELOG Mode _________________ 240
Re- Creat ing Lost Dat afiles Wit hout Backup ____________________________ 241
User - Managed I ncompl et e Recov er y ______________________ 242
Common Sit uat ions Requiring I ncomplet e Recovery _____________________ 242
User- Managed I ncomplet e Recovery St eps ____________________________ 242
Recovering from Lost Cont rol File by Re- Creat ing t he Cont rol File __________ 242
Fl ash Recover y Ar ea __________________________________ 246
Obt aining I nformat ion on Flash Recovery Area _________________________ 246
Configuring Flash Recovery Area____________________________________ 246
Backing Up t he Flash Recovery Area _________________________________ 246
Moving t he Flash Recovery Area ____________________________________ 247
Recov er y Manager ( RMAN) _____________________________ 248
Using A Media Management Layer ( MML) wit h RMAN ____________________ 248
Obt aining I nformat ion about and relat ed t o RMAN using Dict ionary Views ____ 248
St art ing RMAN _________________________________________________ 250
Using rlwrap Ut ilit y wit h RMAN in Unix-Based Syst ems ___________________ 251
Configuring t he RMAN Environment _________________________________ 251
RMAN Channel Commands ________________________________________ 252
Durat ion in days of RMAN informat ion in Cont rol File ____________________ 253
Monit oring RMAN Jobs____________________________________________ 253
Using RMAN BACKUP Command ____________________________________ 253
Backing Up Cont rol File and SPFile __________________________________ 254
Backing Up Archived RedLogs ______________________________________ 254
Backup in NOARCHI VELOG Mode ___________________________________ 255
Encrypt ing RMAN Backups ________________________________________ 255
Using Compression in RMAN Backups ________________________________ 255
Using Mult iplexed Backup Set s _____________________________________ 255
Using Parallelizat ion of Backup Set s _________________________________ 255
Page 15 Oracle DBA Code Examples

Using Duplexed Backup Set s ( Backupset Copies) _______________________ 256
Making I mage Copies ____________________________________________ 256
Validat ing Backup _______________________________________________ 257
I ncrement al Backup _____________________________________________ 257
Tags for Backups and I mage Copies _________________________________ 257
Creat ing Archival Backups_________________________________________ 257
Monit oring RMAN Backups ________________________________________ 258
RMAN Complet e Recovery ______________________________________ 258
Validat ing Backup Files _________________________________________________ 258
Previewing Backup Files Required by a Rest ore ______________________________ 259
I dent ifying Dat afiles Requiring Recovery ___________________________________ 259
Performing Complet e Recovery __________________________________________ 259
RMAN I ncomplet e Recovery _______________________________________ 260
Simplified Recovery Through Reset logs ______________________________ 260
Recovering from Lost Cont rol File using RMAN _________________________ 261
Block Media Recovery (BMR)_______________________________________ 261
Trial Recovery __________________________________________________ 261
Handling Specific Errors During Recovery _____________________________ 262
Configuring I nst ance Crash Recovery Time ( MTTR) _____________________ 263
Working wit h t he Dat a Recovery Advisor in RMAN ______________________ 263
RMAN Maint enance ______________________________________________ 264
Cross Checking Backups and Copies ______________________________________ 264
Delet ing Backups and Copies ____________________________________________ 264
Changing t he Availabilit y of RMAN Backups and Copies ________________________ 264
Exempt ing a Backup or Copy from t he Ret ent ion Policy ________________________ 265
The CATALOG Command _______________________________________________ 265
The CHANGE UNCATALOG Command ____________________________________ 265
RMAN Cat alog __________________________________________________ 266
Creat ing a Recovery Cat alog ____________________________________________ 266
RMAN Cat alog Report ing _______________________________________________ 266
Upgrading a Recovery Cat alog ___________________________________________ 266
I mport ing Recovery Cat alogs ____________________________________________ 267
Moving a Recovery Cat alog _____________________________________________ 267
Dropping a Recovery Cat alog ____________________________________________ 267
Page 16 Oracle DBA Code Examples

Virt ual Privat e Cat alogs ___________________________________________ 267
Using RMAN Script s______________________________________________ 268
Dupl i cat i ng ( Cl oni ng) a Dat abase ________________________ 270
Dat abase Duplicat ion ( Cloning) Met hods ______________________________ 270
Dat abase Duplicat ion Techniques ___________________________________ 270
Dat abase Duplicat ion Prerequisit es __________________________________ 270
Duplicat ing an Act ive Dat abase using RMAN ___________________________ 270
Duplicat ing a Dat abase wit hout Recovery Cat alog or Target Connect ion______ 272
Manually Duplicat ing a Dat abase ___________________________________ 274
Usi ng Or acl e Fl ashback Technol ogy _______________________ 278
Fl ashback Opt i ons ____________________________________ 279
Pr epar i ng Your Dat abase f or Fl ashback ____________________ 280
Usi ng Row Level Fl ashback Opt i ons _______________________ 281
Flashback Query ________________________________________________ 281
Flashback Versions Query _________________________________________ 281
Flashback Transact ion Query ______________________________________ 282
Flashback Transact ion (Backout ) ___________________________________ 282
Usi ng Tabl e Lev el Fl ashback Opt i ons ______________________ 284
Flashback Table ________________________________________________ 284
Flashback Drop _________________________________________________ 284
Flashback Dat a Archive ___________________________________________ 285
Usi ng Fl ashback Dat abase ______________________________ 289
When t o use Flashback Dat abase ___________________________________ 289
Flashback Dat abase Considerat ions _________________________________ 289
Using Flashback Dat abase_________________________________________ 289
Rest ore Point s __________________________________________________ 290
Par t 4 Or acl e Dat abase Secur i t y ______________________ 292
Page 17 Oracle DBA Code Examples

Or acl e Dat abase Secur i t y Management ____________________ 293
Secur i t y Gui del i nes ___________________________________ 294
Managi ng User s ______________________________________ 296
Dat abase Aut hent i cat i on _______________________________ 298
Managin Passwords ______________________________________________ 298
Ext ernal ( OS) Aut hent icat ion ______________________________________ 298
Proxy Aut hent icat ion _____________________________________________ 299
Logging I n As a Different User _____________________________________ 299
Killing User Sessions from OS ______________________________________ 299
Cont r ol l i ng Dat abase Access ____________________________ 301
Syst em and Obj ect Privileges ______________________________________ 301
I nvoker Right s and Definer Right s __________________________________ 301
Roles _________________________________________________________ 301
Users, Roles, and Privileges Views __________________________________ 302
Fine- Grained Dat a Access (Virt ual Privat e Dat abase VPD) ________________ 303
Audi t i ng Dat abase ____________________________________ 306
St andard Audit ing _______________________________________________ 306
Cust omizing Dat abase Audit ing wit h Triggers __________________________ 307
Audit ing t he Dat abase Using Syst em Trigger __________________________ 309
Using Fine Grained Audit ing _______________________________________ 312
Usi ng Dat a Encr y pt i on _________________________________ 315
Oracle Transparent Dat a Encrypt ion (TDE) ____________________________ 315
Tablespace Encrypt ion ___________________________________________ 316
Fi ne- Gr ai ned Access Cont r ol f or UTL_* Pack ages ____________ 318
Creat ing ACL ___________________________________________________ 318
Access Cont rol List s Maint enance ___________________________________ 319
Query Your Access Cont rol List _____________________________________ 320
Page 18 Oracle DBA Code Examples

Par t 5 Or acl e Dat abase Per f or mance Tuni ng _____________ 321
Managi ng Per f or mance St at i st i cs ________________________ 322
Managing OS St at ist ics ___________________________________________ 322
Managing Dat abase St at ist ics ______________________________________ 326
Syst em and Session St at ist ics ___________________________________________ 326
Time Model St at ist ics __________________________________________________ 327
Wait Event s _________________________________________________________ 328
Act ive Session Hist ory ( ASH) ____________________________________________ 330
Segment St at ist ics ____________________________________________________ 332
Handling I mport ant Oracle Wait Event s ______________________________ 332
Using OS Wat cher _______________________________________________ 333
Opt i mi zi ng Per f or mance i n Wi ndow s Ser v er ________________ 335
Opt imizing Performance in Windows Server 2003 _______________________ 335
Tuni ng t he Dat abase I nst ance ___________________________ 336
Tuning t he Shared Pool ___________________________________________ 336
Tuning t he Buffer Cache __________________________________________ 337
Tuning PGA ____________________________________________________ 339
Using Server Result Cache ________________________________________ 339
Obt aining I nformat ion about Obj ect Locks ____________________________ 342
Handling a Hanging Dat abase ______________________________________ 343
Accurat ely Measuring Process Size __________________________________ 343
Managi ng Aut omat i c Wor k l oad Reposi t or y ( AWR) ____________ 345
Managi ng Aut omat ed Mai nt enance Task s __________________ 346
Using Aut omat ic Dat abase Diagnost ic Monit or (ADDM) ___________________ 346
Using Aut omat ic SQL Tuning Advisor ________________________________ 348
I mpl ement i ng Aut omat i c Memor y Management _____________ 351
Conf i gur i ng DB_nK_CACHE_SI ZE ________________________ 352
Managi ng Opt i mi zer Oper at i ons _________________________ 353
Page 19 Oracle DBA Code Examples

Set t ing t he Opt imizer Mode _______________________________________ 353
Defining Access Pat hs and Joins for t he Query Opt imizer _________________ 353
Gat hering Opt imizer St at ist ics _____________________________________ 357
Gat hering Obj ect St at ist ics ______________________________________________ 357
Gat hering Syst em St at ist ics _____________________________________________ 358
Changing St at ist ics Preferences ____________________________________ 358
Managing Pending and Published St at ist ics ____________________________ 359
Managing Ext ended St at ist ics ______________________________________ 360
Mult iColumn St at ist ics _________________________________________________ 360
Expression St at ist ics ___________________________________________________ 361
A Si mpl e Appr oach t o Tuni ng SQL St at ement s _______________ 362
Usi ng Appl i cat i on Tr aci ng Tool s __________________________ 363
Using t he SQL Trace Facilit y and TKPROF _____________________________ 363
Using t he Event 10046 t o Trace SQL Code ____________________________ 363
Tracing End t o End Applicat ion _____________________________________ 364
Enabling and Disabling St at ist ic Gat hering for End t o End Tracing __________ 365
Wr i t i ng Ef f i ci ent SQL __________________________________ 367
I mpr ovi ng SQL Pr ocessi ng Techni ques ____________________ 370
Usi ng SQL Tuni ng Advi sor ______________________________ 371
Par t 6 Or acl e Aut omat i c St or age Management ( ASM) ______ 373
Managi ng Or acl e ASM _________________________________ 374
Obt aining I nformat ion about ASM I nst ance____________________________ 374
Creat ing an ASM I nst ance _________________________________________ 374
Managing Disk Groups and Disks in ASM _____________________________ 375
Fundement als of Managing Disk Groups and Disks ___________________________ 375
Managing Disk Groups At t ribut es _________________________________________ 376
Monit oring Long- Running Operat ions ________________________________ 378
Migrat ing a Dat abase t o ASM ______________________________________ 379
Page 20 Oracle DBA Code Examples

Moving a Tablespace t o ASM _______________________________________ 379
Accessing an ASM inst ance from DB Console __________________________ 380
Managing ASM Files _____________________________________________ 381
Using ASMCMD Ut ilit y ____________________________________________ 382
Using SYSASM Privilege and OSASM Group ___________________________ 384
Manually Upgrading Oracle AS from 10g t o 11g ________________________ 385
Par t 7 Or acl e Real Appl i cat i on Cl ust er __________________ 386
Or acl e RAC Possi bl e I nst al l at i on Conf i gur at i ons _____________ 387
I nst al l i ng Or acl e 10g R2 RAC on Ent er pr i se Li nux 4 __________ 388
I nst allat ion Environment __________________________________________ 388
Required Soft ware ______________________________________________ 388
Used Hardware _________________________________________________ 388
I nst allat ion Plan ________________________________________________ 388
1. Preinst allat ion t asks _________________________________________________ 389
2. Oracle Clust erware inst allat ion _________________________________________ 396
3. Oracle Dat abase 10g Soft ware I nst allat ion _______________________________ 400
4. Apply Pat chset 3 ( 10. 2. 0. 4) for Clust erware and Dat abase Soft ware ___________ 401
5. I nst all EM Agent in clust er nodes ( if required) _____________________________ 402
6. Configure List eners _________________________________________________ 402
7. Perform ASM inst allat ion _____________________________________________ 403
8. Perform clust er dat abase creat ion ______________________________________ 403
9. Post inst allat ion t asks ________________________________________________ 405
10. Useful Post inst allat ion Tasks _________________________________________ 406
I nst al l i ng Or acl e 11g R2 RAC on Ent er pr i se Li nux 5 __________ 407
Main Changes in Oracle 11g Release 2 RAC ___________________________ 407
I nst allat ion Environment __________________________________________ 407
Required Soft ware ______________________________________________ 407
Used Hardware _________________________________________________ 407
I nst allat ion Plan ________________________________________________ 408
1. Preinst allat ion t asks _________________________________________________ 409
2. Oracle Grid I nfrast ruct ure inst allat ion ___________________________________ 416
Page 21 Oracle DBA Code Examples

3. Oracle Grid I nfrast ruct ure Pat ching _____________________________________ 418
4. Oracle Dat abase 11g R2 Soft ware I nst allat ion _____________________________ 418
5. Oracle Dat abase 11g R2 Soft ware Pat ching _______________________________ 419
6. I nst all EM Agent in clust er nodes ( if required) _____________________________ 419
7. ASM Diskgroups Creat ion _____________________________________________ 419
8. RAC Dat abase Creat ion ______________________________________________ 420
9. Post inst allat ion t asks ________________________________________________ 421
10. General Useful Post inst allat ion Tasks in Linux ____________________________ 421
I nst al l i ng Or acl e 10g R2 RAC on Wi ndow s__________________ 423
I nst allat ion Met hods _____________________________________________ 423
I nst allat ion Environment __________________________________________ 423
Required Soft ware ______________________________________________ 423
Used Virt ual Hardware ___________________________________________ 423
I nst allat ion Plan ________________________________________________ 424
1. Preinst allat ion t asks _________________________________________________ 424
2. Oracle Clust erware inst allat ion _________________________________________ 426
3. Apply Pat ch Set 3 ( 10. 2. 0. 4) on Clust erware soft ware ______________________ 428
4. Oracle ASM 10g Soft ware I nst allat ion ___________________________________ 429
5. Apply Pat chset 3 ( 10. 2. 0. 4) on ASM Soft ware _____________________________ 430
6. I nst all EM Agent in clust er nodes ( if required) _____________________________ 430
7. Configure List eners _________________________________________________ 430
8. Creat e ASM I nst ance ________________________________________________ 431
9. I nst all Oracle RAC Dat abase Home Soft ware ______________________________ 432
10. Apply Pat chset 3 ( 10. 2. 0. 4) on Oracle RAC Soft ware Home _________________ 433
11. Perform clust er dat abase creat ion _____________________________________ 433
12. Useful Post inst allat ion St eps _________________________________________ 435
Cl eani ng Up Cl ust er w ar e I nst al l at i on on Wi ndow s ___________ 436
Si ngl e I nst ance t o RAC Conver si on _______________________ 438
The Tools t o Convert a Single I nst ance DB t o RAC ______________________ 438
Conversion Prerequisit es for Oracle 10g R2 ___________________________ 438
Using rconfig Ut it lit y _____________________________________________ 438
Using DBCA ____________________________________________________ 439
Page 22 Oracle DBA Code Examples

Admi ni st er i ng RAC Dat abase ____________________________ 441
Admi ni st er i ng Or acl e Cl ust er w ar e Component s ______________ 442
Managing Cluserware Daemons and Processes _________________________ 442
Displaying Clust erware Processes_________________________________________ 442
St art ing, St opping, Enabling and Disabling crs St ack __________________________ 442
CSS Paramet ers ________________________________________________ 442
Administ ering Vot ing Disks in RAC __________________________________ 442
Mult iplexing Vot ing Disks _______________________________________________ 442
Dynamically Adding and Removing Vot ing Disks aft er I nst alling RAC _____________ 443
Backing up Vot ing Disks ________________________________________________ 443
Recovering Vot ing Disks ________________________________________________ 443
Administ ering t he Oracle Clust er Regist ry ( OCR) _______________________ 443
Replacing t he OCR ____________________________________________________ 444
Adding and Removing t he OCR __________________________________________ 444
Repairing t he OCR ____________________________________________________ 444
Making Physical Backups of t he OCR ______________________________________ 444
Recovering t he OCR using t he Physical Backups _____________________________ 445
Making Logical Backups of t he OCR ( Export ing) ______________________________ 445
Making Logical Backups of t he OCR ( I mport ing) _____________________________ 445
Diagnosing OCR Problems wit h t he OCRDUMP and OCRCHECK Ut ilit ies ____________ 446
Admi ni st er i ng St or age _________________________________ 447
Dat afile Access in Real Applicat ion Clust ers _________________________________ 447
Redo Log File St orage in Real Applicat ion Clust ers ____________________________ 447
Aut omat ic Undo Management in Real Applicat ion Clust ers______________________ 447
Administ ering ASM I nst ances wit h SRVCTL in RAC ______________________ 448
Admi ni st er i ng Cl ust er Dat abases _________________________ 449
Displaying Current I nst ance in SQL* Plus Prompt _______________________ 449
St art ing and St opping I nst ances and RAC Dat abases ____________________ 449
St art ing Up and Shut t ing Down wit h SQL* Plus ______________________________ 449
I nt ermit t ent Windows Shut down I ssue in RAC Environment s ___________________ 449
St art ing Up and Shut t ing Down wit h SRVCTL ________________________________ 449
Cust omizing How Oracle Clust erware Manages RAC Dat abases ____________ 450
Swit ching Bet ween t he Dat abase Aut omat ic and Manual Policies ___________ 450
Page 23 Oracle DBA Code Examples

Cust omizing Resource Paramet ers (like AUTO_START) ___________________ 450
Handling I nit ializat ion Paramet er Files in RAC __________________________ 451
Set t ing Server Paramet er File Paramet er Values for Real Applicat ion Clust ers ______ 451
Paramet ers Used in RAC Dat abases _______________________________________ 451
Paramet ers t hat Must Have I dent ical Set t ings on All I nst ances __________________ 451
Paramet ers That Must Have Unique Set t ings on All I nst ances ___________________ 452
Paramet ers t hat Should Have I dent ical Set t ings on All I nst ances ________________ 452
ASM I nst ance I nit ializat ion Paramet ers and RAC _____________________________ 452
Dropping a RAC Dat abase _________________________________________ 452
Wor k l oad Management i n RAC ___________________________ 453
Types of Workload Dist ribut ion _____________________________________ 453
Connect ion Load Balancing ________________________________________ 453
Client -Side Load Balancing and Failover ____________________________________ 453
Server- Side Load Balancing _____________________________________________ 453
Fast Applicat ion Not ificat ion ( FAN) __________________________________ 454
Using Fast Applicat ion Not ificat ion Callout s ____________________________ 454
Configuring t he Server-Side ONS ___________________________________ 456
Administ ering Load Balancing Advisory _______________________________ 456
Monit oring Load Balancing Advisory _________________________________ 457
Transparent Applicat ion Failover (TAF) _______________________________ 458
TAF Basic Configurat ion wit hout FAN ( From Client Side) _______________________ 458
TAF Basic Configurat ion wit h FAN (Server- Side) _____________________________ 458
TAF Preconnect Configurat ion____________________________________________ 458
Verifying TAF Configurat ion _____________________________________________ 459
Enabling Dist ribut ed Transact ion Processing for Services _________________ 459
Admi ni st er i ng Ser v i ces ________________________________ 460
Service At t ribut es _______________________________________________ 460
Administ ering Services wit h DBCA __________________________________ 460
Administ ering Services wit h PL/ SQL _________________________________ 460
Administ ering Services wit h SRVCTL _________________________________ 462
Cont rolling t he Preferred and Available I nst ances _______________________ 462
Using Services wit h Client Applicat ions _______________________________ 463
Page 24 Oracle DBA Code Examples

Services and t he Scheduler ________________________________________ 463
Measuring Performance by Service Using t he AWR ______________________ 464
Service Thresholds and Alert s ______________________________________ 466
Service Performance Views ________________________________________ 467
Rest rict ed Session and Services ____________________________________ 467
Conf i gur i ng Recov er y Manager and Ar chi v i ng _______________ 468
Backup Possible Dist ribut ions in RAC ________________________________ 468
RMAN Rest ore Scenarios for Real Applicat ion Clust ers ___________________ 468
Clust er File Syst em Rest ore Scheme ______________________________________ 468
Non- Clust er File Syst em Rest ore Scheme __________________________________ 468
RMAN and Oracle Net in Real Applicat ion Clust ers ______________________ 468
Connect ing t o Specific Node _______________________________________ 468
I nst ance Recovery in Real Applicat ion Clust ers _________________________ 469
Single Node Failure in Real Applicat ion Clust ers ______________________________ 469
Mult iple- Node Failures in Real Applicat ion Clust ers ___________________________ 469
Configuring t he RMAN Snapshot Cont rol File Locat ion____________________ 469
Configuring t he RMAN Cont rol File and SPFI LE Aut obackup Feat ure _________ 469
Configuring Channels for RMAN in Real Applicat ion Clust ers _______________ 469
Configuring Channels t o use Aut omat ic Workload Balancing ____________________ 469
Configuring Channels t o Use a Specific I nst ance _____________________________ 469
Node Affinit y Awareness of Fast Connect ions __________________________ 470
Archived Redo Log File Convent ions in RAC ___________________________ 470
Archive Redo Log Configurat ion Scenarios ____________________________ 470
Aut omat ic St orage Management and CFS Archiving Scheme ____________________ 470
Non- Clust er File Syst em Local Archiving Scheme _____________________________ 470
Changing t he Archiving Mode in Real Applicat ion Clust ers ________________ 471
Delet ing Archived Redo Logs aft er a Successful Backup __________________ 471
Monit oring t he Archiver Processes __________________________________ 471
Log_Archive_Dest _1 Set To Default Even When DB_Recovery_File_Dest I s Set
( Bug 6373164) _________________________________________________ 471
Media Recovery in Real Applicat ion Clust ers ___________________________ 473
Parallel Recovery in Real Applicat ion Clust ers __________________________ 473
Page 25 Oracle DBA Code Examples

Using a Flash Recovery Area in RAC _________________________________ 473
Managi ng Back up and Recov er y _________________________ 474
Admi ni st r at i ve Opt i ons ________________________________ 475
Using Ent erprise Manager Grid Cont rol t o Discover Nodes and I nst ances _____ 475
Addit ional I nformat ion About SQL* Plus in RAC _________________________ 475
How SQL* Plus Commands Affect I nst ances _________________________________ 475
Displaying Running I nst ances____________________________________________ 475
Displaying Connect I dent ifier ____________________________________________ 475
Quiescing RAC Dat abases _________________________________________ 476
Quiesced St at e and Cold Backups ________________________________________ 476
Transparent Dat a Encrypt ion and Wallet s in RAC _______________________ 476
Administ ering Syst em and Net work I nt erfaces wit h oifcfg ________________ 476
Defining Net work I nt erfaces wit h oifcfg ____________________________________ 476
Synt ax and Commands for t he oifcfg Command- Line Tool ______________________ 476
Changing Public or I nt erconnect I P Subnet Configurat ion _________________ 477
Changing VI P Addresses __________________________________________ 477
Addi ng Nodes and I nst ances on UNI X- Based Sy st ems ________ 479
Adding Nodes t o a RAC Environment ________________________________ 479
Cloning Oracle Clust erware and RAC Soft ware in Grid Environment s ________ 479
Quick-St art Node and I nst ance Addit ion Procedures _____________________ 480
Adding an Oracle Clust erware Home t o a New Node __________________________ 480
Adding an Oracle Home wit h RAC t o a New Node ____________________________ 481
Det ailed Node and I nst ance Addit ion Procedure ________________________ 481
St ep 1: Connect ing New Nodes t o t he Clust er _________________________ 481
St ep 2: Ext ending Clust erware and Oracle Soft ware t o New Nodes _________ 481
St ep 3: Preparing St orage on New Nodes _____________________________ 481
St ep 4: Adding Nodes at t he Oracle RAC Dat abase Layer _________________ 482
St ep 5: Adding Dat abase I nst ances t o New Nodes ______________________ 482
Del et i ng Nodes and I nst ances on UNI X- Based Sy st ems _______ 483
Opt ion 1: Quick-St art Node and I nst ance Delet ion Procedures _____________ 483
Page 26 Oracle DBA Code Examples

Delet ing an Oracle Home wit h RAC from an Exist ing Node _____________________ 483
Delet ing an Oracle Clust erware Home from an Exist ing Node ___________________ 484
Opt ion 2: Det ailed Node and I nst ance Delet ion Procedure ________________ 485
St ep 1: Delet ing DB I nst ances from Real Applicat ion Clust ers Dat abases _____ 485
Using Ent erprise Manager t o Delet e Dat abase I nst ances from Exist ing Nodes ______ 485
Using DBCA in I nt eract ive Mode t o Delet e Dat abase I nst ances from Exist ing Nodes __ 485
Using DBCA in Silent Mode t o Delet e I nst ance from Exist ing Nodes ______________ 486
St ep 2: Delet ing Nodes from Real Applicat ion Clust ers Dat abases __________ 486
ASM I nst ance Clean-Up Procedures for Node Delet ion ___________________ 487
Addi ng and Del et i ng Nodes and I nst ances on Wi ndow s- Based
Sy st ems ____________________________________________ 488
Cloning Oracle Clust erware and RAC Soft ware in Grid Environment s ________ 489
Quick-St art Node and Dat abase I nst ance Addit ion and Delet ion Procedures ___ 489
Adding an Oracle Clust erware Home t o a New Node __________________________ 489
Adding an Oracle Home wit h RAC t o a New Node ____________________________ 489
Delet ing an Oracle Home wit h RAC from an Exist ing Node _____________________ 489
Delet ing an Oracle Clust erware Home from an Exist ing Node ___________________ 489
Det ailed Node and Dat abase I nst ance Addit ion and Delet ion Procedures _____ 489
Overview of Node Addit ion Procedures _______________________________ 489
St ep 1: Connect ing New Nodes t o t he Clust er _________________________ 489
Making Physical Connect ions ____________________________________________ 489
I nst alling t he Operat ing Syst em __________________________________________ 489
Verifying t he I nst allat ion wit h t he Clust er Verificat ion Ut ilit y ____________________ 489
Checking t he I nst allat ion _______________________________________________ 489
St ep 2: Ext ending Oracle Soft ware t o New Nodes at t he Oracle Clust erware __ 489
St ep 3: Preparing St orage on New Nodes _____________________________ 489
Raw Device St orage Preparat ion for New Nodes _____________________________ 489
St ep 4: Adding Nodes at t he Oracle RAC Dat abase Layer _________________ 489
St ep 5: Adding Dat abase I nst ances t o New Nodes ______________________ 489
Using Ent erprise Manager t o Add Dat abase I nst ances t o New Nodes _____________ 489
Using DBCA in I nt eract ive Mode t o Add Dat abase I nst ances t o New Nodes_________ 489
Using DBCA in Silent Mode t o Add Dat abase I nst ances t o New Nodes _____________ 489
Connect ing t o iSQL* Plus aft er Adding a Node _______________________________ 489
Adding Nodes t hat Already Have Clust erware and Oracle Soft ware t o a Clust er 490
Page 27 Oracle DBA Code Examples

Overview of Node Delet ion Procedures _______________________________ 490
St ep 1: Delet ing I nst ances from Real Applicat ion Clust ers Dat abases________ 490
Using Ent erprise Manager t o Delet e Dat abase I nst ances from Exist ing Nodes ______ 490
Using DBCA in I nt eract ive Mode t o Delet e Dat abase I nst ances from Exist ing Nodes __ 490
Using DBCA in Silent Mode t o Delet e I nst ance from Exist ing Nodes ______________ 490
St ep 2: Delet ing Nodes from Real Applicat ion Clust ers Dat abases __________ 490
St ep 3: ASM I nst ance Clean- Up Procedures for Node Delet ion _____________ 490
Moni t or i ng Per f or mance _______________________________ 491
RAC Common Tuning Tips _________________________________________ 491
I nst ance Recovery and RAC _______________________________________ 491
Global Cache Wait Event s _________________________________________ 491
Monit oring Performance in Ent erprise Manager _________________________ 492
Using t he Clust er Dat abase Performance Page_______________________________ 492
Using t he Clust er Dat abase I nst ance Performance Page _______________________ 492
Using t he Clust er Performance Page ______________________________________ 493
Using t he Clust er I nt erconnect s Page ______________________________________ 493
Mak i ng Appl i cat i ons Hi ghl y Av ai l abl e Usi ng Or acl e Cl ust er w ar e _ 494
Making an Applicat ion Highly Available Examples _______________________ 494
Example1: Making an Applicat ion Highly Available ___________________________ 494
Example2: Making an Applicat ion Highly Available ___________________________ 496
Managing Aut omat ic Oracle Clust erware Resource Operat ions for Act ion Script s ____ 498
Displaying Clust erware Applicat ion and Applicat ion Resource St at us I nformat ion ____ 498
Unregist ering Applicat ions and Applicat ion Resources _________________________ 499
RAC Tr oubl eshoot i ng __________________________________ 500
Diagnosing t he Oracle Clust erware High Availabilit y Component s ___________ 500
Debugging Recommnedat ion ____________________________________________ 500
Clust erware Log Files and t he Unified Log Direct ory St ruct ure __________________ 500
Dynamic Debugging ___________________________________________________ 500
Component Level Debugging ____________________________________________ 500
Oracle Clust erware Shut down and St art up __________________________________ 501
Enabling and Disabling Oracle Clust erware Daemons _________________________ 501
Diagnost ics Collect ion Script ____________________________________________ 501
The Oracle Clust erware Alert s ___________________________________________ 501
Page 28 Oracle DBA Code Examples

Resource Debugging ___________________________________________________ 501
Checking t he Healt h of t he Clust erware ____________________________________ 501
Troubleshoot ing t he Oracle Clust er Regist ry ________________________________ 501
Troubleshoot ing Host name Changes and CSS _______________________________ 501
Enabling Addit ional Tracing for Real Applicat ion Clust ers High Availabilit y _________ 502
Diagnosing Oracle Real Applicat ion Clust ers Component s ______________________ 502
Where t o Find Files for Analyzing Errors ____________________________________ 502
Using I nst ance- Specific Alert Files in Real Applicat ion Clust ers __________________ 502
Enabling Tracing for Java- Based Tools and Ut ilit ies in Real Applicat ion Clust ers _____ 502
Resolving Pending Shut down I ssues ______________________________________ 502
Using t he Clust er Verificat ion Ut ilit y _________________________________ 502
Clust er Verify Locat ions ________________________________________________ 502
Clust er Verify St ages __________________________________________________ 502
Clust er Verify Component s ______________________________________________ 503
CVU Component Verificat ion Examples ____________________________________ 503
Underst anding CVU Commands, Help, Out put , and Nodelist Short cut s ____________ 504
Performing Various CVU Test s ___________________________________________ 504
Known I ssues for t he Clust er Verificat ion Ut ilit y______________________________ 504
Par t 8 Or acl e War ehousi ng __________________________ 505
Or acl e War ehouse Bui l der ( OWB) ________________________ 506
Oracle Warehouse Builder Archit ect ure _______________________________ 506
St art ing and St oping t he Service ___________________________________ 506
Configuring t he Reposit ory and Workspaces ___________________________ 506
St eps of Using Warehouse Builder __________________________________ 507
Mapping Operat ors ______________________________________________ 508
Par t 9 Or acl e Dat abase Ut i l i t i es _______________________ 510
Usi ng SQL* Pl us ______________________________________ 511
Using SQL* Plus Command- Line Opt ions ______________________________ 511
St art ing SQL* Plus Session ________________________________________ 511
Cont rolling User Privileges in SQL* Plus _______________________________ 511
Set t ing t he SQL* Plus Environment wit h t he SET Command _______________ 512
Set t ing SQL* Plus Preferances ______________________________________ 512
Page 29 Oracle DBA Code Examples

Logging SQL* Plus Errors __________________________________________ 513
Key SQL* Plus "Working" Commands_________________________________ 513
Creat ing Command Files in SQL* Plus ________________________________ 513
Copying Tables wit h t he COPY Command _____________________________ 513
Creat ing Web Pages Using SQL* Plus _________________________________ 514
Using SQL t o Generat e SQL _______________________________________ 514
Enabling AUTOTRACE for a User ____________________________________ 514
Using rlwrap Ut ilit y wit h SQL* Plus in Unix-Based Syst ems ________________ 514
Usi ng SQL* Loader Ut i l i t y _______________________________ 516
I nvoking SQL* Loader ____________________________________________ 516
Using SQL* Loader Cont rol File _____________________________________ 516
Loading Excel File int o a Table using SQL* Loader _______________________ 522
Loading Large Fields int o a Table ___________________________________ 522
Using Direct Load Opt ions _________________________________________ 523
Dat a Pump Ex por t and I mpor t ___________________________ 524
Dat a Pump Component s __________________________________________ 524
Dat a Pump Export I nt erfaces ______________________________________ 524
Export Modes Paramet ers _________________________________________ 524
I nvoking Export Dat a Pump Examples _______________________________ 524
Export Filt ering Paramet ers________________________________________ 525
Export Remapping Paramet ers _____________________________________ 525
Sampling Export Dat a ____________________________________________ 525
Export Encrypt ion Paramet ers ______________________________________ 525
Export Est imat ing Paramet ers ______________________________________ 526
Export Net work Link Paramet er ____________________________________ 526
I mport Modes Paramet ers _________________________________________ 526
File- and Direct ory- Relat ed Paramet ers ______________________________ 526
Using TABLE_EXI STS_ACTI ON Paramet er _____________________________ 526
I mport Filt ering Paramet ers _______________________________________ 526
I mport Remapping Paramet ers _____________________________________ 526
Page 30 Oracle DBA Code Examples

I gnoring Nondeferred Const raint s ___________________________________ 527
I mport Net work Link Paramet er ____________________________________ 527
I mport Flashback Paramet ers ______________________________________ 527
Monit oring a Dat a Pump Jobs ______________________________________ 527
LogMi ner ___________________________________________ 529
Types of Supplement al Logging ____________________________________ 529
Levels of Supplement al Logging ____________________________________ 529
Disabling Dat abase- Level Supplement al Logging _______________________ 529
LogMiner Dict ionary Opt ions _______________________________________ 530
Redo Log File Opt ions ____________________________________________ 530
OPTI ONS possible values in DBMS_LOGMNR.START_LOGMNR: ____________ 530
Obt aining LogMiner Operat ional I nformat ion___________________________ 530
Examples of Using LogMiner _______________________________________ 531
Wit hout Sepecifying t he Redo Files _______________________________________ 531
Wit Sepecifying t he Redo Files ___________________________________________ 532
Par t 10 Mi scel l aneous Or acl e Dat abase Topi cs _____________ 534
Managi ng Or acl e Dat abase Cont r ol _______________________ 535
Configuring and Using t he Dat abase Cont rol ___________________________ 535
I mplement ing EM Dat abase Cont rol Aut o St art up _______________________ 535
I nst al l i ng Or acl e 10g R5 ( 10. 2) Ent er pr i se Manager Gr i d Cont r ol f or
Li nux x 86___________________________________________ 537
I nst allat ion Environment __________________________________________ 537
Required Soft ware ______________________________________________ 537
Used Hardware _________________________________________________ 537
I nst allat ion St eps _____________________________________________________ 537
Not e: A reference t o t he requirement s is Not e I D 419646. 1.____________________ 537
Remot e Di agnost i c Agent ( RDA) _________________________ 543
Using Remot e Diagnost ic Agent ( RDA) _______________________________ 543
Par t 11 PL/ SQL Sampl es ______________________________ 544
Page 31 Oracle DBA Code Examples

PL/ SQL Basi cs _______________________________________ 545
PL/ SQL Dat a Types ______________________________________________ 545
Cont rolling Compile- Time Displayed Warnings _________________________ 546
Cat ching Ret urned Errors _________________________________________ 547
Hiding Code ___________________________________________________ 547
Cont rolling Program Flow _________________________________________ 547
Usi ng Cur sor s _______________________________________ 550
Usi ng Recor ds _______________________________________ 554
Usi ng Tabl e Funct i ons _________________________________ 556
Usi ng Col l ect i ons _____________________________________ 558
Using VARRAYS _________________________________________________ 558
Using Nest ed Tables _____________________________________________ 561
Using Associat ive Arrays __________________________________________ 562
Using Collect ion API _____________________________________________ 564
Handl i ng Er r or s ______________________________________ 569
Predefined Except ions ____________________________________________ 569
Using User- Defined Except ions _____________________________________ 569
Using RAI SE_APPLI CATI ON_ERROR _________________________________ 570
Aut onomous Tr ansact i ons ______________________________ 572
Some St or ed Subpr obr ams Concept s ______________________ 573
Serially Reusable Packages ________________________________________ 573
St ored Subprograms and Roles _____________________________________ 574
I nvokers vs. Definers Right s ______________________________________ 574
Pinning an Programunit in t he Shared Pool ____________________________ 575
Usi ng Tr i gger s _______________________________________ 576
Rest rict ions on Triggers ________________________________________________ 576
Using DML Triggers ______________________________________________ 576
Page 32 Oracle DBA Code Examples

Using I nst ead- of Triggers _________________________________________ 577
Using Syst em Triggers ___________________________________________ 578
Handling Mut at ing Tables in Triggers ________________________________ 582
Dropping and Disabling Triggers ____________________________________ 583
Usi ng Dy nami c SQL ___________________________________ 584
Working wit h Nat ive Dynamic SQL __________________________________ 584
Using DBMS_SQL _______________________________________________ 587
Cal l i ng Jav a f r om PL/ SQL ______________________________ 596
Conf i gur i ng Or acl e Dat abase t o Use Ex t er nal Rout i nes ________ 597
Usi ng Lar ge Obj ect s ( LOBs) _____________________________ 600
Creat ing LOB ___________________________________________________ 600
Using SQL wit h I nt ernal LOBs ______________________________________ 600
Using LOBs in PL/ SQL ____________________________________________ 601
Performance Considerat ions _______________________________________ 610
Using Ret urning Clause ________________________________________________ 610
Using CONTEXT I ndex _________________________________________________ 611
Migrat ing from LONGs t o LOBs _____________________________________ 612
Par t 12 Appendi x es _________________________________ 613
Pr ogr am Uni t s and Scr i pt s Used i n t he Document ____________ 614
Ret urn Paramet er Value for Normal User _____________________________ 614
Applying Random Load on Dat abase Sample 1 _________________________ 615
Set up ______________________________________________________________ 615
Using t he Load Generat or Script s _________________________________________ 621
Applying Random Load on Dat abase Sample 2 _________________________ 622
Set up ______________________________________________________________ 622
Using t he Load Generat or Script s _________________________________________ 630
SQL Usage Sampl es ___________________________________ 631
Merge Command ________________________________________________ 631
Page 33 Oracle DBA Code Examples

Mult it able I nsert s _______________________________________________ 631
Parallel I nsert __________________________________________________ 632
Delet ing Duplicat e Rows in a Table __________________________________ 632
Mor e DBA Scr i pt s _____________________________________ 633
Compare Table Sizes in Two Dat abases ______________________________ 633
Usi ng Li nux f or Or acl e _________________________________ 634
Verifying t he Kernel _____________________________________________ 634
Checking for a Taint ed Kernel ______________________________________ 634
Support ed Hardware _____________________________________________ 634
Using Oracle Relink Ut ilit y _________________________________________ 634
Cert ified and Support ed File Syst ems ________________________________ 635
Ent erprise Linux Runlevels ________________________________________ 635
Using / et c/ orat ab File and dbst art Ut ilit y _____________________________ 635
Aut omat ing Jobs ________________________________________________ 636
Using cron __________________________________________________________ 636
Using anacron ________________________________________________________ 637
Using at command ____________________________________________________ 637
Using bat ch command _________________________________________________ 638
Task Scheduler _______________________________________________________ 638
Configuring Linux Memory for Oracle ______________________________________ 638
Using Linux Performance Monit oring Tools ____________________________ 638
About Linux Tools _____________________________________________________ 639
Using Linux Tools _____________________________________________________ 639
Checking Some General Guideline on Truning Oracle in Linux _____________ 640
Troubleshoot ing Oracle Dat abase in Linux ____________________________ 641
Using OS Wat cher ( OSW) _______________________________________________ 641
Using OS Wat cher Graphs( OSWg) ________________________________________ 643
Using t he On- Board Monit or ( LTOM) ______________________________________ 643
Using st race _________________________________________________________ 644

Page 34 Oracle DBA Code Examples

Par t 1 Or acl e DBA Fundament al s
Page 35 Oracle DBA Code Examples

DBA: Best Pr act i ces
DBA: Best Pr act i ce Gui del i nes f or St andal one and RAC Dat abases
Use SPFI LE
Use t emporary t ablespaces
Regist er all inst ances wit h remot e list eners
Use Dat abase Resource Manager
Use resumable space allocat ion
Use Aut omat ic Segment Space Management
Use locally managed t ablespaces
Use Aut omat ic Undo Management
Enable block checking
Enable Flashback Dat abase
Enable ARCHI VELOG mode and use a flash recovery area
Use aut o- t une checkpoint ing
Log checkpoint s t o t he alert log
Mult iplex product ion and st andby redo logs
Set CONTROL_FI LE_RECORD_KEEP_TI ME long enough
Creat e t wo or more cont rol files
Page 36 Oracle DBA Code Examples

Or acl e Dat abase I nst al l at i on Consi der at i on
Est i mat i ng Di sk and Memoer y Requi r ement s
Make sure t he st orage vendor is list ed in t he Oracle St orage Compat ibilit y Program
( OSCP) .
Allocat e swap space t hat is about t wo t o t hree t imes your Oracle RAM allocat ion.
On Est imat ing t he RAM:
o OS RAM: 20 percent of t ot al RAM for MS-Windows, 10% of RAM for UNI X
o Each Oracle connect ion consumes t wo megabyt es of RAM + sort _area_size +
hash_area_size
o I f dynamic memory sizing is not used:
Shared_pool_size: I f all t he SQL st at ement s t hat sent t o ORACLE are using bind
variable adequat ely, t hen 300M is enough in most cases
Large_pool_size: For dedicat ed Oracle server: 20- 30M
Java_pool_size= 10M
Dat a buffer: All t he rest RAM should be allocat ed t o Dat a buffer.
Opt i mal Fl ex i bl e Ar chi t ect ur e
The OFA is a set of recommendat ions from Oracle Corporat ion aimed at simplifying
management of complex soft ware and dat abases oft en running under mult iple versions of
soft ware.
OFA Guidelines
o Arrange at least four mount point s t hat have names like / u01, / u02, / u03, and so
on.
o I f t he Oracle soft ware owner is "oracle", make / u01/ app/ oracle your ORACLE_BASE
# mkdir -p /u01/app/oracle
# chown -R oracle:oinstall /u01/app/oracle
# chmod -R 775 /u01/app/oracle
o Make Oracle_HOME $ORACLE_BASE/ product / version/ { db| client | companion} _[ n] .
Examples:
/u01/app/oracle/product/11.1.0/db_1
/u01/app/oracle/product/11.1.0/client_1
/u01/app/oracle/product/10.1.0.2.0/db_1
o I nst all Flash Recovery Area in $ORACLE_BASE/ flash_recovery_area in a mount point
different from db files.
o Administ rat ive Files are t o be creat ed in ORACLE_BASE/ admin/ dbname
o St ore Oracle DB files in t he mount ed point s.
# mkdir /mount_point/oradata
# chown oracle:oinstall /mount_point/oradata
# chmod 775 /mount_point/oradata
/u01/oradata/prod1/control01.ctl
/u05/oradata/prod1/control02.ctl
/u02/oradata/prod1/redo01.log
/u04/oradata/prod1/redo02.log
dat a file has t his format :
/u20/oradata/prod/system01.dbf
Page 37 Oracle DBA Code Examples

Or acl e Pr oduct s I nst al l ed w i t h t he 11.1 Rel ease
The following product s are inst alled by default wit h dat abase server 11g:
o Oracle Applicat ion Express
o Oracle Warehouse Builder
o Oracle Configurat ion Manager: Tool t hat collect s and uploads configurat ion
informat ion t o t he Oracle configurat ion reposit ory
o Oracle SQL Developer
o Oracle Dat abase Vault : Tool t hat enables you t o secure business dat a

Page 38 Oracle DBA Code Examples

I nst al l i ng Or acl e 10g R2 on En t er pr i se Li nux 4
I nst al l at i on Envi r onment
Emulat ion soft ware: VMWare Server 2 on for Windows
OS: Oracle Linux Ent erprise 4. 5 for x86: kernel 2. 6.9
Requi r ed Sof t w ar e
Oracle Dat abase 10g Release 2 for Linux x86 32-bit
Used Har dw ar e
I n t he VMWare: creat e one virt ual machine ( oradb1) wit h t he following specs:
o 2 GB RAM
o an et hernet card
o one local hardisk wit h 20 GB
I nst al l at i on Pl an
1. Preinst allat ion t asks:
o Hardware requirement s
o Soft ware requirement s
o Environment configurat ion
2. Oracle Dat abase 10g Soft ware I nst allat ion
3. Apply Pat chset 3 ( 10. 2. 0. 4) for Clust erware and Dat abase Soft ware
4. Configure List eners
5. Creat e Dat abase
6. Post inst allat ion t asks

1. Pr ei nst al l at i on t ask s
I nst all Oracle Ent erprise Linux in t he first local hardisk. I nst all not hing in t he remaining
disks.
Not e: for a product ion syst em, consider becoming an Oracle Unbreakable Linux cust omer
and regist er your server on t he Unbreakable Linux Net work.
o Give t he et hernet card I P 192.168.4.21 and t he host name oradb1.mydomain.com.
Define a gat eway. I f it does not exist , make it same as t he host I P address.
o I nsall t he following packages:
Deskt op Environment s
o GNOME Deskt op Environment
Page 39 Oracle DBA Code Examples

Deskt op
o X Window Syst em
o Gnome
Applicat ions
o Graphical I nt ernet ( opt ional)
Servers
o Do not select anyt hing in t his group.
Development
o Development Tools
Syst em
o Administ rat ion Tools
o Syst em Tools
Add t he package ' sysst at ' by clicking on t he Det ails link
and select ing "sysst at - The sar an iost at syst em
monit oring commands. " from t he Opt ional Packages list .
Miscellaneous
o Do not select anyt hing in t his group.
Complet e t he inst allat ion
I nst all furt her packages:
# to know distribution and version of Linux
cat /etc/issue
# to know kernel version (and its errata level)
uname -r

# from CD 3
rpm -Uvh libaio*
rpm -Uvh openmotif21-2.1.30-11.RHEL4.6.i386.rpm
rpm -Uvh openmotif-2.2.3-10.1.el4.i386.rpm

# those packages downloaded from http://rpm.pbone.net
rpm -e compat-libstdc++-296-2.96-132.7.2
rpm -Uvh compat-libstdc++-7.3-2.96.128.i386.rpm
rpm -Uvh compat-libstdc++-devel-7.3-2.96.128.i386.rpm
rpm -Uvh compat-gcc-7.3-2.96.128.i386.rpm
rpm -Uvh compat-gcc-c++-7.3-2.96.128.i386.rpm

# confirm the required packages are installed:
rpm -qa|grep gcc-
rpm -qa|grep glibc-
rpm -qa|grep compat-db-
rpm -qa|grep compat-gcc-
rpm -qa|grep compat-gcc-c++-
rpm -qa|grep compat-libstdc++-
rpm -qa|grep compat-libstdc++-devel-
rpm -qa|grep control-center-2.8.0
rpm -qa|grep openmotif21-
rpm -qa|grep setarch-

# SELINUX must be disabled
Page 40 Oracle DBA Code Examples

cat /etc/selinux/config | grep SELINUX=
vi /etc/selinux/config
SELINUX=disabled
shutdown -h now -r
Check t he hardware requirement s
# Hardware Requirements
# At least 2 GB of physical memory
grep MemTotal /proc/meminfo

# swap space: twice the amount of physical memory
grep SwapTotal /proc/meminfo
# if you don't have enought swap,
# you can add swap space by creating a temporary swap file.
# let's say about 500MB:
dd if=/dev/zero of=tempswap bs=1k count=500000
chmod 600 tempswap
mke2fs tempswap
mkswap tempswap
swapon tempswap

# 400 MB disk space in /tmp
df -k /tmp
# 4 GB of disk space for Oracle software
df

The size of the shared memory should be at least the greater of
MEMORY_MAX_TARGET and MEMORY_TARGET for each Oracle instance on the computer.
To determine the amount of shared memory available, enter the following
command:
df -h /dev/shm/
# to adjust the shared memory file system size:
umount tmpfs
mount -t tmpfs shmfs -o size=1200m /dev/shm
Creat e t he required net work configurat ion:
# Network names Resolution
# configure /etc/hosts if no domain server is used
cat /etc/hosts
127.0.0.1 localhost.localdomain oradb1.mydomain.com localhost
oradb1
Creat e and configure t he required OS users and groups
# inventory group
groupadd -g 501 oinstall
groupadd -g 502 dba
# oracle software owner user
/usr/sbin/useradd -u 200 -g oinstall -G dba oracle
passwd oracle
# make sure nobody user exists (if not there, create it useradd nobody)
id nobody

# The oracle User Environment
# in /home/oracle/.bash_profile
# export DISPLAY if required
export ORACLE_BASE=/u01/app/oracle
if [ $USER = "oracle" ]; then
Page 41 Oracle DBA Code Examples

if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
umask 022
fi
export EDITOR=vi
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_PATH=$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin
export ORACLE_SID=ora10g
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
export NLS_DATE_FORMAT="mm/dd/yyyy hh24:mi:ss"
export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin:$ORA_CRS_HOME/bin
export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORA_NLS10=$ORACLE_HOME/nls/data
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export CLASSPATH=$ORACLE_HOME/JRE
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export THREADS_FLAG=native
export TEMP=/tmp
export TMPDIR=/tmp

# User Shell Limits
# memlock is used to increase the per-process max locked memory
vi /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft memlock 3145728
oracle hard memlock 3145728
Configure kernel paramet ers and shell limit s
Not e: I f you make a mist ake wit h a paramet er set t ing and your syst em does not st art , t hen
you must st art Linux in t he single- user runlevel ( runlevel 1) . At t his runlevel, t he
/ et c/ sysct l. conf file is not run.
# Configuring kernel parameters and shell limits
# they can be tuned for a production db
# Append the following to the /etc/sysctl.conf file as the root user:
vi /etc/sysctl.conf
kernel.sem = 250 32000 100 128
# maximum size may be given to SGA (max 4GB)
# kernel.shmmax = 536870912 (512 M)
# following is 1 GB
kernel.shmmax =1073741824
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
Page 42 Oracle DBA Code Examples

net.core.wmem_default = 262144
net.core.wmem_max = 262144
# to take immediate effect
/sbin/sysctl -p
Configure hangcheck- t imer kernel module:
# check hangcheck-timer Module Configuration
# with this module, if the kernel hangs, the machine will reboot
# verify the module is loaded
/sbin/lsmod | grep -i hang

# if not loaded, load it
vi /etc/modprobe.conf
options hangcheck-timer hangcheck_tick=30 hangcheck_margin=180
# execute and add in the file
vi /etc/rc.local
/sbin/modprobe hangcheck-timer
Creat e t he required direct ories for t he Oracle dat abase soft ware:
# to know if there is an existing oracle inventory
# from its output, ORACLE_BASE will be parent of oraInventory
more /etc/oraInst.loc
# to identify existing Oracle home directories
more /etc/oratab

# in the example above, /u01 should be owned by the root user
# and writable by group oinstall
cd /
chown -R oracle:oinstall /u01
chmod -R 775 /u01/oracle
mkdir -p /u01/app/oracle/product/10.2.0/db_1
mkdir /u01/stage
chown -R oracle:oinstall /u01/app/oracle
chmod -R 775 /u01/oracle
chown oracle:oinstall /u01/stage
As oracle, copy soft ware inst allat ion int o / u01/ st age10g/ db
Disable screensavers on host & guest machines.
o I n Oracle Linux: Applicat ions- > Preferences- > Screen Saver- > Mode: Disable Screen
Saver
o Do t he same aft er logging off and logging on again as oracle user.
Rest art t he machine.
2. Or acl e Dat abase 10g Sof t w ar e I nst al l at i on
-- start OUI
su - oracle
cd /u01/stage10g/db
./runInstaller

Follow the steps.
Install database software only.
3. Appl y Pat chset 3 ( 10.2. 0. 4) f or Cl ust er w ar e and Dat abase Sof t w ar e
# extract 10g Release 2 (10.2.0.4) Patch Set 3 for Linux x86 to
Page 43 Oracle DBA Code Examples

/u01/stage10g/patch10.2.0.4/Disk1
mkdir /u01/stage10g/patch10.2.0.4

cd /u01/stage10g/patch10.2.0.4/Disk1
./runInstaller
Select path of ORACLE_HOME ->Next
4. Conf i gur e Li st ener s
cd /u01/app/oracle/product/10.2.0/db_1/bin
./netca &
Add a new listener

-- optionally, use net manager to manually register the database:
./netmgr
5. Cr eat e Dat abase
cd /u01/app/oracle/product/10.2.0/db_1/bin
./dbca &
follow the steps to create a customized database of SID "ora10g" and Global
nmae "ora10g.oradb1".
Do not use specific templates (non-Custom).

The DBCA may pop up the following message in the end of db creation:
"Error securing Database control, Database Control has been brought up in non-
secure mode. To secure the Database Control execute the following commands:
... "

# check the Oracle processes:
ps -eo pid -o command | grep ora_ | grep -v grep
6. Post i nst al l at i on Task s
Verify OEM:
# verify that OEM is working
http://oradb1:1158/em
# restart the dbconsole if required
emctl status dbconsole
emctl stop dbconsole
emctl start dbconsole
Verify iSQL* Plus:
# in the browser
http://oradb1:5560/isqlplus

# if not running:
isqlplusctl start
Consider implement ing aut omat ic dat abase st art up. See: Aut omat ically St art ing Dat abases
Consider implement ing aut omat ic EM Dat abase Cont rol st art up. See: I mplement ing EM
Dat abase Cont rol Aut o St art up
Consider using rlwrap ut ilit y wit h SQL* Plus and RMAN:
o Using rlwrap Ut ilit y wit h RMAN in Unix- Based Syst ems
o Using rlwrap Ut ilit y wit h SQL* Plus in Unix- Based Syst ems
For easy Oracle Home access:
Page 44 Oracle DBA Code Examples

echo "alias db='cd $ORACLE_HOME'" >> /home/oracle/.bashrc
Page 45 Oracle DBA Code Examples

I nst al l i ng Or acl e 11g R2 on En t er pr i se Li nux 5
Not e: for any inst allat ion, you should check t he Release Not es documenat ion before t aking
any pract ical st ep.
I nst al l at i on Envi r onment
Emulat ion soft ware: VMWare Workst at ion 7 for Windows
OS: Red Hat Ent erprise Linux 5.2 for x86
Requi r ed Sof t w ar e
Oracle Dat abase 11g Release 2 for Linux x86 32-bit
Used Har dw ar e
I n t he VMWare: creat e one virt ual machine wit h t he following specs:
o 2. 0 GB RAM
o One et hernet cards: can be configured as bridged or host - only in VMware.
o One local hardisk wit h 32 GB on SCSI 0: 0. I t will be used for soft ware inst allat ion.
o One local hardisk wit h 20 GB on SCSI 1: 0. I t will be used for Oracle Dat abase dat a
files.
o One local hardisk wit h 20 GB on SCSI 1: 1. I t will be used for Oracle Dat abase flash
recovery.
o CPU Count : 2 ( opt ional)
I nst al l at i on Pl an
1. Preinst allat ion t asks:
o Hardware requirement s
o Soft ware requirement s
o Environment configurat ion
2. Oracle Dat abase 11g Soft ware I nst allat ion
3. Apply Pat chset
4. Configure List eners
5. Creat e Dat abase
6. Post inst allat ion t asks

1. Pr ei nst al l at i on t ask s
I nst all Oracle Ent erprise Linux in t he first local hardisk. I nst all not hing in t he remaining
disks.
Page 46 Oracle DBA Code Examples

Not e: for a product ion syst em, consider becoming an Oracle Unbreakable Linux cust omer
and regist er your server on t he Unbreakable Linux Net work.
o Give t he et hernet card I P 192.168.4.100 and t he host name srv100.mydomain.com.
Define a gat eway. I f it does not exist , make it same as t he host I P address.
o I nsall t he following packages:
Deskt op Environment s
o GNOME Deskt op Environment
Applicat ions
o Graphical I nt ernet ( opt ional)
o Edit ors ( opt ional)
Development
o Development Libraries
o Development Tools
Servers
o Do not select anyt hing in t his group.
Base Syst em
o Administ rat ion Tools
o Syst em Tools
Add t he package 'sysst at ' by clicking on t he Det ails link
and select ing "sysst at - The sar an iost at syst em
monit oring commands. " from t he Opt ional Packages list .
X Window Syst em
Complet e t he inst allat ion
RHEL 5 Bug: Aft er t he I nst allat ion compelet s, RHEL 5.2 and below will hang on boot ing
when it reaches t o "st art ing udev" line. To solve t his problem, shut down t he Vmware
machine and change t he CPU count and Core Count t o only one. I mplement t he changes
below, t hen shut down t he machine, set CPU count back t o 2 and st art up t he machine.
put t he kernel command line paramet ers at t he end of t he "kernel" line:
vi /boot/grub/grub.conf
add divider=10 clocksource=acpi_pm
For example: kernel /vmlinuz-2.6.18 .. clock=acpi_pm divider=10
For Vmware machines, inst all VMWare t ools.
I nst all furt her packages:
# to know distribution and version of Linux (Red Hat Ent. 5.2 used)
cat /etc/issue
# to know kernel version (and its errata level) (2.6.18-92 or newer)
uname -r

# to list missed packages:
rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n' binutils \
compat-libstdc++-33 \
elfutils-libelf \
elfutils-libelf-devel \
gcc \
Page 47 Oracle DBA Code Examples

gcc-c++ \
glibc \
glibc-common \
glibc-devel \
glibc-headers \
ksh \
libaio \
libaio-devel \
libgcc \
libstdc++ \
libstdc++-devel \
make \
sysstat \
unixODBC \
unixODBC-devel

# for missed packages, install them:
rpm -Uvh libaio-devel-0.3.106-3.2.i386.rpm
rpm -Uvh unixODBC*


# SELINUX must be disabled
cat /etc/selinux/config | grep SELINUX=
vi /etc/selinux/config
SELINUX=disabled
shutdown -h now -r
Check t he hardware requirement s
# Hardware Requirements (in cluster nodes)
# At least 1.0 GB of physical memory
grep MemTotal /proc/meminfo

# swap space: same as the amount of physical memory
grep SwapTotal /proc/meminfo

# to display swap and memory in one command:
free

# if you don't have enought swap,
# you can add swap space by creating a temporary swap file.
# let's say about 500MB:
dd if=/dev/zero of=tempswap bs=1k count=500000
chmod 600 tempswap
mke2fs tempswap
mkswap tempswap
swapon tempswap

# 1 GB disk space in /tmp
df -h /tmp
# 4 GB of disk space for Oracle software
df

The size of the shared memory should be at least the greater of
MEMORY_MAX_TARGET and MEMORY_TARGET for each Oracle instance on the computer.
To determine the amount of shared memory available, enter the following
command:
df -h /dev/shm/
Page 48 Oracle DBA Code Examples

# to adjust the shared memory file system size:
umount tmpfs
mount -t tmpfs shmfs -o size=1200m /dev/shm
Creat e t he required net work configurat ion:
ping srv100
ping srv100.mydomain.com

# Network names Resolution
# configure /etc/hosts if no domain server is used
cat /etc/hosts
127.0.0.1 srv100.mydomain.com srv100 localhost.localdomain
localhost
Creat e and configure t he required OS users and groups
# all group and user ids on all the nodes must have identical id
# Grid Infrastructure (GI) and the Oracle RDBMS home will
# be installed using different users:
# oracle inventory group
/usr/sbin/groupadd -g 501 oinstall
/usr/sbin/groupadd -g 502 dba
/usr/sbin/useradd -u 502 -g oinstall -G dba oracle

# set passwords
passwd oracle

# make sure nobody user exists (if not there, create it useradd nobody)
id nobody


# define the env variables for oracle user
vi /home/oracle/.bash_profile
# Oracle evn vars
export EDITOR=vi
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_HOSTNAME=srv100.mydomain.com
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=ora11gr2
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib


# shell startup file
vi /etc/profile
if [ $USER = "oracle" ] || [ $USER = "grid" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
umask 022
Page 49 Oracle DBA Code Examples

fi


# for C shell
vi /etc/csh.login
if ( $USER = "oracle" || $USER = "grid" ) then
limit maxproc 16384
limit descriptors 65536
endif
Configure kernel paramet ers and shell limit s
Not e: I f you make a mist ake wit h a paramet er set t ing and your syst em does not st art , t hen
you must st art Linux in t he single- user runlevel ( runlevel 1) . At t his runlevel, t he
/ et c/ sysct l. conf file is not run.
# Configuring kernel parameters and shell limits
# they can be tuned for a production db
# Append the following to the /etc/sysctl.conf file as the root user:
vi /etc/sysctl.conf

# kernel.shmmax not stated in 11g R2 (max: 4GB) (169706.1)
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.aio-max-nr = 1048576
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

# to take immediate effect
/sbin/sysctl -p

# User Shell Limits
# memlock is used to increase the per-process max locked memory
vi /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

vi /etc/pam.d/login
session required pam_limits.so
Creat e t he required direct ories for t he Oracle dat abase soft ware:
# to know if there is an existing oracle inventory
# from its output, ORACLE_BASE will be parent of oraInventory
more /etc/oraInst.loc
# to identify existing Oracle home directories
more /etc/oratab

# Oracle Inventory Directory
# as a root
mkdir -p /u01/app/oraInventory
chown -R oracle:oinstall /u01/app/oraInventory
chmod -R 775 /u01/app/oraInventory
Page 50 Oracle DBA Code Examples


# Oracle Base Directory
mkdir -p /u01/app/oracle
#needed to ensure that dbca is able to run after the rdbms installation
mkdir -p /u01/app/oracle/cfgtoollogs
chown -R oracle:oinstall /u01/app/oracle
chmod -R 775 /u01/app/oracle

# Oracle RDBMS Home Directory
mkdir -p /u01/app/oracle/product/11.2.0/db_1
chown -R oracle:oinstall /u01/app/oracle/product/11.2.0/db_1
chmod -R 775 /u01/app/oracle/product/11.2.0/db_1

# staging area to hold software installation
mkdir -p /u01/stage11g/db
chown -R oracle:oinstall /u01/stage11g/db
chmod -R 775 /u01/stage11g/db
Part it ion and mount t he disks:
#define the disks to partition
fdisk -l | grep '^Disk'

# as a root, for the disks /dev/sdb and /dev/sdc
#partition the disks:
fdisk /dev/sdb
# answers: "n", "p", "1", "Return", "Return", "p" and "w"
Note: if the following message appears after the "w" command:
WARNING: Re-reading the partition table failed with error 16: Device or
resource busy, then you can avoid restarting the machine by the following
command: partprobe

# to make sure partions are created
ls -lX /dev/sd*

# format the paritions
mkfs.ext3 /dev/sdb1
mkfs.ext3 /dev/sdc1

# Mount the new disk
mkdir /u02
mount /dev/sdb1 /u02
mkdir /u03
mount /dev/sdc1 /u03
df -H

# Update /etc/fstab
vi /etc/fstab
/dev/sdb1 /u02 ext3 defaults 1 2
/dev/sdc1 /u03 ext3 defaults 1 2

# create folder for the db data
mkdir -p /u02/oradata/
chown -R oracle:oinstall /u02/oradata/
chmod -R 775 /u02/oradata/

# create folder for the flash area
mkdir -p /u03/oraflash/
Page 51 Oracle DBA Code Examples

chown -R oracle:oinstall /u03/oraflash/
chmod -R 775 /u03/oraflash/
As or acl e, copy soft ware inst allat ion int o / u01/ st age11g/ db
2. Or acl e Dat abase 11g Sof t w ar e I nst al l at i on
-- start OUI
su - oracle
cd /u01/stage11g/db
./runInstaller

Follow the steps.
Install database software only.
3. Appl y Pat chset

4. Conf i gur e Li st ener s
cd /u01/app/oracle/product/10.2.0/db_1/bin
./netca &
Add a new listener
Add Naming mtehods: Local Naming, EZConnect
5. Cr eat e Dat abase
cd /u01/app/oracle/product/10.2.0/db_1/bin
./dbca &
follow the steps to create a database with sid: ora11gr2

The DBCA may pop up the following message in the end of db creation:
"Error securing Database control, Database Control has been brought up in non-
secure mode. To secure the Database Control execute the following commands:
... "
6. Post i nst al l at i on Task s
Verify OEM:
# verify that OEM is working
https://srv100.mydomain.com:1158/em
# restart the dbconsole if required
emctl status dbconsole
emctl stop dbconsole
emctl start dbconsole

# check Oracle processes:
ps -eo pid -o command | grep ora_ | grep -v grep
Consider implement ing aut omat ic dat abase st art up. See: Aut omat ically St art ing Dat abases
Consider implement ing aut omat ic EM Dat abase Cont rol st art up. See: I mplement ing EM
Dat abase Cont rol Aut o St art up
Consider using rlwrap ut ilit y wit h SQL* Plus and RMAN:
o Using rlwrap Ut ilit y wit h RMAN in Unix- Based Syst ems
o Using rlwrap Ut ilit y wit h SQL* Plus in Unix- Based Syst ems
For easy Oracle Home access:
Page 52 Oracle DBA Code Examples

echo "alias db='cd $ORACLE_HOME'" >> /home/oracle/.bashrc
Page 53 Oracle DBA Code Examples

I nst al l i ng Or acl e 11g R2 on En t er pr i se Li nux 5. 5 w i t h
ASM
Not e: for any inst allat ion, you should check t he Release Not es documenat ion before t aking
any pract ical st ep.
I nst al l at i on Envi r onment
Emulat ion soft ware: VMWare Workst at ion 7 for Windows
OS: Oracle Ent erprise Linux 5. 5 for x86 64- bit
Requi r ed Sof t w ar e
Oracle Dat abase 11g Release 2 for Linux x86 64-bit
Oracle Dat abase 11g Release 2 Grid I nfrast ruct ure ( 11. 2. 0. 1. 0) for Linux x86 64-bit
Used Har dw ar e
I n t he VMWare: creat e one virt ual machine wit h t he following specs:
o 2. 5 GB RAM
o One et hernet card configured as bridged or host - only in VMware
o CPU Count : 2
o Disk1: 34 GB on SCSI 0: 0 used t o inst all t he OS and soft ware
o Disk2: of 12 GB. I t will be used for + Dat a. Set it on cont roller SCSI 1: 1
o Disk3: of 6 GB. I t will be used for + Flash. Set it on cont roller SCSI 1: 2
I nst al l at i on Pl an
1. Preinst allat ion t asks
Hardware requirement s
Soft ware requirement s
Environment configurat ion
2. Oracle Grid I nfrast ruct ure inst allat ion
3. Oracle Grid I nfrast ruct ure Pat ching
4. Oracle Dat abase 11g R2 Soft ware I nst allat ion
5. Oracle Dat abase 11g R2 Soft ware Pat ching
6. I nst all EM Agent in clust er nodes ( if required)
7. ASM Diskgroups Creat ion
8. Dat abase Creat ion
9. Complet e post inst allat ion t asks
Page 54 Oracle DBA Code Examples

10. Useful post inst allat ion t asks

1. Pr ei nst al l at i on t ask s
I nst all Oracle Ent erprise Linux in t he first local hardisk. I nst all not hing in t he remaining
disks.
Not e: for a product ion syst em, consider becoming an Oracle Unbreakable Linux cust omer
and regist er your server on t he Unbreakable Linux Net work.
o Configure t he swap area in t he local hardisk t o have 6 GB disk space.
o Give t he et hernet card I P 192.168. 4. 105 t he host name srv07
o I nsall t he following packages:
Deskt op Environment s
o GNOME Deskt op Environment
Applicat ions
o Graphical I nt ernet ( opt ional)
o Edit ors ( opt ional)
Development
o Development Libraries
o Development Tools
Servers
o Do not select anyt hing in t his group.
Base Syst em
o Administ rat ion Tools
o Syst em Tools
Add t he package 'sysst at ' by clicking on t he Det ails link
and select ing "sysst at - The sar an iost at syst em
monit oring commands. " from t he Opt ional Packages list .
X Window Syst em
Complet e t he inst allat ion
I nst all furt her packages:
# to know distribution and version of Linux
cat /etc/issue
# to know kernel version (and its errata level) (2.6.18-194.el5)
uname -r

# to list missed packages:
rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n' binutils \
compat-libstdc++-33 \
elfutils-libelf \
elfutils-libelf-devel \
gcc \
gcc-c++ \
glibc \
glibc-common \
glibc-devel \
Page 55 Oracle DBA Code Examples

glibc-headers \
ksh \
libaio \
libaio-devel \
libgcc \
libstdc++ \
libstdc++-devel \
make \
sysstat \
unixODBC \
unixODBC-devel

# for missed packages, install them:
rpm -Uvh elfutils-libelf-0.137-3.el5.i386.rpm
rpm -ivh elfutils-libelf-devel-static-0.137-3.el5.i386.rpm elfutils-libelf-
devel-0.137-3.el5.i386.rpm
rpm -Uvh kernel-headers-2.6.18-194.el5.x86_64.rpm
rpm -Uvh glibc-headers-2.5-49.x86_64.rpm
rpm -Uvh glibc-devel-2.5-49.i386.rpm
rpm -Uvh glibc-devel-2.5-49.x86_64.rpm
rpm -Uvh libgomp-4.4.0-6.el5.x86_64.rpm
rpm -Uvh gcc-4.1.2-48.el5.x86_64.rpm
rpm -Uvh gcc-c++-4.1.2-48.el5.x86_64.rpm
rpm -Uvh libstdc++-devel-4.1.2-48.el5.x86_64.rpm
rpm -Uvh gcc-c++-4.1.2-48.el5.x86_64.rpm
rpm -Uvh libaio-devel-0.3.106-5.i386.rpm
rpm -Uvh libaio-devel-0.3.106-5.x86_64.rpm
rpm -Uvh unixODBC-2.2.11-7.1.i386.rpm
rpm -Uvh unixODBC-2.2.11-7.1.x86_64.rpm
rpm -Uvh unixODBC-devel-2.2.11-7.1.i386.rpm
rpm -Uvh unixODBC-devel-2.2.11-7.1.x86_64.rpm


# Oracle ASM Libaray and drivers can be downloaded from here
# to know the kernel verion: uname -rm
# In this case we need:
# library and tools
rpm -Uvh oracleasm-support-2.1.3-1.el5.x86_64.rpm
rpm -Uvh oracleasm-2.6.18-194.el5-2.0.5-1.el5.x86_64.rpm
rpm -Uvh oracleasmlib-2.0.4-1.el5.x86_64.rpm

# SELINUX must be disabled
cat /etc/selinux/config | grep SELINUX=
vi /etc/selinux/config
SELINUX=disabled
shutdown -h now -r
Check t he hardware requirement s
# Hardware Requirements (in cluster nodes)
# At least 1.5 GB of physical memory but practically 1.5 is not fine
grep MemTotal /proc/meminfo

# swap space: same as the amount of physical memory
grep SwapTotal /proc/meminfo

# to display swap and memory in one command:
free
Page 56 Oracle DBA Code Examples


# if you don't have enought swap,
# you can add swap space by creating a temporary swap file.
# let's say about 500MB:
dd if=/dev/zero of=tempswap bs=1k count=500000
chmod 600 tempswap
mke2fs tempswap
mkswap tempswap
swapon tempswap

# 1 GB disk space in /tmp
df -h /tmp
# 8 GB of disk space for Oracle software
df

The size of the shared memory should be at least the greater of
MEMORY_MAX_TARGET and MEMORY_TARGET for each Oracle instance on the computer.
To determine the amount of shared memory available, enter the following
command:
df -h /dev/shm/
# to adjust the shared memory file system size:
umount tmpfs
mount -t tmpfs shmfs -o size=1200m /dev/shm
Creat e t he required net work configurat ion:
ping srv07

# Network names Resolution
cat /etc/hosts
127.0.0.1 srv07 localhost.localdomain localhost

Creat e and configure t he required OS users and groups
Not e: When I t ried using differenct OS users and groups for Grid I nfrast rucut re and ASM,
I got error lat er in DBCA.
# Grid Infrastructure (GI) and the Oracle RDBMS home will
# be installed using different users:
/usr/sbin/groupadd -g 501 oinstall
/usr/sbin/groupadd -g 502 dba
/usr/sbin/groupadd -g 504 asmadmin
/usr/sbin/useradd -u 502 -g oinstall -G dba,asmadmin oracle

# set passwords
passwd oracle

# make sure nobody user exists (if not there, create it useradd nobody)
id nobody

# define the env variables for oracle user
vi /home/oracle/.bash_profile

# Oracle Settings
export EDITOR=vi
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=srv07; export ORACLE_HOSTNAME
Page 57 Oracle DBA Code Examples

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=oradb; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jdk/jre/lib/:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
export CLASSPATH

# shell startup file
vi /etc/profile
if [ $USER = "oracle" ] || [ $USER = "grid" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
umask 022
fi

# for C shell
vi /etc/csh.login
if ( $USER = "oracle" || $USER = "grid" ) then
limit maxproc 16384
limit descriptors 65536
endif
Configure kernel paramet ers and shell limit s
Not e: I f you make a mist ake wit h a paramet er set t ing and your syst em does not st art ,
t hen you must st art Linux in t he single- user runlevel ( runlevel 1) . At t his runlevel, t he
/ et c/ sysct l. conf file is not run.
# Kernel Parameters
# to tune thme, refer to metalink document 169706.1
# Append the following to the /etc/sysctl.conf file as the root user:
vi /etc/sysctl.conf

kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.aio-max-nr = 1048576
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

# to take immediate effect
/sbin/sysctl -p

# User Shell Limits
# memlock is used to increase the per-process max locked memory
vi /etc/security/limits.conf

oracle soft nproc 2047
oracle hard nproc 16384
Page 58 Oracle DBA Code Examples

oracle soft nofile 1024
oracle hard nofile 65536

vi /etc/pam.d/login
session required pam_limits.so
Creat e part it ions in t he disks:
Not e: On a real life st orage, you would creat e a single whole- disk part it ion wit h exact ly 1 MB
offset on each LUN t o be used as ASM Disk. I n fdisk: u ( t o change unit s from cylinder t o
sect ors) , n, p, 1, 2048, w.
# as a root, for the disks /dev/sdb .. /dev/sdd
# confirm they are seen:
ls /dev/sd*
#partition the disks:
fdisk /dev/sdb
# answers: "n", "p", "1", "Return", "Return", "p" and "w"
Note: if the following message appears after the "w" command:
WARNING: Re-reading the partition table failed with error 16: Device or
resource busy, then you can avoid restarting the machine by the following
command: partprobe

# to make sure partions are created
ls -lX /dev/sd*
Configure ASM drivers:
# as root (to be done in all nodes)
oracleasm configure -i

Default user to own the driver interface []: oracle
Default group to own the driver interface []: asmadmin
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done

# Load the kernel module using the following command:
/usr/sbin/oracleasm init
# If you have any problems, make sure you have the correct
# version of the driver (may require Internet connection):
/usr/sbin/oracleasm update-driver

# mark the shared disks: (one node)
/usr/sbin/oracleasm createdisk DISK1 /dev/sdb1
/usr/sbin/oracleasm createdisk DISK2 /dev/sdc1

# check the disks are marked and seen:
/usr/sbin/oracleasm listdisks

#If you need to unmark a disk that was used in a createdisk command:
/usr/sbin/oracleasm deletedisk DISK1
/usr/sbin/oracleasm deletedisk DISK2
Creat e t he required direct ories for t he Oracle soft ware:
# to know if there is an existing oracle inventory
# from its output, ORACLE_BASE will be parent of oraInventory
more /etc/oraInst.loc
# to identify existing Oracle home directories
Page 59 Oracle DBA Code Examples

more /etc/oratab

# Oracle Inventory Directory
# as a root
mkdir -p /u01/app/oraInventory
chown -R oracle:oinstall /u01/app/oraInventory
chmod -R 775 /u01/app/oraInventory

# Grid Infrastructure Home Directory
mkdir -p /u01/11.2.0/grid
chown -R oracle:oinstall /u01/11.2.0/grid
chmod -R 775 /u01/11.2.0/grid

# Oracle Base Directory
mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01/app/oracle
chmod -R 775 /u01/app/oracle

#needed to ensure that dbca is able to run after the rdbms installation
mkdir /u01/app/oracle/cfgtoollogs
chown -R oracle:oinstall /u01/app/oracle
chmod -R 775 /u01/app/oracle

# Oracle RDBMS Home Directory
mkdir -p /u01/app/oracle/product/11.2.0/db_1
chown -R oracle:oinstall /u01/app/oracle/product/11.2.0/db_1
chmod -R 775 /u01/app/oracle/product/11.2.0/db_1
2. Or acl e Gr i d I nf r ast r uct ur e i nst al l at i on
# software staging folder
mkdir -p /u01/app/stage/ora11gr2gridinfra
chown -R oracle:oinstall /u01/app/stage/ora11gr2gridinfra
chmod -R 775 /u01/app/stage/ora11gr2gridinfra

mkdir -p /u01/app/stage/ora11gr2db
chown -R oracle:oinstall /u01/app/stage/ora11gr2db
chmod -R 775 /u01/app/stage/ora11gr2db

# as oracle: copy Grid software files to /u01/app/stage/ora11gr2db

# as root:
chmod -R 775 /u01/app/stage/ora11gr2gridinfra

# lunch OUI from the clusterware ( as oracle)
cd /u01/app/stage/ora11gr2gridinfra
./runInstaller

Installation Option
>Select radio button 'Install and Configure Grid Infrastructure for a
Standalone Server'
>Next

Product Language
>Accept 'English' as language'
>Next
Creat ASM Disk Group
>Disk Group Name: Data (12GB disk: DISK1)
Page 60 Oracle DBA Code Examples

>Redundancy: external
>Next
NOTE: If you see an empty screen for you candidate disks it is likely that
ASMLib has not been properly configured or installed. Try reconfigure them.
If you are sure that ASMLib has been properly configured click on 'Change
Discovery Path' and provide the correct destination.

ASM Password
>Specify and conform the password you want to use
>Next

Privileged OS Groups
>Assign the correct OS groups for OS authentication (mostly default is OK)
>Next

Installation Location
>ORACLE_BASE: /u01/app/oracle
Software location: /u01/11.2.0/grid
>Next

Create Inventory
>Specify the locations: /u01/app/oraInventory
>Next

Perform Prerequisite Checks
>Check that status of all checks is Succeeded
>Next

Summary
>Finish

Execute Configuration Scripts
>Run the scripts as instructed in the screen
>OK

>Next

Message: The installation of the Grid Infrastructure was successfull.
>Close
3. Or acl e Gr i d I nf r ast r uct ur e Pat chi ng
Apply patch set, if there is any.
4. Or acl e Dat abase 11g R2 Sof t w ar e I nst al l at i on
# as oracle: copy DB software files into /u01/app/stage/ora11gr2db

# as root:
chmod -R 775 /u01/app/stage/ora11gr2db

-- start OUI
su - oracle
cd /u01/app/stage/ora11gr2db
./runInstaller

Follow the steps.
Install database software only.
Page 61 Oracle DBA Code Examples


When executing root.sh, select "y" for ovewriting questions.
5. Or acl e Dat abase 11g R2 Sof t w ar e Pat chi ng

6. I nst al l EM Agent i n cl ust er nodes ( i f r equi r ed)

7. ASM Di sk gr oups Cr eat i on
# as grid user: start the ASM Configuration Assistant (ASMCA)
su - oracle
cd /u01/11.2.0/grid/bin
./asmca

>Disk Groups tab
>Create button
>Disk Group Name: FLASH
>Redundancy: External
>DISK2
>OK
>Exit
>Yes
8. Dat abase Cr eat i on
# as oracle
cd /u01/app/oracle/product/11.2.0/db_1/bin
./dbca

follow the steps to create a database with sid: oradb

The DBCA may pop up the following message in the end of db creation:
"Error securing Database control, Database Control has been brought up in non-
secure mode. To secure the Database Control execute the following commands:
... "

# check Oracle processes:
ps -eo pid -o command | grep ora_ | grep -v grep
9. Post i nst al l at i on t ask s
# backup the root.sh script
cp /u01/app/oracle/product/11.2.0/db_1/root.sh ~/root.sh.bak

cont>>
10. Gener al Usef ul Post i nst al l at i on Task s i n Li nux
Consider using rlwrap ut ilit y wit h SQL* Plus and RMAN:
o Using rlwrap Ut ilit y wit h RMAN in Unix- Based Syst ems
o Using rlwrap Ut ilit y wit h SQL* Plus in Unix- Based Syst ems
/* Make crs_stat -t more readable */
/* copy the following script into ~/scripts/crstat.sh */
Page 62 Oracle DBA Code Examples

#!/usr/bin/ksh
#
# Sample 10g CRS resource status query script
#
# Description:
# - Returns formatted version of crs_stat -t, in tabular
# format, with the complete rsc names and filtering keywords
# - The argument, $RSC_KEY, is optional and if passed to the script, will
# limit the output to HA resources whose names match $RSC_KEY.
# Requirements:
# - $ORA_CRS_HOME should be set in your environment
# suggested scrip name: crstat.sh

RSC_KEY=$1
QSTAT=-u
AWK=/usr/bin/awk # if not available use /usr/bin/awk

# Table header:echo ""
$AWK \
'BEGIN {printf "%-45s %-10s %-18s\n", "HA Resource", "Target", "State";
printf "%-45s %-10s %-18s\n", "-----------", "------", "-----";}'

# Table body:
$ORA_CRS_HOME/bin/crs_stat $QSTAT | $AWK \
'BEGIN { FS="="; state = 0; }
$1~/NAME/ && $2~/'$RSC_KEY'/ {appname = $2; state=1};
state == 0 {next;}
$1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
$1~/STATE/ && state == 2 {appstate = $2; state=3;}
state == 3 {printf "%-45s %-10s %-18s\n", appname, apptarget, appstate;
state=0;}'


# then add the following in the .bashrc of oracle user
# if the file was saved in ~/scripts/crstat.sh
alias crstat='~/scripts/crstat.sh'

/* Easy Acces to crs and db homes */
# it is common to access bin directories in clusterware and db homes

# add the following to .bashrc of oracle user
alias db='cd /u01/app/oracle/product/11.2.0/db_1/bin'

# add the following to .bashrc of grid user
alias crs='cd /u01/app/oracle/crs/bin'
Page 63 Oracle DBA Code Examples

Managi ng Or acl e Dat abase I n st ance
Pr oduct Rel ease Number
Or acl e Dat abase Rel ease Number For mat
SELECT * FROM PRODUCT_COMPONENT_VERSION;
SELECT * FROM V$VERSION
1. 2. 3. 4. 5
1 Maj or dat abase release number
2 Maint enance release number
3 applicat ion server release number
4 component specific release number
5 plat form specific release number
Obt ai ni ng Li cense I nf or mat i on
-- Number of users and CPU/Processors
select L.SESSIONS_MAX, L.SESSIONS_WARNING, L.SESSIONS_CURRENT,
L.SESSIONS_HIGHWATER,
L.USERS_MAX, L.CPU_COUNT_CURRENT, L.CPU_SOCKET_COUNT_CURRENT,
L.CPU_COUNT_HIGHWATER,
L.CPU_CORE_COUNT_CURRENT, L.CPU_CORE_COUNT_HIGHWATER,
L.CPU_SOCKET_COUNT_HIGHWATER
from v$license l;

-- Database Edition
select banner from v$version where BANNER like '%Edition%';

-- Oracle Partitioning installed
select decode(count(*), 0, 'No', 'Yes')
from dba_part_tables
where owner not in ('SYSMAN', 'SH', 'SYS', 'SYSTEM') and rownum = 1;

-- Oracle Spatial installed:
select decode(count(*), 0, 'No', 'Yes')
from all_sdo_geom_metadata where rownum = 1;

-- Oracle RAC installed:
select decode(count(*), 0, 'No', 'Yes')
from v$active_instances where rownum <= 2;
Managi ng t he I nst ance Ar chi t ect ur e
Obt ai ni ng I nf or mat i on about t he I nst ance Pr ocesses
ps ef | grep ora

select name, description from V$BGPROCESS order by 1;
Page 64 Oracle DBA Code Examples

Obt ai ni ng I nf or mat i on about t he SGA
select value from v$parameter where upper(name)='MEMORY_TARGET';
select value/1024/1024 mb
from v$parameter where upper(name) = 'SGA_MAX_SIZE'

-- size taken by a memory component
select pool, round(sum(BYTES)/1024/1024) MB
from V$SGASTAT
group by pool

select name , value/1024/1024 MB
from v$parameter
where upper(name) in (
'DB_CACHE_SIZE','DB_RECYCLE_CACHE_SIZE','DB_KEEP_CACHE_SIZE', '
DB_2k_CACHE_SIZE', 'DB_8k_CACHE_SIZE', 'DB_16k_CACHE_SIZE')

-- 10g
SELECT COMPONENT , CURRENT_SIZE/1024/1024 MB
FROM V$SGA_DYNAMIC_COMPONENTS
WHERE CURRENT_SIZE <>0

-- Oracle 11g
SELECT COMPONENT, ROUND(CURRENT_SIZE/1024/1024) CURRENT_SIZE ,
ROUND(MIN_SIZE/1024/1024) MIN, ROUND(MAX_SIZE/1024/1024) MAX
FROM V$MEMORY_DYNAMIC_COMPONENTS;
-- To know how Oracle has modified the memory area sizes by time
select START_TIME, END_TIME, STATUS, COMPONENT, OPER_TYPE, OPER_MODE,
PARAMETER, INITIAL_SIZE/1024/1024 INITIAL_SIZE_MB,
TARGET_SIZE/1024/1024 TARGET_SIZE_MB, FINAL_SIZE/1024/1024 FINAL_SIZE_MB
from V$MEMORY_RESIZE_OPS
order by START_TIME, END_TIME
Cl ear i ng t he Buf f er Cache
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE ;
Dat abase Admi ni st r at i on Aut hent i cat i on
Usi ng Oper at i ng Syst em Aut hent i cat i on
Following are t he st eps t o creat e an Oracle administ rat or using OS aut hent icaion. Creat ing
normal user who is capable of using OS aut hent icat ion is a bit different . See Ext ernal ( OS)
Aut hent icat ion.
1. Creat e an operat ing syst em account for t he user.
2. Add t he account t o t he OSDBA or OSOPER operat ing syst em defined groups.
Unix User Groups: dba, oper
Windows: ORA_DBA, ORA_OPER
3. login t o OS as t he creat ed user
4. I n SQL* Plus
CONNECT / AS SYSDBA
CONNECT / AS SYSOPER
Page 65 Oracle DBA Code Examples

Usi ng Passw or d Fi l e Aut hent i cat i on
5. Creat e t he password file
6. orapwd FI LE= filename PASSWORD= password ENTRI ES= max_users
7. alt er syst em set REMOTE_LOGI N_PASSWORDFI LE = EXCLUSI VE scope= SPFI LE;
8. CONN / AS SYSDBA
9. GRANT SYSDBA TO GRANTEDUSER
10. CONN GRANTEDUSER/ USERPASSWORD AS SYSDBA
I dent i f y i ng User s SYSDBA or SYSOPER User s
SELECT * FROM V$PWFILE_USERS
Dat a Di ct i onar y and Dynami c Per f or mance Vi ew s
Dat a Di ct i onar y Cr eat i on
sql.bsq creat es base t ables
catalog.sql creat es dat a dict ionary views
catproc.sql runs script s required for server- side PL/ SQL
t hey run as SYSDBA
t hey are locat ed in \ rdbms\ admin
St ar t up and Shut dow n
St ar t up Lev el s
STARTUP [FORCE] [RESTRICT] [PFILE=filename] [OPEN [RECOVER] [database] |MOUNT
|NOMOUNT]
ALTER DATABASE { MOUNT | OPEN }
ALTER DATABASE OPEN [READ WRITE| READ ONLY]
ALTER SYSTEM ENABLE | DISABLE RESTRICTED SESSION;
Shut dow n Level s
SHUTDOWN [NORMAL | TRANSACTIONAL | IMMEDIATE | ABORT ]
Aut omat i cal l y St ar t i ng Dat abases
For Oracle 10. 1, refer t o Not e 222813. 1
/* Example 1 */
# compatible with Note 281912.1 (and Note 760051.1)
#(1) login as root
#(2) Set it to Y in /etc/oratab
mydb:/u01/app/oracle/product/10.2.0/db_1:Y

#(3) Create dbora script in the directory /etc/init.d
# fix ORA_HOME and ORA_OWNER values as required
vi /etc/init.d/dbora

Page 66 Oracle DBA Code Examples

#!/bin/bash
#
# description: Start/Stop the Databases..
#
# chkconfig: 2345 99 10
#
# processname: oracle
# config: /etc/oratab
# pidfile: /var/run/oracle.pid

# Source function library.
. /etc/init.d/functions

RETVAL=0
ORA_OWNER="oracle"
ORA_HOME="/u01/app/oracle/product/11.2.0/db_1"

prog="oracle"

start() {
echo -n $"Starting $prog: "
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart"
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && touch /var/lock/subsys/dbora
return $RETVAL
}

stop() {
echo -n $"Stopping $prog: "
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut"
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && rm -r /var/lock/subsys/dbora
return $RETVAL
}

restart() {
stop
start
}

case "$1" in
start)
start
;;
stop)
Page 67 Oracle DBA Code Examples

stop
;;
restart)
restart
;;
*)
echo $"Usage: $0 {start|stop|restart}"
exit 1
esac

exit $?


#(4) as root:
chgrp dba /etc/init.d/dbora
chmod 750 /etc/init.d/dbora

cd /sbin
chkconfig --add dbora
chkconfig --list | grep ora

# to test, restart or:
/etc/init.d/dbora start

/* Example 2 */
-- have been tested on an Oracle 10g Db on Linux 4
#(1) login as root
#(2) Set it to Y in /etc/oratab
mydb:/u01/app/oracle/product/10.2.0/db_1:Y

#(3) Create dbora script in the directory /etc/init.d
# fix ORACLE_HOME value as required

#!/bin/sh
#
# /etc/rc.d/init.d/dbora
# Description: Starts and stops the Oracle database and listeners
#
case "$1" in
start)
echo -n "Starting Oracle Databases: "
echo "------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Starting Oracle Databases as part of system up."
>> /var/log/oracle
echo "------------------------------------" >> /var/log/oracle
su - oracle -c dbstart >> /var/log/oracle
echo "Done."
echo -n "Starting Oracle Listeners: "
su - oracle -c "lsnrctl start" >> /var/log/oracle
echo "Done."
echo ""
echo "------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Finished." >> /var/log/oracle
Page 68 Oracle DBA Code Examples

echo "------------------------------------" >> /var/log/oracle
touch /var/lock/subsys/oracle
;;
stop)
echo -n "Shutting Down Oracle Listeners: "
echo "------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Shutting Down Oracle Databases as part of system
down." >> /var/log/oracle
echo "------------------------------------" >> /var/log/oracle
su - oracle -c "lsnrctl stop" >> /var/log/oracle
echo "Done."
rm -f /var/lock/subsys/oracle
echo -n "Shutting Down Oracle Databases: "
su - oracle -c dbshut >> /var/log/oracle
echo "Done."
echo ""
echo "------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Finished." >> /var/log/oracle
echo "------------------------------------" >> /var/log/oracle
;;
restart)
echo -n "Restarting Oracle Databases: "
echo "------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Restarting Oracle Databases as part of system up."
>> /var/log/oracle
echo "------------------------------------" >> /var/log/oracle
su - oracle -c dbstop >> /var/log/oracle
su - oracle -c dbstart >> /var/log/oracle
echo "Done."
echo -n "Restarting Oracle Listeners: "
su - oracle -c "lsnrctl stop" >> /var/log/oracle
su - oracle -c "lsnrctl start" >> /var/log/oracle
echo "Done."
echo ""
echo "------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Finished." >> /var/log/oracle
echo "------------------------------------" >> /var/log/oracle
touch /var/lock/subsys/oracle
;;
*)
echo "Usage: oracle {start|stop|restart}"
exit
esac

# (4) set file group and permissions
chgrp dba dbora
chmod 750 dbora

# (5) Create the symbolic links: In Linux,
ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/K01dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/K01dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora

/* Example 3 */
#(1) login as root
#(2) Set it to Y in /etc/oratab
mydb:/u01/app/oracle/product/10.2.0/db_1:Y
Page 69 Oracle DBA Code Examples


#(3) Create dbora script in the directory /etc/init.d
# fix ORACLE_HOME value as required
vi /etc/init.d/dbora

#!/bin/bash
#
# oracle Init file for starting and stopping
# Oracle Database. Script is valid for 10g and 11g versions.
#
# chkconfig: 35 80 30
# description: Oracle Database startup script

# Source function library.

. /etc/rc.d/init.d/functions

ORACLE_OWNER="oracle"
ORACLE_HOME="/u01/app/oracle/product/10.2.0/db_1"

case "$1" in
start)
echo -n $"Starting Oracle DB:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
echo "OK"
echo -n "Starting Oracle Listeners:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl start"
echo "OK"
;;
stop)
echo -n $"Stopping Oracle DB:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
echo "OK"
echo -n "Stopping Oracle Listeners:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl stop"
echo "OK"
;;
*)
echo $"Usage: $0 {start|stop}"
esac

# (4) set file group and permissions
chgrp dba /etc/init.d/dbora
chmod 750 /etc/init.d/dbora

# (5) Create the symbolic links: In Linux,
ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/K01dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/K01dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora

# (6) configuring execution for specific runlevels
chkconfig --add dbora --level 0356
Page 70 Oracle DBA Code Examples

Qui esci ng a Dat abase
Users will remain logged in, and t hey can cont inue t o execut e t heir request s t hat are in
progress, while t he dat abase is in t he quiesced st at e. The dat abase, however, will block all
new t ransact ional request s except t hose made by t he users SYS and SYSTEM (not DBA
grant ees).
ALTER SYSTEM QUIESCE RESTRICTED;
ALTER SYSTEM UNQUIESCE;
SELECT instance_name, status, shutdown_pending, active_state FROM v$instance;
Suspendi ng a Dat abase
All reads from and writ es t o t he dat afiles and cont rol files are suspended.
ALTER SYSTEM SUSPEND;
ALTER SYSTEM RESUME;
SELECT instance_name, status, shutdown_pending, active_state FROM v$instance;
Dr oppi ng a Dat abase
Dat afiles, redo log files, and cont rol files are removed aut omat ically. Alert log and
paramet er file are not delet ed.
CONNECT sys/sys_passwd AS SYSDBA
SHUTDOW IMMEDIATE
STARTUP RESTRICT MOUNT
SELECT name FROM v$database;
DROP DATABASE;
I ni t i al i zat i on Fi l es
Managi ng I ni t i al i zat i on Fi l es
During st art up, in $ORACLE_HOME/ dbs ( for UNI X/ Linux) Oracle will look for t he correct
init ializat ion file t o use, in t he following order:
o spfile$ORACLE_SI D.ora
o spfile.ora
o init $ORACLE_SI D.ora
# currently used SPFiles (if null, pfile was used)
select * from v$parameter where name = 'spfile'
# create pfile
CREATE SPFILE='/u01/oracle/dbs/test_spfile.ora' FROM
PFILE='/u01/oracle/dbs/test_init.ora'
# use pecific spfile during startup
STARTUP PFILE = $ORACLE_HOME/dbs/initDBA1.ora
# PFILE can indicate to use SPFILE
SPFILE = /database/startup/spfileDBA1.ora
I n Oracle 11g
Page 71 Oracle DBA Code Examples

# write current values of instance parameters
CREATE PFILE FROM MEMORY;
CREATE SPFILE FROM MEMORY;
Managi ng Par amet er s i n SPFI LE
-- display current value of a parameter
select * from v$parameter where name = 'spfile'
-- parameter values set in SPFILE
select NAME, ISSPECIFIED from V$SPPARAMETER where name like '%dest%';
-- in SQL Plus
show parameter target

-- set parameter value
alter system set parameter=value scope=SPFILE|MEMORY|

BOTH
-- delete a parameter from SPFILe
ALTER SYSTEM RESET undo_suppress_errors SCOPE=BOTH SID='node01';

-- Only sessions started after the statement is executed are affected
-- this option is a must for the parameters whose ISSYS_MODIFIABLE column
-- in V$PARAMETER is DEFERRED. You cannot use it, if the column value is
FALSE
ALTER SYSTEM SET parameter_name DEFERRED;

-- undocumented parameters
SELECT
a.ksppinm parameter, a.ksppdesc description,
b.ksppstvl session_value, c.ksppstvl instance_value
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE
a.indx = b.indx
AND a.indx = c.indx
AND SUBSTR (a.ksppinm,1,1) = '_'
ORDER BY a.ksppinm;
Al er t and Tr ace Fi l es
Moni t or i ng Al er t and Tr ace Fi l es
# to obtain the Location of Trace Files
column NAME format a35
column VALUE format a35
select name, value from v$parameter
where name in ('background_dump_dest','user_dump_dest')

# to set maximum size of trace files (excluding the alert file)
# in number of block unless you sepcify K or M
Page 72 Oracle DBA Code Examples

select * from v$parameter where upper(name )= 'MAX_DUMP_FILE_SIZE'
alter session set MAX_DUMP_FILE_SIZE='100M'
Page 73 Oracle DBA Code Examples

Managi ng Or acl e Dat abase Phy si cal St r u ct ur e
Managi ng Cont r ol Fi l es
Obt ai ni ng Cont r ol Fi l e i nf or mat i on
select value from v$parameter where upper(name)= 'CONTROL_FILES'
select * from v$controlfile;
select * from v$controlfile_record_section
Cr eat i ng Addi t i onal Copi es, Renami ng, and Rel ocat i ng Cont r ol Fi l es
1. Shut down t he dat abase.

2. Copy an exist ing cont rol f ile t o a new locat ion, using operat ing syst em commands.

3. Edit t he CONTROL_FI LES paramet er
CONTROL_FILES="D:\ORACLE\ORADATA\ORA11G2\CONTROL01.CTL","D:\ORACLE\ORADATA\ORA1
1G2\CONTROL02.CTL";
4. Rest art t he dat abase.
Not e: if you are using SPFI LE, STARTUP NOMOUNT t hen use ALTER SYSTEM SET ..
SCOPE= SPFI LE command.
Cr eat i ng New Cont r ol Fi l es
1. Make a list of all dat af iles and redo log f iles of t he dat abase.
2. Shut down t he dat abase.
3. Back up all dat af iles and redo log f iles of t he dat abase.
4. STARTUP NOMOUNT
5. Use t he CREATE CONTROLFILE st at ement :
CREATE CONTROLFILE
SET DATABASE prod
LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log',
'/u01/oracle/prod/redo01_02.log'),
GROUP 2 ('/u01/oracle/prod/redo02_01.log',
'/u01/oracle/prod/redo02_02.log'),
GROUP 3 ('/u01/oracle/prod/redo03_01.log',
'/u01/oracle/prod/redo03_02.log')
RESETLOGS | NORESETLOGS
DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,
'/u01/oracle/prod/rbs01.dbs' SIZE 5M,
'/u01/oracle/prod/users01.dbs' SIZE 5M,
'/u01/oracle/prod/temp01.dbs' SIZE 5M
MAXLOGFILES 50
MAXLOGMEMBERS 3
MAXLOGHISTORY 400
MAXDATAFILES 200
MAXINSTANCES 6
ARCHIVELOG;
Specify t he RESETLOGS clause if you have lost any redo log groups in addit ion t o cont rol files.
I n t his case, you will need t o recover from t he loss of t he redo logs (st ep 8). You must specify
t he RESETLOGS clause if you have renamed t he dat abase. Ot herwise, select t he
NORESETLOGS clause.

Page 74 Oracle DBA Code Examples

Caut i on: The CREATE CONTROLFI LE st at ement can pot ent ially damage specified dat afiles and
redo log files. Omit t ing a filename can cause loss of t he dat a in t hat file, or loss of access t o
t he ent ire dat abase.

6. St ore a backup of t he new cont rol file on an offline st orage device.
7. Edit t he CONTROL_FI LES init ializat ion paramet er
8. I f you are renaming t he dat abase, edit t he DB_NAME paramet er in your inst ance paramet er
file.
9. Recover t he dat abase if necessary.
I f t he new cont rol file was creat ed using t he NORESETLOGS clause, you can recover t he
dat abase wit h complet e, closed dat abase recovery.
I f t he new cont rol file was creat ed using t he RESETLOGS clause, you must specify USI NG
BACKUP CONTROL FI LE in your RECOVER command.

10. I f you did not perform recovery, open t he dat abase normally.
ALTER DATABASE OPEN;
I f you specified RESETLOGS when creat ing t he cont rol file:
ALTER DATABASE OPEN RESETLOGS;

Handl i ng Er r or s Dur i ng CREATE CONTROLFI LE
I f Oracle Dat abase sends you an error ( usually error ORA- 01173, ORA- 01176, ORA- 01177,
ORA- 01215, or ORA- 01216) when you at t empt t o mount and open t he dat abase aft er creat ing
a new cont rol file, t he most likely cause is t hat you omit t ed a file from t he CREATE
CONTROLFI LE st at ement or included one t hat should not have been list ed.

Check i ng f or Mi ssi ng Fi l es af t er Cr eat i ng Cont r ol Fi l es
Check t he alert log t o see if t he dat abase has det ect ed inconsist encies bet ween t he dat a
dict ionary and t he cont rol file.

I f a dat afile exist s in t he dat a dict ionary but not in t he new cont rol file, t he dat abase creat es
a placeholder ent ry in t he cont rol file under t he name MI SSI NGnnnn, where nnnn is t he file
number in decimal. MI SSI NGnnnn is flagged in t he cont rol file as being offline and requiring
media recovery.
o I f t he act ual dat afile corresponding t o MI SSI NGnnnn is read- only or offline normal,
t hen you can make t he dat afile accessible by renaming MI SSI NGnnnn t o t he name
of t he act ual dat afile.

o I f MI SSI NGnnnn corresponds t o a dat afile t hat was not read-only or offline normal,
you must drop t he t ablespace cont aining t he dat afile.
Back i ng Up Cont r ol Fi l es
ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Manage t he Si ze of Cont r ol Fi l es
I t is affect ed by MAXDATAFI LES, MAXLOGFI LES, MAXLOGMEMBERS, MAXLOGHI STORY, and
MAXI NSTANCES paramet ers in t he CREATE DATABASE st at ement . Also it is affect ed by
CONTROL_FI LE_RECORD_KEEP_TI ME
Mul t i pl ex i ng t he Cont r ol Fi l e
1. Alt er t he SPFI LE:
ALTER SYSTEM SET control_files
='$HOME/ORADATA/u01/ctrl01.ctl','$HOME/ORADATA/u02/ctrl02.ctl' SCOPE=SPFILE;
Page 75 Oracle DBA Code Examples

2. Shut down t he dat abase
3. Creat e addit ional cont rol files:
cp $HOME/ORADATA/u01/ctrl01.ctl $HOME/ORADATA/u02/ctrl02.ctl
4. St art t he dat abase:
startup
Mai nt ai ni ng Onl i ne Redo Log Fi l es
For ci ng Log Sw i t ches and Check poi nt s
# Forcing a log switch
ALTER SYSTEM SWITCH LOGFILE;
# forcing checkpoints in seconds
FAST_START_MTTR_TARGET = 600
ALTER SYSTEM CHECKPOINT;

Addi ng Onl i ne Redo Log Fi l e Gr oups
# GROUP n is optional
ALTER DATABASE ADD LOGFILE GROUP 3
('/ORADATA/u01/log3a.rdo', '/ORADATA/u02/log3b.rdo')
SIZE 1M;
Addi ng Onl i ne Redo Log Fi l e Member s
ALTER DATABASE ADD LOGFILE MEMBER
'/ORADATA/u04/log1c.rdo' TO GROUP 1,
'/ORADATA/u04/log2c.rdo' TO GROUP 2,
'/ORADATA/u04/log3c.rdo' TO GROUP 3;
Dr oppi ng Onl i ne Redo Log Fi l e Gr oups
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
ALTER SYSTEM SWITCH LOGFILE;
ALTER DATABASE DROP LOGFILE GROUP 3;
Dr oppi ng Onl i ne Redo Log Fi l e Member s
ALTER DATABASE DROP LOGFILE MEMBER '$HOME/ORADATA/u04/log3c.rdo';
Rel ocat i ng and Renami ng Redo Log Member s
SHUTDOWN
Copy the redo log files to the new location.
STARTUP MOUNT
ALTER DATABASE
RENAME FILE '/diska/logs/log1a.rdo', '/diska/logs/log2a.rdo'
TO '/diskc/logs/log1c.rdo', '/diskc/logs/log2c.rdo';
ALTER DATABASE OPEN;
Page 76 Oracle DBA Code Examples

Ver i f y i ng Bl ock s i n Redo Log Fi l es
# it defaults to TURE
alter system set DB_BLOCK_CHECKSUM=true ;
Cl ear i ng a Redo Log Fi l e
# if DB stops becuase log file is corrupted
ALTER DATABASE CLEAR LOGFILE GROUP 2;
# the redo log file will be available even if not archived
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
Vi ew i ng Redo Log I nf or mat i on
SELECT * FROM V$LOG;
# STATUS: INVALID (inaccessible), STALE (incomplete), DELETED, Blank (in use)
SELECT * FROM V$LOGFILE;
SELECT * FROM V$LOG G, V$LOGFILE M where G.GROUP#=M.GROUP# order by M.GROUP#
Managi ng Ar chi ved Redo Logs
Obt ai ni ng I nf or mat i on about Ar chi v e Log
-- in SQL*Plus
ARCHIVE LOG LIST
-- to know archivelog mode of the database
select LOG_MODE from V$DATABASE

-- historical archived log information from the control file
select
RECID,
NAME,
DEST_ID,
THREAD#,
SEQUENCE#,
RESETLOGS_CHANGE#,
RESETLOGS_TIME,
RESETLOGS_ID,
FIRST_CHANGE#,
FIRST_TIME,
NEXT_CHANGE#,
NEXT_TIME,
BLOCKS * BLOCK_SIZE/1024/1024 MB ,
CREATOR,
ARCHIVED,
DELETED,
STATUS,
COMPLETION_TIME,
END_OF_REDO,
BACKUP_COUNT,
ARCHIVAL_THREAD#,
IS_RECOVERY_DEST_FILE,
COMPRESSED,
FAL,
Page 77 Oracle DBA Code Examples

BACKED_BY_VSS
from V$ARCHIVED_LOG;

-- information about archive log destinations and their status
select * from V$ARCHIVE_DEST

-- information about ARCn processes
select * from V$ARCHIVE_PROCESSES

-- information about any backup made on archived log files
select * from V$BACKUP_REDOLOG

-- online groups and which one to be archived
select * from V$LOG

-- log history information
select * from V$LOG_HISTORY
Changi ng t he Dat abase Ar chi v i ng Mode
select log_mode from v$database ;

CONN / AS SYSDBA
SHUTDOWN
Back up the database
see Specifying Archive Destinations ( next section )
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG; -- or NOARCHIVELOG
ALTER DATABASE OPEN;
SHUTDOWN IMMEDIATE
Back up the database
Speci f y i ng Ar chi v e Dest i nat i ons and t hei r Opt i ons
-- to local destinations
LOG_ARCHIVE_DEST_1 = 'LOCATION=/disk1/archive'
LOG_ARCHIVE_DEST_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST'

-- default is OPTIONAL, REOPEN in seconds (default 300)
-- if REOPEN is omitted, ARCn will never open a destination after a failure
LOG_ARCHIVE_DEST_2 = 'LOCATION=/disk2/archive MANDATORY REOPEN=600'
LOG_ARCHIVE_DEST_3 = 'LOCATION=/disk3/archive OPTIONAL'

-- to a standby db
LOG_ARCHIVE_DEST_4 = 'SERVICE = standby1'

-- control file format
LOG_ARCHIVE_FORMAT=t%t_s%s_r%r.arc
Speci f y i ng t he Mi ni mum Number of Successf ul Dest i nat i ons
alter system set LOG_ARCHIVE_MIN_SUCCEED_DEST=1
Page 78 Oracle DBA Code Examples

Cont r ol l i ng Ar chi v i ng t o a Dest i nat i on
alter system set LOG_ARCHIVE_DEST_STATE_2 = DEFER
alter system set LOG_ARCHIVE_DEST_STATE_2 = ENABLE
Cont r ol l i ng Tr ace Out put Gener at ed by t he Ar chi v el og Pr ocess
LOG_ARCHI VE_TRACE t akes combinat ion of:
0 Disable archivelog t racing. This is t he default .
1 Track archival of redo log file.
2 Track archival st at us for each archivelog dest inat ion.
4 Track archival operat ional phase.
8 Track archivelog dest inat ion act ivit y.
16 Track det ailed archivelog dest inat ion act ivit y.
32 Track archivelog dest inat ion paramet er modificat ions.
64 Track ARCn process st at e act ivit y.
128 Track FAL ( fet ch archived log) server relat ed act ivit ies.
256 Support ed in a fut ure release.
512 Tracks asynchronous LGWR act ivit y.
1024 RFS physical client t racking.
2048 ARCn/ RFS heart beat t racking.
4096 Track real- t ime apply

-- LOG_ARCHIVE_TRACE defaults to 0
select value from v$parameter where upper(name)='LOG_ARCHIVE_TRACE';
-- database must be mounted but not open.
ALTER SYSTEM SET LOG_ARCHIVE_TRACE=12;

Managi ng Tabl espaces
Obt ai ni ng Tabl espace I nf or mat i on
-- tablespace size usage report
BREAK ON REPORT
COMPUTE SUM OF tbsp_size ON REPORT
compute SUM OF used ON REPORT
compute SUM OF free ON REPORT
COL tbspname FORMAT a20 HEADING 'Tablespace Name'
COL tbsp_size FORMAT 999,999 HEADING 'Size|(MB)'
COL used FORMAT 999,999 HEADING 'Used|(MB)'
COL free FORMAT 999,999 HEADING 'Free|(MB)'
COL pct_used FORMAT 999 HEADING'% Used'

SELECT df.tablespace_name tbspname,
sum(df.bytes)/1024/1024 tbsp_size,
nvl(sum(e.used_bytes)/1024/1024,0) used,
nvl(sum(f.free_bytes)/1024/1024,0) free,
nvl((sum(e.used_bytes)*100)/sum(df.bytes),0) pct_used
FROM DBA_DATA_FILES df,
(SELECT file_id,
SUM(nvl(bytes,0)) used_bytes
FROM dba_extents
GROUP BY file_id) e,
(SELECT MAX(bytes) free_bytes, file_id
FROM dba_free_space
GROUP BY file_id) f
Page 79 Oracle DBA Code Examples

WHERE e.file_id(+) = df.file_id
AND df.file_id = f.file_id(+)
GROUP BY df.tablespace_name
ORDER BY 5 DESC
-- *************************************************************

-- tablespace info from control file
select TS#,NAME,INCLUDED_IN_DATABASE_BACKUP,
BIGFILE,FLASHBACK_ON,ENCRYPT_IN_BACKUP
from V$TABLESPACE;
-- descriptions of tablespaces
select TABLESPACE_NAME,BLOCK_SIZE,INITIAL_EXTENT,
NEXT_EXTENT NEXT_EXTENT_SIZE,MIN_EXTENTS,MAX_EXTENTS,
PCT_INCREASE,MIN_EXTLEN,STATUS,
CONTENTS,LOGGING,FORCE_LOGGING,
EXTENT_MANAGEMENT,ALLOCATION_TYPE,PLUGGED_IN,
SEGMENT_SPACE_MANAGEMENT,DEF_TAB_COMPRESSION,RETENTION
from DBA_TABLESPACES

-- segments contained in tablespaces
select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,S.TABLESPACE_NAME,
HEADER_FILE,HEADER_BLOCK,S.BYTES/1024/1024 SEGMENT_SIZE_MB,
D.BLOCKS SEGMENT_BLOCKS,EXTENTS,S.INITIAL_EXTENT,S.NEXT_EXTENT
NEXT_EXTENT_SIZE, S.MIN_EXTENTS,S.MAX_EXTENTS,S.PCT_INCREASE,
FREELISTS,FREELIST_GROUPS,D.RELATIVE_FNO, D.FILE_NAME
from DBA_SEGMENTS S, DBA_TABLESPACES T, DBA_DATA_FILES D
where S.TABLESPACE_NAME = T.TABLESPACE_NAME AND T.TABLESPACE_NAME =
D.TABLESPACE_NAME
AND S.RELATIVE_FNO = D.RELATIVE_FNO
AND T.TABLESPACE_NAME NOT IN ('SYSAUX','SYSTEM')
order BY S.TABLESPACE_NAME

-- extents contained by tablespaces
SELECT EXTENT_ID, E.BLOCK_ID,E.BYTES/1024 EXTENT_SIZE_KB,
E.OWNER,E.SEGMENT_NAME,E.PARTITION_NAME,
E.SEGMENT_TYPE,D.FILE_NAME, S.TABLESPACE_NAME,HEADER_FILE,
HEADER_BLOCK SEG_HEADER_BLOCK,S.BYTES/1024/1024 SEGMENT_SIZE_MB,D.BLOCKS
SEGMENT_BLOCKS, EXTENTS SEG_EXTENTS
FROM DBA_EXTENTS E, DBA_SEGMENTS S, DBA_DATA_FILES D
WHERE E.OWNER=S.OWNER AND E.SEGMENT_NAME = S.SEGMENT_NAME
AND NVL(E.PARTITION_NAME,'0') = NVL(S.PARTITION_NAME,'0')
AND E.SEGMENT_TYPE = S.SEGMENT_TYPE
AND E.FILE_ID = D.FILE_ID
AND S.TABLESPACE_NAME NOT IN ('SYSAUX','SYSTEM')
ORDER BY E.SEGMENT_NAME, E.OWNER, E.PARTITION_NAME,E.EXTENT_ID

-- free extents within tablespaces
SELECT F.TABLESPACE_NAME,F.FILE_ID,F.BLOCK_ID,
F.BYTES/1024/1024 FREE_MB, D.FILE_NAME
FROM DBA_FREE_SPACE F, DBA_DATA_FILES D
WHERE F.FILE_ID = D.FILE_ID
UNION
SELECT F.TABLESPACE_NAME, TO_NUMBER('') AS FILE_ID, TO_NUMBER('') AS BLOCK_ID,
SUM(F.BYTES/1024/1024) FREE_MB, TO_CHAR('') AS FILE_NAME
FROM DBA_FREE_SPACE F
GROUP BY F.TABLESPACE_NAME, TO_NUMBER('') , TO_NUMBER('') ,TO_CHAR('')
ORDER BY TABLESPACE_NAME

-- data files (from control file)
SELECT FILE#,T.NAME TABLESPACE_NAME,D.NAME FILENAME,
CREATION_CHANGE#,CREATION_TIME, RFILE#,STATUS,
Page 80 Oracle DBA Code Examples

ENABLED,CHECKPOINT_CHANGE#,CHECKPOINT_TIME,
UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,
LAST_TIME,OFFLINE_CHANGE#,ONLINE_CHANGE#,
ONLINE_TIME,BYTES/1024/1024 FILESIZE_MB,BLOCKS,
CREATE_BYTES,BLOCK_SIZE,
PLUGGED_IN,BLOCK1_OFFSET,AUX_NAME
FROM V$DATAFILE D, V$TABLESPACE T
WHERE D.TS# = T.TS#
ORDER BY TABLESPACE_NAME, D.RFILE#

-- data files
select FILE_NAME,FILE_ID,T.TABLESPACE_NAME,
F.STATUS FILE_STATUS,
RELATIVE_FNO,
AUTOEXTENSIBLE,
ROUND(BYTES/1024/1024,2) FILESIZE_MB,
ROUND(MAXBYTES/1024/1024,2) MAXSIZE_MB,
ROUND((INCREMENT_BY*T.BLOCK_SIZE)/1024/1024,2) AUTOEXTENSION_SIZE_MB,
ROUND(USER_BYTES/1024/1024,2) AVAILABLE_FOR_DATA_MB -- remaining size used
from DBA_DATA_FILES F, DBA_TABLESPACES T -- for storing metadata
where F.TABLESPACE_NAME = T.TABLESPACE_NAME
order BY TABLESPACE_NAME, F.RELATIVE_FNO

-- tempfiles included in tablespaces
select M.NAME TEMPFILE,FILE#,T.NAME TABLESPACE_NAME,
CREATION_TIME,M.TS#,RFILE#,
STATUS,ENABLED,BYTES,
BLOCKS,CREATE_BYTES,BLOCK_SIZE
from V$TEMPFILE M, V$TABLESPACE T
where M.TS# = T.TS#
order by T.NAME

select FILE_NAME,FILE_ID,T.TABLESPACE_NAME,
F.STATUS FILE_STATUS,
RELATIVE_FNO,
AUTOEXTENSIBLE,
ROUND(BYTES/1024/1024,2) FILESIZE_MB,
ROUND(MAXBYTES/1024/1024,2) MAXSIZE_MB,
ROUND((INCREMENT_BY*T.BLOCK_SIZE)/1024/1024,2) AUTOEXTENSION_SIZE_MB
from DBA_TEMP_FILES F, DBA_TABLESPACES
where F.TABLESPACE_NAME = T.TABLESPACE_NAME
order BY TABLESPACE_NAME, F.RELATIVE_FNO

-- extents in all locally managed temporary tablespaces
SELECT E.TABLESPACE_NAME,E.FILE_ID,
BLOCK_ID BEGIN_BLOCK#,
ROUND(E.BYTES/1024,2) EXTENT_SIZE_KB,
E.BLOCKS,OWNER
FROM V$TEMP_EXTENT_MAP E

-- tablespace groups
select GROUP_NAME, TABLESPACE_NAME
from DBA_TABLESPACE_GROUPS
order BY TABLESPACE_NAME

-- user qoutas
SELECT
USERNAME,TABLESPACE_NAME, BYTES/1024 SIZE_KB,
DECODE(MAX_BYTES,-1,-1,MAX_BYTES/1024/1024) MAX_MB
FROM DBA_TS_QUOTAS
ORDER BY USERNAME

Page 81 Oracle DBA Code Examples

-- user temporary usage
-- SEGTYPE: SORT,HASH,DATA,INDEX,LOB_DATA,LOB_INDEX
SELECT USERNAME,SESSION_NUM SESSION_SN,
SQLADDR,SQLHASH,SQL_ID,
TABLESPACE,
SEGTYPE,SEGFILE# INIT_EXTENT_FILE#,SEGBLK#
INIT_EXTENT_BLK#,EXTENTS,BLOCKS,SEGRFNO#
FROM V$TEMPSEG_USAGE
Cr eat i ng a Local l y Managed Tabl espace
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
EXTENT MANAGEMENT AUTOALLOCATE;
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; -- default 1MB
Speci f y i ng Segment Space Management
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO; -- though it's the default
-- the other option is MANUAL
Addi ng Space t o Tabl espace
ALTER TABLESPACE test01 ADD DATAFILE '..' SIZE 1000M;
ALTER DATABASE DATAFILE '' RESIZE 500m;
Speci f y i ng Nonst andar d Bl ock Si zes f or Tabl espaces
-- must be 2KB, 4KB, 8KB, 16KB, or 32KB.
-- requirement
set DB_CACHE_SIZE, DB_nK_CACHE_SIZE
select value/1024 KB from v$parameter where name='db_block_size';
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K BLOCKSIZE 8K;
Usi ng Bi gf i l e Tabl espace ( BFT)
Oracle recommends t hat you change t he ext ent allocat ion policy from AUTOALLOCATE,
which is t he default , t o UNI FORM and set a very high ext ent size.
select TABLESPACE_NAME, BIGFILE from DBA_TABLESPACES order by BIGFILE;
-- use BigFile tablespaces only with ASM or RAID volume manager.
-- it can take up to 4G blocks. with 8K blocks = 32 terabyte datafile
CREATE BIGFILE TABLESPACE bigtbs -- the other option SMALLFILE
DATAFILE '/u02/oracle/data/bigtbs01.dbf' SIZE 50G -- T is acceptable
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 65536K; -- default 1m
ALTER TABLESPACE bigtbs RESIZE 80G;
ALTER TABLESPACE bigtbs AUTOEXTEND ON NEXT 20G;
-- with this sitting, by default all tbs created later will be BFT
CREATE DATABASE SET DEFAULT BIGFILE tablespace -- the other option SMALLFILE
ALTER TABLESPACE SET DEFAULT BIGFILE TABLESPACE;
SELECT property_value FROM database_properties
WHERE property_name='DEFAULT_TBS_TYPE';

Page 82 Oracle DBA Code Examples

Usi ng Tempor ar y Tabl espace
Oracle recommends creat ing t emporary t ablespaces wit h mult iples- of- 64KB ext ent sizes.
For large dat a warehousing, make it 1MB.
select * from V$TEMPFILE;
select * from DBA_TEMP_FILES;
CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '/u02/oracle/data/lmtemp01.dbf'
SIZE 20M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M; -- default is 1M (AUTOALLOCATE not
allowed)
CREATE BIGFILE TABLESPACE bigtbs
TEMPFILE '/u02/oracle/data/bigtbs01.dbf' SIZE 50G
ALTER TABLESPACE lmtemp ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 18M
REUSE;
-- doesn't apply on default temporary tablespace
ALTER TABLESPACE lmtemp TEMPFILE OFFLINE;
ALTER TABLESPACE lmtemp TEMPFILE ONLINE;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' OFFLINE;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' ONLINE;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;
-- the tablespace though isn't dropped
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP INCLUDING
DATAFILES;
Renami ng a Tempf i l e
ALTER DATABASE TEMPFILE 'C:\ORACLE\ORADATA\ORCL\TEMP02.DBF' OFFLINE;
ren C:\ORACLE\ORADATA\ORCL\TEMP02.DBF TEMP03.DBF
ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\ORCL\TEMP02.DBF' TO
'C:\ORACLE\ORADATA\ORCL\TEMP03.DBF';
ALTER DATABASE TEMPFILE 'C:\ORACLE\ORADATA\ORCL\TEMP03.DBF' ONLINE;
Shr i nk i ng Tempor ar y Tabl espace
ALTER TABLESPACE temp SHRINK SPACE KEEP 1000M;
ALTER TABLESPACE temp SHRINK SPACE TEMPFILE tempfile
'/u01/app/oracle/oradata/prod1/temp02.dbf' KEEP 100m;
SELECT file#, name, bytes/1024/1024 mb FROM v$tempfile;
Usi ng Def aul t Tempor ar y Tabl espace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temptbs02; -- can be temp tbs grp
SELECT PROPERTY_NAME, PROPERTY_VALUE
FROM database_properties
WHERE property_name='DEFAULT_TEMP_TABLESPACE';
Usi ng Tempor ar y Tabl espace Gr oups
CREATE TEMPORARY TABLESPACE lmtemp2 TEMPFILE '/u02/oracle/data/lmtemp201.dbf'
SIZE 50M TABLESPACE GROUP group1;
ALTER TABLESPACE lmtemp TABLESPACE GROUP group2;
-- remove it from a group
ALTER TABLESPACE lmtemp3 TABLESPACE GROUP '';
-- Assigning a Tablespace Group as the Default Temporary Tablespace
ALTER DATABASE mydb DEFAULT TEMPORARY TABLESPACE group2;
Page 83 Oracle DBA Code Examples

select GROUP_NAME, TABLESPACE_NAME
from DBA_TABLESPACE_GROUPS
order BY TABLESPACE_NAME
Suppr essi ng Redo Gener at i on f or a Tabl espace
CREATE TABLESPACE .. NOLOGGING;
Cont r ol l i ng Tabl espaces Av ai l abi l i t y
-- NORMAL, TEMPORARY, IMMEDIATE (not possible in NOARCHIVELOG)
ALTER TABLESPACE users OFFLINE NORMAL;
-- media recovery required if it was offline using TEMPORARY or IMMEDIATE
ALTER TABLESPACE users ONLINE;
Usi ng Read- Onl y Tabl espaces
-- backup the tablespace after making it read only
-- it waits for all transactions started before
ALTER TABLESPACE flights READ ONLY;
-- to list blocking transactions:
SELECT SQL_TEXT, SADDR
FROM V$SQLAREA,V$SESSION
WHERE V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS
AND SQL_TEXT LIKE 'alter tablespace%';
-- all transactions on top of SADDR retuned by previous statement
-- are blocking transactions
SELECT T.SES_ADDR, T.START_SCNB, S.USERNAME, S.MACHINE
FROM V$TRANSACTION T, V$SESSION S
WHERE T.SES_ADDR = S.SADDR
ORDER BY START_SCNB;
-- back to read write
ALTER TABLESPACE flights READ WRITE;
Renami ng Tabl espaces
ALTER TABLESPACE users RENAME TO usersts;
Def aul t Per manent Tabel space
SELECT property_value FROM database_properties WHERE
property_name='DEFAULT_PERMANENT_TABLESPACE';
ALTER DATABASE DEFAULT TABLESPACE users;
Dr oppi ng Tabl espaces
DROP TABLESPACE users INCLUDING CONTENTS;
DROP TABLESPACE test01 CASCADE CONSTRAINTS;
DROP TABLESPACE users INCLUDING CONTENTS CASCADE CONSTRAINTS;
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;
Managi ng t he SYSAUX Tabl espace
A t ypical syst em wit h an average of 30 concurrent act ive sessions may require approximat ely
200 t o 300 MB of space for it s AWR dat a.
Page 84 Oracle DBA Code Examples

-- to monitor is occupants
-- to know which procedure to use to move an occupant
SELECT OCCUPANT_NAME , OCCUPANT_DESC , SCHEMA_NAME ,MOVE_PROCEDURE
,MOVE_PROCEDURE_DESC ,SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS
Di agnosi ng and Repai r i ng Local l y Managed Tabl espace Pr obl ems
Ver i f y i ng t he I nt egr i t y of Segment s Cr eat ed i n ASSM Tabl espaces.
Use DBMS_SPACE_ADMI N. ASSM_SEGMENT_VERI FY.
I f ASSM is disabled, use SEGMENT_VERI FY.
Aft er execut ion, check sid_ora_process_I D. t rc in USER_DUMP_DEST.
The paramet er verify_opt ion t akes one of t he following const ant s:
o SEGMENT_VERI FY_DEEP ( 9)
o SEGMENT_VERI FY_BASI C ( 10) default
o SEGMENT_VERI FY_SPECI FI C ( 11) t hen t he at t rib paramet er is considered.
at t rib t akes one of t he following const ant s:
o HWM_CHECK ( 12) checks whet her high wat er mark informat ion is accurat e.
o BMB_CHECK ( 13) checks whet her space bit map blocks have correct backpoint ers t o
t he segment header.
o SEG_DI CT_CHECK ( 14) checks whet her dict ionary informat ion for segment is
accurat e.
o EXTENT_TS_BI TMAP_CHECK ( 15) checks whet her ext ent maps are consist ent wit h file
level bit maps.
o DB_BACKPOI NTER_CHECK ( 16) checks whet her dat ablocks have correct backpoint ers
t o t he space met adat a blocks.
o EXTENT_SEGMENT_BI TMAP_CHECK ( 17) checks whet her ext ent map in t he segment
mat ch wit h t he bit naps in t he segment .
o BI TMAPS_CHECK ( 18) checks whet her space bit map blocks are accurat e.
declare
v_segname varchar2(100) := 'EMPLOYEES';
v_segowner varchar2(100) :='HR';
v_segtype varchar2(100) :='TABLE';
v_tbs varchar2(100);
begin

select tablespace_name
into v_tbs
from dba_segments
where segment_name=v_segname and owner=v_segowner
and segment_type=v_segtype;

DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY (
segment_owner =>v_segowner,
segment_name =>v_segname,
segment_type =>v_segtype,
partition_name =>'',
verify_option => DBMS_SPACE_ADMIN.SEGMENT_VERIFY_DEEP
);
end;
Page 85 Oracle DBA Code Examples

/

-- Check sid_ora_process_ID.trc in USER_DUMP_DEST
select value from v$parameter where upper(name)='USER_DUMP_DEST'

declare
v_segname varchar2(100) := 'EMPLOYEES';
v_segowner varchar2(100) :='HR';
v_segtype varchar2(100) :='TABLE';
v_tbs varchar2(100);
begin

select tablespace_name
into v_tbs
from dba_segments
where segment_name=v_segname and owner=v_segowner
and segment_type=v_segtype;

DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY (
segment_owner =>v_segowner,
segment_name =>v_segname,
segment_type =>v_segtype,
partition_name =>'',
verify_option => DBMS_SPACE_ADMIN.SEGMENT_VERIFY_SPECIFIC,
attrib => DBMS_SPACE_ADMIN.BMB_CHECK
);
end;
/

-- Check sid_ora_process_ID.trc in USER_DUMP_DEST
select value from v$parameter where upper(name)='USER_DUMP_DEST'
Check i ng Consi st ency of Segment Ex t ent Map w i t h Tabl espace Fi l e Bi t maps
Use ASSM_SEGMENT_VERI FY t o segment residing in a t ablespace wit h aut omat ic segment
space management enabled and SEGMENT_VERI FY when it is disabled.
Aft er execut ion, check sid_ora_process_I D. t rc in USER_DUMP_DEST.
conn sys as sysdba
declare
v_segname varchar2(100) := 'EMPLOYEES';
v_segowner varchar2(100) :='HR';
v_tbs varchar2(100);
v_fno number ;
v_rfno number;
v_hdr number;
begin
-- retreive tablespace name, absolute file number
select tablespace_name, header_file, header_block
into v_tbs, v_fno, v_hdr
from dba_segments
where segment_name=v_segname and owner=v_segowner;

select relative_fno
into v_rfno
from dba_data_files where tablespace_name = v_tbs and file_id=v_fno;

DBMS_SPACE_ADMIN.SEGMENT_VERIFY(
tablespace_name =>v_tbs,
Page 86 Oracle DBA Code Examples

header_relative_file =>v_rfno,
header_block =>v_hdr,
verify_option =>dbms_space_admin.SEGMENT_VERIFY_EXTENTS_GLOBAL --
default SEGMENT_VERIFY_EXTENTS
);
end;
/
-- Check sid_ora_process_ID.trc in USER_DUMP_DEST
select value from v$parameter where upper(name)='USER_DUMP_DEST'
Ver i f y i ng t he I nt egr i t y of ASSM Tabl espaces
Use DBMS_SPACE_ADMI N. ASSM_TABLESPACE_VERI FY
I f ASSM is disabled, use TABLESPACE_VERI FY
Aft er execut ion, check sid_ora_process_I D. t rc in USER_DUMP_DEST.
The paramet er t s_opt ion t akes one of t he following const ant s:
o TS_VERI FY_BI TMAPS ( 19) ( Default ) The bit maps are verified against t he ext ent s. This
will det ect bit s t hat are marked used or free wrongly and will also det ect mult iple
allocat ion of ext ent s. The file met adat a will be validat ed against file$ and cont rol file.
o TS_VERI FY_DEEP ( 20) verifies t he file bit maps as well perform checks on all t he
segment s.
o TS_SEGMENTS ( 21) This opt ion is used t o invoke SEGMENT_VERI FY on all t he
segment s in t he t ablespace.
segment _opt ion: when t he TABLESPACE_VERI FY_SEGMENTS or
TABLESPACE_VERI FY_DEEP is select ed, t he SEGMENT_OPTI ON can be specified opt ionally.
When TS_VERI FY_SEGMENTS is specified, segment _opt ion can be one of t he following:
o SEGMENT_VERI FY_BASI C ( 9)
o SEGMENT_VERI FY_DEEP ( 10)
The value of segment _opt ion is NULL when TS_VERI FY_DEEP or TS_VERI FY_BASI C is
specified.
Aft er execut ion, check sid_ora_process_I D. t rc in USER_DUMP_DEST
conn sys as sysdba

select name from v$tablespace order by name;

begin
DBMS_SPACE_ADMIN.ASSM_TABLESPACE_VERIFY(
tablespace_name =>'EXAMPLE',
ts_option => DBMS_SPACE_ADMIN.TS_VERIFY_DEEP,
segment_option =>NULL);
end;
/

-- Check sid_ora_process_ID.trc in USER_DUMP_DEST
select value from v$parameter where upper(name)='USER_DUMP_DEST'
Mar k i ng t he Segment Cor r upt or Val i d
corrupt _opt ion t akes one of t he following:
o SEGMENT_MARK_CORRUPT (default )
o SEGMENT_MARK_VALI D
Page 87 Oracle DBA Code Examples

conn sys as sysdba

declare
v_segname varchar2(100) := 'EMPLOYEES';
v_segowner varchar2(100) :='HR';
v_tbs varchar2(100);
v_fno number ;
v_rfno number;
v_hdr number;
begin
-- retreive tablespace name, absolute file number
select tablespace_name, header_file, header_block
into v_tbs, v_fno, v_hdr
from dba_segments
where segment_name=v_segname and owner=v_segowner;

select relative_fno
into v_rfno
from dba_data_files
where tablespace_name = v_tbs and file_id=v_fno;

DBMS_SPACE_ADMIN.SEGMENT_CORRUPT (
tablespace_name =>v_tbs,
header_relative_file =>v_rfno,
header_block =>v_hdr,
corrupt_option =>DBMS_SPACE_ADMIN.SEGMENT_MARK_CORRUPT);
end;
Dr oppi ng a Cor r upt ed Segment
Use SEGMENT_DROP_CORRUPT t o drop a segment current ly marked corrupt ( wit hout
reclaiming space) . The space for t he segment is not released, and it must be fixed by
using t he TABLESPACE_FI X_BI TMAPS Procedure or t he TABLESPACE_REBUI LD_BI TMAPS
Procedure.
I f t he segment st at e is valid, t he procedure ret urns ORA- 03211 error.
conn sys as sysdba

declare
v_segname varchar2(100) := 'EMP';
v_segowner varchar2(100) :='HR';
v_tbs varchar2(100);
v_fno number ;
v_rfno number;
v_hdr number;
begin
-- retreive tablespace name, absolute file number
select tablespace_name, header_file, header_block
into v_tbs, v_fno, v_hdr
from dba_segments
where segment_name=v_segname and owner=v_segowner;

select relative_fno
into v_rfno
from dba_data_files
where tablespace_name = v_tbs and file_id=v_fno;

DBMS_SPACE_ADMIN.SEGMENT_DROP_CORRUPT(
tablespace_name =>v_tbs,
header_relative_file =>v_rfno,
header_block => v_hdr);
Page 88 Oracle DBA Code Examples

end;
Dumpi ng a Segment Header and Bi t map Bl ock s
Use DBMS_SPACE_ADMI N. SEGMENT_DUMP
The dump file named sid_ora_process_id.t rc is generat ed in t he locat ion specified in t he
USER_DUMP_DEST
conn sys as sysdba
declare
v_segname varchar2(100) := 'NAMES';
v_segowner varchar2(100) :='HR';
v_tbs varchar2(100);
v_fno number;
v_rfno number;
v_hdr number;
begin
-- retreive tablespace name, absolute file number
select tablespace_name, header_file, header_block
into v_tbs, v_fno, v_hdr
from dba_segments
where segment_name=v_segname and owner=v_segowner;

select relative_fno
into v_rfno
from dba_data_files
where tablespace_name = v_tbs and file_id=v_fno;

DBMS_SPACE_ADMIN.SEGMENT_DUMP(
tablespace_name =>v_tbs,
header_relative_file =>v_rfno,
header_block =>v_hdr,
dump_option => DBMS_SPACE_ADMIN.SEGMENT_DUMP_EXTENT_MAP);
end;
Mar k i ng a DBA Range i n Bi t map as Fr ee or Used
The procedure TABLESPACE_FI X_BI TMAPS marks t he appropriat e DBA range ( ext ent ) as
free or used in bit map.
The BEGI N and END blocks should be in ext ent boundary and should be ext ent mult iple.
fix_opt ion t akes one of t he following
o TABLESPACE_EXTENT_MAKE_FREE
o TABLESPACE_EXTENT_MAKE_USED
conn sys as sysdba
DBMS_SPACE_ADMIN.TABLESPACE_FIX_BITMAPS (
tablespace_name =>'EXAMPLE',
dbarange_relative_file =>4,
dbarange_begin_block =>33,
dbarange_end_block =>83,
fix_option =>DBMS_SPACE_ADMIN.TABLESPACE_EXTENT_MAKE_FREE);
Rebui l di ng t he Appr opr i at e Bi t map
conn sys
begin
DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS (
tablespace_name =>'EXAMPLE',
Page 89 Oracle DBA Code Examples

bitmap_relative_file =>NULL, -- all files
bitmap_block =>NULL); -- Block number of bitmap block to rebuild
-- NULL = all blocks
end;
Rebui l di ng Quot as f or Gi v en Tabl espace
conn sys
exec DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_QUOTAS('USERS');
Mi gr at i ng f r om a Di ct i onar y - Managed t o a Local l y Managed Tabl espace
-- This operation is done online, but space management operations are blocked
-- ASSM won't be active on migrated objects
conn sys
EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('USERS');

-- another way requiring table lock (better)
ALTER TABLE emp MOVE TABLESPACE tbsp_new;
ALTER INDEX emp_pk_idx REBUILD TABLESPACE tbsp_idx_new;

Fi x i ng t he St at e of t he Segment s i n A Tabl espace
Use TABLESPACE_FI X_SEGMENT_STATES t o fix t he st at e of t he segment s in a t ablespace in
which migrat ion was abort ed.
conn sys
EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_STATES('TS1')
Scenar i o 1: Fi x i ng Bi t map When Al l ocat ed Bl ock s ar e Mar k ed Fr ee ( No Ov er l ap)
The TABLESPACE_VERI FY procedure discovers t hat a segment has allocat ed blocks t hat are
marked free in t he bit map, but no overlap bet ween segment s is report ed.
I n t his scenario, perform t he following t asks:
1. Call t he SEGMENT_DUMP procedure t o dump t he ranges t hat t he administ rat or allocat ed t o
t he segment .
2. For each range, call t he TABLESPACE_FI X_BI TMAPS procedure wit h t he
TABLESPACE_EXTENT_MAKE_USED opt ion t o mark t he space as used.
3. Call TABLESPACE_REBUI LD_QUOTAS t o fix up quot as.
Scenar i o 2: Dr oppi ng a Cor r upt ed Segment
You cannot drop a segment because t he bit map has segment blocks marked "free". The
syst em has aut omat ically marked t he segment corrupt ed. I n t his scenario, perform t he
following t asks:
1. Call t he SEGMENT_VERI FY procedure wit h t he SEGMENT_VERI FY_EXTENTS_GLOBAL opt ion.
I f no overlaps are report ed, t hen proceed wit h st eps 2 t hrough 5.
2. Call t he SEGMENT_DUMP procedure t o dump t he DBA ranges allocat ed t o t he segment .
3. For each range, call TABLESPACE_FI X_BI TMAPS wit h t he
TABLESPACE_EXTENT_MAKE_FREE opt ion t o mark t he space as free.
4. Call SEGMENT_DROP_CORRUPT t o drop t he SEG$ ent ry.
5. Call TABLESPACE_REBUI LD_QUOTAS t o fix up quot as.
Page 90 Oracle DBA Code Examples

Scenar i o 3: Fi x i ng Bi t map Wher e Ov er l ap i s Repor t ed
The TABLESPACE_VERI FY procedure report s some overlapping. Some of t he real dat a must be
sacrificed based on previous int ernal errors. Aft er choosing t he obj ect t o be sacrificed, in t his
case say, t able t 1, perform t he following t asks:
1. Make a list of all obj ect s t hat t 1 overlaps.
2. Drop t able t 1. I f necessary, follow up by calling t he SEGMENT_DROP_CORRUPT procedure.
3. Call t he SEGMENT_VERI FY procedure on all obj ect s t hat t 1 overlapped. I f necessary, call
t he TABLESPACE_FI X_BI TMAPS procedure t o mark appropriat e bit map blocks as used.
4. Rerun t he TABLESPACE_VERI FY procedure t o verify t he problem is resolved.
Scenar i o 4: Cor r ect i ng Medi a Cor r upt i on of Bi t map Bl ock s
A set of bit map blocks has media corrupt ion. I n t his scenario, perform t he following t asks:
1. Call t he TABLESPACE_REBUI LD_BI TMAPS procedure, eit her on all bit map
blocks, or on a single block if only one is corrupt .
2. Call t he TABLESPACE_REBUI LD_QUOTAS procedure t o rebuild quot as.
3. Call t he TABLESPACE_VERI FY procedure t o verify t hat t he bit maps are consist ent .


Tr anspor t i ng Tabl espaces Bet w een Dat abases
Limit at ions on Transport able Tablespace Use
The source and t arget dat abase must use t he same charact er set and nat ional charact er
set .
Obj ect s wit h underlying obj ect s ( such as mat erialized views) or cont ained obj ect s ( such as
part it ioned t ables) are not t ransport able unless all of t he underlying or cont ained obj ect s
are in t he t ablespace set .
You cannot t ransport t he SYSTEM t ablespace or obj ect s owned by t he user SYS. This
means t hat you cannot use TTS for PL/ SQL, t riggers, or views. These would have t o be
moved wit h export .
You cannot t ransport a t able wit h a mat erialized view unless t he mview is in t he t ransport
set you creat e.
You cannot t ransport a part it ion of a t able wit hout t ransport ing t he ent ire t able.

1. Check endian format of bot h plat forms.
For cross-plat form t ransport , check t he endian format of bot h plat forms by querying t he
V$TRANSPORTABLE_PLATFORM view.
You can find out your own plat form name:
select platform_name from v$database

2. Pick a self- cont ained set of t ablespaces.
The following st at ement can be used t o det ermine whet her t ablespaces sales_1 and sales_2
are self- cont ained, wit h referent ial int egrit y const raint s t aken int o considerat ion:
DBMS_TTS.TRANSPORT_SET_CHECK( TS_LIST =>'sales_1,sales_2', INCL_CONSTRAINTS
=>TRUE, FULL_CHECK =>TRUE)
Not e: You must have been grant ed t he EXECUTE_CATALOG_ROLE role ( init ially signed t o SYS)
t o execut e t his procedure.
Page 91 Oracle DBA Code Examples

You can see all violat ions by select ing from t he TRANSPORT_SET_VIOLATIONS view. I f t he set
of t ablespaces is self- cont ained, t his view is empt y.
3. Generat e a t ransport able t ablespace set .
3. 1.Make all t ablespaces in t he set you are copying read-only.
3. 2.Export t he met adat a describing t he obj ect s in t he t ablespace(s)
EXPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir
TRANSPORT_TABLESPACES = sales_1,sales_2
TRANSPORT_FULL_CHECK=Y
3. 3.I f you want t o convert t he t ablespaces in t he source dat abase, use t he RMAN
RMAN TARGET /
CONVERT TABLESPACE sales_1,sales_2
TO PLATFORM 'Microsoft Windows NT'
FORMAT '/temp/%U'
4. Transport t he t ablespace set .
Transport bot h t he dat afiles and t he export file of t he t ablespaces t o a place accessible t o
t he t arget dat abase.
5. Convert t ablespace set , if required, in t he dest inat ion dat abase.
Use RMAN as follows:
RMAN> CONVERT DATAFILE
'/hq/finance/work/tru/tbs_31.f',
'/hq/finance/work/tru/tbs_32.f',
'/hq/finance/work/tru/tbs_41.f'
TO PLATFORM="Solaris[tm] OE (32-bit)"
FROM PLATFORM="HP TRu64 UNIX"
DBFILE_NAME_CONVERT=
"/hq/finance/work/tru/", "/hq/finance/dbs/tru"
PARALLELISM=5
Not e: The source and dest inat ion plat forms are opt ional.
Not e: By default , Oracle places t he convert ed files in t he Flash Recovery Area, wit hout
changing t he dat afile names.
Not e: I f you have CLOB dat a on a small- endian syst em in an Oracle dat abase version before
10g and wit h a varying- widt h charact er set and you are t ransport ing t o a dat abase in a big-
endian syst em, t he CLOB dat a must be convert ed in t he dest inat ion dat abase. RMAN does
not handle t he conversion during t he CONVERT phase. However, Oracle dat abase
aut omat ically handles t he conversion while accessing t he CLOB dat a.
I f you want t o eliminat e t his run- t ime conversion cost from t his aut omat ic conversion, you
can issue t he CREATE TABLE AS SELECT command before accessing t he dat a.
6. Plug in t he t ablespace.
IMPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir
TRANSPORT_DATAFILES=
/salesdb/sales_101.dbf,
/salesdb/sales_201.dbf
REMAP_SCHEMA=(dcranney:smith) REMAP_SCHEMA=(jfee:williams)
I f required, put t he t ablespace int o READ WRITE mode.
Usi ng Tr anspor t abl e Tabl espaces: Scenar i os
Tr anspor t i ng and At t achi ng Par t i t i ons f or Dat a War ehousi ng
1. I n a st aging dat abase, you creat e a new t ablespace and make it cont ain t he t able you
want t o t ransport . I t should have t he same columns as t he dest inat ion part it ioned t able.
2. Creat e an index on t he same columns as t he local index in t he part it ioned t able.
3. Transport t he t ablespace t o t he dat a warehouse.
4. I n t he dat a warehouse, add a part it ion t o t he t able.
ALTER TABLE sales ADD PARTITION jul98 VALUES LESS THAN (1998, 8, 1)
Page 92 Oracle DBA Code Examples

5. At t ach t he t ransport ed t able t o t he part it ioned t able by exchanging it wit h t he new
part it ion:
ALTER TABLE sales EXCHANGE PARTITION jul98 WITH TABLE jul_sales
INCLUDING INDEXES WITHOUT VALIDATION
Publ i shi ng St r uct ur ed Dat a on CDs
A dat a provider can load a t ablespace wit h dat a t o be published, generat e t he t ransport able
set , and copy t he t ransport able set t o a CD. When cust omers receive t his CD, t hey can plug
it int o an exist ing dat abase wit hout having t o copy t he dat afiles from t he CD t o disk st orage.
Not e: I n t his case, it is highly recommended t o set t he READ_ONLY_OPEN_DELAYED
init ializat ion paramet er t o TRUE.
Movi ng Dat abases Acr oss Pl at f or ms Usi ng Tr anspor t abl e Tabl espaces
You can use t he t ransport able t ablespace feat ure t o migrat e a dat abase t o a different
plat form.
However, you cannot t ransport t he SYSTEM t ablespace. Therefore, obj ect s such as
sequences, PL/ SQL packages, and ot her obj ect s t hat depend on t he SYSTEM t ablespace are
not t ransport ed. You must eit her creat e t hese obj ect s manually on t he dest inat ion dat abase,
or use Dat a Pump t o t ransport t he obj ect s t hat are not moved by t ransport able t ablespace.

Managi ng Al er t Thr eshol ds
Get t i ng t he Cur r ent Thr eshol d Set t i ng
List of support ed met rics can be found in t he document at ion "PL/ SQL Packages and Types
Reference": 10g, 11g or link or from t he query below:
SELECT METRIC_ID, METRIC_NAME, METRIC_UNIT,
GROUP_ID, GROUP_NAME
FROM V$METRICNAME
ORDER BY METRIC_NAME

-- current threshold settings
select * from DBA_THRESHOLDS;

set serveroutput on
DECLARE
V_WOPERATOR BINARY_INTEGER;
V_WVALUE VARCHAR2(50);
V_COPERATOR BINARY_INTEGER;
V_CVALUE VARCHAR2(50);
V_OBS_PERIOD BINARY_INTEGER;
V_CON_PERIOD BINARY_INTEGER;
FUNCTION GET_OPERATOR_NAME( P_OPER IN BINARY_INTEGER) RETURN VARCHAR2
IS
BEGIN
IF P_OPER =0 THEN
RETURN 'GT';
ELSIF P_OPER =1 THEN
RETURN 'EQ';
ELSIF P_OPER =2 THEN
RETURN 'LT';
ELSIF P_OPER =3 THEN
RETURN 'LE';
ELSIF P_OPER =4 THEN
Page 93 Oracle DBA Code Examples

RETURN 'GE';
ELSIF P_OPER =5 THEN
RETURN 'OPERATOR_CONTAINS';
ELSIF P_OPER =6 THEN
RETURN 'NE';
ELSIF P_OPER =7 THEN
RETURN 'OPERATOR_DO_NOT_CHECK';
END IF;
END GET_OPERATOR_NAME;
BEGIN
DBMS_SERVER_ALERT.GET_THRESHOLD(
metrics_id =>DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
warning_operator =>V_WOPERATOR ,
warning_value =>V_WVALUE,
critical_operator =>V_COPERATOR,
critical_value =>V_CVALUE,
observation_period =>V_OBS_PERIOD,
consecutive_occurrences =>V_CON_PERIOD,
instance_name =>NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name =>NULL);
DBMS_OUTPUT.PUT_LINE('Warning Op.: '|| GET_OPERATOR_NAME(V_WOPERATOR));
DBMS_OUTPUT.PUT_LINE('Warning Val: '||V_WVALUE);
DBMS_OUTPUT.PUT_LINE('Critical Op.: '|| GET_OPERATOR_NAME(V_COPERATOR));
DBMS_OUTPUT.PUT_LINE('Critical Val: '||V_CVALUE);
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE='-13799' THEN
DBMS_OUTPUT.PUT_LINE('No threshold was found with the specified threshold
key.(ORA-13799)');
ELSE
RAISE;
END IF;
END;
/
Set t i ng Tabl espace Al er t Thr eshol ds
warning_operat or t akes one of t he following ( not all applicable for all met rics) :
o OPERATOR_EQ GE GT LE LT NE
o OPERATOR_CONTAI NS
o OPERATOR_DO_NOT_CHECK ( disables t he alert for t he specified met ric)
-- set the free-space-remaining thresholds in the USERS tablespace to 10 MB
(warning)
-- and 2 MB (critical), and disable the percent-full thresholds.
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_BYT_FREE, -- Tablespace FREE
space in KB
warning_operator => DBMS_SERVER_ALERT.OPERATOR_LT, -- GT is not applicable
here
warning_value => '10240',
critical_operator => DBMS_SERVER_ALERT.OPERATOR_LT,
critical_value => '2048',
observation_period => 1, -- computation period (1-60 min)
consecutive_occurrences => 1, -- violation times before alert
instance_name => NULL, -- NULL= 'database_wide'. Passed value is not checked
Page 94 Oracle DBA Code Examples

object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => 'USERS'); -- if NULL -> All Tablespaces

DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL, -- tablespace USAGE by
%
warning_operator => DBMS_SERVER_ALERT.OPERATOR_DO_NOT_CHECK,
warning_value => '0',
critical_operator => DBMS_SERVER_ALERT.OPERATOR_DO_NOT_CHECK,
critical_value => '0',
observation_period => 1,
consecutive_occurrences => 1,
instance_name => NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => 'USERS');
END;
/

SELECT *
FROM dba_thresholds
where object_name = 'USERS' and object_type='TABLESPACE';
Rest or i ng a Tabl espace t o Dat abase Def aul t Thr eshol ds
You can rest ore t he met ric t hreshold values t o revert t o t he dat abase default s by set t ing
t hem t o NULL in t he DBMS_SERVER_ALERT. SET_THRESHOLD.
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL, -- tablespace USAGE by
%
warning_operator => NULL, -- do not use ''
warning_value => NULL,
critical_operator => NULL,
critical_value => NULL,
observation_period => 1,
consecutive_occurrences => 1,
instance_name => NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => 'USERS');
END;
/

SELECT *
FROM dba_thresholds
where object_name = 'USERS' and object_type='TABLESPACE';
Modi f yi ng Dat abase Def aul t Thr eshol ds
set t he obj ect _name t o NULL
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL, -- redo with
TABLESPACE_BYT_FREE
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GT,
warning_value => '80',
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GT,
critical_value => '92',
observation_period => 1,
Page 95 Oracle DBA Code Examples

consecutive_occurrences => 1,
instance_name => NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => NULL);
END;
/

SELECT *
FROM dba_thresholds
where object_name = 'USERS' and object_type='TABLESPACE';
Vi ew i ng Al er t s
-- outstanding alerts (to be cleared)
SELECT
SEQUENCE_ID,OWNER,
OBJECT_NAME,OBJECT_TYPE,SUBOBJECT_NAME,
REASON_ID, REASON,TIME_SUGGESTED,
SUGGESTED_ACTION,ADVISOR_NAME,METRIC_VALUE,
MESSAGE_TYPE,MESSAGE_GROUP,MESSAGE_LEVEL,
HOSTING_CLIENT_ID,MODULE_ID,PROCESS_ID,
HOST_ID,HOST_NW_ADDR,INSTANCE_NAME,
INSTANCE_NUMBER,USER_ID,EXECUTION_CONTEXT_ID,CREATION_TIME
FROM DBA_OUTSTANDING_ALERTS

-- history of alerts that have been cleared
SELECT
SEQUENCE_ID,OWNER,
OBJECT_NAME,SUBOBJECT_NAME,OBJECT_TYPE,
REASON_ID, REASON,TIME_SUGGESTED,
SUGGESTED_ACTION,ADVISOR_NAME,METRIC_VALUE,
MESSAGE_TYPE,MESSAGE_GROUP,MESSAGE_LEVEL,
HOSTING_CLIENT_ID,MODULE_ID,PROCESS_ID,
HOST_ID,HOST_NW_ADDR,INSTANCE_NAME,INSTANCE_NUMBER,
USER_ID,EXECUTION_CONTEXT_ID,CREATION_TIME
FROM DBA_ALERT_HISTORY
ORDER BY SEQUENCE_ID

-- list of all metrics
SELECT METRIC_ID, METRIC_NAME, METRIC_UNIT,GROUP_ID, GROUP_NAME
FROM V$METRICNAME
ORDER BY METRIC_NAME

-- system-level metric values in memory
SELECT BEGIN_TIME,END_TIME,INTSIZE_CSEC,
GROUP_ID,ENTITY_ID,ENTITY_SEQUENCE,
METRIC_ID,METRIC_NAME,VALUE,METRIC_UNIT
FROM V$METRIC - also V$METRIC_HISTORY
ORDER BY BEGIN_TIME, VALUE DESC

-- alert types
select
INST_ID,REASON_ID,OBJECT_TYPE,TYPE,GROUP_NAME,SCOPE,INTERNAL_METRIC_CATEGORY,
INTERNAL_METRIC_NAME
from GV$ALERT_TYPES
order by OBJECT_TYPE,TYPE
Page 96 Oracle DBA Code Examples

Managi ng Dat af i l es and Tempf i l es
Cr eat i ng Dat af i l es
CREATE TABLESPACE
CREATE TEMPORARY TABLESPACE ge 8-9
ALTER TABLESPACE ... ADD DATAFILE
ALTER TABLESPACE ... ADD TEMPFILE ge 8-9
CREATE DATABASE
ALTER DATABASE ... CREATE DATAFILE
Enabl i ng and Di sabl i ng Aut omat i c Ex t ensi on f or a Dat af i l e
ALTER TABLESPACE users
ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;
Manual l y Resi zi ng a Dat af i l e
-- if there is space in the datafile
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' RESIZE 100M;
Bringing Datafiles Online or Taking Offline in ARCHIVELOG Mode
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;
Br i ngi ng Dat af i l es Onl i ne or Tak i ng Of f l i ne i n ARCHI VELOG Mode
ALTER DATABASE DATAFILE ... {ONLINE|OFFLINE}
-- all datafiles will be affected in the following code
-- this is different from ALTER TABLESPACE...ONLINE|OFFLINE which controls
tablespace availability
ALTER TABLESPACE ... DATAFILE {ONLINE|OFFLINE}
ALTER TABLESPACE ... TEMPFILE {ONLINE|OFFLINE}
Tak i ng Dat af i l es Of f l i ne i n NOARCHI VELOG Mode
Use it when you want t o drop t he dat afile.
-- datafile cannot be brought ONLINE again
ALTER DATABASE DATAFILE ... OFFLINE FOR DROP;
-- the datafile MUST then be dropped
-- for dictionary managed tablespace
ALTER TABLESPACE ... DROP DATAFILE
DROP TABLESPACE ... INCLUDING CONTENTS AND DATAFILES
Renami ng and Rel ocat i ng Dat af i l es i n a Si ngl e Tabl espace
ALTER TABLESPACE users OFFLINE NORMAL;
Copy the datafiles to their new locations and rename them using the operating
system.
ALTER TABLESPACE users RENAME DATAFILE
'/u02/oracle/rbdb1/user1.dbf',
'/u02/oracle/rbdb1/user2.dbf'
TO
'/u02/oracle/rbdb1/users01.dbf',
'/u02/oracle/rbdb1/users02.dbf';
-- for system, default temporary, or undo
Page 97 Oracle DBA Code Examples

-- ALTER TABLESPACE cannot be used because you cannot take them OFFLINE
mount the database
ALTER DATABASE RENAME FILE '/u02/oracle/rbdb1/sort01.dbf',
'/u02/oracle/rbdb1/user3.dbf'
TO '/u02/oracle/rbdb1/temp01.dbf', '/u02/oracle/rbdb1/users03.dbf;
Back up the database.
Dr oppi ng Dat af i l es
The following rest rict ions apply:
o The dat abase must be open.
o The dat afile must be empt y, ot herwise use drop t he t ablespace.
o You cannot drop dat afiles in a read-only t ablespace.
o You cannot drop dat afiles in t he SYSTEM t ablespace.
o I f a dat afile in a locally managed t ablespace is offline, it cannot be dropped.
ALTER TABLESPACE example DROP DATAFILE ...
ALTER TABLESPACE lmtemp DROP TEMPFILE
ALTER DATABASE TEMPFILE .. DROP INCLUDING DATAFILES
Copy i ng a Fi l e on a Local Fi l e Syst em
The copied file must meet t he following requirement s:
o The size must be a mult iple of 512 byt es.
o The size must be less t han or equal t o t wo t erabyt es.
Be aware not t o coy a file t hat is being used by a process.
I f you are copying a dat abase dat afile, make it READ ONLY before you st art t o copy.
CREATE DIRECTORY SOURCE_DIR AS '/usr/admin/source';
CREATE DIRECTORY DEST_DIR AS '/usr/admin/destination';
GRANT READ ON DIRECTORY source_dir TO strmadmin;
GRANT WRITE ON DIRECTORY dest_dir TO strmadmin;
CONNECT strmadmin/strmadminpw
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => 'SOURCE_DIR',
source_file_name => 'db1.dat',
destination_directory_object => 'DEST_DIR',
destination_file_name => 'db1_copy.dat');
END;
Tr ansf er r i ng a Fi l e t o a Di f f er ent Dat abase
I n order t o t ransfer a file t he ot her way around, you must replace t he PUT_FI LE
procedure wit h t he GET_FI LE procedure.
I f you are copying a dat abase dat afile, make it READ ONLY before you st art t o copy.
You can monit or copying progress using V$SESSI ON_LONGOPS view.
CREATE DATABASE LINK ODB
CONNECT TO system IDENTIFIED BY system_passwd USING 'prod1';

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
SOURCE_DIRECTORY_OBJECT => 'SOURCE_DIR',
SOURCE_FILE_NAME => 'mydata1.dbf',
DESTINATION_DIRECTORY_OBJECT => 'DEST_DIR',
Page 98 Oracle DBA Code Examples

DESTINATION_FILE_NAME => 'mydata2.dbf'
DESTINATION_DATABASE => 'ODB.ACME.COM');
END;
/
BEGIN
DBMS_FILE_TRANSFER.GET_FILE(
SOURCE_DIRECTORY_OBJECT => 'SOURCE_DIR',
SOURCE_FILE_NAME => 'TEST01.DBF',
SOURCE_DATABASE => 'ODB.ACME.COM',
DESTINATION_DIRECTORY_OBJECT => 'DEST_DIR',
DESTINATION_FILE_NAME => 'TEST01.DBF');
END;
/
Dumpi ng a Dat a Bl ock
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 3281;

-- To dump a number of consecutive blocks::
ALTER SYSTEM DUMP DATAFILE 5 BLOCK MIN 42 BLOCK MAX 50;

select value from v$parameter where name='user_dump_dest';
/u01/app/oracle/admin/pasu/udump/pasu_ora_29673.trc



/* to dump index blocks */
-- get object id of the index:
SELECT object_id FROM dba_objects WHERE object_name = 'MYINDEX';

-- do a treedump of the index:
ALTER SESSION SET EVENTS 'immediate trace name treedump level 106315';

index height
distinct index blocks in the lower level |
RBA block at position zero | |
| | | |
branch: 01c3588a 29579402 (0: nrow: 222, level: 1)


number of entries
lead block number (starts from -1) | non-deleted entries
| | |
leaf: 01c3588b 29579403 (-1: nrow: 485 rrow: 485)
leaf: 01c3588c 29579404 (0: nrow: 479 rrow: 479)
leaf: 01c3588d 29579405 (1: nrow: 479 rrow: 479)
leaf: 01c3588e 29579406 (2: nrow: 479 rrow: 479)


-- define the RBA of the block to dump then
-- get its file# and block#:
SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(223456765),
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(223456765)
FROM dual;


-- dump the header block
ALTER SYSTEM DUMP DATAFILE 7 BLOCK 328745;


-- also, you can find the root block ( it is the block following the header bloc):
SELECT header_file, header_block+1 FROM dba_segments WHERE segment_name='MYINDEX';
Page 99 Oracle DBA Code Examples

Managi ng Undo Tabl espaces
Obt ai ni ng I nf or mat i on on Undo
SHOW PARAMETER UNDO
NAME TYPE VALUE
----------------- ------- -------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS01

-- undo usage in 10-min-intervals for last 7 days
select TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,
UNDOBLKS UNDO_BLOCKS,
MAXQUERYLEN MAX_QUERY_LENGTH_INSEC,
MAXQUERYID LONGEST_QUERY_ID,
TXNCOUNT TOTAL_TRANSACTIONS,
MAXCONCURRENCY
from V$UNDOSTAT
order by BEGIN_TIME DESC

-- stats history of V$UNDOSTAT
select
TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,
INSTANCE_NUMBER,SNAP_ID,
UNDOBLKS,
TXNCOUNT TOTAL_TRANSACTIONS,
MAXQUERYLEN MAX_QUERY_LENGTH_INSEC,
MAXQUERYSQLID,
MAXCONCURRENCY,
SSOLDERRCNT ORA01555_CNT,
NOSPACEERRCNT NOSPACE_CNT,
ACTIVEBLKS,UNEXPIREDBLKS
from DBA_HIST_UNDOSTAT

-- undo segments
select SEGMENT_NAME,OWNER,STATUS from DBA_ROLLBACK_SEGS;

-- undo extents
select
e.SEGMENT_NAME,
e.TABLESPACE_NAME,
e.EXTENT_ID,
e.FILE_ID,
e.BLOCK_ID,
e.BYTES/1024 SIZE_KB,
e.RELATIVE_FNO,
e.STATUS -- ACTIVE EXPIRED UNEXPIRED
from DBA_UNDO_EXTENTS e
order by e.STATUS ASC

-- undo sizes by STATUS
select
e.TABLESPACE_NAME,
e.STATUS,
to_char(sum(e.BYTES/1024),'999,999,999,999') SIZE_KB
from DBA_UNDO_EXTENTS e
group by e.TABLESPACE_NAME, e.STATUS
order by e.STATUS

-- undo sizes consumed by active transactions by username
SELECT s.username, sum(t.used_ublk) used_undo_blocks
Page 100 Oracle DBA Code Examples

from v$session s, v$transaction t
where s.saddr = t.ses_addr
and t.status='ACTIVE'
group by s.username
order by s.username
Enabl i ng Aut omat i c Undo Management
select value from v$parameter where upper(name)='UNDO_MANAGEMENT';
alter system set UNDO_MANAGEMENT=AUTO scope=spfile ;
Cr eat i ng an Undo Tabl espace
CREATE UNDO TABLESPACE undotbs2
DATAFILE '/u01/oracle/rbdb1/undo0201.dbf' SIZE 200M REUSE AUTOEXTEND ON;
Set t i ng St ar t up Undo Tabl espace
select value from v$parameter where upper(name)='UNDO_TABLESPACE';
-- if there are multiple undo tablespaces
-- after all active transactions have committed, the undo tablespace automatically
-- goes from thePENDING OFFLINE mode to the OFFLINE mode.
alter system set UNDO_TABLESPACE='UNDOTBS1';
-- the following switches out current undo tablespace to the next available one
alter system set UNDO_TABLESPACE = '';
Tuni ng Undo Ret ent i on
I f t he undo t ablespace is configured wit h t he AUTOEXTEND opt ion, undo ret ent ion t uning
is slight ly different . I n t his case, t he dat abase t unes t he undo ret ent ion period t o be
slight ly longer t han t he longest - running query on t he syst em at t hat t ime.
For a fixed size undo t ablespace, t he dat abase t unes t he undo ret ent ion period based on
85% of t he t ablespace size, or on t he warning alert t hreshold percent age for space used,
whichever is lower.
I f you want a fixed undo size, use t he Undo Advisor t o specify t he proper undo size for
your requirement . You should est imat e:
o The lengt h of your expect ed longest running query
o The longest int erval t hat you will require for flashback operat ions
-- determine the current retention period
select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,
to_char(end_time, 'DD-MON-RR HH24:MI') end_time,
tuned_undoretention
from v$undostat order by end_time;

-- maximum query time
SELECT round(MAX(maxquerylen)/60) Minutes FROM v$undostat;
Usi ng Undo Adv i sor
Use OEM or PL/ SQL
-- retreive available AWR snaps
select SNAP_ID, STARTUP_TIME, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME
from DBA_HIST_SNAPSHOT
order by SNAP_ID;

set serveroutput on
DECLARE
tid NUMBER; -- task ID
tname VARCHAR2(30); -- task name
oid NUMBER;
Page 101 Oracle DBA Code Examples

BEGIN
DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task');
DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, null, oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 52);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 56);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'INSTANCE', 1);
DBMS_ADVISOR.execute_task(tname);
DBMS_OUTPUT.PUT_LINE(tname);
end;
/

SELECT DBA_ADVISOR_RECOMMENDATIONS.GET_TASK_REPORT('TASKNAME')
FROM DUAL;
Set t i ng t he Undo Ret ent i on Per i od
You must set UNDO_RETENTI ON paramet er when:
o The undo t ablespace has t he AUTOEXTEND opt ion enabled
o You want t o set undo ret ent ion for LOBs
o You want ret ent ion guarant ee
select value from v$parameter where upper(name)='UNDO_RETENTION';
alter system set UNDO_RETENTION = 2400;
Enabl i ng Ret ent i on Guar ant ee
Enabling ret ent ion guarant ee can cause mult iple DML operat ions t o fail. Use wit h caut ion.
select RETENTION from DBA_TABLESPACES where TABLESPACE_NAME='UNDOTBS1';
create undo tablespace undotbs01 .. RETENTION GUARANTEE;
alter tablespace undotbs1 RETENTION GUARANTEE;
alter tablespace undotbs1 RETENTION NOGUARANTEE;
Dr oppi ng an Undo Tabl espace
DROP TABLESPACE undotbs_01;
Usi ng Or acl e Managed Fi l es ( OMF)
OMF dat afiles have t o be creat ed in one direct ory
set DB_CREATE_FILE_DEST, DB_CREATE_ONLINE_LOG_DEST_n, and DB_RECOVERY_FILE_DEST

-- when OMF enabled
CREATE TABLESPACE finance01;
ALTER TABLESPACE finance01 ADD DATAFILE 500M;
DROP TABLESPACE finance01; -- dbf auto deleted

-- create db with OMF
db_name=mydb
DB_CREATE_FILE_DEST = '/u01/app/oracle/oradata'
DB_RECOVERY_FILE_DEST_SIZE = 100M
DB_RECOVERY_FILE_DEST = '/u04/app/oracle/oradata'
LOG_ARCHIVE_DEST_1 = 'LOCATION = USE_DB_RECOVERY_FILE_DEST'
SQL> connect sys/sys_passwd as sysdba
Connected to an idle instance.
SQL> STARTUP NOMOUNT PFILE='initmydb.ora';
SQL> CREATE DATABASE mydb;

Page 102 Oracle DBA Code Examples

Managi ng Schema Obj ect s
Chachi ng Smal l Tabl es i n Memor y
ALTER TABLE hr.countries CACHE;
Cr eat i ng Vi r t ual Col umns
CREATE TABLE EMPLOYEES
( empno number PRIMARY KEY,
sal NUMBER (8,2) NOT NULL,
annual_sal AS (sal*12),
CONSTRAINT MaxAnSal CHECK (annual_sal BETWEEN 0 AND 2000000) );
SELECT TABLE_NAME, COLUMN_NAME, DATA_DEFAULT
FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME='EMPLOYEES' AND COLUMN_NAME='ANNUAL_SAL';

ALTER TABLE employees ADD (income AS (salary*commission_pct));
Cr eat i ng Par t i t i oned Tabl es
/* range partitions */
CREATE TABLE student_history
(student_id NUMBER(10),
degree VARCHAR2(3),
graduation_date DATE,
final_gpa NUMBER)
PARTITION BY RANGE (graduation_date)
(PARTITION p_1997 VALUES LESS THAN
(TO_DATE('01-JUN-1997','DD-MON-YYYY')) TABLESPACE ts1,
PARTITION p_1998 VALUES LESS THAN
(TO_DATE('01-JUN-1998','DD-MON-YYYY')) TABLESPACE ts2,
PARTITION p_1999 VALUES LESS THAN
(TO_DATE('01-JUN-1999','DD-MON-YYYY')) TABLESPACE ts3,
PARTITION p_other VALUES LESS THAN (maxvalue) TABLESPACE ts4);

CREATE TABLE sales_data
(ticket_no NUMBER,
sale_year INT NOT NULL,
sale_month INT NOT NULL,
sale_day INT NOT NULL)
PARTITION BY RANGE (sale_year, sale_month, sale_day)
(PARTITION sales_q1 VALUES LESS THAN (2008, 04, 01) TABLESPACE ts1,
PARTITION sales_q2 VALUES LESS THAN (2008, 07, 01) TABLESPACE ts2,
PARTITION sales_q3 VALUES LESS THAN (2008, 10, 01) TABLESPACE ts3,
PARTITION sales_q4 VALUES LESS THAN (2009, 01, 01) TABLESPACE ts4);

-----------------------------------------------------------------

/* Interval Partitioning */
-- You cant use a partitioning key that includes more than one column
-- system generated partions have names SYS_Pn
CREATE TABLE interval_sales
( prod_id NUMBER(6),
cust_id NUMBER,
time_id DATE,
..)
Page 103 Oracle DBA Code Examples

PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
STORE IN (ts5, ts6, ts7) -- optional
( PARTITION ts1 VALUES LESS THAN (TO_DATE('1-1-2006', 'DD-MM-YYYY')),
PARTITION ts2 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')),
PARTITION ts3 VALUES LESS THAN (TO_DATE('1-7-2008', 'DD-MM-YYYY')),
PARTITION ts4 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')) );

SELECT TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE
FROM Remote DBA_TAB_PARTITIONS
WHERE TABLE_NAME='POS_DATA'
ORDER BY PARTITION_NAME;

create table res (
res_id number not null,
res_date date,
hotel_id number(3),
guest_id number )
partition by range (res_id)
interval (100) store in (users)
( partition p1 values less than (101) );


-- selecting from the generated partition
-- classic method
select * from interval_sales partition for (SYS_P81);
-- the other method (expanded partition access syntax)
select * from
interval_sales partition for (to_date('15-AUG-2009','dd-mon-yyyy'));
alter table res truncate partition for (901);


-- a range partion can be converted to interval partition
alter table pos_data_range set INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'));

-- convert an interval partition to range partition
alter table pos_data_range set INTERVAL();

-- interval can be modified
alter table pos_data set INTERVAL(NUMTOYMINTERVAL(3, 'MONTH'));

-- round robin tablespaces can be modified
alter table pos_data set STORE IN(tablespace1, tablespace2, tablespace3);

-----------------------------------------------------------------

/* Hash Partitioning */
-- used when range distribution is not predictable and for high cardinality columns
-- Updates that would cause a record to move across partition boundaries are not allowed
CREATE TABLE sales_data
(ticket_no NUMBER,
sale_year INT NOT NULL,
sale_month INT NOT NULL,
sale_day INT NOT NULL )
PARTITION BY HASH (ticket_no)
PARTITIONS 4
STORE IN (ts1,ts2,ts3,ts4);

-----------------------------------------------------------------

/* List Partitioning */
CREATE TABLE sales_data
(ticket_no NUMBER,
sale_year INT NOT NULL,
Page 104 Oracle DBA Code Examples

sale_month INT NOT NULL,
sale_day INT NOT NULL,
destination_city CHAR(3),
start_city CHAR(3))
PARTITION BY LIST (start_city)
(PARTITION northeast_sales values ('NYC','BOS','PEN') TABLESPACE ts1,
PARTITION southwest_sales values ('DFW','ORL','HOU') TABLESPACE ts2,
PARTITION pacificwest_sales values('SAN','LOS','WAS') TABLESPACE ts3,
PARTITION southeast_sales values ('MIA','CHA','ATL') TABLESPACE ts4);

-----------------------------------------------------------------

/* Reference Partitioning */
-- put child table data into parent table partitions
-- You can use all partitioning strategies with reference partitioning, except interval
partitioning

CREATE TABLE orders
( order_id NUMBER(12),
order_date DATE,
order_mode VARCHAR2(8),
customer_id NUMBER(6),
order_status NUMBER(2),
order_total NUMBER(8,2),
sales_rep_id NUMBER(6),
promotion_id NUMBER(6),
CONSTRAINT orders_pk PRIMARY KEY(order_id))
PARTITION BY RANGE(order_date)
( PARTITION Q1_2005 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY')),
PARTITION Q2_2005 VALUES LESS THAN (TO_DATE('01-JUL-2005','DD-MON-YYYY')),
PARTITION Q3_2005 VALUES LESS THAN (TO_DATE('01-OCT-2005','DD-MON-YYYY')),
PARTITION Q4_2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY'))
);

CREATE TABLE order_items
( order_id NUMBER(12) NOT NULL,
line_item_id NUMBER(3) NOT NULL,
product_id NUMBER(6) NOT NULL,
unit_price NUMBER(8,2),
quantity NUMBER(8),
CONSTRAINT order_items_fk
FOREIGN KEY(order_id) REFERENCES orders(order_id)
)
PARTITION BY REFERENCE(order_items_fk);

-----------------------------------------------------------------

/* Virtual Column-Based Partitioning */
-- ENABLE ROW MOVEMENT clause ensures row migration among partitions when virtual column
value changes
CREATE TABLE sales
( prod_id NUMBER(6) NOT NULL,
cust_id NUMBER NOT NULL,
time_id DATE NOT NULL,
channel_id CHAR(1) NOT NULL,
promo_id NUMBER(6) NOT NULL,
quantity_sold NUMBER(3) NOT NULL,
amount_sold NUMBER(10,2) NOT NULL,
total_amount AS (quantity_sold * amount_sold)
)
PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY RANGE(total_amount)
SUBPARTITION TEMPLATE
( SUBPARTITION p_small VALUES LESS THAN (1000),
Page 105 Oracle DBA Code Examples

SUBPARTITION p_medium VALUES LESS THAN (5000),
SUBPARTITION p_large VALUES LESS THAN (10000),
SUBPARTITION p_extreme VALUES LESS THAN (MAXVALUE)
)
(PARTITION sales_before_2007 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy')))
ENABLE ROW MOVEMENT
PARALLEL;

-----------------------------------------------------------------

/* Virtual Column-Based Partitioning */
-- ENABLE ROW MOVEMENT clause ensures row migration among partitions when virtual column
value changes
CREATE TABLE sales
( prod_id NUMBER(6) NOT NULL,
cust_id NUMBER NOT NULL,
time_id DATE NOT NULL,
channel_id CHAR(1) NOT NULL,
promo_id NUMBER(6) NOT NULL,
quantity_sold NUMBER(4) NOT NULL,
amount_sold NUMBER(4) NOT NULL,
total_amounts AS (quantity_sold * amount_sold)
)
PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY RANGE(total_amounts)
SUBPARTITION TEMPLATE
( SUBPARTITION p_small VALUES LESS THAN (1000),
SUBPARTITION p_medium VALUES LESS THAN (5000),
SUBPARTITION p_large VALUES LESS THAN (10000),
SUBPARTITION p_extreme VALUES LESS THAN (MAXVALUE)
)
(PARTITION sales_before_2007 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy')))
ENABLE ROW MOVEMENT
PARALLEL;

-----------------------------------------------------------------

/* System Partitioning */
-- the application decided where to store the data
CREATE TABLE docs
( ID NUMBER,
Name VARCHAR2(255),
Desc VARCHAR2(1000))
PARTITION BY SYSTEM
( PARTITION docs_p1 TABLESPACE ts1,
PARTITION docs_p2 TABLESPACE ts2,
PARTITION docs_p3 TABLESPACE ts3,
PARTITION docs_p4 TABLESPACE ts4 );

-- PARTITION must be stated
INSERT INTO docs PARTITION (ts1)
VALUES (1, 'Oracle 11g New Features', 'New features in Oracle 11g Database.');
-- with DELETE command, PARTITION can be stated
DELETE FROM docs PARTITION (ts2) WHERE doc_id=1002;
DELETE FROM docs PARTITION (ts2);
-- PARTITION can be used in queries to target specific partitions
SELECT COUNT(*) FROM docs PARTITION (ts1)

-----------------------------------------------------------------

/* Range-Hash Partitioning */

CREATE TABLE scout_gear
(equipno NUMBER,equipname VARCHAR(32),price NUMBER)
Page 106 Oracle DBA Code Examples

PARTITION BY RANGE (equipno)
SUBPARTITION BY HASH(equipname)
SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4)
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (3000),
PARTITION p4 VALUES LESS THAN (MAXVALUE));

-----------------------------------------------------------------
/* Range-List Partitioning */

CREATE TABLE regional_sales
(ticket_no NUMBER,
sale_year INT NOT NULL,
sale_month INT NOT NULL,
sale_day DATE,
destination_city CHAR(3),
start_city CHAR(3))
PARTITION BY RANGE(sale_day)
SUBPARTITION BY LIST (start_city)
(PARTITION q1_2004 VALUES LESS THAN (TO_DATE('1-APR-2004','DD-MON-YYYY'))
TABLESPACE ts1
(SUBPARTITION q12004_northeast_sales VALUES ('NYC','BOS','PEN'),
SUBPARTITION q12004_southwest_sales VALUES ('DFW','ORL','HOU'),
SUBPARTITION q12004_pacificwest_sales VALUES ('SAN','LOS','WAS'),
SUBPARTITION q12004_southeast_sales VALUES ('MIA','CHA','ATL')
),
PARTITION q2_2004 VALUES LESS THAN (TO_DATE('1-JUL-2004','DD-MON-YYYY'))
TABLESPACE ts2
(SUBPARTITION q22004_northeast_sales VALUES ('NYC','BOS','PEN'),
SUBPARTITION q22004_southwest_sales VALUES ('DFW','ORL','HOU'),
SUBPARTITION q22004_pacificwest_sales VALUES ('SAN','LOS','WAS'),
SUBPARTITION q22004_southeast_sales VALUES ('MIA','CHA','ATL')
),
PARTITION q3_2004 VALUES LESS THAN (TO_DATE('1-OCT-2004','DD-MON-YYYY'))
TABLESPACE ts3
(SUBPARTITION q32004_northeast_sales VALUES ('NYC','BOS','PEN'),
SUBPARTITION q32004_southwest_sales VALUES ('DFW','ORL','HOU'),
SUBPARTITION q32004_pacificwest_sales VALUES ('SAN','LOS','WAS'),
SUBPARTITION q32004_southeast_sales VALUES ('MIA','CHA','ATL')
),
PARTITION q4_2004 VALUES LESS THAN (TO_DATE('1-JAN-2005','DD-MON-YYYY'))
TABLESPACE ts4
(SUBPARTITION q42004_northeast_sales VALUES ('NYC','BOS','PEN'),
SUBPARTITION q42004_southwest_sales VALUES ('DFW','ORL','HOU'),
SUBPARTITION q42004_pacificwest_sales VALUES ('SAN','LOS','WAS'),
SUBPARTITION q42004_southeast_sales VALUES ('MIA','CHA','ATL')
)
);
-----------------------------------------------------------------

/* Interval-Range Partitioned Tables or Range-Range */

CREATE TABLE sales
( prod_id NUMBER(6),
cust_id NUMBER,
time_id DATE,
channel_id CHAR(1),
promo_id NUMBER(6),
quantity_sold NUMBER(3),
amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
SUBPARTITION BY RANGE(amount_sold)
Page 107 Oracle DBA Code Examples

SUBPARTITION TEMPLATE
( SUBPARTITION p_low VALUES LESS THAN (1000),
SUBPARTITION p_medium VALUES LESS THAN (4000),
SUBPARTITION p_high VALUES LESS THAN (8000),
SUBPARTITION p_ultimate VALUES LESS THAN (maxvalue))
( PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy')));

-----------------------------------------------------------------

/* Interval-List Partitioned Tables */

CREATE TABLE sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
SUBPARTITION BY LIST (channel_id)
SUBPARTITION TEMPLATE
(SUBPARTITION p_catalog VALUES ('C')
, SUBPARTITION p_internet VALUES ('I')
, SUBPARTITION p_partners VALUES ('P')
, SUBPARTITION p_direct_sales VALUES ('S')
, SUBPARTITION p_tele_sales VALUES ('T'))
( PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy')));
Par t i t i on Mai nt enance Oper at i ons
Range and list part it ions cant be coalesced.
Hash part it ions cant be dropped, split , or merged.
Only list part it ions allow t he modificat ion of part it ions by adding and dropping t he part it ion
values.
-- Adding Partitions
ALTER TABLE ticket_sales
ADD PARTITION sales_quarter5 VALUES LESS THAN
(TO_DATE('1-APR-2005','DD-MON-YYYY')) TABLESPACE ts5;

-- Splitting a Partition
ALTER TABLE ticket_sales
SPLIT PARTITION ticket_sales01 AT (2000) INTO
(PARTITION ticket_sales01A, ticket_sales01B);

-- Merging Partitions
ALTER TABLE ticket_sales
MERGE PARTITIONS ticket_sales01, ticket_sales02 INTO PARTITION ticket_sales02;

-- Renaming Partitions
ALTER TABLE RENAME PARTITION fight_sales01 TO quarterly_sales01;

-- Exchanging Partitions
-- convert a regular nonpartitioned table into a partition of a partitioned table
-- it only renames the source and destination
ALTER TABLE ticket_sales
EXCHANGE PARTITION ticket_sales02 WITH ticket_sales03;

-- Dropping Partitions
Page 108 Oracle DBA Code Examples

-- use UPDATE GLOBAL INDEXES keyword if data exists in the partition
ALTER TABLE ticket_sales DROP PARTITION ticket_sales01;

-- Coalescing Partitions
-- applied on hash-partitioned and list-partitioned
-- to reduce number of partitions
ALTER TABLE ticket_sales COALESCE PARTITION;
Cr eat i ng Mul t i pl e Tabl es and Vi ew s i n a Si ngl e Oper at i on
I f one included st at ement fails, non of t he ot her st at ement s will be execut ed.
CREATE SCHEMA AUTHORIZATION scott
CREATE TABLE dept (
deptno NUMBER(3,0) PRIMARY KEY,
dname VARCHAR2(15),
loc VARCHAR2(25))
CREATE TABLE emp (
empno NUMBER(5,0) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(5,0),
hiredate DATE DEFAULT (sysdate),
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(3,0) NOT NULL
CONSTRAINT dept_fkey REFERENCES dept)
CREATE VIEW sales_staff AS
SELECT empno, ename, sal, comm
FROM emp
WHERE deptno = 30
WITH CHECK OPTION CONSTRAINT sales_staff_cnst
GRANT SELECT ON sales_staff TO human_resources;
Col l ect i ng Obj ect St at i st i cs
Col l ect i ng I ndex St at i st i cs
granularit y paramet er t akes:
o ' DEFAULT' - gat hers global and part it ion- level st at ist ics
o ' SUBPARTI TI ON' - gat hers subpart it ion- level st at ist ics
o ' PARTI TI ON' - gat hers part it ion- level st at ist ics
o ' GLOBAL' - gat hers global st at ist ics
o ' ALL' - gat hers all ( subpart it ion, part it ion, and global)
begin
DBMS_STATS.GATHER_INDEX_STATS (
ownname =>'HR',
indname =>'EMP_DEPARTMENT_IX',
partname =>'', -- DEFAULT NULL
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -- 0.1 to 100 DEFAULT
to_estimate_percent_type (GET_PARAM('ESTIMATE_PERCENT'))
stattab => null, -- DEFAULT NULL
statid => null, -- DEFAULT NULL
statown => null, -- DEFAULT NULL
degree => null, -- parallelism NUMBER DEFAULT
to_degree_type(get_param('DEGREE'))
granularity => 'ALL', -- DEFAULT GET_PARAM('GRANULARITY')
Page 109 Oracle DBA Code Examples

no_invalidate => false, -- BOOLEAN DEFAULT to_no_invalidate_type
(GET_PARAM('NO_INVALIDATE')),
force => false -- BOOLEAN DEFAULT FALSE
);
end;
/
Col l ect i ng Tabl e St at i st i cs
met hod_opt Accept s:
o FOR ALL [ I NDEXED | HI DDEN] COLUMNS [ size_clause]
o FOR COLUMNS [ size clause] column| at t ribut e [ size_clause] [ , column| at t ribut e
[ size_clause] . . . ]
o size_clause is defined as size_clause : = SI ZE { int eger | REPEAT | AUTO | SKEWONLY}
- int eger : Number of hist ogram bucket s. Must be in t he range [ 1,254] .
- REPEAT : Collect s hist ograms only on t he columns t hat already have hist ograms.
- AUTO : Oracle det ermines t he columns t o collect hist ograms based on dat a dist ribut ion and
t he workload of t he columns.
- SKEWONLY : Oracle det ermines t he columns t o collect hist ograms based on t he dat a
dist ribut ion of t he columns.
The default is FOR ALL COLUMNS SI ZE AUTO
granularit y t akes: ' ALL', ' AUTO' , ' DEFAULT' , ' GLOBAL' , ' GLOBAL AND PARTI TI ON' ,
' PARTI TI ON', ' SUBPARTI TI ON'
begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname =>'HR',
tabname =>'NAMES',
partname =>'',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -- NUMBER DEFAULT
to_estimate_percent_type(get_param('ESTIMATE_PERCENT'))
block_sample => false, -- DEFAULT FALSE
method_opt =>'FOR ALL COLUMNS SIZE AUTO' , -- DEFAULT
get_param('METHOD_OPT')
degree =>1, -- DEFAULT to_degree_type(get_param('DEGREE'))
granularity =>'ALL' , -- DEFAULT GET_PARAM('GRANULARITY')
cascade =>true, -- to_cascade_type(get_param('CASCADE'))
stattab =>'',
statid =>'',
statown =>'',
no_invalidate => false, -- DEFAULT to_no_invalidate_type
(get_param('NO_INVALIDATE'))
force => false
);
end;
/
Col l ect i ng Schema St at i st i cs
met hod_opt Accept s:
o FOR ALL [ I NDEXED | HI DDEN] COLUMNS [ size_clause]
o FOR COLUMNS [ size clause] column| at t ribut e [ size_clause] [ , column| at t ribut e
[ size_clause] . . . ]
o size_clause is defined as size_clause : = SI ZE { int eger | REPEAT | AUTO | SKEWONLY}
Page 110 Oracle DBA Code Examples

- int eger : Number of hist ogram bucket s. Must be in t he range [ 1,254] .
- REPEAT : Collect s hist ograms only on t he columns t hat already have hist ograms.
- AUTO : Oracle det ermines t he columns t o collect hist ograms based on dat a dist ribut ion and
t he workload of t he columns.
- SKEWONLY : Oracle det ermines t he columns t o collect hist ograms based on t he dat a
dist ribut ion of t he columns.
The default is FOR ALL COLUMNS SI ZE AUTO
granularit y t akes: ' ALL', ' AUTO' , ' DEFAULT' , ' GLOBAL' , ' GLOBAL AND PARTI TI ON' ,
' PARTI TI ON', ' SUBPARTI TI ON'
opt ions t akes : GATHER, GATHER AUTO, GATHER STALE, GATHER EMPTY. I t also t akes
LI ST STALE, LI ST EMPTY, LI ST AUTO ( you should use an overloaded procedure different
from t he example above) .
Not e: GET_PARAM( ' .. ' ) is used t ill version 10g. I n 11g, DBMS_STATS. GET_PREFS should be
used.
begin
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname =>'HR',
estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, --0.1-100 DEFAULT
to_estimate_percent_type(get_param('ESTIMATE_PERCENT'))
block_sample =>FALSE,
method_opt =>'FOR ALL COLUMNS SIZE AUTO', -- DEFAULT get_param('METHOD_OPT')
degree =>NULL, -- to_degree_type(get_param('DEGREE')),
granularity =>'ALL', -- DEFAULT GET_PARAM('GRANULARITY'),
cascade =>TRUE, -- DEFAULT to_cascade_type(get_param('CASCADE')),
stattab =>'', -- DEFAULT NULL,
statid =>'', -- DEFAULT NULL,
options =>'GATHER', -- DEFAULT 'GATHER',
statown =>'', -- DEFAULT NULL,
no_invalidate =>false,-- DEFAULT to_no_invalidate_type
(get_param('NO_INVALIDATE')
force => FALSE);
end;
/


begin
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname =>'HR',
estimate_percent =>NULL,
method_opt =>'FOR ALL COLUMNS SIZE AUTO',
degree => DBMS_STATS.DEFAULT_DEGREE,
granularity =>'ALL',
cascade =>TRUE,
options =>'GATHER AUTO');
end;
/
Val i dat i ng Tabl es, I ndexes, Cl ust er s, and Mat er i al i zed Vi ew s
Validat ing an obj ect means verifying t he int egrit y of t he st ruct ure of it .
I f a corrupt ion is report ed, re- creat e t he obj ect .
Page 111 Oracle DBA Code Examples

ANALYZE TABLE emp VALIDATE STRUCTURE;
ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE ONLINE;
ANALYZE INDEX loc_country_ix VALIDATE STRUCTURE;
Li st i ng Chai ned and Mi gr at ed Row s of Tabl es and Cl ust er s
-- set up
-- following script creates CHAINED_ROWS table
@ $ORACLE_HOME$\RDBMS\ADMIN\utlchain.sql

-- handling chained and migrated rows
-- 1
ANALYZE TABLE order_hist LIST CHAINED ROWS;
ANALYZE CLUSTER emp_dept LIST CHAINED ROWS INTO CHAINED_ROWS;
-- 2
SELECT * FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST';
-- 3 if you have too many rows
-- intermediate table to hold the chained rows
CREATE TABLE int_order_hist
AS SELECT *
FROM order_hist
WHERE ROWID IN
(SELECT HEAD_ROWID
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST');
-- 4
DELETE FROM order_hist
WHERE ROWID IN
(SELECT HEAD_ROWID
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST');
-- 5
INSERT INTO order_hist
SELECT * FROM int_order_hist;
-- 6
DROP TABLE int_order_history;
-- 7
DELETE FROM CHAINED_ROWS WHERE TABLE_NAME = 'ORDER_HIST';
-- 8 Use the ANALYZE statement again, and query the output table. Any rows that
appear in the output table are chained. You can eliminate chained rows only by
increasing your data block size.
Tr uncat i ng Tabl es or Cl ust er s
TRUNCATE TABLE emp_dept DROP STORAGE; -- default
TRUNCATE TABLE emp_dept REUSE STORAGE;
Enabl i ng and Di sabl i ng Tr i gger s
select table_name, trigger_name from user_triggers where status='DISABLED';
ALTER TRIGGER reorder ENABLE;
Page 112 Oracle DBA Code Examples

ALTER TRIGGER reorder DISABLE;
ALTER TABLE inventory ENABLE ALL TRIGGERS;
ALTER TABLE inventory DISABLE ALL TRIGGERS;
Managi ng I nt egr i t y Const r ai nt s
Set t i ng Const r ai nt St at es and Def er abi l i t y
Const raint st at es:
ENABLE, VALI DATE : insert s are checked, exist ing dat a in t he t able is checked
ENABLE, NOVALI DATE ( fast ) : insert s are checked, exist ing dat a in t he t able is NOT checked
DI SABLE, VALI DATE : DML on const rained columns disallowed
DI SABLE, NOVALI DATE : insert s are NOT checked, exist ing dat a in t he t able is NOT checked
Const raint Checking ( Deferabilit y)
Nondeferred: enforced at t he end of every DML st at ement .
Deferred : enforced when a t ransact ion is commit t ed.
see next sect ion for alt ering a const raint
CONSTRAINT ... [[NOT] DEFERRABLE INITIALLY [DEFERRED|IMMEDIATE]] [ENABLE

|DISABLE]
[VALIDATE|NOVALIDATE]
ALTER SESSION SET CONSTRAINT[S] = {IMMEDIATE|DEFERRED|DEFAULT}

-- takes effect in TRANSACTION level
-- the trans is rolled back, if COMMIT fails
-- you can use SET CONSTRAINTS ALL IMMEDIATE to see whether COMMIT will fail before it
-- really commits
SET CONSTRAINT | CONSTRAINTS {constraint |ALL } {IMMEDIATE|DEFERRED}

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE,STATUS,DEFERRABLE,DEFERRED,VALIDATED
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_NAME='';
ALTER TABLE emp ADD PRIMARY KEY (empno) DISABLE;
ALTER TABLE countries ADD (UNIQUE(country_name) ENABLE NOVALIDATE);
ALTER TABLE myTable ADD CONSTRAINT uq UNIQUE (id) DEFERRABLE INITIALLY
DEFERRED;
-- takes effect in TRANSACTION level
-- not deferrable constraints are NOT affected
set constraint job_fk deferred;
set constraint job_fk immediate;
set constraints all deferred;
Modi f yi ng, Renami ng, or Dr oppi ng Ex i st i ng I nt egr i t y Const r ai nt s
ALTER TABLE dept DISABLE CONSTRAINT dname_ukey;
ALTER TABLE dept DISABLE PRIMARY KEY KEEP INDEX, DISABLE UNIQUE (dname, loc)
KEEP INDEX;
-- all FKs are also disabled
ALTER TABLE dept DISABLE PRIMARY KEY CASCADE;
ALTER TABLE dept ENABLE NOVALIDATE CONSTRAINT dname_ukey;
ALTER TABLE dept ENABLE NOVALIDATE PRIMARY KEY, ENABLE NOVALIDATE UNIQUE
(dname, loc);
ALTER TABLE dept MODIFY CONSTRAINT dname_key VALIDATE;
ALTER TABLE dept MODIFY PRIMARY KEY ENABLE NOVALIDATE;

ALTER TABLE dept RENAME CONSTRAINT dname_ukey TO dname_unikey;
ALTER TABLE dept DROP UNIQUE (dname, loc);
Page 113 Oracle DBA Code Examples

ALTER TABLE emp DROP PRIMARY KEY KEEP INDEX, DROP CONSTRAINT dept_fkey;
Repor t i ng Const r ai nt Ex cept i ons
The t arget is t o put all t he rows t hat voilat e sepcific int egrit y cont raint s in t he
EXCEPTI ONS t able.
1. create the EXCEPTIONS table
ORACLE_HOME\RDBMS\ADMIN\utlexcpt.sql
2.
ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO EXCEPTIONS;
3.
SELECT * FROM EXCEPTIONS;
4. join the master table with the EXCEPTIONS
SELECT deptno, dname, loc FROM dept, EXCEPTIONS
WHERE EXCEPTIONS.constraint = 'SYS_C00610'
AND dept.rowid = EXCEPTIONS.row_id;
5. fix the incorrect data
6.
DELETE FROM EXCEPTIONS WHERE constraint = 'SYS_C00610';
Obt ai ni ng I nf or mat i on on Const r ai nt s
select * from USER_CONSTRAINTS;
select * from USER_CONS_COLUMNS;
Renami ng Schema Obj ect s
rename mytable to mytable2
Managi ng Obj ect Dependenci es
Manual l y Recompi l i ng Vi ew s Pr ocedur es and Pack ages
ALTER VIEW emp_dept COMPILE;
ALTER PROCEDURE update_salary COMPILE;
ALTER PACKAGE acct_mgmt COMPILE BODY;
ALTER PACKAGE acct_mgmt COMPILE PACKAGE;
Sw i t chi ng t o a Di f f er ent Schema
CONNECT scott/tiger
ALTER SESSION SET CURRENT_SCHEMA = joe;
-- the following is equivalent to SELECT * FROM joe.emp
SELECT * FROM emp;
Usi ng DBMS_METADATA t o Di spl ay I nf or mat i on About Schema Obj ect s
SET LONG 9999
-- storage clauses are not to be returned
EXECUTE
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',f
alse);
Page 114 Oracle DBA Code Examples

-- fetch the DDL of all tables (relational and objects)
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_ALL_TABLES u
WHERE u.nested='NO'
AND (u.iot_type is null or u.iot_type='IOT');
-- return the parameter value to its default
EXECUTE
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');
Speci f yi ng St or age Par amet er s at Obj ect Cr eat i on
Obj ect s include: Tables, Clust ers, I ndexes, LOBs, Varrays, Nest ed Tables, MViews
CREATE TABLE MYTABLE(COL1 NUMBER, COL2 VARCHAR2(100))
STORAGE (INITIAL 10M BUFFER_POOL DEFAULT); -- MINEXTENTS

SELECT EXTENT_ID, BYTES/1024 KB
FROM DBA_EXTENTS
WHERE SEGMENT_TYPE='TABLE' AND SEGMENT_NAME='MYTABLE';

CREATE TABLE ...
( .., CONSTRAINT UQ)CON UNIQUE (PK_id)
USING INDEX
TABLESPACE MYTBS
STORAGE (INITIAL 8K ) );

CREATE MATERIALIZED VIEW ..
TABLESPACE MYTBS
STORAGE (INITIAL 50K)
USING INDEX STORAGE (INITIAL 25K)
...

Managi ng Resumabl e Space Al l ocat i on
Enabl i ng Resumabl e Space Al l ocat i on
-- the user should have this priv
grant RESUMABLE to hr;

select value from v$parameter where upper(name)='RESUMABLE_TIMEOUT';

-- to enable resumable space allocation system wide
-- 0 to disable
alter system set RESUMABLE_TIMEOUT = 3600;
-- or in session level
alter session set RESUMABLE_TIMEOUT = 3600;

-- beside the RESUMABLE_TIMEOUT parameter
ALTER SESSION ENABLE RESUMABLE;
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600; -- default 7200
-- 4340 is SID. NULL for current session
EXEC DBMS_RESUMABLE.set_session_timeout(4340,18000);
ALTER SESSION DISABLE RESUMABLE;

Page 115 Oracle DBA Code Examples

-- naming resumable statements
-- default is 'User username(userid), Session sessionid, Instance instanceid'
-- name is used in the DBA_RESUMABLE and USER_RESUMABLE views
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600 NAME 'insert into table';
Det ect i ng Suspended St at ement s
The AFTER SUSPEND Syst em Event and Trigger is execut ed aft er a SQL st at ement has
been suspended.
-- source: Oracle Documentation
CREATE OR REPLACE TRIGGER resumable_default
AFTER SUSPEND ON DATABASE
DECLARE
/* declare transaction in this trigger is autonomous */
/* this is not required because transactions within a trigger are always
autonomous */
PRAGMA AUTONOMOUS_TRANSACTION;
cur_sid NUMBER;
cur_inst NUMBER;
errno NUMBER;
err_type VARCHAR2;
object_owner VARCHAR2;
object_type VARCHAR2;
table_space_name VARCHAR2;
object_name VARCHAR2;
sub_object_name VARCHAR2;
error_txt VARCHAR2;
msg_body VARCHAR2;
ret_value BOOLEAN;
mail_conn UTL_SMTP.CONNECTION;
BEGIN
-- Get session ID
SELECT DISTINCT(SID) INTO cur_SID FROM V$MYSTAT;

-- Get instance number
cur_inst := userenv('instance');

-- Get space error information
ret_value :=
DBMS_RESUMABLE.SPACE_ERROR_INFO(err_type,object_type,object_owner,table_space
_name,object_name, sub_object_name);

/*
-- If the error is related to undo segments, log error, send email
-- to DBA, and abort the statement. Otherwise, set timeout to 8 hours.
--
-- sys.rbs_error is a table which is to be
-- created by a DBA manually and defined as
-- (sql_text VARCHAR2(1000), error_msg VARCHAR2(4000), suspend_time DATE)
*/

IF OBJECT_TYPE = 'UNDO SEGMENT' THEN
/* LOG ERROR */
INSERT INTO sys.rbs_error (
SELECT SQL_TEXT, ERROR_MSG, SUSPEND_TIME
FROM DBA_RESUMABLE
WHERE SESSION_ID = cur_sid AND INSTANCE_ID = cur_inst);

Page 116 Oracle DBA Code Examples

SELECT ERROR_MSG INTO error_txt FROM DBMS_RESUMABLE
WHERE SESSION_ID = cur_sid and INSTANCE_ID = cur_inst;

-- Send email to receipient via UTL_SMTP package
msg_body:='Subject: Space Error Occurred
Space limit reached for undo segment ' || object_name ||
on ' || TO_CHAR(SYSDATE, 'Month dd, YYYY, HH:MIam') ||
'. Error message was ' || error_txt;
mail_conn := UTL_SMTP.OPEN_CONNECTION('localhost', 25);
UTL_SMTP.HELO(mail_conn, 'localhost');
UTL_SMTP.MAIL(mail_conn, 'sender@localhost');
UTL_SMTP.RCPT(mail_conn, 'recipient@localhost');
UTL_SMTP.DATA(mail_conn, msg_body);
UTL_SMTP.QUIT(mail_conn);
-- Abort the statement
DBMS_RESUMABLE.ABORT(cur_sid);
ELSE
-- Set timeout to 8 hours
DBMS_RESUMABLE.SET_TIMEOUT(28800);
END IF;

/* commit autonomous transaction */
COMMIT;
END;
/
Obt ai ni ng I nf or mat i on about Suspended St at ement s
SELECT USER_ID,SESSION_ID,INSTANCE_ID,
COORD_INSTANCE_ID,COORD_SESSION_ID,STATUS,
TIMEOUT,START_TIME,SUSPEND_TIME,
RESUME_TIME,NAME,SQL_TEXT,
ERROR_NUMBER,ERROR_PARAMETER1,ERROR_PARAMETER2,
ERROR_PARAMETER3,ERROR_PARAMETER4,ERROR_PARAMETER5, ERROR_MSG
FROM DBA_RESUMABLE;

SELECT SID,SEQ#,EVENT,
P1TEXT,P1,P1RAW,
P2TEXT,P2,P2RAW,
P3TEXT,P3,P3RAW,
WAIT_CLASS_ID,WAIT_CLASS#,WAIT_CLASS,
WAIT_TIME,SECONDS_IN_WAIT,STATE
FROM V$SESSION_WAIT
WHERE EVENT LIKE '%statement suspended%'

exec DBMS_RESUMABLE.ABORT(159);
select DBMS_RESUMABLE.GET_SESSION_TIMEOUT(159) from dual;
exec DBMS_RESUMABLE.SET_SESSION_TIMEOUT(sessionID=>159,timeout=>8000);
Recl ai mi ng Wast ed Space
Di spl ayi ng I nf or mat i on About Space Usage f or Schema Obj ect s
/* Using PL SQL */
SET SERVEROUTPUT ON
declare
-- IN vars
v_segment_owner VARCHAR2(30) :='HR';
Page 117 Oracle DBA Code Examples

v_segment_name VARCHAR2(30) :='NAMES';
v_segment_type VARCHAR2(30) :='TABLE'; -- TABLE, TABLE PARTITION, TABLE
SUBPARTITION, INDEX, INDEX PARTITION, INDEX SUBPARTITION, CLUSTER, LOB, LOB
PARTITION, LOB SUBPARTITION
v_partition_name VARCHAR2(30) :=NULL;
-- OUT vars
v_total_blocks NUMBER;
v_total_bytes NUMBER;
v_unused_blocks NUMBER;
v_unused_bytes NUMBER;
v_last_used_extent_file_id NUMBER; -- file id of last Extend containing data
v_filename varchar2(300);
v_last_used_extent_block_id NUMBER;
v_last_used_block NUMBER;
begin
DBMS_SPACE.UNUSED_SPACE (
segment_owner => v_segment_owner,
segment_name => v_segment_name ,
segment_type => v_segment_type,
total_blocks => v_total_blocks ,
total_bytes => v_total_bytes ,
unused_blocks => v_unused_blocks,
unused_bytes => v_unused_bytes ,
last_used_extent_file_id => v_last_used_extent_file_id ,
last_used_extent_block_id =>v_last_used_extent_block_id ,
last_used_block => v_last_used_block ,
partition_name => v_partition_name );

if v_last_used_extent_file_id is not null then
select file_name into v_filename from dba_data_files where file_id =
v_last_used_extent_file_id ;
end if;

DBMS_OUTPUT.PUT_LINE('Total Blocks : '||v_total_blocks);
DBMS_OUTPUT.PUT_LINE('Total MBytes : '||to_char(v_total_bytes/1024/1024));
DBMS_OUTPUT.PUT_LINE('Unused Blocks: '||v_unused_blocks);
DBMS_OUTPUT.PUT_LINE('Unused MBytes: '||to_char(v_unused_bytes/1024/1024));
DBMS_OUTPUT.PUT_LINE('File of Last Extent Containing Data:
'||'('||v_last_used_extent_file_id ||') '||v_filename );
DBMS_OUTPUT.PUT_LINE('Starting Block ID of the Last Extent Containing Data:
'||v_last_used_block );
DBMS_OUTPUT.PUT_LINE('Last Block Within This Extent Which Contains Data:
'||v_last_used_block );
end;
/

-- space usage of data blocks under the segment High Water Mark with
-- auto segment space management Tablespaces
-- following code in Oracle 10g
set serveroutput on

declare
-- IN vars
v_segment_owner VARCHAR2(100):='HR';
v_segment_name VARCHAR2(100) :='NAMES2';
v_segment_type VARCHAR2(100) :='TABLE';
v_partition_name VARCHAR2(100) :=null;

Page 118 Oracle DBA Code Examples

-- OUT vars
v_unformatted_blocks NUMBER;
v_unformatted_bytes NUMBER;
v_fs1_blocks NUMBER;
v_fs1_bytes NUMBER;
v_fs2_blocks NUMBER;
v_fs2_bytes NUMBER;
v_fs3_blocks NUMBER;
v_fs3_bytes NUMBER;
v_fs4_blocks NUMBER;
v_fs4_bytes NUMBER;
v_full_blocks NUMBER;
v_full_bytes NUMBER;
v_segment_size_blocks NUMBER;
v_segment_size_bytes NUMBER;
v_used_blocks NUMBER;
v_used_bytes NUMBER;
v_expired_blocks NUMBER;
v_expired_bytes NUMBER;
v_unexpired_blocks NUMBER;
v_unexpired_bytes NUMBER;
begin
DBMS_SPACE.SPACE_USAGE(
segment_owner => v_segment_owner ,
segment_name => v_segment_name ,
segment_type => v_segment_type ,
unformatted_blocks => v_unformatted_blocks ,
unformatted_bytes => v_unformatted_bytes ,
fs1_blocks => v_fs1_blocks ,
fs1_bytes => v_fs1_bytes ,
fs2_blocks => v_fs2_blocks ,
fs2_bytes => v_fs2_bytes ,
fs3_blocks => v_fs3_blocks ,
fs3_bytes => v_fs3_bytes ,
fs4_blocks => v_fs4_blocks ,
fs4_bytes => v_fs4_bytes ,
full_blocks => v_full_blocks ,
full_bytes => v_full_bytes ,
partition_name => v_partition_name );

DBMS_OUTPUT.PUT_LINE('Free Space in MB:');
DBMS_OUTPUT.PUT_LINE('Unformatted Mbytes: '||v_unformatted_bytes/1024/1024);
DBMS_OUTPUT.PUT_LINE('Free Space in MB:');
DBMS_OUTPUT.PUT_LINE(' 0 to 25% free space: '|| v_fs1_bytes/1024/1024);
DBMS_OUTPUT.PUT_LINE(' 25 to 50% free space: '|| v_fs2_bytes/1024/1024);
DBMS_OUTPUT.PUT_LINE(' 50 to 75% free space: '|| v_fs3_bytes/1024/1024);
DBMS_OUTPUT.PUT_LINE(' 75 to 100% free space: '|| v_fs4_bytes/1024/1024);
DBMS_OUTPUT.PUT_LINE(' Full Mbytes : '|| v_full_bytes/1024/1024);
DBMS_OUTPUT.PUT_LINE('Free Space in Blocks:');
DBMS_OUTPUT.PUT_LINE('Unformatted Blocks: ' || v_unformatted_blocks);
DBMS_OUTPUT.PUT_LINE(' 0 to 25% free space: '|| v_full_blocks);
DBMS_OUTPUT.PUT_LINE(' 25 to 50% free space: '|| v_fs2_blocks);
DBMS_OUTPUT.PUT_LINE(' 50 to 75% free space: '|| v_fs3_blocks);
DBMS_OUTPUT.PUT_LINE(' 75 to 100% free space: '|| v_fs4_blocks);
DBMS_OUTPUT.PUT_LINE(' Full blocks : '|| v_full_blocks);

exception
Page 119 Oracle DBA Code Examples

when others then
if sqlcode = '-942' then
DBMS_OUTPUT.PUT_LINE('Object Does not Exist.');
else
raise;
end if;
end;
/


/* Using Views */
-- segment info
select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,S.TABLESPACE_NAME,
HEADER_FILE,HEADER_BLOCK,S.BYTES/1024/1024 SEGMENT_SIZE_MB,
D.BLOCKS SEGMENT_BLOCKS,EXTENTS,S.INITIAL_EXTENT,
S.NEXT_EXTENT NEXT_EXTENT_SIZE, S.MIN_EXTENTS,S.MAX_EXTENTS,S.PCT_INCREASE,
FREELISTS,FREELIST_GROUPS,D.RELATIVE_FNO, D.FILE_NAME
from DBA_SEGMENTS S, DBA_TABLESPACES T, DBA_DATA_FILES D
where S.TABLESPACE_NAME = T.TABLESPACE_NAME AND T.TABLESPACE_NAME =
D.TABLESPACE_NAME
AND S.RELATIVE_FNO = D.RELATIVE_FNO
AND T.TABLESPACE_NAME NOT IN ('SYSAUX','SYSTEM')
order BY S.TABLESPACE_NAME;

-- extent info
SELECT EXTENT_ID, E.BLOCK_ID,E.BYTES/1024 EXTENT_SIZE_KB,
E.OWNER,E.SEGMENT_NAME,E.PARTITION_NAME,
E.SEGMENT_TYPE,D.FILE_NAME, S.TABLESPACE_NAME,HEADER_FILE,
HEADER_BLOCK SEG_HEADER_BLOCK,S.BYTES/1024/1024 SEGMENT_SIZE_MB,D.BLOCKS
SEGMENT_BLOCKS, EXTENTS SEG_EXTENTS
FROM DBA_EXTENTS E, DBA_SEGMENTS S, DBA_DATA_FILES D
WHERE E.OWNER=S.OWNER AND E.SEGMENT_NAME = S.SEGMENT_NAME
AND NVL(E.PARTITION_NAME,'0') = NVL(S.PARTITION_NAME,'0')
AND E.SEGMENT_TYPE = S.SEGMENT_TYPE
AND E.FILE_ID = D.FILE_ID
AND S.TABLESPACE_NAME NOT IN ('SYSAUX','SYSTEM')
ORDER BY E.SEGMENT_NAME, E.OWNER, E.PARTITION_NAME,E.EXTENT_ID;

-- Segment that cannot allocate additional extents
SELECT a.SEGMENT_NAME, a.SEGMENT_TYPE, a.TABLESPACE_NAME, a.OWNER
FROM DBA_SEGMENTS a
WHERE a.NEXT_EXTENT >= (SELECT MAX(b.BYTES)
FROM DBA_FREE_SPACE b
WHERE b.TABLESPACE_NAME = a.TABLESPACE_NAME)
OR a.EXTENTS = a.MAX_EXTENTS
OR a.EXTENTS = '8192' ; -- data_block_size

-- free extents within tablespaces
SELECT F.TABLESPACE_NAME,F.FILE_ID,F.BLOCK_ID,
F.BYTES/1024/1024 FREE_MB, D.FILE_NAME
FROM DBA_FREE_SPACE F, DBA_DATA_FILES D
WHERE F.FILE_ID = D.FILE_ID
UNION
SELECT F.TABLESPACE_NAME, TO_NUMBER('') AS FILE_ID, TO_NUMBER('') AS BLOCK_ID,
SUM(F.BYTES/1024/1024) FREE_MB, TO_CHAR('') AS FILE_NAME
FROM DBA_FREE_SPACE F
GROUP BY F.TABLESPACE_NAME, TO_NUMBER('') , TO_NUMBER('') ,TO_CHAR('')
ORDER BY TABLESPACE_NAME;
Page 120 Oracle DBA Code Examples

Segment Advi sor
The Aut omat ic Segment Advisor is st art ed by a Scheduler j ob t hat is configured t o run
during t he default maint enance window(s) .
I nput for DBMS_ADVI SOR.CREATE_OBJECT
ATTR4 is unused. Specify NULL
OBJECT_TYPE ATTR1 ATTR2 ATTR3
TABLESPACE tbs name
TABLE schema name table name
INDEX schema name index name
TABLE PARTITION schema name table name table partition name
INDEX PARTITION schema name index name index partition name
TABLE SUBPART. schema name table name table subpartition name
INDEX SUBPART. schema name index name index subpartition name
LOB schema name segment name
LOB PARTITION schema name segment name lob partition name
LOB SUBPART. schema name segment name lob subpartition name

-- object priv EXECUTE ON DBMS_ADVISOR or the system priv ADVISOR

-- run Segment Advisor on hr.employees
variable id number;
declare
v_name varchar2(100);
v_descr varchar2(500);
obj_id number;
begin
v_name:='Manual_Employees';
v_descr:='Segment Advisor Example';
dbms_advisor.create_task (
advisor_name => 'Segment Advisor',
task_id => :id,
task_name => v_name,
task_desc => v_descr);

-- identify the target object
dbms_advisor.create_object (
task_name => v_name,
object_type => 'TABLE',
attr1 => 'HR',
attr2 => 'EMPLOYEES',
attr3 => NULL,
attr4 => NULL,
attr5 => NULL,
object_id => obj_id);

dbms_advisor.set_task_parameter(
task_name => v_name,
parameter => 'recommend_all',
value => 'TRUE');
dbms_advisor.execute_task(v_name);
end;
/

-- viewing segment results
-- asa_recommendations ( all_runs in varchar2 DEFAULT 'TRUE',
-- show_manual in varchar2 DEFAULT 'TRUE',
Page 121 Oracle DBA Code Examples

-- show_findings in varchar2 DEFAULT 'FALSE' )
select tablespace_name, segment_name, segment_type, partition_name,
recommendations, c1 from
table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'TRUE'));

select * from table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'TRUE'));

-- alternatively use DBA_ADVISOR_*
SELECT OWNER,REC_ID,TASK_ID,
TASK_NAME,FINDING_ID,TYPE,
RANK,PARENT_REC_IDS,BENEFIT_TYPE,
BENEFIT,ANNOTATION_STATUS,FLAGS
FROM DBA_ADVISOR_RECOMMENDATIONS
WHERE TASK_ID = :ID

SELECT OWNER,TASK_ID,TASK_NAME,
FINDING_ID,TYPE,PARENT,
OBJECT_ID,IMPACT_TYPE,IMPACT,MESSAGE,MORE_INFO
FROM DBA_ADVISOR_FINDINGS
WHERE TASK_ID = :ID

-- suggested actions to perform
SELECT OWNER,TASK_ID,TASK_NAME,
REC_ID,ACTION_ID,OBJECT_ID,
COMMAND,COMMAND_ID,FLAGS,
ATTR1,ATTR2,ATTR3,
ATTR4,ATTR5,ATTR6,
NUM_ATTR1,NUM_ATTR2,NUM_ATTR3,
NUM_ATTR4,NUM_ATTR5,MESSAGE
FROM DBA_ADVISOR_ACTIONS
WHERE TASK_ID = :ID

-- analyzed objects
SELECT OWNER,OBJECT_ID,TYPE,TYPE_ID,
TASK_ID,TASK_NAME,ATTR1,ATTR2,
ATTR3,ATTR4,ATTR5,OTHER
FROM DBA_ADVISOR_OBJECTS
WHERE TASK_ID = :ID

/* information about the Advisor Runs */
SELECT AUTO_TASKID,SNAPID,SEGMENTS_SELECTED,SEGMENTS_PROCESSED,
TABLESPACE_SELECTED,TABLESPACE_PROCESSED,RECOMMENDATIONS_COUNT,
START_TIME, END_TIME
FROM DBA_AUTO_SEGADV_SUMMARY;

/* control information that show which object analyzed by the Advisor */
SELECT *
FROM DBA_AUTO_SEGADV_CTL
Shr i nk i ng Dat abase Segment s Onl i ne
-- required
ALTER TABLE NAMES ENABLE ROW MOVEMENT -- it acquires table lock

-- shrink table (defrage and then move the HWM)
ALTER TABLE cities SHRINK SPACE; -- it acquires table lock

-- defrage and don't move HWM (later you can SHRINK SPACE)
Page 122 Oracle DBA Code Examples

ALTER TABLE cities SHRINK SPACE COMPACT; -- no table lock

-- LOBs and the Indexes are alos shrinked
ALTER TABLE employees SHRINK SPACE CASCADE;

-- shrink LOB only
ALTER TABLE employees MODIFY LOB (perf_review) (SHRINK SPACE);

-- shrink partition
ALTER TABLE customers MODIFY PARTITION cust_P1 SHRINK SPACE;

-- IOT
ALTER TABLE cities SHRINK SPACE CASCADE;

-- IOT Overflow Segment
ALTER TABLE cities OVERFLOW SHRINK SPACE;
Deal l ocat i ng Unused Space
deallocat es unused space at t he end of t he segment ; no dat a is moved
ALTER TABLE table DEALLOCATE UNUSED KEEP integer;
ALTER INDEX index DEALLOCATE UNUSED KEEP integer;
ALTER CLUSTER cluster DEALLOCATE UNUSED KEEP integer;
Capaci t y Pl anni ng f or Dat abase Obj ect s
Est i mat i ng t he Space Use of a Tabl e
DBMS_SPACE.CREATE_TABLE_COST can be used on dict ionary- managed or locally
managed t ablespaces.
-- based on average row size
SET SERVEROUTPUT ON
declare
v_table_name varchar2(30) :='NAMES';
v_owner varchar2(30) :='HR';
v_tablespace_name varchar2(100) := 'USERS';
v_avg_row_size number ; -- anticipated average row size
v_row_count number := 1000000;
v_pct_free number := 10;
v_used_bytes number; -- User data space including PCTFREE
v_alloc_bytes number; -- The anticipated size of the table taking into
account the tablespace extent characteristics
x char(1);
begin

-- check the tablespace exists
begin
SELECT 'x' into x from dba_tablespaces where
tablespace_name=v_tablespace_name ;
exception
when no_data_found then
raise_application_error(-20001,'Tablespace: ' || v_tablespace_name || '
does not exist');
end;

-- retreive average row size
Page 123 Oracle DBA Code Examples

begin
SELECT AVG_ROW_LEN into V_avg_row_size FROM DBA_TABLES where
table_name=v_table_name and owner=v_owner;
exception
when no_data_found then
raise_application_error(-20002,'Table : ' || V_OWNER ||'.'||v_table_name ||
' does not exist');
end;

DBMS_SPACE.CREATE_TABLE_COST (
tablespace_name => v_tablespace_name,
avg_row_size => v_avg_row_size,
row_count => v_row_count,
pct_free => v_pct_free,
used_bytes => v_used_bytes,
alloc_bytes => v_alloc_bytes);

dbms_output.put_line('User Data Space (MB): '||
TO_CHAR(v_used_bytes/1024/1024 ,'999,999,999,999,999.99'));
dbms_output.put_line('Data Space in the Tablespace (MB): '||
TO_CHAR(v_alloc_bytes/1024/1024,'999,999,999,999,999.99'));
end;
/

-- based on column info
declare
ub NUMBER;
ab NUMBER;
cl sys.create_table_cost_columns;
begin
cl := sys.create_table_cost_columns(
sys.create_table_cost_colinfo('NUMBER',10),
sys.create_table_cost_colinfo('VARCHAR2',30),
sys.create_table_cost_colinfo('VARCHAR2',30),
sys.create_table_cost_colinfo('DATE',NULL));
DBMS_SPACE.CREATE_TABLE_COST('SYSTEM',cl,100000,0,ub,ab);
DBMS_OUTPUT.PUT_LINE('Used Bytes: ' || TO_CHAR(ub));
DBMS_OUTPUT.PUT_LINE('Alloc Bytes: ' || TO_CHAR(ab));
end;
/
Obt ai ni ng Obj ect Gr ow t h Tr ends
User DBMS_SPACE.OBJECT_GROWTH_TREND
o st art _t ime St at ist ics generat ed aft er t his t ime will be used in generat ing t he growt h
t rend
o end_t ime St at ist ics generat ed unt il t his t ime will be used in generat ing t he growt h
t rend
o int erval The int erval at which t o sample
o skip_int erpolat ed Whet her int erpolat ion of missing values should be skipped
o single_dat a_point _flag Whet her in t he absence of st at ist ics t he segment should be
sampled
Ret urned columns:
o t imepoint The t ime at which t he st at ist ic was recorded
o space_usage The space used by dat a
o space_alloc The size of t he segment including overhead and unused space
Page 124 Oracle DBA Code Examples

o qualit y The qualit y of result : "GOOD", "I NTERPOLATED", "PROJECTI ON"
GOOD: accurat e figure
PROJECTED: figures proj ect ed from t he dat a collect ed by t he AWR
I NTERPOLATED: no dat a was available for calculat ions
COL TIMEPOINT FORMAT A30

SELECT *
FROM
table(
DBMS_SPACE.OBJECT_GROWTH_TREND (
object_owner =>'HR',
object_name =>'NAMES',
object_type =>'TABLE',
partition_name =>NULL,
start_time =>NULL,
end_time =>NULL,
interval =>to_dsinterval('0 00:10:00') , -- Day to Second interval
skip_interpolated => 'FALSE',
timeout_seconds =>NULL,
single_datapoint_flag =>'TRUE')
)
Usi ng t he SQL Access Advi sor
Est i mat i ng t he Space Use of a Tabl e
The SQL Access Advisor sources:
o A hypot het ical dat abase workload
o An act ual workload you provide
o SQL cache
-- Method 1: to invoke SQL Access Advisor task linked to a workload
declare
l_taskname VARCHAR2(30) := 'sql_access_test_task';
l_task_desc VARCHAR2(128) := 'Test SQL Access';
l_wkld_name VARCHAR2(30) := 'test_work_load';
l_saved_rows NUMBER := 0;
l_failed_rows NUMBER := 0;
l_num_found NUMBER;
BEGIN
-- create an SQL Access Advisor task.
select COUNT(*)
into l_num_found
from DBA_ADVISOR_TASKS
where TASK_NAME = l_taskname ;
IF l_num_found = 0 THEN
DBMS_ADVISOR.CREATE_TASK (
ADVISOR_NAME => DBMS_ADVISOR.SQLACCESS_ADVISOR,
TASK_NAME => l_taskname,
TASK_DESC => l_task_desc);
END IF;

-- reset the task ( remove all recommendations, and intermediate data from
the task)
DBMS_ADVISOR.RESET_TASK(TASK_NAME => l_taskname);
Page 125 Oracle DBA Code Examples


-- create a workload.
SELECT COUNT(*)
INTO l_num_found
FROM USER_ADVISOR_SQLW_SUM
WHERE WORKLOAD_NAME = l_wkld_name;

IF l_num_found = 0 THEN
DBMS_ADVISOR.CREATE_SQLWKLD(WORKLOAD_NAME => l_wkld_name);
END IF;

-- link the workload to the task
SELECT count(*)
INTO l_num_found
FROM USER_ADVISOR_SQLA_WK_MAP
WHERE TASK_NAME = l_taskname
AND WORKLOAD_NAME = l_wkld_name;

IF l_num_found = 0 THEN
DBMS_ADVISOR.ADD_SQLWKLD_REF(
TASK_NAME => l_taskname,
WORKLOAD_NAME => l_wkld_name);
END IF;

-- Set workload parameters.
DBMS_ADVISOR.SET_SQLWKLD_PARAMETER(l_wkld_name, 'ACTION_LIST',
DBMS_ADVISOR.ADVISOR_UNUSED);
DBMS_ADVISOR.SET_SQLWKLD_PARAMETER(l_wkld_name, 'MODULE_LIST',
DBMS_ADVISOR.ADVISOR_UNUSED);
DBMS_ADVISOR.SET_SQLWKLD_PARAMETER(l_wkld_name, 'SQL_LIMIT',
DBMS_ADVISOR.ADVISOR_UNLIMITED);
DBMS_ADVISOR.SET_SQLWKLD_PARAMETER(l_wkld_name, 'ORDER_LIST',
'PRIORITY,OPTIMIZER_COST');
DBMS_ADVISOR.SET_SQLWKLD_PARAMETER(l_wkld_name, 'USERNAME_LIST',
DBMS_ADVISOR.ADVISOR_UNUSED);
DBMS_ADVISOR.SET_SQLWKLD_PARAMETER(l_wkld_name, 'VALID_TABLE_LIST',
DBMS_ADVISOR.ADVISOR_UNUSED);

-- unmark the required option
/*
-- import the current contents of the server's SQL cache
DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE(l_wkld_name, 'REPLACE', 2,
l_saved_rows, l_failed_rows);
-- load a SQL workload from an existing SQL Tuning Set
DBMS_ADVISOR.IMPORT_SQLWKLD_STS (l_wkld_name, 'SOURCE_STS_Name',
'REPLACE',2, l_saved_rows, l_failed_rows);
*/
-- Set task parameters.
DBMS_ADVISOR.SET_TASK_PARAMETER(l_taskname, '_MARK_IMPLEMENTATION',
'FALSE');
DBMS_ADVISOR.SET_TASK_PARAMETER(l_taskname, 'EXECUTION_TYPE',
'INDEX_ONLY');
DBMS_ADVISOR.SET_TASK_PARAMETER(l_taskname, 'MODE', 'COMPREHENSIVE');
DBMS_ADVISOR.SET_TASK_PARAMETER(l_taskname, 'STORAGE_CHANGE',
DBMS_ADVISOR.ADVISOR_UNLIMITED);
DBMS_ADVISOR.SET_TASK_PARAMETER(l_taskname, 'DML_VOLATILITY', 'TRUE');
DBMS_ADVISOR.SET_TASK_PARAMETER(l_taskname, 'ORDER_LIST',
'PRIORITY,OPTIMIZER_COST');
Page 126 Oracle DBA Code Examples

DBMS_ADVISOR.SET_TASK_PARAMETER(l_taskname, 'WORKLOAD_SCOPE', 'PARTIAL');
DBMS_ADVISOR.SET_TASK_PARAMETER(l_taskname, 'DEF_INDEX_TABLESPACE',
DBMS_ADVISOR.ADVISOR_UNUSED);
DBMS_ADVISOR.SET_TASK_PARAMETER(l_taskname, 'DEF_INDEX_OWNER',
DBMS_ADVISOR.ADVISOR_UNUSED);
DBMS_ADVISOR.SET_TASK_PARAMETER(l_taskname, 'DEF_MVIEW_TABLESPACE',
DBMS_ADVISOR.ADVISOR_UNUSED);
DBMS_ADVISOR.SET_TASK_PARAMETER(l_taskname, 'DEF_MVIEW_OWNER',
DBMS_ADVISOR.ADVISOR_UNUSED);

-- Execute the task: control will not return till the execution finishes
DBMS_ADVISOR.execute_task(task_name => l_taskname);
END;
/


-- Method 2: to invoke SQL Access Advisor linked to a specific STS
declare
l_taskname VARCHAR2(30) := 'sql_access_test_task2';
l_task_desc VARCHAR2(128) := 'Test SQL Access for a STS';
l_wkld_name VARCHAR2(30) := 'test_work_load';
l_sts_name VARCHAR2(30) := 'test_sts';
l_saved_rows NUMBER := 0;
l_failed_rows NUMBER := 0;
l_num_found NUMBER;
BEGIN
-- create an SQL Access Advisor task, if it doesnt exist
select COUNT(*)
into l_num_found
from DBA_ADVISOR_TASKS
where TASK_NAME = l_taskname ;
IF l_num_found = 0 THEN
DBMS_ADVISOR.CREATE_TASK (
ADVISOR_NAME => DBMS_ADVISOR.SQLACCESS_ADVISOR,
TASK_NAME => l_taskname,
TASK_DESC => l_task_desc);
END IF;

-- reset the task ( remove all recommendations, and intermediate data from
the task)
DBMS_ADVISOR.RESET_TASK(TASK_NAME => l_taskname);

-- check if STS already exists
select count(*)
into l_num_found
from DBA_SQLSET where upper(NAME) = upper(l_sts_name) ;

IF l_num_found <> 0 THEN
DBMS_SQLTUNE.DROP_SQLSET ( sqlset_name => l_sts_name);
END IF;

-- create STS
DBMS_SQLTUNE.CREATE_SQLSET(SQLSET_NAME => l_sts_name, DESCRIPTION =>'To
test Access Advisor');

/* unmark the required option
-- (Option 1) Load l_sts_name from an AWR baseline.
-- The data has been filtered to select only the top 30 SQL statements
Page 127 Oracle DBA Code Examples

ordered by elapsed time.
declare
baseline_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
-- a ref cursor is opened to select from the specified baseline
OPEN baseline_cur FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
'peak baseline',NULL, NULL, 'elapsed_time', NULL, NULL, NULL,30 )) p;

-- Next the statements and their statistics are loaded into the STS
DBMS_SQLTUNE.LOAD_SQLSET( SQLSET_NAME=>l_sts_name,
POPULATE_CURSOR=>baseline_cur);
end;

-- (Option 2) Load l_sts_name with SQL statements that are not owned by SYS
and
-- their elapsed time is greater than 20,000 seconds.
declare
sql_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
-- a ref cursor is opened to select the required SQL statments
OPEN sql_cur FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name <>
''SYS'' and elapsed_time > 2000000',NULL, NULL, NULL, NULL,1, NULL, 'ALL'))
p;
-- the statements are loaded into the STS
DBMS_SQLTUNE.LOAD_SQLSET( SQLSET_NAME=>l_sts_name,
POPULATE_CURSOR=>sql_cur);
end;

-- (Option 3) Copy the contents of a SQL workload object to a SQL Tuning Set
-- check the example above for creating a workload
DBMS_ADVISOR.COPY_SQLWKLD_TO_STS ('My_WorkLoad', l_sts_name, 'REPLACE');
*/

-- link the STS to the task
DBMS_ADVISOR.ADD_STS_REF (l_taskname, null, l_sts_name);

-- Set task parameters.
DBMS_ADVISOR.SET_TASK_PARAMETER(l_taskname, '_MARK_IMPLEMENTATION',
'FALSE');
DBMS_ADVISOR.SET_TASK_PARAMETER(l_taskname, 'EXECUTION_TYPE',
'INDEX_ONLY');
DBMS_ADVISOR.SET_TASK_PARAMETER(l_taskname, 'MODE', 'COMPREHENSIVE');
DBMS_ADVISOR.SET_TASK_PARAMETER(l_taskname, 'STORAGE_CHANGE',
DBMS_ADVISOR.ADVISOR_UNLIMITED);
DBMS_ADVISOR.SET_TASK_PARAMETER(l_taskname, 'DML_VOLATILITY', 'TRUE');
DBMS_ADVISOR.SET_TASK_PARAMETER(l_taskname, 'ORDER_LIST',
'PRIORITY,OPTIMIZER_COST');
DBMS_ADVISOR.SET_TASK_PARAMETER(l_taskname, 'WORKLOAD_SCOPE', 'PARTIAL');
DBMS_ADVISOR.SET_TASK_PARAMETER(l_taskname, 'DEF_INDEX_TABLESPACE',
DBMS_ADVISOR.ADVISOR_UNUSED);
DBMS_ADVISOR.SET_TASK_PARAMETER(l_taskname, 'DEF_INDEX_OWNER',
DBMS_ADVISOR.ADVISOR_UNUSED);
DBMS_ADVISOR.SET_TASK_PARAMETER(l_taskname, 'DEF_MVIEW_TABLESPACE',
DBMS_ADVISOR.ADVISOR_UNUSED);
Page 128 Oracle DBA Code Examples

DBMS_ADVISOR.SET_TASK_PARAMETER(l_taskname, 'DEF_MVIEW_OWNER',
DBMS_ADVISOR.ADVISOR_UNUSED);

-- Execute the task: control will not return till the execution finishes
DBMS_ADVISOR.EXECUTE_TASK(task_name => l_taskname);
END;
/


-- Method 3: Quick Tune for a single given statement
begin
-- a task and a workload will be created then the task will be executed
DBMS_ADVISOR.QUICK_TUNE(
ADVISOR_NAME => DBMS_ADVISOR.SQLACCESS_ADVISOR,
TASK_NAME => 'names_quick_tune',
ATTR1 => 'SELECT id FROM hr.names n WHERE id = 100');
end;
/
Following are examples of how t o manage t he t ask and obt ain informat ion about t he advisors
out put repot :
-- while the task is being executed, you can monitor it using the following
query:
select TASK_NAME, STATUS, PCT_COMPLETION_TIME, ERROR_MESSAGE
from DBA_ADVISOR_LOG
where TASK_NAME ='sql_access_test_task';

-- if you need to terminate the executing task (may be time consuming)
exec DBMS_ADVISOR.CANCEL_TASK(TASK_NAME =>'sql_access_test_task');

-- Display the resulting script ( method 1)
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.GET_TASK_SCRIPT('sql_access_test_task') AS script FROM
dual;
SET PAGESIZE 24

-- Display the resulting script ( method 2)
CREATE DIRECTORY ADVISOR_RESULTS AS 'C:\TEMP\';
exec
DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('sql_access_test_task'),
'ADVISOR_RESULTS','advscript.sql');

-- alternatively, use the following queries
-- benefit is the total improvement in execution cost of all the queries
select REC_ID, RANK, BENEFIT, TYPE "Recommendation Type"
from DBA_ADVISOR_RECOMMENDATIONS
where TASK_NAME = 'sql_access_test_task'
order by RANK;

-- display processed statements in the workload
select SQL_ID, REC_ID, PRECOST, POSTCOST,
(PRECOST-POSTCOST)*100/PRECOST AS PERCENT_BENEFIT
from USER_ADVISOR_SQLA_WK_STMTS
where TASK_NAME = 'sql_access_test_task' AND workload_name =
'test_work_load';

-- see the actions for each recommendations
Page 129 Oracle DBA Code Examples

select REC_ID, ACTION_ID, SUBSTR(COMMAND,1,30) AS COMMAND
from USER_ADVISOR_ACTIONS where TASK_NAME = 'sql_access_test_task'
ORDER BY rec_id, action_id;

-- to delete a given task
exec DBMS_ADVISOR.DELETE_TASK('sql_access_test_task');
Page 130 Oracle DBA Code Examples

Managi ng Tabl es
Obt ai ni ng I nf or mat i on about Tabl es
-- 11g
SELECT OWNER,TABLE_NAME,TABLESPACE_NAME,CLUSTER_NAME,IOT_NAME,STATUS,PCT_FREE,
PCT_USED,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,
NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,FREELISTS,FREELIST_GROUPS,LOGGING,
BACKED_UP,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,
AVG_ROW_LEN,AVG_SPACE_FREELIST_BLOCKS,NUM_FREELIST_BLOCKS,DEGREE,
INSTANCES,CACHE,TABLE_LOCK,SAMPLE_SIZE,LAST_ANALYZED,PARTITIONED,IOT_TYPE,
TEMPORARY,SECONDARY,NESTED,BUFFER_POOL,ROW_MOVEMENT,GLOBAL_STATS,USER_STATS,
DURATION,SKIP_CORRUPT,MONITORING,CLUSTER_OWNER,DEPENDENCIES,COMPRESSION,
COMPRESS_FOR,DROPPED,READ_ONLY
FROM DBA_TABLES
WHERE TABLE_NAME='EMP' AND OWNER='HR';

-- columns
SELECT
OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_TYPE_MOD,DATA_TYPE_OWNER,DATA_LENGTH,DATA_P
RECISION,DATA_SCALE,NULLABLE,COLUMN_ID,DEFAULT_LENGTH,DATA_DEFAULT,NUM_DISTINCT,LOW_VAL
UE,HIGH_VALUE,DENSITY,NUM_NULLS,NUM_BUCKETS,LAST_ANALYZED,SAMPLE_SIZE,CHARACTER_SET_NAM
E,CHAR_COL_DECL_LENGTH,GLOBAL_STATS,USER_STATS,AVG_COL_LEN,CHAR_LENGTH,CHAR_USED,V80_FM
T_IMAGE,DATA_UPGRADED,HISTOGRAM
FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME='EMP' AND OWNER='HR';

-- table gathered statistics
SELECT
OWNER,TABLE_NAME,PARTITION_NAME,PARTITION_POSITION,SUBPARTITION_NAME,SUBPARTITION_POSIT
ION,OBJECT_TYPE,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN,AVG_SPACE_
FREELIST_BLOCKS,NUM_FREELIST_BLOCKS,AVG_CACHED_BLOCKS,AVG_CACHE_HIT_RATIO,SAMPLE_SIZE,L
AST_ANALYZED,GLOBAL_STATS,USER_STATS,STATTYPE_LOCKED,STALE_STATS
FROM DBA_TAB_STATISTICS
WHERE TABLE_NAME='EMP' AND OWNER='HR';

-- comments
select * from DBA_TAB_COMMENTS;
select * from DBA_COL_COMMENTS;

-- object and relational tables
SELECT
OWNER,TABLE_NAME,TABLESPACE_NAME,CLUSTER_NAME,IOT_NAME,STATUS,PCT_FREE,PCT_USED,INI_TRA
NS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,FREELISTS,
FREELIST_GROUPS,LOGGING,BACKED_UP,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_
ROW_LEN,AVG_SPACE_FREELIST_BLOCKS,NUM_FREELIST_BLOCKS,DEGREE,INSTANCES,CACHE,TABLE_LOCK
,SAMPLE_SIZE,LAST_ANALYZED,PARTITIONED,IOT_TYPE,OBJECT_ID_TYPE,TABLE_TYPE_OWNER,TABLE_T
YPE,TEMPORARY,SECONDARY,NESTED,BUFFER_POOL,ROW_MOVEMENT,GLOBAL_STATS,USER_STATS,DURATIO
N,SKIP_CORRUPT,MONITORING,CLUSTER_OWNER,DEPENDENCIES,COMPRESSION,COMPRESS_FOR,DROPPED
FROM DBA_ALL_TABLES
WHERE TABLE_NAME='EMP' AND OWNER='HR';
Cr eat i ng Tabl es w i t h some Opt i ons
-- specific tablspace
create table mytab ... tablspace mytbs
-- Parallelizing Table Creation
CREATE TABLE mytb PARALLEL AS SELECT ..
ALTER SESSION FORCE PARALLEL DDL; -- created tables will be parallelized

-- nologging
Page 131 Oracle DBA Code Examples

CREATE TABLE mytb NOLOGGING AS SELECT ..

-- compression
CREATE TABLE mytb ( ... ) COMPRESS FOR ALL OPERATIONS; -- OK in OLTP
CREATE TABLE sales_history ( ... ) COMPRESS FOR DIRECT_LOAD OPERATIONS; -- OK in OLAP
CREATE TABLE sales_history ( ... ) COMPRESS; -- same as above
CREATE TABLE sales
(saleskey number, quarter .. region varchar2(10)) COMPRESS
PARTITION BY LIST (region)
(PARTITION northwest VALUES ('NORTHWEST'),
PARTITION southwest VALUES ('SOUTHWEST'),
PARTITION northeast VALUES ('NORTHEAST') NOCOMPRESS,
PARTITION southeast VALUES ('SOUTHEAST'),
PARTITION western VALUES ('WESTERN'))

-- encrypting columns (REQUIRES OEPENED WALLET)
CREATE TABLE mytb ( .. ssn NUMBER(9) ENCRYPT, ..)

-- temporary table
-- transaction level
CREATE GLOBAL TEMPORARY TABLE mytb (..) ON COMMIT DELETE ROWS;
-- session level
CREATE GLOBAL TEMPORARY TABLE mytb (..) ON COMMIT PRESERVE ROWS;
DML Er r or Loggi ng
DML error logging works wit h I NSERT, UPDATE, MERGE, and DELETE st at ement s.
The following condit ions cause t he st at ement t o fail and roll back wit hout invoking t he
error logging capabilit y:
o Violat ed deferred const raint s
o Out - of- space errors
o Any direct -pat h I NSERT operat ion ( I NSERT or MERGE) t hat raises a unique const raint
or index violat ion
o Any UPDATE or MERGE t hat raises a unique const raint or index violat ion
-- 1. Create an error logging table.
-- LONG, CLOB, BLOB, BFILE, and ADT datatypes are not supported in the
columns
EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG(dml_table_name=>'EMP',
err_log_table_name=>'ERR_EMP');
EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG(dml_table_name=>'EMP',
err_log_table_name=>'ERR_EMP', err_log_table_owner=>'HR',
err_log_table_space=>'USERS');
-- 2 include an error logging clause
INSERT INTO emp VALUES(1001, 'abcd', 75, NULL)
LOG ERRORS INTO ERR_EMP REJECT LIMIT UNLIMITED;
INSERT INTO hr.emp
SELECT .. FROM ..
LOG ERRORS INTO err_emp ('daily_load') REJECT LIMIT 25
-- 3 Query the error logging table and take corrective action
Enabl i ng Di r ect - Pat h I NSERT
To act ivat e direct - pat h I NSERT in serial mode, you must specify t he APPEND hint in each
I NSERT st at ement , eit her immediat ely aft er t he I NSERT keyword, or immediat ely aft er t he
SELECT keyword in t he subquery of t he I NSERT st at ement .
Page 132 Oracle DBA Code Examples

insert /*+ append */ into customer (select xxx);
When you are insert ing in parallel DML mode, direct -pat h I NSERT is t he default . I n order
t o run in parallel DML mode, t he following requirement s must be met :
o You must have Oracle Ent erprise Edit ion inst alled.
o You must enable parallel DML in your session. To do t his, run t he following st at ement :
ALTER SESSION { ENABLE | FORCE } PARALLEL DML;
o You must specify t he parallel at t ribut e for t he t arget t able, eit her at creat e t ime or
subsequent ly, or you must specify t he PARALLEL hint for each insert operat ion.
insert /*+ parallel */ into names3 (select * from names)
I f nologging is specified, t he insert will finish fast er.
select logging from dba_tables where owner='HR' and table_name='EMPLOYEES';
select logging from dba_tablespaces where tablespace_name = 'USERS';
I ndex maint enance is done by end of command. Consider drop and t hen re-creat ing t he
indexes aft er t he st at ement .
Exclusive t able lock is acquired.
Aut omat i cal l y Col l ect i ng St at i st i cs on Tabl es
I n Oracle Dat abase 11g, st at ist ics are collect ed aut omat ically for t ables regardless of t he
t he MONI TORI NG and NOMONI TORI NG keywords
when creat ing t hem.
Al t er i ng Tabl es
I f you enable compression for all operat ions on a t able, you can drop t able columns. I f you
enable compression for direct -pat h insert s only, you cannot drop columns.
-- move the table to a new segment
-- indexes will be UNUSABLE and must be rebuilt
-- statistics must be regathered
ALTER TABLE hr.emp MOVE
STORAGE ( INITIAL 20K
NEXT 40K
MINEXTENTS 2
MAXEXTENTS 20
PCTINCREASE 0 );
alter table emp MOVE TABLESPACE example ;
alter table emp move storage (
NEXT 1024K
PCTINCREASE 0 )
COMPRESS;

-- allocating and deallocating extents
alter table hr.emp allocate extent;
alter table hr.emp deallocate extent;

-- allocating and deallocating extents
alter table hr.emp allocate extent;
alter table hr.emp deallocate unused;

Page 133 Oracle DBA Code Examples

-- Marking Columns Unused
ALTER TABLE hr.emp SET UNUSED (hiredate, mgr);
SELECT * FROM DBA_UNUSED_COL_TABS;
ALTER TABLE hr.emp DROP UNUSED COLUMNS statement;
-- less undo space is consumed when checkpoint is done periodically
ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;

-- read only and read write
ALTER TABLE employees READ ONLY;
ALTER TABLE employees READ WRITE;
select TABLE_NAME, READ_ONLY from user_tables where table_name='EMPLOYEES';
Per f or mi ng Onl i ne Redef i ni t i on w i t h DBMS_REDEFI NI TI ON
Redef i ni ng a Tabl e
/*
This example illustrates online redefinition of the previously created table
hr.admin_emp, which at this point only contains columns: empno, ename,
job,deptno.
The table is redefined as follows:
-New columns mgr, hiredate, sal, and bonus are added.
-The new column bonus is initialized to 0
-The column deptno has its value increased by 10.
-The redefined table is partitioned by range on empno.
*/
--1 Verify that the table is a candidate for online redefinition.
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(
UNAME => 'HR',
TNAME => 'ADMIN_EMP',
OPTIONS_FLAG => dbms_redefinition.cons_use_pk); -- OR
DBMS_REDEFINITION.CONS_USE_ROWID
END;
/
-- 2. Create an interim table hr.int_admin_emp.
CREATE TABLE hr.int_admin_emp
(empno NUMBER(5) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(5),
hiredate DATE DEFAULT (sysdate),
sal NUMBER(7,2),
deptno NUMBER(3) NOT NULL,
bonus NUMBER (7,2) DEFAULT(1000))
PARTITION BY RANGE(empno)
(PARTITION emp1000 VALUES LESS THAN (1000) TABLESPACE admin_tbs,
PARTITION emp2000 VALUES LESS THAN (2000) TABLESPACE admin_tbs2);

-- (Optional) If you are redefining a large table
alter session force parallel dml parallel 2;
alter session force parallel query parallel 2;

--3. Start the redefinition process.
BEGIN
Page 134 Oracle DBA Code Examples

DBMS_REDEFINITION.START_REDEF_TABLE(
UNAME => 'HR',
ORIG_TABLE => 'ADMIN_EMP',
INT_TABLE => 'int_admin_emp',
col_mapping=>'empno empno, ename ename, job job, deptno+10 deptno, 0
bonus', -- NULL if same columns in both tables
options_flag => dbms_redefinition.cons_use_pk);
END;
/

-- 4 Copy dependent objects.
DECLARE
N NUMBER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
uname =>'HR',
orig_table =>'admin_emp',
int_table => 'int_admin_emp',
copy_indexes =>dbms_redefinition.cons_orig_params, -- 0 don't copy
copy_triggers =>TRUE,
copy_constraints =>TRUE,
copy_privileges =>TRUE,
ignore_errors => TRUE, -- default FALSE
num_errors => n,
copy_statistics => FALSE, -- default FALSE
copy_mvlog => FALSE); -- default FALSE
-- bcuz ignore_errors=true
if n =0 then
dbms_output.put_line('Success.');
else
dbms_output.put_line('Number of Errors: ' || n);
end if;
END;
/

/* Note that the ignore_errors argument is set to TRUE for this call. The
reason is that the interim table was created with a primary key constraint,
and when COPY_TABLE_DEPENDENTS attempts to copy the primary key constraint
and index from the original table, errors occurs. You can ignore these
errors, but you must run the query shown in the next step to see if there are
other errors. */

-- 5. Query the DBA_REDEFINITION_ERRORS view to check for errors.
select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;

-- 6. Optionally, synchronize the interim table hr.int_admin_emp
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('hr', 'admin_emp', 'int_admin_emp');
END;
/

--7. Complete the redefinition.
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('hr', 'admin_emp', 'int_admin_emp');
END;
/

/* The table hr.admin_emp is locked in the exclusive mode only for a small
Page 135 Oracle DBA Code Examples

window toward the end of this step. After this call the table hr.admin_emp is
redefined such that it has all the attributes of the hr.int_admin_emp table.
*/
-- 8 Wait for any long-running queries against the interim table to complete,
and then drop the interim table.
Redef i ni ng a Si ngl e Par t i t i on
/ *
We want to move the oldest partition of a range-partitioned sales table to a
tablespace named TBS_LOW_FREQ.
The table containing the partition to be redefined is defined as follows:
CREATE TABLE salestable
(s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
TABLESPACE users
PARTITION BY RANGE(s_saledate)
(PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')));
The table has a local partitioned index that is defined as follows:
CREATE INDEX sales_index ON salestable
(s_saledate, s_productid, s_custid) LOCAL;
*/

-- 1. Ensure that salestable is a candidate for redefinition.
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(
uname => 'STEVE',
tname => 'SALESTABLE',
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
part_name => 'sal03q1');
END;
/

-- 2 Create the interim table in the TBS_LOW_FREQ tablespace. Because this is
a redefinition of a range partition, the interim table is non-partitioned.
CREATE TABLE int_salestable
(s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
TABLESPACE tbs_low_freq;

-- 3. Start the redefinition process using rowid.
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'STEVE',
orig_table => 'salestable',
int_table => 'int_salestable',
col_mapping => NULL,
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
part_name => 'sal03q1');
END;
/
Page 136 Oracle DBA Code Examples

-- 4. Manually create any local indexes on the interim table.
CREATE INDEX int_sales_index ON int_salestable
(s_saledate, s_productid, s_custid)
TABLESPACE tbs_low_freq;
-- 5. Optionally synchronize the interim table.
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
uname => 'STEVE',
orig_table => 'salestable',
int_table => 'int_salestable',
part_name => 'sal03q1');
END;
/
-- 6. Complete the redefinition.
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
uname => 'STEVE',
orig_table => 'salestable',
int_table => 'int_salestable',
part_name => 'sal03q1');
END;
/
-- 7. Wait for any long-running queries against the interim table to complete,
and then drop the interim table.
-- The following query shows that the oldest partition has been moved to the
new tablespace:
select partition_name, tablespace_name from user_tab_partitions where
table_name = 'SALESTABLE';
Mi gr at i ng Basi cFi l e LOBs t o Secur eFi l es
-- Grant privileges required for online redefinition.
GRANT EXECUTE ON DBMS_REDEFINITION TO pm;
GRANT ALTER ANY TABLE TO pm;
GRANT DROP ANY TABLE TO pm;
GRANT LOCK ANY TABLE TO pm;
GRANT CREATE ANY TABLE TO pm;
GRANT SELECT ANY TABLE TO pm;

-- Privileges required to perform cloning of dependent objects.
GRANT CREATE ANY TRIGGER TO pm;
GRANT CREATE ANY INDEX TO pm;

CONNECT pm

CREATE TABLE cust(c_id NUMBER PRIMARY KEY,
c_zip NUMBER,
c_name VARCHAR(30) DEFAULT NULL,
c_lob CLOB);
INSERT INTO cust VALUES(1, 94065, 'hhh', 'ttt');

-- Creating Interim Table
-- no need to specify constraints because they are copied over from the
original table
CREATE TABLE cust_int(c_id NUMBER NOT NULL,
c_zip NUMBER,
c_name VARCHAR(30) DEFAULT NULL,
c_lob CLOB)
Page 137 Oracle DBA Code Examples

LOB(c) STORE AS SECUREFILE (...);

declare
col_mapping VARCHAR2(1000);
begin
-- map all the columns in the interim table to the original table
col_mapping :='c_id c_id , '||'c_zip c_zip , '||'c_name c_name, '||'c_lob
c_lob';
DBMS_REDEFINITION.START_REDEF_TABLE('pm', 'cust', 'cust_int', col_mapping);
end;

declare
error_count pls_integer := 0;
begin
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('pm', 'cust', 'cust_int',
1, TRUE,TRUE,TRUE,FALSE, error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
end;

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('pm', 'cust', 'cust_int');

-- Drop the interim table
DROP TABLE cust_int;
DESC cust;

-- to prove that the primary key on the c_id column is preserved after
migration.
INSERT INTO cust VALUES(1, 94065, 'hhh', 'ttt');
SELECT * FROM cust;
Usi ng Fl ashback Dr op and Managi ng t he Recycl e Bi n
See Flashback Drop.
Managi ng I ndex - Or gani zed Tabl es
-- Creating an Index-Organized Table
-- if the length of a row exceeds 20% of the index
-- block size, then the column that exceeded that threshold and
-- all columns after it are moved to the overflow segment.
-- Specify MAPPING TABLE to create a mapping table; it's needed to create a
bitmap index on the IOT
CREATE TABLE admin_docindex(
token char(20),
doc_id NUMBER,
token_frequency NUMBER,
token_offsets VARCHAR2(2000),
CONSTRAINT pk_admin_docindex PRIMARY KEY (token, doc_id))
ORGANIZATION INDEX TABLESPACE admin_tbs
PCTTHRESHOLD 20
OVERFLOW TABLESPACE admin_tbs2;
-- Creating Index-Organized Tables that Contain Object Types
CREATE OR REPLACE TYPE admin_typ AS OBJECT (col1 NUMBER, col2 VARCHAR2(6));
CREATE TABLE admin_iot (c1 NUMBER primary key, c2 admin_typ) ORGANIZATION
INDEX;
Page 138 Oracle DBA Code Examples

CREATE TABLE admin_iot2 OF admin_typ (col1 PRIMARY KEY) ORGANIZATION INDEX;
-- oken_offsets column value is always stored in the overflow area
TABLE admin_docindex2(
token CHAR(20),
doc_id NUMBER,
token_frequency NUMBER,
token_offsets VARCHAR2(2000),
CONSTRAINT pk_admin_docindex2 PRIMARY KEY (token, doc_id))
ORGANIZATION INDEX
TABLESPACE admin_tbs
PCTTHRESHOLD 20 -- default 50
INCLUDING token_frequency
OVERFLOW TABLESPACE admin_tbs2;

-- Parallelizing Index-Organized Table Creation
CREATE TABLE admin_iot3(i PRIMARY KEY, j, k, l)
ORGANIZATION INDEX
PARALLEL AS SELECT * FROM hr.jobs;

-- Altering Index-Organized Tables
ALTER TABLE admin_docindex INITRANS 4 OVERFLOW INITRANS 6;
ALTER TABLE admin_docindex PCTTHRESHOLD 15 INCLUDING doc_id;
-- if there's no existing overflow segment
ALTER TABLE admin_iot3 ADD OVERFLOW TABLESPACE admin_tbs2;

-- Moving (Rebuilding) Index-Organized Tables
ALTER TABLE admin_docindex MOVE; -- just rebuild
ALTER TABLE admin_docindex MOVE ONLINE;
ALTER TABLE admin_iot_lob MOVE LOB (admin_lob) STORE AS (TABLESPACE
admin_tbs3);
-- IOT is rebuilt along with its overflow segment
ALTER TABLE admin_docindex MOVE TABLESPACE admin_tbs2 OVERFLOW TABLESPACE
admin_tbs3;

-- Creating Secondary Indexes on Index-Organized Tables
CREATE INDEX Doc_id_index on Docindex(Doc_id, Token);
Managi ng Ex t er nal Tabl es
/* obtain info about external tables */
select OWNER,TABLE_NAME,TYPE_OWNER,
TYPE_NAME,DEFAULT_DIRECTORY_OWNER,DEFAULT_DIRECTORY_NAME,
REJECT_LIMIT,ACCESS_TYPE,ACCESS_PARAMETERS, PROPERTY
from DBA_EXTERNAL_TABLES;
select OWNER, TABLE_NAME, LOCATION, DIRECTORY_OWNER, DIRECTORY_NAME
from DBA_EXTERNAL_LOCATIONS;


/* Creating an External Table and Loading Data */
CONNECT / AS SYSDBA;
-- Set up directories and grant access to hr
CREATE OR REPLACE DIRECTORY admin_dat_dir AS 'c:\temp\data';
CREATE OR REPLACE DIRECTORY admin_log_dir AS 'c:\temp\log';
CREATE OR REPLACE DIRECTORY admin_bad_dir AS 'c:\temp\bad';
GRANT READ ON DIRECTORY admin_dat_dir TO hr;
GRANT WRITE ON DIRECTORY admin_log_dir TO hr;
Page 139 Oracle DBA Code Examples

GRANT WRITE ON DIRECTORY admin_bad_dir TO hr;
-- hr connects. Provide the user password (hr) when prompted.
CONNECT hr
-- create the external table
CREATE TABLE admin_ext_employees
(employee_id NUMBER(4),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
job_id VARCHAR2(10),
manager_id NUMBER(4),
hire_date DATE,
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
department_id NUMBER(4),
email VARCHAR2(25))
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER -- or ORACLE_DATAPUMP (for unload)
DEFAULT DIRECTORY admin_dat_dir
ACCESS PARAMETERS
(
records delimited by newline
badfile admin_bad_dir:'empxt%a_%p.bad'
logfile admin_log_dir:'empxt%a_%p.log'
fields terminated by ','
missing field values are null
( employee_id, first_name, last_name, job_id, manager_id,
hire_date char date_format date mask "dd-mon-yyyy",
salary, commission_pct, department_id, email )
)
LOCATION ('empxt1.dat', 'empxt2.dat')
)
PARALLEL -- useful to huge data
REJECT LIMIT UNLIMITED;

-- more options
-- in ACCESS PARAMETERS you can add
LOAD WHEN (job != MANAGER)

-- using SQL*Loader to generated script for creating external tables
-- (1) prepare control file such as:
LOAD DATA
INFILE *
INTO TABLE test_emp
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(employee_id,first_name,last_name,hire_date,salary,manager_id)
BEGINDATA
12345,"sam","alapati",sysdate,50000,99999
23456,"mark","potts",sysdate,50000,99999
-- (2) issue SQL*Loader using EXTERNAL_TABLE =GENERATE_ONLY NOT_USED EXECUTE
sqlldr USERID=system/sammyy1 CONTROL=test.ctl \
EXTERNAL_TABLE=GENERATE_ONLY
-- (3) check the generated log file


/* Unloading into External Tables */
-- (using datapump drivers)
Page 140 Oracle DBA Code Examples

CREATE TABLE test_xt
ORGANIZATION EXTERNAL(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY ext_data_dir
ACCESS PARAMETERS (COMPRESSION ENABLED)
LOCATION ('test_xt.dmp'))
AS
SELECT * FROM scott.dept;

-- encrypting loaded data
CREATE TABLE TEST
ORGANIZATION EXTERNAL (TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY test_dir1
ACCESS PARAMETERS (ENCRYPTION ENABLED) LOCATION ('test.dmp'));

-- unload into multiple files
CREATE TABLE customers
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY ext_data_dir ACCESS PARAMETERS
(NOBADFILE NOLOGFILE)
LOCATION ('customers1.exp', 'customers2.exp', 'customers3.exp',
'customers4.exp'))
PARALLEL 4 REJECT LIMIT UNLIMITED AS
SELECT c.*, co.country_name, co.country_subregion, co.country_region
FROM customers c, countries co where co.country_id=c.country_id;


-- enable parallel for loading (good if lots of data to load)
ALTER SESSION ENABLE PARALLEL DML;

-- load the data in hr employees table
INSERT INTO emp (employee_id, first_name, last_name, job_id, manager_id,
hire_date, salary, commission_pct, department_id, email)
SELECT * FROM admin_ext_employees;


/* Altering External Tables */
ALTER TABLE admin_ext_employees REJECT LIMIT 100;

-- access driver processes only the columns in the select list (default)
ALTER TABLE admin_ext_employees PROJECT COLUMN REFERNCED;
-- the access driver processes all of the columns
-- This setting always provides a consistent set of returned rows
ALTER TABLE admin_ext_employees PROJECT COLUMN ALL;

-- default directory
ALTER TABLE admin_ext_employees DEFAULT DIRECTORY admin_dat2_dir;

-- access parameter
ALTER TABLE admin_ext_employees ACCESS PARAMETERS (FIELDS TERMINATED BY ';');

-- location
ALTER TABLE admin_ext_employees LOCATION ('empxt3.txt', 'empxt4.txt');
Page 141 Oracle DBA Code Examples

Managi ng I ndex es
Usi ng I ndex es
When creat ing an ext remely large index, consider allocat ing a larger t emporary t ablespace
specially for t he index creat ion.
I f you want t he opt imizer t o consider t he invisible indexes in it s operat ion, you can set t he
new init ializat ion paramet er OPTI MI ZER_USE_I NVI SI BLE_I NDEXES t o TRUE ( t he default is
FALSE) . You can set t he paramet er in t he syst em and session levels.
-- obtain info about indexes
select * from DBA_INDEXES;
select * from DBA_IND_COLUMNS;
-- for funcation-based indexes
select * from DBA_IND_EXPRESSIONS;
-- optimizer stats
select * from DBA_IND_STATISTICS;
-- obtained from ANALYZE INDEX...VALIDATE STRUCTURE
select * from INDEX_STATS;

-- create index
CREATE INDEX emp_ename ON emp(ename)
TABLESPACE users
STORAGE (INITIAL 20K
NEXT 20k
PCTINCREASE 75);
CREATE INDEX emp_ename ON emp(ename) COMPUTE STATISTICS;
CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;
CREATE UNIQUE INDEX dept_unique_index ON dept (dname) TABLESPACE indx;
CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY
KEY USING INDEX TABLESPACE users;
CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));
CREATE TABLE b( b1 INT, b2 INT,
CONSTRAINT bu1 UNIQUE (b1, b2)
USING INDEX (create unique index bi on b(b1, b2)),
CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);
CREATE TABLE c(c1 INT, c2 INT);
CREATE INDEX ci ON c (c1, c2);
ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;

-- bitmap index
CREATE BITMAP INDEX gender_idx ON employee(gender) TABLESPACE emp_index_05;

-- Reverse-Key Indexes
-- good when you do sequential insertion of values into the index
CREATE INDEX reverse_idx ON employee(emp_id) REVERSE;

-- function based
CREATE INDEX area_index ON rivers (area(geo));

-- compress
CREATE INDEX emp_ename ON emp(ename) TABLESPACE users COMPRESS 1;
ALTER INDEX emp_ename REBUILD NOCOMPRESS;

-- Invisible Index (11g)
CREATE INDEX name_indx ON employees(emp_name) INVISIBLE;
Page 142 Oracle DBA Code Examples

SELECT /*+ index (EMP_NAME NAME_INDEX) */ ...
ALTER INDEX name_indx VISIBLE;
ALTER INDEX name_indx INVISIBLE;
SELECT INDEX_NAME, VISIBILITY FROM DBA_INDEXES WHERE INDEX_NAME='NAME_INDX';

-- Bitmap Join Index (see next section).

-- rebuilding an index
ALTER INDEX emp_name REBUILD;
ALTER INDEX emp_name REBUILD ONLINE;

-- Monitoring Index Usage
ALTER INDEX index MONITORING USAGE;
ALTER INDEX index NOMONITORING USAGE;
Select INDEX_NAME,TABLE_NAME,MONITORING,USED,START_MONITORING,END_MONITORING
From V$OBJECT_USAGE -- in the same session
WHERE INDEX_NAME='EMP_LNAME_IND';

-- Monitoring Space Use of Indexes
-- 1. Analyzing statistics
exec DBMS_STATS.GATHER_INDEX_STATS( OWNNAME=>'HR', INDNAME=>'EMP_LNAME_IND')
-- 2. Validating the index
ANALYZE INDEX EMP_LNAME_IND VALIDATE STRUCTURE;
-- 3. Checking PCT_USED
SELECT PCT_USED FROM INDEX_STATS WHERE NAME = 'EMP_LNAME_IND';
-- 4. Dropping and rebuilding (or coalescing) the index
ALTER INDEX emp_name REBUILD;

-- estimating index space
-- gather index and its underlying table statistics
SET SERVEROUTPUT ON
declare
l_index_ddl VARCHAR2(1000);
l_used_bytes NUMBER;
l_allocated_bytes NUMBER;
BEGIN
DBMS_SPACE.create_index_cost (
DDL => 'create index persons_idx on persons(person_id)',
USED_BYTES => l_used_bytes,
ALLOC_BYTES => l_allocated_bytes);
DBMS_OUTPUT.PUT_LINE ('used = ' || to_char(l_used_bytes/1024) || ' KB ' || '
allocated = ' || to_char(l_allocated_bytes/1024) || ' KB');
END;
/
Usi ng Bi t map Joi n I ndex es ( BJI )
A bit map j oin index is a bit map index for t he j oin of t wo or more t ables. I t is designed
when j oining fact t ables wit h dimension t ables as in warehouse dat abases (st art schema
model) .
CREATE BITMAP INDEX NAMES_CTR_bji
ON NAMES2(COUNTRIES.COUNTRY_NAME)
FROM NAMES2, COUNTRIES
WHERE NAMES2.NAT_ID=COUNTRIES.ID
NOLOGGING COMPUTE STATISTICS;

Page 143 Oracle DBA Code Examples

SELECT /*+ INDEX_COMBINE(N NAMES_CTR_bji)*/
C.COUNTRY_NAME
FROM NAMES2 N, COUNTRIES C
WHERE N.NAT_ID = C.ID;
Par t i t i oned I ndex es
-- Global Indexes
-- can be nonpartitioned or partitioned indexes
CREATE INDEX ticketsales_idx ON
ticket_sales(month) GLOBAL
PARTITION BY range(month)
(PARTITION ticketsales1_idx VALUES LESS THAN (3)
PARTITION ticketsales1_idx VALUES LESS THAN (6)
PARTITION ticketsales2_idx VALUES LESS THAN (9)
PARTITION ticketsales3_idx VALUES LESS THAN (MAXVALUE));

CREATE INDEX hgidx
ON tab (c1,c2,c3) GLOBAL
PARTITION BY HASH (c1,c2)
(PARTITION p1 TABLESPACE ts1,
PARTITION p2 TABLESPACE ts2,
PARTITION p3 TABLESPACE ts3,
PARTITION p4 TABLESPACE ts4);

-- Local Indexes
CREATE INDEX ticket_no_idx ON
ticket_sales(ticket__no) LOCAL TABLESPACE localidx_01;
Managi ng Mat er i al i zed Vi ew s
Usi ng Mat er i al i zed Vi ew s
When creat ing mat erialized view, you should keep in mind t he following rest rict ions:
The defining query of t he mat erialized view cannot cont ain any non- repeat able
expressions ( ROWNUM, SYSDATE, non- repeat able PL/ SQL funct ions, and so on) .
The query cannot cont ain any references t o RAW or LONG RAW dat at ypes or obj ect REFs
Rest rict ions on Fast Refresh
The defining query of t he mat erialized view is rest rict ed as follows:
The mat erialized view must not cont ain references t o non- repeat ing expressions like
SYSDATE and ROWNUM.
The mat erialized view must not cont ain references t o RAW or LONG RAW dat a t ypes.
I t cannot cont ain a SELECT list subquery.
I t cannot cont ain analyt ical funct ions ( for example, RANK) in t he SELECT clause.
I t cannot cont ain a MODEL clause.
I t cannot cont ain a HAVI NG clause wit h a subquery.
I t cannot cont ain nest ed queries t hat have ANY, ALL, or NOT EXI STS.
I t cannot cont ain a [ START WI TH ] CONNECT BY clause.
I t cannot cont ain mult iple det ail t ables at different sit es.
ON COMMI T mat erialized views cannot have remot e det ail t ables.
Nest ed mat erialized views must have a j oin or aggregat e.
Page 144 Oracle DBA Code Examples

-- required privs
GRANT CREATE MATERIALIZED VIEW TO hr;
GRANT QUERY REWRITE TO hr;

-- mv log
-- you must specify the ROWID, SEQUENCE and the INCLUDING NEW VALUES
-- For aggregate materialized views, it must also contain every column
-- in the table referenced in the materialized view
CREATE MATERIALIZED VIEW LOG
ON employees WITH SEQUENCE, ROWID
(employee_id,department_id,salary) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG
ON departments WITH SEQUENCE, ROWID
(department_id, department_name) INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG ON sales ADD ROWID
(prod_id, cust_id, time_id, amount_sold) INCLUDING NEW VALUES;

DROP MATERIALIZED VIEW LOG ON sales;

-- create mv
-- the options
BUILD IMMEDIATE | DEFERRED
REFRESH NEVER, FAST, FORCE, COMPLETE - on commit or on demand
ENABLE QUERY REWRITE -- is a must for rewriting queries. DISABLE
WITH PRIMARY KEY
START WITH ROUND(SYSDATE + 1) + 11/24 NEXT NEXT_DAY(TRUNC(SYSDATE), 'MONDAY')
+ 15/24
recommended to gather stats on it then
EXECUTE DBMS_STATS.GATHER_TABLE_STATS ( 'HR','DEPT01_MV', estimate_percent
=> 20, block_sample => TRUE, cascade => TRUE);

-- (1) Materialized Views with Aggregates
-- vm log must also contain every column
-- in the table referenced in the materialized view
-- there must be a COUNT(*) and a COUNT(column) on any aggregated columns
CREATE MATERIALIZED VIEW dept01_mv
(department_id, department_name, salary_count, average_salary)
TABLESPACE ts1
BUILD IMMEDIATE
REFRESH FAST
ENABLE QUERY REWRITE
AS
select d.department_id, d.department_name,
count(e.salary), -- it is a must for mv with aggregate or count(*),
avg(e.salary)
from departments d, employees e
where d.department_id = e.department_id
group by d.department_id, d.department_name;

CREATE MATERIALIZED VIEW product_sales_mv
TABLESPACE ts1
BUILD IMMEDIATE -- DEFERRED
REFRESH FAST -- FAST, FORCE, COMPLETE - on commit or on demand
ENABLE QUERY REWRITE
AS
Page 145 Oracle DBA Code Examples

SELECT p.prod_name, SUM(s.amount_sold) AS dollar_sales,
COUNT(*) AS cnt, COUNT(s.amount_sold) AS cnt_amt
FROM sales s, products p
WHERE s.prod_id = p.prod_id GROUP BY p.prod_name;


-- (2) Materialized Views Containing Only Joins
-- contain only joins and no aggregates
-- ROWID column must be present in each materialized view log and mv SELECT
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON times WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID;
CREATE MATERIALIZED VIEW detail_sales_mv
PARALLEL
BUILD IMMEDIATE
REFRESH FAST AS
SELECT s.rowid "sales_rid", t.rowid "times_rid", c.rowid "customers_rid",
c.cust_id, c.cust_last_name, s.amount_sold, s.quantity_sold, s.time_id
FROM sales s, times t, customers c
WHERE s.cust_id = c.cust_id(+) AND s.time_id = t.time_id(+);

-- (3) Nested Materialized Views
-- a materialized view whose definition is based on another materialized view
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON times WITH ROWID;
/*create materialized view join_sales_cust_time as fast refreshable at
COMMIT time */
CREATE MATERIALIZED VIEW join_sales_cust_time
REFRESH FAST ON COMMIT AS
SELECT c.cust_id, c.cust_last_name, s.amount_sold, t.time_id,
t.day_number_in_week, s.rowid srid, t.rowid trid, c.rowid crid
FROM sales s, customers c, times t
WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id;
/* create materialized view log on join_sales_cust_time
and include the required columns */
CREATE MATERIALIZED VIEW LOG ON join_sales_cust_time
WITH ROWID (cust_last_name, day_number_in_week, amount_sold)
INCLUDING NEW VALUES;
/* create the single-table aggregate materialized view sum_sales_cust_time
on join_sales_cust_time as fast refreshable at COMMIT time */
CREATE MATERIALIZED VIEW sum_sales_cust_time
REFRESH FAST ON COMMIT AS
SELECT COUNT(*) cnt_all, SUM(amount_sold) sum_sales, COUNT(amount_sold)
cnt_sales, cust_last_name, day_number_in_week
FROM join_sales_cust_time
GROUP BY cust_last_name, day_number_in_week;
-- Refreshing a Nested Materialized View
DBMS_MVIEW.REFRESH('SALES_MV,COST_MV', nested => TRUE);

/* commenting on mvs */
COMMENT ON MATERIALIZED VIEW sales_mv IS 'sales materialized view';
SELECT MVIEW_NAME, COMMENTS
FROM USER_MVIEW_COMMENTS WHERE MVIEW_NAME = 'SALES_MV';
Usi ng Quer y Rew r i t i ng
A query is rewrit t en only when a cert ain number of condit ions are met :
Page 146 Oracle DBA Code Examples

o Query rewrit e must be enabled for t he session.
o A mat erialized view must be enabled for query rewrit e.
o The rewrit e int egrit y level should allow t he use of t he mat erialized view.
Use Column Alias List s in t he CREATE MATERI ALI ZED VI EW command t o let t he query
rewrit e work
/* Required Privs */
grant query rewrite to hr;
-- if the mv uses a table from other schema
grant query rewrite on employees to scott;
or
grant global query rewrite to hr;

/* Ensuring that Query Rewrite Takes Effect */
-- (1) query rewrite enabled for the mv
select REWRITE_ENABLED from user_mviews where mview_name='DEPT01_MV';
ALTER MATERIALIZED VIEW dept01_mv ENABLE QUERY REWRITE ;
-- (2) QUERY_REWRITE_ENABLED = TRUE | FORCE
ALTER SESSION SET QUERY_REWRITE_ENABLED=true;
select get_par('query_rewrite_enabled') from dual;
-- for user with dba priv
select value from v$parameter where upper(name)='QUERY_REWRITE_ENABLED';
alter system set QUERY_REWRITE_ENABLED=TRUE;
-- (3) OPTIMIZER_MODE = ALL_ROWS, FIRST_ROWS, or FIRST_ROWS_n, (where n = 1,
10, 100, 1000)
select get_par('OPTIMIZER_MODE') from dual;
-- (4) OPTIMIZER_FEATURES_ENABLE must be 10.0.0 or higher
select value from v$parameter where upper(name)='OPTIMIZER_FEATURES_ENABLE';

/* Column Alias */
-- failed example
CREATE MATERIALIZED VIEW sales_mv
ENABLE QUERY REWRITE AS
SELECT s.time_id sales_tid, c.time_id costs_tid
FROM sales s, products p, costs c
WHERE s.prod_id = p.prod_id AND c.prod_id = p.prod_id AND
p.prod_name IN (SELECT prod_name FROM products);
-- the right way
CREATE MATERIALIZED VIEW sales_mv (sales_tid, costs_tid)
ENABLE QUERY REWRITE AS
SELECT s.time_id, c.time_id
FROM sales s, products p, costs c
WHERE s.prod_id = p.prod_id AND c.prod_id = p.prod_id AND
p.prod_name IN (SELECT prod_name FROM products);

/* Rewrite Integrity */
-- QUERY_REWRITE_INTEGRITY: ENFORCED (returned data is 100% correct), TRUSTED,
STALE_TOLERATED

/* Verifying that Query Rewrite has Occurred */
EXPLAIN PLAN FOR
select d.department_id, d.department_name,
count(e.salary),
avg(e.salary)
from departments d, employees e
where d.department_id = e.department_id
group by d.department_id, d.department_name;
Page 147 Oracle DBA Code Examples


-- if rewriting occurred, you should see operation: MAT_VIEW REWRITE ACCESS
SELECT OPERATION, OBJECT_NAME FROM PLAN_TABLE;

/* Using the EXPLAIN_REWRITE Procedure with Query Rewrite */
-- target: to know why rewriting didn't occur
-- 1) create EXPLAIN_REWRITE table
@/oracle/oradb10g/rdbms/admin/utlxrw.sql
-- 2) execute
declare
v varchar2(4000);
begin
v := 'select d.department_id, d.department_name, count(e.salary),
avg(e.salary) from departments d, employees e where d.department_id =
e.department_id group by d.department_id, d.department_name';
DBMS_MVIEW.EXPLAIN_REWRITE(
query => v,
mv =>'', -- you can specify the comma-separated mv(s) or NULL to consider all
mvs
statement_id => 'ID6'); -- client-supplied unique identifier to distinguish
output messages
end;
/

SELECT message, original_cost, rewritten_cost
FROM rewrite_table ORDER BY sequence;

-- alternatively to see neat output use SYS.XRW (see Wareshouse documentation)
ReWr i t e Hi nt s
/* REWRITE and NOREWRITE Hints */
SELECT /*+ NOREWRITE */ ...
SELECT /*+ REWRITE (sum_sales_pscat_week_mv) */ ...

/* The Rewrite_or_Error Hint */
-- use the mv or error
SELECT /*+ REWRITE_OR_ERROR */
prod_id, SUM(quantity_sold) AS sum_sales_qty
FROM sales_data
GROUP BY prod_id;

/* NO_MULTIMV_REWRITE hint prevents the query from being rewritten with more
than one materialized
NO_BASETABLE_MULTIMV_REWRITE hint prevents the query from being rewritten
with a combination of materialized views and the base tables
*/
Usi ng EXPLAI N_MVI EW Pr ocedur e: Vi ew i ng Mat er i al i zed Vi ew Capabi l i t i es
Target : t o det ermine if a mat erialized view is fast refreshable and what t ypes of query
rewrit e you can perform wit h a part icular mat erialized view.

DBMS_MVIEW.EXPLAIN_MVIEW (
mv -- mv, a SELECT or a CREATE MATERIALIZED VIEW statement
statement_id -- any id

--(1) create MV_CAPABILITIES_TABLE
Page 148 Oracle DBA Code Examples

@/oracle/oradb10g/rdbms/admin/utlxmv.sql
--(2) execute
declare
v varchar2(4000);
begin
v := 'DEPT01_MV';
DBMS_MVIEW.EXPLAIN_MVIEW( V,'ID01');
end;
/

SELECT CAPABILITY_NAME, POSSIBLE, RELATED_TEXT,RELATED_NUM, MSGNO, MSGTXT
FROM MV_CAPABILITIES_TABLE
WHERE STATEMENT_ID='ID01'
ORDER BY SEQ
/

SELECT MSGTXT
FROM MV_CAPABILITIES_TABLE
WHERE STATEMENT_ID='ID01'
ORDER BY SEQ
/
Regi st er i ng a User - def i ned Tabl e as Mat er i al i zed Vi ew
-- table and mv have the same name
CREATE TABLE sum_sales_tab
PCTFREE 0 TABLESPACE demo
AS
SELECT s.prod_id, SUM(amount_sold) AS dollar_sales, SUM(quantity_sold) AS
unit_sales FROM sales s GROUP BY s.prod_id;

CREATE MATERIALIZED VIEW sum_sales_tab_mv
ON PREBUILT TABLE WITHOUT REDUCED PRECISION
ENABLE QUERY REWRITE AS
SELECT s.prod_id, SUM(amount_sold) AS dollar_sales,
SUM(quantity_sold) AS unit_sales
FROM sales s GROUP BY s.prod_id;
Page 149 Oracle DBA Code Examples

Managi ng Cl u st er s and Hash Cl ust er s
Hashing is useful when you have t he following condit ions:
Most queries are equalit y queries on t he clust er key:
SELECT . .. WHERE clust er_key = .. . ;
The t ables in t he hash clust er are primarily st at ic in size so t hat you can det ermine t he
number of rows and amount of space required for t he t ables in t he clust er.
-- obtaining info on clusters
select * from DBA_CLUSTERS;
-- map table columns to cluster columns
select * from DBA_CLU_COLUMNS;

-- create cluster
CREATE CLUSTER emp_dept (deptno NUMBER(3))
SIZE 600
TABLESPACE users
STORAGE (INITIAL 200K
NEXT 300K
MINEXTENTS 2
MAXEXTENTS 20
PCTINCREASE 33);
CREATE TABLE emp (
empno NUMBER(5) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
. . .
deptno NUMBER(3) REFERENCES dept)
CLUSTER emp_dept (deptno);
CREATE TABLE dept (
deptno NUMBER(3) PRIMARY KEY, . . . )
CLUSTER emp_dept (deptno);

-- create cluster index
CREATE INDEX emp_dept_index
ON CLUSTER emp_dept
TABLESPACE users
STORAGE (INITIAL 50K
NEXT 50K
MINEXTENTS 2
MAXEXTENTS 10
PCTINCREASE 33);

-- dropping a cluster
-- no tables
DROP CLUSTER emp_dept;
-- there are tables
DROP CLUSTER emp_dept INCLUDING TABLES;
DROP CLUSTER emp_dept INCLUDING TABLES CASCADE CONSTRAINTS;
-- drop a table in the cluster
drop table dept;

/* Managing Hash Clusters */

-- obtain info
selet * from DBA_CLUSTERS ;
Page 150 Oracle DBA Code Examples

selet * from DBA_CLU_COLUMNS;
selet * from DBA_CLUSTER_HASH_EXPRESSIONS;

-- Creating Hash Clusters
CREATE CLUSTER trial_cluster (trialno NUMBER(5,0))
TABLESPACE users
STORAGE (INITIAL 250K NEXT 50K
MINEXTENTS 1 MAXEXTENTS 3
PCTINCREASE 0)
HASH IS trialno HASHKEYS 150;
CREATE TABLE trial (
trialno NUMBER(5,0) PRIMARY KEY,
...)
CLUSTER trial_cluster (trialno);

-- Creating a Sorted Hash Cluster
CREATE CLUSTER call_detail_cluster (
telephone_number NUMBER,
call_timestamp NUMBER SORT,
call_duration NUMBER SORT )
HASHKEYS 10000 HASH IS telephone_number
SIZE 256;
CREATE TABLE call_detail (
telephone_number NUMBER,
call_timestamp NUMBER SORT,
call_duration NUMBER SORT,
other_info VARCHAR2(30) )
CLUSTER call_detail_cluster (
telephone_number, call_timestamp, call_duration );
-- following select gets advantage from the sorted hash cluster
SELECT * WHERE telephone_number = 6505551212;

-- Creating Single-Table Hash Clusters
CREATE CLUSTER peanut (variety NUMBER)
SIZE 512 SINGLE TABLE HASHKEYS 500;

-- Dropping Hash Clusters
DROP CLUSTER emp_dept;
Page 151 Oracle DBA Code Examples

Managi ng Vi ew s, Sequen ces, and Sy nony ms
-- create views
CREATE VIEW sales_staff AS
SELECT empno, ename, deptno FROM emp WHERE deptno = 10
WITH CHECK OPTION CONSTRAINT sales_staff_cnst;
-- Creating Views with Errors
CREATE FORCE VIEW AS ...;

-- creating sequence
CREATE SEQUENCE emp_sequence
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;
-- alter a sequence
ALTER SEQUENCE emp_sequence
INCREMENT BY 10
MAXVALUE 10000
CYCLE
CACHE 20;

-- Creating Synonyms
CREATE PUBLIC SYNONYM public_emp FOR jward.emp;
Page 152 Oracle DBA Code Examples

Managi ng Tr an sact i on s
I mpl ement i ng Or acl e s Concur r ency Cont r ol
Or acl e I sol act i on Lev el s
There are four possible isolat ion levels:
o Serializable: doesnt allow any concurrent .
o Repeat able read
o Read uncommit t ed
o Read commit t ed ( Default ) : Oracle only guarant ees st at ement - level isolat ion here
( changes bet ween reads are seen), not t ransact ion- level isolat ion.

command I t s effect
SET TRANSACTION READ
ONLY
When set , t he t ransact ion t hat follows operat es on essent ially
a snapshot of t he dat abase at t he t ime t he command was
issued. This is especially useful when mult iple select
st at ement s are execut ed over t he course of a t ransact ion, and
dat a must be consist ent .
SET TRANSACTION READ
WRITE
Set t he t ransact gion back t o read writ e
SET TRANSACTION
ISOLATION LEVEL READ
COMMITTED
if we t ry t o modify a record t hat already has a DML Row
Exclusive lock on t hat record, t he at t empt t o updat e will wait
unt il t he locks are released.
SET TRANSACTION
ISOLATION LEVEL
SERIALIZABLE
if we t ry t o modify a record t hat already has a DML Row
Exclusive lock on t hat record, t he at t empt t o updat e will fail

-- must be the first command in an transaction
-- to enable transaction-level isolation
ALTER SESSION SET ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- alternatively ( repeatable reads )
SET TRANSACTION READ ONLY
Or acl e Lock Types
DML Locks: Row- Level Lock: Exclusive Table- Level Lock: Row exclusive
DDL Locks: DDL is not allowed if DML Lock was t here and "ORA- 00054: resource bus" will
be ret urned unless DDL_LOCK_TI MEOUT is defined in seconds ( 11g) .
Explicit Table Locking: DDL requires Table exclusive DML lock. You can specify t hat a DDL
command wait for a specific lengt h of t ime before it fails:
LOCK TABLE ... IN lockmode MODE [NOWAIT | WAIT integer]
lockmode: [ROW] SHARE, [[SHARE] ROW] EXCLUSIVE, SHARE UPDATE
int eger in seconds
Lat ches: are int ernal mechanisms t hat prot ect shared dat a st ruct ures in t he SGA.
Dat a dict ionary locks: whenever t he dict ionary obj ect s are being modified.
Page 153 Oracle DBA Code Examples

Dist ribut ed locks: used in a dist ribut ed dat abase syst em or in RAC.
I nt ernal locks: are used by Oracle t o prot ect access t o st ruct ures such as dat afiles,
t ablespaces, and rollback segment s.
-- current locks in the DB
select SID,
DECODE(TO_CHAR(BLOCK),'0','Not-Blocking','1','Blocking') IS_BLOCKING,
DECODE(TYPE,'TM','DML enqueue','TX','Transaction enqueue','UL','User
supplied',TYPE) LOCK_TYPE,
DECODE(TO_CHAR(LMODE),'0','None','1','Null','2','Row-S (SS)','3','Row-X
(SX)','4','Share (S)','5','S/Row-X (SSX)','6','Exclusive (X)') HELD_LMODE,
DECODE(TO_CHAR(REQUEST),'0','None','1','Null','2','Row-S (SS)','3','Row-X
(SX)','4','Share (S)','5','S/Row-X (SSX)','6','Exclusive (X)') REQUEST_LMODE
from v$lock;

-- Oracle 11g: to allow DDL wait for lock instead or returning error
ALTER SESSION SET ddl_lock_timeout = 30;

-- explicit table locking
lock table emp in EXCLUSIVE mode nowait ;
I dent i f y i ng Bl ock i ng Sessi ons
-- oracle supplied script printing blocking sessions in tree-like view
@$ORACLE_HOME/rdbms/admin/utllockt.sql

select SID,
DECODE(TYPE,'TM','DML enqueue','TX','Transaction enqueue','UL','User
supplied',TYPE) LOCK_TYPE,
DECODE(TO_CHAR(LMODE),'0','None','1','Null','2','Row-S (SS)','3','Row-X
(SX)','4','Share (S)','5','S/Row-X (SSX)','6','Exclusive (X)') HELD_LMODE,
DECODE(TO_CHAR(REQUEST),'0','None','1','Null','2','Row-S (SS)','3','Row-X
(SX)','4','Share (S)','5','S/Row-X (SSX)','6','Exclusive (X)') REQUEST_LMODE
from v$lock
where BLOCK=1;

SELECT sid, username, blocking_session blocking_sid
FROM V$SESSION WHERE blocking_session_status='VALID';
Usi ng Aut onomous Tr ansact i on
The aut onomous t ransact ions give you t he abilit y t o commit or roll back t he subprograms
changes independent of t he main program.
-- write errors log
CREATE OR REPLACE PROCEDURE error_log
(error__msg in varchar2, procedure_name IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO log_table (error_msg, procedure_name)
VALUES (error_msg,procedure_name));
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END error_log;
/

Page 154 Oracle DBA Code Examples

-- using DDL statements in triggers
-- performing an audit of database queries
-- performing an audit of and failed (unauthorized) database activity
CREATE OR REPLACE TRIGGER aud_bef_trig
BEFORE INSERT ON emp FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION
BEGIN
INSERT INTO audit_employee VALUES (
:new.username, 'before insert', sysdate);
COMMIT;
END;
/
Managi ng Long Tr ansact i ons w i t h Wor kspace Manager
Workspace Manager is by default inst alled when you creat e a dat abase using DBCA but
not manually. To check, make sure WMSYS exist s in DBA_USERS.
Wit h Workspace Manager, you can maint ain mult iple versions of dat a, which you can keep
or discard as necessary.
I t s benifit s:
o To enable simult aneous read and writ e access t o dat a during long t ransact ions.
o To creat e mult iple dat a scenarios for what - if analyses.
The original t able will be renamed t o t ableName_LT, new t able t ableName_AUX will be
creat ed. Users be default are placed in LI VE workspace.
Ref r esh: updat e t he dat a in t he refreshed workspace wit h t he changes made in it s parent
workspace.
Mer ge: updat e t he parent workspace wit h t he changes made t o t he merged workspace.
Reference: Applicat ion Developers Guide - Workspace Manager
-- should return LIVE
select dbms_wm.getworkspace from dual;

/* go back in time within a workspace */
-- the example is using the LIVE workspace

-- privs
begin
dbms_wm.grantSystemPriv (
'ACCESS_ANY_WORKSPACE, ' ||
'MERGE_ANY_WORKSPACE, ' ||
'CREATE_ANY_WORKSPACE, ' ||
'REMOVE_ANY_WORKSPACE, ' ||
'ROLLBACK_ANY_WORKSPACE' ,
'USER1' ,
'YES');
end;
/


conn user1

create table dept
Page 155 Oracle DBA Code Examples

( DEPTNO NUMBER(3) PRIMARY KEY,
DNAME VARCHAR2(14) )
/

create table EMP
( EMPNO NUMBER(4) PRIMARY KEY, -- PK is mandatory
ENAME VARCHAR2(10),
DEPTNO NUMBER(3) references dept(deptno))
/


-- set version enabled tables
-- VIEW_WO_OVERWRITE = without Overwrite
begin
dbms_wm.enableVersioning('EMP','VIEW_WO_OVERWRITE');
dbms_wm.enableVersioning('DEPT','VIEW_WO_OVERWRITE');
end;
/

select TABLE_NAME, STATE, HISTORY
from USER_WM_VERSIONED_TABLES
order by TABLE_NAME ;


-- testing data
-- Note: rows cannot be inserted if the Workspace isn't
-- in its latest version by DBMS_WM.GotoSavePoint();
insert into dept values (1,'d1');
commit;
insert into emp values (1,'e1',1);
insert into emp values (2,'e2',1);
commit;

select * from emp, dept where emp.deptno=dept.deptno;

-- save current time
var dt_1 varchar2(21)

begin
select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') into :dt_1 from dual;
end;
/

-- insert extra rows
insert into dept values(2,'d2');
commit;
insert into emp values(3,'e3',2);
insert into emp values(4,'e4',2);
commit;

select * from emp, dept where emp.deptno=dept.deptno;

-- return to dt_1 time
begin
dbms_wm.gotoDate(to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss'));
end;
/
Page 156 Oracle DBA Code Examples

commit;

select * from emp, dept where emp.deptno=dept.deptno;

-- disable versioning
begin
dbms_wm.disableVersioning('DEPT');
dbms_wm.disableVersioning('EMP');
end;
/

/* Creating Workspaces and Merging/Refreshing Data */
-- privs
begin
dbms_wm.grantSystemPriv (
'ACCESS_ANY_WORKSPACE, ' ||
'MERGE_ANY_WORKSPACE, ' ||
'CREATE_ANY_WORKSPACE, ' ||
'REMOVE_ANY_WORKSPACE, ' ||
'ROLLBACK_ANY_WORKSPACE' ,
'USER1' ,
'YES');
end;
/


conn user1

create table dept
( DEPTNO NUMBER(3) PRIMARY KEY,
DNAME VARCHAR2(14) )
/

create table EMP
( EMPNO NUMBER(4) PRIMARY KEY, -- PK is mandatory
ENAME VARCHAR2(10),
DEPTNO NUMBER(3) references dept(deptno))
/

insert into dept values (1,'d1');
commit;
insert into emp values (1,'e1',1);
insert into emp values (2,'e2',1);
commit;

-- set version enabled tables
-- VIEW_WO_OVERWRITE = without Overwrite
-- this option make WAREHOUSES_HIST view contain
-- complete history information about data changes
begin
-- both table must be version enabled because they are linked
dbms_wm.enableVersioning('DEPT,EMP','VIEW_WO_OVERWRITE');
end;
/

-- create workspaces
begin
Page 157 Oracle DBA Code Examples

dbms_wm.createworkspace ('WS1');
dbms_wm.createworkspace ('WS2');
end;
/

select workspace, parent_workspace from user_workspaces;

-- move logged on user to WS1
begin
dbms_wm.gotoworkspace('WS1');
end;
/

select * from emp, dept where emp.deptno=dept.deptno;

-- insert extra rows
insert into dept values(2,'d2');
commit;
insert into emp values(3,'e3',2);
insert into emp values(4,'e4',2);
commit;

select * from emp, dept where emp.deptno=dept.deptno;

-- go back to Live workspace
begin
dbms_wm.gotoworkspace('LIVE');
end;
/

select * from emp, dept where emp.deptno=dept.deptno;

-- change some data within the LIVE workspaces
insert into dept values(3,'d3');
commit;
insert into emp values(5,'e5',3);
insert into emp values(6,'e6',3);
commit;


select * from emp, dept where emp.deptno=dept.deptno;

-- move to Workspace WS2
begin
dbms_wm.gotoworkspace('WS2');
end;
/

-- you won't see changes made in the LIVE workspace nor the changes
-- made in the WS1 workspace.
select * from emp, dept where emp.deptno=dept.deptno;

insert into dept values(4,'d3');
commit;
insert into emp values(7,'e7',4);
insert into emp values(8,'e8',4);
commit;
Page 158 Oracle DBA Code Examples


-- REFRESH WS1
begin
dbms_wm.refreshworkspace('WS1');
end;
/

-- move to WS1
begin
dbms_wm.gotoworkspace('WS1');
end;
/

select * from emp, dept where emp.deptno=dept.deptno;

-- MERGE WS2
begin
dbms_wm.mergeworkspace('WS2');
end;
/

-- verify merge succeeded
begin
dbms_wm.gotoworkspace('LIVE');
end;
/

select * from emp, dept where emp.deptno=dept.deptno;


-- cleaning up
begin
dbms_wm.disableversioning ('dept, emp', true);
end;
/

begin
dbms_wm.gotoworkspace ('LIVE');
dbms_wm.removeworkspace('WS1');
dbms_wm.removeworkspace('WS2');
end;
/

/* Resolving Conflicts */
-- privs
begin
dbms_wm.grantSystemPriv (
'ACCESS_ANY_WORKSPACE, ' ||
'MERGE_ANY_WORKSPACE, ' ||
'CREATE_ANY_WORKSPACE, ' ||
'REMOVE_ANY_WORKSPACE, ' ||
'ROLLBACK_ANY_WORKSPACE' ,
'USER1' ,
'YES');
end;
/

Page 159 Oracle DBA Code Examples

conn user1

create table dept
( DEPTNO NUMBER(3) PRIMARY KEY,
DNAME VARCHAR2(14) )
/

create table EMP
( EMPNO NUMBER(4) PRIMARY KEY, -- PK is mandatory
ENAME VARCHAR2(10),
DEPTNO NUMBER(3) references dept(deptno))
/

insert into dept values (1,'d1');
insert into dept values (2,'d2');
commit;
insert into emp values (1,'e1',1);
insert into emp values (2,'e2',1);
insert into emp values (3,'e3',2);
insert into emp values (4,'e4',2);
commit;

-- set version enabled tables
begin
dbms_wm.enableVersioning('DEPT,EMP','VIEW_WO_OVERWRITE');
end;
/


-- create workspace and goto it
begin
dbms_wm.createworkspace('WS1');
end;
/
begin
dbms_wm.gotoworkspace('WS1');
end;
/

-- make an update
update emp set ename='in WS1' where empno=1;
commit;

-- go back to LIVE workspace
begin
dbms_wm.gotoworkspace('LIVE');
end;
/

-- make an update
update emp set ename='in LIVE' where empno=1;
commit;

-- to see the conflicts in xxx_CONF, you should leave LIVE workspace
begin
dbms_wm.gotoworkspace('WS1');
end;
Page 160 Oracle DBA Code Examples

/

column wm_workspace format a16
select * from EMP_CONF;


/* To resolve the conflict */
-- (1) call dbms_wm.beginresolve
begin
dbms_wm.BeginResolve('WS1');
end;
/

-- (2) call dbms_wm.resolveconflicts
begin
-- Keep update from Parent (LIVE)
dbms_wm.resolveconflicts(
workspace => 'WS1', -- workspace to check for conflicts with others
table_name => 'EMP',
where_clause => 'empno=1',
keep => 'PARENT');
end;
/
-- OR
begin
dbms_wm.resolveconflicts(
workspace => 'WS1',
table_name => 'EMP',
where_clause => 'empno=1',
keep => 'CHILD');
end;
/
commit;

-- (3) call dbms_wm.commitresolve.
begin
dbms_wm.CommitResolve('WS1');
end;
/


-- let's see if we still have a conflict:
select * from emp_conf;

-- check the data
select * from emp ;

-- let's see what data in LIVE
begin
dbms_wm.gotoworkspace('LIVE');
end;
/

select * from emp ;

-- if data modified in WS1 was chosen, merge it with LIVE
begin
Page 161 Oracle DBA Code Examples

dbms_wm.mergeworkspace('WS1');
end;
/

-- Cleaning up
begin
dbms_wm.gotoworkspace('LIVE');
dbms_wm.removeworkspace('WS1');
dbms_wm.disableversioning('emp,dept');
end;
/
Page 162 Oracle DBA Code Examples

Repai r i ng Cor r upt ed Dat a
Opt i ons f or Repai r i ng Dat a Bl ock Cor r upt i on
Drop and re- creat e an obj ect aft er t he corrupt ion is det ect ed.
I f dat a block corrupt ion is limit ed t o a subset of rows, t hen anot her opt ion is t o rebuild t he
t able by select ing all dat a except for t he corrupt rows.
Use t he DBMS_REPAI R package t o det ect and repair corrupt blocks in t ables and indexes.
Use Flashback Versions Query t o query values of a row over a period of t ime. Then
arrange undo plan.
Block Media Recovery BMR
Det ect i ng Cor r upt i ons Met hods
DB_VERI FY ut ilit y
ANALYZE TABLE .. VALI DATE STRUCTURE [ CASCADE]
Set t ing t he init ializat ion paramet ers: DB_BLOCK_CHECKI NG and DB_BLOCK_CHECKSUM
exp ut ilit y
DBMS_REPAI R
Dat a Recovery Advisor aut omat ically runs aft er any corrupt ion is det ect ed and can alos
proact ively invoked. See Using Dat a Recovery Advisor in RMAN.
Usi ng dbv ( DBVer i f y) Ut i l i t y
The ut ilit y checks only for logical corrupt ion below t he HWM.
dbv file=D:\ORACLE\ORADATA\ORA10G\USERS01.DBF blocksize=4096
dbv file=example01.dbf blocksize=8192
..
examine "Total Pages Marked Corrupt"
Set t i ng t he I ni t i al i zat i on Par amet er s f or Det ect i ng Cor r upt i on
Ver i f y i ng Bl ock I nt egr i t y i n Real Ti me: DB_BLOCK_CHECKI NG
Block checking t ypically causes 1% t o 10% overhead, depending on workload.
You should set DB_BLOCK_CHECKI NG t o FULL if t he performance overhead is accept able.
Possible Values:
o OFF: No block checking (except for SYSTEM) (default )
o LOW: Basic block header checks are performed aft er block cont ent s change in memory
o MEDI UM: All LOW checks, as well as block checking for all non- index- organized t able
blocks, are performed.
o FULL: All LOW and MEDI UM checks, as well as checks on index blocks, are performed.
alter system set DB_BLOCK_CHECKING=FULL;
Page 163 Oracle DBA Code Examples

alter session set DB_BLOCK_CHECKING=LOW;
Ver i f y i ng Bl ock I nt egr i t y i n Real Ti me: DB_BLOCK_CHECKSUM
Can prevent corrupt ion caused by underlying I / O syst ems
FULL set t ing causes 4- 5% overhead
DB_BLOCK_CHECKSUM = { OFF | FALSE | TYPICAL
select value from v$parameter where upper(name)='DB_BLOCK_CHECKSUM';
| TRUE |FULL }
Det ect i ng l ost w r i t e: DB_LOST_WRI TE_PROTECT
A dat a block lost writ e occurs when an I / O subsyst em acknowledges t he complet ion of t he
block writ e, while in fact t he writ e did not occur in t he persist ent st orage.
DB_LOST_WRITE_PROTECT = { NONE
Set t ubg t he DB_ULTRA_SAFE Par amet er ( I n Or acl e 11g)
| TYPICAL | FULL }
This paramet er i s used t o set t he ef f ect ive values of t he paramet ers: DB_BLOCK_CHECKING,
DB_LOST_WRITE_PROTECT, DB_BLOCK_CHECKSUM. This paramet er t akes one of t he f ollowing values:

off
t his value means any values you set f or any of t he t hree paramet ers will not be
overridden.

data only
The ef f ect ive value of t he paramet ers will be as f ollows:


Par amet er Act i v e Val u e

DB_BLOCK_CHECKING medium

DB_LOST_WRITE_PROTECT typical


DB_BLOCK_CHECKSUM.
full

data and index
The ef f ect ive value of t he paramet ers will be as f ollows:


Par amet er Act i v e Val u e

DB_BLOCK_CHECKING full

DB_LOST_WRITE_PROTECT typica