常規sql的使用總結（陸續補充）

mahanso發表於2010-11-30
SQL
//建立臨時表空間 

create temporary tablespace mahanso_temp 
tempfile 'E:\oracle\product\10.2.0\oradata\testserver\mahanso_temp01.dbf' 
size 32m 
autoextend on 
next 32m maxsize 2048m 
extent management local; 

//建立資料表空間 
create tablespace mahanso_data 
logging 
datafile 'E:\oracle\product\10.2.0\oradata\testserver\mahanso_data01.dbf' 
size 32m 
autoextend on 
next 32m maxsize 2048m 
extent management local; 

--設定表空間最大大小


ALTER DATABASE DATAFILE 'E:\oracle\product\10.2.0\oradata\testserver\mahanso_data01.dbf'
AUTOEXTEND ON NEXT 100M
MAXSIZE 10000M

--建立使用者
create user mahanso_new identified by mahanso_new
default tablespace mahanso_data
temporary tablespace mahanso_tmp;


--賦普通使用者許可權
grant 
    resource,connect,RECOVERY_CATALOG_OWNER,
    debug any procedure, debug connect session
    to mahanso_test;
      
--帶有物化檢視的使用者授權：
grant    
    UNLIMITED TABLESPACE,CREATE ANY MATERIALIZED VIEW ,
    SELECT ANY TABLE,ON COMMIT REFRESH
    to  mahanso_test;

--修改使用者密碼
alter user user01 identified by user10;
 
--賦值連線和開發許可權
grant connect,resource to mahanso_new;

--准許使用表空間
ALTER USER QUOTA UNLIMITED ON TABLESPACENAME

--賦值debug許可權
GRANT debug any procedure, debug connect session TO USERNAME

--取消使用者許可權

revoke dba from mahanso;

--給使用者賦增刪改查的許可權：　

grant select/insert/update/delete any table to 使用者名稱

--檢視資料檔案和臨時資料檔案和日誌檔案
SELECT NAME FROM V$DATAFILE
UNION ALL
SELECT NAME FROM V$TEMPFILE
UNION ALL
SELECT MEMBER FROM V$LOGFILE;

--角色賦權：

A>建立角色：create role role1; B>給角色賦權：grant select any talble to role1;(grant create any table,create procedure to role1;) C>把角色賦給使用者b: grant role1 to b;

--批處理：

查詢a的所有許可權：select 'grant select on IFSAPP.'||t.object_name||' to IFSERP;' from all_all_tables t where t.owner= 'IFSAPP';, 然後把這個執行即可;

--dba所有表查詢許可權賦給b:謹慎使用，可能會不安全；


--刪除session的連結
SELECT 'alter system kill session '''||sid||','||serial#||''';' FROM v$session WHERE username='USER';
--刪除使用者下所有的表
drop user mahanso_new cascade;
 

--檢視oracle表空間是否自動擴充套件
select file_name,autoextensible,increment_by from dba_data_files

--刪除表空間
DROP TABLESPACE mahanso_data INCLUDING CONTENTS AND DATAFILES;
--刪除臨時表空間
DROP TABLESPACE mahanso_tmp INCLUDING CONTENTS AND DATAFILES;

--修改表空間大小
ALTER   Database datafile '/oracle/oradata/ora11/mahanso_data.dbf' Resize 4500M;

--修改臨時表空間大小
ALTER   Database Tempfile '/oracle/oradata/ora11/mahanso_tmp.dbf'  Resize 4500M;

--修改使用者臨時表空間
alter user temporary tablespace new_tbs;


--增加資料檔案
alter tablespace EMALL_DAT add datafile '/oracle/oradata/tianj/tianj/emall_dat_03.dbf' 
size 800M autoextend 
on next 50M maxsize 1000M;


--更改自動擴充套件屬性
alter database datafile
''/oracle/oradata/ora11/mahanso_data.dbf'  ,
'/oracle/oradata/ora11/mahanso_data02.dbf',
'/oracle/oradata/ora11/mahanso_data03.dbf'  
autoextend off;

--修改表的表空間和對應的索引
1、先找到這哪些表的表空間不對。

select * from dba_tables where tablespace_name='TDB';


2、將表空間在 TDB 中的移到表空間 TDB2009 中

語法是： alter   table   table_name   move   tablespace   tablespace_name;

