I tried to do a full Datapump exp on our Dbase. But resorted to just backing up the table spaces we were working on.
Then I imported using table_exists_action=replace;
Afterwards unfortunately all the Grants on objects are lost... grants For procedures and functions and I imagine others as well...
Why would the grants to the procedures be lost after importing using table_exists_action=replace; ? Is there some other way of running the import to get the grants on the prcedures.. in fact everything on the tablespaces I'm importing ?
I suppose I can fix that by doing the Grant on the Objects ( procedures, functions, etc ) one by one provided the procedures apply to the schema they're in. When I do so however a few of them wouldn't get granted/wouldn't work.
Looks like we would really need to be able to accomplish a full dbase exp and imp...
What would we need to have in place for this to work ?
If I tried a full Datapump export I get a fatal error I imagine it's due to the excessive size of the dump file ( 30.25 GB ) to our Linux server backup folder ?? see below
1.Total estimation using BLOCKS method: 30.25 GB
Then..
Total estimation using BLOCKS method: 30.25 GB Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/TABLESPACE Processing object type DATABASE_EXPORT/PROFILE Processing object type DATABASE_EXPORT/SYS_USER/USER Processing object type DATABASE_EXPORT/SCHEMA/USER Processing object type DATABASE_EXPORT/ROLE Processing object type DATABASE_EXPORT/RADM_FPTM Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA Processing object type DATABASE_EXPORT/RESOURCE_COST Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/WITH_GRANT_OPTION/OBJECT_GRANT Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/CROSS_SCHEMA/OBJECT_GRANT Processing object type DATABASE_EXPORT/CONTEXT Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY Job "GAPLITE"."EXPORT_JOB_SQLDEV_3526" stopped due to fatal error at Fri Mar 24 12:05:34 2017 elapsed 0 00:16:47
The cmd:
impdp gaplite/gaplite@mbqas DIRECTORY=exp_gaplite DUMPFILE=gaplite_SLAC.dmp LOGFILE=gaplite_SLAC_march282017.log TABLESPACES=GAPLITE,I_IDB,I_PCAP,REIMB,TANDEM_REFRESH,R_PAYMENT,CAPS,PAYMENT,PCAP,TANDEM,HCOM,IREPORTS,R_IDB,I_PAYMENT,DEFER_V2,CLIEN T_MAPPING,IDB,R_CAPS,R_GAPLITE,I_GAPLITE,DB_MOD,PARTNER_CLAIMS,PCAP_WORK,SDP,R_PCAP,UCS,PCAPEOD,I_CAPS,PREPAYMENT,CDC,I_PREPAYMENT,ER EIMBURSEMENT,AUDITCOLLECTIONS,SLDRYRUN,R_DRUGOPS,R_REIMB,R_TANDEM_REFRESH,IMPORT_CAPS,EVENTS,DRUGOPS table_exists_action=replace;
but it looks like I should include the SYSTEM tablespace to maintain grant objects, etc. or add something like EXCLUDE=CONSTRAINT,REF_CONSTRAINT,INDEX..
