Search This Blog

Friday, 19 October 2018

tablespace details

pct_free.sql
==================================
set echo off
set lines 999 pages 999;
col tablespace for a22;
col "Used % " for a10;

select a.tablespace_name "Tablespace",
        a.total_size_mb   "Total_Size_MB",
        (a.total_size_mb - b.free_size_mb) Used_Size_MB,
         b.free_size_mb "Free_Size_MB",
         round(((a.total_size_mb - b.free_size_mb)*100/a.total_size_mb), 2)        "Pct_Used",
      round(free_size_mb*100/total_size_mb, 2) "Pct_Free",
          round(b.max_free, 2) "Max_Free",
      round(c.max_next, 2) "Max_Next",
        d.status,
      TO_CHAR(NVL((a.bytes - NVL(b.bytes, 0)) / a.maxbytes * 100, 0), '990.00')  "Used % "
      from   ( select tablespace_name, round(sum(bytes)/1024/1024, 2) total_size_mb,
    decode(sum(decode(autoextensible,'YES',1,0)),0,'NO','YES') auto_extension,sum(bytes) bytes,sum(decode(autoextensible,'YES',maxbytes,bytes)) maxbytes
                 from   dba_data_files group  by tablespace_name ) a, ( select tablespace_name, round(sum(bytes)/1024/1024, 2) free_size_mb,
                                                     round (max( bytes) / ( 1024 * 1024 ), 2 )max_free,sum(bytes) bytes from   dba_free_space
                group  by tablespace_name ) b, ( select tablespace_name, max(next_extent) / ( 1024 * 1024 ) max_next from   dba_segments
                                                 group  by tablespace_name ) c, dba_tablespaces d
where  d.tablespace_name = a.tablespace_name(+)
   and    d.tablespace_name = b.tablespace_name(+)
and    d.tablespace_name = c.tablespace_name(+)
   and    a.tablespace_name is not null
order by 5;

==============================================================
tbs.sql
------------------------------------
set echo off
set line 200
col file_name for a100
select file_id, file_name, bytes/1024/1024 "mb" from dba_data_files where tablespace_name='&tbs';


prompt >>>>> Please check mount point size before resize datafile
prompt
prompt >>>>> HINTS for resize datafile command
prompt =======================================================================
prompt alter database datafile <file_id> resize ...m;
prompt alter tablespace tablespace_name add datafile '...' size ...m;
prompt

No comments:

Post a Comment