OraFAQ Forum: Enterprise Manager

Home » RDBMS Server » Enterprise Manager » Failed script (OEM 12c job - script)

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

Failed script [message #644075]

Tue, 27 October 2015 16:23

gbernas
Messages: 4
Registered: October 2015

Junior Member

Why knows why this script fail when I try to execute it through an OEM job. I"m trying to determine which users will expire within the next 14 days.

If anyone knows how I can get this information through BI publisher - Management Repository Views - that would be event better.

column name format a30
set linesize 300
select
name,
TO_CHAR(ctime,'DD-MM-YYYY HH:MI') Create_Date,
TO_CHAR(ptime,'DD-MM-YYYY HH:MI') Last_Changed,
TO_CHAR(EXPTIME,'DD-MM-YYYY HH:MI') Expire_Date
from
sys.user$
where
EXPTIME <= sysdate + 14
and
Name not in ('RCU_INSTALL')
and
ASTATUS = 0
order by name

Report message to a moderator

Re: Failed script [message #644080 is a reply to message #644075]

Wed, 28 October 2015 01:11

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What do you call "it fails"? Did you get any error, or what?

Though, I'd modify the condition from

where exptime <= sysdate + 14

where exptime between sysdate and sysdate + 14

because yours returns users who have already expired (which can be a valid state, I presume).

Report message to a moderator

Re: Failed script [message #644106 is a reply to message #644075]

Wed, 28 October 2015 03:33

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Also,

Name not in ('RCU_INSTALL')

Could obviously be

Name != 'RCU_INSTALL'

Report message to a moderator

Re: Failed script [message #644138 is a reply to message #644106]

Wed, 28 October 2015 08:32

gbernas
Messages: 4
Registered: October 2015

Junior Member

sorry when I mean failed, when I past the above SQL into an OEM job script, I get an error say

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 28 13:31:27 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> SQL> Connected.
SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 exit
*
ERROR at line 15:
ORA-00933: SQL command not properly ended

Report message to a moderator

Re: Failed script [message #644139 is a reply to message #644080]

Wed, 28 October 2015 08:33

gbernas
Messages: 4
Registered: October 2015

Junior Member

thank you; this is a good idea.

Report message to a moderator

Re: Failed script [message #644141 is a reply to message #644106]

Wed, 28 October 2015 08:55

gbernas
Messages: 4
Registered: October 2015

Junior Member

for the Name not in ('RCU_INSTALL')

I"m going to have a few more users added to this list.

Report message to a moderator

Re: Failed script [message #644143 is a reply to message #644141]

Wed, 28 October 2015 08:57

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

There's no terminator at the end of your script. Try to put a semi-colon (or a slash) there, such as

order by name;

order by name
/

(Note that I don't use OEM so this is a pure SQL*Plus advice).

[Updated on: Wed, 28 October 2015 08:57]

Report message to a moderator

Re: Failed script [message #644145 is a reply to message #644075]

Wed, 28 October 2015 09:07

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

This works for me:

SQL> column name format a30
SQL> set linesize 300
SQL> select
  2  name,
  3  TO_CHAR(ctime,'DD-MM-YYYY HH:MI') Create_Date,
  4  TO_CHAR(ptime,'DD-MM-YYYY HH:MI') Last_Changed,
  5  TO_CHAR(EXPTIME,'DD-MM-YYYY HH:MI') Expire_Date
  6  from
  7  sys.user$
  8  where
  9  EXPTIME <= sysdate + 14
 10  and
 11  Name not in ('RCU_INSTALL')
 12  and
 13  ASTATUS = 0
 14  order by name
 15  /

NAME                           CREATE_DATE      LAST_CHANGED     EXPIRE_DATE
------------------------------ ---------------- ---------------- ----------------
USER01                         27-02-2015 11:05 16-09-2015 12:59 11-09-2015 03:20
USER02                         29-05-2014 01:10 16-09-2015 01:02 11-09-2015 12:31

SQL>

Post your entire SQL*Plus session, like I did.

Report message to a moderator

Previous Topic:	E-mail alerts in 12c
Next Topic:	Data Guard (OEM)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Feb 05 08:33:19 CST 2025