Home » RDBMS Server » Server Utilities » datapump export import
datapump export import [message #661750] Thu, 30 March 2017 10:20 Go to next message
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
Re: datapump export import [message #661751 is a reply to message #661750] Thu, 30 March 2017 10:44 Go to previous messageGo to next message
BlackSwan
Messages: 25716
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.
Re: datapump export import [message #661754 is a reply to message #661750] Thu, 30 March 2017 11:00 Go to previous messageGo to next message
Michel Cadot
Messages: 65199
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: datapump export import [message #661756 is a reply to message #661754] Thu, 30 March 2017 12:31 Go to previous messageGo to next message
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..
Re: datapump export import [message #661757 is a reply to message #661756] Thu, 30 March 2017 12:48 Go to previous messageGo to next message
joy_division
Messages: 4794
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 Go to previous messageGo to next message
BlackSwan
Messages: 25716
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> 

Re: datapump export import [message #661759 is a reply to message #661758] Thu, 30 March 2017 13:01 Go to previous message
joy_division
Messages: 4794
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.
Previous Topic: SQL Loader parallel sessions
Next Topic: sql loader not loading file
Goto Forum:
  


Current Time: Mon Nov 20 20:47:18 CST 2017

Total time taken to generate the page: 0.03526 seconds