0

We have an old DB server, running Oracle 11g on Solaris.

We also have a new DB server, also running Oracle 11g, but on RHEL.

We want to move a schema to the new db - but we need everything it refers to moved too. The data /tables can be moved easily enough, but then there's sequences, stored procedures, db types, views and maybe other things I'm not aware of yet.

The original schema contains different objects assigned to specific tablespaces and we need the same assignments in the destination server too.

What's the best way to accomplish this?

It looks like expdp/impdp can export and import other object types, but I can't find a list of the types to include, or how to say "everything".

Also DBMS_METADATA.GET_DDL looks useful, but again I can only find examples for specific object types and I want "everything in the schema", ideally.

Paul

  • Hmm. The answer may involve DBMS_METADATA.GET_DDL but I'm not sure yet. – The Archetypal Paul Dec 17 '14 at 9:45
  • I'd say, flag your question to move to dba.stackexchange. You might get a better response there. – Reaces Dec 17 '14 at 9:48
  • Yep. I wasn't aware of that one. Will it move immediately? Otheriwse I should delete this one and create a new one there. – The Archetypal Paul Dec 17 '14 at 9:50
  • AFAIK you still need a moderator to move your question (or a peer review has to happen on the flag). Probably easier to delete and recreate. – Reaces Dec 17 '14 at 9:51
  • Probably is a duplicate of this one, which I didn't spot before: dba.stackexchange.com/questions/30516/… – The Archetypal Paul Dec 17 '14 at 11:26
2

Datapump is one answer.

Look at http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#i1007524 - specifically "Transportable Table mode". Moves the metadata, but not the data. You can choose which schemas to include with the SCHEMAS switch, along with the INCLUDE/EXCLUDE parameters.

  • Thanks. The duplicate question also answers this. We're trying datapump right now. – The Archetypal Paul Dec 17 '14 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.