DBMS_SQLDIAG.export_sql_testcase, DBMS_SQLDIAG.import_sql_testcase

In some situation you need to reproduce a SQL failure on a different machine for diagnosis or provide to Oracle Support.  Oracle offers the SQL Test Case Builder to reproduce a SQL error via EXPORT_SQL_TESTCASE and IMPORT_SQL_TESTCASE in the DBMS_SQLDIAG package.  Oracle creates the SQL test case as a script that contains SQL statements that will re-create the database objects with runtime information.  In addition, information will be captured are the SQL text, table data (optional), the execution plan, optimizer statistics, bind variables, user privileges, SQL profile, metadata, runtime information, etc..

EXPORT:

     

 SQL> create or replace directory mysql as '/data/oracle/bkup/test/';

  Directory created.

 SQL> grant read,write on directory mysql to anguyen_dba;

  Grant succeeded.

 SQL> grant dba to anguyen_dba;

Grant succeeded.

  1  declare mycase clob;

  2  begin

  3  dbms_sqldiag.export_sql_testcase

  4  (directory => 'MYSQL',

  5  sql_text =>'select * from rodba.edba_vmstats where rownum <=100 order by 1',

  6  user_name => 'ANGUYEN_DBA',

  7  exportData => TRUE,

  8  testcase => mycase

  9  );

10* end;

/

PL/SQL procedure successfully completed.

The export process create several files in directory MYSQL.  By default, Oracle doesn't export the data.  You can set this parameter to TRUE to get the data.  The file name oratcb1_00E2093D0001main.xml contains the metadata for the test case.

IMPORT:

SQL> create or replace directory mysql as '/data/oracle/bkup/test/';

Directory created.

SQL> grant read,write on directory mysql to anguyen_dba;

Grant succeeded.

SQL> grant dba to anguyen_dba;

Grant succeeded.

SQL> connect anguyen_dba

Enter password:

Connected.

SQL> begin

  2  dbms_sqldiag.import_sql_testcase

  3  (directory => 'MYSQL',

  4  filename =>'oratcb1_00E2093D0001main.xml',

  5  importDATA => TRUE);

  6  end;

  7  /

PL/SQL procedure successfully completed.