客户单位部署了2套logic dg ，考虑到可能会查询，转载备用。

1.日志应用的启动和关闭

ALTER DATABASE STOP LOGICAL STANDBY APPLY; ---停止应用，等待事务完成
ALTER DATABASE ABORT LOGICAL STANDBY APPLY;--不等待事务完成就停止
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; ---实时
ALTER DATABASE START LOGICAL STANDBY APPLY; --非实时
ALTER DATABASE START LOGICAL STANDBY APPLY  IMMEDIATE SKIP FAILED TRANSACTION; --实时应用并跳过失败的事务

如何知道是否开启了实时应用呢？可以查询V$LOGSTDBY_STATE视图或查询是否有lsp进程。

SQL> SELECT * FROM V$LOGSTDBY_STATE;
PRIMARY_DBID SESSION_ID REALTIME_APPLY      STATE
------------ ---------- ------------------  -----------------------------------------
  1480747539          1 Y                   APPLYING
[oracle@rhel6_lhr oraljdg]$ ps -ef|grep -i ora_lsp
6oracle   20450     1  0 15:22 ?        00:00:00 ora_lsp0_oraljdg

2.查看日志文件的应用情况

 COLUMN DICT_BEGIN FORMAT A15;
 COLUMN FILE_NAME FORMAT A50;
 SET NUMF 9999999;
 COL FCHANGE# FORMAT 9999999999999;
 COL NCHANGE# FOR 999999999999999999999;
 SET LINE 200
 SELECT  FILE_NAME, SEQUENCE# AS SEQ#, FIRST_CHANGE# AS FCHANGE#,
         NEXT_CHANGE# AS NCHANGE#, TIMESTAMP, DICT_BEGIN AS BEG,
         DICT_END AS END, THREAD# AS THR#, APPLIED
    FROM DBA_LOGSTDBY_LOG
ORDER BY THREAD#,SEQUENCE#;

SET LINE 9999 PAGESIZE 9999
COL FILE_NAME FORMAT A120
SELECT THREAD#,SEQUENCE#, FILE_NAME, APPLIED, TIMESTAMP 
FROM DBA_LOGSTDBY_LOG D 
WHERE D.SEQUENCE# >=(SELECT MAX(SEQUENCE#)-3 FROM  DBA_LOGSTDBY_LOG NB WHERE  NB.THREAD#=D.THREAD# AND NB.APPLIED='YES' ) 
ORDER BY THREAD#,D.SEQUENCE#; 

3.查看备库SQL Apply的进度

SQL> SELECT LATEST_SCN,MINING_SCN,APPLIED_SCN,LATEST_TIME,MINING_TIME,APPLIED_TIME FROM V$LOGSTDBY_PROGRESS;
LATEST_SCN MINING_SCN APPLIED_SCN LATEST_TIME         MINING_TIME         APPLIED_TIME
---------- ---------- ----------- ------------------- ------------------- -------------------
8895794846 8895316681  8895316680 2010-05-18 16:27:08 2010-05-18 16:03:54 2010-05-18 16:03:54

4.查看备库是否有任何DDL/DML语句未成功应用

 COL EVENT_TIMESTAMP FORMAT A30
 COL EVENT FORMAT A40
 COL EVENT_STATUS FORMAT A80
 SELECT A.EVENT_TIME,
        A.CURRENT_SCN,
        A.COMMIT_SCN,
        XIDUSN,
        XIDSLT,
        XIDSQN,
       TO_CHAR(EVENT) EVENT,
       A.STATUS_CODE,
       STATUS EVENT_STATUS
  FROM DBA_LOGSTDBY_EVENTS A
 WHERE A.EVENT_TIME >= SYSDATE - 10 / 1660
 ORDER BY A.EVENT_TIME ;

5.查看备库SQL Apply的状态

COL REALTIME_APPLY FORMAT A15
COL STATE FORMAT A20
SELECT * FROM V$LOGSTDBY_STATE;
PRIMARY_DBID SESSION_ID REALTIME_APPLY       STATE
------------ ---------- --------------- ---------------
262089084          1                 Y         APPLYING

注意STATE列，该列可能有下述的几种状态：

 INITIALIZING：LogMiner SESSION已创建并初始化

 LOADING DICTIONARY：SQL应用调用LogMiner字典

 WAITING ON GAP：SQL应用正在等待日志文件，可能有中断

 APPLYING：SQL应用正在工作

 WAITING FOR DICTIONARY LOGS：SQL应用正在等待LogMiner字典信息

 IDLE：SQL应用工作非常出色，处于空闲状态

 SQL APPLY NOT ON：没有开启应用

6.取消部分对象或事务的同步

