Regardless of the reason why you are playing with a specific SQL (tune the SQL, avoid an ORA error, work around a wrong result, etc) it’s always helpful to be able to reproduce the same behavior in some other environment so that you can break everything with no impact on the application or the users.

Creating a SQL Testcase requires put together quite a lot of info and it can be very time consuming, SQLTXPLAIN (SQLT) does an amazing job at that (details here) on top of a lot of other things. Anyway SQLT isn’t always around plus it’s not just a TC generator, SQLT comes with a lot of other info and it might take some time to extract them.

Starting 11gR1 Oracle introduced Testcase Builder (TCB) as part of the Oracle Database Fault Diagnosability Infrastructure (ADRCI, DBMS_HM and DBMS_SQLDIAG just to keep it simple). Basically it’s a set of APIs to generate a testcase starting from either a SQL ID or a SQL text.

The easiest way to extract a TC is to use EXPORT_SQL_TESTCASE passing a SQL_ID.

SQL> select sql_id, sql_text from v$sql where sql_text = 'select * from test_tcb';

SQL_ID        SQL_TEXT
------------- -------------------------------
czq6v85ut47uq select * from test_tcb

SQL> var c1 clob

SQL> begin
 dbms_sqldiag.export_sql_testcase(directory => 'TCB_DIR',
                                  sql_id    => 'czq6v85ut47uq', 
                                  testcase  => :c1);
end;
/

All the files will have a system generated common prefix (since we didn’t specify any value for parameter testcase_name). The number of files change depending on the value for the parameters, we used all the default here so the list will look more or less like this:

oratcb1_000657400001dpexp.dmp	oratcb1_000657400001sql.xml
oratcb1_000657400001dpexp.log	oratcb1_000657400001ssimp.sql
oratcb1_000657400001dpexp.sql	oratcb1_000657400001.trc
oratcb1_000657400001dpimp.sql	oratcb1_000657400001ts.xml
oratcb1_000657400001main.xml	oratcb1_000657400001xplf.sql
oratcb1_000657400001ol.xml	oratcb1_000657400001xplo.sql
oratcb1_000657400001prmimp.sql	oratcb1_000657400001xpls.sql
oratcb1_000657400001README.txt	oratcb1_000657400001xpl.txt
oratcb1_000657400001smrpt.html

The most important ones are

1. the README – it includes instructions on how to import the TC
2. the main.xml – it has the reference/map to every other file
3. the dpexp.dmp – this is the expdp of our objects (and eventually data)
4. the xpl*.sql – those scripts allow to reply (explain or run) our SQL with/without outline

Now it’s just a matter of moving the files to another enviornment and import the TC back.

SQL> begin
 dbms_sqldiag.import_sql_testcase(directory => 'TCB_DIR',
                                  filename  =>'oratcb1_000657400001main.xml');
end;
/

SQL> select * from test_tcb;  -- or any of the xpl*.sql files

no rows selected

TCB also has the ability to provide some other useful info, in details:

• it can export data (also just a sample of it) and PL/SQL package bodies if needed (they are usually not), both are not exported by default.
• it can extract runtime info like Dynamic Sampling, Dynamic Plan, list of binds, etc
• it can extract AWR reports and SQL Monitors for the SQL
• it can export the history of the statistics since day X
• it can run the SQL N times and capture runtime info at the end of each exec

Cherry on the cake, SQLd360 exports automatically the TC using TCB since the version v1501 so there is no additional step to perform, just need to execute SQLd360 😀

I’ve been using TCB every time I needed a TC for a while now and so far it always worked well for me, just a couple “problems” worth mentioning are

• the ctrlOptions parameter allows a lot of flexibility but it doesn’t raise any error if the syntax is wrong (this is expected) so you don’t realize you messed up the syntax until the TC is generated and the info you wanted are missing
• even though there is a parameter ignoreStorage in the IMPORT_SQL_TESTCASE, the storage clauses are not ignored if the mail.xml references the list of tablespaces (and it always does). The workaround is to edit the main.xml and remove the reference to the tablespaces file (ts.xml)

Least and absolutely not last, TCB has the ability to extract info directly from the kernel, which are not exposed to any SQL and PL/SQL friendly interface so it sounds like TCB will be *THE* way to extract TC in the future so better get familiar with it now 😉