I want export my Oracle database with its structure such as tables, schema, users, tablespaces, datafiles and everything but without the contained data.

  • Have you looked at this answer from stackoverflow: stackoverflow.com/questions/10886450/… – Ozzie Jul 23 '15 at 9:14
  • 1
    Thanx you Ozzie. But this is only for schema structure, Actually I want entire oracle instance with out data, I want format my server and reinstall oracle. After that I can upload dataagain , But difficult to create structure again like as tablespace, datafile, redolog and schema. – Lilantha Lakmal Jul 23 '15 at 11:04

Judging by the answer you gave in response to @Ozzie, I'm not sure you can do what you want.

You can export the metadata of an 11gR2 database using expdp (Data Pump) and the METADATA_ONLY flag. See http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm

This will export the structure of your data. This won't include redologs, controlfiles, the supporting files required for the instance.

To recover using this export, you would need to create a new database instance using dbca which has all the logs and supporting structure set up, and then import the metadata using impdp.

If you had a copy of the data files, you could also use transportable tablespaces to move the data to the new instance.

The normal method for backing up the entire database is RMAN. This will include everything that you need to restore the database. I don't believe that RMAN provides the option to exclude the data, though.

It may help if you can explain what you are trying to achieve, then better options may be available.


You can use dbms_metadata to export to generate entire DDL of an Oracle schema, given the post I pointed out earlier in comment: How to generate entire DDL of an Oracle schema (scriptable)?

Using the same package, you can generate the tablespaces DDL:

select 'select dbms_metadata.get_ddl(''TABLESPACE'',''' 
|| tablespace_name 
|| ''') from dual;' from dba_tablespaces


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.