8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
DBMS_SQLDIAG : Export and Import Test Cases
The DBMS_SQLDIAG package can be used to export and import SQL test cases.
Related articles.
Setup
We create two test users. Notice we grant the DBA role to the second user. We need that for the imports to run successfully.
conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba drop user testuser1 cascade; create user testuser1 identified by testuser1 quota unlimited on users; grant connect, resource to testuser1; grant select_catalog_role to testuser1; drop user testuser2 cascade; create user testuser2 identified by testuser2 quota unlimited on users; grant connect, resource to testuser2; grant select_catalog_role to testuser2; grant dba to testuser2;
We need directory objects for the export and import operations. Make sure the directories exists on the operating system.
create or replace directory testcase1_dir as '/tmp/testcase1'; grant read, write on directory testcase1_dir to testuser1, testuser2; create or replace directory testcase2_dir as '/tmp/testcase2'; grant read, write on directory testcase2_dir to testuser1, testuser2; create or replace directory testcase3_dir as '/tmp/testcase3'; grant read, write on directory testcase3_dir to testuser1, testuser2;
We connect to the TESTUSER1 user and create the EMP table.
conn testuser1/testuser1@//localhost:1521/pdb1
create table emp (
empno number(4) constraint pk_emp primary key,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2) );
insert into emp values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into emp values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
insert into emp values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
insert into emp values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
insert into emp values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
insert into emp values (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
insert into emp values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
insert into emp values (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
insert into emp values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
insert into emp values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
insert into emp values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
commit;
exec dbms_stats.gather_table_stats(null, 'emp');
Export SQL Test Case
There are several procedures to export SQL test cases. The export contains all objects necessary to run the statement, which in this case is just the EMP table. By default the rows from any dependent tables are not exported. There are parameters to alter the contents of the export, but in these examples we'll keep things simple.
In this example we use the EXPORT_SQL_TESTCASE procedure to export a SQL test case for a specific SQL statement. In this case the SQL statement is hard coded, but it could be returned from the V$SQL view or AWR. There is a BIND_LIST parameter to supply bind variables if needed.
conn testuser1/testuser1@//localhost:1521/pdb1
variable testcase clob;
begin
sys.dbms_sqldiag.export_sql_testcase (
directory => 'TESTCASE1_DIR',
sql_text => 'select * from emp',
testcase => :testcase);
end;
/
If we check the directory on the database server we can see the files generated by the export.
[oracle@localhost testcase1]$ ls oratcb_3_a2dk8bdn0ujx7_1_00A038C30001diag.trc oratcb_3_a2dk8bdn0ujx7_1_00A038C30001ol.xml oratcb_3_a2dk8bdn0ujx7_1_00A038C30001ts.xml oratcb_3_a2dk8bdn0ujx7_1_00A038C30001dpexp.dmp oratcb_3_a2dk8bdn0ujx7_1_00A038C30001prmimp.sql oratcb_3_a2dk8bdn0ujx7_1_00A038C30001xplf.sql oratcb_3_a2dk8bdn0ujx7_1_00A038C30001dpexp.log oratcb_3_a2dk8bdn0ujx7_1_00A038C30001README.txt oratcb_3_a2dk8bdn0ujx7_1_00A038C30001xplo.sql oratcb_3_a2dk8bdn0ujx7_1_00A038C30001dpexp.sql oratcb_3_a2dk8bdn0ujx7_1_00A038C30001smrpt.html oratcb_3_a2dk8bdn0ujx7_1_00A038C30001xpls.sql oratcb_3_a2dk8bdn0ujx7_1_00A038C30001dpimp.sql oratcb_3_a2dk8bdn0ujx7_1_00A038C30001sql.xml oratcb_3_a2dk8bdn0ujx7_1_00A038C30001main.xml oratcb_3_a2dk8bdn0ujx7_1_00A038C30001ssimp.sql [oracle@localhost testcase1]$
In the next example we use a different overload of the EXPORT_SQL_TESTCASE procedure to export a SQL test case for the SQL statement identified by the specified SQL_ID. First we need to get the SQL_ID.
select * from emp; select sql_id from v$sql where sql_text = 'select * from emp'; SQL_ID ------------- a2dk8bdn0ujx7 SQL>
Now we can export the SQL test case using the SQL_ID and a different directory. We will include the data with this SQL test case.
variable testcase clob;
begin
sys.dbms_sqldiag.export_sql_testcase (
directory => 'TESTCASE2_DIR',
sql_id => 'a2dk8bdn0ujx7',
exportdata => true,
testcase => :testcase);
end;
/
It is also possible to export a SQL test case based on an ADR incident. You can read more about ADR incidents here.
variable testcase clob;
begin
sys.dbms_sqldiag.export_sql_testcase (
directory => 'TESTCASE3_DIR',
incident_id => '71593',
testcase => :testcase);
end;
/
There are also the EXPORT_SQL_TESTCASE_DIR_BY_INC and EXPORT_SQL_TESTCASE_DIR_BY_TXT functions, which are variations of the procedures we've already seen.
In all cases, the resulting files can be zipped up and sent to Oracle Support, or another system for them to be loaded, so the problem can be recreated.
Import SQL Test Case
The IMPORT_SQL_TESTCASE procedure was added to the DBMS_SQLDIAG package in Oracle 11.2. Not surprisingly, it imports a previously exported SQL test case into a new schema. It has similar parameters to the EXPORT_SQL_TESTCASE procedures to control what information is imported, but the defaults import everything from the exported SQL test case.
We connect to the TESTUSER2 user and import the SQL test case using the "*mail.xml" file from the first directory, which doesn't include the table data.
conn testuser2/testuser2@//localhost:1521/pdb1
begin
sys.dbms_sqldiag.import_sql_testcase (
directory => 'TESTCASE1_DIR',
filename => 'oratcb_3_a2dk8bdn0ujx7_1_00A038C30001main.xml');
end;
/
The table has been imported, but it contains no data.
select * from emp; no rows selected SQL>
Despite the data not being present, the table statistics have been preserved, including the high/low values for the columns.
column table_name format a12
select table_name, num_rows, avg_row_len
from user_tables;
TABLE_NAME NUM_ROWS AVG_ROW_LEN
------------ ---------- -----------
EMP 14 38
SQL>
column column_name format a12
column high_value format a16
column low_value format a16
select table_name,
column_name,
case
when column_name in ('EMPNO', 'MGR', 'SAL', 'COMM', 'DEPTNO') then
to_char(utl_raw.cast_to_number(high_value))
when column_name in ('ENAME', 'JOB') then
utl_raw.cast_to_varchar2(high_value)
else
null
end as high_value,
case
when column_name in ('EMPNO', 'MGR', 'SAL', 'COMM', 'DEPTNO') then
to_char(utl_raw.cast_to_number(low_value))
when column_name in ('ENAME', 'JOB') then
utl_raw.cast_to_varchar2(low_value)
else
null
end as low_value
from user_tab_cols;
TABLE_NAME COLUMN_NAME HIGH_VALUE LOW_VALUE
------------ ------------ ---------------- ----------------
EMP EMPNO 7934 7369
EMP ENAME WARD ADAMS
EMP JOB SALESMAN ANALYST
EMP MGR 7902 7566
EMP HIREDATE
EMP SAL 5000 800
EMP COMM 1400 0
EMP DEPTNO 30 10
8 rows selected.
SQL>
Drop the EMP table and load the SQL test case from the second directory, which includes the table data.
drop table emp purge;
begin
sys.dbms_sqldiag.import_sql_testcase (
directory => 'TESTCASE2_DIR',
filename => 'oratcb_3_a2dk8bdn0ujx7_2_0196BED80001main.xml');
end;
/
Now the EMP table has been imported into this schema along with its data.
select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL>
Considerations
Whenever we export any data from our system we need to think of the security implications. Passing SQL test cases to other companies could expose private data, or intellectual property owned by your company. As we've seen in the above example, even when we omit the table data, the statistics could expose personal information.
For more information see:
Hope this helps. Regards Tim...