SB Users tab inaccurate on standby DB

Toad for Oracle - Beta
1

Howdy!

I've noticed that the Status column of the SB Users tab is not entirely accurate for read-only standby DBs. An account can be locked but still shown as "OPEN" in Toad.

Any thoughts to (optionally?) adding V$RO_USER_ACCOUNT to the query? It's how Oracle maintains account status on read-only DBs, in addition to whatever the status is replicated from the primary. Unsure how this could affect others though...

Thanks!
Rich

2

Hi Rich,

For me, on a non-standby db, this view is empty.

I'm open to changes that won't affect others, but I don't have a standby db handy, so I'd need an assist on when to trust dba_users and when I should look at v$ro_user_account for the status (any any other columns).

1 Like
3

Hmm. I see that view is new as of 12.1. This could get....messy. I can experiment with a custom filter. Perhaps that's a better choice for now.

Thanks!
Rich

4

Well, we have all kinds of twists and turns in our code for oracle versions, privileges, etc.

I'm thinking in this case we'd need to:

  1. detect if the DB is standby (pretty sure this is in v$instance or v$database)
  2. only use v$ro_user_account if user has privs (and 12.1 or newer)
  3. know when to give v$ro_user_account's status priority over dba_users.

Or something like that. If you get a custom filter that works, let me know what you did.

1 Like