可以利用DBMS_LOGSTDBY.SKIP存储过程跳过特定表或特定用户的DML事务或部分DDL语句。这些跳过的对象或事务可以通过视图DBA_LOGSTDBY_SKIP和DBA_LOGSTDBY_SKIP_TRANSACTION查看。

 EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'VIEW');
 EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'PROFILE');
 EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'DATABASE LINK');
 EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'CREATE VIEW');
 EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'DROP VIEW');
 EXECUTE DBMS_LOGSTDBY.SKIP(STMT=>'SCHEMA_DDL', SCHEMA_NAME=>'%', OBJECT_NAME=>'%', PROC_NAME=>NULL);
 EXECUTE DBMS_LOGSTDBY.SKIP(STMT=>'SCHEMA_DDL', SCHEMA_NAME=>'LHR', OBJECT_NAME=>'%', PROC_NAME=>NULL);
 EXECUTE DBMS_LOGSTDBY.SKIP(STMT=>'SCHEMA_DDL', SCHEMA_NAME=>'MDSYS', OBJECT_NAME=>'%', PROC_NAME=>NULL);



EXEC DBMS_LOGSTDBY.SKIP_TRANSACTION (3, 3, 827); --(XIDUSN = 3, XIDSLT = 3, XIDSQN = 827)
SELECT EVENT, STATUS,'EXEC DBMS_LOGSTDBY.SKIP_TRANSACTION ('||XIDUSN||', '||XIDSLT||', '||XIDSQN||');' FROM DBA_LOGSTDBY_EVENTS A WHERE XIDUSN IS NOT NULL AND   A.EVENT_TIME >= SYSDATE - 60 / 1660;
SELECT 'EXEC DBMS_LOGSTDBY.SKIP_TRANSACTION ('||XIDUSN||', '||XIDSLT||', '||XIDSQN||');' FROM DBA_LOGSTDBY_EVENTS A WHERE XIDUSN IS NOT NULL AND   A.EVENT_TIME >= SYSDATE - 10 / 1660;

SELECT * FROM DBA_LOGSTDBY_SKIP;
SELECT * FROM DBA_LOGSTDBY_SKIP_TRANSACTION;

7.增加apply进程个数

如果Apply进程过于繁忙，那么可以增加Apply进程个数。以下命令调整为20，默认为5个:

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('APPLY_SERVERS',20);
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

8.处理从主库接收到的归档文件

逻辑DG在应用完归档日志后会自动删除该归档文件，这一特性是由逻辑DG中的2个参数控制的，它们分别为LOG_AUTO_DELETE和LOG_AUTO_DEL_RETENTION_TARGET。

LOG_AUTO_DELETE的值默认为TRUE，表示逻辑DG在应用完归档日志后会自动删除该归档文件，默认24小时之后删除（由参数LOG_AUTO_DEL_RETENTION_TARGET控制）。如果希望禁用自动删除的功能，那么可以执行下列语句：

EXECUTE DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', FALSE);

在告警日志中会有类似如下的记录：

Fri Jul 27 13:48:53 2018
LOGMINER: Log Auto Delete - deleting: /u01/app/oracle/flash_recovery_area/ORADGLG/1_202_886695024.dbf
Deleted file /u01/app/oracle/flash_recovery_area/ORADGLG/1_202_886695024.dbf
在某些情况下确实需要禁用归档文件的自动删除功能，例如逻辑DG需要执行Flashback Database操作，如果你想恢复到之前的某个时间点，然后再接着应用，那么就必须要有该时间点后对应的归档。假如LOG_AUTO_DELETE为TRUE的话，应用过的归档已经被删除，想回都回不去。

参数LOG_AUTO_DEL_RETENTION_TARGET表示逻辑DG在应用完归档日志后的多长时间之后再自动删除该归档文件。该参数仅在LOG_AUTO_DELETE设置为TRUE之后才起作用，默认值为1440分钟，即24小时，可以通过以下命令修改该值的大小：

1exec DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DEL_RETENTION_TARGET', 1);
以上命令表示归档日志被应用完之后，再过1分钟才会自动删除该归档日志。需要注意的是，这些设置仅适用于从主库传递过来的归档文件归档到的位置不是闪回恢复区。如果正在使用闪回恢复区，那么这些从主库传递过来的归档文件将不再根据参数LOG_AUTO_DELETE和LOG_AUTO_DEL_RETENTION_TARGET的值做处理。

如果禁止了逻辑DG归档文件的自动删除功能，那么一定要有相应的其他解决方案，不能说取消了自动删除功能，之后逻辑Standby数据库接收到的Standby归档文件就不再管它，这肯定会产生问题，最起码要考虑到逻辑Standby数据库的存储空间是有限的。

