This content has been marked as final. Show 17 replies
I would suggest that you run this script from Tom Kyte mentioned by Uwe,
This would tell you that whether you really can resize the datafile of yours or not! The datafile can't be resized lesser than the HWM of it and that's the message you are getting too.
set verify off column file_name format a50 word_wrapped column smallest format 999,990 heading "Smallest|Size|Poss." column currsize format 999,990 heading "Current|Size" column savings format 999,990 heading "Poss.|Savings" break on report compute sum of savings on report column value new_val blksize select value from v$parameter where name = 'db_block_size' / select file_name, ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest, ceil( blocks*&&blksize/1024/1024) currsize, ceil( blocks*&&blksize/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) / column cmd format a75 word_wrapped select 'alter database datafile '''||file_name||''' resize ' || ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) and ceil( blocks*&&blksize/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
Source of the file:- Re: recover space
as the tablespace is maybe fragmented you have to defragment it to release some space. there are several features to do this more or less efficient.
-alter table/index move
each technologie has pros and cons...
i suggest you to start with shrink space, but don't start with the biggest objects...
Kindly refer the below mention doc and links. how this may help you.
Note: 115586.1 How to Deallocate Unused Space from a Table, Index
Note: 77635.1 How to Determine Real Space used by a Table
I will try this definitely tomorrw and let you know if any of these works..
I got the list of the datafiles, those were afforded to be resized but the tablespace I mentioned above is not there in the list. Actually that tablespace went through a heavy truncate and loading of data. Mainly it is used for archiving tool. This tablespace is still not reduced and huge amount space is lost. When Application truncates table why HMW is not getting reduced? What could be the reason behind not letting me to re-size the datafile.
Out of 90Gb of space only 19 GB is now utilized.
If you have already touched the HWM(High Water Mark) of the used space,you won't be allowed to resize teh datafile beyond that size. The space allocated to the objects within the datafile is not in one go so even if you would delete, it won't ensure that you would get the space given back to you and I think that's what you are seeing right now as well.
Thank you for your help.
I tried shrink space;
first I enable row movement. Then I shrink table space;
Alter table MYSCHEMA.MY_TAB ENABLE ROW MOVEMENT ;
alter table MYSCHEMA.MY_TAB shrink space casacde;
for few tables I got error.
ERROR at line 1:
ORA-10630: Illegal syntax specified with SHRINK clause
*Cause: An illegal option was specified with the SHRINK clause
*Action: Verify the SQL Reference Manual and reissue the command
can you help I am not getting the reason for this.
Instead of casacde it should be cascade
:-P sorry for the spelling mistake.. :(
Thank you for your previous reply.
In another database (for BI) I saw a strange incident. There was a table where 2.5 TB of space was allocated. After so much of huge size we saw no data present there. I understand that could be due to HWM. After I truncated the table I could shrink the segment and by enabling row movement and shrinking the space.
SQL>select bytes from dba_segments where owner='SOME_SCHEMA' and segment_name='SPACEGUILT_TABLE'
The tablespace size 2,975,500M and your script is giving me the result like below:
FILE_NAME Poss. Size Savings
-------------------------------------------------- -------- -------- --------
+file_name ######## ######## 8,682
alter database datafile 'file_name' resize 2966818m;
I saw the main space guilt table is not eating size now. How this amount of size being empty I can't resize my tablespace.? I have space constraint on that server
I should have the elegibility of gaining more than 80% space here.
Neither I can rebuild the table...
ORA-01658: unable to create INITIAL extent for segment in tablespace TBS_ODS_ARCHI2T
Please suggest ASAP.
For ASAP, please raise a Serv1 SR to oracle support. This is a discussion forum and all the threads are of equal importance here.
For your issue, it seems that Seems like that you have touched the limit of file space usage. Use the Segment Advisor and see what other objects can be defragmented to give you some more space.
First of all sorry for writing ASAP :(
Now, coming to your point, I did not get it .. Can you please elaborate it a little? Tablespace usage is 13% now as I described before.. Now, what could be the reason, after shrinking all the segments also I am not able to reduce the tablesapce nor I am able to do alter table <> move ; command.
Please help and would you mind to give me an example please?
SQL> select * from dba_free_space where tablespace_name='FRARDTA9T';
post results from SQL above
SELECT owner, segment_name FROM dba_extents WHERE tablespace_name = 'FRARDTA9T' AND block_id = (SELECT MAX(block_id) FROM dba_extents WHERE tablespace_name = 'FRARDTA9T');
Hello sb92075 ,
The select * from dba_free_space where tablespace_name='MY TABLESPACE' is retruning almost 5.5K records.. as given below.
select count(*) from dba_free_space where tablespace_name='TABLESPACE_NAME'
SQL> SELECT owner,segment_name FROM dba_extents WHERE tablespace_name = TABLESPACE_NAME' AND block_id (SELECT MAX(block_id) FROM dba_extents WHERE tablespace_name = 'TABLESPACE_NAME');