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

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

PostgreSQL pgBadger

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

PostgreSQL Pgbadger Installation On Linux

Migrating From Oracle to PostgreSQL using ora2pg open source tools