How to check history of tablespace autoextend status in oracle

Question

I am new to oracle. I work on oracle database 12c version. I have to find out if the tablespace autoextend status was disabled for previous week.But i can able to view current autoextend status for a tablespace. Is there any direct approach to find the tablespace autoextend status using database views?

Below output shows the current tablespace autoextensible status.

Sample output:

SQL> select TABLESPACE_NAME,AUTOEXTENSIBLE from dba_data_files;

TABLESPACE_NAME                AUT
------------------------------ ---
SYSTEM                         NO
SYSTEM                         YES
SYSAUX                         YES
UNDOTBS1                       YES
USERS                          NO
USERS                          NO
USERS                          YES
EXAMPLE                        YES

Expected

Autoextensible status - yes or no for previous week

No. There isn't going to be a view that gives you the data file configuration from some time in the past. If you want to track this going forward, you'd need to create a job that persists the information at whatever frequency you want to a new table. — Justin Cave, May 25 '21 at 08:59
Can we track autoextend disable status from audit or any static dba_hist views? — Kishan, May 25 '21 at 09:02
If you've enabled auditing of DDL statements like enabling and disabling autoextension, sure, that data would potentially be in the audit tables. I assume, though, that if you had done that you wouldn't be asking the question. — Justin Cave, May 25 '21 at 09:11

score 1 · Accepted Answer · answered May 26 '21 at 23:15

If you're lucky, you can use a Flashback query to find historical values for your data files. To use flashback on the data dictionary you will need to be logged on as a privileged user like SYS:

SYS@orclpdb> select tablespace_name,autoextensible from dba_data_files as of timestamp timestamp '2021-05-20 00:00:00';

TABLESPACE_NAME                AUT
------------------------------ ---
SYSTEM                         YES
SYSAUX                         YES
UNDOTBS1                       YES
USERS                          YES

Unfortunately, a week is a long time for the historical (UNDO) data to be available. Your query is likely to fail with an error like "ORA-08180: no snapshot found based on specified time". But it doesn't hurt to try.

If flashback doesn't work, you'll need to follow Justin's advice and do something like create a scheduler job to periodically write the data into a table.

How to check history of tablespace autoextend status in oracle

1 Answers1