Chinaunix首页 | 论坛 | 博客
  • 博客访问: 586475
  • 博文数量: 772
  • 博客积分: 5000
  • 博客等级: 大校
  • 技术积分: 4980
  • 用 户 组: 普通用户
  • 注册时间: 2008-10-17 13:02
文章分类

全部博文(772)

文章存档

2011年(1)

2008年(771)

我的朋友

分类:

2008-10-17 13:23:22


  日常监测分析数据库和获取数据库中当前用户的数据对象的两个sql程序
  
  rem 这需要 统计某个具体用户的"Table,index,column,constraits"
  rem
  rem 全部表-列定义 table_cols.txt
   set lin 110 pages 3000
   column table_name format a30
   column data_type format a12
   column data_default format a8
   column column_name format a22
   column Cid format 999
   column Len format 9999
   column Prec format 99
   column Scale format 99
  
  select TABLE_NAME, COLUMN_ID "Cid", COLUMN_NAME, DATA_TYPE, DATA_LENGTH "Len",
   nvl(DATA_PRECISION,'-1') "Prec", nvl(DATA_SCALE,'-1') "Scale",
   NULLABLE, DATA_DEFAULT
  from USER_TAB_COLUMNS ;
  
  rem ======== TAB =============
  select * from tab;
  spool off
  
  spool user_indexes.txt
  rem
  rem 全部索引定义user_index.txt
   column table_name format a22
   column index_name format a28
   column index_type format a7
   column column_name format a18
   column # format 99
   column Init format 999999;
  select a.table_name, t.cache "C",a.index_name,
   column_position "#",column_name,
   UNIQUENESS,a.INITIAL_EXTENT/1024 "Init"
   from user_indexes a, user_ind_columns c,user_tables t
   where c.INDEX_NAME =a.INDEX_NAME
   and a.table_name= t.table_name
   order by a.table_name,a.index_name,column_position;
  spool off
  
  spool user_Obj_Table_Index.txt
  rem
  rem 用户对象,表和索引userObj_Table_Index.txt
   set lin 111 pages 333
   column table_name format a24
   column index_name format a32
   column tablespace_name a12
   column Init format 999999;
  rem 由于用户要关心的是我自己的详细数据的存放位置,下面分别得出index,tables
   select tablespace_name,table_name,cache,initial_extent/1024 "Init"
   from user_tables order by tablespace_name,table_name;
   select tablespace_name,table_name,index_name,initial_extent/1024 "Init"
   from user_indexes order by tablespace_name,table_name,index_name;
  spool off
  
  spool user_constraints.txt
  rem
  rem 全部表-列约束_user_constraints.txt
   column CONSTRAINT_NAME format a30
   column TABLE_NAME format a30
   column r_CONSTRAINT_NAME format a20
  
  select CONSTRAINT_NAME,
   CONSTRAINT_TYPE, TABLE_NAME, R_CONSTRAINT_NAME, DELETE_RULE
   from user_constraints
   order by CONSTRAINT_TYPE,TABLE_NAME;
  spool off
  
  spool user_index1rebld.sql
  rem
  rem 重建全部索引
  rem select 'alter index '||index_name||' rebuild;' from user_indexes
  rem where table_name = 'GWNEWS';
  select 'alter index '||INDEX_NAME||' rebuild tablespace indexes;' from user_indexes;
  spool off
  rem @index1rebld.sql
  
  spool user_sources.sql
  rem
  rem 全部代码,主要是procedure
   column name format a22 ;
   column text format a77;
   break on name;
  select text,name from user_source;
  spool off
  
  rem =============== End of File ==================
  
  
  
  
【责编:admin】

--------------------next---------------------

阅读(214) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~