検証環境

WSL
- Ubuntu 22.04 LTS

起動・停止・再起動

3GB越えのめちゃくちゃデカいイメージをプルしてくる。

docker run -d -p 1521:1521 -p 5500:5500 -e ORACLE_PWD=admin --name oracle-express-21 container-registry.oracle.com/database/express:21.3.0-xe

停止

docker stop oracle-express-21

停止後の再起動

docker start oracle-express-21

起動確認

docker logs oracle-express-21

以下のようにログが標準出力に表示される。

Starting Oracle Net Listener.
Oracle Net Listener started.
Starting Oracle Database instance XE.
Oracle Database instance XE started.
 
The Oracle base remains unchanged with value /opt/oracle
#########################
DATABASE IS READY TO USE!
#########################
The following output is now a tail of the alert.log:
Dumping current patch information
===========================================================
No patches have been applied
===========================================================
2024-06-26T05:01:14.426169+00:00
Pluggable database XEPDB1 opened read write
Starting background process CJQ0
2024-06-26T05:01:14.523067+00:00
CJQ0 started with pid=60, OS id=430
Completed: ALTER DATABASE OPEN

起動しているContainer内からOracleに接続(SQL*Plus)

docker exec -it oracle-express-21 sqlplus / as sysdba
 
SQL*Plus: Release 21.0.0.0.0 - Production on Wed Jun 26 05:01:56 2024
Version 21.3.0.0.0
 
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
 
SQL>

この状態でSQLを実行する

SQL> set markup csv on
SQL> select * from all_tab_cols where rownum <= 10;
 
"OWNER","TABLE_NAME","COLUMN_NAME","DATA_TYPE","DATA_TYPE_MOD","DATA_TYPE_OWNER","DATA_LENGTH","DATA_PRECISION","DATA_SCALE","NULLABLE","COLUMN_ID","DEFAULT_LENGTH","DATA_DEFAULT","NUM_DISTINCT","LOW_VALUE","HIGH_VALUE","DENSITY","NUM_NULLS","NUM_BUCKETS","LAST_ANALYZED","SAMPLE_SIZE","CHARACTER_SET_NAME","CHAR_COL_DECL_LENGTH","GLOBAL_STATS","USER_STATS","AVG_COL_LEN","CHAR_LENGTH","CHAR_USED","V80_FMT_IMAGE","DATA_UPGRADED","HIDDEN_COLUMN","VIRTUAL_COLUMN","SEGMENT_COLUMN_ID","INTERNAL_COLUMN_ID","HISTOGRAM","QUALIFIED_COL_NAME","USER_GENERATED","DEFAULT_ON_NULL","IDENTITY_COLUMN","EVALUATION_EDITION","UNUSABLE_BEFORE","UNUSABLE_BEGINNING","COLLATION","COLLATED_COLUMN_ID"
"SYSTEM","LOGMNR_ATTRCOL$","OBJ#","NUMBER",,,22,,,"N",3,,,0,,,0,0,0,"17-AUG-21",,,,"YES","NO",0,0,,"NO","YES","NO","NO",3,3,"NONE","OBJ#","YES","NO","NO",,,,,
"SYSTEM","LOGMNR_ATTRCOL$","NAME","VARCHAR2",,,4000,,,"Y",2,,,0,,,0,0,0,"17-AUG-21",,"CHAR_CS",4000,"YES","NO",0,4000,"B","NO","YES","NO","NO",2,2,"NONE","NAME","YES","NO","NO",,,,"USING_NLS_COMP",
"SYSTEM","LOGMNR_ATTRCOL$","LOGMNR_UID","NUMBER",,,22,22,0,"Y",4,,,0,,,0,0,0,"17-AUG-21",,,,"YES","NO",0,0,,"NO","YES","NO","NO",4,4,"NONE","LOGMNR_UID","YES","NO","NO",,,,,
"SYSTEM","LOGMNR_ATTRCOL$","LOGMNR_FLAGS","NUMBER",,,22,22,0,"Y",5,,,0,,,0,0,0,"17-AUG-21",,,,"YES","NO",0,0,,"NO","YES","NO","NO",5,5,"NONE","LOGMNR_FLAGS","YES","NO","NO",,,,,
"SYSTEM","LOGMNR_ATTRCOL$","INTCOL#","NUMBER",,,22,,,"Y",1,,,0,,,0,0,0,"17-AUG-21",,,,"YES","NO",0,0,,"NO","YES","NO","NO",1,1,"NONE","INTCOL#","YES","NO","NO",,,,,
"SYSTEM","LOGMNR_ATTRIBUTE$","XFLAGS","NUMBER",,,22,,,"Y",14,,,0,,,0,0,0,"17-AUG-21",,,,"YES","NO",0,0,,"NO","YES","NO","NO",14,14,"NONE","XFLAGS","YES","NO","NO",,,,,
"SYSTEM","LOGMNR_ATTRIBUTE$","VERSION#","NUMBER",,,22,,,"Y",1,,,0,,,0,0,0,"17-AUG-21",,,,"YES","NO",0,0,,"NO","YES","NO","NO",1,1,"NONE","VERSION#","YES","NO","NO",,,,,
"SYSTEM","LOGMNR_ATTRIBUTE$","TOID","RAW",,,16,,,"N",22,,,0,,,0,0,0,"17-AUG-21",,,,"YES","NO",0,0,,"NO","YES","NO","NO",22,22,"NONE","TOID","YES","NO","NO",,,,,
"SYSTEM","LOGMNR_ATTRIBUTE$","SYNOBJ#","NUMBER",,,22,,,"Y",6,,,0,,,0,0,0,"17-AUG-21",,,,"YES","NO",0,0,,"NO","YES","NO","NO",6,6,"NONE","SYNOBJ#","YES","NO","NO",,,,,
"SYSTEM","LOGMNR_ATTRIBUTE$","SPARE5","NUMBER",,,22,,,"Y",19,,,0,,,0,0,0,"17-AUG-21",,,,"YES","NO",0,0,,"NO","YES","NO","NO",19,19,"NONE","SPARE5","YES","NO","NO",,,,,
 