alter table tdb2009.ASSOC_INFO move tablespace TDB2009;
alter table tdb2009.BGUSERPOPD move tablespace TDB2009;

select 'alter index TDB2009.'||index_name||' rebuild online nologging tablespace TDB2009;' from user_indexes


怎麼在oracle中，把一個表空間的所有索引換到另一個表空間呢？
select 'alter index ' || index_name || ' move tablespace TDB2009;' from user_indexes

--建立只讀使用者
Create  user  query  Identified  By  mahansooquery;  --建立使用者
 
GRANT  SELECT  ANY  DICTIONARY  TO  query;   --授予所有字典的查詢許可權
 
GRANT  SELECT  ANY  SEQUENCE  TO  query;     --授予所有序列的查詢許可權
 
GRANT  SELECT  ANY  TABLE  TO  query;        --授予所有表的查詢許可權
 
GRANT CREATE SESSION TO query;               --授予建立會話
 
GRANT  SELECT  ANY  VIEW  TO  query;         --授予所有檢視的查詢許可權

--建立訪問特定表使用者
create user mahanso identified by mahanso; 

grant connect to mahanso; 

grant select on mahanso.CYO_CRM_USER to query; --這裡實現想給他許可權查詢的表 
grant select on mahanso.CYO_CUSTOMER to query; --這裡實現想給他許可權查詢的表 

--檢視閃回是否開啟
select log_mode,open_mode,flashback_on from v$database;

--檢視錶空間使用情況
方法一、
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;  

方法二、
SELECT fs.tablespace_name "Tablespace",  
  (df.totalspace - fs.freespace) "Used MB",  
  fs.freespace "Free MB",  
  df.totalspace "Total MB",  
  ROUND (100 * (fs.freespace / df.totalspace)) "Pct. Free"  
  FROM (SELECT tablespace_name, ROUND (SUM (BYTES) / 1024 / 1024)  
  totalspace  
  FROM dba_data_files  
  GROUP BY tablespace_name) df,  
  (SELECT tablespace_name, ROUND (SUM (BYTES) / 1024 / 1024) freespace  
  FROM dba_free_space  
  GROUP BY tablespace_name) fs  
WHERE df.tablespace_name = fs.tablespace_name;

方法三、
select b.file_name FileName, b.tablespace_name "Tablespace",
round(b.bytes / 1024 / 1024 / 1024, 2) "SpaceSize(G)",  
round((b.bytes - sum( nvl( a.bytes,0))) / 1024 / 1024 / 1024, 2) "Used(G)",
round(substr((b.bytes - sum( nvl( a.bytes , 0))) / ( b.bytes) * 100 , 1, 5), 2) "Used(%)"  
from dba_free_space a, dba_data_files b  
where a.file_id = b.file_id  
group by b.tablespace_name, b.file_name, b.bytes  
order by b.tablespace_name;

--檢視錶空間是否具有自動擴充套件的能力


--exp、imp方案

方法一
exp mahanso/asj file=d:/test.dmp log=d:/test.log query=\"where rownum < 5\" tables=users,result

