Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I would like to export all user and roles objects from Oracle 10g database to another. I googled it but I cannot find out how to do it.

First I use this command:

exp system/root@[DB_Name] file=[Back_file_name] owner=([Owner_Name])

But the problem was that some table settings depend on user privileges. Hence I want to also export user objects.

For example it is possible to export all objects with this command:

exp system/root@[DB_Name] Full=Y file=[Back_file_name]

But this command does not export the needed objects for me.

I would like to export only:

  • tables by owner name
  • all users
  • maybe roles

Is this possible?

share|improve this question

migrated from superuser.com Nov 8 '11 at 14:24

This question came from our site for computer enthusiasts and power users.

2 Answers 2

Data Pump export/import is what you're looking for. You can use this to export your schema definitions and load them into the target database, specifying which type of objects you require.

You can select specific types of objects using the INCLUDE parameter, so to just export tables you would:

expdp include=table ...

Provided you run the export/import as a user with sufficient privileges, this will also create the users on the target when you import.

Full documentation for Oracle 10.2:

Data Pump Export

Data Pump Import

share|improve this answer

As I understood you want to migrate your database from one location to another. First you must create tablespaces that you need on target database, than create users as they are created on source db. The point here is that you must create your users manually, and than give them privileges that they needed. There is no way that you can do it with exp or expdp utility.

After that you can use your code:

exp system/root@[DB_Name] file=[Back_file_name] owner=([Owner_Name])

That will export all objects in your schema.

share|improve this answer
    
So it exist some easy way how export tablespaces and then users objects? –  jam Nov 8 '11 at 20:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.