How do I shrink a DBF file in oracle?

How do I shrink a DBF file in oracle?

The basic syntax for this command is: Alter database name datafile ‘file_name’ resize size; Where name is the name of the database, file_name is the name of the file and size is the new size to make this file. We can see this size change in the dba_data_files table as well as from the server.

How do I reduce the size of my Oracle database?


  1. If your tablespace used size is pretty less than allocated size then you can resize the datafile.
  2. Create retention policy for your application tables then you can delete rows from the table / drop partition. After delete you need to move the tables and rebuild the indexes.
  3. Decrease your temp / undo tablespace.

Can we reduce the datafile size in oracle?

Online shrink of objects towards the end of the datafile may allow you to reduce the datafile size. Move the SYSAUX objects with extents towards the end of the datafile to a temporary tablespace, reduce the datafile size and move them back.

How do I shrink a table in oracle?

Oracle table shrink space commands. SQL> Alter table mytable shrink space cascade; Table altered. Note that the cascade option option will only recover space for the object and all dependent objects, and it does not alter the indexes.

How do you resize a data file?

Manually Resizing a Datafile For a bigfile tablespace you can use the ALTER TABLESPACE statement to resize a datafile. You are not allowed to add a datafile to a bigfile tablespace. Manually reducing the sizes of datafiles enables you to reclaim unused space in the database.

How do I shrink a tablespace?

How to Shrink an Oracle Tablespace

  1. Example: purge tablespace USERS;
  2. Example: alter tablespace USERS coalesce;
  3. Example: alter database datafile ‘C:ORACLEPRODUCT10.1.0ORADATAORA10GUSERS01.DBF’resize 10000M;
  4. Errors you might get: ORA-03297: file contains used data beyond requested RESIZE value.

How do I reclaim a space in Oracle?

Here are the high-level steps that will be performed.

  1. Create an ASM Disk Group using the ASM Filter Driver.
  2. Create a tablespace in this diskgroup and populate it with some random data.
  3. Check the space allocated at the database and array level.
  4. Drop the tablespace and run ASM rebalance.
  5. Check the space again.

How do I resize datafiles?

You can’t resize the datafiles. However, you can resize the tablespace by adding new datafiles in the tablespace. You can’t run the ALTER DATABASE query to resize or change the datafile configurations. This is because the ALTER DATABASE command is unsupported in RDS for Oracle.

How do you shrink a table?

Resize an entire table manually

  1. Rest the cursor on the table until the table resize handle. appears at the lower-right corner of the table.
  2. Rest the cursor on the table resize handle until it becomes a double-headed arrow .
  3. Drag the table boundary until the table is the size you want.

How do you increase the size of a datafile in Oracle?

You can specify by how much size the file should increase and Maximum size to which it should extend. SQL> alter database datafile ‘/u01/oracle/ica/icatbs01. dbf’ auto extend ON next 5M maxsize 500M; You can also make a datafile auto extendable while creating a new tablespace itself by giving the following command.

How do I enable Autoextend in Oracle tablespace?

You can check the autoextensible datafiles using the following script. select file_name,tablespace_name,maxbytes from dba_data_files where autoextensible=’YES’; You can enable the autoextend for the existing datafiles as follows. alter database datafile ‘+DATAC1/MSDB/DATAFILE/myDataTBS.

Recent Posts