逻辑Standby数据库接收到的归档文件并不会显示在V$ARCHIVED_LOG视图中，因此以为通过RMAN中的配置自动删除这些文件的希望也是会落空的。对于这类文件的删除，正确的删除方法通常会按照如下步骤操作：

首先执行DBMS_LOGSTDBY.PURGE_SESSION，该过程会检查当前所有接收到的归档日志文件，对于那些已经应用过，不再需要（这里是当前不再需求，未来是否有可能需要就得由DBA来决定了）的文件进行标记，例如：

EXECUTE DBMS_LOGSTDBY.PURGE_SESSION;

然后，查询数据字典DBA_LOGMNR_PURGED_LOG，所有被DBMS_LOGSTDBY. PURGE_SESSION标记不再需要的日志都会记录在这里，例如：

SELECT * FROM DBA_LOGMNR_PURGED_LOG;

该字典只有一列，即归档文件的实际路径。最后根据显示的路径找到这些文件，然后在操作系统中删除即可。

9.调整PREPARER（调制机）的进程数

如果备库上有很多事务在等待Apply，但是还有空闲的Applier进程，且已经没有idle状态的PREPARER（调制机）进程，这时需要增加PREPARER的进程数。以下命令调整为4个，默认为1个:

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('PREPARE_SERVERS',4);
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

10.调整MAX_SGA，防止Paged out

通过以下SQL可以查询到是否发生了Paged out：

SQL>select value bytes from v$logstdby_stats where name='bytes paged out';

如果以上查询结果在增长，那么查看当前MAX_SGA的大小:

SQL>select value from v$logstdby_stats where name like 'maximum SGA for LCR cache%';

VALUE
---------------------------------------------------------------
30

可以增大MAX_SGA:

SQL>alter database stop logical standby apply;
SQL>execute dbms_logstdby.apply_set('MAX_SGA',1000);
SQL>alter database start logical standby apply immediate;

逻辑备库需要将Redo记录解析成LCR（Logical Change Records），会在Shared Pool里分配一部分空间来作为LCR Cache，如果Cache太小，就会像OS的虚拟内存管理一样，需要做page out，这会严重影响应用日志的性能。在默认情况下，LCR Cache为Shared Pool的四分之一，最少不少于30M（默认为30M，最大可以设置到4096M），否则SQL Apply不能启动。如果机器的内存足够，建议将LCR Cache尽量设大一点，当然，同时Share Pool也要足够大。如果机器内存有限，那么可以考虑将Buffer Cache减少一点来给LCR Cache腾出空间。

11.调整事务应用方式

默认情况下逻辑Standby端事务应用顺序与Primary端提交顺序相同。如果希望逻辑Standby端的事务应用不要按照顺序的话，那么可以按照下列的步骤操作：

①停止SQL应用:

SQL>ALTER DATABASE STOP LOGICAL STANDBYAPPLY;

②允许事务不按照Primary的提交顺序应用：

SQL>EXECUTE DBMS_LOGSTDBY.APPLY_SET('PRESERVE_COMMIT_ORDER','FALSE');

③重新启动SQL应用

SQL>ALTER DATABASE START LOGICAL STANDBYAPPLY IMMEDIATE;

恢复逻辑Standby按照事务提交顺序应用的话，按照下列步骤：

①还是先停止SQL应用：

SQL>ALTER DATABASE STOP LOGICAL STANDBYAPPLY;

②重置参数PRESERVE_COMMIT_ORDER的初始值：

SQL>EXECUTE DBMS_LOGSTDBY.APPLY_UNSET('PRESERVE_COMMIT_ORDER');

③重新启动SQL应用：

SQL>ALTER DATABASE START LOGICAL STANDBYAPPLY IMMEDIATE;

————————————————
版权声明：本文为CSDN博主「小麦苗DBA宝典」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/lihuarongaini/article/details/104586179

execute dbms_logstdby.skip(stmt => 'DML',schema_name => '%', object_name => '%');

stmt的取值可以是：
http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10802/d_lsbydb.htm#997290
// 跳过的内容记载在下面

select * from dba_logstdby_skip

// 停止apply

alter database stop logical standby apply;
alter database abort logical standby apply;

// 执行apply

alter database start logical standby apply;

// 实时apply

alter database start logical standby apply immediate;

// 跳过错误，在dba_logstdby_skip表中，ERROR列为Y

execute dbms_logstdby.skip_error('NON_SCHEMA_DDL');

// 执行apply，跳过失败的事务

alter database start logical standby apply skip failed transaction;

// 设置参数，是否记录跳过错误

