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