查询死锁进程

查询死锁进程
select a.owner,
a.object_name,
a.subobject_name,
a.object_type,
l.session_id,
l.oracle_username,
l.os_user_name,
se.SID,
se.SERIAL#
from all_objects a, v$locked_object l,V$session se
where a.object_id = l.object_id
and se.OSUSER = l.os_user_name
and l.oracle_username = 'M3_ODS'; --针对某一用户的


--关于死锁的查询和解锁（针对于整个数据库实例的）
1.查看被锁的表
SELECT p.spid,a.serial#,c.object_name,b.session_id,b.oracle_username,
b.os_user_name
FROM v$process p,v$session a,v$locked_object b,all_objects c
WHERE p.addr=a.paddr AND a.process=b.process
ANDc.object_id=b.object_id
2.查看是哪个进程锁的
SELECT sid,serial#,username,osuser FROM v$session where osuser='tangpj'
3.杀掉这个进程alter system kill session 'sid,serial#';

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

查看表空间使用情况语句
SELECT * FROM
(SELECT d.status "Status",
d.tablespace_name "Name",
d.contents "Type",
d.extent_management "Extent Management",
NVL(a.bytes / 1024 / 1024, 0) "Size (M)",
NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024 "Used (M)",
NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) "Used %"
,C.FILE_NAME
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name) f,
dba_data_files c
WHERE d.tablespace_name = a.tablespace_name(+) AND
d.tablespace_name = f.tablespace_name(+) AND
d.tablespace_name =c.tablespace_name(+) AND
NOT
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status "Status",
d.tablespace_name "Name",
d.contents "Type",
d.extent_management "Extent Management",
NVL(a.bytes / 1024 / 1024, 0) "Size (M)",
NVL(t.bytes, 0) / 1024 / 1024 "Used (M)",
NVL(t.bytes / a.bytes * 100, 0) "Used %"
,C.FILE_NAME
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes
from v$temp_extent_pool
group by tablespace_name) t,
dba_data_files c
WHERE d.tablespace_name = a.tablespace_name(+) AND
d.tablespace_name = t.tablespace_name(+) AND
d.tablespace_name =c.tablespace_name(+) AND
d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
--全部表空间按使用大小降排
order by "Used %" desc;

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

查询某个表空间下的所有表信息

select * from dba_tables where tablespace_name
like 'ODS_STAT_DAT'
order by num_rows desc --通过表记录总数来排序
--通常用来查询表的数据量，以便清理不用的表释放表空间


查询某个表空间的全部信息

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


select /*+ RULE */ ls.osuser os_user_name
,https://www.doczj.com/doc/83895399.html,ername user_name
,decode(ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX'
, 'Transaction enqueue lock', 'UL', 'User supplied lock') lock_type
,o.object_name object
, decode(ls.lmode, 1, null, 2, 'Row Share', 3,'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null)
lock_mode, o.owner, ls.sid, ls.serial# serial_num,SPID "UNIX进程号", ls.id1, ls.id2
from sys.dba_objects o
, (select s.osuser
,https://www.doczj.com/doc/83895399.html,ername
,l.type
,l.lmode
,s.sid
,s.serial#
,p.SPID
,l.id1
,l.id2
from v$session s,
v$lock l
,v$process p
where s.sid = l.sid
and p.addr = s.paddr
) ls
where o.object_id = ls.id1
and o.owner <> 'SYS' and https://www.doczj.com/doc/83895399.html,ername is not null
order by o.owner, o.object_name


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

-----查询自己的程序会话方法：
select * from v$process where addr in (
select paddr from v$session where sid = 916)

注意：1.这里的sid的值为自己程序在会话中的值（通过plsql的工具菜单下的会话 选项可以找出）
2.找出查询后的spid值告诉管理员让其杀掉就可以

------------------------------------------------------------------------
----移动表所在位置，到新的表空间里
alter table table_name move tablespace tablespace_name

---查询一个表所在的表空间
select TABLESPACE_NAME from dba_tables where TABLE_NAME = 表名

------------------------------------------------------------------------
--出mss报表的sql语句来源
select * from stat.rpt_cfg_report_define@db_stat
------------------------------------------------------------------------
--查询表空间使用情况
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 1;
--查询表空间的free space
select tablespace_name,
count(*) as extends,
round(sum(bytes) / 1024 / 1024, 2) as MB,
sum(blocks) as blocks
from dba_free_space
group by tablespace_name;


--查询表空间的总容量
select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name;

-------------------------------------重要
--查询表空间使用率
select total.tablespace_name,
round(total.MB, 2) as Total_MB,
round(total.MB - free.MB, 2) as Used_MB,
round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct
from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_free_space
group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name) total
where free.tablespace_name = total.tablespace_name;

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




