getdate.sql

介绍oracle dba常用的报表代码,已经异常查找等

spool ins_all.log 
begin
insert into files
(db_nm ,
 ts ,
 check_date ,
 file_nm ,
 blocks)
 select 
  upper('&&l') , /*insert database link ,instance name*/
  tablespace_name , /*tablespace name*/
  trunc(sysdate) , /*data query is begin performed*/
  file_name ,/*full name of database file*/
  blocks    /*number of database blokcs in file*/
 from sys.dba_data_files@&&l;
 commit ;
 insert into spaces
 (db_nm , 
  check_date ,
  ts , 
  count_free_blocks ,
  sum_free_blocks ,
  max_free_blocks)
 select
  upper('&&l'),
  trunc(sysdate) ,
  tablespace_name ,
  count(blocks) ,
  sum(blocks),
  max(blocks)
 from sys.dba_free_space@&&l
 group by tablespace_name;
commit ;
insert into extents
 (db_nm ,
  ts ,
  seg_owner,
  seg_name ,
  seg_type ,
  extents ,
  blocks ,
  check_date)
select  
  upper('&&l'),
  tablespace_name ,
  owner ,
  segment_name , 
  segment_type ,
  extents ,
  blocks ,
  trunc(sysdate)
from sys.dba_segments@&&l
where extents > 9
or segment_type = 'rollback';
commit ;
end ;
spool off