Home » RDBMS Server » Server Utilities » Clone Database user (Oracle 11g 11.2.0.1.0)
Clone Database user [message #518035] Mon, 01 August 2011 00:17 Go to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Hi All,

I want to create such a script to clone the Database user with the new name.
Just like we do normal import and export I want that i should enter just the username of the existing user and username of the new user I want to get created, the password for the same.

It should create the new user with all roles and the default roles and privileges of old user.

Thanks N Regards
Deepak
Re: Clone Database user [message #518039 is a reply to message #518035] Mon, 01 August 2011 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on what you mean by "cloning".
Does this include its priviliges?
Does this include its quota?
Does this include public synonym on its objects?
Does this include its data?
...

Give a detailed desciption of your clone.

Regards
Michel
Re: Clone Database user [message #518048 is a reply to message #518039] Mon, 01 August 2011 01:39 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Yes Sir, I want to include everything "privileges,quota,objects,
data.. everything".

I searched over the internet found the scripts which does cloning of user with everything except data.

How I can create a exact duplicate(CLONE) with everything same including data as well.

Thanks
Deepak
Re: Clone Database user [message #518053 is a reply to message #518048] Mon, 01 August 2011 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
expdp will do it very well, and you can use its PL/SQL API: DBMS_DATAPUMP

Regards
Michel

[Updated on: Mon, 01 August 2011 02:01]

Report message to a moderator

Re: Clone Database user [message #518055 is a reply to message #518053] Mon, 01 August 2011 02:21 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Yah, but I want that it should work like this..

Ask the following parameters from me:
"Enter existing username:"
"Enter the new username to be created:"
"Enter the password for the new user: "

and rest the it should do itself, cloning with existing "privileges,quota,objects,data.. everything".

Thanks & Regards
Deepak
Re: Clone Database user [message #518056 is a reply to message #518055] Mon, 01 August 2011 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is why I mentioned DBMS_DATAPUMP, if you want to do it in PL/SQL (procedure or SQL*Plus script).
Otherwise you can do it with expdp generating the parameters with a shell script.

Regards
Michel
Re: Clone Database user [message #518057 is a reply to message #518056] Mon, 01 August 2011 02:29 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Can you help me out with "doing it with expdp generating the parameters with a shell script." with some small example Sir.

Regards
Deepak
Re: Clone Database user [message #518064 is a reply to message #518057] Mon, 01 August 2011 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't post your OS (as it is requested) nor your shell so what can I post?

Regards
Michel
Re: Clone Database user [message #518066 is a reply to message #518056] Mon, 01 August 2011 02:41 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

If you can provide me some link from where I can get some help "to do it with expdp generating the parameters with a shell script."

I'll try to make and then let you know if face some error or challenges.

Thanks & Regards
Deepak
Re: Clone Database user [message #518067 is a reply to message #518064] Mon, 01 August 2011 02:42 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

I am using Windows XP Professional at my end Sir.
"Microsoft Windows XP [Version 5.1.2600]"

Thanks
Deepak

[Updated on: Mon, 01 August 2011 02:44]

Report message to a moderator

Re: Clone Database user [message #518071 is a reply to message #518067] Mon, 01 August 2011 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.google.com/search?q=windows+xp+scripting+-host&hl=en&source=hp&aq=f&aqi=&aql=&oq=

Regards
Michel

[Updated on: Mon, 01 August 2011 02:56]

Report message to a moderator

Re: Clone Database user [message #518104 is a reply to message #518071] Mon, 01 August 2011 04:43 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

This resulted in some tool for scripting "AutoIT", "Installing powershell on Windows XP", not getting it Sir.

Help me.

Thanks
Deepak
Re: Clone Database user [message #518105 is a reply to message #518104] Mon, 01 August 2011 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And tutorials on how to script in windows.
I don't know what is your problem exactly, what you can do and what you can't, I don't anything difficult in writing a script that takes 3 parameters and execute a expdp with content=metadata_only then a impdp with remap_schema and finally a sqlplus to give the new password to the new account.

Regards
Michel
Re: Clone Database user [message #518106 is a reply to message #518104] Mon, 01 August 2011 05:08 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Deepak, you need to read your Windows manual to learn shell scripting. Here's a very simple example of a shell script that prompts for a name and then prints it back:
c:\users\john\home>
c:\users\john\home>
c:\users\john\home>type prompting_shell_script.bat
@echo off
echo Enter a name:
set /p username=
echo you entered %username%

c:\users\john\home>
c:\users\john\home>prompting_shell_script.bat
Enter a name:
john
you entered john

c:\users\john\home>

Re: Clone Database user [message #518122 is a reply to message #518105] Mon, 01 August 2011 07:06 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

How about this?

set /P Old_UserName=Enter Old Username:
set /P New_UserName=Enter New Username:
set /P FileName=Enter Dumpfile Name:

expdp system/oracle file='%FileName%.dmp' directory=DATA_PUMP_DIR logfile='%FileName%_exp.log' schemas=%Old_UserName%

impdp system/oracle file='%FileName%.dmp' directory=DATA_PUMP_DIR logfile='%FileName%_imp.log' REMAP_SCHEMA= %Old_UserName%:%New_UserName%



It is working but I want to merge the create user with all the privileges and role script in the same.

Thanks
Deepak
Re: Clone Database user [message #518123 is a reply to message #518106] Mon, 01 August 2011 07:07 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

I am Really sorry Sir, I just read your message, I also followed the same approach Sir. Thanks for helping can you help for my last reply.

Regards
Deepak
Re: Clone Database user [message #518129 is a reply to message #518122] Mon, 01 August 2011 08:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When you export a schema you also export its privileges.

Regards
Michel
Re: Clone Database user [message #518188 is a reply to message #518129] Tue, 02 August 2011 01:49 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Hi All,

Mentioned below is the code which I have used:

set /P Service_Name=Enter the Service_Name of Database:
sqlplus scott1/tiger@'%Service_Name%' @copy_user_sql.sql
echo   ******Ready to Export the Data of OLD_USER******
echo   ================================================

set /P Old_UserName=Enter Old Username:
set /P New_UserName=Enter New Username:
set /P FileName=Enter Dumpfile Name:
set /P ServiceName=Enter Service Name:

expdp scott1/tiger@'%ServiceName%' file='%FileName%.dmp' directory=DATA_PUMP_DIR logfile='%FileName%_exp.log' schemas=%Old_UserName%

echo   ******Ready to Import Data into NEW_USER******
echo   ================================================

impdp scott1/tiger@'%ServiceName%' file='%FileName%.dmp' directory=DATA_PUMP_DIR logfile='%FileName%_imp.log' REMAP_SCHEMA= %Old_UserName%:%New_UserName%


But the only Drawback which I came accross is that datadump takes alot of time can we over come this drawback?

Thanks
Deepak
Re: Clone Database user [message #518189 is a reply to message #518188] Tue, 02 August 2011 02:01 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You could use a network mode import, so that the export and import run concurrently without staging the dump on disc. It's all in the utilities manual, http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/dp_import.htm#i1006584
Re: Clone Database user [message #518193 is a reply to message #518189] Tue, 02 August 2011 02:11 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Hi Sir, can you modify either of my import or export statement that can help me understand better, I'll really appreciate that Sir.

Thanks
Deepak
Re: Clone Database user [message #518197 is a reply to message #518193] Tue, 02 August 2011 02:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What don't you understand in the example?
The only difference with yours is that you export a schema and not a table.

Regards
Michel
Re: Clone Database user [message #518201 is a reply to message #518197] Tue, 02 August 2011 02:34 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Hi Sir,

The below example is fine for me there is nothing I can't understand:

impdp hr TABLES=employees REMAP_SCHEMA=hr:scott DIRECTORY=dpump_dir1 NETWORK_LINK=dblink


I was just asking for the use of "ImpInit,ImpStart,ImpModes" and others.

Regards
Deepak
Re: Clone Database user [message #518206 is a reply to message #518197] Tue, 02 August 2011 02:47 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Hi All,

I created a database link and try to select data from the table of the database for which I have created the DB link.

Below is the Error:

SQL> create public database link dblink40 using 'dev40';

Database link created.

SQL> select * from sp1_1.VERSION@dblink40;
select * from sp1_1.VERSION@dblink40
                                   *
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from DBLINK40


Below is the entry for the db in the tnsfile.


DEV40 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XX.XX.40)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dev)
    )
  )


Regards
Deepak

[Updated on: Tue, 02 August 2011 02:50]

Report message to a moderator

Re: Clone Database user [message #518212 is a reply to message #518201] Tue, 02 August 2011 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I was just asking for the use of "ImpInit,ImpStart,ImpModes" and others.

Forget them, start with a simple case, make it works and then add new parameters to more precisely fulfil your needs.

Regards
Michel
Re: Clone Database user [message #518213 is a reply to message #518206] Tue, 02 August 2011 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-01017: invalid username/password; logon denied

Your current credentials (user and/or password) do not exist in the remote database.

Regards
Michel
Re: Clone Database user [message #518225 is a reply to message #518213] Tue, 02 August 2011 04:42 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

How this is possible now?

SQL> conn / as sysdba
Connected.
SQL> select * from dba_db_links;

OWNER                          DB_LINK              USERNAME                       HOST              CREATED
------------------------------ -------------------- ------------------------------ -------------------- ---------
PUBLIC                         DBLINK40                                            alpsdev40         02-AUG-11
PUBLIC                         DBLINK101                                           alpsdev           02-AUG-11
PUBLIC                         DEV40                                               ALPSDEV40         02-AUG-11
PUBLIC                         ALPS40                                              ALPSDEV40         02-AUG-11
PUBLIC                         ALPS                                                ALPSDEV           02-AUG-11
VIKRAM                         ALPSDEV              VIKRAM                         ALPSDEV40         02-AUG-11

6 rows selected.

SQL> drop database link DEV40;
drop database link DEV40
                   *
ERROR at line 1:
ORA-02024: database link not found


SQL> drop database link alpsdev;
drop database link alpsdev
                   *
ERROR at line 1:
ORA-02024: database link not found


SQL> drop database link alps;
drop database link alps
                   *
ERROR at line 1:
ORA-02024: database link not found


SQL> drop database link dblink101;
drop database link dblink101
                   *
ERROR at line 1:
ORA-02024: database link not found


Connected to the vikram user and then drop alpsdev dblink and it was dropped but what about other for which there is no user listed;

Regards
Deepak

[Updated on: Tue, 02 August 2011 04:50]

Report message to a moderator

Re: Clone Database user [message #518226 is a reply to message #518225] Tue, 02 August 2011 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
drop database link DEV40;

It is a PUBLIC database link, so "drop PUBLIC database link DEV40;"
Maybe you should read Database SQL Reference
before using a statement.

Regards
Michel
Re: Clone Database user [message #518228 is a reply to message #518226] Tue, 02 August 2011 04:52 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

OOps I am sorry Sir. I'll try other possibilities to access the data of tables using dblink and let you know if face some problem.

Thanks
Deepak
Re: Clone Database user [message #518229 is a reply to message #518226] Tue, 02 August 2011 04:59 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

I have done the change in the parameters on both the database and created a public link for the user whose data I want to access. Below is the result.

SQL> select * from dba_db_links;
no rows selected

SQL> show parameter global_names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_names                         boolean     FALSE

SQL> alter system set global_names=TRUE scope=both;
System altered.

SQL> show parameter global

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_names                         boolean     TRUE

SQL> select * from global_name;

GLOBAL_NAME
------------------------------------------------------------------------------------------------------------------
DEV

SQL> create public database link dblink40 connect to tc33sp1_1 identified by tc33sp1_1 using 'alpsdev40';
Database link created.

SQL> select * from tc33sp1_1.tc_version@dblink40;
select * from tc33sp1_1.tc_version@dblink40
                                   *
ERROR at line 1:
ORA-02085: database link DBLINK40 connects to ALPSDEV


Entry of the tnsnames.ora

ALPSDEV40 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xx.xx.40)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = alpsdev)
    )
  )


Now wats the problem.

Regards
Deepak

[Updated on: Tue, 02 August 2011 05:01]

Report message to a moderator

Re: Clone Database user [message #518231 is a reply to message #518229] Tue, 02 August 2011 05:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-02085: database link %s connects to %s
 *Cause: a database link connected to a database with a different name.
  The connection is rejected.
 *Action: create a database link with the same name as the database it
  connects to, or set global_names=false.

Regards
Michel
Re: Clone Database user [message #518232 is a reply to message #518229] Tue, 02 August 2011 05:11 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

I have two different server with the same name of database, so I have two entries in the tnsnames.ora file one is for .40 and other is for .101.
So I have deleted the entry of .101. Now when I execute the statement:

SQL> create public database link dblink connect to tc33sp1_1 identified by tc33sp1_1 using 'alpsdev';
Database link created.

SQL> select * from tc33sp1_1.tc_version@dblink;
select * from tc33sp1_1.tc_version@dblink
                                   *
ERROR at line 1:
ORA-12170: TNS:Connect timeout occurred

SQL> select * from tc33sp1_1.tc_version@dblink;
select * from tc33sp1_1.tc_version@dblink
                                   *
ERROR at line 1:
ORA-12170: TNS:Connect timeout occurred


The only difference in the two dblinks that I have created in first i have used the SID which I use to connect the remote user
 conn tc33sp1_1/tc33sp1_1@alpsedv40 

and in the other I have used the SERVICE_NAME in the tnsnames.ora file for which I m getting:

SQL> create public database link dblink40 connect to tc33sp1_1 identified by tc33sp1_1 using 'alpsdev40';
Database link created.

SQL> select * from tc33sp1_1.tc_version@dblink40;
select * from tc33sp1_1.tc_version@dblink40
                                   *
ERROR at line 1:
ORA-02085: database link DBLINK40 connects to ALPSDEV


Thanks
Deepak
Re: Clone Database user [message #518234 is a reply to message #518232] Tue, 02 August 2011 05:13 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Hey it worked after setting global_names=false. Thanks for you help Sir now I'll try to take the export and import using the dblink.
I'll update you once done.

Regards
Deepak
Re: Clone Database user [message #518235 is a reply to message #518234] Tue, 02 August 2011 05:23 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Do I have to use "NETWORK_LINK=dblink" in the export "expdp" command as well to make export and import run concurrently.

Regards
Deepak
Re: Clone Database user [message #518236 is a reply to message #518235] Tue, 02 August 2011 05:28 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

When I run the Batch file, I got below mentioned error:


--------------------------------------------------------------------------------
Create a new schema/user like an existing schema/user.
Defaults are shown between brackets [].
--------------------------------------------------------------------------------
Enter existing database schema/user [SCOTT1]:
Enter new database schema/user [SCOTT]: test_sample
Enter password for new database schema/user (Leave blank to copy):
================================================================================
You entered the following information:

Existing database schema/user     : SCOTT1
New database schema/user          : test_sample

If this is correct, press ENTER to generate SQL and PL/SQL statements and
create the new schema/user, otherwise press CTRL+C to cancel and return to the
SQL*Plus prompt.

Make sure you have DBA privileges before continuing!
================================================================================

CREATE USER test_sample IDENTIFIED BY test DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK;
GRANT UNLIMITED TABLESPACE TO test_sample;
GRANT ADMINISTER ANY SQL TUNING SET TO test_sample;
GRANT DBA TO test_sample;
ALTER USER test_sample DEFAULT ROLE DBA;
--------------------------------------------------------------------------------
Creating new database schema/user test_sample like SCOTT1 ...
Only error messages are displayed.
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Ready.
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Documents and Settings\dsharma>echo   ******Ready to Export the Data of OLD_USER******
  ******Ready to Export the Data of OLD_USER******

C:\Documents and Settings\dsharma>echo   ================================================
  ================================================

C:\Documents and Settings\dsharma>set /P Old_UserName=Enter Old Username:
Enter Old Username:scott1

C:\Documents and Settings\dsharma>set /P New_UserName=Enter New Username:
Enter New Username:test_sample

C:\Documents and Settings\dsharma>set /P FileName=Enter Dumpfile Name:
Enter Dumpfile Name:test

C:\Documents and Settings\dsharma>set /P ServiceName=Enter Service Name:
Enter Service Name:alpsdev40

C:\Documents and Settings\dsharma>expdp scott1/tiger@'alpsdev40' file='test.dmp' directory=DATA_PUMP_DIR logfile='test_exp.log' schemas=scott1

Export: Release 11.2.0.1.0 - Production on Tue Aug 2 16:04:22 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=test.dmp" Location: Command Line, Replaced with: "dumpfile=test.dmp"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "SCOTT1"."SYS_EXPORT_SCHEMA_01":  scott1/********@alpsdev40 dumpfile=test.dmp directory=DATA_PUMP_DIR logfile='test_exp.log' schemas=scott1 reuse_dumpfiles=true
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT1"."EMP"                              8.570 KB      14 rows
. . exported "SCOTT1"."J"                                5.445 KB       4 rows
. . exported "SCOTT1"."REC_DATA"                         6.484 KB       9 rows
. . exported "SCOTT1"."TEST_CHINESE"                     5.531 KB       7 rows
. . exported "SCOTT1"."TT1"                              5.148 KB      22 rows
. . exported "SCOTT1"."Y_HOME_PAGE_WINDOW_PANE"          22.67 KB     167 rows
Master table "SCOTT1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT1.SYS_EXPORT_SCHEMA_01 is:
  D:\APP\ADMIN\ADMIN\ALPSDEV\DPDUMP\TEST.DMP
Job "SCOTT1"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:52:10


C:\Documents and Settings\dsharma>echo   ******Ready to Import Data into NEW_USER******
  ******Ready to Import Data into NEW_USER******

C:\Documents and Settings\dsharma>echo   ================================================
  ================================================

C:\Documents and Settings\dsharma>impdp scott1/tiger@'alpsdev40' file='test.dmp' directory=DATA_PUMP_DIR logfile='test_imp.log' REMAP_SCHEMA= scott1:test_sample NETWORK_LINK=dblink40

Import: Release 11.2.0.1.0 - Production on Tue Aug 2 16:05:22 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39200: Link name "dblink40" is invalid.
ORA-02019: connection description for remote database not found



C:\Documents and Settings\dsharma>cmd
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\dsharma>


Regards
Deepak
Re: Clone Database user [message #518237 is a reply to message #518236] Tue, 02 August 2011 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) If you import through network you have not to a) export and b) use file parameter in impdp
(please read the example in the link)
2) The database link is not known in the database you connected with impdp.

Regards
Michel
Re: Clone Database user [message #518238 is a reply to message #518237] Tue, 02 August 2011 06:59 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

My last and Final Question:

Quote:

Example 3-3 Network-Mode Import of Schemas

> impdp hr TABLES=employees REMAP_SCHEMA=hr:scott DIRECTORY=dpump_dir1
NETWORK_LINK=dblink
This example imports the employees table from the hr schema into the scott schema. The dblink references a source database that is different than the target database.

To remap the schema, user hr must have the DATAPUMP_IMP_FULL_DATABASE role on the local database and the DATAPUMP_EXP_FULL_DATABASE role on the source database.

REMAP_SCHEMA loads all the objects from the source schema into the target schema.


If we are talk of the above example then few thing I want to get clear:
1: DBLink refer to the source database which is different from the target database.( where does the scott user exist)
2: In which database does this impdp command is executed?

3: DATAPUMP_IMP_FULL_DATABASE role on the local database( Does this means the target db on which we are running the impdp command according to my knowledge.)

Regards
Deepak

[Updated on: Tue, 02 August 2011 07:34] by Moderator

Report message to a moderator

Re: Clone Database user [message #518240 is a reply to message #518238] Tue, 02 August 2011 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
1: DBLink refer to the source database which is different from the target database.( where does the scott user exist)

Yes

Quote:
2: In which database does this impdp command is executed?

Obviously the target as the database link refers to the source.

Quote:
3: DATAPUMP_IMP_FULL_DATABASE role on the local database( Does this means the target db
which we are running the impdp command

Yes.

Regards
Michel

Re: Clone Database user [message #518246 is a reply to message #518240] Tue, 02 August 2011 08:10 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Thanks everyone, Problem is resolved import done successfully.

Regards
Deepak
Re: Clone Database user [message #518247 is a reply to message #518246] Tue, 02 August 2011 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you get all the objects (privileges, quota, password...) you want?
Please post the final command you use for the future readers.

Regards
Michel
Re: Clone Database user [message #518256 is a reply to message #518247] Tue, 02 August 2011 09:12 Go to previous messageGo to previous message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Sure Sir, I am at home now. I'll post all the commands with the logs tomorrow morning. Thanks a lot for your help Sir.

Regards
Deepak
Previous Topic: How to skip last n no of records in sql Loader load
Next Topic: Backup
Goto Forum:
  


Current Time: Thu Mar 28 12:34:51 CDT 2024