Building SQL Test Cases
For many SQL-related problems, obtaining a reproducible test case makes it easier to resolve the problem. Starting with the 11g Release 2 (11.2), Oracle Database contains the SQL Test Case Builder, which automates the somewhat 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 which the problem occurred, so that you can reproduce and test the problem on a separate database. After the test case is ready, you can upload the problem to Oracle Support to enable support personnel to reproduce and troubleshoot the problem.
The information gathered by SQL Test Case Builder includes the query being executed, table and index definitions (but not the actual data), PL/SQL functions, procedures, and packages, optimizer statistics, and initialization parameter settings.
Creating a Test Case
You can access the SQL Test Case Builder from Enterprise Manager or manually using the DBMS_SQLDIAG package.
Accessing SQL Test Case Builder from Enterprise Manager
From Enterprise Manager, the SQL Test Case Builder is accessible only when a SQL incident occurs. A SQL-related problem is referred to as a SQL incident, and each SQL incident is 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 incidents). This opens the Support Workbench page, with the incidents listed in a table.
- Click Advisor Central under Related Links to 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 the Support Workbench page, to access the SQL Test Case Builder:
- Click an incident ID to open the problem details for the particular incident.
- Next, click Oracle Support in the Investigate and Resolve section.
- Click Generate Additional Dumps and Test Cases.
- For a particular incident, click the icon in the Go To Task column to run the SQL Test Case Builder.
The output of the SQL Test Case Builder is 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 in 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 be as follows:
$ORACLE_HOME/log/diag/rdbms/dbsa/dbsa/incident/incdir_2657/SQLTCB_1
Accessing SQL Test Case Builder Using DBMS_SQLDIAG
You can also invoke the SQL Test Case Builder manually, using the DBMS_SQLDIAG package. This package consists of various subprograms for the SQL Test Case Builder, some of which are listed in Table
SQL Test Case Builder Procedures in DBMS_SQLDIAG
EXPORT_SQL_TESTCASE: Generates a SQL test case
EXPORT_SQL_TESTCASE_DIR_BY_INC: Generates a SQL test case corresponding to the incident ID passed as an argument
EXPORT_SQL_TESTCASE_DIR_BY_TXT: Generates a SQL test case corresponding to the SQL text passed as an argument
参考至:http://docs.oracle.com/cd/E11882_01/server.112/e41573/sql_overview.htm#PFGRF94838
本文原创,转载请注明出处、作者
如有错误,欢迎指正
邮箱:czmcj@163.com
相关推荐
Oracle Database 11g:SQL 基础I 学生指南第1 册 学完本课后,应能完成下列工作: • 阐述本课程的目标 • 列出Oracle Database 11g 的功能 • 从理论和实际实施两方面讨论关系数据库 • 描述RDBMS 和对象关系数据库...
第1章 在windows 平台上安装oracle database 11g 第2章 配置网络服务名 第3章 使用sql database 第4章 使用sql*plus 第二部分 sql 第5章 sql和pl/sql综述 第6章 简单查询 第7章 sql单行函数 第8章 ...
Oracle 11g SQL和PL SQL从入门到精通 pdf格式电子书 下载(一) http://download.csdn.net/source/3268267 Oracle 11g SQL和PL SQL从入门到精通 pdf格式电子书 下载(二) ...
ORACLE 11g SQL 权威指南第十六章 SQL优化
Oracle 11g SQL Fundamentals I
oracle11g+sqlserver2000+透明网关11g配置 原创
本书是专门为Oracle应用开发人员提供的SQL和PL/SQL编程指南。通过学习本书,读者不仅可以掌握Oracle常用工具Oracle Universal Installer、Net Comfiguration Assistant、SQL Developer、SQL*Plus的作用及使用方法,...
Oracle 11g SQL和PLSQL源代码 Oracle 11g SQL和PLSQL源代码
本文详细描述了在oracle10g或11g中使用dblink访问sql server,使用oracle的透明网关来访问异构的数据库,方便一些习惯使用oracle的开发人员操作sql server数据库
Oracle Database 11g Introduction to SQL 简介SQL PL/SQL
目前非常流行的数据库oracle11g对SQL语句的官方参考文档,里面权威地详述了SQL语句在oracle11g中的使用方法
第1章 在windows 平台上安装oracle database 11g 第2章 配置网络服务名 第3章 使用sql database 第4章 使用sql*plus 第二部分 sql 第5章 sql和pl/sql综述 第6章 简单查询 第7章 sql单行函数 第8章 ...
ORACLE11G SQL Language Reference ....... e41084
Oracle 11g SQL Fundamentals II Studednt guide
清华大学出版社出版的《Oracle database 11g SQL开发指南》,本书是由著名的Oracle大师Joson Price著。 学习通过编写SQL语句并构建PL/SQL程序来访问Oracle数据库。完全涵盖了最新版本Oracle数据库的功能和技术,指导...
oracle 11G SQL
Oracle Database 11g SQL 开发指南.part1
清华大学出版社出版的《Oracle database 11g SQL开发指南》,本书是由著名的Oracle大师Joson Price著。 学习通过编写SQL语句并构建PL/SQL程序来访问Oracle数据库。完全涵盖了最新版本Oracle数据库的功能和技术,指导...
oracle11g官方中文帮助 Oracle 11g:ORACLE ACTIVE DATA GUARD.pdf Oracle Database 11g 高可用性.pdf Oracle 数据库 11g 中的分区.pdf Oracle 数据库 11g :真正应用测试与可管理性概述.pdf Oracle 数据库 11g...