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.
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.
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 ?
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.
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.
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?
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.
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
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 ?
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.
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?
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 Ts? 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.
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;
/
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.
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 cant 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
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
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 dont 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
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
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
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
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).
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
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?
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?
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
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.
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!!!
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 ?
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
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-----
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.
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.
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.
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.
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
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 ?
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.
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>
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?
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.
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?
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?
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)
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
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
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>
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
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?)
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?
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
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
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
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
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
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.
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
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.
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
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
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.
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>
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
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
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
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>
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.
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
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,
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