Change username in DBA

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

Change username in DBA_USERS

Change username in DBA_USERS

2005-10-01 - By Radoulov, Dimitre

Reply: 1 2 3 4 5 6 7 8 9 10 >>

There should be a reason for Tom Kyte's answer :O)

<quote>
I'm surprised Tom let the one about updating the sys.user$ table slip by
without
any chiding. Wouldn't that be a strict no-no anyways?

Kashif

Followup:
that would be strictly a no-no -- 100%

</quote>

HI,
Dimitre

-- -- Original Message -- --
From: Chirag DBA
To: David Sharples
Cc: Radoulov, Dimitre ; Oracle-L Freelists ; Ganesh Raja
Sent: Saturday, October 01, 2005 7:00 PM
Subject: Re: Change username in DBA_USERS

D O N E ..... ! ! ! ! !

was thinking of changing the ownership column first.
But see, this works, with not a single error or warning in TRACE file.
SQL> select user#,name from user$ where name like 'SCOT%';

USER# NAME
-- ---- -- -- ---- ---- ---- ---- ---- --
59 SCOTT
64 SCOTT1

SQL> update user$ set name='HERMESNEW' where user#=64;

1 row updated.

SQL> commit;

Commit complete.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 105978600 bytes
Fixed Size 453352 bytes
Variable Size 79691776 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> select user#,name from user$ where name like 'SCOT%';

USER# NAME
-- ---- -- -- ---- ---- ---- ---- ---- --
59 SCOTT

SQL> select user#,name from user$ where name like 'HER%';

USER# NAME
-- ---- -- -- ---- ---- ---- ---- ---- --
65 HERMES
64 HERMESNEW

SQL> select username from dba_users where username like 'HER%';

USERNAME
-- ---- ---- ---- ---- ---- --
HERMES
HERMESNEW

SQL> alter user hermesnew identified by chirag;

User altered.

SQL> conn hermesnew/chirag
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
-- ---- ---- ---- ---- ---- -- -- ---- -- ---- --
BONUS TABLE
CHIRAG TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
TEMP1 TABLE

6 rows selected.

SQL> sho user
USER is "HERMESNEW"
SQL>

Thanx to everyone....

Regards - Chirag

On 10/1/05, Chirag DBA <chiragdba@(protected)> wrote:
hmmmm...!!!

Its a large dump, takes more than 5 hrs. so thinking to get it in "SHORT
WAY".

Export problem was because of replicated objects.

can afford to have a corruption on my local test database.

regards - chirag

On 10/1/05, David Sharples <davidsharples@(protected) > wrote:
simple, dont do it, not supported, doesnt work and corrupts the data
dictionary and oracle support would tell you to go away.

Any good person will tell you to stop right now and do it the proper way

Fromuser / touser export is the only way of doing it, so fix your export
problems.

On 10/1/05, Chirag DBA <chiragdba@(protected) > wrote:
Its not a joke and am doing this on test.

I remember one incident on LAZYDBAs , where a DBA did programming on
USER_ID and then required to change the USER_ID. He did it someway, but I am
unable to recollect.

There must be the way we can do. ( I think so).

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.2722" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>There should be a reason for Tom Kyte's answer
:O)</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2><quote></FONT></DIV>
<DIV><PRE>I'm surprised Tom let the one about updating the sys.user$ table slip
by without
any chiding. Wouldn't that be a strict no-no anyways?

Kashif
</PRE><BR>
<TABLE cellSpacing=2 cellPadding=2>
<TBODY>
<TR>
<TD vAlign=top align=left width="5%"><FONT face=verdana,arial,helvetica
size=-1><B>Followup:<A name=30559094142224></B> </FONT></A></TD></TR>
<TR>
<TD width="95%"><PRE>that would be strictly a no-no -- 100% </PRE></TD><
/TR></TBODY></TABLE></DIV>
<DIV><FONT face=Arial size=2></quote></FONT></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>HI,</FONT><BR>Dimitre </DIV>
<DIV> </DIV>
<DIV> </DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT:
#000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">-- -- Original Message -- -- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=chiragdba@(protected) href="mailto:chiragdba@(protected)">Chirag DBA</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A title=davidsharples@(protected)
href="mailto:davidsharples@(protected)">David Sharples</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Cc:</B> <A title=cichomitiko@(protected)
href="mailto:cichomitiko@(protected)">Radoulov, Dimitre</A> ; <A
title=oracle-l@(protected) href="mailto:oracle-l@(protected)">Oracle-L
Freelists</A> ; <A title=ganesh.raja@(protected)
href="mailto:ganesh.raja@(protected)">Ganesh Raja</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Saturday, October 01, 2005 7:00
PM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Re: Change username in
DBA_USERS</DIV>
<DIV><BR></DIV>
<DIV>D O N E ..... ! ! ! ! ! </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV>was thinking of changing the ownership column first. </DIV>
<DIV>But see, this works, with not a single error or warning in TRACE
file.</DIV>
<DIV>
<P>SQL> select user#,name from user$ where name like 'SCOT%';</P>
<P>     USER# NAME<BR>-- ---- --
-- ---- ---- ---- ---- ---- --<BR>       
59 SCOTT<BR>        64 SCOTT1</P>
<P>SQL> update user$ set name='HERMESNEW' where user#=64;</P>
<P>1 row updated.</P>
<P>SQL> commit;</P>
<P>Commit complete.</P>
<P>SQL> shutdown immediate;<BR>Database closed.<BR>Database
dismounted.<BR>ORACLE instance shut down.<BR>SQL> startup<BR>ORACLE
instance started.</P>
<P>Total System Global Area  105978600 bytes<BR>Fixed
Size            
      
