RE: Detecting xxx fragmentation/corruption?

*cough* sys.source$ is the table containing the source of the stored procedures. This can get quite large for some applications installations.

Most folks usually view it as dba_source, user_source, or all_source.

dba_segments tells you where things take up space permanently.

You can get a pretty good idea of where the used storage goes in your database by

select segment_type,count(*),sum(bytes) from dba_segments group by segment_type;

Toss an owner on and you can break it out by owner - etc.

Free space is a little funky to break out. The view is still dba_free_space, but select the text column from dba_views if you want to see where the components of free space live.

Now - if some block does not appear on either list it is "lost" but I haven't seen that in a *long* time. And the last time I saw blocks on both lists was an early release of parallel index build (7.0.x or 7.1.x, can't remember - that was messy...)

Regards,

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Finn Jorgensen
Sent: Thursday, May 08, 2008 2:27 PM
To: htmldeveloper_at_gmail.com
Cc: Oracle-L Freelists
Subject: Re: Detecting xxx fragmentation/corruption?

Peter,

All that script does is generate a list of how much free space you have in a given tablespace and how many chunks it's in. That's all. It doesn't really concern itself with what kind of object may be taking up the space that's not free.

FYI stored procedures do not take up any space really. At least not in the form of tablespace space as you're implying.

HTH,
Finn

On 5/8/08, Peter Teoh <htmldeveloper_at_gmail.com> wrote:
> We have this doc (metalink):
>
> Subject: Script to Detect Tablespace Fragmentation
> Doc ID: Note:1020182.6
>
> to detect tablespace fragmentation. Can I know if there exists
> another for other space usage resources like stored procedures,
> indexes, clusters? (criteria for fragmentation similarly = blocks +
> block_id = next block ?)
>
> Where are the stored procedures stored physically? How do I identify
> all the differents blocks belonging to the stored procedures?
>
> Similarly, how do extract out all the blocks for the indexes? And
clusters?
>
> Is there a way to detect corruption (like using dbv) for each of these
> structures? And perhaps identifying the exact blocks that is
> corrupted?
>
> Thanks.
>
>
> --
> Regards,
> Peter Teoh
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l