Release 2.0 for Oracle Database 23ai
サンプル・スキーマの確認とダウンロード
CREATE TABLE supplementary_demographics
(
cust_id NUMBER NOT NULL,
education VARCHAR2(21),
occupation VARCHAR2(21),
household_size VARCHAR2(21),
yrs_residence NUMBER,
affinity_card NUMBER(10),
bulk_pack_diskettes NUMBER(10),
flat_panel_monitor NUMBER(10),
home_theater_package NUMBER(10),
bookkeeping_application NUMBER(10),
printer_supplies NUMBER(10),
y_box_games NUMBER(10),
os_doc_set_kanji NUMBER(10),
comments VARCHAR2(4000),
CONSTRAINT supp_demo_pk
PRIMARY KEY (cust_id)
);CREATE TABLE supplementary_demographics
(
cust_id NUMBER NOT NULL,
education VARCHAR2(21),
occupation VARCHAR2(21),
household_size VARCHAR2(21),
yrs_residence NUMBER,
affinity_card NUMBER(10),
cricket NUMBER(10),
baseball NUMBER(10),
tennis NUMBER(10),
soccer NUMBER(10),
golf NUMBER(10),
unknown NUMBER(10),
misc NUMBER(10),
comments VARCHAR2(4000),
CONSTRAINT supp_demo_pk
PRIMARY KEY (cust_id)
);
% mkdir oml4sql
% cd oml4sql
oml4sql %
サンプル・スキーマのインストール
oml4sql % unzip db-sample-schemas-23.2.zip
Archive: db-sample-schemas-23.2.zip
a780e5b01ae238266f94bb1e6ab1edce028b41e0
creating: /Users/ynakakoshi/Documents/oml4sql/db-sample-schemas-23.2
inflating: db-sample-schemas-23.2/CONTRIBUTING.md
inflating: db-sample-schemas-23.2/LICENSE.txt
inflating: db-sample-schemas-23.2/README.md
inflating: db-sample-schemas-23.2/README.txt
inflating: db-sample-schemas-23.2/SECURITY.md
creating: /Users/ynakakoshi/Documents/oml4sql/db-sample-schemas-23.2/customer_orders
[中略]
inflating: db-sample-schemas-23.2/sales_history/promotions.csv
inflating: db-sample-schemas-23.2/sales_history/sales.csv
inflating: db-sample-schemas-23.2/sales_history/sh_create.sql
inflating: db-sample-schemas-23.2/sales_history/sh_install.sql
inflating: db-sample-schemas-23.2/sales_history/sh_populate.sql
inflating: db-sample-schemas-23.2/sales_history/sh_uninstall.sql
inflating: db-sample-schemas-23.2/sales_history/supplementary_demographics.csv
inflating: db-sample-schemas-23.2/sales_history/times.csv
oml4sql %
cd db-sample-schemas-23.2/sales_history
oml4sql % cd db-sample-schemas-23.2/sales_history
sales_history %
sql system/[パスワード]@localhost/freepdb1
sales_history % sql system/*********@localhost/freepdb1
SQLcl: 金 6月 27 17:29:33 2025のリリース25.1 Production
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Last Successful login time: 金 6月 27 2025 17:29:33 +09:00
接続先:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04
SQL>
SQL> @sh_install
Thank you for installing the Oracle Sales History Sample Schema.
This installation script will automatically exit your database session
at the end of the installation or if any error is encountered.
The entire installation will be logged into the 'sh_install.log' log file.
Enter a password for the user SH: ******
Enter a tablespace for SH [USERS]:
Do you want to overwrite the schema, if it already exists? [YES|no]:
Start time: 27-JUN-25 08.32.14.918612 AM +00:00
****** Creating COUNTRIES table ....
Table COUNTRIESは作成されました。
****** Creating CUSTOMERS table ....
Table CUSTOMERSは作成されました。
****** Creating PROMOTIONS table ....
Table PROMOTIONSは作成されました。
****** Creating PRODUCTS table ....
Table PRODUCTSは作成されました。
****** Creating TIMES table ....
Table TIMESは作成されました。
****** Creating CHANNELS table ....
[中略]
Installation verification
____________________________
Verification:
Table provided actual
_____________________________ ___________ _________
channels 5 5
costs 82112 82112
countries 35 35
customers 55500 55500
products 72 72
promotions 503 503
sales 918843 918843
times 1826 1826
supplementary_demographics 4500 4500
Thank you!
___________________________________________________________
The installation of the sample schema is now finished.
Please check the installation verification output above.
You will now be disconnected from the database.
Thank you for using Oracle Database!
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04から切断されました
sales_history %
sales_history % cd ../..
oml4sql %
OML4SQLのサンプル・スクリプトのダウンロード
git clone --filter=blob:none --no-checkout https://github.com/oracle-samples/oracle-db-examples.git
cd oracle-db-examples
git sparse-checkout set machine-learning/sql/23ai
git checkout
oml4sql % git clone --filter=blob:none --no-checkout https://github.com/oracle-samples/oracle-db-examples.git
Cloning into 'oracle-db-examples'...
remote: Enumerating objects: 5924, done.
remote: Counting objects: 100% (32/32), done.
remote: Compressing objects: 100% (28/28), done.
remote: Total 5924 (delta 16), reused 4 (delta 4), pack-reused 5892 (from 3)
Receiving objects: 100% (5924/5924), 926.30 KiB | 6.43 MiB/s, done.
Resolving deltas: 100% (2488/2488), done.
oml4sql % cd oracle-db-examples
oracle-db-examples % git sparse-checkout set machine-learning/sql/23ai
oracle-db-examples % git checkout
remote: Enumerating objects: 53, done.
remote: Counting objects: 100% (44/44), done.
remote: Compressing objects: 100% (34/34), done.
remote: Total 53 (delta 11), reused 10 (delta 10), pack-reused 9 (from 2)
Receiving objects: 100% (53/53), 150.10 KiB | 960.00 KiB/s, done.
Resolving deltas: 100% (11/11), done.
Your branch is up to date with 'origin/main'.
oracle-db-examples %
cd machine-learning/sql/23ai
oracle-db-examples % cd machine-learning/sql/23ai
23ai % ls
dmsh.sql
dmshgrants.sql
oml4sql-anomaly-detection-1class-svm.sql
oml4sql-anomaly-detection-em.sql
oml4sql-association-rules.sql
oml4sql-attribute-importance.sql
oml4sql-classification-decision-tree.sql
oml4sql-classification-glm.sql
oml4sql-classification-naive-bayes.sql
oml4sql-classification-neural-networks.sql
oml4sql-classification-random-forest.sql
oml4sql-classification-regression-xgboost.sql
oml4sql-classification-svm.sql
oml4sql-classification-text-mining-svm.sql
oml4sql-clustering-expectation-maximization.sql
oml4sql-clustering-kmeans-star-schema.sql
oml4sql-clustering-kmeans.sql
oml4sql-clustering-ocluster.sql
oml4sql-cross-validation-decision-tree.sql
oml4sql-feature-extraction-cur.sql
oml4sql-feature-extraction-nmf.sql
oml4sql-feature-extraction-svd.sql
oml4sql-feature-extraction-text-mining-esa.sql
oml4sql-feature-extraction-text-mining-nmf.sql
oml4sql-feature-extraction-text-term-extraction.sql
oml4sql-partitioned-models-svm.sql
oml4sql-r-extensible-algorithm-registration.sql
oml4sql-r-extensible-association-rules.sql
oml4sql-r-extensible-attribute-importance-via-rf.sql
oml4sql-r-extensible-glm.sql
oml4sql-r-extensible-kmeans.sql
oml4sql-r-extensible-principal-components.sql
oml4sql-r-extensible-regression-neural-networks.sql
oml4sql-r-extensible-regression-tree.sql
oml4sql-regression-glm.sql
oml4sql-regression-neural-networks.sql
oml4sql-regression-random-forest.sql
oml4sql-regression-svm.sql
oml4sql-singular-value-decomposition.sql
oml4sql-survival-analysis-xgboost.sql
oml4sql-time-series-esm-auto-model-search.sql
oml4sql-time-series-exponential-smoothing.sql
oml4sql-time-series-mset.sql
oml4sql-time-series-regression-dataset.sql
oml4sql-time-series-regression.sql
README.md
23ai %
最初にOML4SQLのサンプル・スクリプトを修正します。
23ai % mkdir ../23ai.orig
23ai % cp * ../23ai.oprig
23ai %
23ai % patch -p1 < 23ai.patch
patching file oml4sql-feature-extraction-text-mining-nmf.sql
patching file oml4sql-r-extensible-algorithm-registration.sql
patching file oml4sql-r-extensible-association-rules.sql
patching file oml4sql-r-extensible-attribute-importance-via-rf.sql
patching file oml4sql-regression-random-forest.sql
patching file oml4sql-time-series-regression-dataset.sql
patching file oml4sql-time-series-regression.sql
23ai %
OML4SQLのサンプル・スクリプトの実行
23ai % sql sys/********@localhost/freepdb1 as sysdba
SQLcl: 月 6月 30 14:47:10 2025のリリース25.1 Production
Copyright (c) 1982, 2025, Oracle. All rights reserved.
接続先:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04
SQL> @dmshgrants OMLUSER
旧:grant create session to &DMUSER
新:grant create session to OMLUSER
Grantが正常に実行されました。
旧:grant create table to &DMUSER
新:grant create table to OMLUSER
Grantが正常に実行されました。
旧:grant create view to &DMUSER
新:grant create view to OMLUSER
Grantが正常に実行されました。
旧:grant create mining model to &DMUSER
新:grant create mining model to OMLUSER
Grantが正常に実行されました。
旧:grant execute on ctxsys.ctx_ddl to &DMUSER
新:grant execute on ctxsys.ctx_ddl to OMLUSER
Grantが正常に実行されました。
旧:GRANT SELECT ON sh.customers TO &DMUSER
新:GRANT SELECT ON sh.customers TO OMLUSER
Grantが正常に実行されました。
旧:GRANT SELECT ON sh.sales TO &DMUSER
新:GRANT SELECT ON sh.sales TO OMLUSER
Grantが正常に実行されました。
旧:GRANT SELECT ON sh.products TO &DMUSER
新:GRANT SELECT ON sh.products TO OMLUSER
Grantが正常に実行されました。
旧:GRANT SELECT ON sh.supplementary_demographics TO &DMUSER
新:GRANT SELECT ON sh.supplementary_demographics TO OMLUSER
Grantが正常に実行されました。
旧:GRANT SELECT ON sh.countries TO &DMUSER
新:GRANT SELECT ON sh.countries TO OMLUSER
Grantが正常に実行されました。
SQL> exit
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04から切断されました
23ai %
23ai % sql omluser/********@localhost/freepdb1
SQLcl: 月 6月 30 14:48:42 2025のリリース25.1 Production
Copyright (c) 1982, 2025, Oracle. All rights reserved.
接続先:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04
SQL> @dmsh
View MINING_DATAは作成されました。
View MINING_BUILD_TEXTは作成されました。
View MINING_TEST_TEXTは作成されました。
View MINING_APPLY_TEXTは作成されました。
View MINING_DATA_BUILD_Vは作成されました。
View MINING_DATA_TEST_Vは作成されました。
View MINING_DATA_APPLY_Vは作成されました。
View MINING_DATA_ONE_CLASS_Vは作成されました。
View MINING_DATA_BUILD_PARALLEL_Vは作成されました。
View MINING_DATA_ONE_CLASS_PVは作成されました。
View MINING_DATA_TEST_PARALLEL_Vは作成されました。
View MINING_DATA_APPLY_PARALLEL_Vは作成されました。
View MINING_TEST_TEXT_PARALLELは作成されました。
SQL> exit
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04から切断されました
23ai %
create index build_text_idx on mining_build_text_m(education) indextype is ctxsys.context;
23ai % sql omluser/********@localhost/freepdb1
SQLcl: 月 6月 30 14:50:24 2025のリリース25.1 Production
Copyright (c) 1982, 2025, Oracle. All rights reserved.
接続先:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04
SQL> create table mining_build_text_m as select cust_id, education from mining_build_text;
Table MINING_BUILD_TEXT_Mは作成されました。
SQL> create index build_text_idx on mining_build_text_m(education) indextype is ctxsys.context;
Index BUILD_TEXT_IDXは作成されました。
SQL> exit
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04から切断されました
23ai %
23ai % podman exec -it oml4r-db bash
bash-4.4$
curl -OL https://cran.r-project.org/src/contrib/Archive/randomForest/randomForest_4.6-14.tar.gz
bash-4.4$ curl -OL https://cran.r-project.org/src/contrib/Archive/randomForest/randomForest_4.6-14.tar.gz
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 80074 100 80074 0 0 21542 0 0:00:03 0:00:03 --:--:-- 21536
bash-4.4$
ORE CMD INSTALL randomForest_4.6-14.tar.gz
bash-4.4$ ORE CMD INSTALL randomForest_4.6-14.tar.gz
* installing to library '/opt/oracle/product/23ai/dbhomeFree/R/library'
* installing *source* package 'randomForest' ...
** package 'randomForest' successfully unpacked and MD5 sums checked
** using staged installation
** libs
gcc -m64 -I"/usr/lib64/R/../../include/R" -DNDEBUG -I/usr/local/include -fpic -g -O2 -c classTree.c -o classTree.o
gcc -m64 -I"/usr/lib64/R/../../include/R" -DNDEBUG -I/usr/local/include -fpic -g -O2 -c init.c -o init.o
gcc -m64 -I"/usr/lib64/R/../../include/R" -DNDEBUG -I/usr/local/include -fpic -g -O2 -c regTree.c -o regTree.o
gcc -m64 -I"/usr/lib64/R/../../include/R" -DNDEBUG -I/usr/local/include -fpic -g -O2 -c regrf.c -o regrf.o
gcc -m64 -I"/usr/lib64/R/../../include/R" -DNDEBUG -I/usr/local/include -fpic -g -O2 -c rf.c -o rf.o
gfortran -m64 -fno-optimize-sibling-calls -fpic -g -O2 -c rfsub.f -o rfsub.o
gcc -m64 -I"/usr/lib64/R/../../include/R" -DNDEBUG -I/usr/local/include -fpic -g -O2 -c rfutils.c -o rfutils.o
gcc -m64 -shared -L/usr/lib64/R/lib -L/usr/local/lib64 -o randomForest.so classTree.o init.o regTree.o regrf.o rf.o rfsub.o rfutils.o -lgfortran -lm -lquadmath -L/usr/lib64/R/lib -lR
installing to /opt/oracle/product/23ai/dbhomeFree/R/library/00LOCK-randomForest/00new/randomForest/libs
** R
** data
** inst
** byte-compile and prepare package for lazy loading
** help
*** installing help indices
converting help for package 'randomForest'
finding HTML links ... done
MDSplot html
classCenter html
combine html
getTree html
grow html
importance html
imports85 html
margin html
na.roughfix html
outlier html
partialPlot html
plot.randomForest html
predict.randomForest html
randomForest html
rfImpute html
rfNews html
rfcv html
treesize html
tuneRF html
varImpPlot html
varUsed html
** building package indices
** testing if installed package can be loaded from temporary location
** checking absolute paths in shared objects and dynamic libraries
** testing if installed package can be loaded from final location
** testing if installed package keeps a record of temporary installation path
* DONE (randomForest)
bash-4.4$
bash-4.4$ exit
exit
ynakakoshi@nsmacbookintel 23ai %
for f in oml4sql-*.sql
do
basename=${f%.*}
if [ "$basename" != "oml4sql-time-series-regression-dataset" ]; then
echo spool $basename
echo @$basename
fi
done > run-all.sql23ai % for f in oml4sql-*.sql
for> do
for> basename=${f%.*}
for> if [ "$basename" != "oml4sql-time-series-regression-dataset" ]; then
for then> echo spool $basename
for then> echo @$basename
for then> fi
for> done > run-all.sql
23ai %
23ai % export NLS_LANG=American_America.AL32UTF8
23ai % sql omluser/*******@localhost/freepdb1 @run-all
SQLcl: 月 6月 30 16:02:34 2025のリリース25.1 Production
Copyright (c) 1982, 2025, Oracle. All rights reserved.
接続先:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04
SQL>
SQL> -----------------------------------------------------------------------
SQL> -- SAMPLE PROBLEM
SQL> -----------------------------------------------------------------------
SQL> -- Given demographics about a set of customers that are known to have
SQL> -- an affinity card, 1) find the most atypical members of this group
SQL> -- (outlier identification), 2) discover the common demographic
SQL> -- characteristics of the most typical customers with affinity card,
SQL> -- and 3) compute how typical a given new/hypothetical customer is.
SQL> --
SQL> -------
SQL> -- DATA
SQL> -------
SQL> -- The data for this sample is composed from base tables in the SH schema
SQL> -- (See Sample Schema Documentation) and presented through a view:
SQL> -- mining_data_one_class_pv
SQL> -- (See dmsh.sql for view definition).
SQL> --
SQL> --
SQL>
SQL> -----------------------------------------------------------------------
SQL> -- BUILD THE MODEL
SQL> -------------------
[中略]
CASE_ID DAX REGRESSION_FORECAST BASELINE_FORECAST
------------------------------ ---------- ------------------- -----------------
REGRESSION_ERROR BASELINE_ERROR
---------------- --------------
98-02-04 4.633E+003 4540.27832 4681.13345
9.273E+001 -4.81E+001
1行が選択されました。
SQL> /
CASE_ID DAX REGRESSION_FORECAST BASELINE_FORECAST
------------------------------ ---------- ------------------- -----------------
REGRESSION_ERROR BASELINE_ERROR
---------------- --------------
98-02-04 4.633E+003 4540.27832 4681.13345
9.273E+001 -4.81E+001
1行が選択されました。
SQL> -----------------------------------------------------------------------
SQL> -- End of script
SQL> -------------------------------------------------------------------------
SQL>
SQL> exit
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04から切断されました
23ai %
for f in oml4sql-*.lst
do
grep ORA- $f > /dev/null
if [ $? -eq 0 ]; then
echo $f
fi
done23ai % for f in oml4sql-*.lst
do
grep ORA- $f > /dev/null
if [ $? -eq 0 ]; then
echo $f
fi
done
23ai %
