Re: Simple Space Question

Howard,

Thanks for the informative reply. I found the information very useful. I wanted to ask a couple of more things if you do not mind.

In your reply, you said: "I tend to prefer using DBA_TABLES (for tables alone, obviously), and comparing BLOCKS (below the HWM) to EMPTY_BLOCKS (above the HWM) -though this breaks down in you're using ASSM (as does DBMS_SPACE!)."

I'm not sure what you mean by ASSM?

Also, if I analyze the tables frequently, then can I assume that BLOCKS + EMPTY_BLOCKS will be the total space allocated for that table? And that if I sum that up for an entire tablespace I can get the total for the tablespace?

Moving a tablespace to reset the HWM can be expensive, I assume. Is there a way for me to check whether or not I need to reset the HWM? So, if I saw that my HWM is at block 100,000 and the highest block of my data is 500, then a move would be beneficial here.

Thanks again,

Arthur

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:<aelp97$6vm$1_at_lust.ihug.co.nz>...
> Why is it so hard?
>
> You have four views to worry about. DBA_FREE_SPACE tells you how many/what
> size chunks of free space you have within a tablespace.
>
> DBA_SEGMENTS tells you how many blocks in total (above and below the high
> water mark) a segment has acquire. And DBA_EXTENTS tells you how those
> blocks are broken up into extents.
>
> Which leaves knowing how many of those blocks are above the high water mark
> and how many are blow. Which can be tricky. The DBMS_SPACE package can be
> used to return the number above the HWM (it's the FREE_SPACE procedure). But
> that can be awkward to run, so -assuming statistics have been calculated- I
> tend to prefer using DBA_TABLES (for tables alone, obviously), and comparing
> BLOCKS (below the HWM) to EMPTY_BLOCKS (above the HWM) -though this breaks
> down in you're using ASSM (as does DBMS_SPACE!).
>
> Why on earth you would bother 'resizing the object' when you're using
> locally managed tablespace beats me, however. The whole point of locally
> managed tablespaces is that extent acquisitions don't matter (much) anymore.
> All you really need to do is to count the number of extents a segment has
> (DBA_EXTENTS). When the number reaches a couple of hundred, it's time to
> move the segment into a new tablespace that comes with bigger uniform extent
> sizes (say, from a 64K extent size tablespace to a 1M extent size
> tablespace).
>
> Any other approach seems (to me) to be missing the point: locally managed
> tablespaces make extent/segment management a light-weight affair. You seem
> to have taken advantage of the new technology yet still worrying about the
> things that the old technology gave us good reason to worry about.
>
> As for re-setting the high water mark: truncate, drop and move are the only
> commands that would readily lend themselves to that sort of job. Of the
> three, MOVE is the obvious candidate, since it doesn't require an export and
> import to complete the job. There is no need to move the table to another
> tablespace. Moving the segment around *within the same tablespace* is
> perfectly legitimate: just use the command 'ALTER TABLE EMP MOVE;' -there's
> no new tablespace mentioned there, so EMP stays put in the same tablespace.
> But it gets re-populated, and the High Water Mark is adjusted accordingly.
>
> Regards
> HJR
>
> "Arthur" <amerar_at_iwc.net> wrote in message
> news:8b622eae.0206171131.5eeadf38_at_posting.google.com...
> > Hello All,
> >
> > Understanding space usage in an Oracle database is so hard. Maybe
> > someone can help me out with this.
> >
> > All of my tables are locally managed. Basically I am interested in
> > seeing how much unused space is in each table / tablespace. I want to
> > know if any of these objects have been 'over allocated' and are much
> > larger than they need to be. If I need to move the HWM, can I do
> > this without import/export or moving the table to another tablespace?
> >
> > Also, I'd like to see how much space is left in each table /
> > tablespace. This gives me an idea how close we are to needing to
> > resize the object.
> >
> > If you can send a copy of your response to my email, I'd appreciate
> > it......
> >
> > Thank you,
> >
> > Arthur
Received on Tue Jun 18 2002 - 11:37:34 CDT