Restore Standard/Enterprise Dump to Express

Question

I have a need to restore a dump (which is less than 1GB). Can I restore a dump that was made in either enterprise or standard to express? I'm asking because I know from experience with SQL that this is impossible. I've been looking and I can't see to locate if Oracle has the same limitations.

Answer 1

As far as I can tell, yes - you can import a DMP file whose origin is Standard or Enterprise edition into an Express edition database.

However, there are certain restrictions. One of them seems to be OK - size of user data (you have less than 1 GB, while 11g XE accepts up to 11 GB).

If tables are partitioned in the source database, they won't be imported as XE doesn't support partitioning. (Though, if you pre-create such a table and import only data, that should also be OK.)

The same goes for other features that aren't supported by XE (such as internal Java virtual machine, materialized view query rewrite) - they won't be imported.

If tablespace names are different in the source and XE database, don't forget to include the REMAP_TABLESPACES parameter.

I suggest you give it a try; XE is a nice tool to play with. Review the log file - it'll tell you which objects weren't imported and why. Good thing is that - even if something fails to import - it won't stop other objects from importing. Then you'll know whether the final result suits your needs or not.