1.查找当前表级锁的SQL如下：
select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locked_object lo,
dba_objects ao,
v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;

2.杀掉锁表进程：
alter system kill session '436,35123';

3.RAC环境中锁查找：
SELECT inst_id,DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type,block,ctime
FROM GV$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
ORDER BY id1, request;



4.监控当前数据库谁在运行什么SQL语句
select osuser, username, sql_text
from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;



5.找使用CPU多的用户session
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;


6.查看死锁信息
S

ELECT (SELECT username
FROM v$session
WHERE SID = a.SID) blocker, a.SID, 'is blocking',
(SELECT username
FROM v$session
WHERE SID = b.SID) blockee, b.SID
FROM v$lock a, v$lock b
WHERE a.BLOCK = 1 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2;


7.具有最高等待的对象
SELECT o.OWNER,o.object_name, o.object_type, a.event,
SUM (a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a, dba_objects o
WHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATE
AND a.current_obj# = o.object_id
GROUP BY o.OWNER,o.object_name, o.object_type, a.event
ORDER BY total_wait_time DESC;


SELECT a.session_id, s.osuser, s.machine, s.program, o.owner, o.object_name,
o.object_type, a.event,
SUM (a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a, dba_objects o, v$session s
WHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATE
AND a.current_obj# = o.object_id
AND a.session_id = s.SID
GROUP BY o.owner,
o.object_name,
o.object_type,
a.event,
a.session_id,
s.program,
s.machine,
s.osuser
ORDER BY total_wait_time DESC;



8.查询当前连接会话数
select s.value,s.sid,https://www.doczj.com/doc/83895399.html,ername
from
v$sesstat S,v$statname N,v$session A
where
n.statistic#=s.statistic# and
name='session pga memory'
and s.sid=a.sid
order by s.value;



9.等待最多的用户
SELECT s.SID, https://www.doczj.com/doc/83895399.html,ername, SUM (a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a, v$session s
WHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATE
GROUP BY s.SID, https://www.doczj.com/doc/83895399.html,ername
ORDER BY total_wait_time DESC;



10.等待最多的SQL
SELECT a.program, a.session_id, https://www.doczj.com/doc/83895399.html,er_id, https://www.doczj.com/doc/83895399.html,ername, s.sql_text,
SUM (a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a, v$sqlarea s, dba_users d
WHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATE
AND a.sql_id = s.sql_id
AND https://www.doczj.com/doc/83895399.html,er_id = https://www.doczj.com/doc/83895399.html,er_id
GROUP BY a.program, a.session_id, https://www.doczj.com/doc/83895399.html,er_id, s.sql_text, https://www.doczj.com/doc/83895399.html,ername;



11.查看消耗资源最多的SQL
SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls
FROM V$SQLAREA
WHERE buffer_gets > 10000000 OR disk_reads > 1000000
ORDER BY buffer_gets + 100 * disk_reads DESC;



12.查看某条SQL语句的资源消耗
SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls
FROM V$SQLAREA
WHERE hash_Value = 228801498 AND address = hextoraw('CBD8E4B0');


13.查询会话执行的实际SQL
SELECT a.SID, https://www.doczj.com/doc/83895399.html,ername, s.sql_text
FROM v$session a, v$sqltext s
WHERE a.sql_address = s.address
AND a.sql_hash_value = s.hash_value
AND a.status = 'ACTIVE'
ORDER BY https://www.doczj.com/doc/83895399.html,ername, a.SID, s.piece;


14.显示正在等待锁的所有会话
SELECT * FROM DBA_WAITERS;

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

------------------------------------------------------
--以后查表空间剩余空间就这么办
SELECT A.TABLESPACE_NAME,
ROUND(A.TOTAL_SIZE) "total_size(MB)",
ROUND(A.TOTAL_SIZE) - ROUND(B.FREE_SIZE, 3) "used_size(MB)",
ROUND(B.FREE_SIZE, 3) "free_size(MB)",
ROUND(B.FREE_SIZE / TOTAL_SIZE * 100, 2) || '%' FREE_RATE
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 TOTAL_SIZE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 FREE_SIZE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+);


--------------
获得自己的回话:
select username,s.sid,s.serial# from v$session where username='USERNAME'


杀会话：
(1):alter system kill session 'sid,serial#'; ，如果有ora-00031错误，则在后面加immediate，（即alter system kill session 'sid,serial#' immediate; ） 不建议使用

(2)：在命令窗口中，利用exec命令实现：exec kill 建议使用

------------
--查询当前数据库和当前数据库下的所有用户
select * from v$database ；

SELECT username FROM Dba_Users
-------------------------------------------------------------------------
公司写的标准查询表空间状况语句&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

select t.tablespace_name"表空间名称",
round(total_space, 2) "表空间总量(G)",
round(use_space, 2) "使用量(G)",
round(free_space, 2) "剩余量(G)",
round(free_space / total_space * 100, 2) "剩余百分比%",
case
when t.tablespace_name like upper('%DAT') THEN
1
when t.tablespace_name like upper('%IDX') THEN
2
else
3
end "重要等级"
from (select tablespace_name, sum(bytes) / 1024 / 1024 / 1024 total_space
from dba_data_files
group by tablespace_name) t,
(select tablespace_name, sum(bytes) / 1024 / 1024 / 1024 use_space
from dba_segments
group by tablespace_name) t2,
(select tablespace_name, sum(bytes) / 1024 / 1024 / 1024 free_space
from dba_free_space
group by tablespace_name) t3
where t.tablespace_name = t2.tablespace_name(+)
and t.tablespace_name = t3.tablespace_name(+)
and t.tablespace_name NOT LIKE 'SYS%'
order by "重要等级", t.tablespace_name;
--
select owner, table_name, compression
from dba_tables
where table_name like 'ACCT_ITEM_AGGR_201%';
--
select 'TRUNCATE TABLE '||OWNER||'.'||SEGMENT_NAME||';'
OWNER,
SEGMENT_NAME,
tablespace_name,
SUM(BYTES) / 1024 / 1024 / 1024 GBYTES
from dba_segments
where 1 = 1
and tablespace_name = 'ODS_M3_DAT'
--and segment_type = 'INDEX'
--and segment_name like 'MOBILE_SMS_EVENT_11207%'
and segment_name like '%STAT_MOBILE_CALL_E_201206%'
/*and segment_name in
(select

index_name
from dba_indexes
where table_name in (select replace(table_name, 'XXXXX', '11207')
from query_all.xt_event_info))*/
GROUP BY OWNER, SEGMENT_NAME, tablespace_name
order by sum(bytes) desc;
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
select sysdate from dual;
select * from dba_jobs_running;


-------------------------------------------------------------------------------
求最近执行的sql行数：变量名:=sql%rowcount 这是oracle基本语法

-------------------------------------------------------------------------------
-------有关权限的操作
DBA_SYS_PRIVS: 查询某个用户所拥有的系统权限
USER_SYS_PRIVS: 当前用户所拥有的系统权限
SESSION_PRIVS: 当前用户所拥有的全部权限

ROLE_SYS_PRIVS:某个角色所拥有的系统权限
注意: 要以SYS用户登陆查询这个视图,否则返回空.

ROLE_ROLE_PRIVS: 当前角色被赋予的角色
SESSION_ROLES: 当前用户被激活的角色
USER_ROLE_PRIVS: 当前用户被授予的角色


另外还有针对表的访问权限的视图:

TABLE_PRIVILEGES
ALL_TAB_PRIVS
OLE_TAB_PRIVS: 某个角色被赋予的相关表的权限

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
select * from stat.sys_domain@db_stat
查询账务模块表中的字段值域的表，重中之重
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&


----------------------------------------------------------------
****往分区表中的某个分区插入数据的语法如下：
insert into table_name partition(分区名称)
（字段列表） values （值列表）

或者是：
insert into table_name partition(分区名称)
select 语句

****明确指定分区查询
select * from table_name where 条件 partition(partition_name)
---------------

----查询一个表的大小（占用表空间大小）
1 select owner,table_name, NUM_ROWS, BLOCKS*AAA/1024/1024 "Size M", EMPTY_BLOCKS, LAST_ANALYZED
from dba_tables where table_name='XXX'; 表名处要大写
注意：AAA is the value of db_block_size 此法有待研究

2
单位B:select BYTES from dba_segments where segment_name = table_name and segment_type = 'TABLE'

单位KB:select BYTES/1024 “size K” from dba_segments where segment_name = table_name and segment_type = 'TABLE'

单位MB:select BYTES/1024/1024 “size M” from dba_segments where segment_name = table_name and segment_type = 'TABLE'

说明：注意table_name应为引号引起来的全部大写表名称，还有这样查询的不包括表上所建的索引占用的空间大小，
-------------------------------------------------------------------
--row_number() over(partition by column order by column)
说明：该东西是分析函数，主要用来查询以某些字段分组并以某些字段进行排序
得出的结果是排序后的行号，主要用来去

重的操作，该种去重不同于记录
内容全部一致，只有rowid不一致的，因此有时一般的通过rowid去重达不到
预想效果（delete from table_name where rowid not in（select max（rowid）from
table_name group by ziduan））,所以当遇到要去最新数据的时候通常使用到
该种分析函数

select TABLE_NAME,RUN_DATE,ROW_CNT,STATUS,STATUS_DESC,BEGIN_DATE,END_DATE,
RUN_TIME,LOG_INFO,IP
from(
select TABLE_NAME,RUN_DATE,ROW_CNT,STATUS,STATUS_DESC,BEGIN_DATE,END_DATE,
RUN_TIME,LOG_INFO,IP,
row_number() over(partition by table_name,run_date order by begin_date desc) rn
from bwt.ods_exec_info_log t
where t.log_info='程序执行完成' and length(run_date)=6
group by
TABLE_NAME,RUN_DATE,ROW_CNT,STATUS,STATUS_DESC,BEGIN_DATE,END_DATE,
RUN_TIME,LOG_INFO,IP
order by run_date desc,begin_date desc)
where rn=1
---------------------------------------------------------------------
--根据表名查询其所在的数据库对象（过程）
select * from all_source where upper(text) like '%table_name%'

--查询session还有多长时间运行完
SELECT SID,TIME_REMAINING FROM V$SESSION_LONGOPS WHERE TIME_REMAINING<>0 and sid=326
---------------------------------------------------------------------
--很重要
ORACLE中能杀会话的用户名和密码：
用户名：YHYH_ODSWH
密码：yahua#456
语句是：exec sys.p_kill_session(449);（在command窗口的Editor窗口中按F8运行）

--重要
地市下放用户名和密码
query_all/query_all21

--其他
汇总层的用户名密码
STAT_ODS DF#80_jDWO2Kl1
数据质量的用户名密码
AUDIT_ODS lY3x#Qi_sVA0

m3_ods/m3_ods m3_ods/m2_ods m1_ods/m1_ods md/

------------------------------------------------------------------------------------
--根据表名查询其所在的数据库对象（过程）
select * from all_source where upper(text) like '%table_name%'

--查询正在执行的session及其sql文本
select https://www.doczj.com/doc/83895399.html,ername, a.sid, b.SQL_TEXT, b.SQL_FULLTEXT
from v$session a, v$sqlarea b
where a.sql_address = b.address


---查询执行过的session及其sql文本
select b.SQL_TEXT,b.FIRST_LOAD_TIME,b.SQL_FULLTEXT
from v$sqlarea b
where b.FIRST_LOAD_TIME between '2009-10-15/09:24:47' and
'2009-10-15/09:24:47' order by b.FIRST_LOAD_TIME
此方法好处可以查看某一时间段执行过的sql，并且 SQL_FULLTEXT 包含了完整的 sql 语句)

---其他
select OSUSER,PROGRAM,USERNAME,SCHEMANAME,B.Cpu_Time,STATUS,B.SQL_TEXT
from V$SESSION A LEFT JOIN V$SQL B ON A.SQL_ADDRESS=B.ADDRESS AND A.SQL_HASH_VALUE=B.HASH_VALUE order by b.cpu_time desc；

select address, sql_text, piece
from v$session, v$sqltext
where address = sql_address
-- and ma

chine = < you machine name >
order by address, piece；

--查找前十条性能差的sql.

SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,
COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea
order BY disk_reads DESC )where ROWNUM<10 ;




--查看占io较大的正在运行的session

SELECT se.sid,se.serial#,pr.SPID,https://www.doczj.com/doc/83895399.html,ername,se.status,
se.terminal,se.program,se.MODULE,、se.sql_address,st.event,st.p1text,si.physical_reads,
si.block_changes
FROM v$session se,v$session_wait st,v$sess_io si,v$process pr WHERE st.sid=se.sid AND st.sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st.wait_time=0 AND st.event NOT LIKE '%SQL%'
ORDER BY physical_reads DESC



--查询正在运行的回话及其sql文本
select A.SID,
A.MACHINE,
A.PROGRAM,
A.OSUSER,
A.LOGON_TIME,
https://www.doczj.com/doc/83895399.html,ERNAME,
B.SQL_TEXT
from gv$session A, GV$SQL B
where A.status = 'ACTIVE'
AND A.SQL_HASH_VALUE = B.HASH_VALUE
----------------------------------------------------------------------------
DBA们经常需要监控数据库中一些花费大量时间的操作，如备份恢复，收集统计信息，排序都会记录在这个视图当中。
这个视图主要是显示运行时间超过6秒的数据库操作的状态.所以对于数据库监控一些耗时的操作是非常有意义的。
并且可以看到某个进程的执行进度。
来看看这个视图的结构：
SQL> desc v$session_longops
名称 是否为空? 类型
-------------------