注意：a、tables是你要匯出的表名，多表用","分隔。exp只能這樣指定，因為只有在表的模式下面才能按區間匯出。

      b、由於有字串解析問題，容易出現報錯現象(query=\"where rownum < 5\")

方法二
用parfile檔案代替exp、imp引數，建立".par"檔案內容如下：
file=d:/test.dmp
log=d:/test.log
query="where rownum < 4"
tables=users,result

在命令列中執行即可
exp mahanso/asj parfile=D:\new_db\111.par



使用範圍：所有oracle資料庫版本



--expdp和impdp方案

方法一、
匯出資料庫所有表的前10行：
expdp mahanso/asj dumpfile=mahanso_dir:teset32211111.dmp logfile=mahanso_dir:test2.log query=\"where rownum < 10\"

匯出資料庫特定表前10行：
expdp mahanso/asj dumpfile=mahanso_dir:teset32211111.dmp logfile=mahanso_dir:test2.log query=users:\"where rownum < 10\"

注意：a、也有tables引數，但是全表匯出前10行不用指定tables引數

      b、由於有字串解析問題，容易出現報錯現象,"\"分隔符必須使用，解析字串



方法二
用parfile檔案代替expdp、impdp引數，建立".par"檔案內容如下：
directory=mahanso_dir 
dumpfile=tabs_20090225.dmp 
nologfile=yes
query="where rownum <=3"

在命令列中執行即可
expdp mahanso/asj parfile=D:\new_db\test.par

使用範圍：10g以上資料庫





具體expdp、impdp使用方法如下：

--Data pump操作準備

當使用expdp時，以下一系列操作用來確定寫檔案的位置：
在expdp命令列中dumpfile引數規範的一部分指定了oracle寫檔案的目錄，但操作前要先建立目錄和賦予read許可權；     
     create directory pump_dir as 'c:\oracle\pump_dir';
     grant read on directory pump_dir to XXXX；
如果不以sysdba使用者登入，那麼為了能從模式從不是從使用者中匯出資料，使用者必須擁有exp_full_database許可權
      grant exp_full_database to XXXX;

--賬號解鎖
alter user user_name account unlock 

--檢視那密碼過期限制
select * from dba_profiles s where s.profile='default' and resource_name='password_life_time';

--改為無限制
alter profile default limit password_life_time unlimited;

--批次更新表明
select 'alter table "'||table_name||'" rename to '||upper(table_name)||';' from user_tables where table_name<>upper(table_name);

--檢查資料庫增長趨勢
  select to_char(creation_time, 'yyyy-mm') "Month",
  sum(bytes) / 1024 / 1024 "Growth (MB)"
  from sys.v_$datafile
  where creation_time > SYSDATE - 365
  group by to_char(creation_time, 'yyyy-mm')
  order by to_char(creation_time, 'yyyy-mm');


--解鎖過程
select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;
--檢視鎖
--alter system kill session 'sid,serial#';
--把鎖給KILL掉
alter system kill session '146,21177';


--修改表名
rename  oldname to newname

--檢視asm使用情況
select name,total_mb,free_mb, (total_mb-free_mb) used from v$asm_diskgroup; 

--檢視scn最大值
select scn from (select max(next_change#) scn from v$archived_log group by thread#) scn ;

--透過時間查詢
select count(*) from EMALL_USERS as of timestamp to_timestamp('2011-05-17 14:17:35','yyyy-mm-dd hh24:mi:ss') where condition;

 
--透過SCN號來查詢

select dbms_flashback.get_system_change_number from dual;  --當前

select * from EMALL_USERS as of scn 145815851; 


其中SCN號和時間的轉換：

--scn 轉換成時間
SELECT scn_to_timestamp(145815851) FROM dual;

--時間轉換成scn號
SELECT timestamp_to_scn(to_date('2011-05-17 00:05:00','yyyy-mm-dd hh24:mi:ss')) FROM dual;


--列出表結構資訊
select a.table_name,
       c.comments,
       a.column_name,
       a.comments,
       b.data_type,
       b.data_length,
       b.nullable
  from dba_col_comments a, dba_tab_cols b,dba_tab_comments c
 where a.owner = 'WUDADAO_TEST'
   and a.table_name = b.table_name
   and b.owner = 'WUDADAO_TEST'
   and a.comments is not null
   and c.owner = 'WUDADAO_TEST'
   and a.table_name = c.table_name
   and b.table_name = c.table_name
   and c.comments is not null
 group by a.table_name,
          c.comments,
          a.column_name,
          a.comments,
          b.data_type,
          b.data_length,
          b.nullable
          
oracle檢視object物件使用空間

1、表空間大小
Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name order by Sum(bytes)/1024/1024 desc;

2、表佔用空間
select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments where segment_type='TABLE'  group by segment_name order by segment_name;
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name order by Sum(bytes)/1024/1024 desc;

3、索引佔用空間
select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments where segment_type='INDEX'  group by segment_name order by segment_name;

4、資料檔案使用情況
select a.tablespace_name,
       round(a.bytes / 1024 / 1024, 0)"總空間",
       round((decode(b.bytes, null, 0, b.bytes)) / 1024 / 1024, 0)"使用空間",
       round((decode(b.bytes, null, 0, b.bytes)) / a.bytes * 100, 1)"使用率",
       c.file_name,
       c.status
  from sys.sm$ts_avail a, sys.sm$ts_free b, dba_data_files c
 where a.tablespace_name = b.tablespace_name(+)
   and a.tablespace_name = c.tablespace_name
 order by a.tablespace_name;
 
-----------------------------------------------------------------------
select b.file_id 檔案ID,
       b.tablespace_name 表空間,
       b.file_name 物理檔名,
       b.bytes / 1024 / 1024 大小M,
       c.max_extents / 1024 / 1024 可擴充套件數M,
       b.bytes / 1024 / 1024 + c.max_extents / 1024 / 1024 總大小M,
       trunc((b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024) 　已使用M,
       trunc(sum(nvl(a.bytes, 0)) / 1024 / 1024) 　剩餘M,
       trunc(sum(nvl(a.bytes, 0)) / (b.bytes) * 100, 2) 　剩餘比
  from dba_free_space a, dba_data_files b, dba_tablespaces c
 where a.file_id = b.file_id
   and b.tablespace_name = c.tablespace_name
 group by b.tablespace_name,
          b.file_name,
          b.file_id,
          b.bytes,
          c.max_extents,
          b.bytes / 1024 / 1024 + c.max_extents / 1024 / 1024
 order by b.file_id;

5、表空間使用統計
select a.tablespace_name,
       a.bytes / 1024 / 1024 "Sum MB",
       (a.bytes - b.bytes) / 1024 / 1024 "used MB",
       b.bytes / 1024 / 1024 "free MB",
       round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
  from (select tablespace_name, sum(bytes) bytes
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes, max(bytes) largest
          from dba_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 order by ((a.bytes - b.bytes) / a.bytes) desc;


刪除重複列的方法 

(1)   DELETE   FROM   table_name   A   WHERE   ROWID   >   ( 
              SELECT   min(rowid)   FROM   table_name   B 
                WHERE   A.key_values   =   B.key_values); 
(2)   create   table   table2   as   select   distinct   *   from   table1; 
          drop   table1; 
          rename   table2   to   table1; 
(3)   Delete   from   mytable   where   rowid   not   in( 
              select   max(rowid)   from   mytable 
              group   by   column_name   ); 
(4)   delete   from   mytable   t1 
            where     exists   (select   'x '   from   my_table   t2 
                                      where   t2.key_value1   =   t1.key_value1 
                                          and   t2.key_value2   =   t1.key_value2 
                                          ... 
                                          and   t2.rowid   >   t1.rowid); 
         

1) 資料庫session連線數
select count(*) from v$session;
2) 資料庫的併發數
select count(*) from v$session where status='ACTIVE';
3) 是否存在死鎖
set linesize 200
column oracle_username for a16
column os_user_name for a12
column object_name for a30
SELECT l.xidusn, l.object_id,l.oracle_username,l.os_user_name,l.process,
l.session_id,s.serial#, l.locked_mode,o.object_name 
FROM v$locked_object l,dba_objects o,v$session s
where l.object_id = o.object_id and s.sid = l.session_id;

select t2.username||'   '||t2.sid||'   '||t2.serial#||'   '||t2.logon_time||'   '||t3.sql_text
      from v$locked_object t1,v$session t2,v$sqltext t3
      where t1.session_id=t2.sid 
      and t2.sql_address=t3.address
      order by t2.logon_time;
4) 是否有enqueue等待
select eq_type "lock",total_req# "gets",total_wait# "waits",cum_wait_time from v$enqueue_stat where total_wait#>0;
5) 是否有大量長事務
set linesize 200
column name for a16
column username for a10
select a.name,b.xacts,c.sid,c.serial#,c.username,d.sql_text
from v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction e
where a.usn=b.usn
and b.usn=e.XIDUSN
and c.taddr=e.addr
and c.sql_address=d.ADDRESS
and c.sql_hash_value=d.hash_value
order by a.name,c.sid,d.piece;
6)表空間使用率
set linesize 150
column file_name format a65
column tablespace_name format a20
select f.tablespace_name tablespace_name,round((d.sumbytes/1024/1024/1024),2) total_g,
round(f.sumbytes/1024/1024/1024,2) free_g,
round((d.sumbytes-f.sumbytes)/1024/1024/1024,2) used_g,
round((d.sumbytes-f.sumbytes)*100/d.sumbytes,2) used_percent
from (select tablespace_name,sum(bytes) sumbytes from dba_free_space group by tablespace_name) f,
(select tablespace_name,sum(bytes) sumbytes from dba_data_files group by tablespace_name) d 
where f.tablespace_name= d.tablespace_name
order by d.tablespace_name;
臨時檔案：
set linesize 200
column file_name format a55
column tablespace_name format a20
select a.tablespace_name,a.file_name,round(a.bytes/(1024*1024*1024),2) total_g,
round(sum(nvl(b.bytes,0))/(1024*1024*1024),2) free_g,
round((a.bytes/(1024*1024*1024) - sum(nvl(b.bytes,0))/(1024*1024*1024)),2) used_g,
round(((a.bytes/(1024*1024*1024) - sum(nvl(b.bytes,0))/(1024*1024*1024)))/a.bytes/(1024*1024*1024),2) free_g
from dba_temp_files a,dba_free_space b
where a.file_id = b.file_id(+)
group by a.tablespace_name,a.file_name,a.bytes
order by a.tablespace_name;

