DBMS_XMLGEN: SAve SQL query result in XML

DBMS_XMLGEN is a useful Oracle package that gives you a query result in XML file. Couple of days ago I have got a task to export all tables of an schema in different XML files. Here  is the PL/SQL code


grant read,write on directory DUMP_DIR to scott;
grant execute on DBMS_XMLGEN to scott;
grant execute on utl_file to scott;

set serveroutput on;
set echo on;
set timing on;
spool export_schema_xml.log;
DECLARE
  qryCtx DBMS_XMLGEN.ctxHandle;
  l_output utl_file.file_type;
  l_amount NUMBER default 4000;
  l_offset NUMBER(38) default 1;
  l_length NUMBER(38);
  sqltext VARCHAR2 (4000 CHAR);
  result CLOB;
  l_buffer VARCHAR2 (4000 CHAR);
BEGIN
FOR x IN (SELECT x.table_name FROM user_tables x)
LOOP     
  l_offset :=1;
  sqltext := 'SELECT * FROM '||x.table_name;
  qryCtx :=  dbms_xmlgen.newContext (sqltext);
  DBMS_XMLGEN.SETCONVERTSPECIALCHARS (qryCtx,FALSE);
  result :=  DBMS_XMLGEN.getXML(qryCtx);
  l_output := utl_file.fopen('DUMP_DIR', x.table_name||'.xml', 'w', 32760);
  l_length:=nvl(dbms_lob.getlength(result),0);
  WHILE ( l_offset < l_length AND l_length > 0 )
  LOOP
    DBMS_LOB.READ (result, l_amount, l_offset, l_buffer);
    utl_file.put (l_output, l_buffer);
    utl_file.fflush(l_output);
      utl_file.fflush(l_output);
    l_offset := l_offset + l_amount;
  END LOOP;
  utl_file.fflush(l_output);
  utl_file.fclose(l_output);
  DBMS_XMLGEN.CLOSECONTEXT (qryCtx);
  dbms_output.put_line(x.table_name||' Data Exported');
END LOOP;
END;
/

spool off;

ORA-03113: end-of-file on communication channel

ERROR
ORA-03113: end-of-file on communication channel
Process ID: 28105
Session ID: 130 Serial number: 5

Cause: This error may pop-up when you trying to start-up a database after executing 'shutdown abort' or unexpected shutdown of db due to powe failure. This error occurs when oracle fail to archive online redo log file.

Action: if your db is in archivelog mode then bring the db in noarchivelog mode and startup the db. Then, execute a normal shutdown and bring back the db in archivelog mode.

SQL> startup mount;
SQL> alter database noarchivelog;
SQL> alter database open;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

Finding Duplicate SQL

The presence of duplicate SQL indicate that there are some SQL statements which doesn't have Bind variables. These duplicate SQL can raise performance issue because they will increase the number of hard parse in database.

ORACLE 10g introduced two new columns in v$sql view, which can help to identifing duplicate SQL more accurately. Those two new columns are:

1. force_matching_signature 
2. exact_matching_signature

exact_matching_signature - If two or more SQL has same value in this column, ORACLE assumes they are same after making some cosmetic adjustments (removing white space, uppercasing all keywords etc) to them. The is simmiler, when parameter cursor_sharing is set to EXACT.

force_matching_signature - the same value in this column (excluding 0) marks SQLs that ORACLE will consider they are same when it replaces all literals with binds (that is, if cursor_sharing=FORCE).



SELECT sql_text , count(1)
FROM v$sql
WHERE force_matching_signature > 0
  AND force_matching_signature <> exact_matching_signature
GROUP BY sql_text
HAVING count(1) > 10
ORDER BY 2;