How can I reduce the size of data files in Oracle’s tablespace?

2 years ago
Benjamin Taylor
2 minutes

To reduce the size of Oracle tablespace data files, you can follow these steps:

  1. Firstly, you can determine the data file size that needs to be reduced by checking the usage of the current tablespace. You can use the following SQL statement for this query:
  2. Retrieve the tablespace name, file name, size in MB, and used space in MB from the DBA_DATA_FILES table where the tablespace name is the one specified.
  3. In this case, is the name of the tablespace to be queried.
  4. After confirming the data file to be reduced, you can use the statement ALTER DATABASE DATAFILE ‘‘ RESIZE to shrink the size of the data file. In this case, is the name of the data file to be reduced and is the new file size to be set.
  5. For example, to reduce the size of the data file named example.dbf to 100MB, you can execute the following command:
  6. Resize the datafile ‘example.dbf’ in the database to 100MB.
  7. It should be noted that reducing the size of a data file may result in data being truncated, so it is important to backup critical data before performing this operation.
  8. After shrinking the data files, you can query the tablespace usage again to confirm if the size of the data files has been reduced.
  9. Retrieve the tablespace name, file name, size in MB, and used space in MB from the DBA_DATA_FILES table where the tablespace name matches a specified value.
  10. If the size of the data file still has not reduced, it may be due to objects in the tablespace occupying the space in the data file. In this case, consider reorganizing the tablespace to free up space.
bannerAds