• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to Disable AUTOEXTEND Mode on a datafile in Oracle Database

By admin

Question: How do you take a datafile out of autoextend mode?

Oracle datafiles were given the ability to extend automatically if needed from a long time. If the given datafile is unable to allocate the space needed, it can increase the size of the datafile to make space for objects to grow.

To determine if the datafile has autoextend capabilities, query the dba_data_files table. For example:

SQL> select file_name, autoextensible from dba_data_files;

FILE_NAME AUT
---------------------------------------- ---
/u05/app/oracle/oradata/[SID]/tools01.dbf YES
/u05/app/oracle/oradata/[SID]/drsys01.dbf YES
/u05/app/oracle/oradata/[SID]/users01.dbf YES
/u05/app/oracle/oradata/[SID]/indx01.dbf YES
/u05/app/oracle/oradata/[SID]/rbs01.dbf NO
/u05/app/oracle/oradata/[SID]/temp01.dbf YES
/u05/app/oracle/oradata/[SID]/system01.dbf NO

The rows that have YES for AUTOEXTENSIBLE are the datafiles with the autoextend capabilities.

To DISABLE the autoextend capabilities of any of the datafiles in oracle database, issue:

SQL> alter database datafile '[datafile name]' AUTOEXTEND OFF;
Oracle – How To Check if Autoextensible Datafiles Set To Maxsize Unlimited

Filed Under: oracle

Some more articles you might also be interested in …

  1. How to Drop/Truncate Multiple Partitions in Oracle 12C
  2. How To Automate The Opening Of Pluggable Databases After The CDB Starts Up in Oracle 12c
  3. How to Restore ASM Based OCR After Complete Loss of the CRS Diskgroup on Linux/UNIX
  4. Oracle RMAN Pluggable Database Point in Time Recovery
  5. How to check and repair ACFS with FSCK
  6. DNS and DHCP Setup Example for Oracle Grid Infrastructure GNS
  7. How To Resize An ACFS Filesystem/ASM Volume (ADVM)
  8. How to move ASM spfile to a different disk group
  9. CentOS / RHEL 7 : How to set udev rules for ASM on multipath disks
  10. What Happens and What to Do when the SPFILE has been Manually Modified

You May Also Like

Primary Sidebar

Recent Posts

  • Basics of client connectivity in Oracle Data Guard configuration
  • ORA-354 ORA-353 and ORA-312: Possible corruption in Online Redo Log File Members in a Redo Log Group
  • How to relocate the redo log files to a different location on disk
  • Oracle Database: Redo log operations (Add/Drop/Change Location)
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary