Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Garry .

Asked: May 02, 2000 - 1:21 pm UTC

Last updated: November 07, 2013 - 2:56 pm UTC

Version:

Viewed 100K+ times! This question is

You Asked

Deframentation of the SYSTEM TABLESPACE
Presently:
Tspace Status - Size(M) - Used(M)
System - online - 2,622,627 - 58,734
Can the system tablespace be rebuilt with damage to the database offline.



and Tom said...


Ouch -- looks like someone let people create some really big things in system.

There is a chance you can shrink some of the datafiles (see below -- it'll tell you if you can and will generate the alter statements if they apply). We can only shrink files back to their high water mark -- if there is an extent way out at the end of a file -- we'll not be able to shrink it.

If not, the only thing that will undo this is to recreate the database (exp, imp).

----------- maxshrink.sql ----------------------------------

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
/




Rating

  (91 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

The best trick I ever saw

Stelios Karkazis, May 18, 2001 - 4:05 am UTC

This sql script saved my job. I hadn't enough disks, so I decided to run this script and the miracle comes into my eyes.

Very nice !!!


Jiten Padhiar, November 29, 2001 - 8:46 am UTC

I'm not a DBA, but the results proved very useful.

Nice one.

System tablespace grew...

Rory B. Concepcion, January 06, 2003 - 10:25 pm UTC

Hi Tom,

I have a similar problem. My SYSTEM tablespace grew to about 1G. Now I need to resize it into about 200m (coz this is the space it really is occupying). here is the full information.

I have about 15 rollback segments in the RBS tablespace. But the other 10 were neglected to be written in the init.ora so when the database was bounced, only 5 were online and the other 10 offline. When transactions going on in the database and additional rollback segments were needed, it made use of the rollback in the SYSTEM tablespace. Now the SYSTEM tablespace is dictionary managed and it's datafile has autoextend on. So the SYSTEM datafile grew to about 1G. When transactions were finished, we learned about this coz the filesystem the SYSTEM resided on grew and we needed the space. So now I was able to online the other rolback segments and included them in the init.ora. But of course I can't resize the SYSTEM datafile which generated an ora error 3297. When I looked at the tablespace map os SYSTEM, I found out that the "way, way out segments" where the clusters C_TS# and C_FILE#_BLOCK#. But just a few of their blocks where scattered. So, these are the segment blocks preventing me from resizing the SYSTEM tablespace.

Can you think of any workaround for this? I'm not sure if I can recreate those two clusters. Any suggestions would be highly appreciated. Thanks and I'm looking forward to your new book.



Tom Kyte
January 07, 2003 - 6:16 am UTC

that is not what happend. the special SYSTEM rollback is used when you offline files that have active rollback. the rollback is then moved from the normal RBS to the system RBS so we know where it is -- so when the file comes back online -- we can use that undo to fix the file. That is how your system rbs grew.

1gig is trivial. Given that a 36gig drive costs less than $200 usd -- it would be far far cheaper to buy a new drive then spend the time to try and "fix" this.

Short of recreating the ENTIRE database from scratch using export import, you are NOT going to resize that file.

Shrinking system ...

Rory B. COncepcion, January 08, 2003 - 1:08 am UTC

Thanks Tom for the very timely answer. You mean to say that when I have active rollbacks in a rollback segment then it's taken offline, the active rollbacks there would be put to the system rollback? sorry, just verifying on what you meant by offlining file(datafile of what or rollback?).
But thanks again. Good day.

Tom Kyte
January 08, 2003 - 3:11 pm UTC

when you offline a datafile, that has rollback, the rollback is copied into system so when you online the DATAFILE, we can "fix it".

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96524/b_deprec.htm#634866 <code>



HWM in datafile ?

David, January 08, 2003 - 8:34 am UTC

Tom, great article but... sorry, a slight emphasis on concepts:

>We can only shrink files back to their high water mark --
>if there is an extent way out at the end of a file --
>we'll not be able to shrink it.

By definition, the HWM in a SEGMENT is the "highest" location data has EVER reached -- no matter if this piece of data is still there or not -- OK !

However, this is not the case with DATAFILES. If there has ever been an extent at the end of the file, but I has been removed, it doesn't matter -- unlike with segments.

In fact we can shrink a datafile down to the highest extent that "is presently" in it -- not "ever was" in it.

Alternative Solution ?

Johan Snyman, January 08, 2003 - 4:28 pm UTC

Tom,

Would it not be possible (assuming Oracle 8.1.6 or later) to use the transportable tablespace facility to "fix" this ?

I am thinking of something like:

(0) Make a backup.
(1) Perform the required actions to initiate tablespace transportation
(2) Create another database on the same system (preferrably keeping the existing, large system tablespace in place, in case something goes wrong)
(3) Perform the required actions to transport the tablespaces to the new database with the correctly sized system tablespace.
(4) Test
(5) Get rid of the old system tablespace
(6) Backup

Are there any potential problems with this approach ? One thing I am not certain of, is the best way to transfer database user accounts to the "new" database ?


Tom Kyte
January 08, 2003 - 6:30 pm UTC

yes, you can do that - but you'll need to carry over things like

users
roles
public synonyms
grants

yourself.

A Very Useful Stuff

Ashok Shinde, February 18, 2003 - 7:00 am UTC

Tom,

I know this is not the right place to ask the question but I waited for a month so that you clear your backlog but luck was not on my side.

The script is really very useful. But I've additional requirement. I do not know where should I start to solve it myself. I wanted to shrink only those data files whose tablespaces are not accessed by any DDL/DML for last 30 days ?

Please Help.


Tom Kyte
February 18, 2003 - 7:59 am UTC

You would need to enable auditing so you would actually KNOW what tablespaces where accessed by DML in the last 30 days.

DDL would be trickier -- a create, easy to see, a drop -- not so easy to see.

But -- I would ask you this -- this would be a lot of pain for little (in my opinion NO) gain. what is the reasoning behind this?

A Very Useful Stuf

Ashok Shinde, February 19, 2003 - 4:07 am UTC

Tom,
We have a ver large data warehouse having 1000's of tablespaces and 30000 objects used by around 200 developers. Most of the development team is replaced with a new team who does not have complete idea about the calls to all the objects. Our development box is full of disk and each disk is almost full to its capacity. The client is not ready to spend more on the box but wants to have some development. Hence the only alternative to go ahead with new development is to reduce the space occupied by current tablespaces. This has caused me to look at tables/index which are not used for long time and shrink them.


Useful but still confused

Jay Earle, March 25, 2003 - 12:17 pm UTC

Part way down in this thread the Reviewer David seems to contradict what Tom is saying. I am not sure if this is true or not. If the data is removed can the file be shrunk back?


------------------------------------------------

HWM in datafile ? January 08, 2003
Reviewer: David

Tom, great article but... sorry, a slight emphasis on concepts:

>We can only shrink files back to their high water mark --
>if there is an extent way out at the end of a file --
>we'll not be able to shrink it.

By definition, the HWM in a SEGMENT is the "highest" location data has EVER
reached -- no matter if this piece of data is still there or not -- OK !

However, this is not the case with DATAFILES. If there has ever been an extent
at the end of the file, but I has been removed, it doesn't matter -- unlike
with segments.

In fact we can shrink a datafile down to the highest extent that "is presently"
in it -- not "ever was" in it.


Tom Kyte
March 25, 2003 - 12:33 pm UTC

you you get rid of the extent at the end of a file, it (the file) can then be shrunk back -- yes.

maxshrink.sql for temp tablespace

reader, May 03, 2003 - 9:42 am UTC

tom,

i want to run maxshrink.sql for temporary tablespace. - if my temp segments are not deallocated internally ..possible?

if so what changes i shud do in the script.

thanks

RMAN & imp/exp

Sikandar Hayat, May 23, 2004 - 1:06 am UTC

We can also use exp/imp to remove fragmentation but trying to get info if we can get the same from RMAN?

Tom Kyte
May 23, 2004 - 9:26 am UTC

alter table T move;
alter index I rebuild;

dbms_redef

are the ways to do it. RMAN just backs up datafiles as they are.
exp/imp are dangerous ways to reorg things.

Just to confirm

Yogesh, July 28, 2004 - 5:05 am UTC

I have 25 data files in one TBS ... some of these were added at the time of rebuilding the table ... Now the space available for this TBS is surplus by 1G and I want to reclaim the space.

So after using the script for shrinking the datafiles .. I got following results

alter database datafile '/oracle/data/data10' resize 281m;
alter database datafile '/oracle/data/data11' resize 272m;
alter database datafile '/oracle/data/data12' resize 290m;

actual size of all three files is 700m

Now, instead of using above commands to resize them to some value like 290, 272,281 .. I want to round it to 350.

alter database datafile '/oracle/data/data10' resize 350m;
alter database datafile '/oracle/data/data11' resize 350m;
alter database datafile '/oracle/data/data12' resize 350m;

If I do so i.e. resize the file with higher value than HWM, I think it should be fine.

Please confirm.


Tom Kyte
July 28, 2004 - 8:25 am UTC

yes, it will "be fine"

Drop table

Yogesh, September 07, 2004 - 9:36 am UTC

I've dropped one table (900Mb) from one TBS, above script should allow me to shrink some file from that TBS by 900 Mb. But it is not.

I'm getting following results

Smallest
Size Current Poss.
FILE_NAME Poss. Size Savings
-------------------------------------------------- -------- -------- --------
/oracle/c1/data/data4/pl_3.dbf1 674 675 1
/oracle/c1/data/data4/pl_3.dbf2 675 675 0
/oracle/c1/data/data4/pl_3.dbf3 672 675 3
/oracle/c1/data/data4/pl_3.dbf4 688 690 2
/oracle/c1/data/data6/pl_3.dbf5 459 460 1
/oracle/c1/data/data5/pl_3.dbf6 175 175 0
/oracle/c1/data/data1/pl_3.dbf7 407 410 3
/oracle/c1/data/index8/pl_3.dbf8 50 50 0
/oracle/c1/archive/pl_3.dbf9 5 50 45
/oracle/c1/archive/pl_3.dbf10 1 50 49
--------
sum 104

What could be the reason?

Oracle 8.0

Tom Kyte
September 07, 2004 - 10:06 am UTC

think of your table T as being T in the following.

think of other tables as being "X"
think of free space as being "f"

Here is your file:


XXXXXXffTTTTTXXXXXXXXXfffTTTTXXXTTTTf

so, there is some data xxxxx in the front of of the file, then a bit of free space, then your table T, some more stuff, a little more free, your table t again, some more stuff, your table t again and then a tiny bit of free space.

You drop table t. your file now looks like:

XXXXXXfffffffXXXXXXXXXfffffffXXXfffff

you have some stuff, lots of free, some stuff, lots of free, some stuff, little bit of free.

the MAX that file can shrink to will be:

XXXXXXfffffffXXXXXXXXXfffffffXXX

because you have "some stuff" out there. The interior free space is part and parcel of that file.

You'd have to query (using dba_extents) "what is the some stuff that is in the way" and decide if you

a) want to reorg, move that object to another tablespace
b) leave well enough along, you have the freespace and can easily use it in this database.



Great explaination

Yogesh, September 07, 2004 - 10:19 am UTC

Thanks for explaination.

Now the problem is oracle 8.0. I can't alter TBS so I've to create backup tables in some other TBS drop from original again recreate them in original. Or may be imp/exp .. but its very time consuming ... Any other better way ?



Tom Kyte
September 07, 2004 - 11:58 am UTC

why bother putting them back? what matter the tablespace name???

Reducing the tablespace

Jeff, September 22, 2004 - 12:15 pm UTC

Hi Tom,

I am using Oracle 9i with LMT. However one of the tablespace, USERS, is sized on a higher side and it is used only 20%. I just want to confirm the steps to resize.

1. create new tablespace, ts_temp;
2. alter table T move tablespace ts_temp;
alter index I rebuild tablespace ts_temp;
3. drop all objects from USERS and resize (shrink) it.
4. move back the objects from ts_temp back to USERS.
5. drop tablespace ts_temp.

Please let me know if there is any alternate method or if anything is wrong/missed in the above steps.

I also need to do the same with UNDO tablespace. Will the steps be the same? But how come it shows that it is 45% used? Shouldn't it be empty all the time?

Thanks in advance.

Tom Kyte
September 22, 2004 - 1:07 pm UTC

why steps 4 and 5? they seem to be such a waste of time, energy and resources.

why bother with step 2, part 2 if you are just going to move them back


I would do 1, 2. and drop users. done.


UNDO shouldn't show "empty", it allocates extents and keeps them to satisfy your undo retention and transaction rates.


to downsize undo

a) create a new one
b) alter system to use it
c) after "undo retention" period of time (and seeing that all active transactions are complete) you can drop old.

Jeff, September 22, 2004 - 2:35 pm UTC

Tom,

Thanks for your answer.

I need to keep USERS ts because users are created with default tablespace as USERS. Is there a way to change it globally? Or do I have to alter all the users to assign the new ts to them?

Tom Kyte
September 22, 2004 - 3:03 pm UTC

alter each user and change them.


select 'alter user ' || username || ' default tablespace new_users;'
from dba_users
where default_tablespace = 'USERS';

spool it, edit it, run it.

Identifying the object

Yogesh, October 12, 2004 - 5:18 am UTC

As mentioned in above reply, if following is the pattern of the datafile

XXXXXffTTTTTXXXXXXXXXfffTTTTXXXTTTTf

Is there any way to identify the TÂ’s? I mean Can we identify the tables at the end of datafile? If we can identify these objects we can rebuild them in some other table space and free more space.


Tom Kyte
October 12, 2004 - 8:21 am UTC

sure, query dba_extents for the maximum block id by file.

look at those objects -- they are the ones "at the end"

A reader, October 12, 2004 - 8:42 am UTC


phil, October 13, 2004 - 9:54 pm UTC


alter users?

chet, November 16, 2004 - 5:52 pm UTC

Is this an acceptable method for moving the users?

BEGIN
FOR i IN (SELECT username FROM dba_users WHERE default_tablespace = 'USERS') LOOP
EXECUTE IMMEDIATE 'ALTER USER ' || i.username || ' DEFAULT TABLESPACE new_users';
END LOOP;
END;
/


Tom Kyte
November 16, 2004 - 11:37 pm UTC

that won't "move" anything, it'll just change the default tablespace for users.

not sure I would do it for sys/system and others (oracle accounts)

Query to dba_extents

A reader, January 19, 2005 - 8:56 am UTC

Tom,

Can you tell us the query to identify the objects in a tablespace "at the end" ?

Thanks.

Tom Kyte
January 19, 2005 - 10:54 am UTC

search this site for

maxshrink


you can modify that to get the "end", or

</code> https://www.oracle.com/technetwork/issue-archive/2014/14-sep/o54asktom-2279184.html <code>

"whats at the end"

I am unable to run the alter database datafile comand

Sairam, March 04, 2006 - 6:50 am UTC

Hi Tom,

After running the ur script, result as below.

alter database datafile '/ss02/prod/proddata1/applsysd01.dbf' resize 26m;
alter database datafile '/ss02/prod/proddata/gld01.dbf' resize 1m;
alter database datafile '/ss01/prod/proddata/invx01.dbf' resize 14m;
alter database datafile '/ss02/prod/proddata1/undo01.dbf' resize 3m;
alter database datafile '/ss02/prod/proddata/usrd01.dbf' resize 2m;
alter database datafile '/ss02/prod/proddata/emsprod.dbf' resize 1m;

But when I am executing these statements oracle is giving error like "ORA-03297: file contains used data beyond requested RESIZE value"

Its very critical issue for me, Mainly I need to reduce invx01.dbf data file, which is occupying 15GB but data occupied around 4GB. I m unable reduce this data file.

As per ur discussions, we have solution, i.e. we can transfer data from this particular table space to other and again transfer to original one.

But my problem is this is INVX (index tablespace) of 45GB and I canÂ’t shutdown the PRODUCTION system, because its 24/7 support.

If I do online, it takes more time as well as performance of system will reduce.

Please reply me with accurate solution.

Thanks in advance
sairam





Tom Kyte
March 04, 2006 - 7:13 am UTC

show me a cut and paste of my script producing the 14m resize (which doesn't seem reasonable does it - if you say it has 4gig)



Particular Datafile Deletion

Sairam, March 05, 2006 - 2:38 am UTC

Thank you for your speedy response.

When I am executing following query,

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

I got the output of the query as follows.

alter database datafile '/ss02/prod/proddata1/applsysd01.dbf' resize 26m;
alter database datafile '/ss02/prod/proddata/gld01.dbf' resize 1m;
alter database datafile '/ss01/prod/proddata/invx01.dbf' resize 14m;
alter database datafile '/ss02/prod/proddata1/undo01.dbf' resize 3m;
alter database datafile '/ss02/prod/proddata/usrd01.dbf' resize 2m;
alter database datafile '/ss02/prod/proddata/emsprod.dbf' resize 1m;.

And one more thing I want to confirm is,

For the same scenario, I followed following method. Please confirm this is right method or not.

I have done cloning of Production instance in Mirror instance.

My aim is to decrease the invx01.dbf data file size.
That is, invx01.dbf occupied 15GB and I want to decrease to 4GB, (Because actual occupied data is 4GB). I followed following steps.


1. I identified objects exiting in invx01.dbf datafile by following query.

Query is:

SELECT
owner,
segment_name,
segment_type,
a.relative_fno,
b.file_name
FROM dba_Segments a,
dba_data_files b
WHERE b.file_id=a.relative_fno
AND segment_type='INDEX' AND owner='INV' AND
b.file_name='/ss01/prod/proddata/invx01.dbf'

Output is:

MTL_MATERIAL_TRANS_TEMP_N18
MTL_MATERIAL_TRANS_TEMP_N16
MTL_MATERIAL_TRANS_TEMP_N2
MTL_MATERIAL_TRANS_TEMP_N14
MTL_MATERIAL_TRANS_TEMP_N12
MTL_MATERIAL_TRANS_TEMP_N19
MTL_MATERIAL_TRANS_TEMP_N10
MTL_SYSTEM_ITEMS_B_N3
MTL_SYSTEM_ITEMS_B_U1
MTL_SYSTEM_ITEMS_B_N4
MTL_SYSTEM_ITEMS_B_N8
MTL_SYSTEM_ITEMS_B_N9
MTL_SYSTEM_ITEMS_B_N6
MTL_SYSTEM_ITEMS_B_N7
MTL_SYSTEM_ITEMS_B_N10

2. Created another tablespace ABC with two datafiles
abc1.dbf - 2GB
abc2.dbf - 2GB

3. Moved all the above objects (invx01.dbf objects) to ABC tablespace, after confirmation of moving , issued following command

alter database datafile '/ss01/prod/proddata/invx01.dbf' offline drop;

Manually droped the datafile from OS level.
Now I confirmed there is space available after deleting invx01.dbf by issuing df –kh (Solaris command)

4. Added two more datafiles to INVX tablespace.
invx14.dbf - 2GB
invx15.dbf - 2GB


5. Finally moved all objects from ABC tablespace to INVX tablespace.
Like
alter index MTL_SYSTEM_ITEMS_B_N10 rebuild storage (initial 256M next 256M pctincrease 0) parallel 4 nologging tablespace INVX;

for all objects I have done like that.

Now I aim is achieved.

But If monitor through Oracle Enterprise Manager (OEM)
I am finding invx01.dbf entry in INVX tablespace, which is offline. And I want to remove this entry, What is method for removing entry.

Please , confirm this is right method or not.

Thanks in advance.

Sairam















Tom Kyte
March 05, 2006 - 1:43 pm UTC

sorry - but where did the "it has 4gig of data" come from then?

my script would NOT possibly come up with 14m if there were 4gig of data.



Particular Datafile Removing

Sairam, March 06, 2006 - 1:24 am UTC

HI Tom,

As per your query, we are getting the result as follows.

Query:

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 A.TABLESPACE_NAME='INVX'
AND CEIL( blocks*&blksize/1024/1024) -
CEIL( (NVL(hwm,1)*&blksize)/1024/1024 ) > 0

Output:
alter database datafile '/ss01/prod/proddata/invx01.dbf' resize 14m;


You can see the following output as a proof of total_space and free_space occupied by particular invx01.dbf file.

SELECT SUM(df.bytes) TOTAL_SPACE,SUM(fs.bytes) FREE_SPACE
FROM dba_free_space fs, dba_data_files df
WHERE df.TABLESPACE_NAME='INVX'
AND df.file_name='/ss01/prod/proddata/invx01.dbf'

TOTAL_SPACE FREE_SPACE
11,673,600,000,000.00 46,277,976,064.00


I donÂ’t know why its coming like that.

And one more thing I want to confirm what the process (plz see my previous conversation) I followed for removing invx01.dbf is it right method or not.

Please confirm this, So I can proceed further in Production Instance.

Thanks in advance,
Sairam



Tom Kyte
March 06, 2006 - 8:37 am UTC

your query that joins dba_free_space to dba_data_files is slightly WRONG, well, not slightly - very wrong.

dba_free_space - has an entry for each set of contigous free extents.
dba_data_files has an entry for each file.


Say you have 50 free extents
Say you have 1 data file.

You'll have multiplied the bytes in the datafile 50 times!!!!!!!!!!!

Rethink your query. If you are interested, use this instead:
</code> http://asktom.oracle.com/Misc/free.html <code>


plz? what do German Postal codes have to do with this?

PLZ

Marcio Portes, March 07, 2006 - 4:01 pm UTC

Had to look it up ;)

ZIP Used in: Germany, Austria, Switzerland Postleitzahl
</code> http://www.top500.de/g0039909.htm

In German PLZ is the abbreviation for "Postleitzahl", which is the German word for "zip code".
http://en.wikipedia.org/wiki/Plz <code>


Tom Kyte
March 09, 2006 - 11:18 am UTC

exactly - it is a german postal code :) Don't know what possible meaning it could really have to the question at hand however, but it comes up often it seems.

Particular Datafile Removing

Sairam, March 08, 2006 - 1:40 am UTC

HI Tom,

I am not getting your solution.
When I am executing your query, I am getting 14m regarding invx01.dbf. But if you see through OEM and executing scripts I am getting following statistics,

Total space --15GB
used space -- around 7GB.

But your not clarified my method of deleting particular datafile.

Please clarify TOM.

Thanks & Regards,
Sairam




Tom Kyte
March 09, 2006 - 12:11 pm UTC

you have not clarified for ME first. Your query is horribly wrong, I don't know what stats you are getting from OEM.

I can say

If my script says "file can be this size", then the file can be that size.

so, run my script (exactly as is, no modifications)
cut and paste the output
and show me the alter statement failing (cut and paste)

script needs updating to allow varying block sizes

Mark Jaeger, March 16, 2006 - 1:35 pm UTC

Hello Tom,

This script needs to be updated.
It does not take into account the fact
that different datafiles might have different
block sizes. It assumes that fetching the value
of the single init.ora parameter is sufficient,
which it is not.

You probably have to join DBA_DATA_FILES
with DBA_TABLESPACES to get the BLOCK_SIZE column
that applies to each data file.

Regards,

--Mark Jaeger


Tom Kyte
March 16, 2006 - 2:52 pm UTC

umm, no, it does this:


column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/


I get the block size, where do you see it using a fixed size?

Tablespaces with heterogenous block sizes

A reader, March 19, 2006 - 8:20 pm UTC

I think Mark was saying that you need to substitute
&blksize in the script at the top of this page with dba_tablespaces.block_size or v$datafile.block_size
in case a database has tablespaces with different
block sizes (and using the db_nK_cache_size parameters
to accomodate that fact).

Tom Kyte
March 20, 2006 - 7:06 am UTC

ahh, indeed, good point. never really use that feature since it is truly only useful for transports (attach data, reformat data into the real tablespaces, detach it).

but yes - correct. I see that now

Block Re-organization Within a Data File

Su Baba, August 23, 2006 - 4:37 pm UTC

Do you have a script that would do a block re-organization so that the free blocks get pushed to the end of the file?

X - used blocks
f - free blocks

Befeore:
XXXXXXfffffffXXXXXXXXXfffffffXXX

After
XXXXXXXXXXXXXXXXXXffffffffffffff



Tom Kyte
August 27, 2006 - 6:51 pm UTC

not really, the only thing you can do is:

a) find the thing at the "end" (the last X).  that is pretty easy.
https://www.oracle.com/technetwork/issue-archive/2014/14-sep/o54asktom-2279184.html
(at the end of a file)

b) alter that segment and move/rebuild it.  IF you have a locally managed tablespace, they tend to move to the front of the files.


But, beware, it could actually make the file GROW.  Using your "before", if you had a single segment in that file - which has 32 extents (14 free, 18 used)

ops$tkyte%ORA10GR2> select :v,
  2         length(:v) extents,
  3         length(replace(:v,'X','')) free,
  4             length(replace(:v,'f','')) used
  5    from dual;

:V
-------------------------------------------------------------------------------
   EXTENTS       FREE       USED
---------- ---------- ----------
XXXXXXfffffffXXXXXXXXXfffffffXXX
        32         14         18


You would/could end up with:
original: XXXXXXfffffffXXXXXXXXXfffffffXXX
after:    ffffffXXXXXXXfffffffffXXXXXXXfffXXXX

because the move could require 2x (or more even) the space temporarily as it was doing the move. 

Su Baba, August 28, 2006 - 12:26 pm UTC

Would moving data blocks to a different tablespace and then move them back again be a valid approach for the above problem?

Tom Kyte
August 28, 2006 - 12:32 pm UTC

but why move them BACK!!!!?????

just move them out and drop the empty tablespace if that is your goal (to minimize for a couple of minutes the amount of space you are using - remember, it is just going to happen all over again right)

Su Baba, August 28, 2006 - 12:55 pm UTC

To keep the original tablespace name. Is there anyway to rename a tablespace?

Tom Kyte
August 28, 2006 - 1:03 pm UTC

in current releases of the software, sure (to help further support transporatable tablespaces)

ops$tkyte%ORA10GR2> alter tablespace x_8m rename to abc;
 
Tablespace altered.


but given that nothing should really be "referencing" the tablespace name - not sure if I agree with the need (at two times the work) 

Reducing datafiles of SYSTEM tablespace

abz, September 18, 2006 - 7:00 am UTC

I have a SYSTEM tablespace with 4 datafiles, each of 500MB.
The total size occupied by the SYSTEM tablespace is
200MB. What I want is to make on file of 500MB only and delete the other 3 files. How can I do it, can ALTER TABLE ... MOVE TABLESPACE.... will be usefuil here.

Thanks

Tom Kyte
September 18, 2006 - 1:26 pm UTC

not going to happen.

you do not do things like that to SYS owned objects, not permitted.

any workaround?

abz, September 19, 2006 - 8:01 am UTC

Ok if there is no proper way to do it, can you please
suggest any work around/tactic to achieve the same
effect.

Tom Kyte
September 19, 2006 - 2:34 pm UTC

short of rebuild the database, you "live with it". You can shrink each of the files to their minimum size but that is about it.

10g and Shrink Script

Richard, February 22, 2007 - 11:55 am UTC

The datafile shrink script is great, but can lead to a bit of confusion if the Recycle Bin is enabled.

I found that the script reported say 123 MB of space that could be reclaimed, but the
alter database datafile '/blah.dbf' resize 125952;
statements failed for some datafiles. The reason was that there were BIN$ (Recycle Bin) objects in those tablespaces.

Just a caveat for the new age of 10g!!!
Tom Kyte
February 22, 2007 - 11:56 am UTC

indeed!

Shrinking below HWM

Yogesh Purabiya, September 22, 2007 - 6:58 am UTC

If the HWM is at 100 MB and we give the command like

ALTER DATABASE DATAFILE 'D:\ORACLE\...\SYSTEM01.DBF' resize 50m;

will it do resizing or give error message ?

By the way, how do we find (locate) the Sql-Reference manual on-line ?
Tom Kyte
September 26, 2007 - 1:24 pm UTC

because there is some extent that exists above 50m....

otn.oracle.com -> documentation.

alter table move

butterfly, October 10, 2007 - 12:29 pm UTC

"b) alter that segment and move/rebuild it. IF you have a locally managed tablespace, they tend to move to the front of the files."


But, beware, it could actually make the file GROW. Using your "before", if you had a single
segment in that file - which has 32 extents (14 free, 18 used)

You would/could end up with:
original: XXXXXXfffffffXXXXXXXXXfffffffXXX
after: ffffffXXXXXXXfffffffffXXXXXXXfffXXXX

because the move could require 2x (or more even) the space temporarily as it was doing the move.


Hi Tom,
Could you explain why
"they tend to move to the front of the files." ?

Also, i would like to understand the difference between
alter table t move same_tbs
and
alter table t move new_tbs

Does Oracle realize the same operation ? Where does he takes temporary space in the datafile ?

Thanks a lot.



Ricardinho, June 09, 2008 - 2:59 pm UTC

Hi Tom
Assume this scenerio
xxxfffxxxxxfx

after shrink does it become like

xxxxxxxxxxffff
or
xxxfffxxxxxxxf

I always confuse alter table move and shrink space commands in this scenerio, do they perform same in above

Tom Kyte
June 09, 2008 - 3:46 pm UTC

think of shrink space compact as if it:

a) read the table from the bottom up
b) when it hits a row, it deletes it and does a positioned insert of it at the "top" of the table - in the first free slot it finds.
c) it keeps doing that until the first free slot it finds is the one the row was on in the first place (eg: there is no more free space above that row)

so, it would end up like xxxxxxxxxxxxxxffffffff

ops$tkyte%ORA10GR2> create table t
  2  enable row movement
  3  as
  4  select rownum id, rpad('*',1000,'*') data
  5    from all_users
  6  /

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> delete from t where mod(id,2) = 0;

20 rows deleted.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> select id, dbms_rowid.rowid_block_number(rowid) from t;

        ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ------------------------------------
         1                                   52
         3                                   52
         5                                   52
         7                                   52
         9                                   53
        11                                   53
        13                                   53
        15                                   54
        17                                   54
        19                                   54
        21                                   54
        23                                   55
        25                                   55
        27                                   55
        29                                   56
        31                                   56
        33                                   56
        35                                   56
        37                                 3217
        39                                 3217

20 rows selected.

ops$tkyte%ORA10GR2> alter table t shrink space compact;

Table altered.

ops$tkyte%ORA10GR2> select id, dbms_rowid.rowid_block_number(rowid) from t;

        ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ------------------------------------
         1                                   52
        37                                   52
         3                                   52
        39                                   52
         5                                   52
        29                                   52
         7                                   52
        31                                   53
         9                                   53
        33                                   53
        11                                   53
        35                                   53
        13                                   53
        23                                   53
        15                                   54
        25                                   54
        17                                   54
        27                                   54
        19                                   54
        21                                   54

20 rows selected.




Ricardinho, June 10, 2008 - 2:59 pm UTC

thanks a lot for this nice explanation Tom.
Thats really helpful,
My last question:
Could you explain alter table move and shrink commands by figure

Original tablespace mapping:

A-----B--B-C-----

After perform a shrink statement on table B, Does the tablespace mapping will look like:

A-----BB---C-----

or

ABB--------C-----

Tom Kyte
June 10, 2008 - 3:25 pm UTC

shrink does not move extents.

shrink space compact moves all rows to top of table.
shrink space releases extents that do not contain data.

so, if this was the "before"

A-----B--B-C-----


shrinking B might leave:

A-----B----C-----



shrink does not MOVE EXTENTS
shrink de-allocates existing extents.

Problems shrinking down empty files

Paul Robinson, March 26, 2009 - 9:30 am UTC

Tom, 

Thanks for the script I find it very useful, I however have a small query in regards to its use.  I'm using it to shrink down empty tablespaces which are reused for monthly partitioning.

When I use the script it shows the datafile can be shrunk down to 1mb.  The tablespace is locally managed and is using 1mb uniform extents.  It however won't let me shrink the datafile down that small.  The smallest I can get it is 1088KB.  I'm guessing the extra 64KB is for bitmap and header information? Please correct me if i'm wrong.

So the smallest a datafile can be is the initial extent size + 64K?

I did a test on my test system to show what I mean:-

sys@ORCL> create tablespace TEST
  2    logging
  3    datafile 'C:\ORADATA\TEST01.DBF'
  4    size 100m
  5    extent management local uniform size 1M
  6    segment space management auto;

Tablespace created.

sys@ORCL> drop tablespace TEST;

Tablespace dropped.

sys@ORCL> create tablespace TEST
  2    logging
  3    datafile 'C:\ORADATA\ORCL\TEST01.DBF'
  4    size 100m
  5    extent management local uniform size 1M
  6    segment space management auto;

Tablespace created.

sys@ORCL> set verify off
sys@ORCL> column file_name format a50 word_wrapped
sys@ORCL> column smallest format 999,990 heading "Smallest|Size|Poss."
sys@ORCL> column currsize format 999,990 heading "Current|Size"
sys@ORCL> column savings  format 999,990 heading "Poss.|Savings"
sys@ORCL> break on report
sys@ORCL> compute sum of savings on report
sys@ORCL>
sys@ORCL> column value new_val blksize
sys@ORCL> select value from v$parameter where name = 'db_block_size'
  2  /

VALUE
---------------------------------------------------------------------------------------
8192

sys@ORCL>
sys@ORCL> select file_name,
  2         ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
  3         ceil( blocks*&&blksize/1024/1024) currsize,
  4         ceil( blocks*&&blksize/1024/1024) -
  5         ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
  6  from dba_data_files a,
  7       ( select file_id, max(block_id+blocks-1) hwm
  8           from dba_extents
  9          group by file_id ) b
 10  where a.file_id = b.file_id(+)
 11  /

                                                   Smallest
                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
-------------------------------------------------- -------- -------- --------
C:\ORADATA\ORCL\SYSTEM01.DBF                            484    1,000      516
C:\ORADATA\ORCL\UNDOTBS01.DBF                            20       25        5
C:\ORADATA\ORCL\USERS01.DBF                               1        5        4
C:\ORADATA\ORCL\SYSAUX01.DBF                            274      280        6
C:\ORADATA\ORCL\TEST01.DBF                                1      100       99
                                                                     --------
sum                                                                       630

sys@ORCL>
sys@ORCL> column cmd format a75 word_wrapped
sys@ORCL>
sys@ORCL> select 'alter database datafile '''||file_name||''' resize ' ||
  2         ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
  3  from dba_data_files a,
  4       ( select file_id, max(block_id+blocks-1) hwm
  5           from dba_extents
  6          group by file_id ) b
  7  where a.file_id = b.file_id(+)
  8    and ceil( blocks*&&blksize/1024/1024) -
  9        ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
 10  /

CMD
---------------------------------------------------------------------------
alter database datafile 'C:\ORADATA\ORCL\SYSTEM01.DBF' resize 484m;
alter database datafile 'C:\ORADATA\ORCL\UNDOTBS01.DBF' resize 20m;
alter database datafile 'C:\ORADATA\ORCL\USERS01.DBF' resize 1m;
alter database datafile 'C:\ORADATA\ORCL\SYSAUX01.DBF' resize 274m;
alter database datafile 'C:\ORADATA\ORCL\TEST01.DBF' resize 1m;

sys@ORCL> alter database datafile 'C:\ORADATA\ORCL\TEST01.DBF' resize 1m;
alter database datafile 'C:\ORADATA\ORCL\TEST01.DBF' resize 1m
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required

sys@ORCL>  alter database datafile 'C:\ORADATA\ORCL\TEST01.DBF' resize 1088k;

Database altered.

sys@ORCL>

I'm new to oracle and databases so please be gentle.

Tom Kyte
March 30, 2009 - 2:54 pm UTC

... So the smallest a datafile can be is the initial extent size + 64K? ...

for a locally managed tablespace - yes. guess I need to update my script to accommodate such a situation... an empty tablespace.

Affect of table shrink across datafiles

schakro, May 04, 2009 - 4:06 pm UTC

Tom,

Thanks for explaining change in tablespace mapping after a shrink command (in response to Ricardinho just above). You had mentioned
<quote>
so, if this was the "before"
A-----B--B-C-----
shrinking B might leave:
A-----B----C-----
</unquote>

My question is related to datafile mapping.
So if before shrink table B, datafiles are like this:
DATAFILE_1 A----B--B-------
DATAFILE_2 BB----BB----PP--
DATAFILE_3 Q----B---B------

What would be the mapping after shrinking table B? Will it be:
DATAFILE_1 A----BB---------
DATAFILE_2 BBBB--------PP--
DATAFILE_3 Q----BB---------

or will it be:
DATAFILE_1 A----BBBBBBBB---
DATAFILE_2 ------------PP--
DATAFILE_3 Q---------------

I am trying to find out till what point would I be able to reduce the size of datafile_3 using resize.

Thanks a lot for your help.

Tom Kyte
May 04, 2009 - 5:44 pm UTC

the shrink would not move an extent - so neither of them would apply.

shrink space compact will move rows from the "bottom of the table to the top" and then shrink space will deallocate extents that are now "empty"

Re: Affect of table shrink across datafiles

schakro, May 07, 2009 - 5:36 pm UTC

Tom,

Thanks for your reply. I am sorry but I didnt understand it fully.
So if both my mappings are wrong, what would be the correct mapping?
I think I understand it "will move rows from the bottom of the table to the top and then shrink space will deallocate extents that are now empty", but whats actually going to happen at the datafile level? How will the table be stored at the datafile level? I am trying to understand how much space I will be able to release from a datafile after a shrink space.
Thanks in advance.

Tom Kyte
May 11, 2009 - 2:12 pm UTC

... I am trying to understand how much space I will be able to
release from a datafile after a shrink space.
....

impossible to KNOW, until you do it.


extents are allocated in some order - in some location - in some datafile. The first extent of some segment might be "at the end" of the datafile. The next extent might be at the "front" of the datafile. The third extent might be in the MIDDLE of the datafile.

consider this (very hand crafted) example:


ops$tkyte%ORA10GR2> create tablespace testing
  2  datafile size 640k
  3  extent management local uniform size 64k;

Tablespace created.

<b>that tablespace has room for 9 extents of 64k (the first extent is used by us to manage the extents in the datafile itself)</b>


ops$tkyte%ORA10GR2> select blocks, count(*) from dba_free_space where tablespace_name = 'TESTING' group by blocks;

    BLOCKS   COUNT(*)
---------- ----------
        72          1

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2          for i in 1..8
  3          loop
  4                  execute immediate 'create table testing_' || i || '(x int) tablespace testing';
  5          end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

<b>that will allocate extents in the datafile from the "front" to the "back" - you can query dba_extents to verify if you like... testing_1 is at the "front" of the file, "testing_8" is at the end and "testing_4" is in the middle</b>


ops$tkyte%ORA10GR2> create table testing ( x number, y varchar2(4000) default rpad('*',4000,'*'), z varchar2(4000) default rpad('*',3000,'*') ) tablespace testing ;

Table created.

<b>and testing is dead last, it occupies the LAST extent in this datafile, it is the "most far out there" extent.  And that is the FIRST extent of testing</b>

ops$tkyte%ORA10GR2> create or replace procedure fill_testing
  2  as
  3          unable_to_extend exception;
  4          pragma exception_init( unable_to_extend, -1653 );
  5  begin
  6          for i in 1 .. 10
  7          loop
  8                  begin
  9                          insert into testing(x) values(s.nextval);
 10                          commit;
 11                  exception
 12                  when unable_to_extend
 13                  then
 14                          dbms_output.put_line( 'put ' || (i-1) || ' rows in' );
 15                          exit;
 16                  end;
 17          end loop;
 18  end;
 19  /

Procedure created.

<b>a routine to put as many rows into testing  as possible, if it can extend - it will, it will STOP when it cannot fit anymore data...</b>

ops$tkyte%ORA10GR2> select blocks, count(*) from dba_free_space where tablespace_name = 'TESTING' group by blocks;

no rows selected

<b>we are full, no extents can be allocated here...</b>

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec fill_testing;
put 5 rows in

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select extent_id, block_id, blocks from dba_extents where segment_name = 'TESTING' order by extent_id;

 EXTENT_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         0         73          8

<b>the first extent is out there - starts at block 73 of the datafile...</b>


ops$tkyte%ORA10GR2> drop table testing_1;

Table dropped.

ops$tkyte%ORA10GR2> exec fill_testing;
put 8 rows in

PL/SQL procedure successfully completed.

<b>we can put in more because testing can allocate a next extent now, the extent formally used by testing_1 - that extent we know is at the "front" of the datafile - we can see that:</b>

ops$tkyte%ORA10GR2> select extent_id, block_id, blocks from dba_extents where segment_name = 'TESTING' order by extent_id;

 EXTENT_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         0         73          8
         1          9          8

<b>testing's first extent starts at block 73, second at block 9 and now...</b>

ops$tkyte%ORA10GR2> drop table testing_4;

Table dropped.

ops$tkyte%ORA10GR2> exec fill_testing;
put 7 rows in

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select extent_id, block_id, blocks from dba_extents where segment_name = 'TESTING' order by extent_id;

 EXTENT_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         0         73          8
         1          9          8
         2         33          8

<b>the third extent of that table starts dead in the "middle" of the datafile</b>

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> delete from testing where mod(x,2) = 0;

10 rows deleted.

ops$tkyte%ORA10GR2> commit;

Commit complete.

<b>clear out 50% of the rows...</b>

ops$tkyte%ORA10GR2> alter table testing enable row movement;

Table altered.

ops$tkyte%ORA10GR2> alter table testing shrink space compact;

Table altered.

ops$tkyte%ORA10GR2> alter table testing shrink space;

Table altered.

<b>"reorg" by moving rows from the BOTTOM (extent in the MIDDLE of the table) to the TOP (extent at the END of the datafile)</b>


ops$tkyte%ORA10GR2> select extent_id, block_id, blocks from dba_extents where segment_name = 'TESTING' order by extent_id;

 EXTENT_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         0         73          8
         1          9          8

<b>and you can see - we are left with the first two extents - we got rid of an extent in the middle of the datafile</b>





So, you'd have to look at DBA_EXTENTS for a segment you are going to shrink order by extent_id DESC, that is the order in which extents will be deallocated - if the extents that are deallocated are not AT THE END of the file - you won't be able to resize the file smaller....

If they are at the end - you will be able to.

Great

schakro, May 13, 2009 - 5:34 pm UTC

Thanks a lot Tom, this is great. Makes it very clear now.
One more thing, I would think that this would work across datafiles also. So using your example,before the shrink, if the last extent was in datafile_2 (first and second extents in datafile_1 and we have free space in datafile_1 now, where the last extent can be moved to) then after the shrink, the last extent from datafile_2 would be moved to datafile_1. Is this correct?
Thanks again.
Tom Kyte
May 13, 2009 - 5:43 pm UTC

... the last
extent from datafile_2 would be moved to datafile_1. ...

I keep trying to say "no extents shall be moved". No, no extents move in a shrink.

we move rows from the last extents allocated into the first extents allocated.

then, if we empty out an extent (one of the last extents) we can free it. If we empty out most of an extent - and we are using system allocated extents - we can trim it.

But an extent will never move from file 1 to file 2.

Shrinking datafile Vs Alter table ..move

Harschil, May 14, 2009 - 10:49 pm UTC

Hi Tom,
I aim to - lower the HWM in table T .
Can be done
either
a) Alter table T move..
or
b) resize the datafile where table T resides.

Can you please compare approach (a) and (b)?

Regards
Harschil
Tom Kyte
May 15, 2009 - 1:33 pm UTC

describe how/why you think (b) would do anything to the extents allocated to table T?


to reduce the high water mark of an existing table you may:

a) alter table t move - followed by alter index i rebuild for every index on t

b) alter table t shrink space compact; alter table t shrink space; Online reorg - no index rebuild needed. Must be using ASSM (automatic segment space management)

c) dbms_redefinition - online reorg tool

d) create table t_new as select... index/grant/constrain/etc - drop table t; rename t_new to t;


Shrinking datafile Vs Alter table ..move ..

Harschil, May 16, 2009 - 2:44 am UTC

Many thanks Tom.

I understood it now( after you asked how/why) - datafile shrink don't do anything to the extents allocated to table T.


Regards

Harschil.

Shrink and performance

Vinu, August 24, 2009 - 1:01 am UTC

Is there any performance advantage(or disadvantage) we get by doing a shrink on tablespace after purging large number of records ?
Tom Kyte
August 25, 2009 - 9:39 am UTC

well, you do not 'shrink' tablespaces, you can resize datafiles which could have the effect of making the tablespace 'smaller'

but no, performance would not be impacted by the size of the tablespace

if you have a 1mb table in a 100mb tablespace (99mb free)

or

if you have a 1mb table in a 100000000mb tablespace (99999999mb) free


it would take the same amount of time to retrieve data from it.



but since you say "after a purge of records" I think you meant to say "table" not "tablespace"


given that, a shrink might

a) make it faster to retrieve rows
b) make it slower to retrieve rows
c) have no effect one way or the other

Example of (a). You full scan the table frequently, you just purged 50% of the data. The shrink will make the table 50% the size it currently is. Result: full scan likely completes in 50% of the time.

Example of (b). You have an employee table, rows are inserted as they arrive (just a heap table). The data in the table is accidentally sorted by HIRE_DATE (we tend to insert at the end of this table - the table is growing). The clustering factor (measure of how sorted a table is by some index key) is very good on the index on hire_date. You frequently query "where hire_date between :x and :y" and we use an index. You alter table t shrink space compact after a purge and all of a sudden the index is not as "good", we've moved 'new' records from the bottom of the table to the 'top' of the table. We stop using the index because the clustering factor tells us to. We start full scanning (which is the correct approach) but the retrieval times are higher than before - not because we are not using the index but rather because the data we need is scattered.

Example of (c). You use an index on a primary key to retrieve data from this table. We do 3 IO's against the index, get a rowid, read the row by rowid. I don't care how much you reorganize this table - you will not reduce the work needed to get that row via that index.



Unable to resize tablespace datafile?

Mahmood Lebbai, August 26, 2009 - 6:06 pm UTC

Just thought of putting my 2 cents worth...

Thanks for the script..that was helpful...Even i faced the issue of "file contains used data beyond requested RESIZE value" after running the query your script(maxshrink) provided... though altering/moving table and rebuilding the indexes of the highest block_id segments helps..I think we also need to consider other users might also be under the same tablespace and hence using the same datafiles. so we would need to do clean up of the dropped/deleted objects at tablespace level.
so a simple command
"PURGE TABLESPACE <<TABLESPACE_NAME>>"

resolved this issue...I had to execute the above script and then re-run the alter command your maxshrink.sql provided...it just went smooth and datafile got reduced to the smaller size.

Your suggestions if these do not make sense?

Thanks.

Tom Kyte
August 28, 2009 - 4:09 pm UTC

you basically emptied the recyclebin for that tablespace - if you want to do that, that is fine. Just be aware of what you actually did - you made it so that the dropped objects in that tablespace cannot be undropped.

alter table - why compact then shrink?

CJ, October 23, 2009 - 6:36 am UTC

Hi Tom,

In your example above, you have done:

alter table testing shrink space compact;

and then

alter table testing shrink space;

I am curious as to why you did that, because in my case the latter statement alone was sufficient to deallocate the extent

12:34:49 cj@CJDB2>select extent_id, block_id, blocks from dba_extents where segment_name = 'TESTING' order by extent_id;

EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ----------
0 73 8
1 9 8
2 33 8

3 rows selected.

Elapsed: 00:00:00.03
12:34:53 cj@CJDB2>delete from testing where mod(x, 2)=0;

10 rows deleted.

Elapsed: 00:00:00.00
12:35:03 cj@CJDB2>commit;

Commit complete.

Elapsed: 00:00:00.00
12:35:04 cj@CJDB2>select extent_id, block_id, blocks from dba_extents where segment_name = 'TESTING' order by extent_id;

EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ----------
0 73 8
1 9 8
2 33 8

3 rows selected.

Elapsed: 00:00:00.03
12:35:10 cj@CJDB2>alter table testing shrink space;

Table altered.

Elapsed: 00:00:00.01
12:35:17 cj@CJDB2>select extent_id, block_id, blocks from dba_extents where segment_name = 'TESTING' order by extent_id;

EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ----------
0 73 8
1 9 8

2 rows selected.

Elapsed: 00:00:00.03
12:35:19 cj@CJDB2>


Tom Kyte
October 23, 2009 - 2:31 pm UTC

the compact does the heavy lifting - moves the rows from the bottom to the top of the table, then the shrink space only has to redraw the high water mark.

I just two stepped it - you can do it as a single thing.

Keita, December 10, 2009 - 4:52 pm UTC

Hi tom;

1-)
You already mentioned that, shrink doesnt move extents.

Suppose

I have table A. Here is the extend map.

bbbb-bbbb-bbbb-bbbb

I deleted some rows and, below blocks are in freelist or in bitmap

bbbb-fffb-fbbb-bbbb

When I shrink this table,how would the extent map change ?
Can you show the figure?

2-)
Can I block include rows of more than one segment?


Tom Kyte
December 10, 2009 - 6:06 pm UTC

bbbb-fffb-fbbb-bbbb

would become

bbbb-bbbb-fbbb-bfff

after a shrink space compact then become

bbbb-bbbb-fbbb-b

after a shrink space


#2) "Can I block include rows of more than one segment?"

if you mean "can a block include rows of more than one table", the answer is yes, definitely, happens every day in every single Oracle database on the planet - every single one.

If you mean "can a block include rows of more than one segment", the answer is no.

chew on that :)









Now for the details, if you create a cluster - the cluster is the segment. A cluster is a segment that you can create tables in. If you create a b*tree cluster for example using a cluster key of "NUMBER(4)", you could then create the emp and dept table in that cluster using a cluster key of deptno for each (the cluster is the segment, the tables are not segments) and all of the data for deptno=10 would be stored together - both the rows from emp and dept would be together.


The reason every Oracle database on the planet has this happen every day is because the data dictionary is built that way, using b*tree clusters.



A reader, December 13, 2009 - 4:40 pm UTC

Sir;

Correct me if I am wrong,
shrink moves used blocks to forward and free ones to at the end of the segment. (It doesnt move the extends)

1-)

so you said that

bbbb-fffb-fbbb-bbbb

would become

bbbb-bbbb-fbbb-bfff


why not?

bbbb-bbbb-bbbb-ffff



2-) You also said that:

...after a shrink space compact then become

...bbbb-bbbb-fbbb-b

Since my extent consists of 4 blocks, this operation damages my extent and my extent only consist of one block.



Tom Kyte
December 14, 2009 - 8:41 am UTC

yes, forgot to move an f.


... Since my extent consists of 4 blocks, this operation damages my extent and my
extent only consist of one block.
...

we don't damage anything. Don't worry, we only do what it right and correct and non-damaging to your data.

A reader, December 15, 2009 - 3:23 pm UTC

Suppose it became:
...bbbb-bbbb-fbbb-b

My last extent will consist of only one block.
Is that possible in Oracle? How come an extent consist of only one block?
Tom Kyte
December 15, 2009 - 5:10 pm UTC

extents will have at least 5 blocks.

A reader, December 15, 2009 - 5:39 pm UTC

bbbbb-fffbb-bbbbb-bbbbb

would become

bbbbb-bbbbb-bbbbb-bbfff

and after shrink space compact

bbbbb-bbbbb-bbbbb-bb

As you see my last extent will consist of only two blocks,Is that possible?
Tom Kyte
December 16, 2009 - 7:05 am UTC

"extents will have at least 5 blocks. "



create tablespace demo extent management local uniform size 1k
*
ERROR at line 1:
ORA-03249: Uniform size for auto segment space managed tablespace should have
atleast 5 blocks


ops$tkyte%ORA10GR2> !oerr ora 3249
03249, 00000, "Uniform size for auto segment space managed tablespace should have atleast %s blocks"
// *Cause:  For the given blocksize, uniform size specified is insufficient
// *Action: Specify larger extent size and retry

A reader, January 04, 2010 - 5:30 pm UTC

Sir in above scenerio;


...bbbbb-fffbb-bbbbb-bbbbb

...would become

...bbbbb-bbbbb-bbbbb-bbfff

...and after shrink space compact

...bbbbb-bbbbb-bbbbb-bb



Since extent will have at least 5 blocks.

after shrink space compact, it should be again:

bbbbb-bbbbb-bbbbb-bbfff

Am i right?
(if the last three f blocks are given to tablespace, my last extent will have two blocks which is not possible since extent should have minumum 5 blocks)
Tom Kyte
January 05, 2010 - 8:29 am UTC

it depends.

you show only 20 blocks, if the data file those blocks are in has more than 20 blocks - it would DEPEND on what follows these 20 blocks.


But yes, in general, if there aren't enough blocks to be released to make a sensible extent, they will remain as part of whatever they were already

For ur kind attention

Fh.Syed, February 12, 2010 - 12:16 am UTC

Sir,
I have been through your website sql files collection and found out MAXSHRINK.sql.
It works greatly on my 11g server, however on test database of oracle-XE it is not performing the said task of shrinking datafiles .

Here below is the direct copy-paste from SQLPLUS* window.


<b>SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 11 22:20:37 2010</b>

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> connect sys as sysdba
Enter password:
Connected.
<b>SQL> @ c:\maxshrink.sql</b>

VALUE
--------------------------------------------------------------------------------

8192


                                                   Smallest
                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
-------------------------------------------------- -------- -------- --------
C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF                       454      461        7
C:\ORACLEXE\ORADATA\XE\FLOW_1040112558298731.DBF          9      105       96
C:\ORACLEXE\ORADATA\XE\UNDO.DBF                         133      133        0
C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF                       739      743        4
C:\ORACLEXE\ORADATA\XE\APEX_USER.DBF                      1        1        0
C:\ORACLEXE\ORADATA\XE\FLOW_1557732414608939.DBF          1        2        1
C:\ORACLEXE\ORADATA\XE\USERS.DBF                          1       22       21
                                                                     --------
sum                                                                       129

7 rows selected.


CMD
---------------------------------------------------------------------------
alter database datafile 'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF' resize 454m;
alter database datafile 'C:\ORACLEXE\ORADATA\XE\FLOW_1040112558298731.DBF'
resize 9m;

alter database datafile 'C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF' resize 739m;
alter database datafile 'C:\ORACLEXE\ORADATA\XE\FLOW_1557732414608939.DBF'
resize 1m;

alter database datafile 'C:\ORACLEXE\ORADATA\XE\USERS.DBF' resize 1m;

SQL> COMMIT;

Commit complete.

<b>SQL> @ c:\maxshrink.sql</b>

VALUE
--------------------------------------------------------------------------------

8192


                                                   Smallest
                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
-------------------------------------------------- -------- -------- --------
C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF                       454      461        7
C:\ORACLEXE\ORADATA\XE\FLOW_1040112558298731.DBF          9      105       96
C:\ORACLEXE\ORADATA\XE\UNDO.DBF                         133      133        0
C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF                       739      743        4
C:\ORACLEXE\ORADATA\XE\APEX_USER.DBF                      1        1        0
C:\ORACLEXE\ORADATA\XE\FLOW_1557732414608939.DBF          1        2        1
C:\ORACLEXE\ORADATA\XE\USERS.DBF                          1       22       21
                                                                     --------
sum                                                                       129

7 rows selected.


CMD
---------------------------------------------------------------------------
alter database datafile 'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF' resize 454m;
alter database datafile 'C:\ORACLEXE\ORADATA\XE\FLOW_1040112558298731.DBF'
resize 9m;

alter database datafile 'C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF' resize 739m;
alter database datafile 'C:\ORACLEXE\ORADATA\XE\FLOW_1557732414608939.DBF'
resize 1m;

alter database datafile 'C:\ORACLEXE\ORADATA\XE\USERS.DBF' resize 1m;



I have run it twice and no matter how many times i run it , commit , shutdown & start-up, archivelog , no archivelog, mount , open i.e in ever state of database, but it shows the exact same numbers of saving to be made.
I have tried it as-well by turning off all the services except the listener and the database service but have not succeeded.


Kindly give me a hint of what i might have been doing wrong or what is to be done to perform the operation correctly .

Regards
Fh.syed
Tom Kyte
February 16, 2010 - 12:17 pm UTC

"UR" didn't make it to work today, so sorry....




My script does not take into consideration objects that are in the recyclebin, purge it for the tablespaces you want to shrink before attempting to shrink them (and realize that things in the recyclebin - once purged - are *gone*)

http://docs.oracle.com/docs/cd/B19306_01/backup.102/b14192/flashptr004.htm#sthref636

For your kindself

Fh.syed, February 12, 2010 - 12:30 am UTC

Sir,
Since there is not any edit option i have to write another review.
Right after i asked the above question i did the alter commands manually .. and they worked . ( I copy paste the suggested alter commands by the script run)

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1287016 bytes
Variable Size             146803864 bytes
Database Buffers          134217728 bytes
Redo Buffers                2904064 bytes
Database mounted.
SQL> @ c:\maxshrink.sql

VALUE
--------------------------------------------------------------------------------

8192

         from dba_extents
              *
ERROR at line 8:
ORA-01219: database not open: queries allowed on fixed tables/views only


         from dba_extents
              *
ERROR at line 5:
ORA-01219: database not open: queries allowed on fixed tables/views only


SQL> alter database datafile 'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF' resize 454m;
alter database datafile 'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF' resize 454m
*
ERROR at line 1:
ORA-01109: database not open


SQL> alter database open
  2  ;

Database altered.

SQL> alter database datafile 'C:\ORACLEXE\ORADATA\XE\FLOW_1040112558298731.DBF'
  2  resize 9m;

Database altered.

SQL>
SQL> alter database datafile 'C:\ORACLEXE\ORADATA\XE\USERS.DBF' resize 1m;

Database altered.

SQL> alter database datafile 'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF' resize 454m;

Database altered.

SQL> alter database datafile 'C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF' resize 739m;

Database altered.

SQL> alter database datafile 'C:\ORACLEXE\ORADATA\XE\FLOW_1557732414608939.DBF'
  2  resize 1m;

Database altered.

SQL> alter database datafile 'C:\ORACLEXE\ORADATA\XE\FLOW_1557732414608939.DBF'
  2  resize 1m;

Database altered.


SQL> @ c:\maxshrink.sql

VALUE
--------------------------------------------------------------------------------

8192


                                                   Smallest
                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
-------------------------------------------------- -------- -------- --------
C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF                       454      454        0
C:\ORACLEXE\ORADATA\XE\FLOW_1040112558298731.DBF          9        9        0
C:\ORACLEXE\ORADATA\XE\UNDO.DBF                         133      133        0
C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF                       739      739        0
C:\ORACLEXE\ORADATA\XE\APEX_USER.DBF                      1        1        0
C:\ORACLEXE\ORADATA\XE\FLOW_1557732414608939.DBF          1        1        0
C:\ORACLEXE\ORADATA\XE\USERS.DBF                          1        1        0
                                                                     --------
sum                                                                         0

7 rows selected.


no rows selected



I do not have any slightest idea why the script have failed to perform the operation while manual alter commands did what they needed to perform.

Regards
Fh.Syed
Tom Kyte
February 16, 2010 - 12:19 pm UTC

ok, that is easy

all this script does is print out the commands, you have to cut and paste them to actually run them. That is what I intended to have happen.

You did the right thing - by cutting and pasting.

Thanks

A reader, February 24, 2010 - 11:01 pm UTC

Thanks Tom. The script saved a lot of time for me.

maxresize.sql results ORA-03297

matthias, June 28, 2010 - 4:23 am UTC

Hello Tom,

I'm trying to reclaim space from 35G datafile which has only 9G used. But the resize command fails with "ORA-03297: file contains used data beyond requested RESIZE value".

All the tables have already been "moved" and the last used block reported by dba_extents seems to be in line with the space used. But anyway - it wont let me resize the datafile, not even to 20G or so.

Any suggestions (besides moving all objects into a new datafile)?

Thanks alot...

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 28 10:58:14 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYSTEM@VKS_SI0BOS126> ----------- maxshrink.sql ----------------------------------
SYSTEM@VKS_SI0BOS126>
SYSTEM@VKS_SI0BOS126> set verify off
SYSTEM@VKS_SI0BOS126> column file_name format a50 word_wrapped
SYSTEM@VKS_SI0BOS126> column smallest format 999,990 heading "Smallest|Size|Poss."
SYSTEM@VKS_SI0BOS126> column currsize format 999,990 heading "Current|Size"
SYSTEM@VKS_SI0BOS126> column savings  format 999,990 heading "Poss.|Savings"
SYSTEM@VKS_SI0BOS126> break on report
SYSTEM@VKS_SI0BOS126> compute sum of savings on report
SYSTEM@VKS_SI0BOS126>
SYSTEM@VKS_SI0BOS126> column value new_val blksize
SYSTEM@VKS_SI0BOS126> select value from v$parameter where name = 'db_block_size'
  2  /

VALUE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8192

SYSTEM@VKS_SI0BOS126>
SYSTEM@VKS_SI0BOS126> select file_name,
  2         ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
  3         ceil( blocks*&&blksize/1024/1024) currsize,
  4         ceil( blocks*&&blksize/1024/1024) -
  5         ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
  6  from dba_data_files a,
  7       ( select file_id, max(block_id+blocks-1) hwm
  8           from dba_extents
  9          group by file_id ) b
 10  where a.file_id = b.file_id(+)
 11  /

                                                   Smallest
                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
-------------------------------------------------- -------- -------- --------
D:\ORACLE\ORADATA\VKS\SYSTEM01.DBF                    2,712    2,712        0
D:\ORACLE\ORADATA\VKS\TS2014.DBF                          2        2        0
D:\ORACLE\ORADATA\VKS\SAP01.DBF                       4,096    4,096        0
D:\ORACLE\ORADATA\VKS\TS1998.DBF                          8        8        0
D:\ORACLE\ORADATA\VKS\TS2003.DBF                        249      249        0
D:\ORACLE\ORADATA\VKS\TS2005.DBF                        576      576        0
D:\ORACLE\ORADATA\VKS\USERS02.DBF                     4,096    4,096        0
D:\ORACLE\ORADATA\VKS\TS2006.DBF                      1,508    1,508        0
D:\ORACLE\ORADATA\VKS\TS2012.DBF                          2        2        0
D:\ORACLE\ORADATA\VKS\TS2013.DBF                          2        2        0
D:\ORACLE\ORADATA\VKS\SAP02.DBF                       2,136    2,136        0
D:\ORACLE\ORADATA\VKS\USERS01.DBF                     8,966   25,600   16,634
D:\ORACLE\ORADATA\VKS\TS1997.DBF                          2        2        0
F:\ORACLE\ORADATA\VKS\UNDODFLT.DBF                      655      655        0
D:\ORACLE\ORADATA\VKS\TS2000.DBF                        114      114        0
D:\ORACLE\ORADATA\VKS\TS2009.DBF                      4,083    4,083        0
D:\ORACLE\ORADATA\VKS\TS2015.DBF                          2        2        0
D:\ORACLE\ORADATA\VKS\SYSAUX01.DBF                    1,123    1,123        0
D:\ORACLE\ORADATA\VKS\TS1999.DBF                         49       49        0
D:\ORACLE\ORADATA\VKS\TS2010.DBF                      2,234    2,234        0
D:\ORACLE\ORADATA\VKS\TS2001.DBF                        140      140        0
D:\ORACLE\ORADATA\VKS\TS2002.DBF                        164      164        0
D:\ORACLE\ORADATA\VKS\TS2004.DBF                        521      521        0
D:\ORACLE\ORADATA\VKS\TS2007.DBF                      4,752    4,752        0
D:\ORACLE\ORADATA\VKS\TS2008.DBF                      3,737    3,737        0
D:\ORACLE\ORADATA\VKS\TS2011.DBF                          2        2        0
                                                                     --------
sum                                                                    16,634

26 rows selected.

SYSTEM@VKS_SI0BOS126>
SYSTEM@VKS_SI0BOS126> column cmd format a75 word_wrapped
SYSTEM@VKS_SI0BOS126>
SYSTEM@VKS_SI0BOS126> select 'alter database datafile '''||file_name||''' resize ' ||
  2         ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
  3  from dba_data_files a,
  4       ( select file_id, max(block_id+blocks-1) hwm
  5           from dba_extents
  6          group by file_id ) b
  7  where a.file_id = b.file_id(+)
  8    and ceil( blocks*&&blksize/1024/1024) -
  9        ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
 10  /

CMD
---------------------------------------------------------------------------
alter database datafile 'D:\ORACLE\ORADATA\VKS\USERS01.DBF' resize 8966m;

SYSTEM@VKS_SI0BOS126>
SYSTEM@VKS_SI0BOS126> alter database datafile 'D:\ORACLE\ORADATA\VKS\USERS01.DBF' resize 8966m;
alter database datafile 'D:\ORACLE\ORADATA\VKS\USERS01.DBF' resize 8966m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


SYSTEM@VKS_SI0BOS126> alter database datafile 'D:\ORACLE\ORADATA\VKS\USERS01.DBF' resize 20000m;
alter database datafile 'D:\ORACLE\ORADATA\VKS\USERS01.DBF' resize 20000m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


SYSTEM@VKS_SI0BOS126> select *
  2    from (
  3  select owner, segment_name,
  4         segment_type, block_id
  5    from dba_extents
  6   where file_id =
  7     ( select file_id
  8         from dba_data_files
  9        where file_name = 'D:\ORACLE\ORADATA\VKS\USERS01.DBF' )
 10   order by block_id desc
 11         )
 12   where rownum <= 5
 13   ;

OWNER                          SEGMENT_NAME                                                                      SEGMENT_TYPE         BLOCK_ID
------------------------------ --------------------------------------------------------------------------------- ------------------ ----------
DIL_DATA                       SYS_LOB0000201574C00003$$                                                         LOBSEGMENT            1147593
DIL_DATA                       SYS_LOB0000201574C00003$$                                                         LOBSEGMENT            1147585
DIL_DATA                       OBJECTS                                                                           TABLE                 1147577
ENTW                           EVAIN_I_QUEUE                                                                     INDEX                 1147569
ENTW                           EVAIN_I_STATEVAINKEN                                                              INDEX                 1147553

SYSTEM@VKS_SI0BOS126>

SYSTEM@VKS_SI0BOS126> select *
  2    from dba_data_files
  3   where file_name = 'D:\ORACLE\ORADATA\VKS\USERS01.DBF';

FILE_NAME                                             FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS    RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
-------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------
D:\ORACLE\ORADATA\VKS\USERS01.DBF                           4 USERS                          2.6844E+10    3276800 AVAILABLE            4 YES 3.4360E+10    4194302          160 2.6843E+10     3276784 ONLINE

SYSTEM@VKS_SI0BOS126>

Tom Kyte
July 06, 2010 - 11:22 am UTC

look in your recycle bin, you might have to purge the recycle bin for that tablespace.

same problem ORA-03297 in using maxshrink.sql

piotr, July 01, 2010 - 7:58 am UTC

Hi Tom,
I think that I have same problem as matthias on 10gR2 here it is:

select 'alter database datafile '''||FILE_NAME||''' resize ' ||
       ceil( (nvl(hwm,1)*8192)/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*8192/1024/1024) -
      CEIL( (NVL(HWM,1)*8192)/1024/1024 ) > 0
      and a.file_id = 14;

CMD                               
-------------------------------------------------------------------------------------------------------------
alter database datafile '+DATA/crp4/datafile/apps_ts_interface.341.722527955' resize 2092m;                                                      

      
Error starting at line 13 in command:
alter database datafile '+DATA/crp4/datafile/apps_ts_interface.341.722527955' resize 2092m
Error report:
SQL Error: ORA-03297: file contains used data beyond requested RESIZE value
03297. 00000 -  "file contains used data beyond requested RESIZE value"
*Cause:    Some portion of the file in the region to be trimmed is
           currently in use by a database object
*Action:   Drop or move segments containing extents in this region prior to
           resizing the file, or choose a resize value such that only free
           space is in the trimmed.

     
select * from (
select tablespace_name,BLOCK_ID START_BLOCK, BLOCK_ID+blocks END_BLOCK
from DBA_FREE_SPACE where FILE_ID = 14 order by BLOCK_ID desc
) where rownum <= 5;

TABLESPACE_NAME                START_BLOCK            END_BLOCK              
------------------------------ ---------------------- ---------------------- 
APPS_TS_INTERFACE              1615593                1620473                
APPS_TS_INTERFACE              1615577                1615593                
APPS_TS_INTERFACE              1615561                1615577                
APPS_TS_INTERFACE              1615545                1615561                
APPS_TS_INTERFACE              1615529                1615545                

as you can see there are two free extents at the end but they are not coalesced (LMTS) don't know why

this is the tablespace:
select * from dba_tablespaces where tablespace_name = 'APPS_TS_INTERFACE';

TABLESPACE_NAME                BLOCK_SIZE             INITIAL_EXTENT         NEXT_EXTENT            MIN_EXTENTS            MAX_EXTENTS            PCT_INCREASE           MIN_EXTLEN             STATUS    CONTENTS  LOGGING   FORCE_LOGGING EXTENT_MANAGEMENT ALLOCATION_TYPE PLUGGED_IN SEGMENT_SPACE_MANAGEMENT DEF_TAB_COMPRESSION RETENTION   BIGFILE 
------------------------------ ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- --------- --------- --------- ------------- ----------------- --------------- ---------- ------------------------ ------------------- ----------- ------- 
APPS_TS_INTERFACE              8192                   131072                 131072                 1                      2147483645             0                      131072                 ONLINE    PERMANENT LOGGING   NO            LOCAL             UNIFORM         NO         AUTO                     DISABLED            NOT APPLY   NO      


please help
Tom Kyte
July 06, 2010 - 2:18 pm UTC

see above.

Great. Datafile shrinked.

matthias, July 06, 2010 - 5:00 pm UTC

I had tried to "purge recyclebin", but that of course, was not enough...

So I've learned today that there's not only "the" recyclebin, but many.

BTW, would it be possible to determine which blocks are still used by a recyclebin? (could maxshrink.sql be updated?)
Tom Kyte
July 06, 2010 - 6:33 pm UTC

yes it could be - but, I don't think that is the right approach.

I think the right approach would be to put in a comment about purging before issuing the alter statements. If you are shrinking to save space, purging the recyclebin objects in the way seems the right approach.

still problem ORA-03297 in using maxshrink.sql

Piotr, July 23, 2010 - 3:55 am UTC

Hello Tom,
I've looked into recyclebin but still no luck, the recycle bin is purged and turned off and as before I'm getting ORA-03297
when looking into dba_free_space I see that there is a free space but it is divided into small pieces (fragmented?), and then on block 1015817 there is over 4GB free piece.
My block_size is 8K
select BLOCK_ID START_BLOCK, BLOCK_ID+blocks END_BLOCK, ROUND(BYTES/1024/1024,2) FREE_MB
from dba_free_space where file_id = 14 order by block_id desc ;
START_BLOCK            END_BLOCK              FREE_MB                
---------------------- ---------------------- ---------------------- 
1627001                1628153                9                      
1626985                1627001                0,13                   
1626969                1626985                0,13                   
1626953                1626969                0,13                   
1626937                1626953                0,13                   
1626921                1626937                0,13                   
1626905                1626921                0,13                   
1626889                1626905                0,13                   
1626873                1626889                0,13                   
1625705                1626873                9,13                   
1625689                1625705                0,13                   
1625673                1625689                0,13                   
1625657                1625673                0,13                   
1625609                1625657                0,38                   
1625593                1625609                0,13                   
1625577                1625593                0,13                   
1625561                1625577                0,13                   
1625545                1625561                0,13                   
1625513                1625545                0,25                   
1625497                1625513                0,13                   
1015817                1625497                4763,13                
1010265                1015817                43,38  

Do you have any idea why is that so?
Tom Kyte
July 23, 2010 - 9:48 am UTC

throw us a bone - what size does maxshrink say it can shrink to here? what size do you think it should resize to.

if you try resizing bit by bit - can you find the extent that is causing the problem - then go to the dictionary and see what may be there.

maxshrink is just a query - have you looked at the query - do you see anything obviously wrong given the data in your dictionary?

still problem ORA-03297 in using maxshrink.sql

Piotr, July 26, 2010 - 5:04 am UTC

Hello Tom,
sorry for lack of information in previous post, so here it is:
maxshrink:
select 'alter database datafile '''||FILE_NAME||''' resize ' ||
       ceil( (nvl(hwm,1)*8192)/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*8192/1024/1024) -
      CEIL( (NVL(HWM,1)*8192)/1024/1024 ) > 0
      and a.file_id = 14;

CMD                               
------------------------------------------------------------------------------------------------
alter database datafile '+TDATA/test/datafile/apps_ts_interface.314.725028689' resize 7893m;                                                      

Error starting at line 1 in command:
alter database datafile '+TDATA/test/datafile/apps_ts_interface.314.725028689' resize 7893m
Error report:
SQL Error: ORA-03297: file contains used data beyond requested RESIZE value
03297. 00000 -  "file contains used data beyond requested RESIZE value"

top extents in file:
SELECT *
FROM
  (SELECT block_id,    blocks,     SEGMENT_TYPE
  FROM DBA_EXTENTS
  WHERE FILE_ID = 14
  ORDER BY BLOCK_ID DESC
  )
where rownum < 15;

BLOCK_ID               BLOCKS                 SEGMENT_TYPE       
---------------------- ---------------------- ------------------ 
1010249                16                     TABLE              
1008313                16                     TABLE              
992041                 16                     INDEX              
898121                 16                     TABLE              
897961                 16                     TABLE              
788313                 16                     TABLE              
780057                 16                     TABLE              
776105                 16                     INDEX              
768585                 16                     TABLE              
768569                 16                     TABLE              
768553                 16                     TABLE              
768537                 16                     TABLE              
768521                 16                     TABLE              
768505                 16                     TABLE              

top rows from dba_free_space:
select BLOCK_ID START_BLOCK, BLOCK_ID+blocks END_BLOCK, ROUND(BYTES/1024/1024,2) FREE_MB
from DBA_FREE_SPACE where FILE_ID = 14 order by BLOCK_ID desc ;

START_BLOCK            END_BLOCK              FREE_MB                
---------------------- ---------------------- ---------------------- 
1626985                1627001                0,13                   
1626969                1626985                0,13                   
1626953                1626969                0,13                   
1626937                1626953                0,13                   

I've tried as you said, resizing byte by byte:
alter database datafile 14 resize 13328384000; --1627000*8*1024
 alter database datafile succeeded.

alter database datafile 14 resize 13328375808; --1626999*8*1024
 ORA-03297: file contains used data beyond requested RESIZE value

so I can see that problem is with block_id 1626999.
What can I do next?

resolved ORA-03297

Piotr, July 26, 2010 - 8:04 am UTC

Hello again,
problem above was that I wasn't aware of whole recycle bin functionality, when I realized that I should purge dba_recyclebin not just recyclebin (even from sys) everything worked fine
sorry for bothering

Shrink Data file

Ramki, October 31, 2010 - 8:37 am UTC

Sir,

we are running of out of disk space.
Due to some wrong configuration, system created with lot of partition tables & with lot of local index. After deleting all unwanted partition tables & rebuild index I am trying to reduce file size.
'MADHRP_SUM_INDEX' table space has 11 files, which store only index objects. I rebuild all the index of a particular file '474', after that files shows zero objects in it. But when I try to resize the file I am getting error. ORA-03297: file contains used data beyond requested RESIZE value.

Pls help me, am I missing some steps.


select File_NAME,FILE_ID,(BYTES/1024/1024/1024)
from dba_Data_files where tablespace_name = 'MADHRP_SUM_INDEX'
order by bytes desc

/d/db/oradata/oss/uma/umamad/madhrp/hrprdb_index.dbf 472 31.9990234375
/d/db/oradata/oss/uma/umamad/madhrp/hrprdb_index2.dbf 474 27.5390625
/d/db/oradata/oss/uma/umamad/madhrp/hrprdb_index4.dbf 476 17.21484375
/d/db/oradata/oss/uma/umamad/madhrp/hrprdb_index3.dbf 475 17.1435546875
/d/db/oradata/oss/uma/umamad/madhrp/hrprdb_index1.dbf 473 11.71875
/d/db/oradata/oss/uma/umamad/madhrp/hrprdb_index9.dbf 513 11.71875
/d/db/oradata/oss/uma/umamad/madhrp/hrprdb_index5.dbf 477 11.71875
/d/db/oradata/oss/uma/umamad/madhrp/hrprdb_index6.dbf 507 11.71875
/d/db/oradata/oss/uma/umamad/madhrp/hrprdb_index7.dbf 511 11.71875
/d/db/oradata/oss/uma/umamad/madhrp/hrprdb_index8.dbf 512 9.765625
/d/db/oradata/oss/uma/umamad/madhrp/hrprdb_index10.dbf 514 9.765625

I spooled out put of following sql and rebuilded all index

SELECT ' ALTER INDEX MADHRP.' ||segment_name ||' REBUILD PARTITION '|| PARTITION_NAME ||' NOLOGGING ;'
FROM dba_Segments a WHERE a.relative_fno = 474

after this file 474 shows 27GB free space

select file_id, sum (bytes/1024/1024/1024 ) from dba_free_space
where tablespace_name='MADHRP_SUM_INDEX'
group by file_id
order by 2 desc

FILE_ID SUM(BYTES/1024/1024/1024)
---------------------- -------------------------
474 27.791015625
475 9.6319580078125
472 9.254150390625
511 6.08087158203125
473 6.01458740234375
507 5.48748779296875
476 4.37225341796875
477 3.1181640625
514 2.4715576171875
513 1.63861083984375
512 0.2359619140625

11 rows selected


select * FROM dba_Segments a WHERE a.relative_fno = 474

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME ........
------------------------------ ----------------------------

0 rows selected


i understand there is no object now in this file.

but when i try to resize file i am getting error.

alter database datafile '/d/db/oradata/oss/uma/umamad/madhrp/hrprdb_index2.dbf' resize 25000M;

SQL Error: ORA-03297: file contains used data beyond requested RESIZE value
03297. 00000 - "file contains used data beyond requested RESIZE value"
*Cause: Some portion of the file in the region to be trimmed is
currently in use by a database object
*Action: Drop or move segments containing extents in this region prior to
resizing the file, or choose a resize value such that only free
space is in the trimmed.


select 'segments', sum(bytes)/1024/1024 mbytes from dba_segments where tablespace_name = 'MADHRP'

'SEGMENTS' MBYTES
-------------------------------- ----------------------
segments

1 rows selected


Regards
Ramki

Tom Kyte
November 01, 2010 - 3:09 pm UTC

two things

a) ctl-f on this page for:

think of your table T as being T in the following.

read that, that likely plays into this


b) make sure you purged the recyclebin for this tablespace - else the dropped objects are 'still there'

Shrink Data file

Ramki, October 31, 2010 - 11:41 am UTC

select 'segments', sum(bytes)/1024/1024 mbytes
from dba_segments
where tablespace_name = 'MADHRP_SUM_INDEX' and RELATIVE_FNO=474


'SEGMENTS' MBYTES
-------------------------------- ----------------------
segments

1 rows selected

Shrink Data file

Ramki, November 02, 2010 - 4:20 am UTC

Sir,

Thanks for your reply, some more doubt from my side.

since select on dba_segments table returns zero rows , i assumes there is no object in that file file is empty. And dba_free_space shows 27GB free which is same as file size.

mean while i found similar bug reported on version 9.2.0.8.0 , Bug 5361778.
Applies to: 
Oracle Server - Enterprise Edition - Version: 9.2.0.8.0
This problem can occur on any platform.

Symptoms
When attempting to resize datafiles that are empty, the following error occurs:

ORA-03297 file contains <num> blocks of data beyond requested RESIZE value

Cause
A file header dump reveals that the header still contains information in the file when
in fact there is not.  This is potentially Bug 5361778.  Therefore, the file header needs to be rebuilt before the resizecan take place. 


< http://power.iotn.co.kr/zboard.php?tab_num=&id=9_1_bbs&page=7&sn1=&divpage=1&sn=off&ss=on&sc=off&select_arrange=headnum&desc=asc&no=1186 >


>select * from V$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for HPUX: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

5 rows selected

Regards
Ramki
Tom Kyte
November 02, 2010 - 6:31 am UTC

if you believe it to be that bug, and since you obviously have access to support - why would you not work the issue with support????

Shrink Data file

Ramki, November 02, 2010 - 4:20 am UTC

Sir,

Thanks for your reply, some more doubt from my side.

since select on dba_segments table returns zero rows , i assumes there is no object in that file file is empty. And dba_free_space shows 27GB free which is same as file size.

mean while i found similar bug reported on version 9.2.0.8.0 , Bug 5361778.
Applies to: 
Oracle Server - Enterprise Edition - Version: 9.2.0.8.0
This problem can occur on any platform.

Symptoms
When attempting to resize datafiles that are empty, the following error occurs:

ORA-03297 file contains <num> blocks of data beyond requested RESIZE value

Cause
A file header dump reveals that the header still contains information in the file when
in fact there is not.  This is potentially Bug 5361778.  Therefore, the file header needs to be rebuilt before the resizecan take place. 


< http://power.iotn.co.kr/zboard.php?tab_num=&id=9_1_bbs&page=7&sn1=&divpage=1&sn=off&ss=on&sc=off&select_arrange=headnum&desc=asc&no=1186 >


>select * from V$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for HPUX: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

5 rows selected

Regards
Ramki

Interesting

A reader, December 03, 2010 - 8:41 am UTC

I think it would be easier to move the data to another table space.

Thanks

Valeriy, August 18, 2011 - 6:51 am UTC

Great appreciate to you for your very helpful advice!!!
Saved a lot of time for me!

SYSTEM Tablespace

Deb, September 09, 2011 - 4:22 am UTC

Hi Tom,

Some one tried to query a table and he received the following error message.

ORA-01653: unable to extend table SYS.FGA_LOG$ by 27309 in tablespace SYSTEM

Do you think there is something wrong the way some set up has been done. for example auditing etc?

Many thanks
Tom Kyte
January 02, 2013 - 8:25 am UTC

no, it just means in this case, for your audit trail to expand, you need to add more room. You may move the audit trails into other tablespaces - but that is optional.

http://docs.oracle.com/cd/E11882_01/network.112/e16543/auditing.htm#DBSEG99725

Object at end of file

Jay, March 15, 2012 - 12:05 am UTC

Hi Tom,
My SYS datafile was more than 12 GB. I used your scripts to shrink it to about 8 GB (sys.aud$ was a few GBs). If I query the dba_segments, the actual space used now by SYSTEM TABLESPACE is about 2 GB. So I used the query to find out what is there at the end of the file, and got C_OBJ#_INTCOL# CLUSTER. How can I reduce the file size?
Thanks,
Jay


Tom Kyte
March 15, 2012 - 7:31 am UTC

you cannot.

modification of 'SYSTEM' tablespace

A reader, March 15, 2012 - 9:17 pm UTC

Hi Tom,

For testing purpose i add a extra datafile to 'SYSTEM' tablespace.
Then seems i can not drop this file.
So how can I modify 'SYSTEM' back to his original with only 1 datafile?
Is it as below? I dare to test below as it takes me really a while to install the ORACLE successfully on Solaris:)
shutdown
startup mount
alter database datafile 'new file' offline drop
alter database open
Tom Kyte
March 16, 2012 - 8:22 am UTC

I hope this is a scratch database that can be erased without losing anything. Otherwise - if it is not - stop "playing" in it. That would be beyond "not smart"

It should take you just a few minutes to recreate a database using DBCA - on solaris, on windows, whatever. Why does it take you a long while?


You can make this file small, but live with it. It is part of your database now. The tablespace has to be taken offline to remove the database - the database has to be open to remove it cleanly - system cannot be taken offline while the database is open.


alter database datafile 'your playing around filename' resize 128k;

thanks very much

A reader, March 26, 2012 - 10:19 pm UTC

Hi Tom,

Thanks very much.

It is a 'playing' database, so i re-create it with DBCA.

Anyway, learnd from you again that 'adding datafie to system tablespace' is something that if already happened, it can not fallback cleanly never.

Types of Defragmentation

Suresh.R, May 18, 2012 - 8:22 am UTC

Hi Tom,

Thanks a lot for your support.
I know to defragment the tables in database.
Could you please let me know if any option to use same for index and tablespaces and how it can be used?
Also tell me what is the difference between below three (Shrink space, Coalsec & deallocate unused space) commands on defragmentation.

example:-
-----------
We have table T.

1. Alter table T shrink space;
2. Alter table T coalsec;
3. Alter table T deallocate unused space.

kindly let me know the difference between them and how it works.

Many thanks for your support.
Tom Kyte
May 21, 2012 - 8:01 am UTC

why? what are you trying to accomplish. Please don't say "reorganization", tell us why and what you hope to accomplish.



1) that will typically reduce the size of the structure AND the space allocated by reorganizing the structure in place and then releasing space back to dba_free_space.

2) that will typically reduce the size of the structure, but it will not release space back to the tablespace, it will put the freed space onto the free list of the existing structure and use it for subsequent operations on that structure.

3) that will release any extent that is allocated to the segment but has not yet been used by the segment. It will not touch the existing structure.

issue with maxshrinks.sql

deebee, June 14, 2012 - 5:59 am UTC

Hi Tom,
Ive been using the maxshrinks sql for some time without issue but hit an problem recently on a site where RMAN started complaining during the nightly backup about the datafiles that had been resized.

Whatever size the datafiles had come down to, I had to increase by 100m to get RMAN to back them up. this is the relevant support note with the error that led me to do that.

Rman backup fails with Ora-600 [Krbbfmx_notfound] [ID 393211.1]

Im guessing its block size related and the 100m is just enough to push them higher than whatever RMAN doesnt like about it.

Ive posted the info on OTN for further.

https://forums.oracle.com/forums/thread.jspa?threadID=2402030&tstart=0


Tom Kyte
June 14, 2012 - 12:08 pm UTC

please utilize support, this would be an rman bug. It isn't a maxshrink bug or anything like. When you do something totally normal, supported and just fine - it shouldn't cause something to fail like that.

It isn't that upsizing by 100m would fix it - it has to do with unused block optimizations and would hit people at many different sizes. One temporary solution/workaround is to:

backup blocks all database

use blocks all - to skip that optimization.

If you open an SR, there are patches for this for most platforms and starting in 10.2.0.4 - it shouldn't be an issue at all.


clarification

deebee, June 15, 2012 - 4:35 am UTC

absolutely Tom, I should have clarified that, obviously not the shrink script but hitting the bug as a result of resizing.
Getting that error on 10.2.0.3 and Yes, there is a fix for it from 10.2.0.4 I wasnt suggesting anyone should stop using the script (have it in my armoury for a long time), just if if they do get the error, what the fix might be.
If Id known about the possible bug impact beforehand, and on 10.2.0.3, I would have ran the resize on 1 datafile first to see were they going to be affected.
Customer has upgrade plan in place coming shortly anyway PLUS theyve added some disk since so no need to rerun any resizes in the short term. thanks as always.
Tom Kyte
June 15, 2012 - 7:56 am UTC

And there are patches for it for 10.2.0.3 if you work via support.


and an easier workaround than resizing datafiles!

Move Vs Shrink

Suhas Patil, December 20, 2012 - 1:28 am UTC

Hi Tom,

Good Day.

I post
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:153612348067#926206100346741957
was really helpful.
I tried to simulate this test for both move and shrink to find out the difference between move and shrink.

1.Can you please let us know the algorithm of Move as you had pointed out the algorithm of Shrink.
2.I did delete some records from a table and then did the alter table shrink and alter table move. Below are the results.
enable row movement
as
select rownum id, rpad('*',1000,'*') data,object_name,object_type
from all_objects;


dbms_stats.gather_table_stats(user,'T');

delete from t where mod(id,4) = 0;

Commit;

dbms_stats.gather_table_stats(user,'T');

select blocks,empty_blocks from user_tables where table_name = 'T'; 
Original -->7428 ------- 0
Shrink ---->4754 ------- 0
Move   ---->5576 ------- 0

select sum(bytes)/(1024*1024) size_in_MB,sum(extents) extent_num from user_segments where segment_name = 'T'; 
Original -->59 ------ 74
Shrink ---->37.8 ---- 53
Move   ---->44 ------ 59

Can you let me know why Move is occupying larger space than Shrink.

Regards,
Suhas Patil
Tom Kyte
December 20, 2012 - 5:22 pm UTC

1) move will do a full scan of the old segment, create a new compact block from this data and then write it to the datafile. In short -

full scan => build blocks => direct path write

2) please show your work, all of it, so we can reproduce. Based on your outline, I cannot. so, show us the step by steps please. describe the tablespace, your database (blocksize, etc), the storage parameters used, etc.

Shrink vs Move

Suhas Patil, December 20, 2012 - 11:45 pm UTC

Hi Tom,

Good Day, Thanks for replying. Please find below the test case along with the tables storage parameters.
SUHAS # MTN >ed
Wrote file afiedt.buf

  1  create table T
  2  enable row movement
  3  as
  4  select rownum id, rpad('*',1000,'*') data,object_name,object_type
  5* from all_objects
SUHAS # MTN >/

Table created.

Elapsed: 00:00:02.68
SUHAS # MTN >exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.21
SUHAS # MTN >
SUHAS # MTN >select blocks,empty_blocks from user_tables where table_name = 'T';

    BLOCKS|EMPTY_BLOCKS                                                                                                                                                                                 
----------|------------                                                                                                                                                                                 
      6827|           0                                                                                                                                                                                 

1 row selected.

Elapsed: 00:00:00.12
SUHAS # MTN >select sum(bytes)/(1024*1024) size_in_MB,sum(extents) extent_num from user_segments where
  2  segment_name = 'T';

SIZE_IN_MB|EXTENT_NUM                                                                                                                                                                                   
----------|----------                                                                                                                                                                                   
        54|        69                                                                                                                                                                                   

1 row selected.

Elapsed: 00:00:00.31
SUHAS # MTN >delete from t where mod(id,4) = 0;

10147 rows deleted.

Elapsed: 00:00:04.85
SUHAS # MTN >
SUHAS # MTN >Commit;

Commit complete.

Elapsed: 00:00:00.00
SUHAS # MTN >
SUHAS # MTN >exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.96
SUHAS # MTN >
SUHAS # MTN >select blocks,empty_blocks from user_tables where table_name = 'T';

    BLOCKS|EMPTY_BLOCKS                                                                                                                                                                                 
----------|------------                                                                                                                                                                                 
      6827|           0                                                                                                                                                                                 

1 row selected.

Elapsed: 00:00:00.09
SUHAS # MTN >select sum(bytes)/(1024*1024) size_in_MB,sum(extents) extent_num from user_segments where segment_name = 'T';

SIZE_IN_MB|EXTENT_NUM                                                                                                                                                                                   
----------|----------                                                                                                                                                                                   
        54|        69                                                                                                                                                                                   

1 row selected.

Elapsed: 00:00:00.09
SUHAS # MTN >alter table T shrink space;

Table altered.

Elapsed: 00:00:09.89
SUHAS # MTN >exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.10
SUHAS # MTN >select blocks,empty_blocks from user_tables where table_name = 'T';

    BLOCKS|EMPTY_BLOCKS                                                                                                                                                                                 
----------|------------                                                                                                                                                                                 
      4359|           0                                                                                                                                                                                 

1 row selected.

Elapsed: 00:00:00.01
SUHAS # MTN >select sum(bytes)/(1024*1024) size_in_MB,sum(extents) extent_num from user_segments where segment_name = 'T';

SIZE_IN_MB|EXTENT_NUM                                                                                                                                                                                   
----------|----------                                                                                                                                                                                   
   34.6875|        50                                                                                                                                                                                   

1 row selected.

Elapsed: 00:00:00.07
SUHAS # MTN >drop table T purge;

Table dropped.

Elapsed: 00:00:01.62

=============================== MOVE==========================================
SUHAS # MTN >create table T
  2  enable row movement
  3  as
  4  select rownum id, rpad('*',1000,'*') data,object_name,object_type
  5  from all_objects;

Table created.

Elapsed: 00:00:01.81
SUHAS # MTN >exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.68
SUHAS # MTN >select blocks,empty_blocks from user_tables where table_name = 'T';

    BLOCKS|EMPTY_BLOCKS                                                                                                                                                                                 
----------|------------                                                                                                                                                                                 
      6827|           0                                                                                                                                                                                 

1 row selected.

Elapsed: 00:00:00.01
SUHAS # MTN >select sum(bytes)/(1024*1024) size_in_MB,sum(extents) extent_num from user_segments where segment_name = 'T';

SIZE_IN_MB|EXTENT_NUM                                                                                                                                                                                   
----------|----------                                                                                                                                                                                   
        54|        69                                                                                                                                                                                   

1 row selected.

Elapsed: 00:00:00.09
SUHAS # MTN >delete from t where mod(id,4) = 0;

10147 rows deleted.

Elapsed: 00:00:05.59
SUHAS # MTN >exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.96
SUHAS # MTN >select blocks,empty_blocks from user_tables where table_name = 'T';

    BLOCKS|EMPTY_BLOCKS                                                                                                                                                                                 
----------|------------                                                                                                                                                                                 
      6827|           0                                                                                                                                                                                 

1 row selected.

Elapsed: 00:00:00.01
SUHAS # MTN >select sum(bytes)/(1024*1024) size_in_MB,sum(extents) extent_num from user_segments where segment_name = 'T';

SIZE_IN_MB|EXTENT_NUM                                                                                                                                                                                   
----------|----------                                                                                                                                                                                   
        54|        69                                                                                                                                                                                   

1 row selected.

Elapsed: 00:00:00.07
SUHAS # MTN >alter table T move;

Table altered.

Elapsed: 00:00:03.71
SUHAS # MTN >exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.42
SUHAS # MTN >select blocks,empty_blocks from user_tables where table_name = 'T';

    BLOCKS|EMPTY_BLOCKS                                                                                                                                                                                 
----------|------------                                                                                                                                                                                 
      5125|           0                                                                                                                                                                                 

1 row selected.

Elapsed: 00:00:00.01
SUHAS # MTN >select sum(bytes)/(1024*1024) size_in_MB,sum(extents) extent_num from user_segments where segment_name = 'T';

SIZE_IN_MB|EXTENT_NUM                                                                                                                                                                                   
----------|----------                                                                                                                                                                                   
        41|        56                                                                                                                                                                                   

1 row selected.

Elapsed: 00:00:00.07
SUHAS # MTN >
SUHAS # MTN >drop table T purge;

Table dropped.

Elapsed: 00:00:00.25
SUHAS # MTN >create table T
  2  enable row movement
  3  as
  4  select rownum id, rpad('*',1000,'*') data,object_name,object_type
  5  from all_objects;

Table created.

Elapsed: 00:00:01.42
SUHAS # MTN >exit


Table with storage Parameters
===============================
create table T
(
ID NUMBER,
DATA VARCHAR2(1000),
OBJECT_NAME VARCHAR2(30),
OBJECT_TYPE VARCHAR2(19)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);

Thanks and Regards,
Suhas Patil
Tom Kyte
January 02, 2013 - 12:06 pm UTC

this is very different from what you said you did. do you see the big difference between what you said you did and what you actually did???? be careful with your descriptions. It sounded like you said to create a table, shrink it, measure it, move it, measure it again. You did something very different.

In any case, the differences are *very* small. The shrink does a delete+positioned insert, it completely reused the existing space (and the existing bit map blocks used to manage space inside of the structure).

The alter table t rebuilt the segment - somewhere else - and introduced brand new bit map blocks - and it introduced a few more of the bit map blocks used by an ASSM (automatic segment space managed) tablespace.

but in reality, the alter table t move put more rows on fewer blocks! (even though the 'shrunk' table seems to have less blocks overall).

the alter table t move works like a CTAS (create table as select). The shrink is more like "delete some rows, insert some rows, then redraw the high water mark *as low as possible*

but both end up allocating pretty much the exact same amount of space.

If you look at it this way:

ops$tkyte%ORA11GR2> create table t1 enable row movement as select rownum id, rpad('*',1000,'*') data,object_name,object_type from all_objects;

Table created.

ops$tkyte%ORA11GR2> create table t2 enable row movement as select * from t1;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> delete from t1 where mod(id,4) = 0;

18259 rows deleted.

ops$tkyte%ORA11GR2> delete from t2 where mod(id,4) = 0;

18259 rows deleted.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter table T1 shrink space;

Table altered.

ops$tkyte%ORA11GR2> alter table T2 move;

Table altered.

ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T1', estimate_percent=> 100 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T2', estimate_percent=> 100 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table t3 enable row movement as select * from t2;

Table created.

ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T3', estimate_percent=> 100 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select *
  2    from ( select 'T1', min(cnt), max(cnt), avg(cnt), count(distinct bno)
  3             from ( select dbms_rowid.rowid_block_number(rowid) bno, count(*) cnt
  4                      from t1
  5                     group by dbms_rowid.rowid_block_number(rowid)
  6                  )
  7         ),
  8         ( select blocks,empty_blocks from user_tables where table_name = 'T1'),
  9         ( select count(*) extent_cnt, sum(blocks) blks from user_extents where segment_name = 'T1')
 10  union all
 11  select *
 12    from ( select 'T2', min(cnt), max(cnt), avg(cnt), count(distinct bno)
 13             from ( select dbms_rowid.rowid_block_number(rowid) bno, count(*) cnt
 14                      from t2
 15                     group by dbms_rowid.rowid_block_number(rowid)
 16                  )
 17         ),
 18         ( select blocks,empty_blocks from user_tables where table_name = 'T2'),
 19         ( select count(*) extent_cnt, sum(blocks) blks from user_extents where segment_name = 'T1')
 20  union all
 21  select *
 22    from ( select 'T3', min(cnt), max(cnt), avg(cnt), count(distinct bno)
 23             from ( select dbms_rowid.rowid_block_number(rowid) bno, count(*) cnt
 24                      from t3
 25                     group by dbms_rowid.rowid_block_number(rowid)
 26                  )
 27         ),
 28         ( select blocks,empty_blocks from user_tables where table_name = 'T3'),
 29         ( select count(*) extent_cnt, sum(blocks) blks from user_extents where segment_name = 'T1')
 30  /


'T   MIN(CNT)   MAX(CNT)   AVG(CNT) COUNT(DISTINCTBNO)     BLOCKS EMPTY_BLOCKS EXTENT_CNT       BLKS
-- ---------- ---------- ---------- ------------------ ---------- ------------ ---------- ----------
T1          3          7 6.01945055               9100       9100            0         81       9248
T2          4          7 6.04402516               9063       9202            0         81       9248
T3          4          7 6.04402516               9063       9202            0         81       9248





You can see that the move (t2) actually used a little less space for data (number of distinct blocks with rowids on them) even though it has more blocks in "blocks", but they all consume the same amount of space (this time...)

repeated runs might see slightly different results as extent reusage (different sized extents) can and will effect these number

Shrink vs Move

Suhas Patil, December 20, 2012 - 11:48 pm UTC

Hi Tom,

Missed the block size parameter.

db_block_size |integer |8192

The Avg_Row_Len = 1040

Regards,
Suhas Patil


Thanks

Suhas Patil, January 03, 2013 - 5:35 am UTC

Hi Tom,
Thanks a lot for explaining

Regards,
Suhas.

DB Resizing

Vijjay DOMMARAJU, March 18, 2013 - 2:38 am UTC

Hi,

1. We have various Oracle DB in 10g and 11g. These tablespaces are Dictionary managed tablespaces including lobs, clobs and blobs. Users have run the scripts to purge various data from all tables including lobs and blobs. ....etc. Now they want to reduce the datafile size to maximum. Kindly tell me how to do in a fastest way.

2. We have few Oracle 10g databases where ALL tablespaces are Locally Managed, kindly tell me the steps to peform for DB resizing.

3. Last, we are having Oracle 9.2.0.8 databases also, how to resize the dbs. Pl tell me.

For Oracle 10g and 11g we were peforming following steps

1/ Taking Cold backup
2/ Doing expdp
3/ Drop the user and Tablespaces ( as These Tablespaces are dictionary managed and not Locally Managed and with segment space management auto )
4/ creating tablespsaces with LM and Segment Management Auto
5/ impdp
6/ Resizing the DB

DB avergage size is 150 GB, after this we were able to reduce to 90 GB. It is taking almost 24 hours to complete this activity.

I am sure, it can be done in faster way. Pl help me by telling exact steps.

Thank you for your expert guideline.
Tom Kyte
March 25, 2013 - 3:14 pm UTC

1) probably by creating new locally managed tablespaces with a single autoextend datafile (initial something_small next something_reasonable) and performing an alter table t move new_tablespace, alter index i rebuild new_tablespace and then dropping the old tablespaces.


that is, a database reorganization.


You don't give many details, so I can just suggest something that will probably work. can be done in parallel, no undo generation, no redo.

it is 100% offline though of course, you'll be down while doing this.


if you have to be up, you never said, you can use dbms_redefinition to do the same thing in an online fashion - but redo and undo will be generated.


2) ???? If you mean "I was to do #1 to a locally managed tablespace" the steps would be the same.


3) see #1



I would never use exporting for this. Just do it in place with alter.


the alter will READ the source, WRITE the target.


exporting will read the source, write it to disk as just data, then read all of the data again and write it to the database. Multiple reads, multiple writes.

and you stand a >0% chance of losing a grant, a constraint, something with export.

with alter - you read once, you write once, you cannot lose a single grant, constraint - anything. It is all transactional (eg: safe)

Great ....:)

A reader, May 29, 2013 - 2:13 pm UTC

Excellent ................... i have no words

Great ....:)

after runing this script not getting enough space and Disk space is about 100%

Muhammad Irfan, May 31, 2013 - 4:49 pm UTC

Hi,

I used this script on Oracle database 10.2 64-bits running on Sun Solaris 10. Disk space on sever is now about 100 % and i want to shrink it as earliest as possible. running command after this script i am not able to get even 1% free space. I am not a database administrator, but this is high priority task for me.

all result are attach it you can help me to resolve this issue. 
your help in this regard will be high appreciate.


SQL> @/export/home/maxshrink.sql

VALUE
-------------------------------------------------------------
----------------------------------------------------------------------
8192


                                                   Smallest
                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
-------------------------------------------------- -------- -------- --------
/u02/oradata/PROD/axdata1.dbf                        22,296   22,297        1
/u02/oradata/PROD/system01.dbf                          566      567        1
/u02/oradata/PROD/sysaux01.dbf                          529      531        2
/u02/oradata/PROD/undotbs01.dbf                          85      107       22
/u02/oradata/PROD/users01.dbf                             1        2        1
/u02/oradata/PROD/AXDATA.dbf                              1        1        0
/u02/oradata/PROD/AXINDEX.dbf                             1        1        0
                                                                     --------
sum                                                                        27

7 rows selected.


CMD
---------------------------------------------------------------------------
alter database datafile '/u02/oradata/PROD/axdata1.dbf' resize 22296m;
alter database datafile '/u02/oradata/PROD/system01.dbf' resize 566m;
alter database datafile '/u02/oradata/PROD/sysaux01.dbf' resize 529m;
alter database datafile '/u02/oradata/PROD/undotbs01.dbf' resize 85m;
alter database datafile '/u02/oradata/PROD/users01.dbf' resize 1m;

SQL>
SQL> alter database datafile '/u02/oradata/PROD/axdata1.dbf' resize 22296m;

Database altered.

SQL>

Tom Kyte
May 31, 2013 - 5:12 pm UTC

first and foremost, if you are not a DBA, please get out of the database with that DBA account you are using. You are dangerous at best. Get your DBA involved - right now. A system administrator in a database with a database administrator account is seriously bad.

Would you give root to your DBA and let them do whatever they felt like doing????



well, sometimes your disk is full. There could be nothing to be resolved here.

You don't show us that there is any free space to be reclaimed?


and if there is, there are two things to consider

a) you might have to purge your recyclebin to really remove dropped objects. Your DBA will know how to purge it and if it is SAFE to purge it.

b) you might have free space but the free space is followed by allocated space. In that case - a major reorganization of the tablespace would have to take place. Again, something your DBA has to schedule for some off time (and - they are going to need some more FREE SPACE in order to do it).



So, here are the options

a) your disk is full, it really is, there is no free space to be reclaimed. Your DBA will know how to tell you if this is the case.

b) your recyclebin is full of objects that are at the end of datafiles (last allocated space in the file). purging the recyclebin might allow for some more shrinkage

c) your datafiles end with allocated space - they might have free space - but the last bit of them has something allocated in it, for example:


XffffffffffffffffffffffffffffffffffffffX

is that were your file and X = allocated data space and f = free space - you would not be able to shrink that file, on the other hand:

XffffffffffffffffXffffffffffffffffffffffffff

we would be able to shrink that file down to:

XffffffffffffffffX

but no further. In this case, a reorganization would have to take place in order to reclaim space - however this will ALWAYS require yet even more free space first.







have your DBA (not you, definitely not you) look around for trace/alert/etc type of files that could be removed safely. These can take a surprising amount of storage.

after runing this script not getting enough space and Disk space is about 100%

Muhammad Irfan, May 31, 2013 - 6:23 pm UTC

Thanks for prompt reply.

First of all i want to tell you that this is not our production sever this the test server with same specification and same partition size. this is replica of production server. we have restore the latest backup and now the same amount is getting use be database.

i have check purge recyclebin as mentioned above in this blog. but still nothing happened. i also checked recodo files that are also 20MB.

regards,
Irfan
Tom Kyte
May 31, 2013 - 6:29 pm UTC

what are recodo files?

look at:
http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551402703363001567

that'll tell you if you have any free space that is possible to be reclaimed ever.


if you have free space that COULD be reclaimed (if system or sysaux have free space, ignore it, you are not getting that back and they are small anyway), then involve the DBA as you would need to reorganize to get anything back

which means.... get your DBA involved....

and get some more disk since the only way to reorganize will involve NEEDING MORE SPACE to do the reorganization with. (which effectively means - start looking elsewhere, you are not getting space back from this database until you have ..... more free space in the first place)

after runing this script not getting enough space and Disk space is about 100%

Muhammad Irfan, May 31, 2013 - 6:27 pm UTC

Also i want to ask one thing if smallest Poss is 22,297 and current size is also the same how can we get the space.


Smallest
Size Current Poss.
FILE_NAME Poss. Size Savings
-------------------------------------------------- -------- -------- --------
/u02/oradata/PROD/axdata1.dbf 22,296 22,297 1
Tom Kyte
May 31, 2013 - 6:28 pm UTC

you are talking about 1mb or less????? seriously?

after runing this script not getting enough space and Disk space is about 100%

Muhammad Irfan, May 31, 2013 - 6:35 pm UTC

yes you can check the output of script above i have posted.

thanks
Tom Kyte
May 31, 2013 - 7:12 pm UTC

ummmm, you didn't show us what free space you have.


you showed us that you cannot shrink your space, that is all so far.


you have not shown us that there is any space to be reclaimed at all. If you think you did, repost that and I'll tell you why that isn't showing us what free space you might have.

and what are recodo files???

you might be looking for hundreds/thousands of files that are a few kbytes to a few mbytes in size. Nothing magic about 20mb. Your DBA would actually know what to look for. You are dangerous poking about here - you seriously might erase something we actually need thinking we don't.


after runing this script not getting enough space and Disk space is about 100%

Muhammad Irfan, May 31, 2013 - 7:37 pm UTC

As right now i am not able to remote my server but there are 20GB available in / and 12GB available in /opt 
the /u02/oradata where oracle database it is 28GB partition and 3GB is currently free.


Here is the output of Script.
right now we dont have DBA this is why i am working on test server and asking for help.

SQL> @/export/home/maxshrink.sql

VALUE
-------------------------------------------------------------
----------------------------------------------------------------------
8192


                                                   Smallest
                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
-------------------------------------------------- -------- -------- --------
/u02/oradata/PROD/axdata1.dbf                        22,296   22,297        1
/u02/oradata/PROD/system01.dbf                          566      567        1
/u02/oradata/PROD/sysaux01.dbf                          529      531        2
/u02/oradata/PROD/undotbs01.dbf                          85      107       22
/u02/oradata/PROD/users01.dbf                             1        2        1
/u02/oradata/PROD/AXDATA.dbf                              1        1        0
/u02/oradata/PROD/AXINDEX.dbf                             1        1        0
                                                                     --------
sum                                                                        27

7 rows selected.


CMD
---------------------------------------------------------------------------
alter database datafile '/u02/oradata/PROD/axdata1.dbf' resize 22296m;
alter database datafile '/u02/oradata/PROD/system01.dbf' resize 566m;
alter database datafile '/u02/oradata/PROD/sysaux01.dbf' resize 529m;
alter database datafile '/u02/oradata/PROD/undotbs01.dbf' resize 85m;
alter database datafile '/u02/oradata/PROD/users01.dbf' resize 1m;

SQL>

Tom Kyte
May 31, 2013 - 7:43 pm UTC

that isn't the script I've asked for the output from. Please re-read what I posted above, there is a script that will show you what, if any, freespace you have.


if you have 3gb free, what is the problem??? You are not anywhere near 100%. As long as your datafiles are not autoextned (another bit of data that we would know about if you would run the script I asked you to above - to show free space...), the database isn't going to grow or anything.



why bother?

David, May 31, 2013 - 8:36 pm UTC

He is giving you one star rating. I don't know on what basis he is rating you. Why do you answer him? I really think he is wasting your time.

I'm sorry to bother you,

Muhammad Irfan, June 01, 2013 - 6:17 am UTC

I a thankful to you for answering my nontechnical question. I also appreciated you spend your precious time. I hope this is my last question,

Thanks again.
Irfan

Here is the output of free.sql script
%
Tablespace Name KBytes Used Free Used Largest
---------------- ------------ ------------ ------------ ------ ------------
AXDATA 1,024 64 960 6.3 960
AXDATA1 23,036,928 23,035,456 1,472 100.0 960
AXINDEX 1,024 64 960 6.3 960
SYSAUX 548,864 526,720 22,144 96.0 6,720
SYSTEM 588,800 578,496 10,304 98.3 10,176
UNDOTBS1 253,952 55,296 198,656 21.8 192,448
USERS 1,024 448 576 43.8 576
------------ ------------ ------------
sum 24,431,616 24,196,544 235,072

7 rows selected.
Tom Kyte
June 01, 2013 - 2:40 pm UTC

you can shrink your undo tablespace, but that is about it.

axdata is tiny, not worth any effort.
axdata1 is full, nothing to be done there.
axindex is tiny, nothing to be done there
sysaux and system - you are not going to touch, there is only 33mb there anyway, noise...
undotbs1 - the only thing worth even considering looking at
users - noise, 0.5mb free, not worth any effort


So, you can get back around 200mb - that's it. A drop in the bucket, not going to be very meaningful and it might come right back anyway if that database needs an undo tablespf of 250mb (meaning, you are probably doing this work to get space back right this instant, but in a day or two - it'll be gone again, sort of self defeating)



You can do something similar to the following:

ops$tkyte%ORA11GR2> @free 1
old  30: order by &1
new  30: order by 1

                                                                                                 %
                                                                %                   MaxPoss    Max
Tablespace Name           KBytes         Used         Free   Used      Largest       Kbytes   Used
------------------- ------------ ------------ ------------ ------ ------------ ------------ ------
*a EXAMPLE               352,256      310,784       41,472   88.2       32,192   33,554,416    1.0
*a PTEST                 155,648        1,024      154,624     .7        7,872   33,554,416     .5
*a SYSAUX              1,193,984    1,084,736      109,248   90.9       13,568   33,554,416    3.6
*a USERS                  48,640        1,536       47,104    3.2       12,288   33,554,416     .1
*m SYSTEM                903,296      780,736      122,560   86.4       99,008   33,554,416    2.7
*m TMP                    12,280       12,280            0  100.0            0   33,554,416     .0
*m UNDOTBS2              548,864       24,832      524,032    4.5      494,592   33,554,416    1.6
                    ------------ ------------ ------------
sum                    3,214,968    2,215,928      999,040

7 rows selected.

<b>Note that my undo tablespace is about 1/2gb, we can make it smaller</b>

ops$tkyte%ORA11GR2> create undo tablespace new_undo_tbs datafile size 40m autoextend on next 1m;
Tablespace created.

<b> create a new small one, you might have to specify the datafile name, you might want to use maxsize to limit its growth as well, you decide that...</b>

ops$tkyte%ORA11GR2> alter system set undo_tablespace = new_undo_tbs;
System altered.

<b>switchover to the new smaller tablespace....</b>

ops$tkyte%ORA11GR2> connect /
Connected.

<b>wait a bit, give any existing transactions at the very least the ability to complete.....</b>

ops$tkyte%ORA11GR2> drop tablespace undotbs2;
Tablespace dropped.

<b>get rid of old tablespace...</b>


ops$tkyte%ORA11GR2> @free 1
old  30: order by &1
new  30: order by 1

                                                                                                 %
                                                                %                   MaxPoss    Max
Tablespace Name           KBytes         Used         Free   Used      Largest       Kbytes   Used
------------------- ------------ ------------ ------------ ------ ------------ ------------ ------
*a EXAMPLE               352,256      310,784       41,472   88.2       32,192   33,554,416    1.0
*a PTEST                 155,648        1,024      154,624     .7        7,872   33,554,416     .5
*a SYSAUX              1,193,984    1,084,736      109,248   90.9       13,568   33,554,416    3.6
*a USERS                  48,640        1,536       47,104    3.2       12,288   33,554,416     .1
*m NEW_UNDO_TBS           40,960        2,304       38,656    5.6       38,656   33,554,416     .1
*m SYSTEM                903,296      780,736      122,560   86.4       99,008   33,554,416    2.7
*m TMP                    12,280       12,280            0  100.0            0   33,554,416     .0
                    ------------ ------------ ------------
sum                    2,707,064    2,193,400      513,664

7 rows selected.

Need you help again

A reader, June 20, 2013 - 9:17 am UTC

Hi,
I Hope you are doing fine. I need your Help again. As we are running Axapta 3.0 for this data and I use clean database log from Axapta console and I am now able to get free space when I run @free script I get the following output.

SQL>@free 1
FREE SPACE AVAILABLE IN TABLESPACES

                         Total
Tablespace            Size(Mb) Total Free(MB)  % Free Max Free(Kb) No Of Ext.
-------------------- --------- -------------- ------- ------------ ----------
SYSTEM                     566              1     .19          960          2
SYSAUX                     532             10    1.79        4,416         15
AXDATA1                 23,217          2,846   12.26      642,048        167
UNDOTBS1                    10              4   38.75          896         14
USERS                        1              1   56.25          576          1
AXINDEX                      1              1   93.75          960          1
AXDATA                       1              1   93.75          960          1

7 rows selected.

SQL>

when I run the shrink script it is not giving me any command to reduce the table space. And result the same old one. 
I would be really thankful if you help me if there is any possibility to reclaim the space from database.
Regards,
Irfan

Tom Kyte
June 20, 2013 - 2:37 pm UTC

I have no clue what axapta is or does.

read the original answer above, it explains exactly why you might have free space in a tablespace -yet not be able to shrink a datafile. In short (cut and paste here from the original answer:)

There is a chance you can shrink some of the datafiles (see below -- it'll tell 
you if you can and will generate the alter statements if they apply).  <b>We can 
only shrink files back to their high water mark -- if there is an extent way 
out at the end of a file -- we'll not be able to shrink it.</b>



so, while you might have 2,846mb of free space in the datafile, it must be in the "middle" of the file. That is - your file looks like:


xxxxxxFFFFxFxxxxFxxxxxFFFFFFFFFFFFFFFFFFFFFx


where x = allocated, used space and F= free space. That last little x out at the end of the file would make it impossible to shrink the file until x is turned into an F (by dropping the segment, or by moving the segment using alter table T move or alter index I rebuild or dbms_redefinition and so on)


thank you very much

shibu, June 26, 2013 - 12:16 pm UTC

thank you very much tom... i was struggling with space problem for the past three days.. really helpful.. thank you very much

The mechanism for choosing the destination block id

Babak Tourani, November 03, 2013 - 9:47 am UTC

Hi,

I've developed a script for shrinking datafiles, but I've come across a weird (to my small mind, of course!) issue:

When I shrink, I assume a specific threshold block ID, and I attempt to move segments which are located in higher block IDs, but at times although the (for example) ALTER TABLE MOVE statement is successful and the segment is actually moved in the same datafile, it is not moved below my assumed threshold ID. It's just moved a mere extent or two lower!

The strange thing is: if I move the segment to another datafile, and back to the original one, it's usually moved below the threshold block id!

Apart from my observation in the previous sentence being right or wrong, how does Oracle choose the block id that the segment is about to be moved to?

Regards,
Tom Kyte
November 07, 2013 - 2:56 pm UTC

space is typically allocated in a round robin fashion across datafiles, and the placement within a datafile is not really guessable. Any extent that is free and open will do.

Your best bet is to move the objects out of that tablespace, the one that the datafile belongs to, and then shrink.

actually- moving everything out into a new tablespace that has autoextend datafiles and dropping the old tablespace is the surest way to have the (temporarily) smallest files possible.

very well

A reader, July 16, 2014 - 1:31 am UTC

I found it useful for me, when I need shrink DB data files which blocks occupy end of them