I want to share some useful sql queries in blog which we use many times in a day.
I see many post in OTN site which is mention how the poster  see information about  their tablepsace on database.

Here is the some queries which i use

Tablespace growth monitor Scripts:
Script-1 :


SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days 
, ts.tsname
 , max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
 , max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB 
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
 , DBA_HIST_TABLESPACE_STAT ts 
, DBA_HIST_SNAPSHOT sp
 , DBA_TABLESPACES dt
 WHERE tsu.tablespace_id= ts.ts#
 AND tsu.snap_id = sp.snap_id
 AND ts.tsname = dt.tablespace_name
 AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
 GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname 
ORDER BY ts.tsname, days;


Script-2:

column "Percent of Total Disk Usage" justify right format 999.99
 column "Space Used (MB)" justify right format 9,999,999.99
 column "Total Object Size (MB)" justify right format 9,999,999.99
 set linesize 150
 set pages 80
 set feedback off
 set line 5000
 column "SEGMENT_NAME" justify left format A30
 column "TABLESPACE_NAME" justify left format A30
 select * from (select c.TABLESPACE_NAME,c.segment_name,to_char(end_interval_time, 'MM/DD/YY’) mydate, sum(space_used_delta) / 1024 / 1024 "Space used (MB)", avg(c.bytes) / 1024 / 1024 "Total Object Size (MB)",
 round(sum(space_used_delta) / sum(c.bytes) * 100, 2) "Percent of Total Disk Usage"
 from
 dba_hist_snapshot sn,
 dba_hist_seg_stat a,
 dba_objects b,
 dba_segments c
 where begin_interval_time > trunc(sysdate) – 10
 and sn.snap_id = a.snap_id
 and b.object_id = a.obj#
 and b.owner = c.owner
 and b.object_name = c.segment_name
 and c.segment_name = 'S_PARTY’
 group by c.TABLESPACE_NAME,c.segment_name,to_char(end_interval_time, 'MM/DD/YY’)
 order by c.TABLESPACE_NAME,c.segment_name,to_date(mydate, 'MM/DD/YY’));


Script-3:

set pages 80
 set feedback off
 column "OBJECT_NAME" justify left format A30
 column "SUBOBJECT_NAME" justify left format A30
 column "OBJECT_TYPE" justify left format A30
 column "Tablespace Name" justify left format A30
 set line 5000
 SELECT o.OWNER , o.OBJECT_NAME , o.SUBOBJECT_NAME , o.OBJECT_TYPE ,
 t.NAME "Tablespace Name", s.growth/(1024*1024) "Growth in MB",
 (SELECT sum(bytes)/(1024*1024)
 FROM dba_segments
 WHERE segment_name=o.object_name) "Total Size(MB)"
 FROM DBA_OBJECTS o,
 ( SELECT TS#,OBJ#,
 SUM(SPACE_USED_DELTA) growth
 FROM DBA_HIST_SEG_STAT
 GROUP BY TS#,OBJ#
 HAVING SUM(SPACE_USED_DELTA) > 0
 ORDER BY 2 DESC ) s,
 v$tablespace t
 WHERE s.OBJ# = o.OBJECT_ID
 AND s.TS#=t.TS#
 AND o.OWNER='XXXX' --> Put Owner here
 ORDER BY 6 DESC
 /
 
Script-4:

set feedback on
 select * from (select c.TABLESPACE_NAME,c.segment_name "Object Name",b.object_type,
 sum(space_used_delta) / 1024 / 1024 "Growth (MB)"
 from dba_hist_snapshot sn,
 dba_hist_seg_stat a,
 dba_objects b,
 dba_segments c
 where begin_interval_time > trunc(sysdate) – &days_back
 and sn.snap_id = a.snap_id
 and b.object_id = a.obj#
 and b.owner = c.owner
 and b.object_name = c.segment_name
 and c.owner = 'XXXX' --> Put Owner here
 group by c.TABLESPACE_NAME,c.segment_name,b.object_type)
 order by 3 asc;

General info for tablespace:

select  tablespace_name,SEGMENT_SPACE_MANAGEMENT,extent_management,allocation_type from dba_tablespaces;

Tablespace size:
 select t1.n1 "Tablespace Name",
 t2.total "Total size",
 (t2.total-t1.free) "Used Size" ,
 t1.free "Free space",
 trunc((t1.free/t2.total)*100) "%free " ,
 trunc((1-t1.free/t2.total)*100) "% used"
 from
 (select tablespace_name n1,trunc(sum(bytes)/1024/1024) free
 from dba_free_space where tablespace_name not in ('CWMLITE','DRSYS','ODM','XDB','EXAMPLE','TOOLS')
 group by tablespace_name ) t1 ,
 (select trunc(sum(bytes)/1024/1024) total,tablespace_name n2
 from dba_Data_files where tablespace_name not in ('CWMLITE','DRSYS','ODM','XDB','EXAMPLE','TOOLS')
 group by tablespace_name) t2
 where t1.n1=t2.n2;