SID NUMBER
SERIAL# NUMBER
OPNAME VARCHAR2(64)
TARGET VARCHAR2(64)
TARGET_DESC VARCHAR2(32)
SOFAR NUMBER
TOTALWORK NUMBER
UNITS VARCHAR2(32)
START_TIME DATE
LAST_UPDATE_TIME DATE
TIME_REMAINING NUMBER
ELAPSED_SECONDS NUMBER
CONTEXT NUMBER
MESSAGE VARCHAR2(512)
USERNAME VARCHAR2(30)
SQL_ADDRESS RAW(4)
SQL_HASH_VALUE NUMBER
QCSID NUMBER


其中SID和SERIAL#是与v$session中的匹配的，
OPNAME：指长时间执行的操作名.如：Table Scan
TARGET：被操作的object_name. 如：tableA
TARGET_DESC：描述target的内容
SOFAR：这个是需要着重去关注的，表示已要完成的工作数，如扫描了多少个块。
TOTALWORK：指目标对象一共有多少数量（预计）。如块的数量。
UNITS：
START_TIME：进程的开始时间
LAST_UPDATE_TIM：最后一次调用set_session_longops的时间
TIME_REMAINING： 估计还需要多少时间完成，单位为秒
ELAPSED_SECONDS：指从开始操作时间到最后更新时间
CONTEXT：
MESSAGE：对于操作的完整描述，包括进度和操作内容。
USERNAME：与v$session中的一样。
SQL_ADDRESS：关联v$sql
SQL_HASH_VALUE：关联v$sql
QCSID：主要是并行查询一起使用。