exec dbms_logstdby.apply_set('RECORD_SKIP_ERRORS','FALSE');

// 设置参数，是否记录跳过DDL

exec dbms_logstdby.apply_set('RECORD_SKIP_DDL','FALSE');

// 在备库上关掉dataguard，备库可写

alter database guard none;

// 在备库上启用dataguard，备库不可写

alter database guard all;

// 官方文档
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10823/toc.htm

//执行某个表不通过，手工同步表

alter database stop logical standby apply;

// 创建DBLINK指向主库，然后同步创建表

exec dbms_logstdby.instantiate_table('EYGLE','SALES','dblink_name');

alter database start logical standby apply;

// 手工添加备库的日志

$ cp /u01/arch/WENDING/1_22751_666200636.arc /u04/arch/WDSTD/log_1_22751_666200636.arc

SQL> alter database register logical logfile '/u04/arch/WDSTD/log_1_22751_666200636.arc';
or
SQL> alter database register or replace logical logfile '/u04/arch/WDSTD/log_1_22751_666200636.arc';

//查看最后的进度

select LATEST_SCN,MINING_SCN,APPLIED_SCN,LATEST_TIME,MINING_TIME,APPLIED_TIME from V$LOGSTDBY_PROGRESS;

// 监控同步进度的脚本

SELECT * FROM dba_logstdby_log;
select * from dba_logstdby_events order by event_time desc;
select LATEST_SCN,MINING_SCN,APPLIED_SCN,LATEST_TIME,MINING_TIME,APPLIED_TIME from V$LOGSTDBY_PROGRESS;
select LOGSTDBY_ID,type,status process_status from v$logstdby_process;
select * from v$logstdby_state;
select * from v$dataguard_status order by timestamp desc;

// 增加apply的进程数

ALTER DATABASE STOP LOGICAL STANDBY APPLY; --- Stop SQL Apply
EXECUTE DBMS_LOGSTDBY.APPLY_SET('APPLY_SERVERS', 20); --- 调整apply进程数为20，默认为5个
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; --- Start real-time Apply

// 停止apply时，如果当前正在应用，会等待执行后才停止
// 下面的命令可以重复执行，如果执行提示stop，则意味着正在apply还没有结束，等结束后重新执行即可
ALTER DATABASE START LOGICAL STANDBY APPLY;
————————————————
版权声明：本文为CSDN博主「wonder4」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/wonder4/article/details/5630658

系统上线前，发现有业务用户将表建到了system的系统表空间用户下，建到了非默认表空间下。甚至有发现把集群的数据文件创建到了本地目录中的情况。
因此，业务系统上线前，应该首先检查数据库的权限是否合理，是否有不按规划的情况，及时治理，然后再进行清库和导入数据。防止留下隐患。

查看Oracle用户的权限或角色

一、查看用户

1.查看所有用户：

select * from dba_users;
select * from all_users;
select * from user_users;    //查看当前用户

二、查看角色

1.当前用户被激活的全部角色

  select * from session_roles;

2.当前当前用户被授予的角色

  select * from user_role_privs;

3.全部用户被授予的角色

  select * from dba_role_privs;

4、查看某个用户所拥有的角色

select * from dba_role_privs where grantee='用户名';

5、查看某个角色所拥有的权限

select * from dba_sys_privs where grantee='CONNECT';

6.查看所有角色

  select * from dba_roles;

三、查看权限

1.基本权限查询：

select * from session_privs; --当前用户所拥有的全部权限
select * from user_sys_privs;--当前用户的系统权限
select * from user_tab_privs;--当前用户的对象权限
select * from dba_sys_privs ;--查询某个用户所拥有的系统权限
select * from role_sys_privs;--查看角色(只能查看登陆用户拥有的角色)所包含的权限

2. 查看用户的系统权限(直接赋值给用户或角色的系统权限)

   select * from dba_sys_privs;

   select * from user_sys_privs;

2.查看用户的对象权限：

 select * from dba_tab_privs;
 select * from all_tab_privs;
 select * from user_tab_privs;

3.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)

  select * from v$pwfile_users;

扩展

1、以下语句可以查看Oracle提供的系统权限

select name from sys.system_privilege_map

2、查看一个用户的所有系统权限(包含角色的系统权限)

select privilege from dba_sys_privs where grantee='SCOTT'  
union  
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='SCOTT' ); 

3、 查询当前用户可以访问的所有数据字典视图。

select * from dict where comments like '%grant%';   

4、显示当前数据库的全称

select * from global_name;   

问题 1：如何查询一个角色包括的权限？
a.一个角色包含的系统权限

select * from dba_sys_privs where grantee='角色名'  

 select * from dba_sya_privs where grantee='COONNECT'; connect要大写
     另外也可以这样查看：   
 select * from role_sys_privs where role='角色名'   

