//建立臨時表空間

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

;

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

相關文章