select a.tablespace_name,a.file_name,round(a.bytes/(1024*1024*1024),2) total_g,
round(sum(nvl(b.bytes,0))/(1024*1024*1024),2) free_g,
round((a.bytes/(1024*1024*1024) - sum(nvl(b.bytes,0))/(1024*1024*1024)),2) used_g,
round(((a.bytes/(1024*1024*1024) - sum(nvl(b.bytes,0))/(1024*1024*1024)))/a.bytes/(1024*1024*1024),2) free_g
from dba_temp_files a,dba_free_space b
where a.file_id = b.file_id(+)
group by a.tablespace_name,a.file_name,a.bytes
order by a.tablespace_name;
 
歸檔的生成頻率:
set linesize 120
column begin_time for a26
column end_time for a26
select a.recid,to_char(a.first_time,'yyyy-mm-dd hh24:mi:ss') begin_time,
b.recid,to_char(b.first_time,'yyyy-mm-dd hh24:mi:ss') end_time,
round((b.first_time - a.first_time)*24*60,2) minutes
from v$log_history a,v$log_history b
where b.recid = a.recid+1;

sql讀磁碟的頻率:
select a.username,b.disk_reads,b.executions,
round((b.disk_reads/decode(b.executions,0,1,b.executions)),2) disk_read_ratio,b.sql_text
from dba_users a,v$sqlarea b
where a.user_id = b.parsing_user_id
and disk_reads > 5000;
Datafile I/O:
col tbs for a12;
col name for a46;
select c.tablespace_name tbs,b.name,a.phyblkrd+a.phyblkwrt Total,a.phyrds,a.phywrts,a.phyblkrd,a.phyblkwrt
from v$filestat a,v$datafile b,dba_data_files c
where b.file# = a.file#
and b.file# = c.file_id
order by tablespace_name,a.file#;
Disk I/O
select substr(b.name,1,13) disk,c.tablespace_name,a.phyblkrd+a.phyblkwrt Total,a.phyrds,a.phywrts,
a.phyblkrd,a.phyblkwrt,((a.readtim/decode(a.phyrds,0,1,a.phyblkrd))/100) avg_rd_time,
((a.writetim/decode(a.phywrts,0,1,a.phyblkwrt))/100) avg_wrt_time
from v$filestat a,v$datafile b,dba_data_files c
where b.file# = a.file#
and b.file# = c.file_id
order by disk,c.tablespace_name,a.file#;
select a.username,round(b.buffer_gets/(1024*1024),2) buffer_gets_M,b.sql_text
from dba_users a,v$sqlarea b
where a.user_id = b.parsing_user_id
and b.buffer_gets > 5000000;

