All,

I have an Oracle view built on top of a partitioned table. Occasionally the view becomes INVALID, even though there are no DDL changes happening on the base table. The view becomes VALID again automatically when it is accessed or queried, but this behavior is causing issues in production.

I'm trying to understand what could be causing the intermittent invalidation.

create view my_view as
SELECT dly_fct_id, acct_ref_id, bus_dt
FROM my_table
WHERE bus_dt = TO_DATE('01/01/2500','MM/DD/YYYY');

Note: The date above is only a placeholder. In reality this predicate changes dynamically based on the ETL run date.

Base table structure:

CREATE TABLE my_table
(
  dly_fct_id   NUMBER,
  acct_ref_id  NUMBER,
  bus_dt       DATE
)
PARTITION BY RANGE (bus_dt)
INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(
  PARTITION p0 VALUES LESS THAN (TO_DATE('01-JAN-1900','DD-MON-YYYY'))
)
COMPRESS;

Constraint:

ALTER TABLE my_table
ADD CONSTRAINT xpk_my_table
PRIMARY KEY (dly_fct_id, acct_ref_id)
RELY;

The table actually contains ~50 columns, but I only included the relevant ones here.

Additional details:

The view does not become invalid daily, but it happens intermittently.

There are no known DDL operations on the table except regular ETL data loads.

The view becomes VALID again automatically when it is queried.

This is happening in a production environment, so we want to understand the root cause.

Questions:

What could cause a view to become INVALID intermittently without explicit DDL on the base table?

Could this be related to partition maintenance, statistics gathering, or constraint changes?

What system views or logs should we check to identify the root cause?

Any guidance on where to investigate would be greatly appreciated.

SQL>
SQL> create table t
  2  partition by list (owner) automatic
  3  ( partition p1 values ('SYS'))
  4  as select * from dba_objects
  5  where object_id is not null;

Table created.

SQL>
SQL> alter table t
  2  add constraint t_pk
  3  primary key (object_id)
  4  rely;

Table altered.

SQL>
SQL> create or replace
  2  view my_view_on_t as
  3  select * from t
  4  where owner in ('SYS','SYSTEM');

View created.

SQL>
SQL> select status from user_objects
  2  where object_name = 'MY_VIEW_ON_T';

STATUS
-------
VALID

SQL>
SQL> alter table t move partition for ( 'SYS' ) compress;

Table altered.

SQL>
SQL> alter index t_pk rebuild;

Index altered.

SQL>
SQL> select status from user_objects
  2  where object_name = 'MY_VIEW_ON_T';

STATUS
-------
VALID

SQL>
SQL> truncate table t;

Table truncated.

SQL>
SQL> select status from user_objects
  2  where object_name = 'MY_VIEW_ON_T';

STATUS
-------
VALID

SQL>
SQL> alter table t add partition px values ('XXX' );

Table altered.

SQL>
SQL> alter table t modify constraint t_pk disable;

Table altered.

SQL>
SQL> select status from user_objects
  2  where object_name = 'MY_VIEW_ON_T';

STATUS
-------
INVALID

SQL>
SQL>