453352 bytes<BR>Variable
Size            
 
79691776 bytes<BR>Database
Buffers           25165824
bytes<BR>Redo
Buffers           
     
667648 bytes <BR>Database mounted.<BR>Database opened.<BR>SQL> select
user#,name from user$ where name like 'SCOT%';</P>
<P>     USER# NAME<BR>-- ---- --
-- ---- ---- ---- ---- ---- --<BR>       
59 SCOTT</P>
<P>SQL> select user#,name from user$ where name like 'HER%';</P>
<P>     USER# NAME<BR>-- ---- --
-- ---- ---- ---- ---- ---- --<BR>       
65 HERMES<BR>        64 HERMESNEW</P>
<P>SQL> select username from dba_users where username like 'HER%';</P>
<P>USERNAME<BR>-- ---- ---- ---- ---- ---- --<BR>HERMES<BR>HERMESNEW</P>
<P>SQL> alter user hermesnew identified by chirag;</P>
<P>User altered.</P>
<P>SQL> conn hermesnew/chirag<BR>Connected.<BR>SQL> select * from
tab;</P>
<P>TNAME           
             
 
TABTYPE  CLUSTERID<BR>-- ---- ---- ---- ---- ---- -- -- ----
-- ---- --<BR>BONUS         
             
   
TABLE<BR>CHIRAG          
             
 
TABLE<BR>DEPT          
             
   
TABLE
<BR>EMP           
             
   
TABLE<BR>SALGRADE          
            
TABLE<BR>TEMP1          
             
  
TABLE</P>
<P>6 rows selected.</P>
<P>SQL> sho user<BR>USER is "HERMESNEW"<BR>SQL></P>
<P>Thanx to everyone....</P>
<P>Regards - Chirag<BR></P></DIV>
<DIV><SPAN class=gmail_quote>On 10/1/05, <B class=gmail_sendername>Chirag
DBA</B> <<A href="mailto:chiragdba@(protected)">chiragdba@(protected)</A>>
wrote:</SPAN>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px
solid">
<DIV>hmmmm...!!! </DIV>
<DIV> </DIV>
<DIV>Its a large dump, takes more than 5 hrs. so thinking to get it in
"SHORT WAY".</DIV>
<DIV> </DIV>
<DIV>Export problem was because of replicated objects.</DIV>
<DIV> </DIV>
<DIV>can afford to have a corruption on my local test
database.<BR><BR>regards - chirag </DIV>
<DIV><SPAN class=e id=q_106ad10ef8ed1008_1>
<DIV> </DIV>
<DIV><SPAN class=gmail_quote>On 10/1/05, <B class=gmail_sendername>David
Sharples</B> <<A onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:davidsharples@(protected)" target=_blank>davidsharples@(protected)
</A>> wrote:</SPAN>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px
solid">
<DIV>simple, dont do it, not supported, doesnt work and corrupts the data
dictionary and oracle support would tell you to go away.</DIV>
<DIV> </DIV>
<DIV>
<DIV>Any good person will tell you to stop right now and do it the proper
way</DIV>
<DIV> </DIV></DIV>
<DIV>Fromuser / touser export is the only way of doing it, so fix your
export problems.</DIV>
<DIV><SPAN>
<DIV><BR><BR> </DIV>
<DIV><SPAN class=gmail_quote>On 10/1/05, <B class=gmail_sendername>Chirag
DBA</B> <<A onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:chiragdba@(protected)" target=_blank>chiragdba@(protected)</A>
> wrote:</SPAN>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc
1px solid">
<DIV>Its not a joke and am doing this on test.</DIV>
<DIV> </DIV>
<DIV>I remember one incident on LAZYDBAs , where a DBA did programming
on USER_ID and then required to change the USER_ID. He did it someway,
but I am unable to recollect.</DIV>
<DIV> </DIV>
<DIV>There must be the way we can do. ( I think
so).</DIV></BLOCKQUOTE></DIV></SPAN></DIV></BLOCKQUOTE></DIV><BR></SPAN><
/DIV></BLOCKQUOTE></DIV><BR></BLOCKQUOTE></BODY></HTML>