col index_name for a16;
col table_name for a18;
col column_name for a18;
select index_name,table_name,column_name,column_position from user_ind_columns
where table_name = '&tbs';
大事務：
select sid,serial#,to_char(start_time,'yyyy-mm-dd hh24:mi:ss') start_time,sofar,totalwork,(sofar/decode(totalwork,0,1,totalwork))*100 ratio,message from v$session_longops
where message like '%RMAN%';
select sid,serial#,to_char(start_time,'yyyy-mm-dd hh24:mi:ss') start_time,sofar,totalwork,(sofar/decode(totalwork,0,1,totalwork))*100 ratio,message from v$session_longops
where sofar <> totalwork;
where (sofar/totalwork)*100 < 100;
索引檢查：
set linesize 200;
column index_name for a15;
column index_type for a10;
column table_name for a15;
column tablespace_name for a16;
select index_name,index_type,table_name,tablespace_name from user_indexes
where table_name ='&t';
set linesize 200;
column index_name for a26;
column table_name for a26;
column column_name for a22;
column column_position for 999;
column tablespace_name for a16;
select table_name,index_name,column_name,column_position from user_ind_columns where table_name = '&tab';
select table_name,index_name,column_name,column_position from user_ind_columns where index_name = '&ind';
select table_name,index_name,index_type,status,TABLESPACE_NAME from user_indexes where table_name = '&tab';
select table_name,index_name,index_type,status,TABLESPACE_NAME from user_indexes where index_name = '&ind';
set linesize 200;
column index_name for a20;
column table_name for a20;
select index_name,index_type,table_name,partitioned from user_indexes where index_name = '&ind';

