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);

SQL>
select p.spid,sql_text from v$sqlarea v,v$session s,v$process p
where v.HASH_VALUE=s.sql_HASH_VALUE and v.address=s.sql_address
and p.addr=s.paddr and p.spid in (29638,29716,28453,28693);

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