OraFAQ Forum: Client Tools » PL*SQL - Which users are ACTIVE ?

Home » Other » Client Tools » PL*SQL - Which users are ACTIVE ? (SQL*Plus, Release 9.2.0.1.0, Windows XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PL*SQL - Which users are ACTIVE ? [message #494688]

Wed, 16 February 2011 10:27

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 !

Report message to a moderator

Re: PL*SQL - Which users are ACTIVE ? [message #494692 is a reply to message #494688]

Wed, 16 February 2011 10:35

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/

Report message to a moderator

Re: PL*SQL - Which users are ACTIVE ? [message #494694 is a reply to message #494688]

Wed, 16 February 2011 10:58

Michel Cadot
Messages: 68510
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

Report message to a moderator

Re: PL*SQL - Which users are ACTIVE ? [message #494700 is a reply to message #494688]

Wed, 16 February 2011 11:48

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

Michel Cadot
Messages: 68510
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

Report message to a moderator

Re: PL*SQL - Which users are ACTIVE ? [message #494715 is a reply to message #494704]

Wed, 16 February 2011 14:34

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

Report message to a moderator

Re: PL*SQL - Which users are ACTIVE ? [message #494718 is a reply to message #494715]

Wed, 16 February 2011 14:40

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

Report message to a moderator

Re: PL*SQL - Which users are ACTIVE ? [message #494721 is a reply to message #494718]

Wed, 16 February 2011 15:32

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;

Report message to a moderator

Re: PL*SQL - Which users are ACTIVE ? [message #494733 is a reply to message #494704]

Wed, 16 February 2011 17:03

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

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

cookiemonster
Messages: 13904
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?

Report message to a moderator

Re: PL*SQL - Which users are ACTIVE ? [message #494752 is a reply to message #494715]

Wed, 16 February 2011 23:22

Michel Cadot
Messages: 68510
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

Littlefoot
Messages: 21783
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.

Report message to a moderator

Re: PL*SQL - Which users are ACTIVE ? [message #494770 is a reply to message #494760]

Thu, 17 February 2011 01:22

Michel Cadot
Messages: 68510
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

Report message to a moderator

Re: PL*SQL - Which users are ACTIVE ? [message #494812 is a reply to message #494770]

Thu, 17 February 2011 03:41

cookiemonster
Messages: 13904
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.

Report message to a moderator

Re: PL*SQL - Which users are ACTIVE ? [message #494889 is a reply to message #494812]

Thu, 17 February 2011 08:09

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

Smile

Thanks !

Report message to a moderator

Re: PL*SQL - Which users are ACTIVE ? [message #494910 is a reply to message #494889]

Thu, 17 February 2011 08:33

Michel Cadot
Messages: 68510
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

Report message to a moderator

Re: PL*SQL - Which users are ACTIVE ? [message #494920 is a reply to message #494910]

Thu, 17 February 2011 08:48

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Anyone would think you don't like TOAD Michel Wink

Wink

Report message to a moderator

Re: PL*SQL - Which users are ACTIVE ? [message #494932 is a reply to message #494920]

Thu, 17 February 2011 08:54

Michel Cadot
Messages: 68510
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

How they could think that? Laughing

Laughing

Michel

Report message to a moderator

Re: PL*SQL - Which users are ACTIVE ? [message #494934 is a reply to message #494932]

Thu, 17 February 2011 08:58

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Just a hunch Smile

Smile

Report message to a moderator

Re: PL*SQL - Which users are ACTIVE ? [message #494951 is a reply to message #494934]

Thu, 17 February 2011 09:27

Quetzalcoatl1
Messages: 33
Registered: January 2011

Member

Ok i see Michel Cadot ...Well good for you.

Smile

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

Smile

Report message to a moderator

Re: PL*SQL - Which users are ACTIVE ? [message #494974 is a reply to message #494951]

Thu, 17 February 2011 10:06

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 !

Report message to a moderator

Previous Topic:	Inhibit displaying messages in the "Script Output" window
Next Topic:	SQLPLUS not exist

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Sep 30 10:04:11 CDT 2023