等待事件：
set linesize 200
column username for a12
column program for a30
column event for a28
column p1text for a15
column p1 for 999,999,999,999,999
select s.username,s.program,sw.event,sw.p1text,sw.p1 from v$session s,v$session_wait sw
where s.sid=sw.sid and s.status='ACTIVE'
order by sw.p1;
select event,p1 "File #",p2 "Block #",p3 "Reason Code" from v$session_wait
order by event;
where event = 'buffer busy waits';
select owner,segment_name,segment_type,file_id,block_id from dba_extents
where file_id = &P1 and &P2 between block_id and block_id + blocks -1;
column event for a35;
column p1text for a40;
select sid,event,p1,p1text from v$session_wait order by event;

查詢相關SQL：
set linesize 200
set pagesize 1000
column username for a8
column program for a36
select s.sid,s.serial#,s.username,s.program,st.sql_text 
from v$session s,v$sqltext st
where s.sql_hash_value=st.hash_value and s.status='ACTIVE'
order by s.sid,st.piece;
select pid,spid from v$process p,v$session s
where s.sid=&sid and p.addr = s.paddr;
select s.sid,s.serial#,s.username,s.program,st.sql_text 
from v$session s,v$sqltext st,v$process ps
where s.sql_hash_value=st.hash_value
and ps.spid=&sid and s.paddr=ps.addr
order by s.sid,st.piece;
select sql_text from v$sqltext
where hash_value in (select sql_hash_value from v$session
where paddr in (select addr from v$process
where spid=&sid))
order by piece;
select sql_text from v$sqltext
where address in (select sql_address from v$session
where paddr in (select addr from v$process
where spid=&sid))
order by piece;
select sql_text from v$sqltext
where hash_value in (select sql_hash_value from v$session where sid=&sid)
order by piece;
select sql_text from v$sqltext
where address in (select sql_address from v$session where sid=&sid)
order by piece;
select ps.addr,ps.pid,ps.spid,ps.username,ps.program,s.sid,s.username,s.program
from v$process ps,v$session s
where ps.spid=&pid
and s.paddr=ps.addr;
select s.sid,s.serial#,s.username,s.program,st.sql_text 
from v$session s,v$sqltext st,v$process ps
where s.sql_hash_value=st.hash_value
and ps.spid='29863' and s.paddr=ps.addr
order by s.sid,st.piece;

column username for a12
column program for a20
select s.username,s.program,s.osuser,status
from v$session s
where s.status='ACTIVE';

query undotbs used percent:
set linesize 300;
select tablespace_name,segment_name,status,count(*),round(sum(bytes)/1024/1024,2) used_M from dba_undo_extents
group by tablespace_name,segment_name,status;
set linesize 300
column username for a10;
column program for a25;
select s.username,s.program,status,p.spid,st.sql_text from v$session s,v$process p,v$sqltext st where s.status='ACTIVE' and p.addr=s.paddr and st.hash_value=s.sql_hash_value order by s.sid,st.piece;
select snap_id,dbid,instance_number,to_char(snap_time,'yyyy-mm-dd hh24:mi:ss') snap_time from stats$snapshot 
order by INSTANCE_NUMBER,SNAP_ID,SNAP_TIME;
set linesize 120;
column what form. a30;
select job,log_user,what,instance from dba_jobs;
set linesize 120;
column owner for a12;
column segment_name for a24;
column segment_type for a18;
select owner,segment_name,segment_type,file_id,block_id from dba_extents 
where file_id=&file and &block between block_id and block_id + blocks - 1;
select file_id,file_name from dba_data_files where file_id = &file_id;
ANALYZE TABLE ICS_ODS_CUST_ICS_CUR partition(ICS_ODS_CUST_ICS_CUR_PART_1) VALIDATE  STRUCTURE CASCADE;
 
