How-to create a SQL test case for Oracle support

Since 10.2.0.4, we can create a SQL testcase by dbms_sqldiag.export_sql_testcase package. It can be used by Oracle support to create a reproducible case by using dbms_sqldiag.import_sql_testcase.

Here is a simple case:

1. Export a given SQL

declare
  tc_out clob;
begin
   dbms_sqldiag.export_sql_testcase(directory=>'&dump_dir', 
                                    sql_id=>'&sqlid', 
                                    testcase => tc_out);
end;
/

2. Tar the relevant files created in the dump directory and upload it to oracle support site.
3. Oracle support can import it (It will be imported tables to the current schema)

begin
       dbms_sqldiag.import_sql_testcase(directory=>'IMP_TC',
                             filename =>'&xml_file_name_in_dir');
end;
/

I created a testcase(with domain indexes) by it but Oracle support got issue to import it. Looks like the package has problems to support domain indexes.

So I manully created a case by exp/ump. It includes these files

 README.txt             <-- let oracle support know how to use/imp it
 testcase.dmp           <-- exp dump file
 testcase.sql           <-- probmatic sql 
 testcase_exp.log       <-- export log file 
 testcase_imp.par       <-- import par file 
 testcase_SQL_Plan.txt  <-- sql plan file

You can reference oracle online document to get more about dbms_sqldiag. Also, you can reference oracle support document (Doc ID 727863.1).