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

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • 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 20c New Feature: PDB Point-in-Time Recovery or Flashback to Any Time
  2. Threaded_execution=true Prevents OS Login As Sysdba in Oracle Database 12c
  3. ORA-65010: maximum number of pluggable databases created
  4. Understanding Transparent Data Encryption and Keystores in RAC
  5. Configuring Data Guard in Oracle Cloud Infrastructure (OCI)
  6. How to Change the Default Home Page of Oracle HTTP Server
  7. Oracle Interview Questions – Indexes on Partitioned Table
  8. Find Oracle RAC OCR & voting disk location
  9. CentOS / RHEL 7 : Oracleasm Create Disk Failed “Instantiating disk: failed”
  10. Shell Script to run DataPump Import Export

You May Also Like

Primary Sidebar

Recent Posts

  • nixos-rebuild Command Examples in Linux
  • nixos-option: Command Examples in Linux
  • nixos-container : Command Examples in Linux
  • nitrogen Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright