在9i和以前的版本，索引提示的格式为/*+ index(table_alias) */或/*+ index(table_alias index_name) */，但是在10g中不仅可以通过索引名称来确定HINT的索引，还可以通过指定列名的方式，格式为/*+ index(table_alias (column_names)) */

SQL> create table test_hint
  2  as
  3  select * from dba_objects;
Table created.
SQL> create index ind_hint on test_hint(owner,object_type);
Index created.
SQL>  exec dbms_stats.gather_table_stats(user, 'TEST_HINT',
  2   method_opt => 'for all indexed columns size 100',cascade=>true);
PL/SQL procedure successfully completed.
SQL> set autot trace exp
SQL> select * from test_hint where owner = 'SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 11101196
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 23272 |  2113K|   161   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST_HINT | 23272 |  2113K|   161   (1)| 00:00:02 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OWNER"='SYS')
SQL> select /*+index(a)*/ * from test_hint  a where owner = 'SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 890897193
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 23272 |  2113K|  1122   (1)| 00:00:14 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HINT | 23272 |  2113K|  1122   (1)| 00:00:14 |
|*  2 |   INDEX RANGE SCAN          | IND_HINT  | 23272 |       |    84   (0)| 00:00:02 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER"='SYS')
SQL> select /*+index(a ind_hint)*/ * from test_hint  a where owner = 'SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 890897193
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 23272 |  2113K|  1122   (1)| 00:00:14 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HINT | 23272 |  2113K|  1122   (1)| 00:00:14 |
|*  2 |   INDEX RANGE SCAN          | IND_HINT  | 23272 |       |    84   (0)| 00:00:02 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER"='SYS')
SQL> select /*+index(a (owner,object_type))*/ * from test_hint  a where owner = 'SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 890897193
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 23272 |  2113K|  1122   (1)| 00:00:14 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HINT | 23272 |  2113K|  1122   (1)| 00:00:14 |
|*  2 |   INDEX RANGE SCAN          | IND_HINT  | 23272 |       |    84   (0)| 00:00:02 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER"='SYS')
--指定和index完全一致的列，走index
SQL> select /*+index(a (owner))*/ * from test_hint  a where owner = 'SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 890897193
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 23272 |  2113K|  1122   (1)| 00:00:14 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HINT | 23272 |  2113K|  1122   (1)| 00:00:14 |
|*  2 |   INDEX RANGE SCAN          | IND_HINT  | 23272 |       |    84   (0)| 00:00:02 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER"='SYS')
--指定列和where条件一致，也可以使用该index
SQL> select /*+index(a (object_id))*/ * from test_hint  a where owner = 'SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 11101196
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 23272 |  2113K|   161   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST_HINT | 23272 |  2113K|   161   (1)| 00:00:02 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OWNER"='SYS')
--虽然是index中的一个列，但是由于不是where条件中，所以不能被使用
SQL> select /*+index(a (owner))*/ * from test_hint  a where object_type = 'TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value: 1755360976
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |  1752 |   159K|   104   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HINT |  1752 |   159K|   104   (0)| 00:00:02 |
|*  2 |   INDEX SKIP SCAN           | IND_HINT  |  1752 |       |    25   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_TYPE"='TABLE')
       filter("OBJECT_TYPE"='TABLE')
--指定index的第一列，虽然不在where中，但是还是会使用index
--说明：使用/*+ index(table_alias (column_names)) */方式的hint，需要先测试，有可能不能达到预期效果

TOM写了个好工具SHOW_SPACE,这个工具对于Oracle来讲其实就是个存储过程，这个存储过程可以用来分析空间使用情况，有了此工具，就不用再通过写SQL语句来看每条记录或表占用表空间的大小了，使用起来很方便。

