Toad use Quick Start

Reprinted, the author is unknown, please leave a message.

Directory one. Overview of the TOAD function. System requirements. Installation Guide 4. Quick start 1. Introduction to the Usage of Schema Browser 2. SQL Editor's use introduction 3. Treatment of Procedure Editor 4. How to perform PLSQL DEBUG 5. How to optimize SQL 6 using SQLAB Xpert. How to use SQL Modeler to write query 7. How to use Toad's DBA function 1. TOAD function overview During the development of Oracle applications, access database objects and writing SQL programs are a boring and time-consuming job, and daily management for databases is also a lot of SQL scripts. Finished. Quest Software provides an efficient Oracle Application Development Tool-Toad (Tools of Oracle Application Developers). In the new version of Toad, DBA modules are also added to help DBA completed many daily management. Its biggest feature is simple and easy, and the accesses are fast. With TOAD, we can quickly access the database through a graphical user interface, complete complex SQL and PL / SQL code editing and testing work. Toad is designed by Oracle Development Experts to developers, is a powerful and compact professional PL / SQL development environment. Toad mainly has the following features: Mode Browse: Mode Browsing features allow us to quickly access the data dictionary, browse the tables, indexes, and stored procedures in the database. Toad provides quick access to the database, is extremely convenient to use, the user interface is simple, and the structure is reasonable. When we click on a separate database object, Toad immediately displays the details of this object. For example, when we point a database, all and this table related index, constraints, stored procedures, SQL statements, and mutual reference relationships with other tables are displayed on the same interface. To simplify the operation, the user can operate the database object in the browsing window. SQL Editor: The main function of the SQL editor is to edit, run, and adjust the SQL statement. Toad's Advanced Edit window includes numerous features to increase the extent of productization of developers to write SQL statements. For example, simply generate a code template, automatically discover the contents and columns of the package before writing SQL, and the like. The SQL editor includes an editing window and a run results window that allows developers to test run results during editing. The SQL editor includes not only a standard editing command, but also some enhanced features, such as the fields in the fast query table, formatting the contents of the SQL statement, and more. This window can handle the contents of large to 4GB, which is very useful for large development projects. Convenient bookmarks allow developers to find relevant locations very easily. User-defined configuration features can be provided in the Run Results window, support the long and long RAW columns, you can remove data to disk, print data, editing data, etc. Stored Procedure Editor: The main function of the stored procedure editor is editing, compiling, testing, debug stored procedures, and triggers. Toad provides grammatical identity, error identification, and many other easy-to-use features, such as displaying a table name, column name, and Oracle function in a popup window. Unlike other PL / SQL editing tools, TOAD allows multiple database objects to be operated in a file, you can compile multiple objects, compile multiple objects, compile them to the current cursor, start compiling from the cursor. When an error is running, the stored procedure stops to a problematic statement. Users can use shortcuts or templates to quickly write PL / SQL, or generate their own templates as needed. Using Toad can be easily edited, you can set a bookmark, cancel your comment, format SQL statement, and so on.

