CODEWITHSUNDEEP
windowsoracle11goracle10g
zack_falcon
zack_falcon

Reputation: 4376

How do I export a 'database' from Oracle 11g XE and import it to Oracle 10.2?

Up to this point, I've never done anything serious with an Oracle database. I know that they are different than what I've handled, like MS SQL.

So, coming from an MS SQL perspective, I can backup a database, copy over the .bak file to another server, restore it, creating a complete copy of the database.

I'm not sure how to do the same for Oracle, much less different versions. I've seen it done mostly with command lines, so I tried this:

$exp owner/owner schemas=tkcsdb directory=dumpdir dumpfile=dBaseName logfile=dBaseLog

And then the SQL Plus returns:

Unknown Parameter Name 'Schemas'. Failed to process parameters. Export terminated unsuccessfully.

I've since replaced the 'schemas' with 'schema' (same error), and I wanted to try using 'tables', though I'm not sure how that would turn out. I'm not even 100% if 'tkcsdb' is the name of the database I'm looking for - I've been unable to list all the databases I have.

What I want to do (which I've done with MS SQL, MySQL, and even IBM DB2 of all things) is looking to be quite difficult to replicate in Oracle.

As with the title, how do I export a 'database' from Oracle 11g XE and import it to Oracle 10.2?

I'm using Windows 7, the target machine is a Linux Fedora of some sort.

Upvotes: 2

Views: 29571

Answers (1)

Frank Schmitt
Frank Schmitt

Reputation: 30775

EXP and IMP are ancient - do not use them unless you absolutely have to. They cannot handle some of the features of newer Oracle versions.

The tools of choice are EXPDP and IMPDP (short for EXP datapump and IMP datapump).

Unfortunately, using them is a little more complicated, because you can run them only on the database server (contrary to old-style EXP/IMP, which you could run from any client computer).

So, to get your schema from the 11g source DB to the 10g target DB, you'll have to:

  • open a terminal session on the 11g DB server
  • run expdp with version set to 10 (so you can import it on the 10g server)
  • copy the dump file fom the 11g server to the datapump directory of the 10g server (look for a directory called "dpump")
  • open a terminal session on the 10g DB server
  • run impdp with your dump file

Example:

expdp scott/tiger@db11g version=10.2 schemas=SCOTT dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

Upvotes: 6

Related Questions