共計 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 行業資訊頻道，感謝各位的閱讀！