OraFAQ Forum: Server Utilities » datapump export import

Home » RDBMS Server » Server Utilities » datapump export import

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

datapump export import [message #661750]

Thu, 30 March 2017 10:20

jokrasa
Messages: 14
Registered: March 2017

Junior Member

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

Report message to a moderator

Re: datapump export import [message #661751 is a reply to message #661750]

Thu, 30 March 2017 10:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

GRANTS are stored in the SYSTEM tablespace.
If the GRANTS were NOT exported, then by definition they can't be imported.

expdp can produce multiple dump files when properly commanded to do so.

Report message to a moderator

Re: datapump export import [message #661754 is a reply to message #661750]

Thu, 30 March 2017 11:00

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What you say is useless if you don't provide the command line AND the COMPLETE log.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: datapump export import [message #661756 is a reply to message #661754]

Thu, 30 March 2017 12:31

jokrasa
Messages: 14
Registered: March 2017

Junior Member

Hmmm right here's 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..

Report message to a moderator

Re: datapump export import [message #661757 is a reply to message #661756]

Thu, 30 March 2017 12:48

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

That's the import, how about the export?
I have never done a tablespace datapump export, but to me logically, it would export "objects" from the tablespace.

Grants are not objects. Nor are functions and procedures for that matter, so to me they would not be exported either, but without seeing the export command, there is no way to tell.

[added]

Oh, look at PARALLEL and FILESIZE parameters to make a manageable export file size.

[Updated on: Thu, 30 March 2017 12:54]

Report message to a moderator

Re: datapump export import [message #661758 is a reply to message #661757]

Thu, 30 March 2017 12:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

joy_division wrote on Thu, 30 March 2017 10:48

Grants are not objects. Nor are functions and procedures,

Oracle disagrees with you WRT functions & procedures

SQL> select object_type, count(*) from dba_objects group by object_type order by 1;

OBJECT_TYPE		  COUNT(*)
----------------------- ----------
CLUSTER 			10
CONSUMER GROUP			18
CONTEXT 			11
DESTINATION			 2
DIMENSION			 5
DIRECTORY			17
EDITION 			 1
EVALUATION CONTEXT		15
FUNCTION		       349
INDEX			      5199
INDEX PARTITION 	       433

OBJECT_TYPE		  COUNT(*)
----------------------- ----------
INDEXTYPE			 7
JAVA CLASS		     31028
JAVA DATA		       309
JAVA RESOURCE		      1154
JAVA SOURCE			 7
JOB				24
JOB CLASS			14
LIBRARY 		       229
LOB			      1327
LOB PARTITION			27
MATERIALIZED VIEW		 2

OBJECT_TYPE		  COUNT(*)
----------------------- ----------
OPERATOR			54
PACKAGE 		      1397
PACKAGE BODY		      1335
PROCEDURE		       209
PROGRAM 			10
QUEUE				34
RESOURCE PLAN			11
RULE				 1
RULE SET			21
SCHEDULE			 4
SCHEDULER GROUP 		 4

OBJECT_TYPE		  COUNT(*)
----------------------- ----------
SEQUENCE		       293
SYNONYM 		     37302
TABLE			      2633
TABLE PARTITION 	       386
TABLE SUBPARTITION		32
TRIGGER 		       714
TYPE			      2638
TYPE BODY		       243
UNDEFINED			15
UNIFIED AUDIT POLICY		 8
VIEW			      6676

OBJECT_TYPE		  COUNT(*)
----------------------- ----------
WINDOW				 9
XML SCHEMA			50

46 rows selected.

SQL>

Report message to a moderator

Re: datapump export import [message #661759 is a reply to message #661758]

Thu, 30 March 2017 13:01

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Yes, true, I more meant that you don't specify a tablespace when creating functions, procedures or grants.

Report message to a moderator

Previous Topic:	issue wit impdp
Next Topic:	sql loader not loading file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 10 11:10:42 CST 2025