Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Sagi.

Asked: December 27, 2001 - 11:06 am UTC

Last updated: July 01, 2013 - 4:05 pm UTC

Version: 8x

Viewed 10K+ times! This question is

You Asked

Hi Tom !

Happy XMas and New Year 2002 to you.

1. At the time of creating a password file you specify the maximum number of
distinct DBA and OPERs. But say already this step had been taken care and now I
want to see in the exsisting password file the max. no of entries specified.
Which data dictionary or anyother place where we can get this info.

2. Is SVRMGRL removed from 9i onwards. If so then how do we connect for SYSDBA
or INTERNAL accounts.

3. Can we rename a column. I am aware of a VIEW as an alternate for this.

Thanx in advance for your help.

Regards,
Sagi.

and Tom said...

1) take the size of the password file, subtract 1024 and divide by 128. Eg:

$ ls -lag xxx yyy zzz
-rwSr----- 1 tkyte other 3584 Dec 27 11:25 xxx
-rwSr----- 1 tkyte other 6144 Dec 27 11:25 yyy


3584 -> 2560 -> 20, xxx has 20 entries
6144 -> 5120 -> 40, yyy has 40 entries


2) same way you can in 8i, sqlplus.

$ sqlplus "/ as sysdba"

or

$ sqlplus /nolog

SQL*Plus: Release 8.1.7.0.0 - Production on Thu Dec 27 11:31:18 2001

(c) Copyright 2000 Oracle Corporation. All rights reserved.

idle> connect / as sysdba;
Connected.


(and INTERNAL -- thats gone in 9i as well)

3) nope, a view is the way to do it. In 9i, you can do an online reorg that can rename a column as well.


