Question: How can I display the
contents of my Oracle tablespace? I need to scan the
tablespace and identify the indexes and tables. How
can I query Oracle to display the contents of any
tablespace?
Answer: Here is a data dictionary query
that will display the contents of an Oracle tablespace:
You can use the following script to display the contents
of a specified tabledpace:
select
*
from
(select
owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024)
meg
from
dba_segments
where tablespace_name = 'SYSAUX'
order by
blocks desc);
This query will also display the contents of a
tablespace:
break on
file_id skip 1
column file_id
heading "File|Id"
column tablespace_name for a15
column object for
a15
column
owner for a15
column MBytes for 999,999
select tablespace_name,
'free space' owner,
/*"owner" of free space */
' ' object, /*blank object name */
file_id,
/*file id for the extent header*/
block_id, /*block id for the extent
header*/
CEIL(blocks*4/1024) MBytes /*length of the extent, in Mega
Bytes*/
from
dba_free_space
See code depot for full script
where
tablespace_name like '&tablespace_name'
union
select
tablespace_name,
substr(owner, 1, 20), /*owner name
(first 20 chars)*/
substr(segment_name, 1, 32), /*segment
name */
file_id, /*file id for extent header */
block_id, /*block id
for extent header */
CEIL(blocks*4/1024) MBytes /*length of
the extent, in Mega Bytes*/
from dba_extents
where
tablespace_name like '&tablespace_name'
order by 1,
4, 5;