PL / SQL Debugger Options: Toad provides easy-to-use PL / SQL debugging capabilities, saving the valuable time of developers to develop and test in large projects, improve application development. During the development of stored procedures, Toad can be edited, debug, and run code line by line. When the runtime can enter the parameters as needed, observe the changes in the relevant parameters to check the correctness of the stored procedure. During the adjustment process, the TOAD can display all breakpoints, parameters, call stacks, and output parameters through the window. With TOAD, it is very easy to detect errors for stored procedures. Developers can run the PL / SQL statement step by step to identify problems. Debugging sessions can be carried out simultaneously with other program sessions. SQLAB XPERT OPTION: Helps developers to optimize SQL, provide them with SQL execution plans in various optimization mode, and can give optimization suggestions, compare the actual SQL operation results in various modes, help developers really high-speed development efficient Earth code. Toad can also hang some other products, such as PL / Formatter, ReveAlNet Knowledge Base, SQL Impact, etc., which can be tightly integrated with TOAD and jointly provide a perfect integrated development environment. The new version has also added DBA modules, which has broadening the scope of application of Toad products. Second, the system requirements: Client: u Windows 95/98 / NT / 2000 English can ü Complete installation SQL * Net 2.0 or Net8 ü disk: generally installation requires about 20m or so, according to the different modules, different and installation Different, minimum 5m disk space (read from network server) ü memory: Windows 9x / me: 16M memory; Windows NT / 2000: min 32m] ü Tuning module requires additional 25M space server: ü Oracle 7.3 or higher, on any hardware platform. ü Nene requires a DBA user account three. Installation: 1. Selection of three installation methods: a) TOAD AND PERSONAL Configuration Files to PC default installation selection, complete a complete client installation, copy all required files to the local toad directory. Most users should choose to use this installation. b) Personal Config Files To PC, Read Toad from Network Server reads the toad program from the web server, install some custom files on the local computer, and create a shortcut to the server file, generally not recommended to use this method, and request the network The server is already configured. c) Toad to NetWork Server Install Toad to the web server so that other users can install the Personal Config Files To PC, Read Toad from Network Server. 2. Using the feature required, you need to run some scripts (all in the toad / temps directory) ü If you need toAd to view the execution plan, you must run the script preptoad.sql or NOTOAD.SQL N If you want to create a TOAD user to store If you need to use the object, you run preptoad.sql. n If you want to establish an object to be used in the current connection user mode, you run NOTOAD.SQL. n Note that if you choose to establish an Toad user, you need to modify the script first, specify the user's default table space and temporary table space.

ü You need to use Oracle8i's Profile Analyzer, you must run toadprofiler.sql u needs to strengthen your toad, you must run toadsecurity.sql four, toad use Quick Start 1. Schema Browser: Schema Browser is an extremely distinctive module for Toad. With the help of Schema Browser, you can easily browse all kinds of database objects inside the database, such as tables, indexes, sequences, stored procedures, etc., and can easily pour out DDL statements and make various modifications and reconstruction work. Located on a certain object, the operations allowed by this object will be listed automatically. Features: 1 Support for new, view, modifications in all objects in the Oracle database, integrate the functions required to manage all database objects. l Follow the mode -> Object Category -> Object -> Object Options -> Object itself details and object-related information to organization, very organized, easy to locate objects, and you can see all the objects you need. information. l For Tables: i. Complete Buildings Wizard, do not enter lengthy syntax, more comprehensive replacement. II. You can modify the various logic and physical properties of the table, modify the properties of the column, add the column, delete columns (in Oracle8i), modify the physical storage properties, Truncate, Drop, etc. Iii. You can generate a complete DDL statement of the table, including storage information, permission information, constraints, indexes, and triggers, etc., which are difficult to do. IV. It can be easily analyzed for the table, view all information on the table, and you can have the data inside the table and save it as a SQL statement. v. You can reorganize the table, you can complete the EXP / IMP not doing, and less manual participation is safer. l For view, stored procedures: i. You can save the establishment view, the statement of the stored procedure is text, II. Re-compiling the confidential view, stored procedures, modify the definition statement of the view. Conveniently viewing the code, interdependence, permission information, etc. saved as text, etc. l Rollback segments, i. Simply clear online, offline status, convenient online / offline operation II. Detailed returning segment physical information and current status. Iii. It can be convenient to modify the physical storage attribute L table space: i. TableSpace map; intuitive display data physics distributed II on the tablespace II. Visually view the utilization rate of each table space, the remaining space, crushing information III Various Alter operations: online, offline, increase data files, changing data file size, changing physical storage properties, etc. L. Other database objects are also complete. 2. SQL Editor: a) Activate three ways to activate SQL Editor: i. After connecting to the database, Toad automatically opens the SQL Editor window.

