Building SQL Test Cases

Source: Internet
Author: User

For many sql-related problems, obtaining a reproducible test case makes it easier to resolve the problem. Starting with the one-to-oneG Release 2 (11.2), Oracle Database contains the SQL Test case Builder, which automates the S Omewhat difficult and time-consuming process of gathering and reproducing as much information as possible about a problem and the environment in which it occurred.

SQL Test Case Builder captures information pertaining to a sql-related problem, along with the exact environment under WHI Ch The problem occurred, so, can reproduce and test the problem on a separate database. After the test case was ready, you can upload the problem-to-Oracle support-to-enable personnel to reproduce and TR Oubleshoot the problem.

The information gathered by SQL Test case Builder includes the query being executed, table and index definitions The actual data), PL/SQL functions, procedures, and packages, optimizer statistics, and initialization parameter settings.

16.6.1Creating a Test case

You can access the SQL Test case Builder from an Enterprise Manager or manually using the package DBMS_SQLDIAG .

16.6.1.1Accessing SQL Test case Builder from Enterprise Manager

From Enterprise Manager, the SQL Test case Builder was accessible only when a SQL incident occurs. A sql-related problem is referred to as a SQL incident, and each SQL incident are identified by an incident number. You can access the SQL Test case Builder from the Support Workbench page in Enterprise Manager.

You can access the Support Workbench page in either of the following ways:

    • In the Database Home page of Enterprise Manager, under Diagnostic Summary , click the link to Active Incidents (indicating the number of active Inc idents). This opens Support Workbench the page, with the incidents listed in a table.

    • Click Advisor Central under to Related Links open the Advisor Central page. Next, click SQL Advisors and then click here to go to support Workbench to open the Support Workbench page.

From Support Workbench the page, to access the SQL Test case Builder:

  1. Click an incident ID to open the problem details for the particular incident.

  2. Next, click Oracle Support on the section Investigate and Resolve .

  3. Click Generate Additional dumps and Test Cases.

  4. For a particular incident, click the "icon in the Go To Task " column to run of the SQL Test case Builder.

    The output of the SQL Test case Builder was a SQL script that contains the commands required to re-create all the necessary Objects and the environment. SQL Test Case Builder stores the file with the following location, where inc_num refers to the incident number and Run_num refers to the run number:

    $ADR _home/incident/incdir_inc_num/sqltcb_run num

    For example, a valid output file name could is as follows:

    $ORACLE _home/log/diag/rdbms/dbsa/dbsa/incident/incdir_2657/sqltcb_1
16.6.1.2Accessing SQL Test case Builder Using Dbms_sqldiag

You can also invoke the SQL Test case Builder manually, using the package DBMS_SQLDIAG . This is consists of various subprograms for the SQL Test case Builder, some of which is listed in Table 16-1.

Table 16-1 SQL Test case Builder procedures in Dbms_sqldiag

Export_sql_ TESTCASE

TR valign= "Top" align= "left" and
Procedure Name Function
generates a SQL test case

Export_sql_testcase_dir_ By_inc

generates a SQL test case corresponding to the Incident ID passed as a argument

export_sql_testcase_dir_by_txt

Generates a SQL test case corresponding to the SQL text passed as an Argu ment

For more information on the this package and all of its procedures and parameters, see Oracle Database PL/SQL Packages and Types Reference.

http://docs.oracle.com/cd/E11882_01/server.112/e41573/sql_overview.htm#PFGRF94838

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.