How to check history of tablespace autoextend status in oracle
I was in a meeting call reviewing the last weeks highlights of critical activity performed on all of the oracle databases. Suddenly i got a bridge call notification from the monitoring team that tablespace got exhausted due to space crunch as the application data got a sudden growth. I was told that one of the tablespace autoextend option was disabled and tablespace was unable to extend to a max size. I tried to find the rca about the tablespace autogrowth status for a specific time. So lets look at the ways to find it !
Check the current status of USERS tablespace
SQL> select tablespace_name,autoextensible from dba_data_files;
TABLESPACE_NAME AUT
------------------------------ ---
SYSTEM YES
SYSAUX YES
EXAMPLE YES
USERS YES
Check if DB level audit is enabled
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string NONE
SQL> show parameter enable_ddl_logging
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_ddl_logging boolean TRUE
Enable DB level audit if not enabled
SQL> alter system set audit_trail=DB scope=spfile;
System altered.
SQL> alter system set enable_ddl_logging=true;
System altered.
Check the current time
SQL> select to_char(sysdate,'dd-mm-yy hh24:mi:ss') as B4_change from dual;
B4_CHANGE
-----------------
29-05-21 20:15:04
Disable autoextend for the datafile
20:15:14 SQL> alter database datafile '/apps01/oradata/orcl19xusers01.dbf' autoextend off;
Database altered.
Check the tablespace autoextend status after disable
SQL> select tablespace_name,autoextensible from dba_data_files;
TABLESPACE_NAME AUT
------------------------------ ---
SYSTEM YES
SYSAUX YES
EXAMPLE YES
USERS NO
Again enable the autoextend status to check the difference
20:15:32 SQL> alter database datafile '/apps01/oradata/orcl19xusers01.dbf' autoextend on;
Database altered.
Verify the status of the tablespace
SQL> select tablespace_name,autoextensible from dba_data_files;
TABLESPACE_NAME AUT
------------------------------ ---
SYSTEM YES
SYSAUX YES
EXAMPLE YES
USERS YES
Use the below timestamp command to check the autoextend status history
20:15:19 SQL> select tablespace_name,autoextensible from dba_data_files as of timestamp timestamp '2021-05-29 20:15:14';
TABLESPACE_NAME AUT
------------------------------ ---
SYSTEM YES
SYSAUX YES
EXAMPLE YES
USERS NO
Locate to the below directory to ddl logs
[oracle@orcl19x ~]$ cd $ORACLE_BASE/diag/rdbms/DBNAME/SID/log/ddl
[oracle@orcl19x ddl]$ pwd
/apps01/base/diag/rdbms/orcl19x/orcl19x/log/ddl
[oracle@orcl19x ddl]$ ls
log.xml
Check the ddl modification in the ddl audit logfile
<msg time='2021-05-29T20:32:15.415+05:30' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='orcl19x' host_addr='fe80::d9a1:1f74:379:20e3%enp0s3'
version='1'>
<txt>alter database datafile '/apps01/oradata/orcl19xusers01.dbf' autoextend off
</txt>
</msg>
<msg time='2021-05-29T20:32:19.562+05:30' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='orcl19x' host_addr='fe80::d9a1:1f74:379:20e3%enp0s3'>
<txt>alter database datafile '/apps01/oradata/orcl19xusers01.dbf' autoextend on
</txt>
</msg>