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 Index

VMWARE WORKSTATION 3,4,5,6,7,8,9,10,11,12,14,15...etc LICENSE KEYS COLLECTION

How to CreateYour Own AWS Account Alias?

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL ALTER TABLE ... SET LOGGED / UNLOGGED