createtable.sql

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

---存储事例说明信息的表
drop table dbs ;
create table dbs 
(db_nm  varchar2(8) ,  /*事例 instance name */
 host_nm varchar2(8) , /*host (server) name */
 description varchar2(80) /*instance description*/
) ;
/
---存储文件信息的表
drop table files ;
create table files
(
 db_nm varchar2(8) , /*instance name*/
 ts    varchar2(30) ,/*tablespace name*/
 check_date date ,   /*date entry was made*/
 file_nm varchar2(80) , /* size of the file*/
 blocks  number,
 primary key(db_nm , ts , check_date , file_nm) 
 ) ;
/
---files表的视图，按表空间分组 
drop view files_ts_vies ;
 create view files_ts_view as 
 select 
  db_nm ,   				/*instance name*/
  ts ,							/*tablespace name*/
  check_date ,      /*data entry was made*/
  sum(blocks) sum_file_blocks  /*blocks allocation for ts*/ 
  from files 
  group by 
	  db_nm ,
	  ts ,
	  check_date ;
/
---this table will store information about free apace
---存储自由空间的表
drop table spaces ;
create table spaces
( db_nm varchar2(8) ,
  ts    varchar2(30) ,
  check_date date ,
  count_free_blocks number ,
  sum_free_blocks number ,
  max_free_blocks number ,
  primary key (db_nm , ts , check_date)) ;
/
---存储已用盘区信息的表
drop table extents ;
create table extents
(
 db_nm varchar2(8) ,
 ts    varchar2(30) ,
 seg_owner varchar2(30) ,
 seg_name varchar2(32) , 
 seg_type varchar2(17) , 
 extents  number , 
 blocks   number , 
 check_date date ,
 primary key (db_nm, ts , seg_owner , seg_name ,check_date));
 /
 create public database link case  ----数据库连接管道
    connect to system identified by a1b2c3 ----用户名和密码
    using 'james' ; 
/