共计 13616 个字符，预计需要花费 35 分钟才能阅读完成。
丸趣 TV 小编给大家分享一下 oracle 中 DataMining 组件怎么用，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！
一.DataMining 组件 说明
在说明之前，我们先用如下 SQL 查看一下 DB 中的组件：
SQL col comp_id for a15
SQL col version for a15
SQL col comp_name for a30
SQL select comp_id,comp_name,versionfrom dba_registry ;
COMP_ID COMP_NAME VERSION
——————————————— —————
XDB Oracle XML Database 11.2.0.3.0
AMD OLAP Catalog 11.2.0.3.0
EM Oracle Enterprise Manager 11.2.0.3.0
SDO Spatial 11.2.0.3.0
ORDIM Oracle Multimedia 11.2.0.3.0
CONTEXT Oracle Text 11.2.0.3.0
ODM Oracle Data Mining 11.2.0.3.0
EXF Oracle Expression Filter 11.2.0.3.0
RUL Oracle Rules Manager 11.2.0.3.0
OWM Oracle Workspace Manager 11.2.0.3.0
CATALOG Oracle Database Catalog Views 11.2.0.3.0
COMP_ID COMP_NAME VERSION
——————————————— —————
CATPROC Oracle Database Packages and T11.2.0.3.0
ypes
JAVAVM JServer JAVA Virtual Machine 11.2.0.3.0
XML Oracle XDK 11.2.0.3.0
CATJAVA Oracle Database Java Packages 11.2.0.3.0
XOQ Oracle OLAP API 11.2.0.3.0
APS OLAP Analytic Workspace 11.2.0.3.0
17 rows selected.
MOS 上说明：
Oracle 8i/9i/10g/11g 组件（Components）说明
http://blog.csdn.net/tianlesoftware/article/details/5937382
Oracle DataMining (ODM) embeds data mining within the Oracle database. ODM algorithmsoperate natively on relational tables or views, thus eliminating the need toextract and transfer data into standalone tools or specialized analyticservers. ODM s integrated architecture results in a simpler, more reliable, andmore efficient data management and analysis environment.
–ODM 是数据库内嵌的数据挖掘组件。ODM 在本地对表或视图进行操作，提取和传输数据到单独的工具或者指定的分析服务上。
ODM 综合架构提供了一个简单，可靠，更有效的数据管理和分析环境。
Data miningtasks can run asynchronously and independently of any specific user interfaceas part of standard database processing pipelines and applications. Dataanalysts can mine the data in the database, build models and methodologies, andthen turn those results and methodologies into full-fledged applicationcomponents ready to be deployed in production environments.
– 数据挖掘任务可以通过用户置顶的接口，作为标准数据库的进程通道或者应用异步和独立的运行。数据分析可以在数据库中挖掘数据，构建模块和算法，然后把结果和算法存入应用组件。
The benefits ofthe integration with the database cannot be emphasized enough when it comes todeploying models and scoring data in a production environment. ODM allows auser to take advantage of all aspects of Oracle s technology stack as part ofan application. Also, fewer moving parts results in a simpler, morereliable, more powerful advanced business intelligence application.
ODM 组件相关的用户：
ODM, ODM_MTR : Oracle 9i
DMSYS/ DMSYS : Oracle 10g
SYS : Oracle 11gR1, 11gR2
Oracle DataMining 11g Release 1 (11.1) has a tight integration with Oracle Database. DataMining metadata and PL/SQL packages have been migrated from DMSYS to SYS. TheDMSYS schema no longer exists in Oracle Database 11g Release 1 (11.1) freshinstallations.
– 从 Oracle 11gR1 开始，ODM 与数据库结合的更紧密，ODM 的 metadata 和 PL/SQL 包也从 DMSYS 中迁移到了 SYS 用户。从 Oracle 11gR1 开始，DMSYS 用户已经不存在，这里是新安装的实例。升级的还是会有。
In 11g freshinstallation Oracle Data Mining will no longer appear in dba_registry view sinceit is not treated as a separate component in 11g, in fact it has beenintegrated into the database itself. However if you have upgraded yourdatabase from an earlier version to 11g, you may see this component indba_registry. This is because of the existence of DMSYS schema, which is notused in 11g and all the objects of DMSYS are shifted to SYS during the upgradeprocess. So once you drop DMSYS schema, Oracle Data Mining willdisappear from the dba_registry.
– 从 Oracle 11g 开始，对于新安装的数据库，Oracle DataMining 将不会显示在 dba_registry. 因为 ODM 不在作为一个数据库的组件而存在，事实上它已经整合到了数据里。
但是，如果是从低版本升级到 11g 的，那么我们还是可以从 dba_registry 中看到，这是因为 DMSYS 用户已经存在，但是其不被使用，所有 DMSYS 的对象，在升级的过程中都迁移到了 SYS 用户。所以当我们 drop 掉 DMSYS 用户之后，在 dba_registry 中就不能看到 ODM 的信息了。
我这里在前面的查询中可以看到 ODM 的信息，是因为我这里就是从 Oracle 10g 升级到 Oracle 11g 的。
二．ODM 卸载
MOS：
Note.297551.1 HowTo Remove the Data Mining Option from the Database
2.1 For Database versions 9i Release 2 (9.2.x)and 10g Release 1 (10.1.x).
TheDBA should be able to deinstall Data Mining from the Deinstall icon onthe OUI (Oracle Universal Installer) page. This is a supported way.
If OUI does not work for somereasons, you may do the following to deinstall ODM :
1. Start SQLPLUS and connect with user sysas sysdba
2. Remove ODM repositories from thedatabase :
2.1.1 For Database release 9i Release 2 (9.2.x).
Drop ODM, ODM_MTR schemasusing the command:
SQL DROP USER ODM_MTR CASCADE;
SQL DROP USER ODM CASCADE;
Drop ODM public synonyms usingthe commands:
SQL set hea off
SQL spool /path/drop_odm_synonyms.sql
SQL select Drop public synonym || SYNONYM_NAME || fromDBA_SYNONYMS where TABLE_OWNER = ODM
SQL spool off
SQL @/path/drop_odm_synonyms.sql
The synonyms names may needto be in double quotes in order for the script to run properly.
Note: 264 synonyms should be selected anddropped.
2.1.2 ForDatabase release 10g Release 1 (10.1.x)
Drop DMSYS schema using the command:
SQL DROP USER DMSYS CASCADE;
Drop DMSYS public synonyms using thecommands:
SQL set hea off
SQL spool /path/drop_dmsys_synonyms.sql
SQL select Drop public synonym || SYNONYM_NAME || fromDBA_SYNONYMS where TABLE_OWNER = DMSYS
SQL spool off
SQL @/path/drop_dmsys_synonyms.sql
Note: 568 synonyms should be selected anddropped.
Followed by:
SQL DELETE FROM exppkgact$ WHERESCHEMA= DMSYS
2.1.3. Update theDBA registry :
Before updatingthe DBA registry, first make sure Data Mining is registered in your database byrunning the following:
SQL connect / as sysdba;
SQL select comp_id, version, status from dba_registry;
If ODM (DataMining) is returned by the above query than execute the following toremove Data Mining from the DBA registry:
SQL exec dbms_registry.removed(ODM
For UNIX Platform thefollowing steps allow to remake Oracle executable without DM libraries
1. Shutdown database
2. cd $ORACLE_HOME/rdbms/lib
3. make -f ins_rdbms.mk dm_off
4. make -f ins_rdbms.mk ioracle
5. bring database up
The sqlplusbanner should no longer display the Data Mining option and v$option should show Oracle Data Mining as false on a Unix system based install.
Pleasenote: It is possible synonyms may exist for Data Mining objects. Ifany exist, remove them to complete the removal.
2.2 For Database version 10g Release 2(10.2.x).
With DatabaseRelease 10g Release 2, the only supported way to remove the Data Mining optionis to use the Oracle Universal Installer.
– 从 Oracle 10gR2 开始只能从 OUI 中删除 ODM 组件。
The steps would be :
(1) Start The Oracle UniversalInstaller.
(2) In Installer , click on the Deinstall Button.
(3) Find you RDBMS home and expandit.
(4) Under it you will find OracleDatabase 10g 10.2.0.x . Expand it.
(5) Under it you will find anotherfolder also called Oracle Database 10g 10.2.0.1 . Expand it.
(6) In the bottom of this tree youwill find Oracle Data Mining RDBMS Files 10.2.0.1 .
This is what youneed to remove.
This Data Mininguninstall process should relink oracle executable with dm_off target, whichshould be reflected in sys.v$option view and SQLPlus banner.
– 在 OUI 的卸载进程会 relink oracle with dm_off.
Note : On someplatform (e.g: HP UX) you may notice that after selecting the Oracle DataMining RDBMS Files 10.2.0.1 and pressing OK you will be asked if you re sureyou want to remove basically everything, including Oracle Database 10g itself.
If this is the case do not proceed that waybut try this way :
– 在一些平台下如果卸载进程不能 relink，那么需要手工处理，如下：
（1）On unix and linux platform:
1. Shutdown database
2. cd $ORACLE_HOME/rdbms/lib
3. make -f ins_rdbms.mk dm_off
4. make -f ins_rdbms.mk ioracle
5. bring database up
（2）On all platform:
Update thedatabase registry to remove the Data Mining component from it by performing thefollowing :
(1) Start SQL+ and log as SYSDBA.
(2) Execute this :
SQL exec dbms_registry.removed(ODM
The sqlplusbanner should no longer display the Data Mining option and v$option should show Oracle Data Mining as false on a Unix system based install.
It is notrecommended to remove the Data Mining schema (DMSYS) due to its dependencieswith the database component Oracle Text. This is documented in Note378159.1 Text breaks if Data Mining is deinstalled.
2.3 For Database version 11g Release 1(11.1.x).
With 11g, OracleData Mining is installed as part of the database installation. Data Miningis now part of Oracle binary and SYS metadata.
– 从 Oracle 11g 开始，ODM 作为数据库的一部分来进行安装，而不是作为组件。
The Data Mining option cannot be removedbut it can be disabled:
– 因为在 11g 中 ODM，不是组件，所以我们只能禁用，不能卸载：
2.3.1 On Linux/Unix platform as below:
1. Shutdown database
2. cd $ORACLE_HOME/rdbms/lib
3. make -f ins_rdbms.mk dm_off
4. make -f ins_rdbms.mk ioracle
5. bring database up
2.3.2 On Windows platform as below:
Use the removeoption from the Universal Installer and select the Data Mining under EnterpriseEdition.
To verify the option have been disabled:
– 验证禁用情况：
(1) Start sqlplus
(2) Connect as DBA user
(3) Query the sys.v$option using :
SQL Selectparameter, value from v$option where parameter = Data Mining
If the returnedvalue for column value is FALSE, then the option have beendisabled.
2.4 For Database version 11g Release 2 (11.2.x).
On both Unix andWindows we use the the chopt tool (available in 11.2) to disable Oracle DataMining in the database.
– 从 Oracle 11gR2 开始，可以使用 chopt 工具来禁用 ODM 组件。
2.4.1 On Linux/Unix platform as below:
$ cd $ORACLE_HOME/bin
$ srvctl stop database -d myDb
$ chopt disable dm
$ srvctl start database -d myDb
2.4.2 On Windows platform as below:
1. Shut down the database with srvctl orSQL*Plus:
srvctl stop database -d myDb
2. Stop the database service, OracleServiceSID, using the Services program inControl Panel.
3. Run the following commands:
cd %ORACLE_HOME%/bin
chopt disable dm
4. Start the database service, OracleServiceSID, using the Services program inControl Panel.
5. Start up the database:
srvctl start database -d myDb
三． ODM 安装
MOS 文档如下：
Note.420791.1 HowTo Manually Install Data Mining
How To Manually Install Data Mining InOracle 11g? [ID 818314.1]3.1 For RDBMS 10.1.x
1. Set ORACLE_HOME and ORACLE_SID.
2. START SQLPlus and connect with SYS user as SYSDBA.
3. Execute the following commands:
Unix – Linux
SQL run$ORACLE_HOME/dm/admin/dminst.sql SYSAUX TEMP
SQL run $ORACLE_HOME/dm/admin/odmpatch.sql
SQL run $ORACLE_HOME/rdbms/admin/utlrp.sql
Windows
SQL start%ORACLE_HOME%\dm\admin\dminst.sql SYSAUX TEMP
SQL start %ORACLE_HOME%\dm\admin\odmpatch.sql
SQL start %ORACLE_HOME%\rdbms\admin\utlrp.sql
4. Ensure Oracle Data Mining is at Valid status in dba_registry by executingthe following query:
SQL select COMP_NAME,VERSION,STATUS from dba_registry whereCOMP_NAME= Oracle Data Mining
3.2 For RDBMS 10.2.x
1. Set ORACLE_HOME and ORACLE_SID.
2. START SQLPlus and connect with SYS user as SYSDBA.
3. Execute the following commands:
Unix – Linux
SQL run$ORACLE_HOME/rdbms/admin/dminst.sql SYSAUX TEMP
SQL run $ORACLE_HOME/rdbms/admin/odmpatch.sql
SQL run $ORACLE_HOME/rdbms/admin/utlrp.sql
Windows
SQL start %ORACLE_HOME%\rdbms\admin\dminst.sql SYSAUX TEMP
SQL start %ORACLE_HOME%\rdbms\admin\odmpatch.sql
SQL start %ORACLE_HOME%\rdbms\admin\utlrp.sql
4. Ensure Oracle Data Mining is at Valid status in dba_registry by executingthe following query:
SQL select COMP_NAME,VERSION,STATUS from dba_registry whereCOMP_NAME= Oracle Data Mining
3.3 For RDBMS 11.1.x and 11.2.x No DMSYS Schema
Oracle DataMining 11g Release 1 (11.1) has a tight integration with Oracle Database.Data Mining metadata and PL/SQL packages have been migrated from DMSYS to SYS. The DMSYS schema no longer exists in OracleDatabase 11g Release 1 (11.1) fresh installations….
从 Oracle 11g 开始，ODM 已经整个到数据里里面，不在作为组件安装，以前版本中的 DMSYS 用户也不存在，其对象也迁移到 SYS 用户下。
If the databasehas been upgraded from earlier version, then DMSYS schema could be there, inwhich case DataMining Adminstration Guide should be followed the relevant steps tomigrate data to SYS schema and then drop DMSYS schema.
– 如果是从以前版本升级到 11g，那么 DMSYS 用户还会存在。
There is nolonger an odm directory in the ORACLE_HOME, and no entry for Data Mining inthe DBA_REGISTRY but there is a value in V$OPTION:
SQL col value format a30
SQL col parameter format a30
SQL select * from v$option where PARAMETER = Data Mining
PARAMETER VALUE
—————————— ——————————
DataMining TRUE
If the abovereturns false, then Data Mining files need to be installed and the Oracleexecutable linked with Data Mining in the Oracle Home. In 11.2 the followingcommand can be used for both Unix/Linux and Windows to enable DataMining in the Oracle Home:
– 如果以上返回 False，那么 ODM 文件需要安装，并 link DM 到 Oracle Home。
在 Oracle 11gR2 下，可以使用如下命令：
chopt enable dm
For 11.1 please go to the Oracle UniversalInstaller and install Data Mining files.
– 在 Oracle 11gR1 中可以使用 OUI 来安装 ODM。
If there is aneed to install Data Mining manually into an Oracle Database, please performthe following steps:
– 手工安装 ODM 步骤如下：
1) Go to the ORACLE_HOME/rdbms/admin directory of the RDBMS instance where youwant to install Data Mining:
2) Ensure ORACLE_HOME, ORACLE_SID are set for the upgraded DB env.
3) Login as SYS and run from SQL*Plus:
SQL @catodm.sql
SQL @dbmsodm.sql
SQL @prvtodm.plb
It is always good to collect the output in a spool file for possible analysisby Support.
四．ODM 状态问题
Note.235486.1 Howto check that the Oracle Data Mining (ODM) Monitor is running
Note.237348.1 TheOracle Data Mining Catalog has a LOADED status instead of a VALID Status
当我们从 dba_registry 中查看 ODM 的状态是 LOADED。如：
SQL select comp_name, version, statusfrom dba_registry where comp_name like %
Data Mining
COMP_NAME STATUS VERSION
———————————– ———– ———-
Oracle Data Mining LOADED 9.2.0.1.0
The ODM catalogmay be valid but as some ODM patchset releases are not part of the RDBMSrelease, the ODM catalog is flagged LOADED.
这种情况下 ODM 可能是有效的，但是一些 ODM 的 patchset 不是 RDBMS 的一部分，所以这里的 ODM 显示为 LOADED。
解决方法：
1) Check if there are anyinvalid objects in the ODM schema.
– 检查是否有无效对象：
a) Connect to SQL*Plus as the ODM user andexecute the following SQL query :
SQL select object_name, object_type from user_objects where status= INVALID
This query should return 0.
b) If objects are invalid, recompile allindividual objects.
Use one of the following commands depending on the object type:
SQL alter package package_name compile; (package specification)
SQL alter package package_name compile body; (package body)
SQL alter view view_name compile; (view)
SQL alter java class object_name resolve; (java class)
Ifan object compiles with warnings, use either of the following commands to see the errors that caused thewarnings:
SQL show errors
or
SQL select * from user_errors where name = OBJECT_NAME
2) Update the registry when all the objects are valid in the ODM schema.
Note : in the following examples, the ODM patchset release version is 9.2.0.3.0.
a) Check the ODM patchsetrelease version :
Connect to SQL*Plus as the ODM user and execute the following SQL query:
SQL select value from odm_configuration where name = ProductBanner
Theoutput indicates the ODM patchset release version.
VALUE
—————————————————————————-
Oracle9i Data Mining (ODM Schema) Version 9.2.0.3.0 – PRODUCTION
b) Update the registry :
SQL connect / as sysdba
SQL execute sys.dbms_registry.loaded(ODM , 9.2.0.3.0 , Oracle Data Mining- Production
SQL execute sys.dbms_registry.valid(ODM
3) Verify that the ODMCatalog is VALID now.
SQL select comp_name, version, statusfrom dba_registry where comp_name like %Data Mining
This query should return :
COMP_NAME STATUS VERSION
———————————– ———– ———-
Oracle Data Mining VALID 9.2.0.3.0
看完了这篇文章，相信你对“oracle 中 DataMining 组件怎么用”有了一定的了解，如果想了解更多相关知识，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！
