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 &apos;/apps01/oradata/orcl19xusers01.dbf&apos; 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 &apos;/apps01/oradata/orcl19xusers01.dbf&apos; autoextend on
 </txt>
</msg>

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s