I'm an Oracle noob, and my intention is to transfer all data and metadata from one schema to another schema within an Oracle database. I'm planning to use datapump's expdp
and impdp
commands. I have questions regarding this:
- Can I create a target schema without a user or should I create a user first (which creates a schema also)?
- Can I execute
expdp
andimpdp
commands using SYS (as sysdba)
account? Is that a preferred method? -
Does this statement take all the objects (data and metadata) from a schema and move these into a different schema?
expdp \"/ as sysdba\" schemas=<schemaname> directory=dumpdir dumpfile=<schemaname>.dmp logfile=expdp_<schemaname>.log
So is the target schema an exact copy of the source schema after
impdp
command?
Best Answer
impdp
will create the user if it's not present yet, so you don't have to worry about it unless that's not what you want.Do not run
impdb
orexpdp
assysdba
, only do that if Oracle support requests it in specific circumstances. Use an ordinary user for that - one that has been granted thedba
role for instance. (There are the[IMPORT|EXPORT]_FULL_DATABASE
privileges specifically for this type of thing, you'll need to grant access to the Oracle directory object(s) too.)A full schema export (metadata and contents) would indeed look like:
If you want to import to a different user/schema (the target database can be the same as the source), you can use:
If you don't want a complete import, you can set some filters both on data and metadata. See Filtering During Import Operations.
The Utilities Guide has all the details, I strongly recommend reading at least the overview part.