ANALYZE TABLE ODSDATA.&object VALIDATE STRUCTURE CASCADE INTO INVALID_ROWS;
analyze index SYS_C00311764 validate structure cascade;
column owner for a12;
column segment_name for a26;
column segment_type for a16;
column tablespace_name for a20;
column bytes for 999,999,999,999;
 
select owner,segment_name,segment_type,tablespace_name,bytes,blocks,buffer_pool from dba_segments 
where segment_name='&seg'
order by bytes desc;
select segment_name,segment_type,tablespace_name,partition_name,bytes from user_segments 
where segment_name='ODSV_REC_FILE'
and segment_name in (select distinct table_name from user_part_col_statistics where table_name='ODSV_REC_FILE')
order by bytes desc;
col object_name for a26;
select object_name,object_type,status,temporary from user_objects 
where object_name = '&o';

set linesize 180
break on hash_value skip 1 dup
col child_number format 999 heading 'CHILD'
col operation format a82
col cost format 999999
col Kbytes format 999999
col object format a25
select hash_value,
       child_number,
       lpad(' ', 2 * depth) || operation || ' ' || options ||
       decode(id, 0, substr(optimizer, 1, 6) || ' Cost=' || to_char(cost)) operation,
       object_name object,
       cost,
       cardinality,
       round(bytes / 1024) kbytes
from v$sql_plan
where hash_value=&hash_value
/*in
    (select a.sql_hash_value
          from v$session a, v$session_wait b
         where a.sid = b.sid and b.event = 'db file scattered read')*/
order by hash_value, child_number, id;

Top 10 by Buffer Gets:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
        buffer_gets, executions, buffer_gets/executions "Gets/Exec",
        hash_value,address
   FROM V$SQLAREA
  WHERE buffer_gets > 10000
ORDER BY buffer_gets DESC)
WHERE rownum <= 10
;

Top 10 by Physical Reads:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
        disk_reads, executions, disk_reads/executions "Reads/Exec",
        hash_value,address
   FROM V$SQLAREA
  WHERE disk_reads > 1000
ORDER BY disk_reads DESC)
WHERE rownum <= 10
;

Top 10 by Executions:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
        executions, rows_processed, rows_processed/executions "Rows/Exec",
        hash_value,address
   FROM V$SQLAREA
  WHERE executions > 100
ORDER BY executions DESC)
WHERE rownum <= 10
;

Top 10 by Parse Calls:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
        parse_calls, executions, hash_value,address
   FROM V$SQLAREA
  WHERE parse_calls > 1000
ORDER BY parse_calls DESC)
WHERE rownum <= 10
;

Top 10 by Sharable Memory:

set linesize 100
set pagesize 100
SELECT * FROM 
(SELECT substr(sql_text,1,40) sql,
        sharable_mem, executions, hash_value,address
   FROM V$SQLAREA
  WHERE sharable_mem > 1048576
ORDER BY sharable_mem DESC)
WHERE rownum <= 10
;

Top 10 by Version Count:

set linesize 100
set pagesize 100
SELECT * FROM 
(SELECT substr(sql_text,1,40) sql,
        version_count, executions, hash_value,address
   FROM V$SQLAREA
  WHERE version_count > 20
ORDER BY version_count DESC)
WHERE rownum <= 10
;

Top 10 by CPU usage:

set linesize 1000
set pagesize 1000
col  sql_text   format a40
select * from 
(select sql_text, 
round(cpu_time/1000000) cpu_time, 
round(elapsed_time/1000000) elapsed_time, 
disk_reads, 
buffer_gets, 
rows_processed 
from v$sqlarea 
order by cpu_time desc, disk_reads desc
) 
where rownum < 10;


Top 10 for Running Time:

set linesize 1000
set pagesize 1000
col   sql_fulltext  format a40
select * from
(select t.sql_fulltext,
(t.last_active_time-to_date(t.first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60 time
,disk_reads,buffer_gets,rows_processed,
t.last_active_time,t.last_load_time,t.first_load_time
from v$sqlarea t order by t.first_load_time desc)
where rownum < 10;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12272958/viewspace-680636/，如需轉載，請註明出處，否則將追究法律責任。

相關文章