Change User/Schema Name.

Change Oracle Schema Name

SQL> host exp system/oracle owner=scott file=c:\scottschema.dmp log=c:\scott.log
Export: Release 10.1.0.2.0 - Production on Tue Dec 12 09:36:37 2006
Copyright (c) 1982, 2004, Oracle.
All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 -
ProductionWith the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified users ....
exporting pre-schema procedural objects and actions.
exporting foreign function library names for user SCOTT.
exporting PUBLIC type synonyms.
exporting private type synonyms.
exporting object type definitions for user SCOTT
About to export SCOTT's objects ....
exporting database links.
exporting sequence numbers.
exporting cluster definitions.
about to export SCOTT's tables via Conventional Path .... .
exporting table A 0 rows exported. .
exporting table ABCDEFGHIJKLMNOPQRSTUVWXYZ 0 rows exported. .
exporting table ABCDEFGHIJKLMNOPQRSTUVWXYZ1234 0 rows exported. .
exporting table A_12345678901234567890123456$# 0 rows exported. .
exporting table B 100 rows exported. .
exporting table BIG 46968 rows exported. .
exporting table BIG_TABLE 46967 rows exported. .
exporting table BONUS 0 rows exported. .
exporting table C 1000 rows exported. .
exporting table D 1000 rows exported. .
exporting table DEPT 4 rows exported. .
exporting table E 1000 rows exported. .
exporting table EMP 14 rows exported. .
exporting table FINAL_OUTPUT. .
exporting table Q 10000 rows exported. .
exporting table SALGRADE 5 rows exported. .
exporting table T 1000 rows exported. .
exporting table TEST 7 rows exported.
exporting synonyms.
exporting views.
exporting stored procedures.
exporting operators.
exporting referential integrity constraints.
exporting triggers. exporting indextypes.
exporting bitmap, functional and extensible indexes.
exporting posttables actions.
exporting materialized views.
exporting snapshot logs.
exporting job queues.
exporting refresh groups and children.
exporting dimensions.
exporting post-schema procedural objects and actions.
exporting statistics
Export terminated successfully without warnings.



SQL> select user_id,username from user_users;
USER_ID USERNAME
---------- ------------------------------
57 SCOTT



SQL> select count(*) from session_privs;
COUNT(*)
----------
81



SQL> select count(*) from session_roles;
COUNT(*)
----------
6

SQL> set echo off
SQL> set feedback off
SQL> set heading off

SQL> spool c:\scottpriv.sql

SQL> select 'grant '||privilege||' to newscott;' from session_privs;
........

SQL>spool off

SQL> spool c:\scottroles.sql

SQL> select 'grant '||role||' to newscott;' from session_roles;
grant CONNECT to newscott;
grant RESOURCE to newscott;
grant IMP_FULL_DATABASE to newscott;
grant SELECT_CATALOG_ROLE to newscott;
grant HS_ADMIN_ROLE to newscott;
grant EXECUTE_CATALOG_ROLE to newscott;

SQL> spool off
SQL> set heading on
SQL> set feedback on
SQL> set echo on

SQL> select default_tablespace from user_users;
DEFAULT_TABLESPACE
------------------------------
TEST
1 row selected.
SQL> conn system/oracleConnected.

SQL> create user newscott identified by newscott default tablespace test;
User created.

SQL>@c:\scottpriv.sql <>


SQL>@c:\scottroles.sql <>

SQL> host imp system/oracle fromuser=scott touser=newscott file=c:\scottschema.dmp log=scottimp.log
Import: Release 10.1.0.2.0 - Production on Tue Dec 12 09:54:39 2006Copyright (c) 1982, 2004, Oracle.
All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport file created by EXPORT:V10.01.00 via conventional pathimport done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set. importing SCOTT's objects into NEWSCOTT. .
importing table "A" 0 rows imported. . importing table "ABCDEFGHIJKLMNOPQRSTUVWXYZ" 0 rows imported. .
importing table "ABCDEFGHIJKLMNOPQRSTUVWXYZ1234" 0 rows imported
........

SQL>drop user scott cascade;

SQL> select username,user_id from user_users;
USERNAME USER_ID
------------------------------ ----------
NEWSCOTT 5
81 row selected.

SQL> select count(*) from session_privs;
COUNT(*)
----------
81
1 row selected.

SQL> select count(*) from session_roles;
COUNT(*)
----------
6
1 row selected.