Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

How To display the contents of my Oracle tablespace

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 
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;

Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Sequence

How to configure Replication Manager (repmgr) ?

Postgresql maximum size

PostgreSQL pgBadger