II. Click on the button III on the top of the Total Toolbar. From the menu bar: Database -> SQL Editor B) Features: l Allows the multiple edit window while editing multiple SQL statements, while editing multiple databases simultaneously At the same time, the plurality of databases operate L allows editing SQL, PLSQL, Java, HTML, and text L using bookmark function, can quickly locate a statement in a large number of SQL statements L SQL Editor itself has a powerful editing function, very Convenient to edit the SQL statement. l Strong shortcut keys support and toolbar's support, more efficient writing (Many shortcuts can be defined by yourself, in View -> Options -> SQL Editor) L can be entered according to the previous conditions, intelligent speculative code, You can do some degree of code intelligent fill L to locate the mouse over the table / view / stored procedure name, press F4, open the object description window, easy to see the definition of the table and view, the source code of the stored procedure, L Very easy to analyze the SQL statement to perform plan: Click the button on the toolbar to see the result l Support some SQL * Plus commands, which can easily perform a large amount of database scripts L. L. L. Through SQLAB Xpert, a comprehensive analysis of the current SQL statement will give a variety of reasonable tuning, just click the TUNING button on the toolbar: l Convenient to save, open, and run SQL script l Support for query results. Conductive, l can save the query results in a variety of formats, can be saved as HTML, plain text, XLS and other format Ls easy to get the list of SQL functions and usage descriptions (Show SQL Help Window Button) L can view The execution of the SQL statement: The actual execution result and execution efficiency of the SQL statement are seen by the following autotrace window. l Support History SQL (F8): It can be easily queried for SQL before, and does not need to be re-entered repeatedly. l You can perform anonymous blocks, stored procedures, and see the results of DBMS_output from the bottom window, such as: 3. Stored Procedure Editor A) Try to open the stored procedure writer: l Click on the third button on the Total menu bar: l Click Database -> Procedure Editor, l You can call B) by Schema Browser: 1. Powerful custom settings: From the way of displaying the word, to automatically replace, to the definition of various shortcuts, etc., the more familiar, more and more. 2. 3. Automatically display SQL and PL / SQL keywords with different colors, clear understanding, and can customize the specific display methods of various keywords according to their habits. You can customize the stored procedure to have a template. When you create a new stored procedure, the framework of the automatic generation program can be easily called to call Schema Browser, locate the mouse on an object, F4, Schema Browser opens the detailed description of the object, help smoothly and quickly Developer. Support code automatically corrects, such as entering NDF, automatically replacing NO_DATA_FOUND, entering the automatic replacement to THE, similar automatic replacement can also modify and add yourself.

7. Convenient left row number display, click the left line number to increase / delete 8. Support for various shortcuts, not only includes common editing operations, but also have their own pairs of PLSQL blocks (own definition shortcuts). If you can define Ctrl Shift D: Pop up the type of all PLSQL Block, you can choose. 9. You can encode, while debugging in an integrated development environment, no need to switch 10. Convenient to open multiple stored procedures while switching 11 through the page. Quickly compile the stored procedure, quickly locate the error, choose whether to compile dependence on the depends 12. Built-in PL / Formatter, you can format the stored procedure with PL / Formatter, and you can provide a summary analysis and modification of the stored procedure. It is recommended that the PL / Formatter can format the existing stored procedure to make all stored procedures. All in line with the same writing standard, which is both conducive to improving the readability of the program, avoiding the style of the module written by different people. PL / Formatter also helps improve the modularity of the program, enhance the structure of the program, enhance SQL's share. PL / Formatter can also help complete the management between PL / SQL and Oracle versions. 13. Built-in REVEALNET KNOWLEDGE BASE, click the right mouse button to open, quickly get technical support, solve technical puzzles NPL / SQL: N contains answers that may encounter problems throughout the development process Ncode Library: a large number of rigorous PL / SQL code Examples and ready-made processes, full source code provides n Various error handling modules available, all kinds of character dates function n contain more than 1400 topics NADMIN: N to answer 80% of the DBA daily work, covering more than 2400 topics N From the network to performance optimization, recover from backup to Error Message, what is the full example: What should I do if I encounter a storing process? 4. How do I debug in the stored procedure? u Require Toad separate debug option (Standard Version No This option) u To use Toad to DEBUG for the stored procedure, you must have Oracle system package: dbms_debug, and you must have an Oracle Probe API V2.0 or higher version. u Integrated in Stored Precedure Editor, you can write one side while debugging, greatly increasing the development efficiency U Once you open Procedure Editor, the Debug menu is activated and you can start DEBUG. u allows the stored procedure to debug, breakpoint debugging, also supports debugging the trigger.

