oracle运维-常用SQL
发布时间：2020-07-26 04:47:29
来源：51CTO
阅读：440
作者：心神cgr
################################################V$active_session_history################################################
set line 1000
col session_id for 99999999
col session_serial# for 9999999
col program for a30
col sql_id for a30
col event for a30
col seq# for 999999
col count(*) for 99999
select session_id,session_serial#,program,sql_id,event,seq#,count(*)
from v$active_session_history
where sample_time between to_date('2018-12-27 08:00:00','yyyy-mm-dd hh34:mi:ss')
and to_date('2018-12-28 09:00:00','yyyy-mm-dd hh34:mi:ss')
and sql_id is not null
group by session_id,session_serial#,program,sql_id,event,seq#
having count(*) >1;
set line 100
col sample_time for a30
col sample_id for 9999999
col event for a30
col seq# for 9999999999
col sql_id for a30
select sample_time,sample_id,event,seq#,sql_id
from v$active_session_history
where session_id=&session_id
and session_serial#=&session_serial#
and event='&event'
and seq#=&seq#;
set line 200
col session_id for 999999999999
col session_serial for 9999999999
col xid for 9999999999
col program for a30
col event for a30
col blocking_session for 99999999
select session_id,session_serial,xid,program,event,blocking_session
from v$active_session_history
where between to_date('2018-12-27 08:00:00','yyyy-mm-dd hh34:mi:ss')
and to_date('2018-12-28 09:00:00','yyyy-mm-dd hh34:mi:ss')
and sql_id is not null
and xid is not null
set line 200
col session_id for 999999
col session_serial# for 9999999
col seq# for 99999999
col event for a30
col count(*) for 999999
select inst_num, session_id,session_serial#,seq#,event,
count(*)
from dba_hist_active_sess_history
where sample_time between to_date('2018-12-28 00:00:00','yyyy-mm-dd hh34:mi:ss')
and to_date('2018-12-29 00:00:00','yyyy-mm-dd hh34:mi:ss')
group by inst_num, session_id,session_serial#,seq#,event
order by count(*);
set line 100
col sample_id for 999999
col inst_num for 99
col blocking_session for 999999
select sample_id,inst_num,blocking_session
from dba_hist_active_sess_history
where sample_time between to_date('2018-12-28 00:00:00','yyyy-mm-dd hh34:mi:ss')
and to_date('2018-12-29 00:00:00','yyyy-mm-dd hh34:mi:ss')
and event='&event'
and inst_num=&inst_num
and session_id=&session_id
and session_serial=&session_serial
and seq#=&seq#
set line 100
col program for a30
col inst_num for 99
col sql_id for a30
select program,inst_num,event,sql_id
from dba_hist_active_sess_history
where sample_id=&sample_id
and inst_num=&inst_num
and session_id=&session_id
and session_serial#=&session_serial#
-- in_connection_mgmt
-- in_parse
-- in_hard_parse
-- in_sql_execution
-- in_plsql_execution
-- in_plsql_rpc
-- in_plsql_compliation
-- in_java_execution
-- in_bind
-- in_cursor_close
-- in_sequence_load
select
in_connection_mgmt,
in_parse,
in_hard_parse,
in_sql_execution,
in_plsql_execution,
in_plsql_rpc,
in_plsql_compliation,
in_java_execution,
in_bind,
in_cursor_close,
in_sequence_load
from dba_hist_active_sess_history
where inst_num=&inst_num
and SESSION_ID=&session_id
and session_serial#=&session_serial#
set line 100
col session_id for 99999999
col session_serial# for 9999999
col sql_id for a30
col sql_exec_id for 9999999
col count(*) for 99999999
select session_id,session_serial#,sql_id,sql_exec_id,count(*)
from dba_hist_active_sess_history
group by session_id,session_serial#,sql_id,sql_exec_id
order by count(*);
set line 100
col opname for a30
col plsql_entry_id for 999999
col p1 for a30
col p2 for a30
col p3 for a30
select opname,plsql_entry_id,p1,p2,p3
from dba_hist_active_sess_history
where instance_number=&instance_number
and session_id=&session_id
and session_serial#=&session_serial
set line 100
col program for a30
col inst_num for 99
col blocking_session for 99999999
col blocking_session_serial# for 99999999999
col blocking_inst_id for 9999
select program,inst_num,event,blocking_session,blocking_session_serial#,blocking_inst_id,sql_id
from dba_hist_active_sess_history
where inst_num=&inst_num
and session_id=&session_id
and session_serial#=&session_serial#
select type,parameter from v$rowcache where cache#=10
###############################V$session_longops,v$session#####查看进程剩余耗时#####################################
set line 200
col sid for 999999999999
col SERIAL# for 99999999999
col PROGRAM for a30
col opname for a30
col time_remaining for 99999999999999
SELECT a.SID,a.SERIAL#,a.PROGRAM "程序进程",b.OPNAME "操作名称",b.TIME_REMAINING "剩余时间(秒)"
FROM v$session a,v$session_longops b
WHERE a.SID = b.SID
AND a.SERIAL# = b.SERIAL#
AND a.PROGRAM LIKE '%rman%'
AND time_remaining >0;
#############################oradebug#########################################################################################
1.session Tracing
alter session set tracefile_identifier='10046';
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set events '10046 trace name context forever,level 12';
2.tracing process
select p.PID,p.SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = &SESSION_ID
/
select 'ospid: ' || p.spid || ' # ''' ||s.sid||','||s.serial#||''' '||
s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
from v$session s , v$process p
where p.addr = s.paddr
and s.username <> ' ';
connect / as sysdba
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
oradebug tracefile_name
oradebug event 10046 trace name context off
connect / as sysdba
oradebug setorapid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
oradebug tracefile_name
oradebug event 10046 trace name context off
-- 10053 trace
####################################################v$SQLAREA####################################################################
yyyy-mm-dd hh34:mi:ss
set line 200
col sql_id for a30
col last_active_time for a30
col first_load_time
col last_load_time
col executions
col buffer_gets
col user_io_wait_time
col cpu_time
col elapsed_time
col physical_read_requests
col physical_read_bytes
select
sql_id,
last_active_time,
first_load_time,
last_load_time,
executions,
buffer_gets,
user_io_wait_time,
cpu_time,
elapsed_time,
physical_read_requests,
physical_read_bytes
from v$SQLAREA
order by physical_read_bytes desc;
##############################################实验：检查两个表中的数据及对应的数据的条数是否相同########################################
1.建立视图
create or replace view v as
select * from emp where deptno!=10
union all
select * from emp where ename='SCOTT'
2.要求查找视图V和表emp中不同的数据
SELECT rownum,empno,ename
FROM v
WHERE ename='SCOTT';
SELECT rownum,empno,ename
FROM emp
WHERE ename='SCOTT';
3.比较数据集不同
select v.empno,v.ename ,b.empno,b.ename
from v
full join
emp b on(v.empno=b.empno)
where (v.empno is null or b.empno is null);
####################################################统计信息######################################
dba_tab_statistics
dba_tab_col_statistics
set line 100
-- var low_value number;
-- var high_value varchar2(100);
-- select DBMS_STATS.CONVERT_RAW_VALUE('yuiovbssas',:high_value) from dual;
col owner for a10
col table_name for a20
col column_name for a30
col high_value for a30
col low_value for a30
col num_nulls for 9999
col histogram for a30
select owner,table_name,column_name,high_value,low_value,num_nulls,histogram
from dba_tab_col_statistics
where table_name=''
and column_name=''
and owner='';
set line 200
col owner for a20
col table_name for a20
col num_rows for 99999
col partition_name for a20
col LAST_ANALYZED for a30
col blocks for 99999
col empty_blocks for 99999
select owner,table_name,num_rows,partition_name,LAST_ANALYZED,blocks,empty_blocks
from dba_tab_statistics
where owner=''
and table_name='';
-- -- -- -- -- -- -- -- -- -- 索引统计系信息-- -- -- -- -- -- -- --
查看索引的聚簇因子，
-- 聚簇因子应该与表的块数近似
set line 100
col ower for a10
col table_name for a20
col index_name for a20
col clustering_factor for 999999999
col NUM_ROWS for 99999
select owner,table_name,index_name,clustering_factor,NUM_ROWS
from dba_indexes
where index_name='&index_name'
and owner='&owner'
and table_name='&table_name';
查看数据表当前占用block数目
select count(dbms_rowid.rowid_block_number(rowid)) from &table_name;
-- -- -- -- -- -- -- -- -- -- -- --列统计信息 -- -- -- -- -- -- -- -- -- -- -- --
set line 100 verify off
col low_value for a30
col high_value for a30
col num_distinct for 99999
col num_nulls for 9999999
select low_value,high_value,num_distinct,num_nulls
from dba_tab_col_statistics
where table_name='&table_name'
and owner='&owner'
and column_name='&column_name';
-- 1 41,43,43,45,53,53,24 5F75746C245F6C6E635F696E645F7061727473 10807 0
var l varchar2(100);
exec dbms_stats.convert_row_value('5F75746C245F6C6E635F696E645F7061727473',:l);
print l;
#######################V$sqlarea########################################
set line 200
col sql_id for a30
col plan_hash_value for 30
col executions for 99999
col first_load_time for a30
col disk_reads for 99999
col buffer_gets for 9999999
col cpu_time for 9999999999
col last_load_time for 999999999
col last_active_time for 99999999
col loads for 999999
select sql_id,plan_hash_value,executions,first_load_time,disk_reads,buffer_gets,cpu_time,last_load_time,last_active_time,loads
from v$sqlarea
where sql_id='<>';
###################################################################查询隐含参数
SELECT KSPPINM, KSPPSTVL, KSPPDESC
FROM X$KSPPI X, X$KSPPCV Y
WHERE X.INDX = Y.INDX AND KSPPINM = '_db_file_optimizer_read_count';
#################通过创建索引来避免全表扫描，不必要的排序，函数索引,重建索引避免全表扫描#################
1.创建复合索引
create index on (column_name,0) tablespace ;
原理：虽然对于单键值B树索引而言NULL值不入索引，但对于复合索引B树索引来说，NULL值是入索引的。
2.对于需要排序的列创建索引
create index table_name(column_name) tablespace ;
3.
百分号在最前面的like条件确实是没法走索引的，-----'%....'
但同时我们也知道对于有百分号的like条件，
如果百分号的like条件的最后面，则这种情况下是可以走索引的。-----'...%'
在不改变SQL的情形下把百分号从like条件的最前面移到最后面
create index on (reverse(column_name)) ;
4.创建全局索引
create index on () online tablespace
###################对症下药
1.如果是统计信息不准或是因为CBO计算某些SQL的成本所用的公式先天不足而导致的SQL性能问题，
我们可以通过重新收集统计信息，或者手动修改统计信息，或者使用Hint来加以解决。
2.如果是SQL语句的写法问题，我们可以通过在不更改业务逻辑的情况下改写SQL来加以解决。
3.如果是不必要的全表扫描/排序而导致的目标SQL的性能问题，我们可以通过建立合适的索引(包括函数索引、位图索引)来解决。
4.如果是因为各种原因导致的目标SQL的执行计划不稳定，可以通过使用hint、sql profile或者spm来加以解决。
5.如果是表或者索引的不良设计导致的目标SQL的性能问题，我们可以通过重新设计表/索引，重新组织表的里的数据来加以解决。
6.如果上述调整措施都失效，可以考虑用并行来缩短目标SQL的执行时间。
7.如果上述调整措施都失效，调整SQL。
#################创建索引##############################
create index table_name(column_name) tablespace online nologging parallel 2;
alter index noparallel;
##################分区表信息确认##################
1.
set line 100
col table_name for a30
col num_rows for 999999
col blocks for 9999999
col avg_row_len for 99999999
col partitioned for a10
col ext_M for 9999999
select table_name,
num_rows ,--记录数
blocks,
avg_row_len,
partitioned, --是否分区
num_rows * avg_row_len /1024/1024/1024/0.9 est_M -- "数据量大小"
from dba_tables
where table_name='&table_name';
set line 100
col partitioning_type for a10
col subpartitioning_type for a10
select partitioning_type, --分区类型
subpartitioning_type
from dba_part_tables
where table_name='&table_name'
set line 100
col owner for a10
col name for a20
col object_type for a30
col column_name for a30 --分区列
col column_position for a30
select *
from dba_part_key_columns
where name ='&table_name';
#############SPM########################
在当前session中禁掉spm并同时开启自动捕获sql plan baseline
alter session set optimizer_use_sql_plan_baseline=false;
alter session set optimizer_capture_sql_plan_baseline=true;
select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baseline where sql_text like '';
############################执行计划######################
alter session set statistics_level='ALL' ;
set line 200 pages 100
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9 |00:00:00.01 | 4 |
|* 1 | COUNT STOPKEY | | 1 | | 9 |00:00:00.01 | 4 |
| 2 | TABLE ACCESS FULL| T1 | 1 | 9 | 9 |00:00:00.01 | 4 |
-------------------------------------------------------------------------------------
Starts:表示每个具体执行计划步骤被重复执行的次数
E-Rows:表示执行计划中cbo对每一个具体执行计划步骤返回结果集cardinality的预估值
A-Rows:表示执行计划中每个具体步骤的返回结果集的cardinality的实际值
A-Time:表示执行计划中每一个具体执行计划中实际执行时间
Buffers:表示执行计划中每一个具体执行计划步骤实际消耗的逻辑读
###########################选择率################################3
对于like '%...%'这样的模糊查询条件，cbo默认的可选择率5%来评估其返回结果集的cardinality的值
###################查询表空间使用率
COLUMN ts_name format a35 heading "Tablespace"
COLUMN unalloc_size format 999,999 heading "Unalloc(MB)"
COLUMN used_size format 999,999,999 heading "Used(MB)"
COLUMN free_size format 999,999 heading "Free(MB)"
COLUMN capacity format 99,99 heading "Used_pct(%)"
SELECT a.tablespace_name ts_name
,a.unalloc_size
,nvl(f.free_size, 0) free_size
,a.used_size - nvl(f.free_size, 0) used_size
,round((a.used_size - nvl(f.free_size, 0)) /
(a.unalloc_size + a.used_size)
,2)*100 capacity
FROM (SELECT tablespace_name
,round(SUM(bytes) / 1024 / 1024) free_size
,round(AVG(bytes)/1024/1024) avg_size
FROM dba_free_space
GROUP BY tablespace_name) f
,(SELECT tablespace_name
,round(SUM(user_bytes) / 1024 / 1024) used_size
,round(SUM(decode(autoextensible
,'YES'
,decode(sign(maxbytes - user_bytes)
,-1
,0
,maxbytes - user_bytes)
,0)) / 1024 / 1024) unalloc_size
FROM dba_data_files
WHERE status='AVAILABLE'
GROUP BY tablespace_name) a
WHERE 1 = 1
AND a.tablespace_name = f.tablespace_name(+)
ORDER BY capacity DESC;
######################用户密码过期查改
select username,account_status,PROFILE from dba_users where username='&username';
SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
select username,account_status,PROFILE from dba_users where username='&username';
-------------------------------------------------------------------更新分割线2019-1-16------------------------------------------------
#########################表空间数据文件丢失，处理方法######################
startup mount
alter database datafile 7 offline drop; (在非归档模式下脱机数据文件，需要加上drop参数)
alter database open;
数据库开启后，现在可以重新将表空间脱机
alter tablespace offline for recover;(当数据文件被误删时，表空间的脱机命令需要加上for recover参数)
##################创建两个新的联机日志文件组，并进行一次手工的日志切换
alter database add logfile group 4 ('','') size 200m;
alter database add logfile group 5 ('','') size 200m;
alter system switch logfile;
###############创建联机日志文件组
alter database add logfile member '' to group 1;
alter database add logfile member '' to group 2;
alter database add logfile member '' to group 3;
###############如何转移system表空间
1.startup mount
2.!mv
3.alter database rename file '' to '';
4.alter database open;
##############如何转移非system表空间
1.alter tablespace offline;
2.!mv
3.alter tablespace rename datafile '' to '';
4.alter tablespace online;
###############创建临时表空间组
使用临时表空间组的好处
1.同一个用户，在不同的session里可以使用不同的临时表空间
每一个用户有一个默认的临时表，同一个用户登录多个session的时候，如果不使用临时表空间组，那么所有的session使用同一个临时表空间，容易产生瓶颈问题。
2.在并行操作中，不同的从属进程可以使用不同的临时表空间
3.在数据库层面可以同时指定多个默认临时表空间
create tempoary tablespace temp1 tempfile '' size 10m;
create tempoary tablespace temp2 tempfile '' size 10m;
create tempoary tablespace temp3 tempfile '' size 10m;
create tempoary tablespace temp4 tempfile '' size 10m;
alter tablespace temp1 tablespace group temp_test;
alter tablespace temp2 tablespace group temp_test;
alter tablespace temp3 tablespace group temp_test;
alter tablespace temp4 tablespace group temp_test;
############删除临时表空间组(当组内没有成员时，该空间组自动删除)
alter tablespace temp1 tablespace group '';
alter tablespace temp2 tablespace group '';
alter tablespace temp3 tablespace group '';
alter tablespace temp4 tablespace group '';
-------------------------------------------------------------------更新分割线2019-1-21--------------------------------------------------------------------------------------
################创建用户
create user profile default identified by password expire default tablespace tempoary tablespace account unlock;
password expire :用来设置用户口令过期，失效，强制用户登录数据库时必须修改口令
account unlock:创建用户的时候解锁用户，默认该选项
###################################查看最近7天的DB time##########################
set line 100
col DATE_TIME for a40
col STAT_NAME for a40
col per_sec for 99999
WITH sysstat AS
(select sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
ss.stat_name stat_name,
ss.value e_value,
lag(ss.value, 1) over(order by ss.snap_id) b_value
from dba_hist_sysstat ss, dba_hist_snapshot sn
where trunc(sn.begin_interval_time) >= sysdate - 7
and ss.snap_id = sn.snap_id
and ss.dbid = sn.dbid
and ss.instance_number = sn.instance_number
and ss.dbid = (select dbid from v$database)
and ss.instance_number = (select instance_number from v$instance)
and ss.stat_name = 'DB time')
select to_char(BEGIN_INTERVAL_TIME, 'mm-dd hh34:mi') ||
to_char(END_INTERVAL_TIME, ' hh34:mi') date_time,
stat_name,
round((e_value - nvl(b_value, 0)) /
(extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60 +
extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60 +
extract(minute from(end_interval_time - begin_interval_time)) * 60 +
extract(second from(end_interval_time - begin_interval_time))),
0) per_sec
from sysstat
where (e_value - nvl(b_value, 0)) > 0
and nvl(b_value, 0) > 0;
##############################表空间与文件和用户对应关联,
set line 200
col name for a60
select t.name,f.name from v$datafile f,v$tablespace t where t.ts#=f.ts# order by 1;
select t.name,count(f.name) from v$datafile f,v$tablespace t where t.ts#=f.ts# group by t.name order by 1;
select username,default_tablespace from dba_users where account_status='OPEN';
##############################表空间使用率
COLUMN ts_name format a35 heading "Tablespace"
COLUMN unalloc_size format 999,999 heading "Unalloc(MB)"
COLUMN used_size format 999,999,999 heading "Used(MB)"
COLUMN free_size format 999,999 heading "Free(MB)"
COLUMN capacity format 99,99 heading "Used_pct(%)"
SELECT a.tablespace_name ts_name
,a.unalloc_size
,nvl(f.free_size, 0) free_size
,a.used_size - nvl(f.free_size, 0) used_size
,round((a.used_size - nvl(f.free_size, 0)) /
(a.unalloc_size + a.used_size)
,2)*100 capacity
FROM (SELECT tablespace_name
,round(SUM(bytes) / 1024 / 1024) free_size
,round(AVG(bytes)/1024/1024) avg_size
FROM dba_free_space
GROUP BY tablespace_name) f
,(SELECT tablespace_name
,round(SUM(user_bytes) / 1024 / 1024) used_size
,round(SUM(decode(autoextensible
,'YES'
,decode(sign(maxbytes - user_bytes)
,-1
,0
,maxbytes - user_bytes)
,0)) / 1024 / 1024) unalloc_size
FROM dba_data_files
WHERE status='AVAILABLE'
GROUP BY tablespace_name) a
WHERE 1 = 1
AND a.tablespace_name = f.tablespace_name(+)
ORDER BY capacity DESC;
###############################################asm
sqlplus / as sysasm
select * from v$asm_operation; <==> $asmcmd lsop
asmcmd rebal --power N -- 修改磁盘组reblance的并行度
asmcmd lsdg
#################################################
——————————————————————————————————我是分割线 更新时间 2019年1月24日 ————————————————————
###########################转载于网络######################查看sga，pga等使用率
set line 100
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)
union
select name,round(total,2) total,round((total-free),2) used,round(free,2) free,round((total-free)/total*100,2) pctused from (
select 'Shared pool' name,(select sum(bytes/1024/1024) from v$sgastat where pool='shared pool')total,
(select bytes/1024/1024 from v$sgastat where name='free memory' and pool='shared pool') free from dual)
union
select name,round(total,2)total,round(total-free,2) used,round(free,2) free,round((total-free)/total,2) pctused from (
select 'Default pool' name,( select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
(select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from (
select 'KEEP pool' name,(select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='KEEP' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
(select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='KEEP' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from (
select 'RECYCLE pool' name,( select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='RECYCLE' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
(select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='RECYCLE' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from(
select 'DEFAULT 16K buffer cache' name,(select a.cnum_repl*16/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=16384) total,
(select a.anum_repl*16/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=16384) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from(
select 'DEFAULT 32K buffer cache' name,(select a.cnum_repl*32/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=32768) total,
(select a.anum_repl*32/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=32768) free from dual)
union
select name,total,total-free used,free, (total-free)/total*100 pctused from (
select 'Java Pool' name,(select sum(bytes/1024/1024) total from v$sgastat where pool='java pool' group by pool)total,
( select bytes/1024/1024 free from v$sgastat where pool='java pool' and name='free memory')free from dual)
union
select name,Round(total,2),round(total-free,2) used,round(free,2) free, round((total-free)/total*100,2) pctused from (
select 'Large Pool' name,(select sum(bytes/1024/1024) total from v$sgastat where pool='large pool' group by pool)total,
( select bytes/1024/1024 free from v$sgastat where pool='large pool' and name='free memory')free from dual)
order by pctused desc;
/
set line 100
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);
#################################################锁相关
####------------------------------------
v$transaction
XIDUSN 表示当前事务使用的回滚段的编号
XIDSLOT 表示该事物在回滚段头部的事务表中对应的记录编号
XIDSQN 说明序列号
STATUS 说明该事物是否为活动的
select XIDUSN,XIDSLOT,XIDSQN,STATUS from v$transaction;
####-------------------------------------------
v$lock
记录session已经获得的锁定以及正在请求的锁定的信息
SID 说明session的id
TYPE 锁定级别，主要关注TX和TM
LMODE 已经获得的锁定的模式，以数字编码表示
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
REQUEST 正在请求的锁定的模式，以数字编码表示
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
BLOCK 是否阻止了其他用户获得锁定，大于0说明是，等于0说明否
set line 100
select sid,type,id1,id2,
decode(LMODE,0,'None',1,'Null',2,'row share',3,'row exclusive',4,'share',5,'share row exclusive',6,'exclusive') lock_mode ,
decode(REQUEST,0,'None',1,'Null',2,'row share',3,'row exclusive',4,'share',5,'share row exclusive',6,'exclusive') request_mode,
block
from v$lock
where sid=&sid;
注：
type为锁类型
对TM而言id1为表对象，id2始终为0
对TX将ID1.ID2分别对应哪个事务，
lock_mode 持有锁
request_mode请求锁
block 被这个锁锁住的
TX: 行级锁，就一种模式，排他锁
TM: 表级锁，行级排他锁
ID1的拆解
select trunc(&ID1/power(2,16)) as undo_blk#,
bitand(&ID1,to_number('ffff','xxxx')) + 0 as slot#
from dual;
###-------------
v$enqueue_lock--把正在请求的锁列出来
其中的记录按照申请锁定的时间先后顺序排列，先申请锁定
的session排在前面，排在前面的session将会先获得锁定
###-------------
v$lock_object
记录了当前已经被锁定的对象的信息
####--------------
在数据库系统中我们同时可以获得的TX锁定的总个数由初始化参数transaction决定，
而可以获得TM锁定的个数由初始化参数dml_locks决定
####--------------
dba_ddl_locks;
######################数据泵数据迁移，前后对象类型比对
select owner,object_type,count(*) from dba_objects where owner in ('&username') group by owner,object_type order by 1;
--csv--
select owner||','||object_type||','||count(*) from dba_objects where owner in ('&username') group by owner,object_type order by 1;
方便粘贴到excel表格中
2594
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
