Oracle查询语句

查看该表空间上所有对象

SELECT t.owner, t.segment_name,SUM(bytes)/1024/1024 From dba_segments t WHERE t.tablespace_name = 'TBS_DEFAULT' GROUP BY t.owner,t.segment_name ORDER BY SUM(bytes) desc;

查看LOB类型的段所属的表

select table_name,column_name,segment_name,index_name from user_lobs;

移动表至其他表空间，并重建索引

alter table TICKETBASE move tablespace TB_LOB; alter index SYS_C005391 rebuild online;

oracle 查看cpu占用情况 以及具体sql语句情况

1、以下语句可以查出具体占用cpu的情况的spid，占用大小参照value的值 select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value from v$session a,v$process b,v$sesstat c where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;

2、以下语句查询出具体sql占用cpu情况 SELECT sql_text piece FROM v$sqltext a WHERE (a.hash_value, a.address) IN

(SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value), 
 DECODE(sql_hash_value, 0, prev_sql_addr, sql_address) 
 FROM v$session b 
 WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '5744' )) 

ORDER BY piece ASC 其中的spid5744可以在第一条语句中获得

闪回版本查询

select * from TICKETDETAIL_TEMP as of scn 329350000; 1. 获取数据删除前的一个时间点或scn，如下： SQL>select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;

TIME SCN

---

----------------------------------------

2010-06-29 23:03:14 1060499

2.查询该时间点（或scn）的数据，如下： SQL> select * from t as of timestamp to_timestamp('2010-06-29 22:57:47', 'yyyy-mm-dd hh24:mi:ss'); SQL> select * from t as of scn 1060174;