Rating

  (23 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Used or allowed

Randy, December 27, 2001 - 4:57 pm UTC

Is that the number of password entries allowed or actually used?

Tom Kyte
December 27, 2001 - 5:36 pm UTC

number allowed. number used = select count(*) from v$pwfile_users

Good one

Randy, December 27, 2001 - 5:53 pm UTC

Oh yeah--completely forgot about the dictionary view for that. I should have those things memorized by now!!

-Thanks

How to do this in NT?

Lisa, December 28, 2001 - 11:08 am UTC

The answer 1, how do I come up the command if my password file is reside in NT box?
Thanks.

Tom Kyte
December 28, 2001 - 11:31 am UTC

use dir instead of ls, the math is the same.

Nirmal, December 31, 2001 - 8:21 am UTC

Oracle 817 on WinNT4.

c:\> orawpd file=c:\orarep.ora password=oracle entries=2

The file was created and it's size is 2k.

now the calculation:
2k = 2*1024 = 2048
=> 2048 - 1024 = 1024
=> 1024/128 = 8.

So it's meaning that i can keep maxinum 8 user entries in the passwordword. It's not 2, since i created the file entries=2?.

Can you pls clear out this.

Tom Kyte
December 31, 2001 - 8:40 am UTC

do this:

C:\>orapwd file=c:\orarep.ora password=oracle entries=2

C:\>dir orarep.ora
Volume in drive C has no label.
Volume Serial Number is F455-B3C3

Directory of C:\

12/31/2001 08:42a 1,536 orarep.ora
1 File(s) 1,536 bytes
0 Dir(s) 7,823,032,320 bytes free

for me, its not 2k using 816 and 901 (and I seriously doubt 817 would be any different).



8.1.7 Password File Size

Mark A. Williams, December 31, 2001 - 10:22 am UTC

Tom:

Here it is on 8.1.7...

C:\Temp>c:\oracle\8.1\bin\orapwd file=orarep.ora password=oracle entries=2

C:\Temp>dir orarep.ora
Volume in drive C has no label.
Volume Serial Number is 6056-B9DA

Directory of C:\Temp

12/31/2001 10:23a 1,536 orarep.ora
1 File(s) 1,536 bytes
0 Dir(s) 8,502,665,216 bytes free

Shock! Horror! It is the same. Luckily I was sitting down. Now, of course, if you just look at it from Windows Explorer it will say '2K' because it rounds in the display...

- Mark

Password File Size Discussions

Vikas Khanna, February 16, 2002 - 7:31 am UTC

Hello Tom,
Even if you specified the size tobe 1536 bytes if the no. of enteries are 2 in the password file, then also by applying your formala the result is coming to be 4 entries which is not the right case??
1536 - 1024 = 512 bytes
512/128 gives u 4 instead of 2 entries.

Where is the problem??
In some cases it is coming to be true as per your formula. Why it is so differentiating ??

Tom Kyte
February 22, 2002 - 9:27 am UTC

It is right -- you ask for 2, 3 or 4 and we give you 4. It is rounded up.

Pls Clarify

Anirudh, May 20, 2003 - 7:00 am UTC

Hi Tom,
could you please clarify this:
"It is right -- you ask for 2, 3 or 4 and we give you 4. It is rounded up. "
Thanks


Tom Kyte
May 20, 2003 - 12:52 pm UTC

it means regardless of whether you asked for 1, 2, 3 or 4 entries, we allocate 4 of them at a time.

password file creation in oracle 9i

Mujib Kasem, October 13, 2003 - 1:25 am UTC

In the oracle8i we create pwd file by using ORADIM utility before database creation. But in oracle 9i how can we do that thing .

Tom Kyte
October 13, 2003 - 7:18 am UTC


you always used orapwd -- not oradim.

</code> http://docs.oracle.com/docs/cd/A87861_01/NT817EE/index.htm <code>

(thats from 8i). oradim managed 'services', orapwd the password file


same in 9i

password file

atul, February 12, 2004 - 12:19 pm UTC

Hi,

We are having a database running with ownership of oracle as

oracle 66568 1 0 Feb 09 - 0:03 ora_smon_test_154

I don't have oracle password,but through password file i can login as "svrmgrl" or as "sysdba".

When i logged in through my unix-id "t1test" and connected as svrmgrl and started the database it shows as follows:

t1test 66568 1 0 Feb 09 - 0:03 ora_smon_test_154

If you see its showing my id instead of "oracle".
Can i give this database to users to use,what ill effects are there to start with other id?

Thank,
ATUL


Tom Kyte
February 12, 2004 - 12:40 pm UTC

probably means your install is wrong. what are the permissions on the oracle binary? it should look like this:


$ ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 ora9ir2 ora9ir2 51500205 Nov 25 10:55 /home/ora9ir2/bin/oracle


if you do not see the 's' in there, your install is wrong, you did not run root.sh after installing.

password file

atul, February 13, 2004 - 7:35 am UTC

Hi,
setting is as follows:

-rwsr-s--x 1 oracle dba 37836717 Dec 02 2002 oracle

You can see 's' there.

One thing is that this base has been upgraded from 8.0.6 to 8.1.7..

Thank,
ATUL

Tom Kyte
February 13, 2004 - 10:24 am UTC

"base". hmmm. whats that. anyway.


if the setuid bit is on
AND
it is the actual binary you are using (eg: you are not confused over oracle homes)
AND
the userid isn't showing as oracle
THEN
something is fishy in your system.


regardless of who I log in as, when you have a setuid program as the oracle binary is, it'll be running "as the owner"

tell me, what does ipcs -a show you, who is the owner of the shared memory segments associated with the SGA. If that is not Oracle -- you are "getting confused" somewhere for the s bit would ensure that Oracle was the owner.




Password file

atul, July 27, 2004 - 3:55 am UTC

Hi,

I am using oracle 8.1.7.

My remote_login_passwordfile='EXCLUSIVE',
When i try to connect using "conn sys as sysdba" and provide
password, it gives error as

"ORA-01017: invalid username/password; logon denied",
But when i connect through svrmgrl and give the same passowrd,It will get connected...

Whats wrong?

Thank,
Atul

Tom Kyte
July 27, 2004 - 7:24 am UTC

show us a cut and paste, include the banners for sqlplus and svrmgrl.



Nice

Raju, May 25, 2005 - 1:18 pm UTC

Dear Tom,
I use Oracle under Windows NT.How to access the password file??
Can we write a procedure or function
to access those values

Tom Kyte
May 25, 2005 - 3:29 pm UTC

what password file?  the oracle one?  it is just a binary (not useful to you) file in the OS.  You would need OS access to it.

You can "query" it

 
ops$tkyte@ORA9IR2> select * from v$pwfile_users;
 
USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
 

How Many Users can be defined in PWFILE

Mark Harrison, August 10, 2006 - 10:57 am UTC

Tom, thanks. The thread entirely answered a previously unknown and "dark area". :-)



John, June 02, 2009 - 5:43 am UTC

Hello Tom,

If an Oracle password file contains the password for Administrative accounts, then why is it that in 9i(9.2.0.6 and 9.2.0.8) the timestamp of the file $ORACLE_HOME/dbs/orapwSID change when a non SYS account is UNLOCKed ?

There is no change in the file size, just the timestamp is updated with latest one.

Thank you.
John
Tom Kyte
June 02, 2009 - 7:48 am UTC

does it really matter?

ops$tkyte%ORA10GR2> !ls -l $ORACLE_HOME/dbs/orapw$ORACLE_SID
-rw-r-----  1 ora10gr2 ora10gr2 1536 Mar 26 11:52 /home/ora10gr2/oracle/product/10.2.0/db_1/dbs/orapwora10gr2

ops$tkyte%ORA10GR2> alter user scott account lock;

User altered.

ops$tkyte%ORA10GR2> alter user scott account unlock;

User altered.

ops$tkyte%ORA10GR2> !ls -l $ORACLE_HOME/dbs/orapw$ORACLE_SID
-rw-r-----  1 ora10gr2 ora10gr2 1536 Mar 26 11:52 /home/ora10gr2/oracle/product/10.2.0/db_1/dbs/orapwora10gr2




I don't have a 9i instance with me today, but frankly, it would definitely fall into the "so what" bucket

John, June 03, 2009 - 6:45 am UTC