u can have a breakpoint support, support WATCH variables, and allow runtime to change variables to change variables to allow the debugging step to support debugging steps for multi-layer calls: 1. Click on the third button of the menu bar:, open Procedure Editor 2. Click the button of the Procedure Editor's menu bar to call out the stored procedure 3 you need to debug. Click the button on the toolbar to enter the parameters you need to pass. If you do not need to pass the parameters, you can directly click the button on the toolbar:, start debugging directly, as shown in the following image input parameters: 4. Click the button of the menu bar:, you can check it, you can define a breakpoint, run directly to the breakpoint, during the stored procedure, the current statement will be high brightness, and position the mouse pointer On the variable, TOAD automatically displays the current value of the variable. 5. You can stop the program's execution at any time, just point the DEBUG -> Halt Execution, or the button on the menu bar, you can abort the execution 6. When you touch another stored procedure, you can select STEP over, so you can run the called stored procedure, return to the result, continue to debug this stored procedure, you can also choose Trace Into. This opens the called stored procedure to further deepen the sub-stored procedure that is called. If you need to keep debug messages, you should select Compile Dependencies with debug information when compiling, which is the button on the right side of the Procedure Editor toolbar. 7. Support for real-time tracking of variables: Using Watch: You can track a variable before running, or running during operation: add watch, position your mouse to the variable, then click Debug -> Add Watch At Cursor, this variable The current value of the variable should appear on the Debug status window and in real time. The variable v_empname in the figure above. You can modify 8 at time. Breppoint support: Click the stored procedure to break the line number, the row color is red, enter the parameter, click the execution button, you can run directly to the breakpoint, you can do not need to track, more efficient Commissioning. Debug Option also supports conditional breakpoints, only when the condition is satisfied, it will stop in that place, otherwise it will continue to operate without this breakpoint. Ë Note that the result of dbms_output.put_line is only when the program execution is complete, there is no result in the execution of the program. 4. How to use SQLAB Xpert to tune the storage process? We know that the ORCE database is optimized, the main step is to optimize SQL. With no special tools (such as toad), SQL optimization is a very difficult and bored thing, and you need to understand the in-depth understanding of Oracle and the superb Oracle tuning skills.

Using Toad's SQLAB Xpert Tuning module, we can make our ordinary developers can also conduct very fast and accurate analysis of SQL. In SQL Editor and Procedure Editor, select the SQL statement you want to debug (SQLab Xpert can debug SELECT, INSERT, UPDATE, DELETE statement, copy to SQL Editor, then click the Tuning button on the toolbar, Toad will automatically open the SQLAB Xpert window, capture the current SQL statement and convert it to the statement in accordance with the Explain Plan format. When you open XPERT for the first time, toad will prompt you, you need to install some SERVER-side tables, view, and package, you can store the table to a specified table space (such as Tools). You can simply follow the prompt step by step. If the system setting does not allow Job (JOB_QUE_PROCESS = 0), Toad prompts that some parts will not be installed now, it can be ignored. After the installation is complete, you can choose to make some non-DBA users can also use TOAD's SQLAB Xpert tuning. After the installation is complete, Toad will prompt you some questions about optimization, you can choose to reset each time you can reset, or set this setting in the future. Of course, if you need, or change it, the administration and preference of the window of SQLAB OPTION XPERT are reset. Example: The following SQL statement involves the association between multiple tables, there is a certain complexity, we can help analyze the toad's SQLAB XPERT Option: select a.name. B.Name, C.EMP_NAME from Dep A, Manager B, EMP c where a.dep_no = c.DEP_NO and B.Manag_id = C.Manag_id and c.emp_no = 3 We copied this SQL to SQL Editor, click the Tuning button above, Toad Open the SQLAB Xpert Option window . As shown below: From the above figure we can see: In various optimization modes, what is the implementation plan of the SQL statement, and toad gives each of the specific each step of the implementation plan, each The cost of the step, the relevant data related to the table, etc. Wait to help the optimizer make the best choice. You can save the current analysis results inside its database, and you can call again after you. We can also ask for automatic optimization features of SQLAB: Click the Auto Tune button under ORIGINAL SQL, Toad will analyze the execution results (of course, this requires you to set UTL_FILE_DIR in INITSID.ORA, if the server is in UNIX machine In the case, you need to get the trace file through the FTP mode, there are some small settings).