b.一个角色包含的对象权限

 select * from dba_tab_privs where grantee='角色名'   

问题 2：Oracle究竟有多少种角色？

select * from dba_roles; 

问题 3：如何查看某个用户，具有什么样的角色？

select * from dba_role_privs where grantee='用户名'  

问题4：查看哪些用户具有DBA的角色

select grantee from dba_role_privs where granted_role='DBA';

转载http://blog.itpub.net/31015730/viewspace-2150322/

数据库锁表分析

1、会话1 update Scott.emp set deptno=10;
2、会话2 update scott.emp set deptno=30;

查询是否存在锁定

select s1.INST_ID,
       s1.username || '@' || s1.machine || ' (SID=' || s1.sid ||
       ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' ||
       s2.sid || ' ) ' AS blocking_status
  from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
 where l1.INST_ID = s1.INST_ID
   and s1.INST_ID = s2.INST_ID
   and s2.INST_ID = l2.INST_ID
   and s1.sid = l1.sid
   and s2.sid = l2.sid
   and l1.BLOCK = 1
   and l2.request > 0
   and l1.id1 = l2.id1
   and l2.id2 = l2.id2;

19:22:53 SQL> select s1.INST_ID,
19:24:50   2         s1.username || '@' || s1.machine || ' (SID=' || s1.sid ||
19:24:50   3         ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' ||
19:24:50   4         s2.sid || ' ) ' AS blocking_status
19:24:50   5    from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
19:24:50   6   where l1.INST_ID = s1.INST_ID
19:24:50   7     and s1.INST_ID = s2.INST_ID
19:24:50   8     and s2.INST_ID = l2.INST_ID
19:24:50   9     and s1.sid = l1.sid
19:24:50  10     and s2.sid = l2.sid
19:24:50  11     and l1.BLOCK = 1
19:24:50  12     and l2.request > 0
19:24:50  13     and l1.id1 = l2.id1
19:24:50  14     and l2.id2 = l2.id2;

   INST_ID
----------
BLOCKING_STATUS
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1
SYS@localhost.localdomain (SID=16 ) is blocking SYS@WORKGROUP\LAPTOP-5RF2B7J0 ( SID=15 )

查询update后未提交的会话

SELECT
    A.SID,
    A.SERIAL#,
    A.USERNAME,
    A.EVENT,
    A.WAIT_CLASS,
    A.SECONDS_IN_WAIT,
    A.PREV_EXEC_START,
    B.LOCKED_MODE,
    C.OWNER,
    C.OBJECT_NAME,
    C.OBJECT_TYPE 
FROM
    V$SESSION A
    INNER JOIN V$LOCKED_OBJECT B ON A.SID = B.SESSION_ID
    INNER JOIN DBA_OBJECTS C ON B.OBJECT_ID = C.OBJECT_ID 
WHERE
    A.WAIT_CLASS = 'Idle'
    AND A.SECONDS_IN_WAIT >10

/SESSION空闲后一段时间还锁定的才算有问题,这里随便给了个数值10秒/

19:27:05 SQL> SELECT
19:27:07   2    A.SID,
19:27:07   3    A.SERIAL#,
19:27:07   4    A.USERNAME,
19:27:07   5    A.EVENT,
19:27:07   6    A.WAIT_CLASS,
19:27:07   7    A.SECONDS_IN_WAIT,
19:27:07   8    A.PREV_EXEC_START,
19:27:07   9    B.LOCKED_MODE,
19:27:07  10    C.OWNER,
19:27:07  11    C.OBJECT_NAME,
19:27:07  12    C.OBJECT_TYPE 
19:27:07  13  FROM
19:27:07  14    V$SESSION A
19:27:07  15    INNER JOIN V$LOCKED_OBJECT B ON A.SID = B.SESSION_ID
19:27:08  16    INNER JOIN DBA_OBJECTS C ON B.OBJECT_ID = C.OBJECT_ID 
19:27:08  17  WHERE
19:27:08  18    A.WAIT_CLASS = 'Idle'
19:27:08  19    AND A.SECONDS_IN_WAIT >10;

   SID    SERIAL# USERNAME             EVENT                                         WAIT_CLASS                                                       SECONDS_IN_WAIT PREV_EXEC_START     LOCKED_MODE OWNER     OBJECT_NAME                    OBJECT_TYPE
------ ---------- -------------------- --------------------------------------------- ---------------------------------------------------------------- --------------- ------------------- ----------- ---------- ------------------------------ -------------------
    16         61 SYS                  SQL*Net message from client                   Idle                                                                          38 2021-12-15 19:26:31           3 SCOTT     DEPT                           TABLE
    16         61 SYS                  SQL*Net message from client                   Idle                                                                          38 2021-12-15 19:26:31           3 SCOTT     EMP                            TABLE