create or replace procedure show_space
( p_segname_1 in varchar2,
p_owner_1 in varchar2 default user,
p_type_1 in varchar2 default 'TABLE',
p_space in varchar2 default 'AUTO',
p_analyzed in varchar2 default 'Y'
)
as
p_segname varchar2(100);
p_type varchar2(10);
p_owner varchar2(30);
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number;
l_fs1_bytes number;
l_fs2_blocks number;
l_fs2_bytes number;
l_fs3_blocks number;
l_fs3_bytes number;
l_fs4_blocks number;
l_fs4_bytes number;
l_full_blocks number;
l_full_bytes number;
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ||
p_num );
end;
begin
p_segname := upper(p_segname_1); -- rainy changed
p_owner := upper(p_owner_1);
p_type := p_type_1;
if (p_type_1 = 'i' or p_type_1 = 'I') then --rainy changed
p_type := 'INDEX';
end if;
if (p_type_1 = 't' or p_type_1 = 'T') then --rainy changed
p_type := 'TABLE';
end if;
if (p_type_1 = 'c' or p_type_1 = 'C') then --rainy changed
p_type := 'CLUSTER';
end if;
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );
if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO') then
dbms_space.free_blocks
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks );
p( 'Free Blocks', l_free_blks );
end if;
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
/*IF the segment is analyzed */
if p_analyzed = 'Y' then
dbms_space.space_usage(segment_owner => p_owner ,
segment_name => p_segname ,
segment_type => p_type ,
unformatted_blocks => l_unformatted_blocks ,
unformatted_bytes => l_unformatted_bytes,
fs1_blocks => l_fs1_blocks,
fs1_bytes => l_fs1_bytes ,
fs2_blocks => l_fs2_blocks,
fs2_bytes => l_fs2_bytes,
fs3_blocks => l_fs3_blocks ,
fs3_bytes => l_fs3_bytes,
fs4_blocks => l_fs4_blocks,
fs4_bytes => l_fs4_bytes,
full_blocks => l_full_blocks,
full_bytes => l_full_bytes);
dbms_output.put_line(rpad(' ',50,'*'));
dbms_output.put_line('The segment is analyzed');
p( '0% -- 25% free space blocks', l_fs1_blocks);
p( '0% -- 25% free space bytes', l_fs1_bytes);
p( '25% -- 50% free space blocks', l_fs2_blocks);
p( '25% -- 50% free space bytes', l_fs2_bytes);
p( '50% -- 75% free space blocks', l_fs3_blocks);
p( '50% -- 75% free space bytes', l_fs3_bytes);
p( '75% -- 100% free space blocks', l_fs4_blocks);
p( '75% -- 100% free space bytes', l_fs4_bytes);
p( 'Unused Blocks', l_unformatted_blocks );
p( 'Unused Bytes', l_unformatted_bytes );
p( 'Total Blocks', l_full_blocks);
p( 'Total bytes', l_full_bytes);
end if;
end;
/

用法

SQL> create table t
  2  as
  3  select * from dba_objects;
Table created.
SQL> set serverout on
SQL>  exec show_space('T','TEST');
Total Blocks............................768
Total Bytes.............................6291456
Unused Blocks...........................52
Unused Bytes............................425984
Last Used Ext FileId....................4
Last Used Ext BlockId...................1033
Last Used Block.........................76
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................696
Total bytes.............................5701632
PL/SQL procedure successfully completed.
SQL> delete from t;
50602 rows deleted.
SQL> exec show_space('T','TEST');
Total Blocks............................768
Total Bytes.............................6291456
Unused Blocks...........................52
Unused Bytes............................425984
Last Used Ext FileId....................4
Last Used Ext BlockId...................1033
Last Used Block.........................76
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........696
75% -- 100% free space bytes............5701632
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................0
Total bytes.............................0
PL/SQL procedure successfully completed.
SQL> alter table t move;
Table altered.
SQL> exec show_space('T','TEST');
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................5
Unused Bytes............................40960
Last Used Ext FileId....................4
Last Used Ext BlockId...................401
Last Used Block.........................3
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................0
Total bytes.............................0
PL/SQL procedure successfully completed.

今天遇到一个比较特殊的死锁现象，记录下来

DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00090022-000002ba        15     145     X             15     145           X
session 145: DID 0001-000F-00000019     session 145: DID 0001-000F-00000019
Rows waited on:
Session 145: obj - rowid = 0000E0A3 - AAAOCjAAFAAAAA8AAA
  (dictionary objn - 57507, file - 5, block - 60, slot - 0)
Information on the OTHER waiting sessions:
End of information on OTHER waiting sessions.
Current SQL statement for this session:
UPDATE T SET Y = Y WHERE X = :B1
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x67987910         4  CHF.T
===================================================

问题原因：自治事件导致(重现)

drop table t;
create table t ( x int, y int );
create or replace trigger t before update on t
for each row
declare
    pragma autonomous_transaction;
begin
    update t set y = y where x = :new.x;
    commit;
end;
/
insert into t values ( 1, 1 );
commit;
update t set y = y where x = 1;

我见识过的，讲的最好的关于dump相关的文章，这个里面关于dump block,dump index,dump controlfile说的比较详细，看了收获不小，现在贡献出来和大家分享
如何阅读oracle数据块的dump文件
http://www.xifenfei.com/wp-content/uploads/2011/09/read_oracle_dump.pdf