Automatic optimization is very efficient to perform actual operation comparisons for all execution plans, and give the time comparison of actual execution, as shown below: We can also give TOAD to actual optimization suggestions: Click ORIGINAL SQL, click the Advice button below. TOAD gives reasonable advice based on the analysis and execution results of SQL: such as: 1. Put the tables and indexes to two different tablespaces, because we don't specify the USING INDEX clause when you create a table, add constraints, so the tables and indexes are built on the user's default table space. 2. Analyze Tables EMP, DEP, and Manager, collect the latest statistics, which is beneficial to CBO to get the best implementation plan 3. Create an index on a suitable column: MANAG_ID and DEP_NO of EMP. When there is a lot of data in the table, this can greatly reduce the cost due to full mete scan. 5. SQL Modeler: SQL Modeler can help developers easy to write a variety of complex SQL statements, just by simply drag the mouse, you can write a complex SQL statement. Open SQL Modeler: i. Menu Bar Database -> SQL Modeler, Open Window II. Click the Fourth button of the Toolbar, Open the SQL Modeler window window consisting of three main parts: N table selection area: Right right The part used to select the table used in the FROM clause. Drag the area or double-click on the left. N Table Model Region: The relationship between the tables and tables used to pattern selection. n Generate a SQL area: here the SQL statement corresponding to the query created by the graphical mode, in SQL Modeler, also supports the generated SQL to perform EXPLAIN Plan, debug SQL performance, run SQL, to see if it is the result of hope, Save the generated statement or paste the statement into the SQL Editor. With the help of SQL Modeler, even the primary programmers can write complex and efficient SQL queries. For example, the example below; the generation steps: 1. Select Table EMP, DEP, and Manager 2. Drag the EMP_NAME and EMP_NO, DEP Name, Manager's Name 3. Double-click the EMP_NO CONDition column of the Criteria window, pop up the window, enter the query condition, such as EMP_NO = 3 4. Since the result does not want Emp_no to appear, double-click the Visible of the EMP_NO column, set to False 5. This will come out the needs of the query, and now you can run the query to get the result, analyze the query SQL, or open this SQL in SQL Editor, or save it as a SQL Model file, or further process, such as adding, summary, etc. DBA features: TOAD's ability in DBA daily management is very powerful, help DBA is safe and fast to complete many daily work, as well as some gadgets that are unique to complete some common methods.

