Thursday, June 28, 2012

Compile all invalid objects in Oracle 10.2 ->

If you have several invalid objects in Oracle database you can try to compile them all together with this in sqlplus (user that is running this needs rights to execute utl_recomp package):
exec utl_recomp.recomp_parallel(4);

recomp_parallel let you set degree of parallelism. 4 is good default.

There is also possible to use recomp_serial with utl_recomp.
If you need more info about utl_recomp you find it here:
http://docs.oracle.com/cd/E14072_01/appdev.112/e10577/u_recomp.htm


And you can check database invalid objects with this:
select object_name, object_Type, owner from dba_objects where status='INVALID';

You can also use all_objects if the user you are using does not have rights to use dba_objects.

Failed login attempts in Oracle 11.2 database

After moving Oracle 11.2g database into the another RAC cluster we faced a lot of failed login attempts into Grid Control. We needed to know where these login attempts are coming. And with following sql we did get enough information to tackle these logins:

select to_char(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') tstamp ,OS_USERNAME,USERNAME,USERHOST,ACTION_NAME, TERMINAL from DBA_AUDIT_SESSION where returncode = 1017 order by 1;

returncode 1017 stands for Oracle error "invalid username/password; logon denied"
and thats why we get those failed login attemps with it.