SQL Test Case
Builder is a diagnostic tool in 11g, back-ported to 10g, that is designed to be
able to obtain a reproducible test case for developers, DBAs, or Oracle
Support.
SQL Test Case Builder Process
SQL Test Case Builder Process
·
Build
and/or identify a data pump directory on your database tier host to export test
case data to.
·
Identify
the SQL_ID for the problematic SQL statement.
·
Run
DBMS_SQLDIAG.EXPORT_SQL_TESTCASE to export a test case to your dump
directory.
·
Zip the
test case dump data and upload to Oracle Support, or transfer to another system
in your environment.
·
Either
the customer or Oracle Support uses DBMS_SQLDIAG.IMPORT_SQL_TESTCASE to import
the test case into their environment.
Exporting the testcase
-----------------------------
Create directory that we’ll use for exporting the test case:
SQL> Create directory testcase_exp as '/u01/app/testcase';
Directory created.
Use v$sql to get the SQL identifier.
SQL> Select SQL_ID,SQL_TEXT from v$sql where SQL_ID='18z53kqpbwj69';
SQL_ID SQL_TEXT
------------- ----------------------------------------
18z53kqpbwj69 Select * from departments
The parameter description of EXPORT_SQL_TESTCASE can be found in $ORACLE_HOME/rdbms/admin/dbmsdiag.sql
PARAMETERS:
directory (IN) - directory to store the various generated files
sql_id (IN) - identifier of the statement in the cursor cache
plan_hash_value (IN) - plan hash value of a particula plan of the SQL
exportEnvironment (IN) - TRUE if the compilation environment should be exported
exportMetadata (IN) - TRUE if the definition of the objects referenced in the SQL should be exported.
exportData (IN) - TRUE if the data of the objects referenced in the SQL should be exported.
exportPkgbody (IN) - TRUE if the body of the packages referencedin the SQL should be exported.
samplingPercent (IN) - if exportData is TRUE, specify the sampling percentage to use to create the dump file
ctrlOptions (IN) - opaque control parameters
timeLimit (IN) - how much time should we spend exporting the SQL test case
testcaseName (IN) - an optional name for the SQL test case. This is used to prefix all the generated scripts.
testcaseMetadata (OUT) - the resulting test case
SQL> declare
tc_out clob;
begin
dbms_sqldiag.export_sql_testcase(directory=>'TESTCASE_EXP',
sql_id=>'18z53kqpbwj69',
testcase => tc_out);
end;
/
PL/SQL procedure successfully completed.
Or using an SQL-statement :
SQL> declare
tc_out clob;
begin
dbms_sqldiag.export_sql_testcase(directory=>'TESTCASE_EXP',
sql_text=>'Select * from departments',
testcase => tc_out);
end;
/
A SQL test case contains a set of files needed to help reproduce a SQL problem on a different machine.
It contains:
1. a dump file containing schemas objects and statistics (.dmp)
2. the explain plan for the statements (in advanced mode)
3. diagnostic information gathered on the offending statement
4. an import script to execute to reload the objects.
5. a SQL script to replay system statistics of the source
6. A table of content file describing the SQL test case metadata (xxxxmain.xml).
[oracle@oggOra1 testcase]$ pwd
/u01/app/testcase
[oracle@oggOra1 testcase]$ ls -ltrh
total 364K
-rw-r--r-- 1 oracle oinstall 1.2K Jul 20 19:59 oratcb2_0090000D0001sql.xml
-rw-r--r-- 1 oracle oinstall 2.8K Jul 20 19:59 oratcb2_0090000D0001README.txt
-rw-r--r-- 1 oracle oinstall 637 Jul 20 19:59 oratcb2_0090000D0001ol.xml
-rw-r--r-- 1 oracle oinstall 65 Jul 20 19:59 oratcb2_0090000D0001ts.xml
-rw-r--r-- 1 oracle oinstall 4.8K Jul 20 19:59 oratcb2_0090000D0001dpexp.sql
-rw-r--r-- 1 oracle oinstall 1.3K Jul 20 20:00 oratcb2_0090000D0001dpexp.log
-rw-r----- 1 oracle oinstall 208K Jul 20 20:00 oratcb2_0090000D0001dpexp.dmp
-rw-r--r-- 1 oracle oinstall 2.5K Jul 20 20:00 oratcb2_0090000D0001xpl.txt
-rw-r--r-- 1 oracle oinstall 406 Jul 20 20:00 oratcb2_0090000D0001xpls.sql
-rw-r--r-- 1 oracle oinstall 695 Jul 20 20:00 oratcb2_0090000D0001xplo.sql
-rw-r--r-- 1 oracle oinstall 432 Jul 20 20:00 oratcb2_0090000D0001xplf.sql
-rw-r--r-- 1 oracle oinstall 87K Jul 20 20:00 oratcb2_0090000D0001.trc
-rw-r--r-- 1 oracle oinstall 847 Jul 20 20:00 oratcb2_0090000D0001ssimp.sql
-rw-r--r-- 1 oracle oinstall 1.9K Jul 20 20:00 oratcb2_0090000D0001main.xml
-rw-r--r-- 1 oracle oinstall 4.4K Jul 20 20:00 oratcb2_0090000D0001dpimp.sql
Importing the testcase in another database
-------------------------------------------------------
SQL> connect / as sysdba
SQL> create directory testcase_imp as '<imp_tc-dir>';
SQL> grant read,write on directory testcase_imp to <user>;
SQL> connect <user>/<password>
SQL> begin
dbms_sqldiag.import_sql_testcase(directory=>'TESTCASE_IMP',
filename =>'oratcb2_0090000D0001main.xml');
end;
/
Note:
- You should not run TCB under user SYS
- By default for TCB, the data is NOT exported. In some case data is required, for example, to diagnose wrong result problem. To export data, call export_sql_testcase() with
exportData=>TRUE
- The data will be imported by default, unless turned OFF by
importData=>FALSE
- TCB includes PL/SQL package spec by default , but not the PL/SQL package body. You may need to have the package body as well, for example,to invoke the PL/SQL functions.
To export PL/SQL package body, call export_sql_testcase() with
exportPkgbody=>TRUE
To import PL/SQL package body, call import_sql_testcase() with
importPkgbody=>TRUE
Note: How to grant
another user than SYS to use export_sql_testcase from cursor cache ( SQL_ID) the
following grant are needed
SQL> grant select on V_$SQL to <USER>;
SQL> grant select on V_$SQL_BIND_CAPTURE to <USER>;
SQL> grant select on V_$SQL to <USER>;
SQL> grant select on V_$SQL_BIND_CAPTURE to <USER>;
