oracle运维-常用SQL

################################################V$active_session_history################################################set line 1000col session_id for 99999999col session_serial# for 9999999col program for a30col sql_id for a30col event for a30col seq# for 999999col count(*) for 99999select session_id,session_serial#,program,sql_id,event,seq#,count(*) from v$active_session_historywhere 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 nullgroup by session_id,session_serial#,program,sql_id,event,seq#having count(*) >1;set line 100col sample_time for a30col sample_id for 9999999col event for a30col seq# for 9999999999col sql_id for a30select sample_time,sample_id,event,seq#,sql_idfrom v$active_session_historywhere session_id=&session_idand session_serial#=&session_serial#and event='&event'and seq#=&seq#;set line 200col session_id for 999999999999col session_serial for 9999999999col xid for 9999999999col program for a30col event for a30col blocking_session for 99999999select session_id,session_serial,xid,program,event,blocking_sessionfrom v$active_session_historywhere 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 nulland xid is not nullset line 200col session_id for 999999col session_serial# for 9999999col seq# for 99999999col event for a30col count(*) for 999999select inst_num, session_id,session_serial#,seq#,event,count(*)from dba_hist_active_sess_historywhere 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#,eventorder by count(*);set line 100col sample_id for 999999col inst_num for 99col blocking_session for 999999select sample_id,inst_num,blocking_session from dba_hist_active_sess_historywhere 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_numand session_id=&session_idand session_serial=&session_serialand seq#=&seq#set line 100col program for a30col inst_num for 99col sql_id for a30select program,inst_num,event,sql_idfrom dba_hist_active_sess_historywhere sample_id=&sample_idand inst_num=&inst_numand session_id=&session_idand 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_loadselect 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_loadfrom dba_hist_active_sess_historywhere inst_num=&inst_numand SESSION_ID=&session_idand session_serial#=&session_serial#set line 100col session_id for 99999999col session_serial# for 9999999col sql_id for a30col sql_exec_id for 9999999col count(*) for 99999999select session_id,session_serial#,sql_id,sql_exec_id,count(*)from dba_hist_active_sess_historygroup by session_id,session_serial#,sql_id,sql_exec_idorder by count(*);set line 100col opname for a30col plsql_entry_id for 999999col p1 for a30col p2 for a30col p3 for a30select opname,plsql_entry_id,p1,p2,p3from dba_hist_active_sess_historywhere instance_number=&instance_numberand session_id=&session_idand session_serial#=&session_serialset line 100col program for a30col inst_num for 99col blocking_session for 99999999col blocking_session_serial# for 99999999999col blocking_inst_id for 9999select program,inst_num,event,blocking_session,blocking_session_serial#,blocking_inst_id,sql_idfrom dba_hist_active_sess_historywhere inst_num=&inst_numand session_id=&session_idand session_serial#=&session_serial#select type,parameter from v$rowcache where cache#=10###############################V$session_longops,v$session#####查看进程剩余耗时#####################################set line 200col sid for 999999999999col SERIAL# for 99999999999col PROGRAM for a30col opname for a30col time_remaining for 99999999999999SELECT a.SID,a.SERIAL#,a.PROGRAM "程序进程",b.OPNAME "操作名称",b.TIME_REMAINING "剩余时间(秒)"FROM v$session a,v$session_longops bWHERE a.SID = b.SIDAND 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 processselect p.PID,p.SPID,s.SIDfrom v$process p,v$session swhere s.paddr = p.addrand s.sid = &SESSION_ID/select 'ospid: ' || p.spid || ' # ''' ||s.sid||','||s.serial#||''' '||  s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program linefrom v$session s , v$process pwhere p.addr = s.paddrand s.username <> ' ';connect / as sysdbaoradebug setospid 9834oradebug unlimitoradebug event 10046 trace name context forever,level 12oradebug tracefile_nameoradebug event 10046 trace name context offconnect / as sysdbaoradebug setorapid 9834oradebug unlimitoradebug event 10046 trace name context forever,level 12oradebug tracefile_nameoradebug event 10046 trace name context off-- 10053 trace####################################################v$SQLAREA####################################################################yyyy-mm-dd hh34:mi:ssset line 200col sql_id for a30col last_active_time for a30col first_load_timecol last_load_timecol executionscol buffer_getscol user_io_wait_timecol cpu_timecol elapsed_timecol physical_read_requestscol physical_read_bytesselect     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_bytesfrom v$SQLAREAorder 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,enameFROM    vWHERE   ename='SCOTT';SELECT rownum,empno,enameFROM    empWHERE   ename='SCOTT';3.比较数据集不同select v.empno,v.ename ,b.empno,b.enamefrom vfull joinemp b on(v.empno=b.empno)where (v.empno is null or b.empno is null);####################################################统计信息######################################dba_tab_statisticsdba_tab_col_statisticsset 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 a10col table_name for a20col column_name for a30col high_value for a30col low_value for a30col num_nulls for 9999col histogram for a30select owner,table_name,column_name,high_value,low_value,num_nulls,histogramfrom dba_tab_col_statisticswhere table_name=''and column_name=''and owner='';set line 200col owner for a20col table_name for a20col num_rows for 99999col partition_name for a20col LAST_ANALYZED for a30col blocks for 99999col empty_blocks for 99999select owner,table_name,num_rows,partition_name,LAST_ANALYZED,blocks,empty_blocksfrom dba_tab_statisticswhere owner=''and table_name='';-- -- -- -- -- -- -- -- -- -- 索引统计系信息-- -- -- -- -- -- -- -- 查看索引的聚簇因子，-- 聚簇因子应该与表的块数近似set line 100col ower for a10col table_name for a20col index_name for a20col clustering_factor for 999999999col NUM_ROWS for 99999select 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 offcol low_value for a30col high_value for a30col num_distinct for 99999col num_nulls for 9999999select 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   0var l varchar2(100);exec dbms_stats.convert_row_value('5F75746C245F6C6E635F696E645F7061727473',:l);print l;#######################V$sqlarea########################################set line 200col sql_id for a30col plan_hash_value for 30col executions for 99999col first_load_time for a30col disk_reads for 99999col buffer_gets for 9999999col cpu_time for 9999999999col last_load_time for 999999999col last_active_time for 99999999col loads for 999999select sql_id,plan_hash_value,executions,first_load_time,disk_reads,buffer_gets,cpu_time,last_load_time,last_active_time,loadsfrom v$sqlareawhere sql_id='<>';###################################################################查询隐含参数SELECT   KSPPINM, KSPPSTVL, KSPPDESCFROM   X$KSPPI X, X$KSPPCV YWHERE   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 100col table_name for a30col num_rows for 999999col blocks for 9999999col avg_row_len for 99999999col partitioned for a10col ext_M for 9999999select table_name,num_rows ,--记录数blocks,avg_row_len,partitioned, --是否分区num_rows * avg_row_len /1024/1024/1024/0.9 est_M  -- "数据量大小"from dba_tableswhere table_name='&table_name';set line 100col partitioning_type for a10col subpartitioning_type for a10select partitioning_type, --分区类型subpartitioning_type from dba_part_tables where table_name='&table_name'set line 100col owner for a10col name for a20col object_type for a30col column_name for a30 --分区列col column_position for a30select * from dba_part_key_columns where name ='&table_name';#############SPM########################在当前session中禁掉spm并同时开启自动捕获sql plan baselinealter 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 100select *  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 mountalter 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 a40col STAT_NAME for a40col per_sec for 99999WITH 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 200col name for a60select 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;###############################################asmsqlplus / as sysasmselect * from v$asm_operation;  <==> $asmcmd lsopasmcmd rebal --power N   -- 修改磁盘组reblance的并行度asmcmd  lsdg#################################################----------------------------------我是分割线 更新时间 2019年1月24日 --------------------###########################转载于网络######################查看sga，pga等使用率set line 100select 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)unionselect 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)  unionselect 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)unionselect 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)unionselect 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)unionselect 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)unionselect 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)unionselect 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)unionselect 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 100select 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)unionselect 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$transactionXIDUSN 表示当前事务使用的回滚段的编号XIDSLOT 表示该事物在回滚段头部的事务表中对应的记录编号XIDSQN 说明序列号STATUS 说明该事物是否为活动的select XIDUSN,XIDSLOT,XIDSQN,STATUS from v$transaction;####-------------------------------------------v$lock记录session已经获得的锁定以及正在请求的锁定的信息     SID 说明session的idTYPE 锁定级别，主要关注TX和TMLMODE 已经获得的锁定的模式，以数字编码表示0 - none1 - null (NULL)2 - row-S (SS)3 - row-X (SX)4 - share (S)5 - S/Row-X (SSX)6 - exclusive (X)REQUEST 正在请求的锁定的模式，以数字编码表示0 - none1 - 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 100select 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,blockfrom v$lockwhere 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表格中