SYS.LTADM or WMSYS.LTADM or both?
I'm investigating some invalid objects in a 10.2.0.4 database. The objects are are follows:
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ---------------------------------------- ------------------- -------
PUBLIC ALL_WM_VERSIONED_TABLES SYNONYM INVALID
PUBLIC USER_WM_VERSIONED_TABLES SYNONYM INVALID
PUBLIC DBA_WM_VERSIONED_TABLES SYNONYM INVALID
WMSYS USER_WM_VERSIONED_TABLES VIEW INVALID
WMSYS ALL_WM_VERSIONED_TABLES VIEW INVALID
WMSYS DBA_WM_VERSIONED_TABLES VIEW INVALID
The public synonyms are invalid because the base views are invalid so that part is easy. When I try to compile the three WMSYS views I get the same compilation error for each view:
ORA-00904: "SYS"."LTADM"."ARETHEREDIFFS": invalid identifier
Upon checking I find that the LTADM package is not owned by SYS but by WMSYS:
select owner, object_name, object_type, status from dba_objects where object_name ='LTADM';
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ -------------------- ------------------- -------
WMSYS LTADM PACKAGE VALID
WMSYS LTADM PACKAGE BODY VALID
As I test I cloned the USER_WM_VERSIONED_TABLES and changed the code so that it referenced WMSYS.LTADM.ARETHEREDIFFS and the view compiled with no errors.
So, I have two questions:
1. Who should own the LTADM package? SYS? WMSYS? Or both (one in each schema)?
2. Is there anything wrong with rewiting the exisiting view code to point to WMSYS.LTADM.ARETHEREDIFFS?
Thanks in advance.