Hi, If the autonomous transaction doesn't work (from memory i think it won't but can't remember why) but you can submit a dbms_job which locks 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 prefer but I prefer dbms_job for one off jobs. Regards, Fergal On 9 Oct 2014 21:02, <rajendra.pande@xxxxxxx> wrote: > Nice!! > > > > Maybe an autonomous transaction? > > > > Regards > > > > - Raj Pande > > UBS AG > > Platform Services - Operations > > Global Service Delivery (GSDM) > > 480 Washington Blvd. Jersey City, NJ 07310 > > TEL# - External - +1 201 318 7597 > > Internal - 19 436 7597 > > > > *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto: > oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Jure Bratina > *Sent:* Thursday, October 09, 2014 3:58 PM > *To:* andrew.kerber@xxxxxxxxx > *Cc:* mark.powell2@xxxxxx; Oracle-L Freelists > *Subject:* Re: Logout system trigger > > > > Hi, > > when tracing the session when such a trigger fires, an ORA-30511 is found > in the trace: > > PARSING IN CURSOR #10476772 len=76 dep=1 uid=553 oct=47 lid=553 > tim=1412883002571503 hv=1931945942 ad='45bd7ad0' sqlid='11j0qr5tkf9yq' > begin > execute immediate 'alter user u1 identified by a account lock'; > end; > END OF STMT > PARSE > #10476772:c=2999,e=3411,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1412883002571501 > ===================== > PARSING IN CURSOR #10473708 len=41 dep=2 uid=553 oct=43 lid=553 > tim=1412883002593638 hv=1975896108 ad='4752a160' sqlid='ca8n0fxuwbk1c' > alter user u1 identified > END OF STMT > PARSE > #10473708:c=0,e=21909,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,plh=0,tim=1412883002593636 > CLOSE #10473708:c=0,e=5,dep=2,type=0,tim=1412883002593965 > EXEC > #10476772:c=0,e=22382,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=0,tim=1412883002594025 > ERROR #10476772:*err=30511* tim=1412883002594048 > > > $ oerr ora 30511 > 30511, 00000, "invalid DDL operation in system triggers" > // *Cause: An attempt was made to perform an invalid DDL operation > // in a system trigger. Most DDL operations currently are not > // supported in system triggers. The only currently supported DDL > // operations are table operations and ALTER?COMPILE operations. > // *Action: Remove invalid DDL operations in system triggers. > > Might be because of this restriction: h <http://goog_1416375729> > ttp://docs.oracle.com/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, 2014 at 9:41 PM, Andrew Kerber <andrew.kerber@xxxxxxxxx> > wrote: > > The syntax is valid. I was just adding an extra level of complexity by > changing the password again. > > Sent from my iPad > > > > On Oct 9, 2014, at 2:02 PM, Powell, Mark <mark.powell2@xxxxxx> wrote: > > > > Shouldn't the command to lock the account just be: 'alter user my > account lock' ? > > > > -----Original Message----- > > From: oracle-l-bounce@xxxxxxxxxxxxx [mailto: > oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Andrew Kerber > > Sent: Thursday, October 09, 2014 12:14 PM > > To: Oracle-L Freelists > > Subject: Re: Logout system trigger > > > > Here is the trigger so far: > > > > create or replace trigger my_logoff_trigger before logoff on my.schema > declare > > sql_cmd varchar2(200); > > pwd varchar2(30); > > begin > > dbms_output.enable(10000); > > update my_audit > > set logoff_time=systimestamp > > where os_pid=(SELECT P.SPID FROM V$PROCESS P INNER JOIN V$SESSION S ON > > S.PADDR = P.ADDR WHERE S.AUDSID = sys_context('USERENV', > 'SESSIONID')) > > and oracle_session_id=SYS_CONTEXT('USERENV','SESSIONID'); > > commit; > > pwd:=generate_password(); > > sql_cmd:='alter user my identified by '||pwd||' account lock'; > > execute immediate sql_cmd; > > end if; > > exception > > when others then > > RAISE; > > end; > > > > Sent from my iPad > > > >> On Oct 9, 2014, at 11:00 AM, Andrew Kerber <andrew.kerber@xxxxxxxxx> > wrote: > >> > >> I am trying to create a trigger that locks an account after a user > disconnects. It also writes a record to an auditing table. It writes the > record successfully, then I use execute immediate to lock the account, but > the lock command seems to be ignored. I expect there is some special > processing I need to do. Does anyone have an example of how to do this? > 11.2.0.4 EE on Linux. > >> > >> Sent from my iPad > > -- > > //www.freelists.org/webpage/oracle-l > > > > > > -- > > //www.freelists.org/webpage/oracle-l > > > > > > -- > //www.freelists.org/webpage/oracle-l > > > > Please visit our website at > http://financialservicesinc.ubs.com/wealth/E-maildisclaimer.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. >