The following probably describes the main functions of each common module: a) Database Monitor provides intuitive Oracle database health, including the following details: You can see the number of logical Io, physical IO, various status, SGA utilization, sharing The use of the pool, the waiting status of various events, and the changes of various events B) Instance Monitor i. You can use all nodes defined by local TNSNames.ora files, Listener and database running status and Version and other related information. II. You can start remotely, shut down the database (if a Password authentication mode) c) UNIX Monitor: i. Monitor the health of the UNIX machine, such as detailed machine startup time, CPU utilization, main process, and resources occupied by these processes , Major disk IO, etc. d) TRACE / KILL Session i. Convenient view All connection users related information: such as terminals, programs, connection time, etc. Tracking a user, check the user's SQL statement, see the statement execution plan, III. Convenient to check the Lock object of the system, have anything waiting for other transaction-release locks, and can easily find what the user is ending, the user performs what SQL. IV. View the use status of the returning segment, what user, what statement, what back to the segment, how much read and write, etc. E) Oracle Parameter i. It can be convenient to view the parameters of the Oracle system: whether the default value, parameter effect Brief description, whether you can use ALTER Session / ALTER System to dynamically change, for those that can dynamically change, you can double-click the mouse to modify f) NLS_Parameter: i. Convenient view Session, Instance, Database level NLS parameter value, II. You can dynamically modify the session NLS parameter value g) Extents i. You can view detailed storage of tables and indexes under a user, II. Find those tables and indexes that occupy a large amount of space, you can consider using the corresponding countermeasures, For example, partition III. Find those tables and indexes that are about to reach max_extents and correct IV in time. Find those indexes and tables in the same table space, and can be corrected in time. h) TableSpace i. Visually view the utilization, total space, usage space of each table space, the remaining can be space and the default storage parameters, and whether the table space needs to be organized, etc. Information II. View the status of each table space, Composed of data files, as well as details III for each data file. View the objects in each table space and the detailed storage feature of these objects i) TableSpace map: i. Table / index in graphical display tablespace The space occupied, the size of each segment, segment is continuous, and it is very intuitive II. J) Server Statistics: i. It can be very intuitive to see the overall operation of the system, quickly locate the system bottleneck. II. You can view the system The condition of the class waiting event, see the situation of each connection session, K) Pinned Code: i. We know that frequently executed large stored procedures is best to PIN, which avoids replacement of memory by Oracle in the case of insufficient memory Avoid dynamic loading, and avoiding the Flush Shared Pool to be cleared.

II. Depending on the size of each module, the number of execution is determined whether to put a stored procedure PIN. A button is getting it. l) Control Files: You can view which control files in the current system, and the details of the internal records of the file. (In fact, V $ ControlFile, V $ controlfile_record_section) m) redo log manager: i. It can be easily viewed for log group, current log, member name, size, convenient increase in each group, delete LOG group, switch Current logs, specific controls for log archives. Ii. You can complete almost all of the REDO LOG in this interface. n) Repair Chained Rows: i. Chained Rows affects system performance, in system performance, you need to repair CHAINED ROWS to a normal table. This can be easier to use Repaie Chained Rows. Choose the table that needs to be analyzed, then analyze it, see if there is a chained rower, if you have, click Repair, toad will help you fix those chained rows. o) Identify Space deficits If the system has 1659, 1653 errors, it is a unqualified DBA. But to foresee these errors instantly, there is still a certain difficulty. After this gadget with toad, a little table space, you know if the remaining free space in this table space allows any table to be extended to the next extent. And can directly correct problems after discovering problems. p) Estimate Table / Index Size i. In the physical plan of the database, the definition of the storage clause of the table / index is very important, affecting the system. But if there is no tool, it is not easy to define a reasonable storage clause for the table. II. Inside toad, special tools help you define the storage of the table / index: 1. You can define the number of data that needs to be stored in the future 2. A variety of estimation methods: a) According to the existing data Analysis to define the size of the table (most accurate, but relatively slow) b) Define the size of the table according to the table defined data type C) estimated the size according to the previously analyzed data in the data dictionary (requires recent analysis of the table) Q) Analyze All Tables i. CBO-based SQL requires CBO to provide optimized execution paths to improve the execution efficiency of the program, but if you do a lot of DML operations on the table, you don't update the table's statistics, this time CBO can't choose the most Effective execution path, resulting in low program execution efficiency. Ii. Handmade analysis table is more troublesome, one is done. III. With Toad's Analyze AllTables, you can analyze all tables below, there is a wealth of control options, including the estimation or complete, whether to analyze indexes, if you estimate, how much percentage is used. r) Rebuild Table i. After the data in the table is repeated, the table may become very broken, the table may become very broken, the index will be quite broken, resulting in the unreasonable case of PCTFREE and PCTUSED. The efficiency is quite low. Ii. Traditional way is to solve this problem by EXP / IMP, but steps are more troublesome, and hand-working also has certain hazards.