Re: Simple Space Question

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 Mon Jun 17 2002 - 17:53:03 CDT