Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, oracle.

Asked: November 07, 2016 - 6:29 am UTC

Last updated: November 09, 2016 - 1:55 am UTC

Version: 11.2.0

Viewed 1000+ times

You Asked

Hi team,

I wanted to know that when i export entire schema from one database and import into anothere database then-

Objects like - tables,functions,triggers,procedures,dblinks,synonyms,public synonyms and many more.

Which objects export during schemas export ?
Which exported objects import during import ?

like public synonyms not import like this .

Pls let me know

and Connor said...

Anything *owned* by that schema is exported. But you can see from the datapump export the different items that are included in the file, eg, here's a schema export on 12c

Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 477.5 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/XMLSCHEMA/XMLSCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/RLS_POLICY/RLS_POLICY
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/JAVA_SOURCE/JAVA_SOURCE
Processing object type SCHEMA_EXPORT/JAVA_CLASS/JAVA_CLASS
Processing object type SCHEMA_EXPORT/JAVA_RESOURCE/JAVA_RESOURCE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/EVENT/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Processing object type SCHEMA_EXPORT/POST_SCHEMA/GRANT/PROCOBJ_GRANT
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/ASSOCIATION
. . exported "MCDONAC"."T1"                              11.30 MB   99213 rows
. . exported "MCDONAC"."T2"                              11.30 MB   99250 rows
...
...
. . exported "MCDONAC"."PAR_DEMO":"P_201605"                 0 KB       0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  C:\ORACLE\ADMIN\NP12\DPDUMP\EXPDAT.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Nov 7 19:58:21 2016 elapsed 0 00:02:21


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, November 08, 2016 - 4:40 am UTC

Thanks for reply

I also having the same but after import I checked the public synonyms not created.
I have go through some sites they saying public synonyms will not be create during import so I have a doubt is it may be.


Connor McDonald
November 09, 2016 - 1:55 am UTC

Look closer at that list of object types in the datapump - there are no references to public synonyms
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.