How to backup certain tables in oracle?

I want to continue working on oracle db from home and I need certain tables I created. How do I back them up then restore them?

oracle-10g

user55542

Posted 2011-02-17T22:15:35.547

Reputation: 743

We're going to need more information. Do you want to transfer the tables electronically? What are your home and not-home environments like? Does the home copy need to be synced back in with the not-home copy? – Pops – 2011-02-17T22:20:30.350

I'd suggest asking on serverfault.com – Joel Mansford – 2011-02-17T23:26:12.527

You probably just need to use the export tool from the command line. But you need to read the manual on this as it's not 100% export and then import at home. – Rhys Gibson – 2011-02-18T04:26:01.550

Answers

Completely agree with Sathya. One thing to note -- rather than reading the manual, you can issue "help=Y" to the imp/exp command. To wit:

exp help=y
imp help=y

This will show you the most common parameters you can use, and also show you the default values for each parameter.

myopic.bones

Posted 2011-02-17T22:15:35.547

Reputation: 60

Quick & easy way - use Oracle's exp & imp tools

To export them:

exp scott/tiger file=emp.dmp tables=(emp,dept)

Transfer the dmp file to your destination & then to import them:

imp scott/tiger file=emp.dmp fromuser=scott touser=scott tables=(emp,dept)

Sathyajith Bhat

Posted 2011-02-17T22:15:35.547

Reputation: 58 436

For exporting a table:-

expdp mydbusername/mydbpassword@mydbinstancename tables=mytablename directory=MY_DIR dumpfile=mytablename.dmp

For importing a table:-

impdp mydbusername/mydbpassword@mydbinstancename tables=mytablename directory=MY_DIR dumpfile=mytablename.dmp

For directory parameter you can use default directory DATA_PUMP_DIR or you can use your own directory MY_DIR.

Oracle version used for above commands:-

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

Paramvir Singh Karwal

Posted 2011-02-17T22:15:35.547

Reputation: 101

I remember a tool called ODUMP when I was using Oracle 8i on NetWare. The following web page explains how to import and export data: http://www.orafaq.com/wiki/Import_Export_FAQ

These import and export tools can be used to transfer an entire database or specific tables, but I believe there is a limitation in that the data has to be imported back to the same tablespace (which won't be a problem if you're using Oracle 10g or newer which have support for tablespace renaming).

I'm not seeing any helpful information for the ODUMP command in Google Search results, so I may not be remembering the name of the tool correctly (I haven't used Oracle for quite a few years now since switching to PostgreSQL, but I still regard it as an excellent SQL server).

Randolf Richardson

Posted 2011-02-17T22:15:35.547

Reputation: 14 002

there is a limitation in that the data has to be imported back to the same tablespace there's no such limitation - atleast not on "regular" tables. It will cause a problem on Partitioned Tables. – Sathyajith Bhat – 2011-02-18T07:10:13.350