時間：2015-06-28 00:00:00 來源：IT貓撲網(wǎng) 作者：網(wǎng)管聯(lián)盟 我要評論(0)
1.----查某session當前正在執(zhí)行的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
--表分析
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;
---查數(shù)據(jù)庫鎖表的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';
存儲函數(shù)和過程
查看函數(shù)和過程的狀態(tài)
SQL>select object_name,status from user_objects where object_type='FUNCTION';
SQL>select object_name,status from user_objects where object_type='PROCEDURE';
查看函數(shù)和過程的源代碼
SQL>select text from all_source where owner=user and name=upper('&plsql_name');
查補丁
$ cd $ORACLE_HOME/OPatch
$ ./opatch lsinventory
關鍵詞標簽：oracle
相關閱讀
熱門文章
Oracle中使用alter table來增加,刪除,修改列的語法
oracle中使用SQL語句修改字段類型-oracle修改SQL語句案例
誤刪Oracle數(shù)據(jù)庫實例的控制文件
為UNIX服務器設置Oracle全文檢索
人氣排行 oracle中使用SQL語句修改字段類型-oracle修改SQL語句案例 Oracle中使用alter table來增加,刪除,修改列的語法 ORACLE SQL 判斷字符串是否為數(shù)字的語句 ORACLE和SQL語法區(qū)別歸納(1) oracle grant 授權語句 ORACLE修改IP地址后如何能夠使用 如何加速Oracle大批量數(shù)據(jù)處理 Oracle刪除表的幾種方法