How-to create a SQL test case for Oracle support

Since, 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

  tc_out clob;
                                    testcase => tc_out);

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)

                             filename =>'&xml_file_name_in_dir');

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).

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: