oracle运维-常用SQL

################################################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表格中

新闻名称：oracle运维-常用SQL
URL网址：http://scjbc.cn/article/pdsggh.html