CODEWITHSUNDEEP
oracle-databaseplsqldatabase-administrationoracle12c
Sammy
Sammy

Reputation: 777

Getting DBA_USERS information

I am trying to retreive a USER id, from DBA_USERS like we can do in DBA_ROLES.

I've tryied to retreive ROWID column from DBA_ROLES, but i get this warning:

"ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table"

From what i can understand, DBA_USERS is a Oracle generated view and it is not possible to retrieve this ROWID. Am i right?

If this is correct, how can i know from which tables this view is generated? Or how can i know the ROWID of a USER?

Kind regards!

Sam

Upvotes: 0

Views: 2790

Answers (3)

golosovsky
golosovsky

Reputation: 718

DBA_USERS is a view, a view which consists of a query on a few tables.

ORA-01445 means that Oracle cannot retrieve the ROWID you request due to the fact that you need to query the relevant table directly (or change the view SQL and query the ROWID also) to get the relevant ROWID (needless to say that if your view is created by joining a couple of tables — how can Oracle determine which ROWID you want?) .

The "main" table DBA_USERS gets data from is sys.USER$ table.

To get the ROWID, first look at the SQL behind DBA_USERS (it's very simple on most IDEs) to understand which data you want to query except the ROWIDs.

Then you can just query:

select ROWID, USER# user_id, NAME username
from sys.USER$;

(or any other column you need).

Good luck!

Upvotes: 0

Sylvain Leroux
Sylvain Leroux

Reputation: 52070

I am trying to retrieve a USER id, from DBA_USERS

You are looking for DBA_USERS.USER_ID :

SQL> SELECT USER_ID FROM DBA_USERS WHERE USERNAME = 'SYLVAIN'; 

   USER_ID
----------
    48

I've tryied to retreive ROWID column

ROWID have nothing to do here. Those are kind of "pointers" to the row physical storage. Under some specific conditions they are subject to change. Since views don't have physical storage, ROWID is meaningless for them -- hence the error "ORA-01445" :

from oraerr:
ORA-01445: cannot select ROWID from a join view without a key-preserved table

Cause: A SELECT statement attempted to select ROWIDs from a view derived from a join operation. Because the rows selected in the view do not correspond to underlying physical records, no ROWIDs can be returned.

Action: Remove ROWID from the view selection clause, then re-execute the statement.

Upvotes: 4

Martin Drautzburg
Martin Drautzburg

Reputation: 5253

What Sylvain is talking about is the rownum not the rowid. The rownum is a sequential number, whereas the rowid denotes the physical location of a row.

See here:

0:opal@spmdtz> select rowid, rownum, xxx.* from xxx;

Rowid             |rownum|x   |y   |
------------------------------------
AAAS/3AAGAAAbmYAAA|     1|foo1|foo2|
AAAS/3AAGAAAbmYAAB|     2|bar1|bar2|

The rowid can be useful when you want to update a row. You can say where rowid= ... or in other cases where you want to refer to a row you already "have". I believe it is the fastest way to access a row.

But I don't understand why you would need the rowid in your query.

Upvotes: 0

Related Questions