Recently as a part of maintenance activity, we observed that users tablespace was consuming 363 GB disk space but when we checked from dba_segments, the actual size of the objects was 3GB only. We tried to resize the datafiles but during resizing datafiles we got the below error.
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
Oracle throw this error because datafiles has reached to the high watermark and some of the objects might be residing to the end of the hwm and you cannot shrink the datafiles below the High Water Mark (HWM).
For example, suppose you created one tablespace of size 120 GB and created two tables inside that tablespace of size 60 GB each. Now the overall size of the tablesapce is 120 GB and we can say that tablespace has reached to the high watermark because it doesn’t contain the data beyound HWM(120GB).
What will happen if you dropped table1 and then trying to resize the datafile?
Inspite of 60 GB free space inside the tablespace, the database doesn’t allow you to resize the datafile because tablespace has reached to its maxsize and still contains data(table2) which is sitting somewhere near to the HWM and it will throw error “file contains used data beyond requested RESIZE value”.
<===============60GB===============120GB>
table1 table2
<=================|=================120GB>
table2
As we can see in users tablespace actual size of the data is 3.5GB only but it is occupying 363 GB of disk space. When we tried to resize the datafiles, it throws the ORA-03297 error because of HWM. To overcome this you must move all objects from users tablesapce to new tablesapce.
You can move all objects to different tablespace in many ways: for example, using the alter table move command or the dbms_redefinition package. But the safest and the easy ways to do so is to use the remap_tablespace using expdp. If tablespace contains clustered objects then you cannot use alter table move command or any other scripts. So the only one option to do so is to use expdp.
Steps to Move objects into different tablespace using EXPDP:
STEP1: Create directory for export dumpfile:
SQL> create or replace directory test_dir as '/data/oracle';
Directory created.
STEP2: Grant read, write on the newly created directory.
SQL> grant read, write on directory test_dir to username;
Grant succeeded.
STES 3: Export all tablesapce objects using expdp.
nohup expdp "/ as sysdba" DIRECTORY=test_dir DUMPFILE=users.dmp LOGFILE=users.log TABLESPACES=USERS &
STEP 4: Import objects to the newly created tablespace using remap_tablespace.
Please note that, you must use table_exists_action=replace otherwise database willl throw error: object already exists and skipped because of default table_exists_action of skip.
nohup impdp "/ as sysdba" DIRECTORY=test_dir DUMPFILE=users.dmp table_exists_action=replace remap_tablespace=USERS:MTNGB1 LOGFILE=users.log &
Finally verify the objects in both the tablesapce and drop the tablespace which was consuming huge space.