----调整DG的传输进程为ARCH
ics:
alter system set log_archive_dest_3 ='SERVICE=lics arch async noaffirm reopen=60 valid_for=(online_logfiles,primary_role) db_unique_name=lics' scope =both;
----增加收集统计信息JOB
DECLARE
v_job BINARY_INTEGER;
BEGIN
dbms_job.submit(job => v_job,
what => 'dbmgr.gather_cbo_stats_auto.process_wait_objects;',
next_date => SYSDATE+1,
INTERVAL => 'dbmgr.gather_cbo_stats_auto.gather_job_interval');
INSERT INTO stats_gather_jobs
(job, job_type)
VALUES
(v_job, 'GATHER');
COMMIT;
END;
----ORA 08104
select i.obj#, i.flags, u.name, o.name, o.type#
from sys.obj$ o, sys.user$ u, sys.ind$ idx, sys.ind_online$ i
where bitand(i.flags, 512) = 512 and o.obj#=idx.obj# and
o.owner# = u.user# and idx.obj#=i.obj#;
使用dbms_repair.online_index_clean清理；
DECLARE
RetVal BOOLEAN;
OBJECT_ID BINARY_INTEGER;
WAIT_FOR_LOCK BINARY_INTEGER;
BEGIN
OBJECT_ID := 3143711;
-- 我的数据库中非法索引的id为3143711;
WAIT_FOR_LOCK := NULL;
RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN();
COMMIT;
END;
/
-----MIS COW库起库脚本
/etc/paic/cow/start_cow2open.sh
是否启用block change tracking，oracle建议在使用增量备份策略时启用此功能，可以加速增量备份。
配置方法可以如下：
启用Eg：SQL> alter database enable block change tracking using file '/fratest/fra/LPMS/rman_block_change_track.log' ;
禁用 Eg：SQL> alter database disable block change tracking;
----PA AGENT
PA agent的启动，停止脚本：
只执行启动脚本：
Cd $HOME/quest/pa_agent/agents/bin
启动： ./quest_launcher_daemon start
停止： ./quest_launcher_daemon stop
-----SYSTEMSTATE,HANG分析
19:16:20 sys@t5ics>
19:16:21 sys@t5ics>oradebug setmypid;
Statement processed.
19:16:41 sys@t5ics>oradebug dump systemstate 266;
Statement processed.
19:16:58 sys@t5ics>oradebug tracefile_name
/paic/t5ics/rdbms/oracle/diag/rdbms/t5ics/t5ics/trace/t5ics_ora_5587.trc
----按照用户角度去统计CPU消耗情况
select u.username,
round(sum(cpu_time_delta) / 1000000, 0) CPU_TOTAL,
sum(cpu_time_delta)/
(select sum(cpu_time_delta) from dba_hist_sqlstat s, dba_hist_snapshot t
where s.snap_id = t.snap_id
and ((t.begin_interval_time between
to_date('2017-03-27', 'yyyy-mm-dd') and
to_date('2017-04-27', 'yyyy-mm-dd'))))
*100 PCT
from dba_hist_sqlstat s, dba_hist_snapshot t ,dba_users u
where s.snap_id = t.snap_id
and s.parsing_user_id=u.user_id
and ((t.begin_interval_time between
to_date('2017-03-27', 'yyyy-mm-dd') and
to_date('2017-04-27', 'yyyy-mm-dd')))
group by u.username
order by 2 desc;
----确认历史备份
select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name='CHS_CHANNEL_CLAIM_INFO'
order by stats_update_time desc;
----根据时间点做恢复
begin
dbms_stats.restore_table_stats(ownname => 'LIFEDATA',tabname => 'CHS_CHANNEL_CLAIM_INFO',as_of_timestamp => to_timestamp('2017-04-25 00:06:58', 'yyyy-MM-dd HH24:MI:ss'));
end;
/
这个应该就是使用ORACLE自己的备份去做恢复。
时间点指向当天第一次的备份，我觉得就OK了。
---提前分配表的DATA BLOCK
alter table chad_insert_test allocate extent (SIZE 1G);
---回收表的空间数据块
alter table chad_insert_test DEALLOCATE UNUSED KEEP 200M;
（保留200M的空间）
alter table chad_insert_test DEALLOCATE UNUSED;
(回收所有未使用的块)
----回收表的高水位
alter table chad_insert_test move;
------RMAN注册CATALOG
登陆到数据库的主机、备机，
su – dbcom
cd /etc/paic/network
more tnsnames.ora|grep cat11g
----以上命令，如果没有输出，则增加cat11g数据库连接串。
CAT11G=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=cat11g.db.paic.com.cn)
(PORT=1534)
)
(CONNECT_DATA=
(SERVICE_NAME=cat11g)
)
)
注意：主机、备机都需要作以上操作，否则db切换主机后，rman备份会无法进行。
Rman注册：
步骤如下：
#rman target / catalog rman11g/rman11g@cat11g ---(对应的catalog数据库)
RMAN> register database;
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 90 DAYS;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';
RMAN> report schema;
---批量KILL LOCAL=NO进程
ps -ef|grep -v grep|grep LOCAL=NO |grep ics |grep -v icss|awk '{print $2}'|xargs kill -9
-查看历史PATCH信息
col comments for a40
select * from sys.registry$history
order by action_time desc;
---segment physical reads 排序
SELECT dhso.object_name,
sum(dhss.physical_reads_delta),
sum(dhss.physical_read_requests_delta),
/* sum(db_block_changes_delta) BLOCK_CHANGED*/
round(sum(dhss.physical_reads_delta) /
(SELECT sum(dhss.physical_reads_delta)
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhso.obj#
AND dhss.dataobj# = dhso.dataobj#
AND dhs.snap_id between 146363 and 146460 ----146460 */
HAVING sum(dhss.physical_reads_delta) > 0) * 100,
2) precent
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhso.obj#
AND dhss.dataobj# = dhso.dataobj#
/* AND begin_interval_time BETWEEN to_date('2017-03-31 15:00','YY-MM-DD HH24:MI')
AND to_date('2017-03-31 23:59','YY-MM-DD HH24:MI')*/
AND dhs.snap_id between 146363 and 146460 ----146460 */
GROUP BY dhso.object_name
HAVING sum(dhss.physical_reads_delta) > 0
ORDER BY 4 desc;
-----disk_read
select sql_id,
sum(s.disk_reads_delta) "disk_reads(number of blocks)",
round(sum(s.disk_reads_delta)/1700786998*100,2) "disk_reads(%)"
from dba_hist_snapshot sn, dba_hist_sqlstat s
where s.snap_id = sn.snap_id and sn.snap_id between 146422 and 146460
and s.instance_number=sn.instance_number
and s.executions_delta>0 and disk_reads_delta>0
group by sql_id
order by 3 desc;
----executions
select sql_id,
sum(s.executions_delta) "executions",
round(sum(s.executions_delta)/2002856361 *100,2) "executions(%)"
from dba_hist_snapshot sn, dba_hist_sqlstat s
where s.snap_id = sn.snap_id and sn.snap_id between 146422 and 146460
and s.instance_number=sn.instance_number
and s.executions_delta>0
group by sql_id
order by 3 desc;
---CPU_TIME
select sql_id,
round(sum(s.cpu_time_delta)/1000000,2) "CPU_TIME",
round(sum(s.cpu_time_delta)/1000000/8162126 *100,2) "CPU_TIME(%)"
from dba_hist_snapshot sn, dba_hist_sqlstat s
where s.snap_id = sn.snap_id and sn.snap_id between 146422 and 146460
and s.instance_number=sn.instance_number
and s.executions_delta>0
group by sql_id
order by 3 desc;
------其中的总数脚本：
select sum(s.disk_reads_delta) disk_reads
from dba_hist_snapshot sn, dba_hist_sqlstat s
where s.snap_id = sn.snap_id and sn.snap_id between 146422 and 146460
and s.instance_number=sn.instance_number
and s.executions_delta>0 and disk_reads_delta>0
----统计failed pared记录趋势
with s as (
select sn.instance_number, sn.snap_id, sn.end_interval_time stime, sy.value,
(sy.value - lag(sy.value, 1) over (partition by sn.instance_number order by sn.snap_id)) parse_failed_time
from dba_hist_snapshot sn, dba_hist_sys_time_model sy
where sy.snap_id = sn.snap_id and sy.stat_name = 'failed parse elapsed time' and sy.instance_number = sn.instance_number
)
select instance_number , snap_id, to_char(stime,'YYYY/MM/DD HH24:MI:SS'), round(parse_failed_time/1000000,2)
from s
where instance_number = 1 and snap_id between 146266 and 146459
order by instance_number, snap_id
----统计事务数变化趋势
select to_char(begin_time,'yyyy/mm/dd hh24:mi:ss'),to_char(end_time,'yyyy/dd/dd hh24:mi:ss'),metric_unit,round(maxval,0),round(average,0) from dba_hist_sysmetric_summary
where metric_unit='Transactions Per Second'
and snap_id between 152250 and 152260
order by 1 ;
----统计EVENT的趋势变化
with t as (
select s.snap_id, s.instance_number, s.end_interval_time,event_name,
total_waits - lag(total_waits, 1) over (partition by s.instance_number,event_name order by s.snap_id) waits,
(time_waited_micro - lag(time_waited_micro, 1) over (partition by s.instance_number,event_name order by s.snap_id))/1000 twt
from dba_hist_system_event ev, dba_hist_snapshot s
where ev.instance_number = s.instance_number and ev.snap_id = s.snap_id
and event_name in ('db file sequential read','log file parallel write') ----,'log file parallel write','db file sequential read' 'log file sync'
and s.end_interval_time BETWEEN TO_DATE('20170427 15:00:00', 'yyyymmdd hh24:mi:ss') AND TO_DATE('20170427 23:59:59', 'yyyymmdd hh24:mi:ss') )
select to_char(end_interval_time,'YYYYMMDD HH24:MI'), instance_number,event_name, sum(waits), sum(twt), round(sum(twt)/sum(waits), 2) wt
from t
where (to_char(end_interval_time, 'hh24') between 0 and 12 or to_char(end_interval_time, 'hh24') between 13 and 23)
group by to_char(end_interval_time,'YYYYMMDD HH24:MI'), instance_number,event_name
order by 1, event_name;
----prelim
z4cs2011:t4lucd0 > sqlplus -prelim '/ as sysdba';
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Mar 27 16:23:59 2017
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
SQL>
SQL>
SQL> select status from v$instance;
select status from v$instance
*
ERROR at line 1:
ORA-01012: not logged on
SQL> conn / as sysdba
Prelim connection established
----格式化SQLPLAN
Select Sp.Id,
Lpad(' ', Sp.Depth * 3, ' ') || Sp.Operation || ' ' || Sp.Options Operation,
Sp.Object_Name,cardinality,access_predicates,filter_predicates
From dba_hist_sql_plan Sp
Where sql_id='0zkq1vju66sxx' --And plan_hash_value =1155695790
Order By Id;
-----Strace常用
strace -c -p 40292
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
85.55 0.010406 4 2962 241 read
9.65 0.001174 5 241 poll
2.32 0.000282 0 12320 semop
1.92 0.000234 1 282 sendto
0.56 0.000068 0 3114 lseek
0.00 0.000000 0 20 write
0.00 0.000000 0 12 open
0.00 0.000000 0 13 close
0.00 0.000000 0 5 select
0.00 0.000000 0 518 241 recvfrom
0.00 0.000000 0 1 kill
0.00 0.000000 0 1 fdatasync
strace -o server.strace -Ttt -p 40292 （进程40292按照时间排序输出到server.strace）
-----清理SHARED_POOL中某SQL的CURSOR
select sql_id,address,hash_value from v$sqlarea
where sql_id='a3wtrs8cvx687';
SQL> alter session set events '5614566 trace name context forever';
Session altered.
SQL> exec sys.dbms_shared_pool.purge('00000003FF82C0A8,431921415','C');
PL/SQL procedure successfully completed.
SQL> alter session set events '5614566 trace name context off';
Session altered
----分析监听日志
[padba@cnsz081003 chad]$
./netlog_pass.pl -h -t '27-FEB-2017 17:[4-5][0-9]' elis.log | sort -n -k 3 -r
./netlog_pass.pl -h -t '28-FEB-2017 10:[0-5][0-59]' chad | sort -n -k 3 -r
./netlog_pass.pl -h -t '20-FEB-2017 08:[4-5][0-9]' log_1799.xml | sort -n -k 3 -r
./netlog_pass.pl -h -t '05-APR-2017 10:[0-1][0-9]' luqd0.log | sort -n -k 3 -r
./lsnr.sh -h -t "30-JUN-2017 11:[0-2][0-9]:[0-2][0-9]" lugz0.log
./lsnr.sh -h -t "30-JUN-2017 11:[0-1][0-9]:[0-5][0-9]" lugz0.log
./lsnr.sh -t "30-JUN-2017 11:[0-2][0-9]:[0-5][0-9]" lugz0.log
---查看审计配置
select * from DBA_OBJ_AUDIT_OPTS; ----对象的审计
select * from DBA_PRIV_AUDIT_OPTS; ----权限的审计
---根据字符集查找（123或abc）
egrep '123|abc' filename // 用egrep同样可以实现
---根据SID查找child_children的ADDR
select ADDR from v$latch_children
where name='cache buffers chains'
and addr in (select a.P1RAW from v$session a
where sid='&sid' );
-----根据V$LATCH_CHILDREN的ADDR列来查找LATCH涉及的对象
SELECT /*+ RULE */
e.owner || '.' || e.segment_name segment_name,
e.extent_id extent#,
x.dbablk - e.block_id + 1 block#,
x.tch,
l.child#
FROM sys.v$latch_children l,
sys.x$bh x,
sys.dba_extents e
WHERE x.hladdr in ('0000000F3730E6C8','0000000F3D97D720','0000000F40A90180')
AND e.file_id = x.file#
AND x.hladdr = l.addr
AND x.dbablk BETWEEN e.block_id AND e.block_id + e.blocks -1
ORDER BY x.tch DESC;
----找对象
select /*+rule*/
x.hladdr, x.obj, o.object_name, x.dbarfil, x.dbablk, x.tch
from x$bh x, dba_objects o
where x.hladdr in
(select distinct p1raw
from v$session
where event = 'latch: cache buffers chains')
and o.data_object_id = x.obj
and rownum<11
order by x.tch desc;
---根据HLADDR来查
select p1raw,count(*) from v$session
where event='latch: cache buffers chains'
group by p1raw
order by 2 desc;
select /*+rule*/ x.hladdr,x.obj,o.object_name,x.dbarfil,x.dbablk,x.tch
from x$bh x,dba_objects o
where x.hladdr ='0000000F44308DC8' and o.data_object_id=x.obj
order by x.tch desc;
---- linux删除过期文件
find . -name "*.trc" -mtime +30 -exec rm -rf {} \;
----获取行锁的ROWID
select Dbms_Rowid.Rowid_Create(1, Data_Object_Id, Lo_Fno, Lo_Block, 0) from dual;
(其中的OBJECT_ID,FILE_NO,BLOCK_NO可以从V$SESSION或者ASH中获取)
----调整10G的JOB
begin
sys.dbms_ijob.broken( 6795,FALSE);
end;
begin
sys.dbms_ijob.next_date(job => 6795,next_date => to_date('2016-12-06 17:00:00','YYYY-MM-DD HH24:MI:SS'));
END;
查看CG CPU情况：
/opt/cgtools/cginfo -t perf -s cpu
-----批量KILL PG库的会话
select pg_terminate_backend(pid) from pg_stat_activity
where usename='rasopr' and query like '%select MAX(end_time) from rais_sync_record%';
调整PROFILE的LIMIT：
--- ISW库查询TABLESPACE情况
select db_name,
snap_date,
sum(ts_size) / 1024 / 1024 / 1024 total,
sum(free_bytes) / 1024 / 1024 / 1024 free,
(sum(ts_size) - sum(free_bytes)) / 1024 / 1024 / 1024 used
from dbqc$ts_info
where db_name in ('PACRPT', 'EPODS', 'EPCIS')
and snap_date > date
'2010-1-1'
and (to_char(snap_date, 'mmdd') = '0101' or
to_char(snap_date, 'yyyymmdd') = '20161010')
and to_char(snap_date, 'hh24') between '01' and '06'
group by db_name, snap_date
-----查询ORACLE被LOCKED的对象
select object_name, s.sid, s.serial#, p.spid
from v$locked_object l, dba_objects o, v$session s, v$process p
where l.object_id = o.object_id and l.session_id = s.sid and s.paddr = p.addr;
-----DG应用状态
SELECT pid, PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY where process='RFS' or process='MRP0';
----调整PROFILE的LIMIT
alter profile PRF_LIFEJ2EE limit SESSIONS_PER_USER 1800;
----统计归档日志
select to_char(completion_time,'yyyy-mm-dd hh24'),count(*) from v$archived_log
where name not in ('llass','rlass')
and completion_time+3 > sysdate
group by to_char(completion_time,'yyyy-mm-dd hh24')
order by 1 desc;
----
物理CPU个数：
cat /proc/cpuinfo | grep "physical id" | sort | uniq
每个物理CPU的逻辑核数：
cat /proc/cpuinfo | grep "cores" | uniq
系统整个cpu线程数：
cat /proc/cpuinfo | grep "processor" | wc -l
CPU型号：
cat /proc/cpuinfo | grep "model name" | uniq
MYSQL导库的表结构（不包括数据）：
mysqldump -d xbrl > /tmp/xrbl.sql
MYSQL导表（导出xbrl库中 2个表psx_dimension_group psx_dimensions）：
mysqldump xbrl psx_dimension_group psx_dimensions > /tmp/dump_xbrl_tables.sql
查看隐含参数：
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from x$ksppi a, x$ksppcv b
where a.indx = b.indx and a.ksppinm like '%&name%';
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from x$ksppi a, x$ksppcv b
where a.indx = b.indx and a.ksppdesc like '%shared mutex wait time%';
kks shared mutex wait time – cs
---计算LOB表的大小
SELECT
(SELECT NVL(SUM(S.BYTES),0) -- The Table Segment size
FROM DBA_SEGMENTS S
WHERE S.OWNER = UPPER('ICSDATA') AND
(S.SEGMENT_NAME = UPPER('ICS_BENEFIT_DEMO_DETAIL'))) +
(SELECT NVL(SUM(S.BYTES),0) -- The Lob Segment Size
FROM DBA_SEGMENTS S, DBA_LOBS L
WHERE S.OWNER = UPPER('ICSDATA') AND
(L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('ICS_BENEFIT_DEMO_DETAIL') AND L.OWNER = UPPER('ICSDATA'))) +
(SELECT NVL(SUM(S.BYTES),0) -- The Lob Index size
FROM DBA_SEGMENTS S, DBA_INDEXES I
WHERE S.OWNER = UPPER('ICSDATA') AND
(I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('ICS_BENEFIT_DEMO_DETAIL') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('ICSDATA')))
"TOTAL TABLE SIZE(BYTES)"
FROM DUAL;
查看占用cpu最高的进程
ps aux|head -1;ps aux|grep -v PID|sort -rn -k +3|head
ps -eo pid,pcpu,args | sort +1n
或者top （然后按下M，注意这里是大写）
查看占用内存最高的进程
ps aux|head -1;ps aux|grep -v PID|sort -rn -k +4|head
---字符集相关
select userenv('language') from dual; ----数据库字符集
select lengthb('啊') from dual; ---一个汉字占用字节
----COW库起库脚本
查看/etc/paic/cow/start_cow2open.sh 里的如下部分可以知道部署COW起库用的参数文件为：/etc/paic/network/dba/init${ORACLE_SID}.ora
if [ -f /etc/paic/network/dba/init${ORACLE_SID}.ora ]
then
echo "pfile exist,begin to create spfile from special pfile"
sqlplus '/as sysdba'<<!
create spfile from pfile='/etc/paic/network/dba/init${ORACLE_SID}.ora';
exit;
!
------DB BLOCK CHANGE情况
SELECT to_char(begin_interval_time,'YY-MM-DD HH24') snap_time,
dhso.object_name,
sum(db_block_changes_delta) BLOCK_CHANGED
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhso.obj#
AND dhss.dataobj# = dhso.dataobj#
AND begin_interval_time BETWEEN to_date('2016-09-27 13:00','YY-MM-DD HH24:MI')
AND to_date('2016-09-27 14:00','YY-MM-DD HH24:MI')
GROUP BY to_char(begin_interval_time,'YY-MM-DD HH24'),
dhso.object_name
HAVING sum(db_block_changes_delta) > 0
ORDER BY sum(db_block_changes_delta) desc ;
----查询被偷取的buffer cache情况
with cur as
(select d.CURRENT_SIZE/ 1024 / 1024 CURRENT_SIZE
from v$sga_dynamic_components d
where d.COMPONENT='DEFAULT buffer cache'),
spec as
(select name,to_number(value)/ 1024 / 1024 db_cache_size
from V$SPPARAMETER
where name='db_cache_size')
select spec.name,
cur.CURRENT_SIZE "CURRENT_SIZE(M)",
spec.db_cache_size "PARAMETER_VALUE(M)",
'FF00FF' "#stolen_mem#",
(spec.db_cache_size-cur.CURRENT_SIZE)||'M' stolen_mem
from cur, spec
where (spec.db_cache_size-cur.CURRENT_SIZE)-2.5*1024>0
or spec.db_cache_size/2-cur.CURRENT_SIZE>0
----查回滚速度
select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",
to_char(sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400),'yyyy-mm-dd hh24:mi:ss')
from v$fast_start_transactions;
----查询高版本的SQL
Doc ID 296377.1
https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=11ymqf0dny_4&_afrLoop=371676928895319#aref_section21
.直接查询
select sql_id,version_count, ADDRESS,sql_text
from v$sqlarea
where version_count > 100
order by version_count, hash_value;
?Version 9.2.X.X and below :
select * from v$sql_shared_cursor where kglhdpar = '0000000386BC2E58'
?Version 10.0.X.X and above:
select * from v$sql_shared_cursor where address = '0000000386BC2E58'
select * from v$sql_shared_cursor
where sql_id='54znbush4vgzn'
-----获取OUTLINE
select statement_id,plan_id from plan_table where rownum<2;
select * from table(dbms_xplan.display(null,null,'advanced -bytes','plan_id = 5417'));
select * from table(dbms_xplan.display(null,null,'advanced','plan_id = 5417'));
----从AWR与内存中取详细HINT
select * from table(dbms_xplan.display_awr
('9v3u2vxx3a8cr','1814554837','203185647','ADVANCED'));
dbms_xplan.display_cursor();
-----数据受限模式
---以受限模式启动库
startup restrict;
---开启受限模式
alter system enable restricted session;
---关闭受限模式
alter system disable restricted session;
----查看内存中执行计划
select * from table(dbms_xplan.display_cursor);
----分布式事物异常处理
时间点很吻合，rollback吧。
状态为prepared的，执行如下操作rollback分布事务，胡璨实施一下吧。谢谢。
rollback force 'local_tran_id';
execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');
----查询表的主外键关系
select (select a.table_name
from dba_constraints a
where a.constraint_name = c.r_constraint_name
) 主表表名,
c.r_constraint_name 主表主键键约束名,
c.table_name 从表表名,
c.constraint_name 从表外键约束名,c.*
from dba_constraints c
where c.constraint_type = 'R'
and c.table_name in ('CEIS_SYNC_CLIENT_PROP_TBL','CEIS_CLIENT_PROPERTY_INFO','CEIS_BIEB_POL_INFO1','CEIS_BIEB_POL_INFO2','CEIS_BIEB_POL_INFO3');
-----高水位情况
SELECT TABLE_NAME,(BLOCKS*8192/1024/1024)"理论大小M",
(NUM_ROWS*AVG_ROW_LEN/1024/1024)"实际大小M",
round((NUM_ROWS*AVG_ROW_LEN/1024/1024)/(BLOCKS*8192/1024/1024),3)*100||'%' "实际使用率%"
FROM DBA_TABLES where table_name='DEV_SAL_INFO_TMP';
-----
select * from dba_hist_snapshot t where t.begin_interval_time> date'2015-5-29' order by snap_id desc
select * from table(dbms_workload_repository.awr_report_html('203185647',1,147024,147026));
-----生成ASH
select * from
table(dbms_workload_repository.ash_report_html('151886107',1,to_date('2015-6-1 17:40:00','yyyy-mm-dd hh24:mi:ss'),to_date('2015-6-1 17:50:00','yyyy-mm-dd hh24:mi:ss')))
----对一个分区表收集统计信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ROBINSON',
tabname => 'P_TEST',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'for all columns size repeat',
degree => DBMS_STATS.AUTO_DEGREE,
granularity => 'ALL',
cascade=>TRUE
);
END;
/
上面的例子收集ROBINSON.P_TEST表的统计信息。里面值得注意的一个参数就是granularity，他有7个选项。
granularity => 'ALL' 收集分区，子分区，全局的统计信息
granularity => 'AUTO' 这个是默认的设置，ORACLE会根据分区类型来决定用ALL,GLOBAL AND PARTITION ,还是其他的
granularity => 'DEFAULT' 这个是过期了的
granularity => 'GLOBAL' 收集全局统计信息
granularity => 'GLOBAL AND PARTITION' 收集全局，分区统计信息，但是不收集子分区统计信息
granularity => 'PARTITION' 收集分区统计信息
granularity => 'SUBPARTITION' 收集子分区统计信息
当然我们可以指定partname，自己控制对哪个分区收集统计信息
-----连接数
select a.snap_id,to_char(b.begin_interval_time,'yyyy-mm-dd hh24:mi:ss'),to_char(b.end_interval_time,'yyyy-mm-dd hh24:mi:ss'),a.current_utilization,a.max_utilization,a.limit_value
from dba_hist_resource_limit a,dba_hist_snapshot b
where resource_name='processes' and a.snap_id=b.snap_id
order by a.snap_id desc;
----通过TRUNCATE清理大表的表分区
alter table icsdata.ICS_BENEFIT_DEMO_DETAIL truncate partition SYS_P12013 update global indexes parallel 4;
alter table icsdata.ICS_BENEFIT_DEMO_DETAIL truncate partition SYS_P13093 update global indexes parallel 4;
alter table icsdata.ICS_BENEFIT_DEMO_DETAIL truncate partition SYS_P14173 update global indexes parallel 4;
--确认无失效对象产生
select * from dba_objects where status ='INVALID' ;
select * from dba_indexes where status='UNUSABLE';
select * from dba_ind_partitions where status='UNUSABLE';
select * from dba_tables where status='UNUSABLE';
select * from dba_constraints where status='DISABLE';
----快照排序
select snap_id,dbid,to_char(end_interval_time,'yyyy-mm-dd hh24:mi:ss') from dba_hist_snapshot
order by end_interval_time desc;
------PLSQL取AWR报告
select * from table(dbms_workload_repository.awr_report_html('203185647',1,175129,175130));
----新用户创建脚本
conn sys/&password@&sid as sysdba
-- Create the user
create user lifeemon
identified by paic1234
default tablespace users
temporary tablespace TEMP
profile DEFAULT
quota unlimited on users;
----赋予新用户权限的拼接语句
select 'grant '||privilege||' to LIHONGLIANG001;' from dba_sys_privs
where grantee='ZHANGYU090'
union all
select 'grant '||granted_role||' to LIHONGLIANG001;' from dba_role_privs
where grantee='ZHANGYU090'
union all
select 'grant '||privilege||' on '||owner||'.'||table_name||' to LIHONGLIANG001;' from dba_tab_privs
where grantee='ZHANGYU090';
结果类似如下：
---grant privilege to lifeemon
grant CREATE DATABASE LINK to lifeemon;
grant SELECT ANY TABLE to lifeemon;
grant CREATE TABLE to lifeemon;
grant CREATE SESSION to lifeemon;
grant SELECT_CATALOG_ROLE to lifemon;
grant R_APPMGR_QRY to lifemon;
grant DEBUG on SYS.DBMS_WORKLOAD_REPOSITORY to lifemon;
grant EXECUTE on SYS.DBMS_WORKLOAD_REPOSITORY to lifemon;
grant SELECT on DBMGR.FACTUSER_LIST to lifemon;
grant EXECUTE on PDFDATA.PDF_ICS_FUNC_PKG to lifemon;
grant EXECUTE on DBMGR.PRC_KILL_SESSION to lifemon;
------ASH中EVENT情况
select event,sql_id,count(*) from dba_hist_active_sess_history
where snap_id between 3882 and 3883
and sample_time between to_date('2016-04-15 12:02:00','yyyy-mm-dd hh24:mi:ss') and to_date('2016-04-15 12:07:00','yyyy-mm-dd hh24:mi:ss')
group by event,sql_id
order by 3 desc;
----SGA与PGA使用率
select name,total,round(total-free,2) used, round(free,2) free,round((total-free)/total*100,2) pctused from
(select 'SGA' name,(select sum(value/1024/1024) from v$sga) total,
(select sum(bytes/1024/1024) from v$sgastat where name='free memory')free from dual)
union
select name,total,round(used,2)used,round(total-used,2)free,round(used/total*100,2)pctused from (
select 'PGA' name,(select value/1024/1024 total from v$pgastat where name='aggregate PGA target parameter')total,
(select value/1024/1024 used from v$pgastat where name='total PGA allocated')used from dual);
----PGA使用情况
select sum(PGA_ALLOC_MEM)/1024/1024/1024 from v$process;
----最大PGA使用量
select sum(pga_max_mem)/1024/1024/1024 from v$process；
----查看ORACLE进程情况；
ps -ef|grep $ORACLE_SID|grep -v grep| grep LOCAL=NO| awk '{print $2}'
---会话汇总情况：
set linesize 300
select inst_id,count(1) from gv$session gs
WHERE gs.status='ACTIVE' AND gs.TYPE<>'BACKGROUND'
group by inst_id
ORDER BY gs.inst_id;
---会话详情：
SELECT gs.INST_ID,gs.saddr,gs.SID,gs.SERIAL#,gs.EVENT,gsql.SQL_ID,gsql.SQL_FULLTEXT,gs.USERNAME,gs.OSUSER, gs.PROCESS,gs.MACHINE,gs.PROGRAM FROM gv$session gs,gv$sql gsql
WHERE gs.status='ACTIVE' AND gs.TYPE<>'BACKGROUND'and gsql.SQL_ID=gs.SQL_ID and gsql.INST_ID=gs.INST_ID
ORDER BY gs.inst_id;
----会话等待情况
set linesize 300
select inst_id,decode(event,null,'SUM',event),count(1) from gv$session gs
WHERE gs.status='ACTIVE' AND gs.TYPE<>'BACKGROUND'
group by rollup(inst_id,event)
ORDER BY 1,3 desc;
---查询表中数据存放的数据块情况
set linesize 200
col name format a40
select a.file_id,a.block_id,a.blocks,b.name
from dba_extents a,v$datafile b
where a.file_id=b.file# and a.owner='&Schema' and a.segment_name='&TableName';
----根据数据文件与块号查找对象
Select tablespace_name,segment_type,owner,segment_name From dba_extents Where file_id=&file_id and &block_number between block_id and block_id+blocks-1;
----DBV验证段
dbv userid=system/oracle segment_id=4.4.522
dbv userid=system/oracle segment_id=tsn.segfile.segblock
tsn--表示表空间id
segfile--表示段头所在数据文件号
segblock--表示段头数据块号
这三个值可以通过数据字典sys_dba_segs获取，相关的列分别是TABLESPACE_ID、 HEADER_FILE和HEADER_BLOCK
select tablespace_id,header_file,header_block
from sys.sys_dba_segs
where owner='&schema' and segment_name='&segment';
TABLESPACE_ID HEADER_FILE HEADER_BLOCK
------------- ----------- ------------
4 4 522
----查出表中记录所在的块
select distinct dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from &schema&Table;
----查询数据块存放的数据类型情况
set linesize 200
col segment_name format a30
select segment_name,tablespace_name,segment_type,block_id,file_id,bytes from dba_extents where block_id=&block_id and file_id=&file_id;
----快照/报告
exec dbms_workload_repository.create_snapshot;
select max(snap_id) from dba_hist_snapshot;
@?/rdbms/admin/awrrpt.sql
----清理数据库AWR报告信息：
begin
dbms_workload_repository.drop_snapshot_range(low_snap_id =>4274 ,high_snap_id =>4275 ,dbid => 49371800);
end;
----获取当前最繁忙BUFFER(TOP 10)
select * from
(select addr,ts#,file#,dbarfil,dbablk,tch from x$bh
order by tch desc)
where rownum<11;
---获取当前热点BUFFER来自对象情况（TOP10）
select e.owner,e.segment_name,e.segment_type
from dba_extents e,
(select * from
(select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc)
where rownum<11
)b
where e.relative_fno=b.dbarfil
and e.block_id<=b.dbablk
and e.block_id+e.blocks>b.dbablk;
----获取SHARED POOL中CHUNK信息；
select * from x$ksmsp;
------查询在sort排序区使用的执行耗时的SQL：
Select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value))as Space,tablespace,segtype,sql_text
from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr order by se.username,se.sid;
-----或是：
Select su.username,su.Extents,tablespace,segtype,sql_text
from v$sort_usage su,v$sql s
Where su.SQL_ID = s.SQL_ID;
----查找约束对象
set linesize 200
col owner format a20
col CONSTRAINT_NAME format a30
col table_name format a20
col column_name format a20
select au.owner,au.table_name,au.CONSTRAINT_NAME,cu.column_name from dba_cons_columns cu, dba_constraints au where cu.constraint_name=au.constraint_name and
cu.table_name='&table_name';
----RMAN备份情况
set linesize 200
col INPUT_BYTES_DISPLAY format a20
col OUTPUT_BYTES_DISPLAY format a20
col TIME_TAKEN_DISPLAY format a20
SELECT R.START_TIME,
R.END_TIME,
R.INPUT_BYTES_DISPLAY,
R.OUTPUT_BYTES_DISPLAY,
R.STATUS,
R.INPUT_TYPE,
R.TIME_TAKEN_DISPLAY
FROM V$RMAN_BACKUP_JOB_DETAILS R
WHERE R.START_TIME > SYSDATE - 1
ORDER BY R.START_TIME desc;
---数据库的版本和平台和相关信息
select * from v$version;
select * from dba_registry_database;
select dbid, name, open_mode, database_role, platform_name from v$instance;
select dbms_utility.port_string from dual;
-----是否配置了DataGuard
select protection_mode, protection_level, remote_archive, database_role, dataguard_broker,guard_status
from v$database;
---日志文件的组成 （单实例、RAC通用）
select l.group#,l.THREAD#, lf.type,lf.member, l.bytes/1024/1024, l.status LOG_STATUS, lf.status LOGFILE_STATUS
from v$log l, v$logfile lf
where l.group# = lf.group#
order by 1,3;
---干掉DLL的锁会话
Select s.Sid,
'ALTER SYSTEM KILL SESSION ''' || s.Sid || ',' || s.Serial# || ''';' Killsql,
s.Serial#,
p.Spid,
Owner,
Name,
Mode_Held,
Mode_Requested,
Machine,
s.Module,
s.Status,
s.Action
From Sys.Dba_Ddl_Locks a, V$process p, V$session s
Where s.Paddr = p.Addr
And a.Session_Id = s.Sid
And Name = '&pkg_name'
Order By s.Sid;
---创建DBLINK
CREATE public database link pub_xacsdbser CONNECT TO enn_idp IDENTIFIED BY enn_idp
using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.37.148.238)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME= csdbser)))';
---查看数据库中OPEN用户权限分配
select grantee,privilege from dba_sys_privs where grantee in (
SELECT username FROM DBA_USERS
WHERE ACCOUNT_STATUS='OPEN'
and default_tablespace not in ('SYSTEM') )
union
select a.grantee,b.privilege from (
select * from dba_role_privs where grantee in (
SELECT username FROM DBA_USERS
WHERE ACCOUNT_STATUS='OPEN'
and default_tablespace not in ('SYSTEM'))) a
left join dba_sys_privs b on a.granted_role=b.GRANTEE
order by 1;
-----查看某个用户的权限
select PRIVILEGE from dba_sys_privs
where grantee in
(select granted_role from dba_role_privs
where grantee='DBMONOPR')
UNION
SELECT PRIVILEGE FROM DBA_SYS_PRIVS
WHERE GRANTEE='DBMONOPR';
----To see the (current) undo tablespace percent of space in use
SELECT ((SELECT (NVL(SUM(bytes), 0))
FROM dba_undo_extents
WHERE tablespace_name = 'UNDOTBS1'
AND status IN ('ACTIVE', 'UNEXPIRED')) * 100) /
(SELECT SUM(bytes)
FROM dba_data_files
WHERE tablespace_name = 'UNDOTBS1') PCT_INUSE
FROM dual;
---表空间使用情况
SELECT A.TABLESPACE_NAME "表空间名",
A.TOTAL_SPACE "总空间(G)",
A.TOTAL_SPACE-B.USED_SPACE "剩余空间(G)",
B.USED_SPACE "已使用(G)",
B.USED_SPACE/A.TOTAL_SPACE "已使用百分比%",
AA.AUTOEXTENSIBLE,
AA.STATUS
FROM (select tablespace_name,
trunc(sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)/1024/1024/1024),2) total_space from dba_data_files
group by tablespace_name
) A,
(select TABLESPACE_NAME,TRUNC(sum(bytes)/1024/1024/1024,2) USED_SPACE from dba_extents
GROUP BY TABLESPACE_NAME) B,
(SELECT TABLESPACE_NAME,
MAX(AUTOEXTENSIBLE) AUTOEXTENSIBLE,
SUM(BYTES) / 1024 / 1024 /1024 "SIZE(G)",
STATUS
FROM DBA_DATA_FILES A
GROUP BY TABLESPACE_NAME, STATUS) AA
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME and a.tablespace_name=aa.tablespace_name
ORDER BY 5;
---表空间使用情况
select tt.tablespace_name,
tt.total_size,
tt.used_size,
tt.USED_RATE AS "USED%",
tt.free_size,
AA.AUTOEXTENSIBLE,
AA.STATUS
from (select a.tablespace_name,
a.total_size,
a.header_size + nvl(b.used_size, 0) used_size,
round((a.header_size + nvl(b.used_size, 0)) / a.total_size * 100,
2) USED_RATE,
a.total_size - (a.header_size + nvl(b.used_size, 0)) free_size
from (select tablespace_name,
round(sum(bytes) / 1024 / 1024 /1024, 2) total_size,
round(sum(64) / 1024/1024, 2) header_size
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,
round(sum(bytes) / 1024 / 1024 /1024, 2) used_size
from dba_extents
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)) tt,
(SELECT TABLESPACE_NAME,
MAX(AUTOEXTENSIBLE) AUTOEXTENSIBLE,
SUM(BYTES) / 1024 / 1024 /1024 "SIZE(G)",
STATUS
FROM DBA_DATA_FILES A
GROUP BY TABLESPACE_NAME, STATUS) AA
WHERE TT.TABLESPACE_NAME = AA.TABLESPACE_NAME
ORDER BY tt.USED_RATE;
----临时段使用情况1
SELECT se.username, se.SID, se.serial#, se.sql_address, se.machine, se.program, su.TABLESPACE,su.segtype, su.CONTENTS from
v$session se, v$sort_usage su WHERE se.saddr = su.session_addr
-----查看临时表空间每个数据文件实际使用量
Select f.tablespace_name,
d.file_name "Tempfile_name",
round((f.bytes_free + f.bytes_used) / 1024 /1024, 2) "total MB",
round(((f.bytes_free + f.bytes_used) -nvl(p.bytes_used, 0)) / 1024 / 1024, 2) "Free MB" ,
round(nvl(p.bytes_used, 0)/ 1024 / 1024, 2)"Used MB",
round((round(nvl(p.bytes_used, 0)/ 1024 /1024, 2)/round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2))*100,2) as"Used_Rate(%)",
d.autoextensible
from SYS.V_$TEMP_SPACE_HEADER f,DBA_TEMP_FILES d, SYS.V_$TEMP_EXTENT_POOL p
where f.tablespace_name(+) = d.tablespace_name
and f.file_id(+) = d.file_id
and p.file_id(+) =d.file_id;
----查询在sort排序区使用的执行耗时的SQL
Select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value))as Space,tablespace,segtype,s.SQL_FULLTEXT,se.sql_id
from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr
order by space desc
----回滚段事务情况
select a.name,b.xacts,c.sid,c.serial#,c.EVENT,e.START_TIME,c.username,d.sql_text,d.sql_id,b.WAITS
from v$rollname a,gv$rollstat b, gv$session c,gv$sqltext d, gv$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;
2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
select * from dba_sys_privs;
select * from user_sys_privs;
3.查看角色(只能查看登陆用户拥有的角色)所包含的权限
select * from role_sys_privs;
4.查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
5.查看所有角色：
select * from dba_roles;
6.查看用户或角色所拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;
7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from V$PWFILE_USERS
注意：
1、以下语句可以查看Oracle提供的系统权限
select name from sys.system_privilege_map
2、查看一个用户的所有系统权限(包含角色的系统权限)
Sql代码
select privilege from dba_sys_privs where grantee='DATAUSER'
union
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='DATAUSER' );
查询表空间名称和大小
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;
查看数据库引起锁表的SQL语句：
SELECT a.username,
a.machine,
a.program,
a.sid,
a.serial#,
a.status,
c.piece,
c.sql_text
FROM v$session a,
v$sqltext c
WHERE a.sid in
(
select distinct t2.sid
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid
)
and a.sql_address=c.address(+)
ORDER BY c.piece
查看数据库锁的情况必须要有DBA权限，可以使用一下SQL 语句：
select object_id,session_id,locked_mode from v$locked_object;
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;
--查看被锁的表
select p.spid,
a.SID,
a.serial#,
a.EVENT,
a.BLOCKING_INSTANCE,
a.BLOCKING_SESSION,
a.BLOCKING_SESSION_STATUS,
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
and c.object_id = b.object_id;
杀掉进程
alter system kill session 'sid,serial#';
查询所有用户表使用大小的前三十名
select * from (select segment_name,bytes from dba_segments where owner = USER order by bytes desc ) where rownum <= 30
查询表空间有那些表:
select table_name from all_tables where tablespace_name= 'TEMP';
查询最近使用过的SQL：
select * from v$sqlarea
查询锁阻塞 SELECT (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
---获取TRACE文件
select d.value||'/'||LOWER(RTRIM(i.INSTANCE,CHR(0)))||'_ora_'||p.spid||'.trc' trace_file_name
from (select p.spid from sys.v$mystat m,sys.v$session s,sys.v$process p
where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,
(select t.instance from sys.v$thread t,sys.v$parameter v
where v.name='thread' and (v.value=0 or t.thread#=to_number(v.value)))i,
(select value from sys.v$parameter where name='user_dump_dest')d;
---或者
select tracefile from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where statistic#=1));
---转储library_cache内容
SQL>ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME LIBRARY_CACHE LEVEL 4';
---查看所有隐藏参数：
SELECT i.ksppinm name,
i.ksppdesc description,
CV.ksppstvl VALUE,
CV.ksppstdf isdefault,
DECODE (BITAND (CV.ksppstvf, 7),
1, 'MODIFIED',
4, 'SYSTEM_MOD',
'FALSE')
ismodified,
DECODE (BITAND (CV.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadjusted
FROM sys.x$ksppi i, sys.x$ksppcv CV
WHERE i.inst_id = USERENV ('Instance')
AND CV.inst_id = USERENV ('Instance')
AND i.indx = CV.indx
AND i.ksppinm LIKE '/_%' ESCAPE '/'
ORDER BY REPLACE (i.ksppinm, '_', '');
[sql] view plaincopy
SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND TRANSLATE (ksppinm, '_', '#') LIKE '#%';
----查看_db_block_hash_buckets的参数值
[sql] view plaincopy
SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND ksppinm = '_db_block_hash_buckets';
--查看大表
SELECT ds.segment_name, (SUM(ds.bytes) / 1024) / 1024 / 1024 G_BYTES
FROM dba_segments ds
WHERE ds.owner = 'MSC'
AND ds.segment_type LIKE 'TABLE%'
GROUP BY ds.segment_name, ds.segment_type
HAVING(SUM(ds.bytes) / 1024) / 1024 / 1024 > 0.0001
ORDER BY 2 DESC;
----
----查看统计信息中列的VALUE
declare
out_date date;
begin
dbms_stats.convert_raw_value(rawval => '786D071E010101',resval => out_date);
dbms_output.put_line(to_char(out_date,'yyyy-mm-dd'));
end;
ics:
alter system set log_archive_dest_3 ='SERVICE=lics arch async noaffirm reopen=60 valid_for=(online_logfiles,primary_role) db_unique_name=lics' scope =both;
----增加收集统计信息JOB
DECLARE
v_job BINARY_INTEGER;
BEGIN
dbms_job.submit(job => v_job,
what => 'dbmgr.gather_cbo_stats_auto.process_wait_objects;',
next_date => SYSDATE+1,
INTERVAL => 'dbmgr.gather_cbo_stats_auto.gather_job_interval');
INSERT INTO stats_gather_jobs
(job, job_type)
VALUES
(v_job, 'GATHER');
COMMIT;
END;
----ORA 08104
select i.obj#, i.flags, u.name, o.name, o.type#
from sys.obj$ o, sys.user$ u, sys.ind$ idx, sys.ind_online$ i
where bitand(i.flags, 512) = 512 and o.obj#=idx.obj# and
o.owner# = u.user# and idx.obj#=i.obj#;
使用dbms_repair.online_index_clean清理；
DECLARE
RetVal BOOLEAN;
OBJECT_ID BINARY_INTEGER;
WAIT_FOR_LOCK BINARY_INTEGER;
BEGIN
OBJECT_ID := 3143711;
-- 我的数据库中非法索引的id为3143711;
WAIT_FOR_LOCK := NULL;
RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN();
COMMIT;
END;
/
-----MIS COW库起库脚本
/etc/paic/cow/start_cow2open.sh
是否启用block change tracking，oracle建议在使用增量备份策略时启用此功能，可以加速增量备份。
配置方法可以如下：
启用Eg：SQL> alter database enable block change tracking using file '/fratest/fra/LPMS/rman_block_change_track.log' ;
禁用 Eg：SQL> alter database disable block change tracking;
----PA AGENT
PA agent的启动，停止脚本：
只执行启动脚本：
Cd $HOME/quest/pa_agent/agents/bin
启动： ./quest_launcher_daemon start
停止： ./quest_launcher_daemon stop
-----SYSTEMSTATE,HANG分析
19:16:20 sys@t5ics>
19:16:21 sys@t5ics>oradebug setmypid;
Statement processed.
19:16:41 sys@t5ics>oradebug dump systemstate 266;
Statement processed.
19:16:58 sys@t5ics>oradebug tracefile_name
/paic/t5ics/rdbms/oracle/diag/rdbms/t5ics/t5ics/trace/t5ics_ora_5587.trc
----按照用户角度去统计CPU消耗情况
select u.username,
round(sum(cpu_time_delta) / 1000000, 0) CPU_TOTAL,
sum(cpu_time_delta)/
(select sum(cpu_time_delta) from dba_hist_sqlstat s, dba_hist_snapshot t
where s.snap_id = t.snap_id
and ((t.begin_interval_time between
to_date('2017-03-27', 'yyyy-mm-dd') and
to_date('2017-04-27', 'yyyy-mm-dd'))))
*100 PCT
from dba_hist_sqlstat s, dba_hist_snapshot t ,dba_users u
where s.snap_id = t.snap_id
and s.parsing_user_id=u.user_id
and ((t.begin_interval_time between
to_date('2017-03-27', 'yyyy-mm-dd') and
to_date('2017-04-27', 'yyyy-mm-dd')))
group by u.username
order by 2 desc;
----确认历史备份
select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name='CHS_CHANNEL_CLAIM_INFO'
order by stats_update_time desc;
----根据时间点做恢复
begin
dbms_stats.restore_table_stats(ownname => 'LIFEDATA',tabname => 'CHS_CHANNEL_CLAIM_INFO',as_of_timestamp => to_timestamp('2017-04-25 00:06:58', 'yyyy-MM-dd HH24:MI:ss'));
end;
/
这个应该就是使用ORACLE自己的备份去做恢复。
时间点指向当天第一次的备份，我觉得就OK了。
---提前分配表的DATA BLOCK
alter table chad_insert_test allocate extent (SIZE 1G);
---回收表的空间数据块
alter table chad_insert_test DEALLOCATE UNUSED KEEP 200M;
（保留200M的空间）
alter table chad_insert_test DEALLOCATE UNUSED;
(回收所有未使用的块)
----回收表的高水位
alter table chad_insert_test move;
------RMAN注册CATALOG
登陆到数据库的主机、备机，
su – dbcom
cd /etc/paic/network
more tnsnames.ora|grep cat11g
----以上命令，如果没有输出，则增加cat11g数据库连接串。
CAT11G=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=cat11g.db.paic.com.cn)
(PORT=1534)
)
(CONNECT_DATA=
(SERVICE_NAME=cat11g)
)
)
注意：主机、备机都需要作以上操作，否则db切换主机后，rman备份会无法进行。
Rman注册：
步骤如下：
#rman target / catalog rman11g/rman11g@cat11g ---(对应的catalog数据库)
RMAN> register database;
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 90 DAYS;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';
RMAN> report schema;
---批量KILL LOCAL=NO进程
ps -ef|grep -v grep|grep LOCAL=NO |grep ics |grep -v icss|awk '{print $2}'|xargs kill -9
-查看历史PATCH信息
col comments for a40
select * from sys.registry$history
order by action_time desc;
---segment physical reads 排序
SELECT dhso.object_name,
sum(dhss.physical_reads_delta),
sum(dhss.physical_read_requests_delta),
/* sum(db_block_changes_delta) BLOCK_CHANGED*/
round(sum(dhss.physical_reads_delta) /
(SELECT sum(dhss.physical_reads_delta)
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhso.obj#
AND dhss.dataobj# = dhso.dataobj#
AND dhs.snap_id between 146363 and 146460 ----146460 */
HAVING sum(dhss.physical_reads_delta) > 0) * 100,
2) precent
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhso.obj#
AND dhss.dataobj# = dhso.dataobj#
/* AND begin_interval_time BETWEEN to_date('2017-03-31 15:00','YY-MM-DD HH24:MI')
AND to_date('2017-03-31 23:59','YY-MM-DD HH24:MI')*/
AND dhs.snap_id between 146363 and 146460 ----146460 */
GROUP BY dhso.object_name
HAVING sum(dhss.physical_reads_delta) > 0
ORDER BY 4 desc;
-----disk_read
select sql_id,
sum(s.disk_reads_delta) "disk_reads(number of blocks)",
round(sum(s.disk_reads_delta)/1700786998*100,2) "disk_reads(%)"
from dba_hist_snapshot sn, dba_hist_sqlstat s
where s.snap_id = sn.snap_id and sn.snap_id between 146422 and 146460
and s.instance_number=sn.instance_number
and s.executions_delta>0 and disk_reads_delta>0
group by sql_id
order by 3 desc;
----executions
select sql_id,
sum(s.executions_delta) "executions",
round(sum(s.executions_delta)/2002856361 *100,2) "executions(%)"
from dba_hist_snapshot sn, dba_hist_sqlstat s
where s.snap_id = sn.snap_id and sn.snap_id between 146422 and 146460
and s.instance_number=sn.instance_number
and s.executions_delta>0
group by sql_id
order by 3 desc;
---CPU_TIME
select sql_id,
round(sum(s.cpu_time_delta)/1000000,2) "CPU_TIME",
round(sum(s.cpu_time_delta)/1000000/8162126 *100,2) "CPU_TIME(%)"
from dba_hist_snapshot sn, dba_hist_sqlstat s
where s.snap_id = sn.snap_id and sn.snap_id between 146422 and 146460
and s.instance_number=sn.instance_number
and s.executions_delta>0
group by sql_id
order by 3 desc;
------其中的总数脚本：
select sum(s.disk_reads_delta) disk_reads
from dba_hist_snapshot sn, dba_hist_sqlstat s
where s.snap_id = sn.snap_id and sn.snap_id between 146422 and 146460
and s.instance_number=sn.instance_number
and s.executions_delta>0 and disk_reads_delta>0
----统计failed pared记录趋势
with s as (
select sn.instance_number, sn.snap_id, sn.end_interval_time stime, sy.value,
(sy.value - lag(sy.value, 1) over (partition by sn.instance_number order by sn.snap_id)) parse_failed_time
from dba_hist_snapshot sn, dba_hist_sys_time_model sy
where sy.snap_id = sn.snap_id and sy.stat_name = 'failed parse elapsed time' and sy.instance_number = sn.instance_number
)
select instance_number , snap_id, to_char(stime,'YYYY/MM/DD HH24:MI:SS'), round(parse_failed_time/1000000,2)
from s
where instance_number = 1 and snap_id between 146266 and 146459
order by instance_number, snap_id
----统计事务数变化趋势
select to_char(begin_time,'yyyy/mm/dd hh24:mi:ss'),to_char(end_time,'yyyy/dd/dd hh24:mi:ss'),metric_unit,round(maxval,0),round(average,0) from dba_hist_sysmetric_summary
where metric_unit='Transactions Per Second'
and snap_id between 152250 and 152260
order by 1 ;
----统计EVENT的趋势变化
with t as (
select s.snap_id, s.instance_number, s.end_interval_time,event_name,
total_waits - lag(total_waits, 1) over (partition by s.instance_number,event_name order by s.snap_id) waits,
(time_waited_micro - lag(time_waited_micro, 1) over (partition by s.instance_number,event_name order by s.snap_id))/1000 twt
from dba_hist_system_event ev, dba_hist_snapshot s
where ev.instance_number = s.instance_number and ev.snap_id = s.snap_id
and event_name in ('db file sequential read','log file parallel write') ----,'log file parallel write','db file sequential read' 'log file sync'
and s.end_interval_time BETWEEN TO_DATE('20170427 15:00:00', 'yyyymmdd hh24:mi:ss') AND TO_DATE('20170427 23:59:59', 'yyyymmdd hh24:mi:ss') )
select to_char(end_interval_time,'YYYYMMDD HH24:MI'), instance_number,event_name, sum(waits), sum(twt), round(sum(twt)/sum(waits), 2) wt
from t
where (to_char(end_interval_time, 'hh24') between 0 and 12 or to_char(end_interval_time, 'hh24') between 13 and 23)
group by to_char(end_interval_time,'YYYYMMDD HH24:MI'), instance_number,event_name
order by 1, event_name;
----prelim
z4cs2011:t4lucd0 > sqlplus -prelim '/ as sysdba';
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Mar 27 16:23:59 2017
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
SQL>
SQL>
SQL> select status from v$instance;
select status from v$instance
*
ERROR at line 1:
ORA-01012: not logged on
SQL> conn / as sysdba
Prelim connection established
----格式化SQLPLAN
Select Sp.Id,
Lpad(' ', Sp.Depth * 3, ' ') || Sp.Operation || ' ' || Sp.Options Operation,
Sp.Object_Name,cardinality,access_predicates,filter_predicates
From dba_hist_sql_plan Sp
Where sql_id='0zkq1vju66sxx' --And plan_hash_value =1155695790
Order By Id;
-----Strace常用
strace -c -p 40292
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
85.55 0.010406 4 2962 241 read
9.65 0.001174 5 241 poll
2.32 0.000282 0 12320 semop
1.92 0.000234 1 282 sendto
0.56 0.000068 0 3114 lseek
0.00 0.000000 0 20 write
0.00 0.000000 0 12 open
0.00 0.000000 0 13 close
0.00 0.000000 0 5 select
0.00 0.000000 0 518 241 recvfrom
0.00 0.000000 0 1 kill
0.00 0.000000 0 1 fdatasync
strace -o server.strace -Ttt -p 40292 （进程40292按照时间排序输出到server.strace）
-----清理SHARED_POOL中某SQL的CURSOR
select sql_id,address,hash_value from v$sqlarea
where sql_id='a3wtrs8cvx687';
SQL> alter session set events '5614566 trace name context forever';
Session altered.
SQL> exec sys.dbms_shared_pool.purge('00000003FF82C0A8,431921415','C');
PL/SQL procedure successfully completed.
SQL> alter session set events '5614566 trace name context off';
Session altered
----分析监听日志
[padba@cnsz081003 chad]$
./netlog_pass.pl -h -t '27-FEB-2017 17:[4-5][0-9]' elis.log | sort -n -k 3 -r
./netlog_pass.pl -h -t '28-FEB-2017 10:[0-5][0-59]' chad | sort -n -k 3 -r
./netlog_pass.pl -h -t '20-FEB-2017 08:[4-5][0-9]' log_1799.xml | sort -n -k 3 -r
./netlog_pass.pl -h -t '05-APR-2017 10:[0-1][0-9]' luqd0.log | sort -n -k 3 -r
./lsnr.sh -h -t "30-JUN-2017 11:[0-2][0-9]:[0-2][0-9]" lugz0.log
./lsnr.sh -h -t "30-JUN-2017 11:[0-1][0-9]:[0-5][0-9]" lugz0.log
./lsnr.sh -t "30-JUN-2017 11:[0-2][0-9]:[0-5][0-9]" lugz0.log
---查看审计配置
select * from DBA_OBJ_AUDIT_OPTS; ----对象的审计
select * from DBA_PRIV_AUDIT_OPTS; ----权限的审计
---根据字符集查找（123或abc）
egrep '123|abc' filename // 用egrep同样可以实现
---根据SID查找child_children的ADDR
select ADDR from v$latch_children
where name='cache buffers chains'
and addr in (select a.P1RAW from v$session a
where sid='&sid' );
-----根据V$LATCH_CHILDREN的ADDR列来查找LATCH涉及的对象
SELECT /*+ RULE */
e.owner || '.' || e.segment_name segment_name,
e.extent_id extent#,
x.dbablk - e.block_id + 1 block#,
x.tch,
l.child#
FROM sys.v$latch_children l,
sys.x$bh x,
sys.dba_extents e
WHERE x.hladdr in ('0000000F3730E6C8','0000000F3D97D720','0000000F40A90180')
AND e.file_id = x.file#
AND x.hladdr = l.addr
AND x.dbablk BETWEEN e.block_id AND e.block_id + e.blocks -1
ORDER BY x.tch DESC;
----找对象
select /*+rule*/
x.hladdr, x.obj, o.object_name, x.dbarfil, x.dbablk, x.tch
from x$bh x, dba_objects o
where x.hladdr in
(select distinct p1raw
from v$session
where event = 'latch: cache buffers chains')
and o.data_object_id = x.obj
and rownum<11
order by x.tch desc;
---根据HLADDR来查
select p1raw,count(*) from v$session
where event='latch: cache buffers chains'
group by p1raw
order by 2 desc;
select /*+rule*/ x.hladdr,x.obj,o.object_name,x.dbarfil,x.dbablk,x.tch
from x$bh x,dba_objects o
where x.hladdr ='0000000F44308DC8' and o.data_object_id=x.obj
order by x.tch desc;
---- linux删除过期文件
find . -name "*.trc" -mtime +30 -exec rm -rf {} \;
----获取行锁的ROWID
select Dbms_Rowid.Rowid_Create(1, Data_Object_Id, Lo_Fno, Lo_Block, 0) from dual;
(其中的OBJECT_ID,FILE_NO,BLOCK_NO可以从V$SESSION或者ASH中获取)
----调整10G的JOB
begin
sys.dbms_ijob.broken( 6795,FALSE);
end;
begin
sys.dbms_ijob.next_date(job => 6795,next_date => to_date('2016-12-06 17:00:00','YYYY-MM-DD HH24:MI:SS'));
END;
查看CG CPU情况：
/opt/cgtools/cginfo -t perf -s cpu
-----批量KILL PG库的会话
select pg_terminate_backend(pid) from pg_stat_activity
where usename='rasopr' and query like '%select MAX(end_time) from rais_sync_record%';
调整PROFILE的LIMIT：
--- ISW库查询TABLESPACE情况
select db_name,
snap_date,
sum(ts_size) / 1024 / 1024 / 1024 total,
sum(free_bytes) / 1024 / 1024 / 1024 free,
(sum(ts_size) - sum(free_bytes)) / 1024 / 1024 / 1024 used
from dbqc$ts_info
where db_name in ('PACRPT', 'EPODS', 'EPCIS')
and snap_date > date
'2010-1-1'
and (to_char(snap_date, 'mmdd') = '0101' or
to_char(snap_date, 'yyyymmdd') = '20161010')
and to_char(snap_date, 'hh24') between '01' and '06'
group by db_name, snap_date
-----查询ORACLE被LOCKED的对象
select object_name, s.sid, s.serial#, p.spid
from v$locked_object l, dba_objects o, v$session s, v$process p
where l.object_id = o.object_id and l.session_id = s.sid and s.paddr = p.addr;
-----DG应用状态
SELECT pid, PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY where process='RFS' or process='MRP0';
----调整PROFILE的LIMIT
alter profile PRF_LIFEJ2EE limit SESSIONS_PER_USER 1800;
----统计归档日志
select to_char(completion_time,'yyyy-mm-dd hh24'),count(*) from v$archived_log
where name not in ('llass','rlass')
and completion_time+3 > sysdate
group by to_char(completion_time,'yyyy-mm-dd hh24')
order by 1 desc;
----
物理CPU个数：
cat /proc/cpuinfo | grep "physical id" | sort | uniq
每个物理CPU的逻辑核数：
cat /proc/cpuinfo | grep "cores" | uniq
系统整个cpu线程数：
cat /proc/cpuinfo | grep "processor" | wc -l
CPU型号：
cat /proc/cpuinfo | grep "model name" | uniq
MYSQL导库的表结构（不包括数据）：
mysqldump -d xbrl > /tmp/xrbl.sql
MYSQL导表（导出xbrl库中 2个表psx_dimension_group psx_dimensions）：
mysqldump xbrl psx_dimension_group psx_dimensions > /tmp/dump_xbrl_tables.sql
查看隐含参数：
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from x$ksppi a, x$ksppcv b
where a.indx = b.indx and a.ksppinm like '%&name%';
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from x$ksppi a, x$ksppcv b
where a.indx = b.indx and a.ksppdesc like '%shared mutex wait time%';
kks shared mutex wait time – cs
---计算LOB表的大小
SELECT
(SELECT NVL(SUM(S.BYTES),0) -- The Table Segment size
FROM DBA_SEGMENTS S
WHERE S.OWNER = UPPER('ICSDATA') AND
(S.SEGMENT_NAME = UPPER('ICS_BENEFIT_DEMO_DETAIL'))) +
(SELECT NVL(SUM(S.BYTES),0) -- The Lob Segment Size
FROM DBA_SEGMENTS S, DBA_LOBS L
WHERE S.OWNER = UPPER('ICSDATA') AND
(L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('ICS_BENEFIT_DEMO_DETAIL') AND L.OWNER = UPPER('ICSDATA'))) +
(SELECT NVL(SUM(S.BYTES),0) -- The Lob Index size
FROM DBA_SEGMENTS S, DBA_INDEXES I
WHERE S.OWNER = UPPER('ICSDATA') AND
(I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('ICS_BENEFIT_DEMO_DETAIL') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('ICSDATA')))
"TOTAL TABLE SIZE(BYTES)"
FROM DUAL;
查看占用cpu最高的进程
ps aux|head -1;ps aux|grep -v PID|sort -rn -k +3|head
ps -eo pid,pcpu,args | sort +1n
或者top （然后按下M，注意这里是大写）
查看占用内存最高的进程
ps aux|head -1;ps aux|grep -v PID|sort -rn -k +4|head
---字符集相关
select userenv('language') from dual; ----数据库字符集
select lengthb('啊') from dual; ---一个汉字占用字节
----COW库起库脚本
查看/etc/paic/cow/start_cow2open.sh 里的如下部分可以知道部署COW起库用的参数文件为：/etc/paic/network/dba/init${ORACLE_SID}.ora
if [ -f /etc/paic/network/dba/init${ORACLE_SID}.ora ]
then
echo "pfile exist,begin to create spfile from special pfile"
sqlplus '/as sysdba'<<!
create spfile from pfile='/etc/paic/network/dba/init${ORACLE_SID}.ora';
exit;
!
------DB BLOCK CHANGE情况
SELECT to_char(begin_interval_time,'YY-MM-DD HH24') snap_time,
dhso.object_name,
sum(db_block_changes_delta) BLOCK_CHANGED
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhso.obj#
AND dhss.dataobj# = dhso.dataobj#
AND begin_interval_time BETWEEN to_date('2016-09-27 13:00','YY-MM-DD HH24:MI')
AND to_date('2016-09-27 14:00','YY-MM-DD HH24:MI')
GROUP BY to_char(begin_interval_time,'YY-MM-DD HH24'),
dhso.object_name
HAVING sum(db_block_changes_delta) > 0
ORDER BY sum(db_block_changes_delta) desc ;
----查询被偷取的buffer cache情况
with cur as
(select d.CURRENT_SIZE/ 1024 / 1024 CURRENT_SIZE
from v$sga_dynamic_components d
where d.COMPONENT='DEFAULT buffer cache'),
spec as
(select name,to_number(value)/ 1024 / 1024 db_cache_size
from V$SPPARAMETER
where name='db_cache_size')
select spec.name,
cur.CURRENT_SIZE "CURRENT_SIZE(M)",
spec.db_cache_size "PARAMETER_VALUE(M)",
'FF00FF' "#stolen_mem#",
(spec.db_cache_size-cur.CURRENT_SIZE)||'M' stolen_mem
from cur, spec
where (spec.db_cache_size-cur.CURRENT_SIZE)-2.5*1024>0
or spec.db_cache_size/2-cur.CURRENT_SIZE>0
----查回滚速度
select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",
to_char(sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400),'yyyy-mm-dd hh24:mi:ss')
from v$fast_start_transactions;
----查询高版本的SQL
Doc ID 296377.1
https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=11ymqf0dny_4&_afrLoop=371676928895319#aref_section21
.直接查询
select sql_id,version_count, ADDRESS,sql_text
from v$sqlarea
where version_count > 100
order by version_count, hash_value;
?Version 9.2.X.X and below :
select * from v$sql_shared_cursor where kglhdpar = '0000000386BC2E58'
?Version 10.0.X.X and above:
select * from v$sql_shared_cursor where address = '0000000386BC2E58'
select * from v$sql_shared_cursor
where sql_id='54znbush4vgzn'
-----获取OUTLINE
select statement_id,plan_id from plan_table where rownum<2;
select * from table(dbms_xplan.display(null,null,'advanced -bytes','plan_id = 5417'));
select * from table(dbms_xplan.display(null,null,'advanced','plan_id = 5417'));
----从AWR与内存中取详细HINT
select * from table(dbms_xplan.display_awr
('9v3u2vxx3a8cr','1814554837','203185647','ADVANCED'));
dbms_xplan.display_cursor();
-----数据受限模式
---以受限模式启动库
startup restrict;
---开启受限模式
alter system enable restricted session;
---关闭受限模式
alter system disable restricted session;
----查看内存中执行计划
select * from table(dbms_xplan.display_cursor);
----分布式事物异常处理
时间点很吻合，rollback吧。
状态为prepared的，执行如下操作rollback分布事务，胡璨实施一下吧。谢谢。
rollback force 'local_tran_id';
execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');
----查询表的主外键关系
select (select a.table_name
from dba_constraints a
where a.constraint_name = c.r_constraint_name
) 主表表名,
c.r_constraint_name 主表主键键约束名,
c.table_name 从表表名,
c.constraint_name 从表外键约束名,c.*
from dba_constraints c
where c.constraint_type = 'R'
and c.table_name in ('CEIS_SYNC_CLIENT_PROP_TBL','CEIS_CLIENT_PROPERTY_INFO','CEIS_BIEB_POL_INFO1','CEIS_BIEB_POL_INFO2','CEIS_BIEB_POL_INFO3');
-----高水位情况
SELECT TABLE_NAME,(BLOCKS*8192/1024/1024)"理论大小M",
(NUM_ROWS*AVG_ROW_LEN/1024/1024)"实际大小M",
round((NUM_ROWS*AVG_ROW_LEN/1024/1024)/(BLOCKS*8192/1024/1024),3)*100||'%' "实际使用率%"
FROM DBA_TABLES where table_name='DEV_SAL_INFO_TMP';
-----
select * from dba_hist_snapshot t where t.begin_interval_time> date'2015-5-29' order by snap_id desc
select * from table(dbms_workload_repository.awr_report_html('203185647',1,147024,147026));
-----生成ASH
select * from
table(dbms_workload_repository.ash_report_html('151886107',1,to_date('2015-6-1 17:40:00','yyyy-mm-dd hh24:mi:ss'),to_date('2015-6-1 17:50:00','yyyy-mm-dd hh24:mi:ss')))
----对一个分区表收集统计信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ROBINSON',
tabname => 'P_TEST',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'for all columns size repeat',
degree => DBMS_STATS.AUTO_DEGREE,
granularity => 'ALL',
cascade=>TRUE
);
END;
/
上面的例子收集ROBINSON.P_TEST表的统计信息。里面值得注意的一个参数就是granularity，他有7个选项。
granularity => 'ALL' 收集分区，子分区，全局的统计信息
granularity => 'AUTO' 这个是默认的设置，ORACLE会根据分区类型来决定用ALL,GLOBAL AND PARTITION ,还是其他的
granularity => 'DEFAULT' 这个是过期了的
granularity => 'GLOBAL' 收集全局统计信息
granularity => 'GLOBAL AND PARTITION' 收集全局，分区统计信息，但是不收集子分区统计信息
granularity => 'PARTITION' 收集分区统计信息
granularity => 'SUBPARTITION' 收集子分区统计信息
当然我们可以指定partname，自己控制对哪个分区收集统计信息
-----连接数
select a.snap_id,to_char(b.begin_interval_time,'yyyy-mm-dd hh24:mi:ss'),to_char(b.end_interval_time,'yyyy-mm-dd hh24:mi:ss'),a.current_utilization,a.max_utilization,a.limit_value
from dba_hist_resource_limit a,dba_hist_snapshot b
where resource_name='processes' and a.snap_id=b.snap_id
order by a.snap_id desc;
----通过TRUNCATE清理大表的表分区
alter table icsdata.ICS_BENEFIT_DEMO_DETAIL truncate partition SYS_P12013 update global indexes parallel 4;
alter table icsdata.ICS_BENEFIT_DEMO_DETAIL truncate partition SYS_P13093 update global indexes parallel 4;
alter table icsdata.ICS_BENEFIT_DEMO_DETAIL truncate partition SYS_P14173 update global indexes parallel 4;
--确认无失效对象产生
select * from dba_objects where status ='INVALID' ;
select * from dba_indexes where status='UNUSABLE';
select * from dba_ind_partitions where status='UNUSABLE';
select * from dba_tables where status='UNUSABLE';
select * from dba_constraints where status='DISABLE';
----快照排序
select snap_id,dbid,to_char(end_interval_time,'yyyy-mm-dd hh24:mi:ss') from dba_hist_snapshot
order by end_interval_time desc;
------PLSQL取AWR报告
select * from table(dbms_workload_repository.awr_report_html('203185647',1,175129,175130));
----新用户创建脚本
conn sys/&password@&sid as sysdba
-- Create the user
create user lifeemon
identified by paic1234
default tablespace users
temporary tablespace TEMP
profile DEFAULT
quota unlimited on users;
----赋予新用户权限的拼接语句
select 'grant '||privilege||' to LIHONGLIANG001;' from dba_sys_privs
where grantee='ZHANGYU090'
union all
select 'grant '||granted_role||' to LIHONGLIANG001;' from dba_role_privs
where grantee='ZHANGYU090'
union all
select 'grant '||privilege||' on '||owner||'.'||table_name||' to LIHONGLIANG001;' from dba_tab_privs
where grantee='ZHANGYU090';
结果类似如下：
---grant privilege to lifeemon
grant CREATE DATABASE LINK to lifeemon;
grant SELECT ANY TABLE to lifeemon;
grant CREATE TABLE to lifeemon;
grant CREATE SESSION to lifeemon;
grant SELECT_CATALOG_ROLE to lifemon;
grant R_APPMGR_QRY to lifemon;
grant DEBUG on SYS.DBMS_WORKLOAD_REPOSITORY to lifemon;
grant EXECUTE on SYS.DBMS_WORKLOAD_REPOSITORY to lifemon;
grant SELECT on DBMGR.FACTUSER_LIST to lifemon;
grant EXECUTE on PDFDATA.PDF_ICS_FUNC_PKG to lifemon;
grant EXECUTE on DBMGR.PRC_KILL_SESSION to lifemon;
------ASH中EVENT情况
select event,sql_id,count(*) from dba_hist_active_sess_history
where snap_id between 3882 and 3883
and sample_time between to_date('2016-04-15 12:02:00','yyyy-mm-dd hh24:mi:ss') and to_date('2016-04-15 12:07:00','yyyy-mm-dd hh24:mi:ss')
group by event,sql_id
order by 3 desc;
----SGA与PGA使用率
select name,total,round(total-free,2) used, round(free,2) free,round((total-free)/total*100,2) pctused from
(select 'SGA' name,(select sum(value/1024/1024) from v$sga) total,
(select sum(bytes/1024/1024) from v$sgastat where name='free memory')free from dual)
union
select name,total,round(used,2)used,round(total-used,2)free,round(used/total*100,2)pctused from (
select 'PGA' name,(select value/1024/1024 total from v$pgastat where name='aggregate PGA target parameter')total,
(select value/1024/1024 used from v$pgastat where name='total PGA allocated')used from dual);
----PGA使用情况
select sum(PGA_ALLOC_MEM)/1024/1024/1024 from v$process;
----最大PGA使用量
select sum(pga_max_mem)/1024/1024/1024 from v$process；
----查看ORACLE进程情况；
ps -ef|grep $ORACLE_SID|grep -v grep| grep LOCAL=NO| awk '{print $2}'
---会话汇总情况：
set linesize 300
select inst_id,count(1) from gv$session gs
WHERE gs.status='ACTIVE' AND gs.TYPE<>'BACKGROUND'
group by inst_id
ORDER BY gs.inst_id;
---会话详情：
SELECT gs.INST_ID,gs.saddr,gs.SID,gs.SERIAL#,gs.EVENT,gsql.SQL_ID,gsql.SQL_FULLTEXT,gs.USERNAME,gs.OSUSER, gs.PROCESS,gs.MACHINE,gs.PROGRAM FROM gv$session gs,gv$sql gsql
WHERE gs.status='ACTIVE' AND gs.TYPE<>'BACKGROUND'and gsql.SQL_ID=gs.SQL_ID and gsql.INST_ID=gs.INST_ID
ORDER BY gs.inst_id;
----会话等待情况
set linesize 300
select inst_id,decode(event,null,'SUM',event),count(1) from gv$session gs
WHERE gs.status='ACTIVE' AND gs.TYPE<>'BACKGROUND'
group by rollup(inst_id,event)
ORDER BY 1,3 desc;
---查询表中数据存放的数据块情况
set linesize 200
col name format a40
select a.file_id,a.block_id,a.blocks,b.name
from dba_extents a,v$datafile b
where a.file_id=b.file# and a.owner='&Schema' and a.segment_name='&TableName';
----根据数据文件与块号查找对象
Select tablespace_name,segment_type,owner,segment_name From dba_extents Where file_id=&file_id and &block_number between block_id and block_id+blocks-1;
----DBV验证段
dbv userid=system/oracle segment_id=4.4.522
dbv userid=system/oracle segment_id=tsn.segfile.segblock
tsn--表示表空间id
segfile--表示段头所在数据文件号
segblock--表示段头数据块号
这三个值可以通过数据字典sys_dba_segs获取，相关的列分别是TABLESPACE_ID、 HEADER_FILE和HEADER_BLOCK
select tablespace_id,header_file,header_block
from sys.sys_dba_segs
where owner='&schema' and segment_name='&segment';
TABLESPACE_ID HEADER_FILE HEADER_BLOCK
------------- ----------- ------------
4 4 522
----查出表中记录所在的块
select distinct dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from &schema&Table;
----查询数据块存放的数据类型情况
set linesize 200
col segment_name format a30
select segment_name,tablespace_name,segment_type,block_id,file_id,bytes from dba_extents where block_id=&block_id and file_id=&file_id;
----快照/报告
exec dbms_workload_repository.create_snapshot;
select max(snap_id) from dba_hist_snapshot;
@?/rdbms/admin/awrrpt.sql
----清理数据库AWR报告信息：
begin
dbms_workload_repository.drop_snapshot_range(low_snap_id =>4274 ,high_snap_id =>4275 ,dbid => 49371800);
end;
----获取当前最繁忙BUFFER(TOP 10)
select * from
(select addr,ts#,file#,dbarfil,dbablk,tch from x$bh
order by tch desc)
where rownum<11;
---获取当前热点BUFFER来自对象情况（TOP10）
select e.owner,e.segment_name,e.segment_type
from dba_extents e,
(select * from
(select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc)
where rownum<11
)b
where e.relative_fno=b.dbarfil
and e.block_id<=b.dbablk
and e.block_id+e.blocks>b.dbablk;
----获取SHARED POOL中CHUNK信息；
select * from x$ksmsp;
------查询在sort排序区使用的执行耗时的SQL：
Select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value))as Space,tablespace,segtype,sql_text
from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr order by se.username,se.sid;
-----或是：
Select su.username,su.Extents,tablespace,segtype,sql_text
from v$sort_usage su,v$sql s
Where su.SQL_ID = s.SQL_ID;
----查找约束对象
set linesize 200
col owner format a20
col CONSTRAINT_NAME format a30
col table_name format a20
col column_name format a20
select au.owner,au.table_name,au.CONSTRAINT_NAME,cu.column_name from dba_cons_columns cu, dba_constraints au where cu.constraint_name=au.constraint_name and
cu.table_name='&table_name';
----RMAN备份情况
set linesize 200
col INPUT_BYTES_DISPLAY format a20
col OUTPUT_BYTES_DISPLAY format a20
col TIME_TAKEN_DISPLAY format a20
SELECT R.START_TIME,
R.END_TIME,
R.INPUT_BYTES_DISPLAY,
R.OUTPUT_BYTES_DISPLAY,
R.STATUS,
R.INPUT_TYPE,
R.TIME_TAKEN_DISPLAY
FROM V$RMAN_BACKUP_JOB_DETAILS R
WHERE R.START_TIME > SYSDATE - 1
ORDER BY R.START_TIME desc;
---数据库的版本和平台和相关信息
select * from v$version;
select * from dba_registry_database;
select dbid, name, open_mode, database_role, platform_name from v$instance;
select dbms_utility.port_string from dual;
-----是否配置了DataGuard
select protection_mode, protection_level, remote_archive, database_role, dataguard_broker,guard_status
from v$database;
---日志文件的组成 （单实例、RAC通用）
select l.group#,l.THREAD#, lf.type,lf.member, l.bytes/1024/1024, l.status LOG_STATUS, lf.status LOGFILE_STATUS
from v$log l, v$logfile lf
where l.group# = lf.group#
order by 1,3;
---干掉DLL的锁会话
Select s.Sid,
'ALTER SYSTEM KILL SESSION ''' || s.Sid || ',' || s.Serial# || ''';' Killsql,
s.Serial#,
p.Spid,
Owner,
Name,
Mode_Held,
Mode_Requested,
Machine,
s.Module,
s.Status,
s.Action
From Sys.Dba_Ddl_Locks a, V$process p, V$session s
Where s.Paddr = p.Addr
And a.Session_Id = s.Sid
And Name = '&pkg_name'
Order By s.Sid;
---创建DBLINK
CREATE public database link pub_xacsdbser CONNECT TO enn_idp IDENTIFIED BY enn_idp
using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.37.148.238)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME= csdbser)))';
---查看数据库中OPEN用户权限分配
select grantee,privilege from dba_sys_privs where grantee in (
SELECT username FROM DBA_USERS
WHERE ACCOUNT_STATUS='OPEN'
and default_tablespace not in ('SYSTEM') )
union
select a.grantee,b.privilege from (
select * from dba_role_privs where grantee in (
SELECT username FROM DBA_USERS
WHERE ACCOUNT_STATUS='OPEN'
and default_tablespace not in ('SYSTEM'))) a
left join dba_sys_privs b on a.granted_role=b.GRANTEE
order by 1;
-----查看某个用户的权限
select PRIVILEGE from dba_sys_privs
where grantee in
(select granted_role from dba_role_privs
where grantee='DBMONOPR')
UNION
SELECT PRIVILEGE FROM DBA_SYS_PRIVS
WHERE GRANTEE='DBMONOPR';
----To see the (current) undo tablespace percent of space in use
SELECT ((SELECT (NVL(SUM(bytes), 0))
FROM dba_undo_extents
WHERE tablespace_name = 'UNDOTBS1'
AND status IN ('ACTIVE', 'UNEXPIRED')) * 100) /
(SELECT SUM(bytes)
FROM dba_data_files
WHERE tablespace_name = 'UNDOTBS1') PCT_INUSE
FROM dual;
---表空间使用情况
SELECT A.TABLESPACE_NAME "表空间名",
A.TOTAL_SPACE "总空间(G)",
A.TOTAL_SPACE-B.USED_SPACE "剩余空间(G)",
B.USED_SPACE "已使用(G)",
B.USED_SPACE/A.TOTAL_SPACE "已使用百分比%",
AA.AUTOEXTENSIBLE,
AA.STATUS
FROM (select tablespace_name,
trunc(sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)/1024/1024/1024),2) total_space from dba_data_files
group by tablespace_name
) A,
(select TABLESPACE_NAME,TRUNC(sum(bytes)/1024/1024/1024,2) USED_SPACE from dba_extents
GROUP BY TABLESPACE_NAME) B,
(SELECT TABLESPACE_NAME,
MAX(AUTOEXTENSIBLE) AUTOEXTENSIBLE,
SUM(BYTES) / 1024 / 1024 /1024 "SIZE(G)",
STATUS
FROM DBA_DATA_FILES A
GROUP BY TABLESPACE_NAME, STATUS) AA
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME and a.tablespace_name=aa.tablespace_name
ORDER BY 5;
---表空间使用情况
select tt.tablespace_name,
tt.total_size,
tt.used_size,
tt.USED_RATE AS "USED%",
tt.free_size,
AA.AUTOEXTENSIBLE,
AA.STATUS
from (select a.tablespace_name,
a.total_size,
a.header_size + nvl(b.used_size, 0) used_size,
round((a.header_size + nvl(b.used_size, 0)) / a.total_size * 100,
2) USED_RATE,
a.total_size - (a.header_size + nvl(b.used_size, 0)) free_size
from (select tablespace_name,
round(sum(bytes) / 1024 / 1024 /1024, 2) total_size,
round(sum(64) / 1024/1024, 2) header_size
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,
round(sum(bytes) / 1024 / 1024 /1024, 2) used_size
from dba_extents
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)) tt,
(SELECT TABLESPACE_NAME,
MAX(AUTOEXTENSIBLE) AUTOEXTENSIBLE,
SUM(BYTES) / 1024 / 1024 /1024 "SIZE(G)",
STATUS
FROM DBA_DATA_FILES A
GROUP BY TABLESPACE_NAME, STATUS) AA
WHERE TT.TABLESPACE_NAME = AA.TABLESPACE_NAME
ORDER BY tt.USED_RATE;
----临时段使用情况1
SELECT se.username, se.SID, se.serial#, se.sql_address, se.machine, se.program, su.TABLESPACE,su.segtype, su.CONTENTS from
v$session se, v$sort_usage su WHERE se.saddr = su.session_addr
-----查看临时表空间每个数据文件实际使用量
Select f.tablespace_name,
d.file_name "Tempfile_name",
round((f.bytes_free + f.bytes_used) / 1024 /1024, 2) "total MB",
round(((f.bytes_free + f.bytes_used) -nvl(p.bytes_used, 0)) / 1024 / 1024, 2) "Free MB" ,
round(nvl(p.bytes_used, 0)/ 1024 / 1024, 2)"Used MB",
round((round(nvl(p.bytes_used, 0)/ 1024 /1024, 2)/round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2))*100,2) as"Used_Rate(%)",
d.autoextensible
from SYS.V_$TEMP_SPACE_HEADER f,DBA_TEMP_FILES d, SYS.V_$TEMP_EXTENT_POOL p
where f.tablespace_name(+) = d.tablespace_name
and f.file_id(+) = d.file_id
and p.file_id(+) =d.file_id;
----查询在sort排序区使用的执行耗时的SQL
Select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value))as Space,tablespace,segtype,s.SQL_FULLTEXT,se.sql_id
from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr
order by space desc
----回滚段事务情况
select a.name,b.xacts,c.sid,c.serial#,c.EVENT,e.START_TIME,c.username,d.sql_text,d.sql_id,b.WAITS
from v$rollname a,gv$rollstat b, gv$session c,gv$sqltext d, gv$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;
2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
select * from dba_sys_privs;
select * from user_sys_privs;
3.查看角色(只能查看登陆用户拥有的角色)所包含的权限
select * from role_sys_privs;
4.查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
5.查看所有角色：
select * from dba_roles;
6.查看用户或角色所拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;
7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from V$PWFILE_USERS
注意：
1、以下语句可以查看Oracle提供的系统权限
select name from sys.system_privilege_map
2、查看一个用户的所有系统权限(包含角色的系统权限)
Sql代码
select privilege from dba_sys_privs where grantee='DATAUSER'
union
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='DATAUSER' );
查询表空间名称和大小
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;
查看数据库引起锁表的SQL语句：
SELECT a.username,
a.machine,
a.program,
a.sid,
a.serial#,
a.status,
c.piece,
c.sql_text
FROM v$session a,
v$sqltext c
WHERE a.sid in
(
select distinct t2.sid
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid
)
and a.sql_address=c.address(+)
ORDER BY c.piece
查看数据库锁的情况必须要有DBA权限，可以使用一下SQL 语句：
select object_id,session_id,locked_mode from v$locked_object;
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;
--查看被锁的表
select p.spid,
a.SID,
a.serial#,
a.EVENT,
a.BLOCKING_INSTANCE,
a.BLOCKING_SESSION,
a.BLOCKING_SESSION_STATUS,
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
and c.object_id = b.object_id;
杀掉进程
alter system kill session 'sid,serial#';
查询所有用户表使用大小的前三十名
select * from (select segment_name,bytes from dba_segments where owner = USER order by bytes desc ) where rownum <= 30
查询表空间有那些表:
select table_name from all_tables where tablespace_name= 'TEMP';
查询最近使用过的SQL：
select * from v$sqlarea
查询锁阻塞 SELECT (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
---获取TRACE文件
select d.value||'/'||LOWER(RTRIM(i.INSTANCE,CHR(0)))||'_ora_'||p.spid||'.trc' trace_file_name
from (select p.spid from sys.v$mystat m,sys.v$session s,sys.v$process p
where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,
(select t.instance from sys.v$thread t,sys.v$parameter v
where v.name='thread' and (v.value=0 or t.thread#=to_number(v.value)))i,
(select value from sys.v$parameter where name='user_dump_dest')d;
---或者
select tracefile from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where statistic#=1));
---转储library_cache内容
SQL>ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME LIBRARY_CACHE LEVEL 4';
---查看所有隐藏参数：
SELECT i.ksppinm name,
i.ksppdesc description,
CV.ksppstvl VALUE,
CV.ksppstdf isdefault,
DECODE (BITAND (CV.ksppstvf, 7),
1, 'MODIFIED',
4, 'SYSTEM_MOD',
'FALSE')
ismodified,
DECODE (BITAND (CV.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadjusted
FROM sys.x$ksppi i, sys.x$ksppcv CV
WHERE i.inst_id = USERENV ('Instance')
AND CV.inst_id = USERENV ('Instance')
AND i.indx = CV.indx
AND i.ksppinm LIKE '/_%' ESCAPE '/'
ORDER BY REPLACE (i.ksppinm, '_', '');
[sql] view plaincopy
SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND TRANSLATE (ksppinm, '_', '#') LIKE '#%';
----查看_db_block_hash_buckets的参数值
[sql] view plaincopy
SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND ksppinm = '_db_block_hash_buckets';
--查看大表
SELECT ds.segment_name, (SUM(ds.bytes) / 1024) / 1024 / 1024 G_BYTES
FROM dba_segments ds
WHERE ds.owner = 'MSC'
AND ds.segment_type LIKE 'TABLE%'
GROUP BY ds.segment_name, ds.segment_type
HAVING(SUM(ds.bytes) / 1024) / 1024 / 1024 > 0.0001
ORDER BY 2 DESC;
----
----查看统计信息中列的VALUE
declare
out_date date;
begin
dbms_stats.convert_raw_value(rawval => '786D071E010101',resval => out_date);
dbms_output.put_line(to_char(out_date,'yyyy-mm-dd'));
end;
426
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
