• 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. Oracle Database 12.2 RMAN Cross Platform Tablespace Transport Over Network
  2. How to Manage Oracle database Audit File Directory Growth with cron (ASM Instances Only)
  3. Oracle 11G RMAN – Understanding UNDO backup optimisation
  4. Managing Oracle Database Backup with RMAN (Examples included)
  5. How to trace asmcmd command on UNIX/Linux
  6. How To Create an Encrypted Tablespace in Oracle 12c Pluggable Database
  7. Recommendation for the Oracle Real Application Cluster Interconnect and Jumbo Frames
  8. How to Monitor SGA Memory on Oracle Pluggable Databases
  9. How to extend ASM disk from OS level in CentOS/RHEL
  10. How to delete archives from only 1 archive destination when 2 or more are in use

You May Also Like

Primary Sidebar

Recent Posts

  • How to Disable IPv6 on Ubuntu 18.04 Bionic Beaver Linux
  • How to Capture More Logs in /var/log/dmesg for CentOS/RHEL
  • Unable to Start RDMA Services on CentOS/RHEL 7
  • How to rename a KVM VM with virsh
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary