Thanks, Tom
Charlotte, September 21, 2001 - 11:43 am UTC
This is great!
Amazing script
Saeed Ahmed., August 10, 2003 - 4:33 pm UTC
I liked this scripts very much, at toms site ever read leads to more thrist and to learn more about the Oracle stuffs.
May God guide TOM and his team to the straight path.
Can I shrink datafiles in LMT?
Jim Dickson, April 15, 2004 - 5:34 am UTC
I recall using the above script to shrink data dictionary manaaged datafiles in 8i.
Can i use same technique on 9iR2 datafiles in LMT?
eg
CREATE TABLESPACE indiv_copy_data DATAFILE
'/oradata/${ORACLE_SID}/indiv_copy_data_01.dbf'
SIZE 101M AUTOEXTEND ON NEXT 100M MAXSIZE 4001M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M
SEGMENT SPACE MANAGEMENT AUTO
NOLOGGING;
I know i should try myself but only have production databases since running out of disk space, hence problem :)
April 15, 2004 - 8:32 am UTC
sure, it does LMTS as well.
Tablespace "shrink" after import, due to HWM ?
john, August 04, 2004 - 6:43 am UTC
I run the script and found one of the tablespace with this for my existing db:
Smallest
Size Current Poss.
FILE_NAME Poss. Size Savings
-------------------------------------------------- -------- -------- --------
/u02/oradata/data_file1.dbf 4,426 4,427 1
But when I export and import the same DB to another instance (from 8.1.6 to 9i), I found the "new" tablespace/datafile size is very small (from 4.5G to less than 500M !!):
My question is: What could be the problem here ? How to find out the objects "taken up" the space in the old tablespace and How to "clean it" or shrink the tablespace or datafile size to reclaim some disk space ?
many thanks.
August 04, 2004 - 10:43 am UTC
that doesn't show HWM problems, they are a *possible* cause -- but not necessarily the problem here.
files hold extents. the "last extent" in the file is the files current "smallest possible size".
that file might have had 1 extent in it, that extent could have been way out at the "end" of the file, that one extent could have been 100k in size. That one extent would prevent you from shrinking that file down.
you could have a single extent in there, taking virtually no space, that would prevent a "shrink"
you can use dba_extents to find the "last extent" in the file:
ops$tkyte@ORA9IR2> select owner,
2 segment_name,
3 segment_type,
4 (select file_name
5 from dba_data_files b
6 where b.file_id = a.file_id
7 and b.relative_fno = b.relative_fno ) file_name
8 from (select owner, segment_name, segment_type, file_id, relative_fno, block_id,
9 max(block_id) over (partition by file_id, relative_fno) max_block_id
10 from dba_extents
11 ) a
12 where block_id = max_block_id
13 /
OWNER SEGMENT_NAME
------------------------------ ------------------------------
SEGMENT_TYPE FILE_NAME
------------------ ------------------------------
SYS C_TS#
CLUSTER /home/ora9ir2/oradata/ora9ir2/
system01.dbf
SYS _SYSSMU8$
TYPE2 UNDO /home/ora9ir2/oradata/ora9ir2/
o1_mf_undo_new_046rbnhr_.dbf
OLAPSYS CWM2$AW_MEASUREMAP_PK
INDEX /home/ora9ir2/oradata/ora9ir2/
cwmlite01.dbf
WKSYS SYS_C001847
INDEX /home/ora9ir2/oradata/ora9ir2/
drsys01.dbf
QS_CS SYS_IOT_TOP_30414
INDEX /home/ora9ir2/oradata/ora9ir2/
example01.dbf
ODM_MTR EIGHT_CLOUDS_APPLY_UNBINNED
TABLE /home/ora9ir2/oradata/ora9ir2/
odm01.dbf
IOT_HEAP SYS_IOT_TOP_34701
INDEX /home/ora9ir2/oradata/ora9ir2/
tools01.dbf
OPS$TKYTE T
TABLE /home/ora9ir2/oradata/ora9ir2/
users01.dbf
XDB SYS_C001697
INDEX /home/ora9ir2/oradata/ora9ir2/
xdb01.dbf
SYS _SYSSMU9$
TYPE2 UNDO /home/ora9ir2/oradata/ora9ir2/
o1_mf_undo_tes_0jxfk77w_.dbf
10 rows selected.
those would be the segments that would prevent a file shrink - you could "alter move" or "alter rebuild" a table/index to "move it".
Another advice about this ?
Sven Bleckwedel, August 06, 2004 - 9:26 am UTC
Hi Tom,
Could you give more advice about this ? I´m asking a bit more because some objects are in some "special" area (system, undo, etc...). Any warnings before doing something like "defragmenting" these objects in these places ?
OWNER SEGMENT_NAME SEGMENT_TYPE FILE_NAME
------ ------------ ------------ ------------------------------
SYS C_TS# CLUSTER /home/ora9ir2/oradata/ora9ir2/
system01.dbf
SYS _SYSSMU8$ TYPE2 UNDO /home/ora9ir2/oradata/ora9ir2/
o1_mf_undo_new_046rbnhr_.dbf
...
SYS _SYSSMU9$ TYPE2 UNDO /home/ora9ir2/oradata/ora9ir2/
o1_mf_undo_tes_0jxfk77w_.dbf
Tks in Adv,
Sven
August 06, 2004 - 10:14 am UTC
don't touch or do anything to system. (you cannot)
for undo, if you want to shrink it simply:
a) create a new one
b) alter system set undo_tablespace = newone;
c) wait for any active transactions using the old to complete
d) drop the old one.
What is relative_fno
Logan Palanisamy, August 06, 2004 - 7:05 pm UTC
Tom,
In your query to identify the extent just below the HWM in each data file, why are you partitioning and joining by both the file_id and relative_fno? Isn't the file_id unique enough? What is relative_fno anyway? What is it used for? How does it help?
I have an Oracle 11i db with nearly 400 data files. All of them have both the file_id and relative_fno exactly the same.
ops$tkyte@ORA9IR2> select owner,
2 segment_name,
3 segment_type,
4 (select file_name
5 from dba_data_files b
6 where b.file_id = a.file_id
7 and b.relative_fno = b.relative_fno ) file_name
8 from (select owner, segment_name, segment_type, file_id, relative_fno,
block_id,
9 max(block_id) over (partition by file_id, relative_fno)
max_block_id
10 from dba_extents
11 ) a
12 where block_id = max_block_id
13 /
Thanks as always
August 07, 2004 - 10:00 am UTC
they only different when you exceed something like 1022 datafiles.
the file_id should be sufficient, unique.
Datafile HWM
J B, August 12, 2006 - 4:30 am UTC
Hi tom,
We have some tablespaces for which the datafiles have been already shrunk to their respective HWM's.
But when queried from the dba_free_space it is noticed that these tablespaces have huge logical free space with in the tablespaces. This free space gets to us since we drop the old partitions for the tables belonging to these tablespaces.
How can we get this space reclaimed, and get the file(s) lying at the OS even further shrunk so that the true picture of logical & physical mapping can be seen.
I know, we can move the tables and rebuild the indexes on to a different tablespace and then completely drop off the existing tablespace definition including contents and datafiles.
This will give us the exact mapping of OS physical file SIZe and the logical space consumed with in the file.
Can we issue the MOVE command and REBUILD for the index within the same tablespace. Will the blocks at the HWM move down where the free space is available and allow us to reclaim the space at the physical file level.
Thanks
August 12, 2006 - 7:52 pm UTC
if you drop old partitions, I'm guessing you create new right?
if so, are you saying the space is not being reused??
(you have to move anything that is currently at the "end" of the file to the "front", you'd have to look for the extent allocated furthest out in the file, find the segment it belongs to and move it. if you use locally managed tablespaces - they tend to move to the "front" of the file
but beware, you could end up just making the file bigger!!!!
simple example - datafile, 3mb+64k in size. 3-1mb extents.
first extents - segment "one"
second extent free
third extent - segment "one"
you alter segment "one" move, temporarily you'll have:
first extent -segment "one"
second extent - "temporary extent for segment one"
third extent - segment "one"
new fourth extent - "temporary extent for segment one"
after the move it complete:
first extent - free
2nd - segment one
3rd - free
4th - segment one
compacting down to every last bit and byte isn't practical in most cases.
Suggestions Required
J B, August 13, 2006 - 2:59 am UTC
Hi Tom,
Yes, we drop the old partitions and also create new partitions for the same table on a daily basis.
As an example i would like to explain what happened today:
Before dropping of partitions the free space avaialble for a given tablespace was and that too for a given table since there is 1:1 mapping for this table to the tablespace. This is a LMT and the table is filled in by using INSERT /*+ APPEND */ hint thus always writing above the HWM for the datafiles associated with them..
Tablespace_name Free Space
TS_ADAM_DENORM_DATA 19444.3125 M
TS_ADAM_DENORM_INDEX 17.6875 M
After dropping of partitions, since there is a global PK index it became necessary for us to rebuild it to make it in USABLE state, and astonishingly the file sizes grew and the OS reported %FULL to be more than what it had before. However the logical space with in the tablespace grew from
17M to 20G.
This is what we don't desire!
Tablespace_name Free Space
TS_ADAM_DENORM_DATA 26868.3125 M
TS_ADAM_DENORM_INDEX 20012.3125 M
What we do in order to avoid this is create fresh new tablespace and MOVE the data to the new tablespace so created and then rebuild the INDEX based on the moved data.
This ensures us that we have reclaimed the space and we are ready to drop the old data and index tablespace(s). Drop tablespace OLD_DATA including contents and datafiles ensures releasing worth 20G of data at the OS level.
Is there no way that the tablespace free extents on the start of the file can be filled by those which are filled at the end of the file and then we can resize the file to a size less than what the file size is as of current, which essentially means lowering down the HWM for the datafiles.
This has become a daily routine to be carried out and thus wastes quite a lot of time.
Thanks
August 13, 2006 - 9:26 am UTC
why don't you maintain the indexes rather than rebuild?
but I cannot imagine the index tablespace jumping from 17m to 20gig, unless something "non obvious" is going on here that you have neglected to mention.
provide both sides of the equation here - the used and the "not used" allocations.
You are showing us free space - one expects free space perhaps to "go up" after you drop lots of data and then subsequently rebuild the global indexes on the data you just dropped (eg: you freed up tons of space)
Your subsequent "let's add more data" operations would reuse this space - would they not???
Feedback
J B, August 13, 2006 - 10:03 am UTC
Maintaining the Global indexes is more time consuming so we rather rebuild the "UNUSABLE" index.
Had the space been reused there would n't have been any issue but since we fill the table using insert /*+append */ it just writes above the HWM ceiling and doesn't use the available free space.
The Free space jumped from 17M to 20G, may be rebuild also requires temporary segment with in the same tablespace. Once the segment gets created it drops the temporary segment but the space is being shown as "FREE SPACE" available to the tablespace.
Is there no way to get the data lying at the HWM to get adjusted to the first free available extent so that the file sizes at the OS level can be resized and shrunk appropriately .
Thanks
August 13, 2006 - 3:42 pm UTC
it might take longer to "maintain indexes"..... for the drop to happen but you suffer no downtime (so it is "zero" in duration really when you think about it...)
the insert /*+ append */ bit it about space allocated to a segment - NOT SPACE IN DBA_FREE_SPACE. That is precisely and exactly the space insert append will tend to use!!!!!!
so, how do you measure free space - insert append would definitely use this to allcoate new extents.
and indexes - they don't count when it comes to append and "write above the high water mark", that is only about tables
(I have to confess that I'm very confused now - not sure what you are measuring)
Datafile Shrink not happening
hitesh bajaj, October 19, 2006 - 7:05 am UTC
Hi Tom,
We have a table (R_DATA) which is allocated to a tablespace(TS_R_DATA) and no other segment is allocated to this tablespace.
This is a partitioned table and we get rid of the oldest partition and add new partition on a daily basis.
Today we used the online shrink to compact the segment and lower down its HWM. This means all the data has been compacted and the datafile is ready to shrink.
However, strangely we found that the Savings to shrink that file is just 1M where as the logical free space available with in the tablespace is 96G.
What needs to be done in order to shrink the file size belonging to this tablespace so as to release some space at the file system level.
Tblspace Name Total Size Free MB Used MB
TS_R_DATA 231139.18 96,215.25 134,923.93
FNM FILE_SIZE_IN_MB HWM_MB SAVING_MB
---------------------------------------------------------------------- --------------- ---------- ----------
/fs-b01-a/databases/r-16.dbf 6154.0625 6154.0625 0
/fs-b01-b/databases/r-17.dbf 6418 6417.0625 .9375
/fs-b01-c/databases/r-18.dbf 6291 6290.0625 .9375
/fs-a01-b/databases/r-20.dbf 6276 6275.0625 .9375
/fs-a01-c/databases/r-21.dbf 6334 6333.0625 .9375
/fs-b01-b/databases/r-23.dbf 6326 6325.0625 .9375
/fs-b01-c/databases/r-24.dbf 6198 6197.0625 .9375
/fs-a01-a/databases/r-19.dbf 5064 5063.0625 .9375
/fs-b01-a/databases/r-22.dbf 5048 5047.0625 .9375
/fs-a01-a/databases/r-25.dbf 4686 4685.0625 .9375
/fs-a01-c/databases/r-27.dbf 3910 3909.0625 .9375
/fs-b01-a/databases/r-28.dbf 4606 4605.0625 .9375
/fs-b01-b/databases/r-29.dbf 3638 3637.0625 .9375
/fs-b01-c/databases/r-30.dbf 3510 3509.0625 .9375
/fs-a01-a/databases/r-13.dbf 4777 4776.0625 .9375
/fs-a01-a/databases/r-01.dbf 11717 11716.0625 .9375
/fs-a01-a/databases/r-07.dbf 9685 9684.0625 .9375
/fs-b01-a/databases/r-10.dbf 9490 9489.0625 .9375
/fs-a01-b/databases/r-02.dbf 12901 12854.0625 46.9375
/fs-b01-c/databases/r-12.dbf 9951 9926.0625 24.9375
/fs-a01-c/databases/r-09.dbf 9799 9798.0625 .9375
/fs-b01-b/databases/r-11.dbf 9973 9972.0625 .9375
/fs-a01-c/databases/r-03.dbf 12965 12934.0625 30.9375
/fs-b01-a/databases/r-04.dbf 12619 12608.0625 10.9375
/fs-b01-b/databases/r-05.dbf 12949.0625 12893.0625 56
/fs-b01-c/databases/r-06.dbf 13076.0625 13045.0625 31
/fs-a01-b/databases/r-08.dbf 9624 9623.0625 .9375
/fs-a01-b/databases/r-14.dbf 6626 6625.0625 .9375
/fs-a01-c/databases/r-15.dbf 6554 6553.0625 .9375
/fs-a01-b/databases/r-26.dbf 3974 3973.0625 .9375
October 19, 2006 - 8:20 am UTC
read this entire page please, we've covered this.
there are extents at the "end" of the file, the file can only shrink down to the last allocated extent.
File not Shrinking
Hitesh Bajaj, October 23, 2006 - 2:39 am UTC
Hi Tom,
I understand that there might be some extent allocated to the segment which is not allowing us to resize the datafile at the OS level.
But for the online segment shrink what I understand is that, from the end of the segment the rows will be deleted (last extent) and will be inserted back at the top of the segment where the free space is avaiable.
This means that after the segment gets shrinked and compacted the HWM for the table gets resetted and simultaneously the HWM for the OS files should also be able to shrink, since there is only a single table in the defined tablespace.
Your inputs to this will be appreciated.
Thanks
October 23, 2006 - 10:10 am UTC
no it doesn't.
what if in the file from "beginning" to "end" you have:
begin of file middle of file end of file
last_extent middle_extent first_extent
when you are done, your file cannot be shrunk at all - the first extent is as the "end" of the datafile!
Got your point
Hitesh Bajaj, October 23, 2006 - 10:41 am UTC
Hi Tom,
I understand your point, is there any way to shrink the datafile(s) associated with the tablespace.
We have to allocate the free space somewhere else out of this 96G used by this tablespace.
I know that creation of a new tablespace and then using alter table table_name MOVE command would do my job, but that invalidates all the indexes associated and its not an online solution.
Please advise.
October 23, 2006 - 12:35 pm UTC
create a new tablespace, move objects into it, then you can drop this one entirely, and then you can rename new tablespace back if you want (10g - rename tablespace)
dbms_redefinition makes this "online"
you can of course query dba_extents to see the "extent" of the problem (haha). You can see how many extents are between you and the "end of the file" and you might be able to seriously limit the number of objects (segments) you move - it might just take one move to free up a large gap of free space at the end of the file.