How to get the schema informataion/dump (without data) for an oracle database instance in xml format?

Question

Well.. the question is descriptive enough I guess. What I am looking for is an exact equivalent of the below MySQL command in oracle-

mysqldump --xml --no-data -u[username] -p[pass] [db_instance] > [someXMLfile]

Where on a linux box do I have to run the oracle command? Straight inside the shell would do?

Answer 1

You can get an XML representation of any given table using the GET_XML function in the DBMS_METADATA package. The DBMS_METADATA documentation has an example of generating the XML for all tables in a schema (this excludes the storage clauses, though you can obviously eliminate that call)

set pagesize 0
set long 90000
execute DBMS_METADATA.SET_TRANSFORM_PARAM(
  DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_ALL_TABLES u
     WHERE u.nested='NO' 
     AND (u.iot_type is null or u.iot_type='IOT');
execute DBMS_METADATA.SET_TRANSFORM_PARAM(
   DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');