Oracle查询语句
(以“ --查看该表空间上所有对象 SELECT t.owner, t.segment_name,SUM(bytes)/1024/1024 From dba_segments t WHERE t.tablespace_name = 'TBS_DEFAULT' GROUP BY t.owne...”为内容创建页面) |
|||
| （未显示2个用户的6个中间版本） | |||
| 第1行： | 第1行： | ||
| − | + | ==查看该表空间上所有对象== | |
SELECT t.owner, t.segment_name,SUM(bytes)/1024/1024 From dba_segments t | SELECT t.owner, t.segment_name,SUM(bytes)/1024/1024 From dba_segments t | ||
WHERE t.tablespace_name = 'TBS_DEFAULT' | WHERE t.tablespace_name = 'TBS_DEFAULT' | ||
GROUP BY t.owner,t.segment_name | GROUP BY t.owner,t.segment_name | ||
ORDER BY SUM(bytes) desc; | 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;<br/> | ||
| + | 1. 获取数据删除前的一个时间点或scn，如下：<br/> | ||
| + | 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）的数据，如下：<br/> | ||
| + | SQL> select * from t as of timestamp to_timestamp('2010-06-29 22:57:47', 'yyyy-mm-dd hh24:mi:ss'); <br/> | ||
| + | SQL> select * from t as of scn 1060174; | ||
2014年10月10日 (五) 09:53的最后版本
目录 |
[编辑] 查看该表空间上所有对象
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;
