两个sql程序

王朝mssql·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

日常监测分析数据库和获取数据库中当前用户的数据对象的两个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 ==================

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航