No question at this time
DBA Top 10
1 M. Cadot 12400
2 B. Vroman 6300
3 A. Kavsek 5600
4 J. Schnackenberg 3200
5 P. Wisse 3100
6 J. Péran 2200
7 T. Boles 600
8 Z. Hudec 400
8 G. Lambregts 400
8 N. Havard 400
8 F. Pachot 400
8 D. Dave 400
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48572
Total active users1389
Act. users last 24h2
Act. users last hour0
Registered user hits last week114
Registered user hits last month299
Go up

A question about datapump
Next thread: A question about datapump
Prev thread: Recover big table

Message Score Author Date
Hi everyone, So I hope everyone is healthy a...... Joël Péran Nov 25, 2020, 16:43
>>> <i> and users without schemas as well, </i> ...... Score: 800 PtsScore: 800 PtsScore: 800 PtsScore: 800 PtsScore: 800 Pts Michel Cadot Nov 25, 2020, 18:06
Thanks Joel and Michel !! Its helps me to deal ...... Raj Kumar Dec 13, 2020, 06:10

Follow up by mail Click here


Subject: A question about datapump
Author: Joël Péran, France
Date: Nov 25, 2020, 16:43, 59 days ago
Os info: Windows 2012 R2 EE
Oracle info: 12.1.0.2 and 19C
Message: Hi everyone,

So I hope everyone is healthy and safe, working at home as I do ;-)
I have imported data from a production database to a new test database with Datapump. There were many schemas involved, and users without schemas as well, and one profile and some roles.
I did not want to use the full export parameter (but maybe I should have) and as a result of my import, I had a lot of small errors dealing with :
- the profile (the profile was not imported so the user based on this profile was not created
- thus the objects of this user were not created as well
- many packages and procedures were not compiled due to lack of rights on "dba" objects (ex: dbms_lock package, v$session view ...)
So my question is simple : do I have to do a full import and just include the schemas I need to and excluding all sys / sysaux schemas ? Do you have tricks to include profiles and roles (roles were not created neither) with the export command ?
And another one :) Some objects where created by mistake on the prod database (a pity) in SYS tablespace. If I use the parameter TABLESPACE to include the tablespaces needed, and if I use a remap_tablespace command, will all my objects be imported even those who were wrongly created ?
Regards,
Joel
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: A question about datapump
Author: Michel Cadot, France
Date: Nov 25, 2020, 18:06, 58 days ago
Score:   Score: 800 PtsScore: 800 PtsScore: 800 PtsScore: 800 PtsScore: 800 Pts
Message:
>>> and users without schemas as well,

All users have a schema (maybe with no objects in it) unless you use Enterprise User which is very seldom used.

>>> the profile (the profile was not imported so the user based on this profile was not created

You can pre-create dummy profiles with the non-existent names.

>>> So my question is simple : do I have to do a full import and just include the schemas I need

If you did a schema level export you won't have the profiles and roles (but object privileges should be there), you need a full export for this.

>>> o you have tricks to include profiles and roles (roles were not created neither) with the export command ?

You can do something like that:
C:\>expdp michel/michel full=yes "include=user:\"in ('SCOTT')\"" "include=schema:\"in ('SCOTT')\"" ^

More? include=profile,role

Export: Release 11.2.0.4.0 - Production on Mer. Nov. 25 18:01:52 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MICHEL"."SYS_EXPORT_FULL_02":
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.125 MB
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
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/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
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/TABLE/FGA_POLICY
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
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/TYPE/TYPE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."SYS_EXPORT_SCHEMA_03" 291.3 KB 1234 rows
. . exported "SCOTT"."SYS_EXPORT_SCHEMA_02" 234.2 KB 1224 rows
. . exported "SCOTT"."SYS_EXPORT_SCHEMA_01" 168.8 KB 1210 rows
. . exported "SCOTT"."DEPT" 5.945 KB 4 rows
. . exported "SCOTT"."EMP" 8.585 KB 15 rows
. . exported "SCOTT"."SALGRADE" 5.867 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "MICHEL"."SYS_EXPORT_FULL_02" successfully loaded/unloaded
******************************************************************************
Dump file set for MICHEL.SYS_EXPORT_FULL_02 is:
E:\ORACLE\SAVE\EXPDAT.DMP
Job "MICHEL"."SYS_EXPORT_FULL_02" successfully completed at...

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: A question about datapump
Author: Raj Kumar, India
Date: Dec 13, 2020, 06:10, 41 days ago
Message: Thanks Joel and Michel !!

Its helps me to deal with the similar situation. g
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here