Transcription
Bert ScalzoDan HotkaToadforOracle UNLEASHED800 East 96th Street, Indianapolis, Indiana 46240 USA
Toad for Oracle UnleashedCopyright 2016 by Pearson Education, Inc.All rights reserved. No part of this book shall be reproduced, stored in a retrievalsystem, or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise, without written permission from the publisher. No patent liability isassumed with respect to the use of the information contained herein. Although everyprecaution has been taken in the preparation of this book, the publisher and authorsassume no responsibility for errors or omissions. Nor is any liability assumed fordamages resulting from the use of the information contained herein.ISBN-13: 978-0-13-413185-6ISBN-10: 0-13-413185-1Library of Congress Control Number: 2015906538Printed in the United States of AmericaFirst Printing July 2015TrademarksAll terms mentioned in this book that are known to be trademarks or service marks havebeen appropriately capitalized. Sams Publishing cannot attest to the accuracy of thisinformation. Use of a term in this book should not be regarded as affecting the validityof any trademark or service mark.Editor-in-ChiefMark TaubExecutive EditorGreg DoenchDevelopment EditorSusan Brown ZahnManaging EditorKristy HartProject EditorElaine WileyCopy EditorPaula LowellIndexerKen JohnsonProofreaderDebbie WilliamsWarning and DisclaimerEvery effort has been made to make this book as complete and as accurate aspossible, but no warranty or fitness is implied. The information provided is on an “asis” basis. The authors and the publisher shall have neither liability nor responsibility toany person or entity with respect to any loss or damages arising from the informationcontained in this book.Editorial AssistantMichelle HousleySpecial SalesSenior CompositorGloria SchurickFor information about buying this title in bulk quantities, or for special sales opportunities (which may include electronic versions; custom cover designs; and contentparticular to your business, training goals, marketing focus, or branding interests),please contact our corporate sales department at corpsales@pearsoned.com or(800) 382-3419.For government sales inquiries, please contact governmentsales@pearsoned.com.For questions about sales outside the U.S., please contactinternational@pearsoned.com.Cover DesignerAlan Clements
Contents at a GlanceIntroduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi1Getting Started. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12Fast Track . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253Working with SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 434Schema Browser . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 695Working with Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 936Working with PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1217Miscellaneous Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1578Getting Started with Toad Automation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1719Database Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19110Toad as a SQL Tuning Tool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251
Table of ContentsIntroduction . xiWho Should Read This Book . xiWhy This Book Is Unique . xiiHow This Book Is Organized. xii1Getting Started .1Introduction to Toad . 1Toad Release History . 2Toad Editions and Suites . 3Prerequisites and Installation . 5Running Toad the First Time . 8Oracle Client Software . 9Database Connections . 10Introduction to Main Screens . 16Basic Toad Customizations . 18Common Newbie Questions . 21Can Toad Do That? . 21Toad versus Database Security . 22Sharing Toad Options Settings. 22Help—Toad Running Slowly . 22Summary . 232Fast Track .25Organizing Connections . 25Toolbars and Menus . 30Keyboard Shortcut Keys . 33Startup Windows/Screens . 34Startup Files, Scripts, and Actions . 36Advanced Toad Customizations . 37Sharing Toad Settings. 40Summary . 413Working with SQL.43Editor Window . 43SQL Editor Output Area . 48Object Describe . 51Toad Insights . 57Code Snippets. 58SQL Recall . 60Auto Replace . 63
ContentsQuery Builder . 64Summary . 684Schema Browser .69Configuring the Schema Browser . 69Creating and Editing Objects . 76Printing Data Using FastReport . 79Using the Reports Manager . 85Summary . 915Working with Data .93Reviewing Data Grid Options . 93Filter/Sort the Table Data . 97View/Edit Query . 99Show/Hide Columns . 100Show Detail Dataset . 100Calculate Selected Cells . 101Export Dataset . 102Refresh Dataset . 103Cancel Query Execution or Fetch . 104Add/Delete Rows . 104Row Set Navigation Buttons . 105Edit Row Set Control Buttons . 105Commit/Rollback . 105Bookmark Navigation. 105Single Record Viewer . 105Save Data in Various Formats . 106Master Detail Browser . 111ER Diagrammer . 113HTML Documentation Generator . 115Summary . 1196Working with PL/SQL .121Using the Editor . 121Using Code Templates . 127Project Manager. 132Using Code Snippets . 133Code Shortcuts . 135Code Analysis . 139Using the Debugger. 142Basic Debugging . 142Advanced Debugging Techniques . 148Using the PL/SQL Profiler . 153Summary . 156v
viToad for Oracle Unleashed7Miscellaneous Tools .157Code Road Map . 157External Tools. 160Compare Files . 163TNS Editor . 164Script Manager . 165UNIX Monitor . 167Summary . 1698Getting Started with Toad Automation .171Command Line (Legacy) . 172Automation Designer (Future) . 180Summary . 1909Database Administration .191Health Check . 192Session Browser . 194Database Browser . 197Simple Export . 202Data Pump Export . 205Tablespace Management . 210Generate Database Script . 212Generate Schema Script . 214Compare Databases . 216Compare Schema . 218Analyze All Objects . 220Rebuild Multiple Objects . 223Summary . 22410Toad as a SQL Tuning Tool .227Toad and Explain Plans . 227SQL Statistics . 231Toad and SQL Trace . 233Toad and TKPROF . 238Toad and Trace File Browser . 241Statspack Interface. 243AWR Browser . 245Toad and the PL/SQL Profiler . 246Toad and the SQL Optimizer . 248Summary . 249Index .251
viiAbout the AuthorsBert Scalzo is a Database Expert for HGST (a Western Digital company) and was formerlyat Dell Software as a member of the Toad dev team for 15 years. He has worked withOracle databases for more than two decades. Mr. Scalzo’s work history includes time atboth Oracle Education and Oracle Consulting. He holds several Oracle Masters certifications and has an extensive academic background—including a BS, MS, and PhD inComputer Science, an MBA, plus insurance industry designations. Mr. Scalzo is also anOracle ACE.Mr. Scalzo is an accomplished speaker and has presented at numerous Oracle conferencesand user groups, including OOW, ODTUG, IOUG, OAUG, RMOUG, and many others.He has written several books and numerous articles, papers, and blogs for the OracleTechnology Network (OTN), Oracle Magazine, Oracle Informant, PC Week (eWeek), DellPower Solutions Magazine, The LINUX Journal, LINUX.com, Oracle FAQ, and Toad World.http://bertscalzo.com/
viiiDan Hotka is a Training Specialist and an Oracle ACE Director who has more than 37years in the computer industry, and more than 31 years of experience with Oracle products. His experience with the Oracle RDBMS dates back to the Oracle V4.0 days. Danenjoys sharing his knowledge of the Oracle RDBMS. Dan is well published with 12 Oraclebooks and well over 200 published articles. He is frequently published in Oracle tradejournals, regularly blogs, and speaks at Oracle conferences and user groups around theworld.Dan Hotka, Author/Instructor/Oracle ACE logspot.com
ixDedicationTo my past and present miniature schnauzers Ziggy, Max, andDexter—the three most wonderful four-legged kids that any parentcould ever have. JAnd to my wife Susan who’s always jealous of my many bookdedications solely to the dogs Bert Scalzo, PhDThis book is dedicated to my grandson Riggs,without whom my family would not be the same.Here’s to the next generation!Dan Hotka
xWe Want to Hear from You!As the reader of this book, you are our most important critic and commentator. We valueyour opinion and want to know what we’re doing right, what we could do better, whatareas you’d like to see us publish in, and any other words of wisdom you’re willing to passour way.We welcome your comments. You can email or write to let us know what you did ordidn’t like about this book—as well as what we can do to make our books better.Please note that we cannot help you with technical problems related to the topic of this book.When you write, please be sure to include this book’s title and authors as well
Toad for Oracle UNLEASHED 800 East 96th Stre
