oracle常用维护SQL

　　1.----查某session当前正在执行的sql

　　SQL>select s.sid,sql_text from v$session s,v$sql q

　　where s.SQL_ADDRESS=q.address and s.sql_hash_value=q.hash_value

　　and s.sid in (45,48,107)  and s.serial# in (53098,54004,44803);

　　2.--查事件

　　SQL>select event,p1text from v$session_wait

　　where sid in (45,48,107) and seq# in (53098,54004,44803);

　　SQL>select * from v$session_event where sid=14

　　---查索引定义

　　SQL>select * from user_ind_columns where index_name=upper('&index_name');

　　--重建索引

　　SQL>ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

　　--表分析

　　analyze table slview.flowraw compute statistics for all indexes;

　　--索引提示

　　SELECT * FROM A WHERE COL1 = XXX;

　　---查看某表的约束条件

　　SQL>select constraint_name, constraint_type,search_condition, r_constraint_name

　　from user_constraints where table_name = upper('&table_name');

　　SQL>select c.constraint_name,c.constraint_type,cc.column_name

　　from user_constraints c,user_cons_columns cc

　　where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')

　　and c.owner = cc.owner and c.constraint_name = cc.constraint_name

　　order by cc.position;

　　--查表空间大小

　　SELECT upper(f.tablespace_name) "表空间名",d.Tot_grootte_Mb "表空间大小(M)",d.Tot_grootte_Mb - f.total_bytes "已使用空间(M)",

　　to_char(round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2),'990.99') "使用比",f.total_bytes "空闲空间(M)",　f.max_bytes "最大块(M)"

　　FROM

　　(SELECT tablespace_name,　　round(SUM(bytes)/(1024*1024),2) total_bytes,round(MAX(bytes)/(1024*1024),2) max_bytes　　FROM sys.dba_free_space

　　GROUP BY tablespace_name) f,

　　(SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb　　FROM　 sys.dba_data_files dd GROUP BY dd.tablespace_name) d

　　WHERE d.tablespace_name = f.tablespace_name　　ORDER BY 4 DESC;

　　---查数据库锁表的session

　　select o.object_name, l.session_id, s.serial#, s.program,

　　s.username, s.command,   s.machine, s.lockwait   from v$locked_object l, all_objects o, v$session s

　　where o.object_id = l.object_id   and s.sid = l.session_id ;

　　alter system kill session '23,234';

　　----查oracle的job

　　select job, what, next_date, interval, failures, broken from user_jobs;

　　exec dbms_job.run(5);

　　-----查表空间各表大小

　　select segment_name, bytes

　　from user_segments

　　where segment_type = 'TABLE';

　　或者

　　Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name

　　where tablespace_name='USER';

　　存储函数和过程

　　查看函数和过程的状态

　　SQL>select object_name,status from user_objects where object_type='FUNCTION';

　　SQL>select object_name,status from user_objects where object_type='PROCEDURE';

　　查看函数和过程的源代码

　　SQL>select text from all_source where owner=user and name=upper('&plsql_name');

　　查补丁

　　$ cd $ORACLE_HOME/OPatch

　　$ ./opatch lsinventory