Queries I often used on table spaces sizes and space used

To check the allocated space and used space in tablespaces:

1) SELECT d.status "Status", d.tablespace_name "Name",
        TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
        TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99999999.999') "Used (M)",
        TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
        FROM sys.dba_tablespaces d,
        (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,
        (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE
        d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT
        (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status
        "Status", d.tablespace_name "Name",
        TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
        TO_CHAR(NVL(t.bytes,0)/1024/1024,'99999999.999') "Used (M)",
        TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
        FROM sys.dba_tablespaces d,
        (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
        (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
        WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND
        D.EXTENT_MANAGEMENT LIKE 'LOCAL' AND D.CONTENTS LIKE 'TEMPORARY';

2)
 SELECT /* + RULE */  DF.TABLESPACE_NAME "Tablespace",
       DF.BYTES / (1024 * 1024 * 1024) "Size (GB)",
       SUM(fs.bytes) / (1024 * 1024 * 1024) "Free (GB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY DF.TABLESPACE_NAME,FS.BYTES,DF.BYTES_FREE,DF.BYTES_USED
 ORDER BY 4 DESC;
 SELECT * FROM dba_temp_free_space; 

2)  To check the schemas sizes in database: 

SELECT s.owner,SUM (s.BYTES) / (1024 * 1024 * 1024) SIZE_IN_GB
FROM dba_segments s
GROUP BY S.OWNER;

3) Learn about TEMPORARY   TABALE SPACE HERE

4) Query to find the long running queries in database :

select distinct sesid,serial, spid pid, osuser,schemaname,machine, floor(last_call_et/60) last_call, EVENT,program, sql_text, Mins, P1, P2, P3,hash_value
from
    (select sesid,serial, spid, sql_text, osuser,schemaname,machine, last_call_et, floor((sysdate - to_date(decode(start_time,'// ::',to_char(sysdate,'dd/mm/yyyy
hh24:mi:ss'), start_time), 'dd/mm/yyyy hh24:mi:ss'))* 1440) Mins, EVENT,program, P1, P2, P3,hash_value
    from
        (SELECT s.sid sesid,s.serial# serial,s.program, t.sql_text, s.osuser,s.schemaname,s.machine, s.last_call_et, substr(last_load_time,9,2) || '/' || substr(last_load_time,6,2) ||'/' ||
substr(last_load_time,1,4) || ' ' ||
        substr(last_load_time,12,2) || ':' || substr(last_load_time,15,2) || ':' || substr(last_load_time,18,2) start_time, U.event, U.p1, U.p2, U.p3,
        V.spid,T.hash_value
        FROM v$session S,
        v$sql T,
        v$session_wait U,
        v$process V
        WHERE S.status = 'ACTIVE'
        AND S.sql_address = T.address
        and exists (select 1 from v$sqltext_with_newlines y where y.hash_value = S.sql_hash_value)
        and T.SQL_TEXT <> 'COMMIT'
        AND S.sql_hash_value = T.hash_value
        AND U.SID = S.SID
        and V.addr = S.paddr
        and S.type='USER'
        )
    )
order by LAST_CALL desc;

5) 

## To truncate all tables ###

1.      Select 'truncate TABLE schemaowner.'||table_name||'  ;' FROM dba_tables where owner='schemaowner';

           

2.     

## To move all the Tables and indexes from one TBS to another ##

 

1.      select 'ALTER TABLE schemaowner.'||table_name|| ' MOVE TABLESPACE  BI_EDW_DEV_DATA;' FROM dba_tables  where owner='schemaowner';

2.      select 'ALTER INDEX schemaowner.'||index_name||' REBUILD TABLESPACE BI_EDW_DEV_IDX ;' FROM DBA_INDEXES WHERE OWNER='schemaowner';