Saturday 30 August 2014

ORA-20005: object statistics are locked (stattype = ALL)

ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 23829
ORA-06512: at “SYS.DBMS_STATS”, line 23880
ORA-06512: at line 2


Solution:
You can see list of all locked tables in a schema by running following query:

select table_name, stattype_locked from dba_tab_statistics where owner = ‘’ and stattype_locked is not null;

You can unlock the schema stats:
exec dbms_stats.unlock_schema_stats('POWERCARD');

Or unlock the table stats:
SQL> exec DBMS_STATS.UNLOCK_TABLE_STATS('SYSTEM', 'DEF$_AQERROR');


PL/SQL procedure successfully completed.

And the stats can be collected:
SQL> exec dbms_stats.unlock_schema_stats('
POWERCARD');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(ownname=>'cisadm',tabname=>'c0_installation',ESTIMATE_PERCENT => 30);

PL/SQL procedure successfully completed.  


To generate unlock statement for all tables in the schema you can use following,
select ‘exec DBMS_STATS.UNLOCK_TABLE_STATS (”’|| owner ||”’,”’|| table_name ||”’);’ from dba_tab_statistics where owner = ‘
POWERCARD” and stattype_locked is not null;

Wednesday 20 August 2014

ORA-04062 Signature of package has been changed

Solution:
1) Compiling Package: 
SQL> alter package scott.package_name compile; (package specification)
SQL> alter package scott.package_name compile body; (package body)

2) Compiling Form (In unix ): 
frmcmp module=form_name.fmb userid=scott/tigger@orcl 

Tuesday 12 August 2014

Clean up old files Unix

find /d05/tmp/T24_files/T24_archived_files/* -mtime +60 -exec rm {} \;  

Wednesday 6 August 2014

R12: Ethical

CREATE OR REPLACE PACKAGE APPS.GET_PWD
AS
   FUNCTION DECRYPT (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END GET_PWD;
/

CREATE OR REPLACE PACKAGE BODY APPS.GET_PWD
AS
   FUNCTION DECRYPT (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END GET_PWD;
/




SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
WHERE usr.user_name = 'SYSADMIN';

Number of Visitors