Migrating everything to do with a schema from one Oracle DB server to another

Question

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

• The Archetypal Paul: Hmm. The answer may involve DBMS_METADATA.GET_DDL but I'm not sure yet.
• Reaces: I'd say, flag your question to move to dba.stackexchange. You might get a better response there.
• The Archetypal Paul: Yep. I wasn't aware of that one. Will it move immediately? Otheriwse I should delete this one and create a new one there.
• Reaces: 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.
• The Archetypal Paul: Probably is a duplicate of this one, which I didn't spot before: dba.stackexchange.com/questions/30516/…

Answer 1

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.

• The Archetypal Paul: Thanks. The duplicate question also answers this. We're trying datapump right now.