Re: How to Find Data Blocks

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Thu, 12 Oct 2000 09:46:54 +0200
Message-ID: <8s3qaa$g4$1@s1.read.news.oleane.net>

Here's a script that gives you
the map of your tablespace:

Accept ts_ CHAR PROMPT "Tablespace name (default '%'): "

Column tsn_       FORMAT A16       HEADING "Tablespace" TRUNC
Column file_id    FORMAT 9999999   HEADING "File"
Column block_id   FORMAT 9999999   HEADING "Block"
Column blocks     FORMAT 99999999  HEADING "NbBlocks"
Column name_      FORMAT A37       HEADING "Segment"    TRUNC

Break ON tsn_ SKIP PAGE -
      ON file_id SKIP 1;

Spool fragts

select tablespace_name tsn_, file_id, 1 block_id, 1 blocks,

       '<header>' name_
from dba_extents
where tablespace_name like nvl(upper('&ts_'),'%') union
select tablespace_name tsn_, file_id, 1 block_id, 1 blocks,

       '<header>' name_
from dba_free_space
where tablespace_name like nvl(upper('&ts_'),'%') union
select tablespace_name tsn_, file_id, block_id, blocks,

       decode(owner, NULL, segment_name,
                     owner||'.'||segment_name) name_

from dba_extents
where tablespace_name like nvl(upper('&ts_'),'%') union
select tablespace_name tsn_, file_id, block_id, blocks,

       '<free>' name_
from dba_free_space
where tablespace_name like nvl(upper('&ts_'),'%') order by tsn_, file_id, block_id
/

Spool OFF

--
Have a nice day
Michel


JS <ghp_at_videotron.ca> a écrit dans le message : a%8F5.12641$j%6.275799_at_wagner.videotron.net...


> Would like to know which view to query to find out which blocks in any given

> data file actually contain data, and which blocks are empty. Thank you,

> running Oracle 8 PE on Win 95.

>

>

Received on Thu Oct 12 2000 - 02:46:54 CDT