Export Data to Flat File

Question

I have a newbie question - I searched the web for a long time but I cannot find any answers.

I understand that all packages,procedures and stored program units are stored in system tablespace or sysaux tablespace. My requirement is to copy a few schemas from a source database into a target database. Is it enough to exp the schemas and bring them into the target database ? Will this bring the stored program units also across.Or will I need to export the sys/system schemas ?. I exported one schema and did the following in unix.

strings expdat.dmp|grep -i "package" but this returns nothing. I also opened the exp file and searched for the packages but to no avail. So I created a parfile (tables=somepackagename) but exp complained that the object was not found.

Any Ideas ?

Answer 1

As long as the procedures/functions are owned by the user you are exported, it should come within the export. If you did not found "package" that's maybe because the user you exported did not host any packages (but maybe only standalone procedures and/or functions). Depending of your Oracle version, you may have expdp and option include.

Nicolas.

Answer 2

Yes, the code for packages, procedure, functions, etc. is stored in the SYSTEM tablespace, but when you export a schema Oracle doesn't care about this - it captures the DDL necessary to recreate the object in the new database.

As N. Gasparotto mentions, be sure that the schema you're exporting actually owns the objects of interest. You can get a quick summary of objects owned by connecting to the source database with an account that has catalog privileges and issuing this query:

select owner, object_type, count(*)
from dba_objects
group by owner, object_type
;

(For example, you might see only synonyms for the schema of interest and therefore it don't actually own the packages)

The newer expdp/impdp utilites have more features, but have the disadvantage of requiring both access to the database host filesystem and privileges to create Oracle directory objects. exp/imp should be fine for what you need to do.