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
==================================
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