CheckSpacePutLine

CREATE OR REPLACE PROCEDURE CheckSpacePutLine
IS

used number;
free number;
total number;
hit number;
pins number;
reloads number;
lib number;
smem number;
sdisk number;
para varchar2(2000);
pbytes number;
err number;
application date;
BEGIN

 select sum(usedmb),sum(freemb),sum(totalmb)
 into used,free,total
 from
 (
  select usedmb,freemb,totalmb
   from ts_used_free
   where to_char(check_date,'yyyymmdd')=to_char(sysdate,'yyyymmdd')
 );
 SELECT 1 - (phy.value / (cur.value + con.value))
 into Hit FROM v$sysstat cur, v$sysstat con, v$sysstat phy
 WHERE cur.name = 'db block gets' AND con.name = 'consistent gets'
 AND phy.name = 'physical reads';

 select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
     sum(reloads)/sum(pins) *100 libcache
     into pins,reloads,lib
     from v$librarycache;

 SELECT  value  into smem
  FROM v$sysstat WHERE name IN ('sorts (memory)');
 SELECT  value  into sdisk
  FROM v$sysstat WHERE name IN ('sorts (disk)');

 select bytes  into pbytes
 from v$sgastat where pool='shared pool' and name ='free memory';

 select count(*) into err
  from alert_tab  WHERE text LIKE '%ORA-%' ;

 select max(log_date) into application from eport.application_log ;

 para := 'Used '||' '
 || to_char(used) || ' ' || ' Free' ||' '
 || to_char(free) || ' ' || ' Total'||' '
 || to_char(total)|| ' ' ||' bufferHitis '||''
 || to_char(hit)||' ' ||' SharePool'||' '
 || to_char(lib)||' ' || 'MemSort'||''||to_char(smem)||' DiskSort'||' '
 || to_char(sdisk) ||' '||' '
 || 'SharePoolFreebs:'||' '||to_char(pbytes)  ||''
 || 'Terrs'||' '|| to_char(err) || ' ' || 'The Last Ap Err occured in' || to_char(application);

 dbms_output.put_line(para);

END;
/

CREATE OR REPLACE PROCEDURE "INSERT_TS" is
begin

insert into ts_used_free
select
 sysdate    "check_date",
 tablespace_name   "Tablespace",
 (totalspace - freespace) "UsedMB",
 freespace   "FreeMB",
 totalspace   "TotalMB",
 round (100*(freespace/totalspace))
      "PCT FREE"
from
 (
 select
  tablespace_name,
  round(sum(bytes)/1048576 ) totalspace
 from
  dba_data_files
 group by
 tablespace_name
 ) ,
 (
 select
  tablespace_name   tbname,
  round(sum(bytes)/1048576) freespace
 from
  dba_free_space
 group by
  tablespace_name
)
where
 tablespace_name = tbname;

end;
/

SQL> desc ts_used_free;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 CHECK_DATE                                         DATE
 TABLESPACE                                         VARCHAR2(30)
 USEDMB                                             NUMBER
 FREEMB                                             NUMBER
 TOTALMB                                            NUMBER
 PCTFREE                                            NUMBER

SQL>

CREATE TABLE TS_USED_FREE
(
  CHECK_DATE  DATE,
  TABLESPACE  VARCHAR2(30 BYTE),
  USEDMB      NUMBER,
  FREEMB      NUMBER,
  TOTALMB     NUMBER,
  "PCTFREE"   NUMBER
)
TABLESPACE SYSTEM
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCACHE
NOPARALLEL;

CREATE SYNONYM EPORT.TS_USED_FREE FOR TS_USED_FREE;

GRANT SELECT ON  TS_USED_FREE TO PUBLIC;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9408/viewspace-102279/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9408/viewspace-102279/