10 rows selected.
 
SQL>

スキーマの作成

SQL*Plusで接続

docker exec -it oracle-express-21 sqlplus / as sysdba

セッションの切り替え（CDB→PDB)

SQL> ALTER SESSION SET CONTAINER = XEPDB1;

セッション確認

SQL> show con_name
 
CON_NAME
------------------------------
XEPDB1

表領域の作成以下は、MYTBSという名前の表領域を作成するコマンド。必要に応じて内容を書き換える。

SQL> CREATE TABLESPACE MYTBS DATAFILE '/opt/oracle/oradata/XE/MYTBS01.dbf' SIZE 100M;

スキーマ（ユーザ）の作成

表領域がMYTBS、IDがTEST_USER、PASSWORDがpass12345のユーザを作成するコマンド。必要に応じて内容を書き換える。

CREATE USER TEST_USER IDENTIFIED BY pass12345 DEFAULT TABLESPACE MYTBS TEMPORARY TABLESPACE TEMP;

グラント設定

SQL> GRANT CONNECT TO TEST_USER;
 
Grant succeeded.
 
SQL> GRANT RESOURCE TO TEST_USER;
 
Grant succeeded.
 
SQL> GRANT DBA TO TEST_USER;
 
Grant succeeded.
 
SQL> GRANT CREATE VIEW TO TEST_USER;
 
Grant succeeded.

作成したスキーマへの疎通確認

一旦、SQL*Plusからexitしたうえで

docker exec -it oracle-express-21 sqlplus TEST_USER/pass12345 as sysdba

A5:SQL経由の場合は直接接続を選択して、

項目名	値
ホスト名	localhost
ポート番号	1521
サービス名	XEPDB1
ユーザID	<上記で作成したもの>
パスワード	<上記で作成したもの>

Spring Data JPA経由

各種Oracleに繋ぐ依存関係の設定などプロジェクトの設定が完了している前提で、以下の設定プロファイルを作成する

spring.datasource.url=jdbc:oracle:thin:@localhost:1521/XEPDB1
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.username=<上記で作成したもの>
spring.datasource.password=<上記で作成したもの>

Showichiro/実施コマンド.md

Select an option

No results found