advertisements
_____________________________________________________________________________________________________________________
There is no direct method to perform the schema or username renaming. Oracle does not provide any single command to perform this. There are two indirect ways to perform this. Out of these 2 methods one is not recommended by the Oracle.
Suppose we wanted to rename the Scott user to Tiger.
Step1. Export the scott schema details to a dump file using exp or expdp utility
Object list of Scott user is given below.
SQL> select object_name, object_type from all_objects where owner='SCOTT';
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
DEPT TABLE
EXAMPLE TABLE PARTITION
EXAMPLE TABLE PARTITION
EXAMPLE TABLE
SYS_C00278761 INDEX
EMP1 TABLE
EXAMPLE_PARTITION TABLE
TMP$$_SYS_C002787610 INDEX
GT_EMP TABLE
9 rows selected.
$ expdp dumpfile=scott.dmp logfile=scott.log directory=exp_dir schemas=SCOTT
Export: Release 11.1.0.7.0 - 64bit Production on Wednesday, 22 June, 2011 1:45:10
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA dumpfile=scott.dmp logfile=scott.log directory=exp_dir schemas=SCOTT
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.437 MB
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/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EXAMPLE_PARTITION" 837.4 KB 95120 rows
. . exported "SCOTT"."EXAMPLE":"EXAMPLE_P1" 441.3 KB 49999 rows
. . exported "SCOTT"."EXAMPLE":"EXAMPLE_P2" 408.3 KB 45121 rows
. . exported "SCOTT"."DEPT" 5.945 KB 4 rows
. . exported "SCOTT"."EMP1" 5.890 KB 3 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/SCOTT/scott.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 01:55:10
Step3. Create the target user(TIGER) in the database with necessary privileges.
SQL> create user tiger identified by welcome;
User created.
SQL> alter user tiger default tablespace users;
User altered.
SQL> alter user tiger quota unlimited on users;
User altered.
Step4. Import the dump to the new user with remap schema option with impdp or fromuser touser option in imp utility.
$ impdp dumpfile=scott.dmp logfile=imp_scott.log directory=exp_dir remap_schema=scott:tiger
Import: Release 11.1.0.7.0 - 64bit Production on Wednesday, 22 June, 2011 2:05:38
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA dumpfile=scott.dmp logfile=imp_scott.log directory=exp_dir remap_schema=scott:tiger
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TIGER" already exists
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/TABLE_DATA
. . imported "TIGER"."EXAMPLE_PARTITION" 837.4 KB 95120 rows
. . imported "TIGER"."EXAMPLE":"EXAMPLE_P1" 441.3 KB 49999 rows
. . imported "TIGER"."EXAMPLE":"EXAMPLE_P2" 408.3 KB 45121 rows
. . imported "TIGER"."DEPT" 5.945 KB 4 rows
. . imported "TIGER"."EMP1" 5.890 KB 3 rows
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/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 02:05:55
You can ignore the error, why because the user already created manually.
Step5: Validate the object list
SQL> select object_name, object_type from all_objects where owner='TIGER';
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
DEPT TABLE
EXAMPLE TABLE PARTITION
EXAMPLE TABLE PARTITION
EXAMPLE TABLE
SYS_C00278761 INDEX
EMP1 TABLE
EXAMPLE_PARTITION TABLE
TMP$$_SYS_C002787610 INDEX
GT_EMP TABLE
9 rows selected.
Method 2 : This method is not recommended by oracle because this is a manual updation of data dictionary tables.
SQL> select object_name, object_type from all_objects where owner='SCOTT';
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
DEPT TABLE
EXAMPLE TABLE PARTITION
EXAMPLE TABLE PARTITION
EXAMPLE TABLE
SYS_C00278761 INDEX
EMP1 TABLE
EXAMPLE_PARTITION TABLE
TMP$$_SYS_C002787610 INDEX
GT_EMP TABLE
9 rows selected.
Step 1. Connect to sqlplus using sys as sysdba
Step 2. Update the name column of sys.user$ table for the scott user as tiger
SQL> update user$ set name='TIGER' where name='SCOTT';
1 row updated.
Step 3. Reset the password of Tiger user
SQL> alter user tiger identified by welcome;
User altered.
Step 4 Connect to the Tiger user and validate the objects
SQL> select object_name, object_type from all_objects where owner='SCOTT';
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
DEPT TABLE
EXAMPLE TABLE PARTITION
EXAMPLE TABLE PARTITION
EXAMPLE TABLE
SYS_C00278761 INDEX
EMP1 TABLE
EXAMPLE_PARTITION TABLE
TMP$$_SYS_C002787610 INDEX
GT_EMP TABLE
9 rows selected.
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment