查看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;