Scribd
Upload
15 views2 pages

Add Datafile To Oracle

To reclaim space from a fragmented Oracle datafile, first identify free space in the tablespace using specific SQL queries. Then, move objects either to another tablespace or within the same one, and shrink segments if using ASSM. Finally, manually resize the datafile, ensuring to back up data before proceeding with these operations.

Uploaded by

ashutoshkj
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
15 views2 pages

Add Datafile To Oracle

To reclaim space from a fragmented Oracle datafile, first identify free space in the tablespace using specific SQL queries. Then, move objects either to another tablespace or within the same one, and shrink segments if using ASSM. Finally, manually resize the datafile, ensuring to back up data before proceeding with these operations.

Uploaded by

ashutoshkj
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

If a datafile in Oracle is fragmented, you have limited options to shrink it directly.

However,
you can follow these steps to reclaim space efficiently:

1️⃣ Identify Free Space in the Tablespace

Run the following query to check free and used space in the USERS tablespace:

SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb,


maxbytes/1024/1024 AS max_size_mb,
autoextensible
FROM dba_data_files
WHERE tablespace_name = 'USERS';

Check for free space within the tablespace:

SELECT tablespace_name, file_id, block_id, bytes/1024/1024 AS free_mb


FROM dba_free_space
WHERE tablespace_name = 'USERS'
ORDER BY file_id, block_id;

2️⃣ Move Objects to Reclaim Space

Fragmentation occurs when objects are scattered in different file blocks. You can move
objects to defragment space:

A. Move Tables to Another Tablespace (If Possible)

If you have another tablespace, move the tables and then shrink the datafile:

ALTER TABLE my_table MOVE TABLESPACE NEW_TABLESPACE;

After moving, rebuild indexes:

ALTER INDEX my_index REBUILD TABLESPACE NEW_TABLESPACE;

B. Move Tables Within the Same Tablespace

If you don’t want to use another tablespace, move them within the same one:

ALTER TABLE my_table MOVE;

Then rebuild the indexes:

ALTER INDEX my_index REBUILD;

3️⃣ Shrink Segments (If Using ASSM)

If USERS is an Automatic Segment Space Management (ASSM) tablespace, you can shrink
segments:

ALTER TABLE my_table ENABLE ROW MOVEMENT;


ALTER TABLE my_table SHRINK SPACE CASCADE;
4️⃣ Manually Resize the Datafile

After defragmentation, you can reduce the datafile size:

ALTER DATABASE DATAFILE '/path/to/datafile/users01.dbf' RESIZE 500M;

💡 Important Notes:

 Oracle only allows shrinking unallocated free space at the end of the file.
 If fragmentation is severe, consider exporting and importing data (EXPDP/IMPDP).
 Always take a backup before performing these operations.

Let me know if you need further details! 🚀

You might also like