The best trick I ever saw May 18, 2001 - 4am US/Eastern
Reviewer: Stelios Karkazis from 30 STROFILIOU STR. 14561 KIFISIA - ATHENS - GREECE
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 !!!
 November 29, 2001 - 8am US/Eastern
Reviewer: Jiten Padhiar from Englan (UK)
I'm not a DBA, but the results proved very useful.
Nice one.
System tablespace grew... January 6, 2003 - 10pm US/Eastern
Reviewer: Rory B. Concepcion from Philippines
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.
Followup January 7, 2003 - 6am US/Eastern:
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 ... January 8, 2003 - 1am US/Eastern
Reviewer: Rory B. COncepcion from Philippines
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 ? January 8, 2003 - 8am US/Eastern
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.
Alternative Solution ? January 8, 2003 - 4pm US/Eastern
Reviewer: Johan Snyman from Johannesburg South Africa
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 ?
Followup January 8, 2003 - 6pm US/Eastern:
yes, you can do that - but you'll need to carry over things like
users
roles
public synonyms
grants
yourself.
A Very Useful Stuff February 18, 2003 - 7am US/Eastern
Reviewer: Ashok Shinde from Pune, India
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.
Followup February 18, 2003 - 7am US/Eastern:
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 February 19, 2003 - 4am US/Eastern
Reviewer: Ashok Shinde from Pune, India
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 March 25, 2003 - 12pm US/Eastern
Reviewer: Jay Earle from New Brunswick, Canada
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.
Followup March 25, 2003 - 12pm US/Eastern:
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 May 3, 2003 - 9am US/Eastern
Reviewer: reader
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 May 23, 2004 - 1am US/Eastern
Reviewer: Sikandar Hayat from Pindi Bhattian - Pakistan
We can also use exp/imp to remove fragmentation but trying to get info if we can get the same from
RMAN?
Followup May 23, 2004 - 9am US/Eastern:
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 July 28, 2004 - 5am US/Eastern
Reviewer: Yogesh from Pune, India
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.
Followup July 28, 2004 - 8am US/Eastern:
yes, it will "be fine"
Drop table September 7, 2004 - 9am US/Eastern
Reviewer: Yogesh from Pune, India
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
Followup September 7, 2004 - 10am US/Eastern:
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 September 7, 2004 - 10am US/Eastern
Reviewer: Yogesh from Pune, India
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 ?
Followup September 7, 2004 - 11am US/Eastern:
why bother putting them back? what matter the tablespace name???
Reducing the tablespace September 22, 2004 - 12pm US/Eastern
Reviewer: Jeff
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.
Followup September 22, 2004 - 1pm US/Eastern:
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.
September 22, 2004 - 2pm US/Eastern
Reviewer: Jeff
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?
Followup September 22, 2004 - 3pm US/Eastern:
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 October 12, 2004 - 5am US/Eastern
Reviewer: Yogesh from Pune, India
As mentioned in above reply, if following is the pattern of the datafile
XXXXXffTTTTTXXXXXXXXXfffTTTTXXXTTTTf
Is there any way to identify the T’s? I mean Can we identify the tables at the end of datafile? If
we can identify these objects we can rebuild them in some other table space and free more space.
Followup October 12, 2004 - 8am US/Eastern:
sure, query dba_extents for the maximum block id by file.
look at those objects -- they are the ones "at the end"
October 12, 2004 - 8am US/Eastern
Reviewer: A reader
October 13, 2004 - 9pm US/Eastern
Reviewer: phil from Sydney
alter users? November 16, 2004 - 5pm US/Eastern
Reviewer: chet from Gainesville, FL
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;
/
Followup November 16, 2004 - 11pm US/Eastern:
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 January 19, 2005 - 8am US/Eastern
Reviewer: A reader
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 March 4, 2006 - 6am US/Eastern
Reviewer: Sairam from Bangelore,INDIA
Hi Tom,
After running the ur script, result as below.
alter database datafile '/ss02/prod/proddata1/applsysd01.dbf' resize 26m;
alter database datafile '/ss02/prod/proddata/gld01.dbf' resize 1m;
alter database datafile '/ss01/prod/proddata/invx01.dbf' resize 14m;
alter database datafile '/ss02/prod/proddata1/undo01.dbf' resize 3m;
alter database datafile '/ss02/prod/proddata/usrd01.dbf' resize 2m;
alter database datafile '/ss02/prod/proddata/emsprod.dbf' resize 1m;
But when I am executing these statements oracle is giving error like "ORA-03297: file contains used
data beyond requested RESIZE value"
Its very critical issue for me, Mainly I need to reduce invx01.dbf data file, which is occupying
15GB but data occupied around 4GB. I m unable reduce this data file.
As per ur discussions, we have solution, i.e. we can transfer data from this particular table space
to other and again transfer to original one.
But my problem is this is INVX (index tablespace) of 45GB and I can’t shutdown the PRODUCTION
system, because its 24/7 support.
If I do online, it takes more time as well as performance of system will reduce.
Please reply me with accurate solution.
Thanks in advance
sairam
Followup March 4, 2006 - 7am US/Eastern:
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 March 5, 2006 - 2am US/Eastern
Reviewer: Sairam from Bangalore, INDIA
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
Followup March 5, 2006 - 1pm US/Eastern:
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 March 6, 2006 - 1am US/Eastern
Reviewer: Sairam from Bangalore, INDIA
HI Tom,
As per your query, we are getting the result as follows.
Query:
SELECT 'alter database datafile '''||file_name||''' resize ' ||
CEIL( (NVL(hwm,1)*&blksize)/1024/1024 ) || 'm;' cmd
FROM dba_data_files a,
( SELECT file_id, MAX(block_id+blocks-1) hwm
FROM dba_extents
GROUP BY file_id ) b
WHERE a.file_id = b.file_id(+)
AND A.TABLESPACE_NAME='INVX'
AND CEIL( blocks*&blksize/1024/1024) -
CEIL( (NVL(hwm,1)*&blksize)/1024/1024 ) > 0
Output:
alter database datafile '/ss01/prod/proddata/invx01.dbf' resize 14m;
You can see the following output as a proof of total_space and free_space occupied by particular
invx01.dbf file.
SELECT SUM(df.bytes) TOTAL_SPACE,SUM(fs.bytes) FREE_SPACE
FROM dba_free_space fs, dba_data_files df
WHERE df.TABLESPACE_NAME='INVX'
AND df.file_name='/ss01/prod/proddata/invx01.dbf'
TOTAL_SPACE FREE_SPACE
11,673,600,000,000.00 46,277,976,064.00
I don’t know why its coming like that.
And one more thing I want to confirm what the process (plz see my previous conversation) I followed
for removing invx01.dbf is it right method or not.
Please confirm this, So I can proceed further in Production Instance.
Thanks in advance,
Sairam
Followup March 6, 2006 - 8am US/Eastern:
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:
http://asktom.oracle.com/~tkyte/Misc/free.html
plz? what do German Postal codes have to do with this?
PLZ March 7, 2006 - 4pm US/Eastern
Reviewer: Marcio Portes
Had to look it up ;)
ZIP Used in: Germany, Austria, Switzerland Postleitzahl
http://www.top500.de/g0039909.htm
In German PLZ is the abbreviation for "Postleitzahl", which is the German word for "zip code".
http://en.wikipedia.org/wiki/Plz
Followup March 9, 2006 - 11am US/Eastern:
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 March 8, 2006 - 1am US/Eastern
Reviewer: Sairam from Bangalore, INDIA
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
Followup March 9, 2006 - 12pm US/Eastern:
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 March 16, 2006 - 1pm US/Eastern
Reviewer: Mark Jaeger from Chicago
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
Followup March 16, 2006 - 2pm US/Eastern:
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 March 19, 2006 - 8pm US/Eastern
Reviewer: A reader
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).
Followup March 20, 2006 - 7am US/Eastern:
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 August 23, 2006 - 4pm US/Eastern
Reviewer: Su Baba
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
Followup August 27, 2006 - 6pm US/Eastern:
not really, the only thing you can do is:
a) find the thing at the "end" (the last X). that is pretty easy.
http://www.oracle.com/technology/oramag/oracle/04-sep/o54asktom.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.
August 28, 2006 - 12pm US/Eastern
Reviewer: Su Baba
Would moving data blocks to a different tablespace and then move them back again be a valid
approach for the above problem?
Followup August 28, 2006 - 12pm US/Eastern:
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)
August 28, 2006 - 12pm US/Eastern
Reviewer: Su Baba
To keep the original tablespace name. Is there anyway to rename a tablespace?
Followup August 28, 2006 - 1pm US/Eastern:
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 September 18, 2006 - 7am US/Eastern
Reviewer: abz
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
Followup September 18, 2006 - 1pm US/Eastern:
not going to happen.
you do not do things like that to SYS owned objects, not permitted.
any workaround? September 19, 2006 - 8am US/Eastern
Reviewer: abz
Ok if there is no proper way to do it, can you please
suggest any work around/tactic to achieve the same
effect.
Followup September 19, 2006 - 2pm US/Eastern:
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 February 22, 2007 - 11am US/Eastern
Reviewer: Richard from Hertfordshire, UK
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!!!
Followup February 22, 2007 - 11am US/Eastern:
indeed!
Shrinking below HWM September 22, 2007 - 6am US/Eastern
Reviewer: Yogesh Purabiya from India
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 ?
Followup September 26, 2007 - 1pm US/Eastern:
because there is some extent that exists above 50m....
otn.oracle.com -> documentation.
alter table move October 10, 2007 - 12pm US/Eastern
Reviewer: butterfly from India
"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.
June 9, 2008 - 2pm US/Eastern
Reviewer: Ricardinho
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
Followup June 9, 2008 - 3pm US/Eastern:
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.
June 10, 2008 - 2pm US/Eastern
Reviewer: Ricardinho
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-----
Followup June 10, 2008 - 3pm US/Eastern:
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.
|