Elapsed: 00:00:00.06

----------查看被锁对象
set lin 400 pages 49999
col USERNAME for a15
col owner for a15
col OBJECT_NAME for a35
col PROGRAM for a35
col PROCESS for a15
select a.LOCKED_MODE,a.inst_id,b.owner,b.object_name,a.object_id,a.session_id,c.serial#,c.username,c.sql_id,c.PROCESS ,c.PROGRAM
from gv$locked_object  a, dba_objects b,gv$session c
 where a.object_id=b.object_id and c.sid=a.session_id;

19:32:51 SQL> set lin 400 pages 49999
19:33:07 SQL> col USERNAME for a15
19:33:07 SQL> col owner for a15
19:33:07 SQL> col OBJECT_NAME for a35
19:33:07 SQL> col PROGRAM for a35
19:33:07 SQL> col PROCESS for a15
19:33:07 SQL> select a.LOCKED_MODE,a.inst_id,b.owner,b.object_name,a.object_id,a.session_id,c.serial#,c.username,c.sql_id,c.PROCESS ,c.PROGRAM
19:33:07   2  from gv$locked_object  a, dba_objects b,gv$session c
19:33:07   3   where a.object_id=b.object_id and c.sid=a.session_id;

LOCKED_MODE    INST_ID OWNER           OBJECT_NAME                          OBJECT_ID SESSION_ID    SERIAL# USERNAME        SQL_ID             PROCESS         PROGRAM
----------- ---------- --------------- ----------------------------------- ---------- ---------- ---------- --------------- ------------------ --------------- -----------------------------------
          3          1 SCOTT           DEPT                                     87106         15          9 SYS             022scd5v03tnp      23708:31696     navicat.exe
          3          1 SCOTT           DEPT                                     87106         16         61 SYS                                19093           sqlplus@localhost.localdomain (TNS
                                                                                                                                                               V1-V3)

alter system kill session 'sid,serial#'

操作背景：

压力测试期间，发现一个进程未执行任何sql，在V$SQL中未找到sql_id,因此无法判断阻塞原因，需要通过跟踪进程进行分析
操作如下：
1、查询该阻塞进程的spid

SQL> select spid from v$process where addr in (select paddr from v$session where sid='15');

SPID
------------------------
3417

2、oracle debug

SQL>oradebug set ospid 93773
SQL>oradebug event 10046 trace name context forever ,level 12
SQL>oradebug tracefile_name

SQL>oradebug close_trace

3、操作系统跟踪

strace -o /tmp/93773 -p 93773
tail -f /tmp/93773

经过跟踪，发现进程无任何信息打印，故认为该进程已经异常，且通过v$sql无法查到产生阻塞的语句，最后联系负责该应用的工程师，重启应用后，该阻塞被释放。 如果重启依然未被释放，则在数据库层面杀掉该会话。

备注：
当应用端认为没有未提交的会话，且数据库端查找不到指定的语句，可以创建新的profiles，并设置IDLE_TIME的值为15-30（分钟），设置resource_limit=true，自动断开inactive的会话，配合sqlnet.ora中的SQLNET.EXPIRE_TIME=10 （分钟）防止未提交的空闲会话阻塞数据库的其他会话。

索引的优化和维护
目的：
通过对数据库中的索引进行优化和维护，提交检索的速度和效率。
原理：
随着数据库的使用，不可避免地对基本表进行插入，更新和删除，这样导致叶子行在索引中被删除，使该索引产生碎片。插入删除越频繁的表，索引碎片的程度也越高。碎片的产生使访问和使用该索引的I/O成本增加。碎片较高的索引必须重建以保持最佳性能。当索引的层数增大时，I/O的成本增加，检索的效率开始降低，oracle建议当索引的层数大于3时，则应当对此索引进行重建以提交效率。随着表记录的增加，相应的索引也要增加。如果一个索引的next值设置不合理(太小)，索引段的扩展变得很频繁。索引的extent太多，检索时的速度和效率就会降低。当然还有就是由于一些人为的原因或者系统表的迁移，有可能造成索引的失效，也会降低检索的效率和速度。
通过对索引进行分析，找出碎片比例占索引20%以上的索引；通过查询和索引相关的数据字典和表找出失效的索引、层数大于3的索引和被扩展超过10次的索引；将这些问题索引数据统计到临时表。分别对这些索引进行重建或重新设置 next值（尽量增大，到合理的数值），从而达到优化索引和提高数据库效率的目的。
脚本：
DECLARE

   v_index_name varchar2(100);
   v_analyze_str varchar2(300);
   height        number;
   lf_rows       number;
   del_lf_rows   number;
   v_ex_count    number;
   v_idx_owner   varchar2(30);
   v_tab_name    varchar2(50);
   v_tabspa_name varchar2(50);      
   v_idx_status  varchar2(10);      
   CURSOR analyze_index IS
   SELECT index_name,table_owner,table_name,tablespace_name,status
   FROM user_indexes; 

BEGIN

   OPEN analyze_index;
   FETCH  analyze_index INTO

v_index_name,v_idx_owner,v_tab_name,v_tabspa_name,v_idx_status;

   WHILE analyze_index%FOUND LOOP 
   if  v_idx_status ='INVALID' then
   insert into lifemenu.index_stats_prob
   values(v_index_name,null,null,null,

‘该索引已失效’,v_idx_owner,v_tab_name,v_tabspa_name,v_idx_status);

   end if;
   if v_idx_status ='VALID' then
   v_analyze_str := 'analyze index '||v_index_name||' validate structure';
   EXECUTE IMMEDIATE v_analyze_str;       
   SELECT HEIGHT,DECODE(LF_ROWS,0,1,LF_ROWS),DEL_LF_ROWS
   INTO   height,lf_rows,del_lf_rows
   FROM   index_stats;
   if (del_lf_rows/lf_rows)>0.2 then
   insert into lifemenu.index_stats_prob
   values(v_index_name,del_lf_rows/lf_rows,null,v_ex_count,'该索引的碎片太多，建议重建',v_idx_owner,v_tab_name,v_tabspa_name,v_idx_status);
   end if;
   if height>=4 and (del_lf_rows/lf_rows) <=0.2 then
   insert into lifemenu.index_stats_prob
   values(v_index_name,null,height,v_ex_count,'该索引的层数超过3，建议重建',v_idx_owner,v_tab_name,v_tabspa_name,v_idx_status);
   end if;
   select count(*)
   into v_ex_count
   from user_extents
   where segment_name =v_index_name;     
   if v_ex_count>=11 and (del_lf_rows/lf_rows)<=0.2  and height<4 then
   insert into lifemenu.index_stats_prob
   values(v_index_name,null,null,v_ex_count,'该索引扩展超过10次，建议重建时增大参数next',v_idx_owner,v_tab_name,v_tabspa_name,v_idx_status);       
   end if;
   end if;
   commit;
   v_index_name :='';
   v_analyze_str :='';
   v_idx_status :='';

FETCH analyze_index INTO
v_index_name,v_idx_owner,v_tab_name,v_tabspa_name,v_idx_status;

   END LOOP;
   CLOSE analyze_index;

END;
执行结果：
执行结果存储在表index_stats_prob中，表的结构和说明如下：
create table INDEX_STATS_PROB
(
INDEX_NAME VARCHAR2(30) not null,//索引名称
FRAG_PCT NUMBER, //碎片比例
HEIGHT NUMBER, //索引层数
EXTENT_TIME NUMBER, //扩展次数
COM VARCHAR2(50), //建议处理方法
OWNER VARCHAR2(30) not null,//所属用户
TABLE_NAME VARCHAR2(50), //所属表
TABLESPACE_NAME VARCHAR2(50), //所在表空间
STATUS VARCHAR2(10) //状态
)
其中问题索引主要就有四类：
1 碎片比例大于20%的
2 索引层数大于3的且不属于第一类的
3 索引扩展次数超过10的且不属于第一类和第二类的
4 失效的索引
之所以这样处理，是因为碎片比较多的问题索引必须进行处理，其次是层数多的索引，再其次是扩展次数比较多的索引。
在测试数据库ld05sh中的执行结果中，没有发现第二类和第四类的问题索引。
下列是分别在各个用户下执行脚本所耗费的时间的列表：
用户 脚本执行时间
ACCTMAN 1115秒
LIFEBASE 2576秒
LIFEDATA 22633秒
LIFELOG 1741秒
LIFEMAN 143秒
LIFEREPT 1836秒
REINSMAN 1475秒

