Script عن الـ Tablespace

[عبدالله أسعد]

Script عن الـ Tablespace مساحتها و المساحة الغير مستخدمه و يحذ رك اذا كان
TS_FREE_PROC<=10 and TS_FREE<=TS_WARN_SIZE
or
TS_MAX_NEXT_EXT > TS_MAX_FREE

set pagesize 50000
set linesize 100 
set verify off
set feedback off

set term off                                       

set echo off
spool $spoolfile 
set serveroutput on size 10000
declare
 TS_ALL             number(8,2);
 TS_FREE            number(8,2);
 TS_MAX_FREE        number(8,2);
 TS_MAX_NEXT_EXT    number(8,2);
 TS_CAP             number(8,2);
 TS_CAP_PROC        number(8,2);
 TS_FREE_PROC	     number(8,2);
 TS_CAP_STATUS      char(10);
 TS_WARN_SIZE       number(4);
 TS_ALERT_SIZE      number(4);
 TS_DATE            char(16);

 cursor TS_CUR is
   select tablespace_name,sum(bytes)
   from dba_data_files where tablespace_name not in ('TEMP','ROLLBACK')
   group by tablespace_name
   order by tablespace_name;
begin TS_WARN_SIZE:=1000;
 TS_ALERT_SIZE:=500;
 select to_char(sysdate,'DD/MM/YYYY HH24:MI') into TS_DATE from dual; DBMS_output.put_line('=======================================================================================');
 DBMS_output.put_line('ver.1.1  Tablespace Report - Date:'||TS_DATE);
 DBMS_output.put_line('=======================================================================================');
 DBMS_output.put_line('Tablespace             TOTAL   TOT_FREE  MAX_FREE  MAX_NEXT_EXT  PCT_FREE  WARN');
 DBMS_output.put_line('---------------------------------------------------------------------------------------');


 FOR FC IN TS_CUR LOOP

-- Size(MB) 
   select sum(bytes)/1024/1024 
   into TS_ALL from dba_data_files where tablespace_name = FC.tablespace_name;

-- Free(MB)
  select nvl(sum(bytes)/1024/1024,0) 
   into TS_FREE from dba_free_space where tablespace_name = FC.tablespace_name;

 select nvl(max(next_extent)/1024/1024,0)
 into TS_MAX_NEXT_EXT from dba_segments where tablespace_name = FC.tablespace_name;

-- nvl for autoallocating
 select nvl(max(bytes)/1024/1024,0) 
 into TS_MAX_FREE from dba_free_space where tablespace_name = FC.tablespace_name;


   TS_CAP := TS_ALL - TS_FREE;
   TS_CAP_PROC := 100*TS_CAP/TS_ALL;
   TS_FREE_PROC := 100 - TS_CAP_PROC;

   TS_CAP_STATUS :='OK';


if (TS_FREE_PROC<=10 and TS_FREE<=TS_WARN_SIZE) then
TS_CAP_STATUS:='<===';
if (TS_FREE_PROC<=10 and TS_FREE<=TS_ALERT_SIZE) then
TS_CAP_STATUS:='<==!';
end if;
end if;
if (TS_MAX_NEXT_EXT > TS_MAX_FREE) then
TS_CAP_STATUS:='<==!';
end if;
       
   dbms_output.put_line(rpad(FC.tablespace_name,20)||lpad(to_char(TS_ALL),8)
||lpad(to_char(TS_FREE),10)||lpad(to_char(TS_MAX_FREE),11)||
lpad(to_char(TS_MAX_NEXT_EXT),14)||lpad(TS_FREE_PROC,9)||'%  '||lpad(TS_CAP_STATUS,5));
 END LOOP;
end;
/
spool off

[الأسيف]

شكر الله لك أخي الفاضل ..

[عبدالله أسعد]

Script ايضا عن TableSpace

tablespace.doc