Home » RDBMS Server » Server Utilities » how to import user with network link with limitation rights (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ,windows server 2008,)
how to import user with network link with limitation rights [message #639689] Tue, 14 July 2015 04:54 Go to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
hello i have two database one its production "prod" and second its test name "test" . i import my one user name "EST" from my production database to test database using network link .
for this i did this steps on my test database

i create user

create user est identified by est;


grant dba to est;


conn est/est@test

create or replace directory practice as 'E:\practice';
create database link old_est connect to est identified by est using 'prod';
host impdp est/est@test directory=practice network_link=old_est remap_schema=est:est logfile=latest.log exclude=statistics


but its give me error when i import 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user


actually my production database user have not have dba role but i assing him export full database system privillage to my this est user on my production database but still its give me same error.

can you tell me how to import it successfully with limited access

Report message to a moderator

Re: how to import user with network link with limitation rights [message #639690 is a reply to message #639689] Tue, 14 July 2015 04:57 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, you created a directory, but - did you grant privileges to user who is going to use that directory? Such as
create directory ...
grant read, write on directory practice to est;

Report message to a moderator

Re: how to import user with network link with limitation rights [message #639693 is a reply to message #639689] Tue, 14 July 2015 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

If you don't specify schemas or tables then FULL is assumed and so you can't do it with limited access.
If you want to export/import only EST schema then specify it and you don't need any specific privilege.

In addition, don't you think that "remap_schema=est:est" is quite silly?

Report message to a moderator

Re: how to import user with network link with limitation rights [message #639694 is a reply to message #639690] Tue, 14 July 2015 05:54 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
thanks for your reply

littlefoot actually i create directory with this my est user.so i think i not need read write privilege to be grant on my est user.but still i did this and its show me one more error


grant read, write on directory practice to est
                                           *
ERROR at line 1:
ORA-01749: you may not GRANT/REVOKE privileges to/from yourself



Database link created.


Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 14 July, 2015 16:14:11

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user


and Michel Cadot i just only want to import this est user only ..actually i want to import daily this est user from my production database to my test database using network link

[Updated on: Tue, 14 July 2015 05:57]

Report message to a moderator

Re: how to import user with network link with limitation rights [message #639695 is a reply to message #639694] Tue, 14 July 2015 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
i create directory with this my est user.so i think i not need read write privilege to be grant on my est user.but still i did this and its show me one more error


1/ You do not need it because it has the DBA role but EST is not the owner of the directory.
2/ The error is the same one for any object privilege not the fact you can own it or not. Try with another account and you will see you can.

Report message to a moderator

Re: how to import user with network link with limitation rights [message #639696 is a reply to message #639694] Tue, 14 July 2015 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
and Michel Cadot i just only want to import this est user only ..actually i want to import daily this est user from my production database to my test database using network link


So you have to specify a SCHEMAS parameter.

Report message to a moderator

Re: how to import user with network link with limitation rights [message #639697 is a reply to message #639696] Tue, 14 July 2015 06:21 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
thanks michel i did this but same error



conn est/est@test

create or replace directory practice as 'E:\practice';
create database link old_est connect to est identified by est using 'prod';
host impdp est/est@test directory=practice network_link=old_est schemas=est logfile=latest.log exclude=statistics


but same error as its before

Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 14 July, 2015 16:40:05

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Pr
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user

Report message to a moderator

Re: how to import user with network link with limitation rights [message #639702 is a reply to message #639697] Tue, 14 July 2015 07:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

It works for me:
SQL> create user est identified by est quota unlimited on ts_d01;

User created.

SQL> create table est.t (val integer);

Table created.

SQL> grant create session, create table, create database link to est;

Grant succeeded.

SQL> grant read, write on directory DATA_PUMP_DIR to est;

Grant succeeded.

SQL> connect est/est
Connected.
EST> create database link mika_est connect to est identified by est using 'MIKA';

Database link created.

EST> select * from dual@mika_est;
D
-
X

1 row selected.

EST> host impdp est/est network_link=mika_est directory=DATA_PUMP_DIR;

Import: Release 10.2.0.4.0 - Production on Mardi, 14 Juillet, 2015 14:56:01

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Starting "EST"."SYS_IMPORT_SCHEMA_01":  est/******** network_link=mika_est directory=DATA_PUMP_DIR
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
...

So now copy and paste what you do like I did.

Report message to a moderator

Re: how to import user with network link with limitation rights [message #639754 is a reply to message #639702] Wed, 15 July 2015 05:22 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
you are right michel
but when i create this est user on my test database i grant him dba role and my production database est user not have dba role.so that reason its give me error.i did this steps now its work

i this steps on my test database

sqlplus sys/*****@test as sysdba

create user est identified by est;

grant CONNECT,RESOURCE,create database link to est;

create or replace directory practice as 'E:\practice';

grant read, write on directory practice to est;

conn est/est@test

create database link old_est connect to est identified by est using 'prod';

host impdp est/est@test directory=practice network_link=old_est schemas=est logfile=latest.log exclude=statistics

Report message to a moderator

Re: how to import user with network link with limitation rights [message #639756 is a reply to message #639754] Wed, 15 July 2015 05:35 Go to previous messageGo to next message
gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK
Senior Member
It think that Michel would also like you to show the result of each command you enter. Copy and paste the whole session.

Report message to a moderator

Re: how to import user with network link with limitation rights [message #639760 is a reply to message #639756] Wed, 15 July 2015 05:47 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
actuly my question is if my one database user have on my production database server have no dba role and if my test database user have dba role so its not import its give me error while importing.

Report message to a moderator

Re: how to import user with network link with limitation rights [message #639761 is a reply to message #639760] Wed, 15 July 2015 05:50 Go to previous messageGo to next message
gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK
Senior Member
Why have you granted DBA on the test system then?

Report message to a moderator

Re: how to import user with network link with limitation rights [message #639763 is a reply to message #639760] Wed, 15 July 2015 06:24 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

I showed you that in BOTH databases the user has only "create session, create table, create database link", no DBA involved, not required.

So do the same thing for both your databases, use SQL*Plus and copy and paste the WHOLE sessions.

Report message to a moderator

Previous Topic: Datapump Import Parallel not effect in Index Creation
Next Topic: SQL Loader - Failure
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Feb 07 01:08:27 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.