TOAD Handbook

Bert Scalzo, currently TOAD product architect at Quest Software, has worked with Oracle databases for well over two decades, including stints at both Oracle Education and Oracle Consulting. An accomplished speaker and presenter at Oracle conferences and user groups, he is author of many books, including TOAD Pocket Reference for Oracle, Second Edition (O’Reilly Media, Inc., 2005) and Oracle DBA Guide to Data Warehousing and Star Schemas (Prentice Hall, 2003).   Dan Hotka, an internationally recognized Oracle expert, has twenty-six years of experience with Oracle products. He regularly teaches courses on TOAD and works closely with Quest Software. He has authored or coauthored eleven books and speaks regularly at Oracle conferences and user groups around the world. He can be found on the Web at DanHotka.com.

Acknowledgments xiii

About the Authors xv

 

Introduction 1

Who Should Read This Book 1

Why This Book Is Unique 2

How This Book Is Organized 2

 

Chapter 1: TOAD Setup and Configuration 5

Windows Platform Support 6

Database Connectivity 7

Database Client Versions 10

Which Version of TOAD to Use 10

Running the TOAD Installer 12

Copying TOAD Settings 13

TOAD Adheres to Oracle Security 13

Activating TOAD’s Read-Only Mode 15

Advanced TOAD Security Options 15

Customizing TOAD to User Taste 16

Summary 17

 

Chapter 2: Database and Schema Browsers 19

Auto-Start a Browser 20

Database Browser 21

Schema Browser 27

Schema Browser Options 42

Summary 44

 

Chapter 3: SQL Editor 45

Basic Concepts of the SQL Editor 45

Predefined Shortcuts 59

User-Defined Shortcuts 62

Using Bind Variables 63

Table and Column Name Select Lists 64

TOAD Table Aliases Substitutions 66

Auto-Replacement Substitutions 67

SQL Templates 68

Code Snippets 70

Executing SQL Statements 71

Executing SQL Scripts 72

Editing Result-Set Data 73

Saving Result-Set Data 75

Printing Result-Set Data 76

Examining Explain Plans 79

Examining Basic Performance Information 82

Other Result Output Tabs 84

SQL*Plus Compatibility 84

SQL-Creating-SQL 87

Summary 88

 

Chapter 4: TOAD PL/SQL Editor 89

Basic Concepts of the PL/SQL Editor 89

Formatting PL/SQL Code 96

Predefined Shortcuts 98

Using Aliases, Pick Lists, Shortcuts, Snippets, and Auto-

Replacement Substitutions 101

PL/SQL Object Templates 102

Compiling PL/SQL Code 105

Executing PL/SQL Code 105

Saving PL/SQL Code to Files 107

Debugging Setup, Requirements, and Parameters 108

Basic Debugging PL/SQL Code 109

Debugging Breakpoint Options 115

Advanced Debugging PL/SQL Code 117

Profiling PL/SQL Code 120

Setting Up Source Code Control 123

Using Source Code Control 123

Summary 123

 

Chapter 5: Database Reporting 125

TOAD Report Manager 125

Building Reports Using Fast Reports 131

Adding Reports to the TOAD Report Manager 141

Using the HTML Schema Document Generator 144

Summary 148

 

Chapter 6: Tuning Tools in TOAD 149

Oracle Tuning Using TOAD 149

SQL Explain Plans/Auto Trace 150

Oracle Trace Facility 155

StatsPack Reporting 165

Automated Workload Repository Reporting 168

Summary 171

 

Chapter 7: Database Management 173

Database Health Check 174

Creating New Databases 176

Tablespaces and Data Files 178

Start-ups and Shut-downs 181

Oracle and NLS Parameters 183

Generating Database Scripts 186

Generating Schema Scripts 188

Comparing Database Differences 190

Rebuilding Multiple Objects 195

Top Session Finder 197

SGA Trace/Optimization 198

Session Browser 200

Summary 203

 

Chapter 8: Exporting Table Data 205

Saving Grid Contents 205

Flat File Export 208

Creating Inserts 209

Schema Browser Script 210

Oracle Export 211

Data Pump Export 214

Data Subset Wizard 218

Import Table Data 220

Summary 222

 

Chapter 9: Other Useful Tools 223

Master-Detail Browser 223

ER Diagram 227

Code Road Map 229

Query Builder 232

External Tools 238

Compare Files 240

TNS Editor 241

Script Manager 243

Summary 245

 

Chapter 10: TOAD App Designer 247

TOAD Command Line 247

TOAD App Designer 251

Summary 260

 

Index 261