here is a super simple SQL that can help you to find data files that are no Auto-Extendable in your database farm using Oracle Enterprise Manager SQL.
SQL Data
SELECT
table1.CM_TARGET_NAME ,
table1.FILE_NAME ,
table1.AUTOEXTENSIBLE ,
table1.TABLESPACE_NAME
FROM
CM$MGMT_DB_DATAFILES_ECM table1
WHERE
table1.CM_TARGET_TYPE ='oracle_database'
AND table1.CM_SNAPSHOT_TYPE='oracle_dbconfig'
AND
(
(
table1.TABLESPACE_NAME IS NOT NULL
OR table1.FILE_NAME IS NOT NULL
)
AND
(
NLS_UPPER(table1.AUTOEXTENSIBLE) = NLS_UPPER('NO')
)
)
| CM_TARGET_NAME | FILE_NAME | AUTOEXTENSIBLE | TABLESPACE_NAME |
| DEMO1 | +DATA/DEMO1/datafile/system.495.788236081 | NO | SYSTEM |
| DEMO1 | +DATA/DEMO1/datafile/sysaux.496.788236083 | NO | SYSAUX |
| DEMO1 | +DATA/DEMO1/tempfile/temp.498.788236091 | NO | TEMP |
| DEMO2 | /u01/oradata/data01/psdefault.dbf | NO | PSDEFAULT |
| DEMO3 | /DEMO3/oradata/data02/pstemp01.dbf | NO | PSTEMP |
| DEMO3 | /DEMO3/oradata/data02/pstemp02.dbf | NO | PSTEMP |
| DEMO3 | /DEMO3/oradata/data02/tde_tbs_temp01.dbf | NO | TDE_TBS_TEMP |
| DEMO4 | /u01/oradata/data02/system03.dbf | NO | SYSTEM |
| DEMO4 | /u01/oradata/data02/xxah_08.dbf | NO | XXAH |
| DEMO4 | /u01/oradata/data03/cists08.dbf | NO | CISTS_01 |
| DEMO4 | /u01/oradata/data03/cists09.dbf | NO | CISTS_01 |
| DEMO4 | /u01/oradata/data03/cists10.dbf | NO | CISTS_01 |
| DEMO4 | /u01/oradata/data03/cists11.dbf | NO | CISTS_01 |
| DEMO4 | /u01/oradata/data03/cists12.dbf | NO | CISTS_01 |
| DEMO4 | /u01/oradata/data03/cists13.dbf | NO | CISTS_01 |
| DEMO4 | /u01/oradata/data03/cists14.dbf | NO | CISTS_01 |
| DEMO4 | /u01/oradata/data03/cists15.dbf | NO | CISTS_01 |
| DEMO4 | /u01/oradata/data03/cists33.dbf | NO | CISTS_01 |
| DEMO4 | /u01/oradata/data03/cists36.dbf | NO | CISTS_01 |
| DEMO4 | /u01/oradata/data03/cists37.dbf | NO | CISTS_01 |
| DEMO5 | /u01/oradata/data02/temp07.dbf | NO | PSTEMP |
| DEMO5 | /u01/oradata/data02/temp1.dbf | NO | AHGTT |
| DEMO5 | /u01/oradata/data02/temp2.dbf | NO | PSGTT01 |
| DEMO5 | /u01/oradata/data02/temp3.dbf | NO | PSTEMP |
| DEMO5 | /u01/oradata/data02/temp4.dbf | NO | TEMP |
| DEMO5 | /u01/oradata/data02/temp5.dbf | NO | PSTEMP |
| DEMO5 | /u01/oradata/data02/temp6.dbf | NO | PSTEMP |
PI Graph
SELECT
NLS_UPPER(table1.AUTOEXTENSIBLE),
COUNT(*) CNT
FROM
CM$MGMT_DB_DATAFILES_ECM table1
WHERE
table1.CM_TARGET_TYPE ='oracle_database'
AND table1.CM_SNAPSHOT_TYPE='oracle_dbconfig'
AND
(
(
table1.TABLESPACE_NAME IS NOT NULL
OR table1.FILE_NAME IS NOT NULL
)
)
GROUP BY
NLS_UPPER(table1.AUTOEXTENSIBLE)
Hope this helps you to find out the datafiles that are not auto extendable, and now you know what to do 😀