SQL> conn aa/admin
已连接。
SQL> insert into test select * from test ;

已创建898432行


打开另一个会话,可使用以下脚本来观察之前

那个会话所作的操作的执行状态.

SQL> select b.*
2 from v$session a, v$session_longops b
3 where a.sid=b.sid
4 and a.serial#=b.serial#
5 /


--查询正在运行的回话还有多长时间运行完 单位：秒
SELECT SID,TIME_REMAINING FROM V$SESSION_LONGOPS WHERE TIME_REMAINING<>0


SID SERIAL# OPNAME TARGET SOFAR TOTALWORK UNITS START_TIME LAST_UPDATE_TIME TIME_REMAINING ELAPSED_SECONDS CONTEXT MESSAGE
---- ---------- --------------
10 74 Table Scan AA.TEST 6623 12389 Blocks 2007-5-7 2: 2007-5-7 2:52:03 30 34 0 Table Scan: AA.TEST: 6623 out of 12389 Blocks done

SQL>

可以看到现在是在对aa.test作表扫描。预计有12389个blocks,已然完成了6623个,已经使用了34秒，大概还需要30秒可以完成。
可以看出大概195块/秒的速度来扫描表。

---------------------------------------------------------------------------------------------------------------------------
--查询表的说明和表的字段说明的视图 ：dba_tab_comments 表的注释说明
dba_tab_columns 表的字段的注释说明