Just curious on what oracle updates in the orapw<sid> file when an non sys account is unlocked in 9i.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL>  !ls -l $ORACLE_HOME/dbs/orapwfalcon
-rwSr-----   1 oracle   oinstall    1536 Jun  1 15:03 /u01/app/oracle/product/9.2.1.0/dbs/orapwfalcon

SQL> alter user falcon account unlock;

User altered.

SQL>  !ls -l $ORACLE_HOME/dbs/orapwfalcon
-rwSr-----   1 oracle   oinstall    1536 Jun  3 19:18 /u01/app/oracle/product/9.2.1.0/dbs/orapwfalcon

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production


Thank you.
Tom Kyte
June 04, 2009 - 10:14 am UTC

it just seems to 'touch it', doesn't update it


Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> @test
SQL>
SQL> !ls -l $ORACLE_HOME/dbs/orapw$ORACLE_SID
-rwSr-----  1 ora9ir2 ora9ir2 1536 Jun  4 09:00 /home/ora9ir2/dbs/orapwora9ir2

SQL> !sum $ORACLE_HOME/dbs/orapw$ORACLE_SID
17552     2

SQL> exec dbms_lock.sleep(60)

PL/SQL procedure successfully completed.

SQL> alter user scott account unlock;

User altered.

SQL> !ls -l $ORACLE_HOME/dbs/orapw$ORACLE_SID
-rwSr-----  1 ora9ir2 ora9ir2 1536 Jun  4 09:02 /home/ora9ir2/dbs/orapwora9ir2

SQL> !sum $ORACLE_HOME/dbs/orapw$ORACLE_SID
17552     2


A reader, October 03, 2009 - 4:50 pm UTC

Hi Sir;

why pwd file necessary for oracle? I dont need a sysdba password if I need to access the database directly from server.
I notice that hexadecimal sys password can be seen in dba_users
Does it mean that this is an external table?


Tom Kyte
October 08, 2009 - 6:15 am UTC

... why pwd file necessary for oracle?..

because it isn't - trick question. go ahead, erase it, see what happens (on your TEST SYSTEM PLEASE)


A reader, October 10, 2009 - 2:14 am UTC

I notice that hexadecimal sys password can be seen in dba_users
Does it mean that this is an external table?

A reader, January 11, 2010 - 3:01 am UTC

I notice that hexadecimal sys password can be seen in dba_users
Does it mean that this is an external table?


Tom Kyte
January 18, 2010 - 6:21 am UTC

how did you get from point A to point B??????

what you see in dba_users is the hashed (not encrypted) representation of the password. I don't know why you would think that would imply "this is external table"?


For the sysdba connection as sys, the password can be stored externally from the database in the password file. Normally the password stored in there is IN SYNC with the password in the database, but it doesn't have to be. If you do an alter user identified by - it'll sync them up, but if you just create a new password file, you can put whatever you want in there and it could be different from the database (dba_users) information.

password file location

A reader, February 21, 2010 - 4:39 am UTC

Greetings thomas,

and thanks like always.

if someone create a password file in a none default location, can i know where or not.
Tom Kyte
March 01, 2010 - 5:40 am UTC

http://docs.oracle.com/docs/cd/E11882_01/server.112/e10595/dba007.htm#sthref106

Some operating systems require the password file to adhere to a specific format and be located in a specific directory. Other operating systems allow the use of environment variables to specify the name and location of the password file.

A minor clarification

Kumar, June 08, 2010 - 8:54 am UTC

Tom,
Any data dictionary view exists which logs the time of password changes for an user?
Tom Kyte
June 09, 2010 - 8:59 am UTC

only if you created one - typically by hooking into the password verification routines

http://docs.oracle.com/docs/cd/E11882_01/network.112/e10574/authentication.htm#DBSEG33224

or if you have enabled auditing to some level

Last password change

Greg, June 09, 2010 - 9:37 am UTC

The time of the LAST password change is in the PTIME column of SYS.USER$ But doesn't the SYS.USER_HISTORY$ table have the list of changes? I don't know if it's 100% accurate, but it seems to be there.
Tom Kyte
June 10, 2010 - 12:04 pm UTC

that only works if you have the password functions in place.

Password History

Greg, June 10, 2010 - 2:05 pm UTC

Ahh - that explains that! We've been running some version of password verification for the last 7 years... that's why it seems "standard" to me.

Thanks
Greg

Entries=1 that equal to 4

Saravanan, June 21, 2013 - 10:42 am UTC

Thanks Tom for your valuable reponse.

If I specify entries=1, you are saying that will be equal to 4.

So Can I include 4 account as sysdba, even If I secify the entries=1
Tom Kyte
July 01, 2013 - 4:05 pm UTC

up through 11gr2 it is still 4

then... in 12cR1:

[tkyte@localhost ~]$ orapwd file=x.ora entries=1

Enter password for SYS:
[tkyte@localhost ~]$ ls -lag x.ora
-rw-r-----. 1 tkyte 3072 Jul  1 11:59 x.ora
[tkyte@localhost ~]$


it jumped to 16 - but that might not be true on all ports, it is tied to the OS block size in some fashion (but always a multiple of 4)

http://docs.oracle.com/cd/E16655_01/server.121/e17636/dba.htm#sthref128