-
Datafile resize
Hi all,
In my ProductionDB i have truncated table which contails 4.7GB.The table present in ISHDATA tablespace with datafiles
'/mnt/db/oracle/oradata/ISPRO/ishdata01.dbf' upto 10 files are there in Ishdata tablespace.
After truncating i tried to resize all the 10 datafiles, to show free space at OS level.(MD wants to see at OS level.)
So i tried as ..
SQL> alter database datafile '/mnt/db/oracle/oradata/ISPRO20/ishdata01.dbf' resize 1900m;
alter database datafile '/mnt/db/oracle/oradata/ISPRO20/ishdata01.dbf' resize 1900m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
...
SQL> alter database datafile '/mnt/db/oracle/oradata/ISPRO20/ishdata10.dbf' resize 4000m;
alter database datafile '/mnt/db/oracle/oradata/ISPRO20/ishdata10.dbf' resize 4000m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
spaces at datafiles reduces at different level ie., for one datafile 250MB released another 1800MB released.
So,please help me on this regard.
Thanks in advance.
-
you have other tables which are at the end of the file so the datafiles cant be resized
-
Query DBA_EXTENTS to find the segments at the high end of the data files.
-
So..,If i want to rezise i have to drop entire tablespace and recreate with new size.So i have to take backup of that tablespace(ishdata).
ok., In no way i can play in production.
Thanks to all.
-
Originally Posted by ams-jamali
So..,If i want to rezise i have to drop entire tablespace and recreate with new size.
Nope.
You have to move offending objects to a different place, resize tablespace, move objects back.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
PAVB, isn't it easyer, after the objects have been moved, just to drop the empty tablespace?
OK, say you do not have to drop it, but generally yes, that's the easyest way
-
Resizing tablespace... can't follow.
I can try as
alter tablespace [tbs] coalesce;
then i can resize datafile (am i right...)
-
Yes, I belive that is what PAVB means
-
Yes, then resize datafile. Sorry, I'd a senior moment.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
It doesn't help me to release even 1GB.
I found free space using dba_data_files & dba_extents it shows only 154MB.
So instead of resizing i left that..,
Thanks to all.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|