索引可以加快查询的速度，但索引会占用许多存储空间，在插入和删除行的时候，索引还会引入额外的开销，因此确保索引得到有效利用是我们很关注的一个问题。在Oracle9i之前，要知道一个索引是否被使用是困难的，而Oracle 9i中提供了一个有效的监控方法:ALTER INDEX MONITORING USAGE。
但是使用这个特性时需要注意：
"To execute the MONITORING USAGE clause, the index must be in your own schema."
具体说明请参考online document。
另外，有一篇很好的文章：http://echo.sharera.com/blog/BlogTopic/5630.htm,不过作者的脚本有一些错误:V$ALL_OBJECT_USAGE因该替换为V\$ALL_OBJECT_USAGE.
今天在sqlplus中设置set autotrace 时出错：
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
解决过程请参考asktom文章：http://asktom.oracle.com/~tkyte/article1/autotrace.html
oracle生产库运行在windows下的情况较少，昨天解决安装问题时，看到metalink一篇文章，讲如何在windows下彻底删除oracle，贴在这里，方便大家查阅。
(Metalink Notes:124353.1)
Title: Manually Removing all Oracle Components on Microsoft Windows Platforms
PURPOSE
-------
This article describes the procedure how to manually perform the actions to
create a "clean machine" on Microsoft Windows 95/98/NT/2000/XP and how to
manually remove all Oracle components (e.g.: oracle RDBMS database server,
IAS, OEM, client installations, etc) and services from your computer.
SCOPE & APPLICATION
-------------------
This article is intended for DBA's on the Microsoft Windows platform.
WARNING:
1. Remove all Oracle components from your computer only as a last resort,
and only if you want to remove ALL Oracle components from your system.
2. These instructions remove all Oracle components, services, and registry
entries from your computer. In addition, any database files in the
subdirectories under ORACLE_BASE\ORADATA\ are also removed.
3. These procedures will also remove Oracle network configuration files,
user written scripts and any other user generated files that may be stored
in Oracle_Base directories.
4. Exercise extreme care when removing registry entries. Removing incorrect
entries can severely compromise your computer.
MANUALLY REMOVING ALL ORACLE COMPONENTS AND SERVICES FROM YOUR COMPUTER
-----------------------------------------------------------------------
Section A (steps 1-6) describes the removal of all Oracle components on
Windows NT,2000 and XP.
Section B (steps 1-4) describes the removal of all Oracle components on
Windows 95 and Windows 98.
A. Removing Components on Windows NT/2000/XP.
---------------------------------------------
To remove all Oracle components from a computer on Windows NT/2000/XP:
1. Check privileges:
-----------------
1.a. Ensure you are logged in as a user with Administrator privileges.
2. Stop all Oracle services (if any are running):
----------------------------------------------
2.a. NT: Choose Start > Settings > Control Panel > Services.
2000,XP: Right click My Computer > Manage > Services and Applications >
> Services
2.b. If any Oracle services (their names begin with Oracle) exist and have
the status Started, select the service and click Stop.
2.c. Click Close to exit the Services window.
2.d. Close the Control Panel/Computer Management window.
3. Remove the entries in the Windows registry:
-------------------------------------------
3.a. Start the registry editor:
Choose Start > Run > regedit
Note: On Windows NT you can use regedt32 instead. The searching
capabilities of regedt32 is limited compared to regedit. It will be
needed to be able to edit 32-bit entries in the registry. Since we
are not going to update any 32-bit entry it is not needed.
3.b. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
Note the value of the key INST_LOC, this is the location of the
Oracle Universal Installer (OUI). The default location is
C:\Program Files\Oracle\Inventory. If this value is different, make
note of it, so we can delete these files later.
Delete this ORACLE key.
3.c. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC and expand all subkeys and
remove all keys under here which are related with the
"Oracle ODBC Driver"
3.d. Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services and remove
all keys under here that begin with ORACLE or ORAWEB.
3.e. Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\...
...\Application and remove all keys under here that begin with ORACLE.
3.f. Go to HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\...
...\Uninstall and remove any entries related to Oracle.
3.g. Go to HKEY_CLASSES_ROOT, remove all keys that begin with Ora or ORCL
(e.g. Oracle..., ORADC..., ORAMMC..., OraOLE..., OraPerf... and ORCL...).
3.h. Go to HKEY_LOCAL_MACHINE\SOFTWARE\Classes, remove all keys that begin
with Ora or ORCL
(e.g. Oracle..., ORADC..., ORAMMC..., OraOLE..., OraPerf... and ORCL...).
3.i. Go to HKEY_CURRENT_USER\Software\Oracle, delete this ORACLE key.
3.j. Go to HKEY_USERS\…\Software\Oracle, delete this ORACLE key.
3.k. Close the registry.
4. Clean up the environment settings:
----------------------------------
4.a. NT: Choose Start > Settings > Control Panel > System > Environment tab
2000,XP: Choose Start > Settings > Control Panel > System >
Advanced tab > Environment variables.
4.b. At "System Variables" click on the variable PATH in order to modify
the value. For example, you may see a path similar to this one:
C:\ORACLE\ORA81\BIN;C:\PROGRAM FILES\ORACLE\JRE\1.1.7\BIN
4.c. If an %ORACLE_HOME% was installed, remove this %ORACLE_HOME%\BIN path.
4.d. If JRE was installed by Oracle, remove the JRE path.
4.e. If there is a CLASSPATH variable under "System Variables", first make
note of the path defined, then delete it. This variable can be added
back at a later date if needed.
4.f. Check if there are any other Oracle variables set in "System Variables",
ORACLE_HOME, ORACLE_SID, TNS_ADMIN, JSERV or WV_GATEWAY_CFG. If these
exist, delete them also.
4.g. Click on APPLY and OK.
4.h. Close the Control Panel window.
5. Delete the software and icons:
------------------------------
5.a. NT: Choose Start > Programs > Windows NT Explorer.
2000,XP: Choose Start > Programs > Accessories > Windows Explorer.
5.b. NT: Go to %SystemDrive%\WINNT\PROFILES\ALL USERS\START MENU\PROGRAMS
2000,XP: Go to %SystemDrive%\DOCUMENTS AND SETTINGS\ALL USERS\...
...\START MENU\PROGRAMS
Note 1: These locations depend on whether OS was upgraded from NT, or
this was a fresh install of 2000/XP.
Note 2: To locate your System Drive, type in DOS-box: echo %SystemDrive%
and delete the following icons:
- Oracle Installation Products
- PRODUCT_NAME - HOME_NAME e.g.
Oracle for Windows NT - Dev6i
Oracle Reports 6i - Dev6i
Oracle Olap Client 2.2 - Dev6i
Oracle9i Lite
Oracle - OraHome92
5.c. Go to %SystemDrive%\Program Files\Oracle or the location of INST_LOC as
noted earlier in step 3.b. and delete this directory.
Note: In order to successfully delete all files, you may have to reboot
your computer first, in order to clear Operating System locks
on those files.
5.d. Go to the temporary directory and delete all files and directories in
here (see note in 5.c.).
NT: %SystemDrive%\Temp
2000,XP:
%SystemDrive%\Documents and Settings\
5.e. Go to the drive where the Oracle software is installed on your machine
and delete all ORACLE_BASE directories on your hard drive (see note in
5.c.).
5.f. Close the Windows Explorer / Windows NT Explorer.
6. Finish the removal:
-------------------
6.a. Empty the recycle bin
Right click on recycle bin > Empty Recycle Bin.
6.b. Reboot your computer.
6.c. Optionally: If you are on Windows 2000 or XP run the System Defragmenter
utility:
- from Control Panel, select Administrative Tools > Computer Management
- expand Storage, then select Disk Defragmenter
- highlight each virtual drive, in turn, and click Defragment
- reboot your computer when finished.
B. Removing Components on Windows 95 or Windows 98.
---------------------------------------------------
To remove all Oracle components from a computer on Windows 95/Windows 98:
1. Remove the entries in the Windows registry:
-------------------------------------------
1.a. Start the registry editor at the MS-DOS command prompt:
C:\> REGEDIT
1.b. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
Note the value of the key INST_LOC, this is the location of the Oracle
Universal Installer. The default location is
C:\Program Files\Oracle\Inventory
If this value is different, make note of it, so we can delete these files
later. Delete this ORACLE key.
1.c. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC and expand all subkeys and
remove all keys under here which are related with the
"Oracle ODBC Driver".
1.d. Go to:
HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall
and remove any entries related to Oracle.
1.e. Go to HKEY_CLASSES_ROOT, remove all keys that begin with Ora or ORCL
(e.g. Oracle..., ORADC..., ORAMMC..., OraOLE..., OraPerf... and ORCL...).
1.f. Go to HKEY_LOCAL_MACHINE\SOFTWARE\Classes, remove all keys that begin
with Ora or ORCL
(e.g. Oracle..., ORADC..., ORAMMC..., OraOLE..., OraPerf... and ORCL...).
1.g. Go to HKEY_CURRENT_USER\Software\Oracle, delete this ORACLE key.
1.h. Go to HKEY_USERS\…\Software\Oracle, delete this ORACLE key.
1.i. Close the registry.
2. Clean up the environment settings:
----------------------------------
2.a. Open your AUTOEXEC.BAT with an editor to modify the PATH variable.
For example, you may see a path similar to this one:
C:\ORACLE\ORA81\BIN;G:\PROGRAM FILES\ORACLE\JRE\1.1.7\BIN .
2.b. If an %ORACLE_HOME% was installed, remove this %ORACLE_HOME%\BIN path.
2.c. If there is a CLASSPATH variable set in the AUTOEXEC.BAT, first make note
of the path defined, then delete it. This variable can be added back at
a later date if needed.
2.d. If JRE was installed by Oracle, remove the JRE path.
2.e. Save the AUTOEXEC.BAT file and close the edit.
3. Delete the software and icons:
------------------------------
3.a. Choose Start > Programs > Windows Explorer.
3.b. Go to SYSTEM_DRIVE:\WINDOWS\START MENU\PROGRAMS
and delete the following icons:
- Oracle - HOME_NAME
where HOME_NAME is the previous Oracle home name.
- Oracle Installation Products
3.c. Go to SYSTEM_DRIVE:\Program Files\Oracle or the location of INST_LOC as
noted earlier in step 1.b and delete this directory.
3.d Go to SYSTEM_DRIVE:\Temp and delete all files and directories in here.
3.e. Go to the drive where the Oracle software is installed on your machine
and delete all ORACLE_BASE directories on your hard drive.
3.f. Remove any Oracle-related .INI files that may exist in the Windows
directory. The Windows directory may be found by entering
"echo %WINDIR%" from a command prompt.
Typical Oracle .INI files include ORADIM73.INI, ORADIM80.INI,
ORACLE.INI, ORAODBC.INI
3.g. Close the Windows Explorer.
4. Finish the removal:
-------------------
4.a. Empty the recycle bin
Right click on recycle bin > Empty Recycle Bin
4.b. Reboot your computer.
RELATED DOCUMENTS
-----------------
Oracle Installation Guides for Windows.
SEARCH WORDS
------------
WIN95, WIN98, WINNT, WIN2000, Windows95, Windows98, WindowsNT, Windows2000,
de-install, deinstall, re-install, reinstall
昨天晚上在winxp(sp2)上安装oracle9i patchsets 9.2.0.8的时候，出现以下错误:
郁闷的是中文乱码，我搞不清楚到底出了什么错，无从下手。
夜也深了，睡觉先。
早上起来，第一件事是解决错误提示中中文乱码问题，经过努力，终于消除了乱码，方法就是:
开始->控制面板->日期、时间、语言和区域设置->区域和语言选项->设置“非Unicode程序的语言”为英文,重新启动winxp，再启动oracle 安装程序，就是英文显示了，记住看到错误提示后要将刚才的设置改为中文。
现在可以看到错误提示的真面目了：
原来错误就是：Certain Files which need to be Reinstalled by Oracle Universal Installer are Being Used by one or more runing services.
现在就可以借助google来帮我们解决问题了。
总结一下解决这个问题的方法就是：
1、找到使用oracle_home目录中文件的windows services，停止这些服务；
(其实找到这些服务很难，你也不大可能停止提示列出的所有services,可以借助工具Process Explorer解决这个问题，用这个工具可以找到使用oracle_home目录中文件的进程，kill这些进程即可。)
照理说，这个时候就可以正常安装了，保险的话可以继续下面步骤。
2、将oracle_home对应的目录(例如：D:\oracle\ora92)改名;
3、重起机器后，将oracle_home对应的目录名改回来，就可以正常安装了。
参考文档：Metalink Note: 117672.1
另外Process Explorer这个工具也是这次的一个意外收获，以前想找到正在使用windows某个文件的特定进程，试过几个工具，比较下来还是process Explorer方便。
学习使用oracle也有5、6个年头了，解决过不少实际工作中遇到的问题，有时候也记录些心得体会，不过分散在电脑上，常常过了段时间就丢了，昨天晚上上metalink的时候发现oracle已经发布了Oracle 9i RDBMS 的Patchset 9.2.0.8，这可能是oracle9i最后的补丁集，但是当我在我的笔记本上安装的时候却无法继续，今天上午我又费了很大的劲才解决了这个问题，当时感觉很有成就感，这是我写此blog的直接动因；另外我也希望能和更多的朋友在这里交流学习和使用oracle中遇到的各种问题。 明天去参加oracle官方培训Oracle9i Database Performance Tuning R2 Ed 2，这是好不容易从公司争取到培训费用的，在这里也庆祝一下。
