Search Results sys.obj$




The SYS.OBJ$ table is a fundamental data dictionary object in Oracle Database that stores metadata about all database objects, including tables, views, indexes, sequences, and other schema entities. In the context of Oracle E-Business Suite (EBS) 12.1.1 or 12.2.2, this table plays a critical role in maintaining object definitions, dependencies, and access control. Below is a detailed analysis of its structure, significance, and usage within Oracle EBS.

1. Overview of SYS.OBJ$

The SYS.OBJ$ table is part of the Oracle data dictionary and resides in the SYS schema. It contains essential metadata for every database object, such as:
  • Object ID (OBJ#) – A unique numeric identifier for each object.
  • Owner (OWNER#) – References the user who owns the object.
  • Object Name (NAME) – The name of the object.
  • Namespace (NAMESPACE) – Categorizes the object type (e.g., table, view, procedure).
  • Type (TYPE#) – Numeric code representing the object type (e.g., 2 for a table, 7 for a trigger).
  • Creation Timestamp (CTIME) – When the object was created.
  • Last Modification Timestamp (MTIME) – When the object was last altered.
  • Status (STATUS) – Indicates validity (e.g., 0 for invalid, 1 for valid).

2. Role in Oracle EBS

Oracle EBS relies heavily on the data dictionary for object management, security, and dependency resolution. The SYS.OBJ$ table supports:
  • Object Dependency Tracking – EBS modules use this table to verify dependencies before applying patches or upgrades.
  • Security and Privileges – The table helps enforce schema-level access control by linking objects to their owners.
  • Performance Optimization – The Oracle optimizer references SYS.OBJ$ to validate object existence during SQL parsing.

3. Key Considerations for EBS Administrators

While SYS.OBJ$ is critical, direct modifications are strongly discouraged due to risks of database corruption. Instead, EBS administrators should:
  • Use ALL_OBJECTS, DBA_OBJECTS, or USER_OBJECTS views for safer queries.
  • Leverage Oracle-provided scripts (e.g., adpatch in EBS) for object-related changes.
  • Monitor object validity post-patching via UTL_RECOMP or AD_ZD utilities in EBS.

4. Example Queries in EBS Context

-- Check invalid objects in APPS schema (common post-patch step)
SELECT owner, object_name, object_type 
FROM dba_objects 
WHERE status = 'INVALID' AND owner = 'APPS';

-- Find tables owned by APPS schema
SELECT name FROM sys.obj$ 
WHERE owner# = (SELECT user# FROM sys.user$ WHERE name = 'APPS') 
AND type# = 2;

5. Conclusion

The SYS.OBJ$ table is a cornerstone of Oracle's data dictionary, enabling EBS to manage thousands of objects efficiently. While it is primarily accessed indirectly through views and tools, understanding its structure aids in troubleshooting and optimizing EBS environments. Administrators should prioritize using supported interfaces to avoid destabilizing the system.