Calculate the space used by a single object

This script will help you calculate the size of a single object :

SELECT  s.owner, 
        s.segment_name, 
        s.bytes/1024/1024 "Size(MB)"
FROM    dba_segments s,
        dba_lobs l
WHERE   s.owner = '&schema_name'
  AND   s.owner=l.owner(+)
  AND   s.segment_name = 
        CASE WHEN s.segment_type = 'LOBSEGMENT' 
        THEN L.SEGMENT_NAME(+)
        WHEN s.segment_type = 'LOBINDEX'
        THEN L.INDEX_NAME(+)
        END
AND     s.SEGMENT_NAME='&segment_name';

Calculate the space used by a whole schema

If you want the space used by a whole schema, then here is a variation of the first query :

SELECT  s.owner, 
        SUM(s.bytes/1024/1024) "Size(MB)"
FROM    dba_segments s,
        dba_lobs l
WHERE   s.owner = '&schema_name'
  AND   s.owner=l.owner(+)
  AND   s.segment_name = 
        CASE WHEN s.segment_type = 'LOBSEGMENT' 
        THEN L.SEGMENT_NAME(+)
        WHEN s.segment_type = 'LOBINDEX'
        THEN L.INDEX_NAME(+)
        END
group by s.owner
order by 2;

What is the screen command ?

When you have long running jobs, you need to keep them active when you disconnect from server.
The screen command allows you to detach your terminal from you session.
By doing this there is no link between your session and your terminal and you can disconnect from server while your terminal and your jobs keep running.
Even better, once you reconnect to the server you can reattach the terminal to your new session and find your terminal exactly like you left it.
Continue reading Keep jobs active with screen command →

who never needed to export data to excel from sqlplus ? everybody had one day needed to do that.
Sqlplus support the HTML markup which provides an excellent result once opened in excel.
With the HTML markup, sqlplus create a table with all columns from your query including the name of your columns as table header.
Here is a small exemple:

spool myxlsfile.xls
SET MARKUP HTML ON ENTMAP ON PREFORMAT OFF ;
select 	to_char(end_time,'hh24:mi:ss') as sample_time,
		value
from 	v$sysmetric_history
where 	end_time between sysdate - interval '1' hour and sysdate
and 	group_id = 2
and 	metric_name = 'Host CPU Utilization (%)'
order by metric_name, end_time;
spool off

Continue reading Export data to excel →