Reduce the size of Tablespace
A reader, June 18, 2003 - 10:06 am UTC
Hi Tom,
I have the same problem that our friend had before. The issue is :
I wanted to create a new database on Oracle 8.1.7. I usually copy one of the databases on that server, then create a new instance and rename the database. I do it this way because it is faster to create the database with same structure and same users and same....
It works ok. This time, I forgot to reduce the size of tablesapces and imported data to them. Now one of my tablespaces is 5GB, but it used just 500MB. It is Managed in Dictionary tablesapce, so I assumed if I use Coalesce, it will let me to reduce the size. But still I get the same error. ORA-03297
Before doing import, I droped the user which had objects in that tablespace, I mean I made it empty, then I created a new user and did the import fromuser.. touser and now, it just used 500 MB. So to my knowledge, it doesn't have any objects which is using extends above 500 MB. I don't know why it doesn't allow me to reduce the size !
As always, please help me on this isssue.
Thanks in advance
June 19, 2003 - 7:35 am UTC
because at least one of the extents in your datafiles is at the "end" of the data file.
use LMTS and extents tend to goto the "front" of the datafile. stop using DMTS!
A reader, June 19, 2003 - 8:43 am UTC
Thanks Tom. I assumed Coalesce make all extends continues. I mean remove the last extend and put it at the end of the list. But it seems it just coalesce the data blocks within an extend! Am I right?
Regards,
Arash
June 19, 2003 - 12:52 pm UTC
coalesce doesn't move anything. So, if your data file had:
UUUFFFUFFFFFFFFFFFFFFFFFFFFU
(u=used, f=free) as extents, coalesce would result in:
UUUF..UF...................U
with the ..... being part of one extent. that one used extent at the end will stop you from shrinking the file.
A reader, June 19, 2003 - 1:40 pm UTC
Thank you so much. I got it.
Reduce the size of Tablespace datafile
Vikas Sahrma, July 18, 2003 - 9:59 am UTC
Hi Tom,
Your solution of moving and dropping tha tables is good.
What can be done if the datafile is in RBS which is also DMT.
I have the same problem with RBS while doing an import of a large table.
Thanks and Regards,
Vikas Sharma
July 19, 2003 - 11:08 am UTC
when using rbs's -- i always
a) created a tablespace/rbs
b) put a single rbs in that tablespace
to "shrink", it is as easy as
a) drop rbs
b) shrink file
c) create rbs
Problem in LMT
Riyaz, July 21, 2003 - 7:07 am UTC
Thanks for your valuable tips.
I faced one problem. In the "test server", I took exports of all users and dropped all tablespaces. (it was DMT)
And then created LMT and imported the users. While doing import, it was giving error. When I checked the same, the data file is FULL. But I created the new tablespace with 20% increase in size compared to the old DMT tablespaces. Then why error has occured. Is LMT occupy more space than DMT?
Please answer.
July 21, 2003 - 8:31 am UTC
it is simply taking the
initial, next, minextents, pctincrease
from the original tablespace and allocating AT LEAST that much space in the LMT. Say you had segments with:
( initial 1m next 2m pctincrease 50 minextents 5 ) and it had 5 extents right now
( initial 64k next 64k minextents 100 ) and it had 100 extents right now
and you created a 1m uniform allocated tablespace.
that first segment would take:
1 + 2 + 3 + 4.5 + 6.75 meg of space rounded UP to the next megabyte increment ( minextents = 4, pctincrease = 50% )
the second segment would take
100 * 1m (minextents = 100)
where as right now it take 6400k (6.3meg)
You might want to move objects from dmt's to lmt's using
alter table t move tablespace LMT storage ( initial 1k next 1k minextents 1 pctincrease 0 );
just to get rid of legacy storage clauses forever.
Thanks. Pl confirm my understanding.
Riyaz, July 24, 2003 - 4:58 am UTC
Thanks.
I used "alter tablespace" and done the job as per your suggestion.
Pl confirm my understanding based on your earlier explanation.
Suppose in DMT, it had
( initial 64k next 64k minextents 100 )
ie., 100 extents (ie 6400K space occupied),
If I import the same in LMT which has (1M initial 1 M next pctincrease 0).. then will it allocate 100 * 1 M = 100M for that particular table?
Pl confirm my understanding.
July 24, 2003 - 8:45 am UTC
yes, it will -- it will use minextents to
a) compute the space it would have taken given the init/next/pctincrease
b) compute the min extents it would have
and it will make sure that the table in the LMT
a) has at LEAST that much space
b) has at LEAST that many extents.
correction
Riyaz, July 24, 2003 - 5:00 am UTC
Correction:
I used 'alter table move tablespace' as per your suggestion. Pl confirm my above understanding.
fragmentation of LMT
kiro, July 24, 2003 - 10:24 am UTC
Hi Tom
As you said LMT could not be fragmented, but if we have
in LMT datafile
UUUUUUUUUUUUUUUUUUUUUUUUUEE.
when U - used extend
E - empty space(naver contain data).
and I have made truncate table T1 with app 30 extends
we will have following ficture of datafile:
UUUUUUFFFFFFFFFFFFFFUUUUUEE.
when U - used extend
E - empty space(naver contain data)
F - free space.
Is it possible or not? If yes I think it is also fragmentation of datafile and you can not resize it.
Regards
July 24, 2003 - 4:19 pm UTC
that is not fragmented is it -- fragmentation is when you
a) ask how much free space do I have?
b) the answer is 500meg
c) you try to create a table with a 100meg initial extent and it fails with "insufficient space"
THAT is fragmentation -- your free space is in so many small, non-contigous chunks that you cannot use it.
In your second example ANY object would be able to use ANY of the F's -- that is not fragmented.
usable holes
Dennis, July 24, 2003 - 1:18 pm UTC
Kiro,
The ability to shrink a file and the fragmentation of a file are two concepts.
Here, your file is not fragmented as you are in LMT (assuming uniform). This means anytime an object needs to grow into a new extent, it will be able to find space, since the 'free holes' are all multiples of the extent size (it's uniform after all). In DMT this could hold to not true, especially if you had PCTINCREASE on your tables, or tables of different initial and next sizes. In DMT, your datafile was fragmented if you had say 100 MB free, but couldn't allocate a 5M extent. With LMT, if you have a uniform size of 5M, then if you have 100 MB free, you will be able to allocate 20 extents to use it all.
Not being able to shrink the file merely means that an extent is near the end of the file. That doesn't mean it's fragmented.
Hope that helps.
Dennis
July 24, 2003 - 4:30 pm UTC
well said.
Kiro, July 28, 2003 - 7:05 am UTC
Thanks Tom and Denis!
Fragmentaion and coalesce
tp, July 14, 2004 - 3:22 am UTC
Hi Tom,
if coalesing brings only adjuscent blocks together
how will the fragmented blocks be brought together inorder to shrink the datafile.
uuuffffuuuuuuffff
u-used
f-free
if an extent requires eight blocks then i thought it will bring all the free blocks together..
July 14, 2004 - 11:22 am UTC
why bother shrinking anything, it'll just grow again is my point. The index is a dynamic strucuture. Unless you've removed lots of data you will never ever put back in there -- why bother "shrinking" anything? You'll just need to grow it again and that is expensive.
tell me, how many days before the free space in this index after a coalesce goes way down? you would not be getting any long term payback by "shrinking" anything.
Facing same problem.
Js, July 14, 2004 - 12:35 pm UTC
hello Sir,
No doubt, LMT has many advantages over DMT.
But I have one doubt regarding LMT.
Suppose, I have only 400 mb free space in tablespace and my big table's next extent is 500m. So In DMT it is possible to make next extent to 400m and continue processing.
But, If my tablespace is LMT [ Autoallocate ] and after lot of extents its next extent is > 400m then ?
Thanks,
Js
July 14, 2004 - 9:53 pm UTC
then there would have been 0mb free in the LMT. if you were using uniform, you would always have N*extent_size mb free, no more, no less. N is just a number >= 0
Reducing datafile after one-ff job
Andre, September 01, 2004 - 4:28 pm UTC
Tom,
We have recently upgraded to Oracle 10g and as part of the upgrade process we converted also large table (30Gb) into partitioned one by doing insert select... old table after this was dropped. As result, tablespace which contained these tables become blown up to 60Gb and I believe has structure like ffffffuuuuuu as older table was removed. Is there any way of reducing datafile back to 30Gb? I understand that alter table move can do the trick, but will table be accessible during this operation (there are some long running processes accessing this table almost constantly)? Is there other ways to do this?
Thanks.
September 01, 2004 - 8:22 pm UTC
with 10g you have an easier online "reorg" process with dbms_redefinition -- you would have to move this to another tablespace, and then just drop this one.
so, either online reorg or offline reorge but in anycase -- if you have the ffffffuuuuuu in a datafile -- it will take a reorg to "reclaim"
Hint: when rebuilding in the future, rebuild into a new tablespace. In 10g you can even rename a tablespace to put it back after you drop the old if you like.
Reducing datafile after one-ff job
Andre, September 02, 2004 - 4:23 pm UTC
Thanks Tom. We have some processes constantly reading/updating data in this partitioned table, will online reorg affect performance of these processes? Is it going to have impact on undo tablespace? Could you, please, confirm that with the current situation within the tablespace Oracle will use free space below HWM for inserts?
Thank you very much.
September 02, 2004 - 4:32 pm UTC
everything you do on that server will "affect the performance of the existing processes" as you are adding more work.
there will be the "materialized view log" that will be in place (so we can sync the replicates)
there will be the additional IO performed to read the data
and then write the data
and log the data.
what does the HWM have to do with anything? Oracle will *create a new segment*, copy the data into it, sync it up and then drop the old segment in effect. You'll have two copies of the data for a bit.
index rebuild fails with tablespace error
Somasundaram Balu, December 08, 2004 - 12:21 pm UTC
Hi,
I am having a problem with index rebuild over weekends. The Index tablespace fails with not enough space in that index tablespace(unable to extend temp segment error in Index tablespace) I tried increasing the datafile but still same error. finally I put an autoextend on on the datafile index rebuild completed succesfully but for every week's run the tablespace increases and grows by 500 M. What is the problem here? I have checked the application and the index is not growing every week
Thanks
December 09, 2004 - 12:41 pm UTC
the solution:
stop doing this.
why why why are you doing this??????????????
(i know why it is happening, but I want you to prove to me that this is even a remotely good thing for you to be doing)
review
Sven, January 13, 2005 - 11:17 am UTC
Hi Tom,
Is it possible to use table "move" command (in order to compact table) on some tables in the system tablespace?
Thanks,
January 13, 2005 - 11:40 am UTC
as long as they are not owned by SYS or SYSTEM :)
most sys tables -- no ddl
some sys tables -- ok for ddl, but only if support says "sure"
contigous files
Sheila, March 18, 2005 - 11:13 am UTC
I was wondering that if the contigous files are bad when I defrag my puter?and if they are how can I fix them?The files are bule and says they are contigous.. is it good or bad? Thanks>>>>>>>>
March 18, 2005 - 11:19 am UTC
sorry -- not really sure what you are saying
read this
A reader, March 22, 2005 - 5:39 am UTC
DMT Tablespace not being dropped
Amit bansal, September 20, 2005 - 11:55 pm UTC
Hi Tom,
Good morning...
I have been trying to remove a DMT tablespace which has no objects in it. It's size was 22 Gb. Even after half an hour it didnt drop the tablespace. Then i tried to resize the datafiles, so that i can use the freed space on to another tablespace. It gave an ORA-03297 error, even though it does not have any objects in it. Moreover on quering the dba_free_space it shows 17 gb(4.5 gb less than earlier).
The database version is 8.1.6.0.0
Please help ..
September 21, 2005 - 6:53 pm UTC
select * from dba_segements where tablespace_name = 'that_tablespace'
please -- show output.
DMT Tablespace not being dropped
Amit Bansal, September 23, 2005 - 6:00 am UTC
Tom,
The query select * from dba_segements where tablespace_name = 'ODS_LARGE_I'
no rows selected
One of my colleague gave a solution for it. He created a table of 10 mb and gave a next extent of 10 mb. Then he ran a shell script to allocate extents to it. As he told me, basic purpose was to coalesce it. This tablespace was used earlier for creating indexes, which were dropped. So the tablespace became fragmented. After nearly whole space was allocated in 6 hrs!!! We dropped the tablespace after it.
Is that the case that while dropping the DMT tablespace, oracle tries to coalesce it. Is there any other work around for this problem. Anyways now we have made the new tablespace as locally managed.
September 23, 2005 - 9:21 am UTC
Oracle is trying to drop the free extents - which if you have a DMT can take a very very long time (you said it would not drop, not that "it was taking a long time to drop" - big difference).
Coalesce tablespac
Kumar, September 27, 2005 - 12:30 pm UTC
I read the following on tbe net
"As a DBA, you could help Oracle by manually performing the coalesce operation yourself
by issuing the ALTER TABLESPACEÂ…COALESCE command. OracleÂ’s SMON process is also supposed to coalesce tablespace honeycombs, but trusting SMON to coalesce free
space is not very reliable or recommended".
Is it recommended to coalesce, I have read from Oracle guide that SMON does the coalescing. When do you recommend to coalesce the tablespace?
September 27, 2005 - 1:44 pm UTC
Never.
Use locally managed tablespaces and even SMON isn't involved, it just happens.
Trusting SMON to coalesce free space is generally both reliable and recommended.
It would be a rare case using old versions, with legacy dictionary managed tablespaces you MIGHT consider using that command with - and only after finding SMON didn't coalesce fast enough for you (lots of drops+creates right away)
Kumar, September 28, 2005 - 2:56 am UTC
Thanks. My tablespaces are all locally managed, so no issues.
But, just out of interest, you had mentioned that
"only after finding SMON didn't coalesce fast enough for you"
Is there any way of finding it ?
Regards,
Kumar
September 28, 2005 - 9:48 am UTC
you get a create table statement failing even though there are enough free extents, and they are contigous.
it is "obvious" is the point.
Mark, October 11, 2005 - 7:17 pm UTC
Just wanted to comment on how satisfied I am with my (first) reorganization using 10g EM Database Control. My story, to keep it brief, was that I wanted to move a blob column that mostly got in the way (the "normal" columns were accessed very frequently, but the blobs were PDFs, mainly there for archival purposes - an occasional lookup). After moving the blob, the original tablespace was still very large and I couldn't shrink it. I had to move all the tables in oldtblspc to newtblspc, so that space isn't wasted. I was just dreading the amount of work I thought I had to do, altering each table and index, in order to move them over to the new tablespace...
Turns out all I had to do to reorg is search for oldtblspc, press "Next" through a few simple screens, and all the work was done for me.
I'm relieved this isn't going to be an all-nighter. That's all. :)
slow response from dba_free_space
Sudershan, May 23, 2006 - 2:03 pm UTC
Ora-9206
O.K. this database has a mix of dictionary and locally managed tablespaces..
after doing a truncate/import/ into this DB..I am seeing a very slow peroformance from dba_free_space...and it has way more records than it used to...54807 vs about 5000 or so.
would manual coalesce help..on these dictionary tablespaces.
Can manual coalescing be done..at any time..or it should be done off-hours?
thanks
May 24, 2006 - 6:43 am UTC
you can do the alter tablespace t coalesce. As for whether it should be done off hours - that would be "yes" for anyone that has "off hours" (any sort of maintanence...)
It "might help", then again, it might do nothing. Depends on how the space is laid out.
but what would also maybe help would be to use the CBO which is sensitive to the size of objects when developing query plans. That would be something you would want to do in test first however (gathering stats on SYS objects that is)
Is it possible to shrink the tablespace
Dilipkumar Patel, October 30, 2006 - 12:37 pm UTC
Hi Tom,
I am using Oracle 10g Rel 2, I have few tablespaces from which I want to release space to UNIX file systems, by resizeing the datafiles.
Some of the datafiles, I am not able to resize.
system@COLLP> accept tblsp_name prompt 'Enter Tablespace Name: '
Enter Tablespace Name: OUTST_200511_TBLS
system@COLLP>
system@COLLP>
system@COLLP> set verify off
system@COLLP>
system@COLLP> column dummy noprint
system@COLLP> column pct_used format 999.9 heading "%|Used"
system@COLLP> column name format a20 heading "Tablespace Name" noprint
system@COLLP> column Mbytes format 99999999.999 heading "MBytes"
system@COLLP> column used format 99999999.999 heading "Used"
system@COLLP> column max_used format 99999999.999 heading "Max Used"
system@COLLP> column free format 99999999.999 heading "Free"
system@COLLP> column largest format 99999999.999 heading "Largest"
system@COLLP> column file_name format A50 heading "File name"
system@COLLP> break on report
system@COLLP> compute sum of Mbytes on report
system@COLLP> compute sum of free on report
system@COLLP> compute sum of used on report
system@COLLP>
system@COLLP>
system@COLLP> select nvl(b.tablespace_name,
2 nvl(a.tablespace_name,'UNKOWN')) name,
3 b.file_name file_name,
4 kbytes_alloc Mbytes,
5 kbytes_alloc-nvl(kbytes_free,0) used,
6 nvl(kbytes_free,0) free,
7 ((kbytes_alloc-nvl(kbytes_free,0))/
8 kbytes_alloc)*100 pct_used,
9 nvl(largest,0) largest,
10 (kbytes_alloc - nvl(largest,0)) max_used
11 from ( select file_id,
12 sum(bytes)/(1024*1024) Kbytes_free,
13 max(bytes)/(1024*1024) largest,
14 tablespace_name
15 from sys.dba_free_space
16 group by file_id, tablespace_name ) a,
17 ( select sum(bytes)/(1024*1024) Kbytes_alloc,
18 sum(maxbytes)/(1024*1024) maxbytes,
19 file_name, file_id,
20 tablespace_name
21 from sys.dba_data_files
22 group by file_name, file_id , tablespace_name )b
23 where a.tablespace_name (+) = b.tablespace_name
24 and a.file_id = b.file_id
25 and b.tablespace_name = nvl('&tblsp_name',b.tablespace_name)
26 order by 1;
%
File name MBytes Used Free Used Largest Max Used
-------------------------------------------------- ------------- ------------- ------------- ------ ------------- -------------
/fs711/collp/data/OUTST_200511_TBLS01.dbf 512.000 161.438 350.563 31.5 215.750 296.250
/fs717/collp/data/OUTST_200511_TBLS03.dbf 166.000 128.063 37.938 77.1 .938 165.063
/fs707/collp/data/OUTST_200511_TBLS02.dbf 512.000 228.313 283.688 44.6 215.625 296.375
------------- ------------- -------------
1190.000 517.813 672.188
system@COLLP> @resize
system@COLLP> alter database datafile '&filenm' resize &mb
2 /
Enter value for filenm: /fs707/collp/data/OUTST_200511_TBLS02.dbf
Enter value for mb: 300m
alter database datafile '/fs707/collp/data/OUTST_200511_TBLS02.dbf' resize 300m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
system@COLLP> @resize
system@COLLP> alter database datafile '&filenm' resize &mb
2 /
Enter value for filenm: /fs711/collp/data/OUTST_200511_TBLS01.dbf
Enter value for mb: 400m
alter database datafile '/fs711/collp/data/OUTST_200511_TBLS01.dbf' resize 400m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
system@COLLP>
Can you suggest me some way to release the space back to unix level.
October 30, 2006 - 12:52 pm UTC
search site for maxshrink
LMT great - but what about the disk file fragmentation?
Allen, November 20, 2006 - 2:01 pm UTC
Tom,
Locally managed table spaces are great - but what about the physical disks?
I am running Oracle 9.2.0.1.0 on two servers with windows 2003, RAID-5. Using the windows defrag tool to analyze the disk. The report shows lots of red (fragmented) space and little blue (non-fragmented) space.
Any thoughts or feelings on either shutting down Oracle to defrag the disk or running defrag with Oracle up. Both are 9am to 5 pm systems so midnight defrag only hurts the DBA. (I'm just being polite - I believe you have many thoughts on the subject :)
Thanks in advance,
Allen
November 20, 2006 - 2:13 pm UTC
you are on raid 5 (stripes things all over the place)
and you are worried about "fragmentation", hmmm...
I would not really "defrag" a database disk, what is the point. Especially since striping's goal is to --- well -- spread data out, fragement it all over the place?
LMT great - but what about the disk file fragmentation
Allen, November 20, 2006 - 3:35 pm UTC
Tom,
Hi. Yea the RAID-5 is a corporate standard that only PeopleSoft has an exception for. They use RAID-10.
I do want the active data spread across as many physical spindles as possible to reduce contention. The defrag is to keep data together so a single read returns as many usable records as possible on the disk track. RAID-5 writes a file out over multiple spindles but once it starts writing it does not jump around. RAID still tries to use consecutive blocks/sectors for that file (source: </code>
http://en.wikipedia.org/wiki/RAID, http://en.wikipedia.org/wiki/RAID#RAID_5 <code>
As DBA's we try to keep internal data and index blocks full with coalece. How does one keep the physical blocks & sectors unfragmented?
BTW, a little more research showed the fragmented files where in \oracle\admin\bdump and udump. I was worried the data files where getting fragmented. The files are over allocated during creation but the "things expand to overwhelm the space and time provided" One day, the files will grow beyond their initial allocation.
Thanks in advance,
Allen
November 20, 2006 - 3:43 pm UTC
I'm not an expert in "disk defragging", but I'm not sure how well a "disk defragger" works on stripped stuff - the purpose of striping is to - fragment.
the defragger will be working on the raid 5 logical volume, not on a physical disk.
I'm not convinced, can you convince me otherwise?
(as a database guy, I do not use coalesce very often - and coalesce doesn't work on "data" - only on "index" blocks). I've never defragged a database device, doesn't seem to make sense. It is not used like a normal file.
and trace files - bah, don't worry about them, the only time you create them would be times of error which should be few and far between.
LMT great - but what about the disk file fragmentation
Allen, November 20, 2006 - 4:37 pm UTC
Hi,
Tom wrote:
"I'm not convinced, can you convince me otherwise?"
...pause...hmmm. I doubt it. I do not have the low level trace info about what Oracle is doing to show any improvement in block reads or efficency. Even with some data it would only be for just a small hand full of vendors; so the results may not apply to defragmentation in general. Then again "proof is proof" so "when all else has been removed, what ever remains - how ever improbable - must be the truth"-Sherlock Holmes.
Besides - this is the first time over the years I've come to your forum and not found an answer or something that lead to the answer.
Let me do some research. Possibly to fill a table space of 4k with 1k extents with 300k of data, then check for fragmentation.
Yea, the trace files can either be deleted or even just copied to another file, the orginal deleted and the copy renamed back to the original name. At least it makes windows happy. The .trc are kept for a few months so the vendors can look back and see what the log file and error messages have been.
Thanks in advance,
Allen
Moving to LMT on a PRODUCTION system
V, November 22, 2006 - 4:18 pm UTC
Tom,
DO you see any issue moving approx. 1500 tables/indexes from DMT to LMT on a production system while users are accessing?
November 24, 2006 - 12:54 pm UTC
assuming you use dbms_redefinition and don't try to do it all simultaneously, but rather one by one or some by some over time, not really.
LOB
V, November 30, 2006 - 11:31 am UTC
Does dbms_redefinition move the LONG, LOB data also?
November 30, 2006 - 1:57 pm UTC
AMAZING
A reader, December 20, 2006 - 1:36 am UTC
LMT
SM, July 04, 2007 - 1:23 pm UTC
I am continously facing ora-1653:unable to extend by 15,for a table in Locally Managed Tablespace(8192 bytes is block size) with freespace 8GB(approx), but with contiguous space groups of 81920 bytes(10 blocks).Can you suggest a solution.
July 05, 2007 - 12:55 pm UTC
I suggest that you are not accurately telling us what is happening.
first, it is highly unlikely to have 10 block exents (unless it is maybe a system allocated extent sized tablespace - but you don't say - and it would still be a very strange size)
second, 15 is a strange number to ask for (system managed would not) so it would imply it is uniform size, but if it were uniform - you would not have any 10 block extents.
so, you'll need to show us what you really mean...
LMT
SM, July 07, 2007 - 12:54 pm UTC
I am receiving error ORA-1653: unable to extend table <table_name> by 15 in tablespace <Tablespace_name>
(Although next_extent from dba_tables is 106496Bytes and pct_increase=0)
while accessing bytes/8192 from dba_free_space, am receiving multiple contiguous spaces of size 10.
You can ask me for the exact parameters that would help you to help me.
July 08, 2007 - 9:23 am UTC
if you are getting unable to extent table, you are not in TEMP anymore :) you are trying to do a create table as select - and this has nothing to do with temp.
that tablespace name - is it even your temporary tablespace name - it will not be.
Follow up on windows server disk file fragmentation
Allen, August 24, 2007 - 10:41 am UTC
Tom,
Hi. I can not belive it has been almost a year since posting.
We have moved from RAID-5 to RAID-10 for both Oracle as MS SQL Server for PeopleSoft. Between the old mainframe'rs that hand place files and the new guys there are alot of folks who believe RAID-5 is great.
Using windows defrag, What I found was that oversizing tables space files initialy will keep the file from fragmenting. Internaly it might be a mess, but the piecess that make up the physical phile will not be fragmented.
Also redo-logs - which are continualy re-written, do not fragment.
Export files fragment. Here I wish the "Mythbusters" could take on "things work better with a little fragmentation." If this is so then why do computers run faster just after a de-frag??
All this is posted because the idea of defragging a file on mainframe, DEC-10, VAX, PC is natural but I have run into resistance on defragmenting servers?? Somehow people belive windows servers are immune from the same disk problems of PCs.
Of course these are my views and your experiance will vary. Do not take this as instructions but as one persons experiance.
Regards,
Allen
August 27, 2007 - 3:24 pm UTC
umm, you are taking about file systems which do get 'fragmented', but not tablespaces which do not.
I still want someone to DEFINE precisely what it means.
And sometimes filesystems do not run any faster after a defrag. It entirely depends on the access patterns, totally.
Not everything benefits from a file system 'defrag', sure wish someone would post definitive numbers as the result of a comprehensive benchmark.
99% of the time, I think it is "well, we defragged the disk and everything 'seems' faster". Now we are in the continous defrag mode.
I personally haven't really seen a benefit from defragging my disks upon which I use a lot (desktop, laptop).
Raid-5 isn't horrible when used properly. I wouldn't want to direct path load onto it (not because of fragmentation -that has nothing to do with it), because raid-5 is slow for writes and a direct path load has a good chance to blow out any cache you have in front of it. I wouldn't necessarily want my redo on it (but it can be done - depends on the load).
fragmentation - strange on 10gr2
A reader, August 29, 2007 - 3:19 pm UTC
Hi Tom - I am on 10gr2 and I have a strange issue. I see a big difference in segment size after I refresh my dev from production. Please see below. Even though the count doesn't match, it's a very small delta. What could be the reason?
DEV
---
SQL>
SQL> SELECT SUM(bytes)/(1024*1024*1024) FROM dba_segments WHERE segment_type = 'INDEX'
2 AND segment_name IN (SELECT index_name FROM dba_indexes WHERE owner IN ('SYSADM' ))
3 ;
SUM(BYTES)/(1024*1024*1024)
---------------------------
55.0537109
SQL>
SQL> SELECT owner, COUNT(*) FROM dba_indexes WHERE tablespace_name = 'PSINDEX' GROUP BY owner;
OWNER COUNT(*)
------------------------------ ----------
SYSADM 44690
SQL>
PROD
----
SQL> SELECT SUM(bytes)/(1024*1024*1024) FROM dba_segments WHERE segment_type = 'INDEX'
2 AND segment_name IN (SELECT index_name FROM dba_indexes WHERE owner IN ('SYSADM' ))
3 ;
SUM(BYTES)/(1024*1024*1024)
---------------------------
74.4780884
SQL> SELECT owner, COUNT(*) FROM dba_indexes WHERE tablespace_name = 'PSINDEX' GROUP BY owner;
OWNER COUNT(*)
------------------------------ ----------
SYSADM 44706
September 04, 2007 - 4:41 pm UTC
you don't tell us anything about the tablespaces and the extent management strategy you are using...
but here are some guesses:
a) in production, the indexed data has been subject to updates and deletes - some of the existing index space in prod is "white space" - allocated to the index but currently not used. Even just inserts could cause this as when a leaf block splits - we are likely to do a 60/40 block split - so each leaf block could be about 1/2 half.
when you refresh, you build the indexes cleanly.
b) in prod you have a few GIGANTIC indexes that did not get copied over - it only takes ONE index to account for this space and you have 16 of them.
c) you have entirely different extent management strategies in place - both use uniform sizing for example, but in prod, the sizes are much smaller and there is less "wastage" at the end of the index.
for example.
shrink datafiles that grew big once
Nicosa, February 13, 2009 - 12:09 pm UTC
Hi Tom,
I have a tablespace that grew really big because the purge didn't happen for a long time.
I did the purge and I have now the datafiles of that tablespace look like this :
UUUUFUFUUUUFUUFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFUUU
UUUUFUFFUUFUUFFFFFFFFFFFFFFFFFFFFFFUFFFFFFFFFFFFFFUU
(big empty space ^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^)
(U=used, F=free)
In my case, 90% of the datafiles are FREE, but I cannot resize them to reclaim space as some extents are "at the end" of those datafiles.
I first thought of doing some "ALTER TABLE MOVE" to bring back used extents to "the beginning" of datafiles but I cannot as some of those objects are partionned IOT using extended partition name (space blank in the name, I wish Oracle didn't allow that to even exists...).
The only ways I see to reclaim that space that shall have never been used are :
- doing some export/import (definitly not my favorite)
- doing some CTAS to duplicate data to another tablespace, truncate objets then resize datafiles, then "insert as select" back the data to original objects.
The main point is : there are really a big number of objects whose extents are at the end of the files. There are a lot of FKs betweens those objects, lots of them are iot and/or partitionned.
Doing CTAS + "insert as select" to get data back to original tablespace would prevent me to get DDL of those objects to recreate them in a new tablespace.
I'm on 10.2 on RH4, and the data to be moved is around 6Gb in total.
The system can easily be offlined for operations.
Could you shed some your (wonderful) light on how you would achieve this ?
February 16, 2009 - 11:28 am UTC
give me an example of your partitioned IOT issue - give me the shorted ddl necessary to cause an issue...
I was the culprit...
Nicosa, March 02, 2009 - 11:43 am UTC
Hi Tom,
I couldn't reproduce what I first described as an issue due to "extented partition name" on IOT.
I guess I must have been mistyping commands at the time...
The "alter table move partition" worked just fine (when typed correctly).
All my apologizes for that non-existing issue.
Not a question but thanks
Allen Shepard, July 09, 2010 - 9:48 am UTC
Tom,
I forgot to say thank you for speaking at HROUG on July 7th, 2010. I ran into some old friends from other jobs.
Thanks for explaining what the documentation implies but does not give enough detail on - table fragmentation. There are multiple ways to free up white space, thank you for doing a quick comparison between them.
Also thanks for the insight into x10 database. We will still look into using it but now with "eyes open." I did not think of loading the DB nor the indexes. The lack of disk I/0 and I/O subrutine code was all I saw, sadly.
Regards,
Allen
"Information is inversely proportional to the glossiness of the brochure" - A. Shepard
Question to follow :-)
Resizing of datafiles
Prasad, May 23, 2012 - 4:33 am UTC
Use following script to resize datafiles of particular tablespace:
set verify off
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
column cmd format a100 word_wrapped
select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
and tablespace_name='&TABLESPACE_NAME'
/
It will give you the command to resize the datafile in database
May 23, 2012 - 9:03 am UTC
hey, thanks for my script. Looks a lot like a bit of my maxshrink script referenced on this page:
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/
column cmd format a75 word_wrapped
select 'alter database datafile ''' || file_name || ''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/
Fixing fragmentation
lalu, June 30, 2015 - 9:20 am UTC
Hi Tom,
We have a LMT and size will be around 750G.
Out of which more than 60% is free and maximum size of tables in the database are 20-25G.(3 tables)
Still we face ORA-1652.
select min(next_extent)
from dba_segments
where tablespace_name = 'TESTTS'; 2 3
MIN(NEXT_EXTENT)
----------------
1048576
SYS@database> select next_extent from dba_segments where SEGMENT_NAME in ('T1','T2') --These are 2 biggest tables
2 ;
NEXT_EXTENT
-----------
1048576
1048576
SYS@database> select count(*)
from dba_free_space
where tablespace_name = 'TESTTS'
and bytes <= ( select min(next_extent)
from dba_segments
where tablespace_name = 'TESTTS')
order by block_id
/ 2 3 4 5 6 7 8
COUNT(*)
----------
497010
SYS@database> select count(*) from dba_free_space where tablespace_name = 'TESTTS' ;
COUNT(*)
----------
497017
select *
from dba_free_space
where tablespace_name = 'TESTTS'
and bytes >= ( select min(next_extent)
from dba_segments
where tablespace_name = 'TESTTS')
order by block_id
/ 2 3 4 5 6 7 8
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
TESTTS 17 140928 6147801088 375232 17
TESTTS 18 158024 5867700224 358136 18
TESTTS 19 397192 1949171712 118968 19
TESTTS 17 516160 2280652800 139200 17
TESTTS 19 516160 2280652800 139200 19
TESTTS 18 516160 2280652800 139200 18
TESTTS 6 544628 1179648 72 6
Looking at above fact the TS is highly fragmented though its LMT.
TS is created as "EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 16K SEGMENT SPACE MANAGEMENT AUTO "
Please suggest a better way to fix the TS fragmentation.