Can you l= ock an account while a user is connected? If no, then perhaps the user is no= t technically disconnected and perhaps that is the issue, in that case a dbm= s_job with a wait to ensure no more sessions connected before locking is the= trick.

= On Thu, Oct 9, 2014 at 3:52 PM, Fergal Taheny <ftaheny@gmail.com> wrote:

Hi,

If the autonomous transaction doesn't work (from memory i thi= nk it won't but can't remember why) but you can submit a dbms_job which lock= s the account. Submitting a dmbs_job is dml so no problem there and then the= job does the ddl.

I have used this approach for other ddl
a few times and it works fine. And yeah you can use dbms_scheduler if you pr= efer but I prefer dbms_job for one off jobs.

Regards,
Fergal

On 9 Oct 2014 21:02, <= rajendra.pande@u= bs.com> wrote:
Nice!! <= u>
&nbs= p;
Maybe an= autonomous transaction?

<= span style=3D"font-size:10.0pt;font-family:"Tahoma","sans-ser= if";color:#002060">Regards <= /u>
=
- Raj Pand= e
UBS AG
&nb= sp;Platform Services - Operations
Globa= l Service Delivery (GSDM)
480 Washingto= n Blvd. Jersey City, NJ 07310=
TEL# - Ext= ernal - +1 201 318 7597<= /u>
  &n= bsp;          Internal - 19 436= 7597

From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Jure Bratina
= Sent: Thursday, October 09, 2014 3:58 PM
To: andrew.kerber@gmail.com
<= b>Cc: mark.powe= ll2@hp.com; Oracle-L Freelists
Subject: Re: Logout system trig= ger

<= div>
Hi,
whe= n tracing the session when such a trigger fires, an ORA-30511 is found in th= e trace:

PARSING= IN CURSOR #10476772 len=3D76 dep=3D1 uid=3D553 oct=3D47 lid=3D553 tim=3D141= 2883002571503 hv=3D1931945942 ad=3D'45bd7ad0' sqlid=3D'11j0qr5tkf9yq'
beg= in
execute immediate 'alter user u1 identified by a account lock';=
end;
END OF STMT
PARSE #10476772:c=3D2999,e=3D3411,p=3D0,cr=3D0,cu= =3D0,mis=3D1,r=3D0,dep=3D1,og=3D1,plh=3D0,tim=3D1412883002571501
=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
PARSING IN CURSOR #= 10473708 len=3D41 dep=3D2 uid=3D553 oct=3D43 lid=3D553 tim=3D141288300259363= 8 hv=3D1975896108 ad=3D'4752a160' sqlid=3D'ca8n0fxuwbk1c'
alter user u1 i= dentified
END OF STMT
PARSE #10473708:c=3D0,e=3D21909,p=3D0,cr=3D0,cu= =3D0,mis=3D1,r=3D0,dep=3D2,og=3D1,plh=3D0,tim=3D1412883002593636
CLOSE #1= 0473708:c=3D0,e=3D5,dep=3D2,type=3D0,tim=3D1412883002593965
EXEC #1047677= 2:c=3D0,e=3D22382,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D1,og=3D1,plh=3D0,t= im=3D1412883002594025
ERROR #10476772:err=3D30511 tim=3D1412883002= 594048

$ oerr ora 30511
30511, 00000, "invalid DDL operation in system triggers= "
// *Cause: An attempt was made to perform an invalid DDL operatio= n
//          in a system tr= igger. Most DDL operations currently are not
//    &n= bsp;     supported in system triggers. The only currentl= y supported DDL
//          o= perations are table operations and ALTER?COMPILE operations.
// *Action: R= emove invalid DDL operations in system triggers.

Might be because of this restriction: h ttp://docs.oracle.c= om/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS797
Restrictions (= for BEFORE LOGOFF triggers): DDL on other objects is limited to compiling an= object, creating a trigger, and creating, altering, and dropping a table.

Regards,
Jure Bratina

On Thu, Oct 9, 20= 14 at 9:41 PM, Andrew Kerber <andrew.kerber@gmail.com> wrote:
The syntax is valid. I was just adding an extra level of c= omplexity by changing the password again.

Sent from my iPad=

> On Oct 9, 2014, at 2:02 PM= , Powell, Mark <= mark.powell2@hp.com> wrote:
>
> Shouldn't the command to l= ock the account just be: 'alter user my account lock' ?
>
> ----= -Original Message-----
> From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce= @freelists.org] On Behalf Of Andrew Kerber
> Sent: Thursday, Octob= er 09, 2014 12:14 PM
> To: Oracle-L Freelists
> Subject: Re: Log= out system trigger
>
> Here is the trigger so far:
>
&g= t; create or replace trigger my_logoff_trigger before logoff on my.schema de= clare
> sql_cmd varchar2(200);
> pwd varchar2(30);> begin
> dbms_output.enable(10000);
> u= pdate my_audit
> set logoff_time=3Dsystimestamp
>&n= bsp; where os_pid=3D(SELECT P.SPID FROM V$PROCESS P INNER JOIN V$SESS= ION S ON
> S.PAD= DR =3D P.ADDR WHERE S.AUDSID =3D sys_context('USERENV', 'SESSIONID'))
>= ; and oracle_session_id=3DSYS_CONTEXT('USERENV','SESSIONID');> commit;
> pwd:=3Dgenerate_password();=
> sql_cmd:=3D'alter user my identified by '||pwd||' acco= unt lock';
> execute immediate sql_cmd;
> end= if;
> exception
> when others then
> RAISE;> end;
>
> Sent from my iPad
>
>> On Oct 9, 2= 014, at 11:00 AM, Andrew Kerber <andrew.kerber@gmail.com> wrote:
>>
&= gt;> I am trying to create a trigger that locks an account after a user d= isconnects. It also writes a record to an auditing table. It wri= tes the record successfully, then I use execute immediate to lock the accoun= t, but the lock command seems to be ignored. I expect there is some sp= ecial processing I need to do. Does anyone have an example of how to d= o this? 11.2.0.4 EE on Linux.
>>
>> Sent from my iPa= d
> --
> http://www.freelists.org/webpage/oracle-l
>
&g= t;
> --
> http://www.freelists.org/webpage/oracle-l
>
&g= t;
--
http://www.freelists.org/webpage/oracle-l

<= /u>

Please visit our website at
http://financialservicesinc.ubs.com/wealth/E-maildisclaim= er.html
for important disclosures and information about our e-mail
policies. For your protection, please do not transmit orders
or instructions by e-mail or include account numbers, Social
Security numbers, credit card numbers, passwords, or other
personal information.