4

I want to clone my existing oracle database structure without data including packages, users, tablespaces etc. I read some methods but they all copied data as well. Is there anyway out in order to do this?

Thanks in advance.

Improve this question

5 Answers 5

Reset to default
6

Use SQL Developer > Tools > Database Export..

On "Specify Data" do not include any tables.

alt text

Omit packages etc here,

alt text

Fine tune your selection here,

alt text

My trial export has not finished yet but I expect this will work.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

-1 SQL Developer can't export/import information about tablespaces and users as specified in question.
do not have the option to choose no tables
6

Use Oracle exp.exe utility for export. E.g.

EXP.EXE login/password@TNSNAME file=entire_db.dmp owner=(scott, my_user, user2) rows=n grants=y triggers=y

Only rows=n option in command above make sense for your task.

You can import to target database with imp.exe utility.

Look for detailed option list and definition by running this utilities with help=y option.

BUT tablespaces and users on target databases must be created manually before running import. There are no standard tablespace cloning tools (including SQL Developer), but some queries exists for generating such cloning scripts. Examples can be found here and here.

P.S. This question better fits to ServerFault than to StackOverflow ...

Improve this answer

Comments

1

You can do it in toad but its not free tool. (Database Tab->Export->Export DDL)

Improve this answer

Comments

0

Try Oracle Export command with ROWS=N

exp SCOTT/TIGER ROWS=N TRIGGERS=N
Improve this answer

2 Comments

-1: Why triggers excluded? How you deal with tablespaces and users? How to import data?
A full database export (FULL = Y, ROWS = N) will export definitions of all database items, including tablespaces and users, but no data. A similar export with ROWS = Y will include all of the data.
0

Use SQL Developer, Tools > Database Export.

You will need to specify at least one table in the 'data' option because you cannot choose to NOT export any data. Pick a table that has a small number of rows or create a dummy table without any rows beforehand as a workaround.

Improve this answer

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.