Revoke DBMS_JAVA permissions

Revoke dbms_java permission

Query the dba_java_policy table

$sqlplus / as sysdba
SQL>select * from  dba_java_policy

KING      GRANTEE TYPE SCHEMA TYPE NAME                NAME ACTION ENABLED    SEQ

GRANT XXPMS    SYS                java.io.FilePermission   *   read ENABLED 178
GRANT XXPMS   SYS                java.io.FilePermission   /-   read,write  ENABLED 181
GRANT XXPMS   SYS                java.io.FilePermission       /bin/ls   execute ENABLED 180



Using the SEQ value, you can run the following statement to revoke the granted java permissions

eg: suppose you want to revoke SEQ 178

begin
  DBMS_JAVA.disable_permission(178);
  DBMS_JAVA.delete_permission(178);
end;

ERROR: While GATHER_TABLE_STATS: ***ORA-20005: object statistics are locked (stattype = ALL)***

ERROR: While GATHER_TABLE_STATS: ***ORA-20005: object statistics are locked (stattype = ALL)***

Connect as a dba user and use the following statement to unlock the statistics:

sqlplus / as sysdba or sqlplus system/****

SQL>EXEC DBMS_STATS.UNLOCK_SCHEMA_STATS('SCOTT');

SQL>exec dbms_stats.gather_schema_stats('SCOTT',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

Error APP-FF-33980: Internal error attempting to generate package

Error "APP-FF-33980: Internal error attempting to generate package". While saving the fast formula.

How to diagnosis:
Compile the fast formula using FFXBCP command
go to
$cd $FF_TOP/bin
$FFXBCP apps/apps 0 Y -k %% 'ENTER_THE_NAME_OF_THE_FAST_FORMUALA'

it will generate 2 files one .log and .out

Please check the log file to see the package name
Connect using
$sqlplus apps/apps
SQL> alter package packagename compile body;
SQL> show error

fix the error and compile the fast formula

Calculating Employee Service years

Below script return Number of years, Months and days of an employee

Select ppos.person_id,
trunc(months_between(coalesce(ppos.projected_termination_date,sysdate),ppos.date_start )/12) ||' Years '||
       TO_CHAR (FLOOR(MOD (months_between(coalesce(ppos.projected_termination_date,sysdate),ppos.date_start ), 12)) ) ||' Months '||
       (To_Date (coalesce(ppos.projected_termination_date,sysdate))-Add_Months (ppos.date_start,Trunc (Months_Between (coalesce(ppos.projected_termination_date,sysdate), ppos.date_start)) ) )      ||' Days ' yearOfService
      
From   per_periods_of_service ppos

Flashback version Between query in oracle

Flashback version between query used to track a table row in a specified time period

Syntax: VERSIONS BETWEEN TIMESTAMP [lower bound] AND [upper bound];

eg:
SELECT * FROM wf_notifications
VERSIONS  BETWEEN TIMESTAMP TO_TIMESTAMP('15-AUG-2012 08:13:07')
                                      AND TO_TIMESTAMP('15-AUG-2012 12:01:17')

        Where message_type = 'XXXX'