Oracle 10g – How to Export All Tables with User Using Data Pump

datapumporacleoracle-10g

I want to take backup of all my tables and the user too in Oracle 10g Enterprise edition and then I want to import it to some other computer. The user is not system I have created another user and all my tables are in that user.

Best Answer

You can use Oracle Datapump to perform export-import in schema(user) level.

Here are some examples.

expdp scott/tiger schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

The above command exports SCOTT schema in a dump file called SCOTT.dmp in a directory specified using DIRECTORY parameter, and writes it's log in expdpSCOTT.log file.

You can copy this dump file to another host and use Datapump Import to import it as shown below.

impdp scott/tigerschemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

References:

Data Pump Export

Data Pump Import

CREATE DIRECTORY

Related Solutions

Oracle 10g – How to prevent one user from hogging all resources

There is a built-in utility called Resource Manager exactly for that purpose: http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dbrm.htm#i1010776

Export Oracle tables based on owner + all users from database

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: