Home » Other » Client Tools » PL*SQL - Which users are ACTIVE ? (SQL*Plus, Release 9.2.0.1.0, Windows XP)
PL*SQL - Which users are ACTIVE ? [message #494688] Wed, 16 February 2011 10:27 Go to next message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
Hi to all,

I type the following command to know what users have access to the database i'm currently connected to:

SQL> select *
2 from all_users
3 /


I know need to know what users are active or inactive.
Is there a PL*SQL command for that ?

Thanks !
Re: PL*SQL - Which users are ACTIVE ? [message #494692 is a reply to message #494688] Wed, 16 February 2011 10:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I know need to know what users are active or inactive.

define/quantify "active" & "inactive"

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: PL*SQL - Which users are ACTIVE ? [message #494694 is a reply to message #494688] Wed, 16 February 2011 10:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ This information is only available to DBA
2/ Why do you want to know this?

Don't forget to answer BlackSwan's question.

Regards
Michel
Re: PL*SQL - Which users are ACTIVE ? [message #494700 is a reply to message #494688] Wed, 16 February 2011 11:48 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
You could always shutdown the database and wait for the phone calls...

[Updated on: Wed, 16 February 2011 12:00] by Moderator

Report message to a moderator

Re: PL*SQL - Which users are ACTIVE ? [message #494704 is a reply to message #494700] Wed, 16 February 2011 12:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have you other smart answers or is this the only one you are able to post?

Regards
Michel
Re: PL*SQL - Which users are ACTIVE ? [message #494715 is a reply to message #494704] Wed, 16 February 2011 14:34 Go to previous messageGo to next message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
BlackSwan:

- SQL*Plus: Release 9.2.0.1.0

- I meant i wanted to know if there are any DISABLED users for a particular development database. Users that have been created but have been disabled (instead of deleted after person quits for example)
- When you say to "follow posting Guide Lines", i'm sorry, i have read a part of it (and will continue as it goes on) but i'm still a recent user and haven't had time to read it all. Can you tell me what more i need to do so that my posting is enough documented or readable? Thanks in advance.


Michel Cadot:
- I'm not the DBA, which means i wont even be able to see that information ? Ok then.

- The reason was because i typed the following command in order to know which user has the "ABC_EXECUTE" role:
in our development database:
SQL> select *
  2  from dba_role_privs
  3  where granted_role='ABC_EXECUTE';

- Which then, brought me to ask if there was any way to know which users are DISABLED that exists but have been for some reason DISABLED.

Thanks for your usual support!

p.s not sure if the code between brackets and [/code] are doing what expeted for the formatting of my post
Re: PL*SQL - Which users are ACTIVE ? [message #494718 is a reply to message #494715] Wed, 16 February 2011 14:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
when all else fails Read The Fine Manual
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4174.htm#i1628672

> I meant i wanted to know if there are any DISABLED users
post SQL & results that show how DISABLE user
Re: PL*SQL - Which users are ACTIVE ? [message #494721 is a reply to message #494718] Wed, 16 February 2011 15:32 Go to previous messageGo to next message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
Thanks for the link BlackSwan, in deed fine manual it is:

Here's what i typed:

SQL> select username, account_status
  2  from dba_users
  3  /


And here is what the result looks like:

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------
ABC334                         OPEN
DEF444                         OPEN
SCPODM                         OPEN
PICA                           EXPIRED(GRACE)
OUTLN                          LOCKED
ALALAF                         LOCKED
TSMSYS                         EXPIRED & LOCKED
DIP                            EXPIRED & LOCKED
ORACLE_OCM                     EXPIRED & LOCKED



This answers my question, so goobye to all !

By the way here's how to lock an account:
SQL> alter user alfredo account unlock;


Re: PL*SQL - Which users are ACTIVE ? [message #494733 is a reply to message #494704] Wed, 16 February 2011 17:03 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Michel Cadot wrote on Wed, 16 February 2011 13:01
Have you other smart answers or is this the only one you are able to post?

Regards
Michel

Are you so block-headed you cannot detect sarcasm anymore?
Laughing

[Updated on: Wed, 16 February 2011 23:19] by Moderator

Report message to a moderator

Re: PL*SQL - Which users are ACTIVE ? [message #494739 is a reply to message #494733] Wed, 16 February 2011 17:26 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you so block-headed that you can't detect when someone is telling you off for posting nothing other than sarcasm?
If you're not going to actually help people what use are you?
Re: PL*SQL - Which users are ACTIVE ? [message #494752 is a reply to message #494715] Wed, 16 February 2011 23:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I'm not the DBA, which means i wont even be able to see that information ? Ok then.

So how are you able to query dba_users, dba_sys_privs and execute alter user?
Anyway, I meant that if you are not the DBA you have not access to this information.

Regards
Michel

[Updated on: Wed, 16 February 2011 23:23]

Report message to a moderator

Re: PL*SQL - Which users are ACTIVE ? [message #494760 is a reply to message #494752] Thu, 17 February 2011 00:05 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I suppose that "being a DBA" is a job. "Being able to connect to a database using DBA privileges" is something different.
Re: PL*SQL - Which users are ACTIVE ? [message #494770 is a reply to message #494760] Thu, 17 February 2011 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh, oh!
Not being a DBA and having DBA privileges is quite dangerous.

Regards
Michel
Re: PL*SQL - Which users are ACTIVE ? [message #494812 is a reply to message #494770] Thu, 17 February 2011 03:41 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Seems to be the way they work though, from the OPs previous thread we discovered that they all had access to the dba views.
Re: PL*SQL - Which users are ACTIVE ? [message #494889 is a reply to message #494812] Thu, 17 February 2011 08:09 Go to previous messageGo to next message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
Hi to all,

I'm clearly not the DBA as for i don't even have TOAD licence yet on my computer.

But i don't know how come in the DEV environment i'm able to perform such commands. Anyhow, thanks for all of you for the answers as well as for our little friend with sarcasm.

Smile

Thanks !
Re: PL*SQL - Which users are ACTIVE ? [message #494910 is a reply to message #494889] Thu, 17 February 2011 08:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
...as for i don't even have TOAD licence yet on my computer.

I have not either and I manage databases, and this is why I can call me a DBA.


Regards
Michel
Re: PL*SQL - Which users are ACTIVE ? [message #494920 is a reply to message #494910] Thu, 17 February 2011 08:48 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Anyone would think you don't like TOAD Michel Wink
Re: PL*SQL - Which users are ACTIVE ? [message #494932 is a reply to message #494920] Thu, 17 February 2011 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How they could think that? Laughing

Michel
Re: PL*SQL - Which users are ACTIVE ? [message #494934 is a reply to message #494932] Thu, 17 February 2011 08:58 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Just a hunch Smile
Re: PL*SQL - Which users are ACTIVE ? [message #494951 is a reply to message #494934] Thu, 17 February 2011 09:27 Go to previous messageGo to next message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
Ok i see Michel Cadot ...Well good for you.

Smile

But i'm one of the front end develoopers that will be called to develop beautiful reports using:
- MicroStrategy
- IBM Cognos Report Studio 8
- as well as maybe Business Oebjects

And the whole original reason fo this post was because i create a Frreform SQL report using MicroStrategy (for those familiar with that BI tool), which in return is calling a store proc and passing 2 parameters.

The report was executing with no apparent errors but the report was always empty, which i then realised because there were almost no data in the DEV environment so before even going further and asking another team to POINT the MicroStrategy project to another environment such as the TEST environment (that supposely has more data) i decided to try and directly TEST the store proc and passing 2 parameters etc ... An therefore i was getttin the infamous error mesage:

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00904: insufficient privilege to access object ABC.ADW_SOME__PKG
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


And well that's how i ended up in this famous forum. VoilĂ  ...


Smile
Re: PL*SQL - Which users are ACTIVE ? [message #494974 is a reply to message #494951] Thu, 17 February 2011 10:06 Go to previous message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
And of course i was not the one that developped that famous store proc ...

Chat to you all soon !
Previous Topic: Inhibit displaying messages in the "Script Output" window
Next Topic: SQLPLUS not exist
Goto Forum:
  


Current Time: Thu Mar 28 09:11:04 CDT 2024