select * from dba_tab_comments where table_name like '%查询的东西%'；
select * from dba_tab_columns where table_name like '%查询的东西%'
---------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
--查看表空间使用率
select total.tablespace_name,
round(total.GB, 2) as Total_GB,
round(total.GB - free.GB, 2) as Used_GB,
round((1 - free.GB / total.GB) * 100, 2) as Used_Percent
from (select tablespace_name, sum(bytes) / 1024 / 1024/1024 as GB
from dba_free_space
group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024/1024 as GB
from dba_data_files
group by tablespace_name) total
where free.tablespace_name = total.tablespace_name;




--查看表空间中每个表的占用情况


SELECT t.tablespace_name,t.owner,t.segment_name, SUM(t.BYTES) / 1024 / 1024 / 1024 table_size
FROM DBA_SEGMENTS t
WHERE t.tablespace_name = UPPER('ODS_M1_DAT')
GROUP BY t.tablespace_name,t.owner,t.segment_name
order by SUM(t.BYTES) / 1024 / 1024 / 1024 desc;


--查看undo表空间使用情况
SELECT tablespace_name, status, SUM (bytes) / 1024 / 1024 "Bytes(M)"
FROM dba_undo_extents
GROUP BY tablespace_name, status;



--查看用户临时表空间大小
select tablespace_name,file_name,bytes/1024/1024 "file_size(M)",autoextensible from dba_temp_files;


--查看用户默认表空间和临时表空间
select username,temporary_tablespace,default_tablespace from dba_users

--查看表空间使用率
SELECT temp_used.tablespace_name,
total - used as "Free",
total as "Total",
round(nvl(to

tal - used, 0) * 100 / total, 3) "Free percent"
FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used
FROM sys.GV_$TEMP_SPACE_HEADER
GROUP BY tablespace_name) temp_used,
(SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
FROM dba_temp_files
GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name



--查看临时表空间正在使用情况
select sort.TABLESPACE, sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
from v$sort_usage sort, v$session sess, v$sql sql
where sort.SESSION_ADDR = sess.SADDR(+)
and sess.SQL_ADDRESS=sql.ADDRESS(+)
order by blocks desc;


---------------------------------------------------------------------------------------------
监控一个session执行的状况，其中的sid就是要监控的session编号

select sid, opname,start_time, message, trunc(sofar/totalwork*100,2) || '%' as perwork
from v$session_longops where sid = 404 order by start_time desc;
---------------------------------------------------------------------------------------------
---------------------
查看sql文本的执行计划：
选中要查看的sql文本，按F5键即可
---------------------
如果想要把sql文本的执行计划导入到excel中中，方法如下：

首先，执行如下语句
explain plan for “要查看的sql文本”;

然后执行:select * from table(DBMS_XPLAN.display)
看到执行计划的结果

最后导出.csv文件即可，该文件即是excel

---------------------------------------------------------------------------------------------
--查询session的进程编号
select spid from v$process where addr=(select paddr from v$sesssion where sid='')


-------------------------------------------------------------------------------------------
--几个表合成一个分区表

如果想要把已经存在的几个表放到一个新建的分区表中去，一个表的数据放到一个分区中去，方法就是
修改表名为分区名称就可以，这样操作快捷，数据库这是改变了名称，并没有对数据进行处理，不过前提
是需要表中存在分区字段，并且分区表也是根据该字段进行分区的，sql如下：

alter table table_name(分区表) exchange partition/subpartition partition_name(分区名称)
with table table_name(换名称为分区名称的表)
---------------------------------------------------------------------------------------------

nohup ./kitchen.sh /norep /file "../RESOURCE_FILE/ALL/DISPATCH/new20130818.kjb" >../LOG/new_0915.log &
-------------------------------------------------------------------------
厚德载雾，自强不吸。 霾头苦干，再创灰黄！＂事实将再次证明： 雾以吸为贵！