3

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

Improve this question
2
  • Have you looked at this answer from stackoverflow: stackoverflow.com/questions/10886450/…
    – Ozzie
    Commented Jul 23, 2015 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
    Commented Jul 23, 2015 at 11:04

3 Answers 3

Reset to default
1

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.

Improve this answer
1

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

HTH.

Improve this answer
0

For those looking how to export data with the tool sqlcl and datapump, the parameter to control, whether to include data or not is includerows. Compare to docs at https://docs.oracle.com/en/database/oracle/sql-developer-command-line/21.4/sqcug/data-pump.html

so includerows=false does the same as content=metadata_only in earlier software.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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