DATAFILES DO NOT CHANGE TO READ ONLY MODE AFTER ENTERING THE ARCHIVING PERIOD (Doc ID 2737544.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

AV_ILM_0608_0614 tablespace is in ONLINE status even after the archive period stated. From the below query we can check that tablespace has entered archive period on 01-OCT-2020. As per this date the status column of the dba_tablespace must show READ ONLY. But it is not the case.

SQL> select a.tablespace_name, a.status, to_char(b.bytes,'999,999,999,999')
"BYTES",
2
to_char(add_months(to_date('01-01-1970','MM-DD,YYYY'),substr(a.tablespace_name
,9,3)+1),'DD-MON-YYYY') "WHEN PLACE OFFLINE",
3
to_char(add_months(to_date('01-01-1970','MM-DD,YYYY'),substr(a.tablespace_name
,14,3)+1),'DD-MON-YYYY') "WILL BE DELETED",
4
(-1)*months_between(to_char(add_months(to_date('01-01-1970','MM-DD,YYYY'),subs
tr(a.tablespace_name,9,3)),'DD-MON-YYYY'),
5
to_char(add_months(to_date('01-01-1970','MM-DD,YYYY'),substr(a.tablespace_name
,14,3)),'DD-MON-YYYY')) "MONTHNS BETWEEN"
6 from dba_tablespaces a, dba_data_files b
7 where a.tablespace_name = b.tablespace_name
8 and a.tablespace_name like '%ILM%'
9 order by a.tablespace_name
10 /

TABLESPACE_NAME STATUS BYTES WHEN PLACE OFFLINE
WILL BE DELETED MONTHNS BETWEEN
------------------------------ --------- ----------------
-------------------- -------------------- ---------------
AV_ILM_0608_0614 ONLINE 104,857,600 01-OCT-2020
01-APR-2021 6 <<<

Changes

 NA

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.