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>

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%

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%

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

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

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

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

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

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

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.

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

 conn tc33sp1_1/tc33sp1_1@alpsedv40 

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

--------------------------------------------------------------------------------
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>