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