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:
      owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024) meg 
      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' 
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;


Popular posts from this blog

PostgreSQL pgBadger

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory

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

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Database startup / shutdown /restart