-- recreate rulemanager (inside is recreasiton of catexf.sql)
?@/rdbms/admin/catnorul.sql
?@/rdbms/admin/catrul.sql

begin 
dbms_rlmgr_dr.cleanup_events; 
end;
/

begin 
dbms_rlmgr_dr.execschdactions('RLM$SCHDNEGACTION'); 
end;
/

-- observed error
    Tue Dec 20 02:03:08 2011 
    ORA-12012: error on auto execute of job EXFSYS.RLM$EVTCLEANUP 
    Tue Dec 20 02:03:08 2011 
    ORA-04068: existing state of packages has been discarded 
    Tue Dec 20 02:03:08 2011 
    ORA-04065: not executed, altered or dropped stored procedure EXFSYS.DBMS_RLMGR_DR 
    Tue Dec 20 02:03:08 2011 
    ORA-06508: PL/SQL: could not find program unit being called: EXFSYS.DBMS_RLMGR_DR 
    Tue Dec 20 02:03:08 2011 
    ORA-06512: at line 1 

-- recompile package
alter package EXFSYS.DBMS_RLMGR_DR compile;
alter package EXFSYS.DBMS_RLMGR_DR compile body;

-- flash shared pool
ALTER SYSTEM FLUSH SHARED_POOL;


select owner, job_name, job_action from dba_scheduler_jobs where owner = 'EXFSYS'
select owner, job_name, run_count, failure_count, last_start_date from dba_scheduler_jobs where owner = 'EXFSYS'


BEGIN
  DBMS_SCHEDULER.run_job (job_name            => 'EXFSYS.RLM$SCHDNEGACTION', use_current_session => FALSE);
END;
/

BEGIN
  DBMS_SCHEDULER.run_job (job_name            => 'EXFSYS.RLM$EVTCLEANUP', use_current_session => TRUE);
END;
/




--
col owner for a10
col object_name for a20
col object_type for a20
select owner, object_name, object_type, status from dba_objects where object_name = 'RLM$SCHDNEGACTION'

=================================

set linesize 200
set pages 999
col object for a40
col dependecy_object for a40

select obj as object, robj as dependecy_object, referenced_type from (
select   owner || '.' || name as obj, type,
   referenced_owner || '.' || referenced_name as robj,referenced_type,
	owner, referenced_owner as rowner, type, referenced_type as rtype, dependency_type
from dba_dependencies d
where 
type LIKE 'PACKAGE%' and lower(NAME) IN ('dbms_rlmgr_dr')
)
where owner != rowner;


/*
 *
 */ 
set pages 999
set linesize 200
col obj for a40
col robj for a40
col comments for a30
--
WITH
plsql_objects AS (
    SELECT owner || '.' || name as obj, type, referenced_owner || '.' || referenced_name as robj, referenced_name, referenced_type, owner, referenced_owner as rowner, type, referenced_type as rtype, dependency_type
    FROM dba_dependencies d
    WHERE type LIKE 'PACKAGE%' AND owner != referenced_owner AND lower(NAME) IN ('dbms_rlmgr_dr') 
    ORDER BY referenced_name
),
--    
plsql_objects_with_comments AS ( 
    SELECT obj, robj,
           CASE(referenced_type) 
           WHEN 'PACKAGE' THEN ' '
           WHEN 'SYNONYM' THEN (SELECT CAST(count(1) AS VARCHAR(255)) FROM dba_synonyms WHERE owner='PUBLIC' AND synonym_name=referenced_name) || ' exists public synonyms'
           END AS Comments
    FROM plsql_objects
    order by comments, obj, robj
)    
--
SELECT * FROM plsql_objects_with_comments
 

/*
OBJ                                      ROBJ                                     COMMENTS
---------------------------------------- ---------------------------------------- ---------------
EXFSYS.DBMS_RLMGR_DR                     SYS.DBMS_ASSERT
EXFSYS.DBMS_RLMGR_DR                     SYS.EXF$DBMS_EXPFIL_SYSPACK
EXFSYS.DBMS_RLMGR_DR                     SYS.STANDARD
EXFSYS.DBMS_RLMGR_DR                     SYS.STANDARD
EXFSYS.DBMS_RLMGR_DR                     XDB.DBMS_XMLDOM
EXFSYS.DBMS_RLMGR_DR                     PUBLIC.ALL_SCHEDULER_JOBS                resolve synonym
EXFSYS.DBMS_RLMGR_DR                     PUBLIC.DBMS_ASSERT                       resolve synonym
EXFSYS.DBMS_RLMGR_DR                     PUBLIC.DBMS_LOCK                         resolve synonym
EXFSYS.DBMS_RLMGR_DR                     PUBLIC.DBMS_SCHEDULER                    resolve synonym
EXFSYS.DBMS_RLMGR_DR                     PUBLIC.DBMS_SQL                          resolve synonym
EXFSYS.DBMS_RLMGR_DR                     PUBLIC.DBMS_SQL                          resolve synonym
EXFSYS.DBMS_RLMGR_DR                     PUBLIC.DBMS_XMLDOM                       resolve synonym
EXFSYS.DBMS_RLMGR_DR                     PUBLIC.DBMS_XMLPARSER                    resolve synonym
EXFSYS.DBMS_RLMGR_DR                     PUBLIC.PLITBLM                           resolve synonym
EXFSYS.DBMS_RLMGR_DR                     PUBLIC.USER_SCHEDULER_JOBS               resolve synonym
EXFSYS.DBMS_RLMGR_DR                     PUBLIC.USER_TRIGGERS                     resolve synonym
*/

set linesize 200
col object_name for a30
col synonym_name for a30
col table_owner for a10
--
SELECT 
     o.object_type,s.table_name,
     s.synonym_name,s.table_owner,s.owner
FROM 
     dba_synonyms s,
     dba_objects o
WHERE 
     o.object_name=s.table_name and o.object_type LIKE 'PACKAGE' and o.owner=s.table_owner 
     and s.owner='PUBLIC' and o.owner = 'EXFSYS'
 
 
 WITH
plsql_objects AS (
    SELECT owner || '.' || name as obj, type, referenced_owner || '.' || referenced_name as robj, referenced_name, referenced_type, owner, referenced_owner as rowner, type, referenced_type as rtype, dependency_type
    FROM dba_dependencies d
    WHERE type LIKE 'PACKAGE%' AND owner != referenced_owner AND lower(referenced_NAME) IN ('dbms_rlmgr_dr') 
    ORDER BY referenced_name
),
--    
plsql_objects_with_comments AS ( 
    SELECT obj, robj,
           CASE(referenced_type) 
           WHEN 'PACKAGE' THEN ' '
           WHEN 'SYNONYM' THEN (SELECT CAST(count(1) AS VARCHAR(255)) FROM dba_synonyms WHERE owner='PUBLIC' AND synonym_name=referenced_name) || ' exists public synonyms'
           END AS Comments
    FROM plsql_objects
    order by comments, obj, robj
)    
--
SELECT * FROM plsql_objects_with_comments


select owner,object_name,status from dba_objects where object_type='SYNONYM' and owner='PUBLIC' and status='INVALID'

-- remove schema
----------------
-- How To Install and Uninstall Expression Filter Feature or EXFSYS schema [ID 258618.1]



set linesize 200
set pages 999
col object for a40
col owner for a10
col referenced_name for a40
col dependecy_object for a40
SELECT referenced_owner || '.' || referenced_name , referenced_type 
FROM dba_dependencies d
WHERE owner != referenced_owner AND OWNER='EXFSYS' 
ORDER BY referenced_name