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

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: