[oracle]ORACLE 常用脚本（4）
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 c:\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 c:\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 ==================
*************查看当前用户使用的操作****************************

SELECT a.machine,a.terminal,a.osuser, a.username, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address =b.address order by b.address

***************************************************

1. 监控事例的等待

select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
from v$session