处理建议：
1． 对于第一类和第二类问题索引，即碎片比例大于20%的问题索引和索引层数大于3的且不属于第一类的，都建议进行重建。可以执行下列语句来找出这些索引。同时我们对这两类问题索引也提供了它们扩展的次数，因此如果发现它们被扩展的次数过大，那么在重建的时候也要注意增大索引的next参数。
SELECT *
FROM INDEX_STATS_PROB
WHERE FRAG_PCT IS NOT NULL OR HEIGHT IS NOT NULL
ORDER BY FRAG_PCT DESC;
重建语句如下：
alter index 用户名.索引名 rebuild
tablespace 表空间名
storage(initial 初始值 next 扩展值)
nologging
如果出于空间或其他考虑，不能重建索引，可以整理索引：
alter index用户名.索引名 coalesce
2． 对于第三类索引，由于发现在ld05sh中此类索引特别多，所以建议重建扩展次数在50次以上的索引，当然如果在其他环境中发现这类索引的数量不是很多，需要综合考虑，按照扩展次数的多少来确定重建的优先次序，即扩展次数越多的，越优先考虑重建，重建时主要是增大索引的next参数。
执行下列语句来找出这些索引：
SELECT *
FROM INDEX_STATS_PROB
WHERE FRAG_PCT IS NULL AND HEIGHT IS NULL AND EXTENT_TIME>10
ORDER BY EXTENT_TIME DESC;
重建语句相同，注意重建时要增大next 参数：
alter index 用户名.索引名 rebuild
tablespace 表空间名
storage(initial 初始值 next 扩展值)
nologging
3．重建索引后，原有的索引被删除，这样会造成表空间的碎片。
整理表空间的碎片　alter tablespace 表空间名 coalesce

4. 另外，优化索引还有一些普遍的原则，如：
   1）定期对数据更新（主要是删除）频繁的表重建索引
   2）建议索引不要建立在系统表空间内
   3）建议索引建立在db_block_size比较大的表空间中
   4）记录太少的表，应当少建或不建索引；经常处理的业务表（插入、删除、修改），应在查询允许的情况下尽量减少索引。

具体操作：
登录要分析的数据库
在lifemenu用户下创建存放分析数据的表index_stats_prob，脚本如下：
create table INDEX_STATS_PROB
(
INDEX_NAME VARCHAR2(30) not null,
FRAG_PCT NUMBER,
HEIGHT NUMBER,
EXTENT_TIME NUMBER,
COM VARCHAR2(50),
OWNER VARCHAR2(30) not null,
TABLE_NAME VARCHAR2(50),
TABLESPACE_NAME VARCHAR2(50),
STATUS VARCHAR2(10)
)
将此表授权给其他的用户：
grant all on index_stats_prob to 用户名
用其他用户登录要进行索引优化的数据库，执行PL/SQL脚本，然后参照处理建议对索引进行重建，从而达到优化索引，提高效率的目的。
各个用户下的执行时间都不同，具体可以参照执行结果中的脚本耗费时间来设计脚本执行计划。
验证说明：
重建索引后，检索的速度和效率都会得到提高，用户可以通过前后的检索所需时间的比较来验证。

建议：
1．首先需要肯定的一点，该文章总结的非常好，基本上考虑到了在实际使用过程中对索引维护尤其是在判断索引要不要重建的问题。
2．一般情况下，建议对数据库统计信息的收集采用dbms_stats来进行。
3．需要注意的是‘3）建议索引建立在db_block_size比较大的表空间中’，这句话需要调整为，如果你对数据库的表和索引根据业务需求进行优化，制定了该表和索引的基本数据块的大小，并且相应的创建了相应数据块大小的表空间，而且在数据库内存参数中也要相应的运用不同数据块大小的内存的情况下，需要注意该用法。在一般的情况下，没有对表和索引的数据块大小进行设计，默认采用数据库db_block_size的大小，表空间也没有考虑不同的数据块大小的设置，所以，简单起见，就不需要考虑它了。换句话说，想要考虑，就必须从全局的角度来考虑和设计。
4．明显看到在测试系统中信息收集的PL/SQL执行的效率并不是太高，当然选择在数据库维护窗口阶段执行，还是可以的。
5．从以上脚本来看，建议先进行评估对数据库进行统计信息的收集，包括表和索引收集对业务运行的影响，收集的时间长度估算等，然后再进行统计信息的收集，收集后，才来分析那些20%的高水位的指标的索引进行重新创建。否则，有可能按照规则筛选出来的结果很多，在实际实施的时候，限制的因素就多，存储空间，维护时间，系统资源处理能力以及重新创建后产生的效果等。
6．在确定了需要创建的索引之后，就需要分析创建索引的可行性。
1）。创建索引执行过程的时间。
2）。创建索引的过程对系统运行的影响，尤其是索引扩展段比较多的情况下，后台进程SMON 需要清理临时段的时间也需要考虑。
3）。重建索引所需要注意索引的存储参数以及表空间的存储参数控制。
4）。结合业务应用对表和索引的使用，尽量对索引的重建有个比较清晰的认识，尽量做到全局的权衡。
5）。尽量具体索引问题具体分析解决。