Home » SQL & PL/SQL » SQL & PL/SQL » Changing Username
Changing Username [message #208694] Mon, 11 December 2006 22:37 Go to next message
inforacle
Messages: 21
Registered: December 2006
Junior Member
Hai,
we can change password for a user, is it Possible to change Username itself?
Re: Changing Username [message #208707 is a reply to message #208694] Mon, 11 December 2006 23:21 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,

No. you cann't change username.some like password.
Re: Changing Username [message #208710 is a reply to message #208707] Mon, 11 December 2006 23:29 Go to previous messageGo to next message
inforacle
Messages: 21
Registered: December 2006
Junior Member
fine, atleast is it Possible to Change user_id (userid) ,
What is the user of the Userid
Re: Changing Username [message #208719 is a reply to message #208710] Mon, 11 December 2006 23:58 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you plan to mess up with Oracle dictionary, I believe you'll get into a deep trouble.

Why would you want to do that, anyway? What's wrong with the old username or its ID? If you don't like it, drop the user and create a new one.
Re: Changing Username [message #208722 is a reply to message #208710] Tue, 12 December 2006 00:05 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,
You can query on user_users for user_id.
SQL> select user_id, username from user_users;

USER_ID USERNAME
---------- ------------------------------
57 SCOTT


and No you cann't change user_id or Username.

But still you want to change username then you fellow below procedure.

step.
1.export complete user schema.
2.generate script for all privs of user schema.
3.create new user with new name.
4.assign default tablespace and quota on tbs
5.assign all privs to new users through generate script
6.import ur old schema export dump file.



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 - Produc
tion
With 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>

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>
SQL>
SQL> select 'grant '||privilege||' to newscott;' from session_privs;

....
....
SQL>spool off
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/oracle
Connected.
SQL> create user newscott identified by newscott default tablespace test;

User created.

SQL>@c:\scottpriv.sql <<hit enter>>
SQL>@c:\scottroles.sql <<hit enter>>

SQL> host imp system/oracle fromuser=scott touser=newscott file=c:\scottschema.d
mp log=scottimp.log

Import: Release 10.1.0.2.0 - Production on Tue Dec 12 09:54:39 2006

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.01.00 via conventional path
import 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                               58

1 row selected.

SQL> select count(*) from session_privs;

  COUNT(*)
----------
        81

1 row selected.

SQL> select count(*) from session_roles;

  COUNT(*)
----------
         6

1 row selected.

SQL>




hope this helps
Taj

[Updated on: Tue, 12 December 2006 00:06]

Report message to a moderator

Re: Changing Username [message #208724 is a reply to message #208722] Tue, 12 December 2006 00:13 Go to previous message
inforacle
Messages: 21
Registered: December 2006
Junior Member
thanks
Previous Topic: what's difference between admin and developer
Next Topic: Overlapping intervals
Goto Forum:
  


Current Time: Fri Dec 09 04:04:48 CST 2016

Total time taken to generate the page: 0.07223 seconds