docker安裝oracle
# 下載鏡像 19.3.0.0.0
docker pull registry.cn-hangzhou.aliyuncs.com/zhuyijun/oracle:19c
# 創(chuàng)建文件
mkdir -p /home/mymount/oracle19c/oradata
mkdir -p /home/mymount/oracle23c/oradata
# 授權(quán),不授權(quán)會(huì)導(dǎo)致后面安裝失敗
chmod 777 /home/mymount/oracle19c/oradata
chmod 777 /home/mymount/oracle23c/oradata
docker run -d \
-p 1521:1521 -p 5500:5500 \
-e ORACLE_SID=SID \
-e ORACLE_PDB=PDB \
-e ORACLE_PWD=123456 \
-e ORACLE_EDITION=standard \
-e ORACLE_CHARACTERSET=AL32UTF8 \
-v /home/mymount/oracle19c/oradata:/opt/oracle/oradata \
--name oracle19c \
registry.cn-hangzhou.aliyuncs.com/zhuyijun/oracle:19c
# 查看日志,等待加載進(jìn)度(初次加載比較慢)
docker logs -tf oracle19c
# 等待過(guò)程可能會(huì)比較漫長(zhǎng),成功的日志:
#########################
DATABASE IS READY TO USE!
#########################
# 連接數(shù)據(jù)庫(kù)登錄 默認(rèn)賬號(hào)密碼:sys/123456
docker exec -it oracle19c /bin/bash
sqlplus sys/123456@localhost:1521/pdb as sysdba;
查看日志是否安裝成功
docker logs -ft 容器名
docker logs -ft orcl19c
docker container ls -a命令查看容器名
sys 用戶登錄容器
### 打開(kāi)容器
docker exec -it oracle19c /bin/bash
sqlplus sys/123456@localhost:1521/pdb as sysdba;
show pdbs;
docker exec -it oracle23 /bin/bash
sqlplus sys/123456@localhost:1523 as sysdba;
sys 用戶登錄容器創(chuàng)建表空間以及用戶
-- 查詢數(shù)據(jù)庫(kù)實(shí)例的名稱,也就是 ORACLE_SID 的值 -- 默認(rèn)是 xe
SELECT name FROM v$database;
-- 查看oracle現(xiàn)在的狀態(tài) ,狀態(tài)為 OPEN 則正常
select status from v$instance;
-- 查看所有表空間(相當(dāng)于mysql中的庫(kù))
SELECT name FROM v$tablespace;
-- 創(chuàng)建表空間
-- mkdir -p /home/oracle/escdb
-- chmod 777 /home/oracle/escdb
sqlplus sys/123456@localhost:1521/pdb as sysdba;
CREATE TABLESPACE EDU DATAFILE '/opt/oracle/oradata/SID/PDB/ZS_EDU.dbf' SIZE 100M AUTOEXTEND ON MAXSIZE 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
-- 查詢所有用戶名
-- select username from all_users;
select username from user_users;
-- 創(chuàng)建用戶
CREATE USER IAM IDENTIFIED BY 123456 DEFAULT TABLESPACE idm_spc;
===========================================
CREATE USER ZS_EDU IDENTIFIED BY 123456 DEFAULT TABLESPACE edu;
-- 分配角色
ALTER USER ZS_EDU QUOTA UNLIMITED ON EDU;
GRANT CONNECT, RESOURCE TO IAM;
GRANT CONNECT, RESOURCE TO ZS_EDU;
-- 退出后登錄,普通用戶不需要指定角色類型
sqlplus zs_edu/123456@localhost:1521/pdb;
建表
(復(fù)制sql,替換表名執(zhí)行完畢后,再修改自己想要的字段即可)
-- 查看當(dāng)前數(shù)據(jù)庫(kù)中用戶的所有表
-- SELECT table_name FROM user_tables;
-- 創(chuàng)建表
CREATE TABLE IAM.test_table (
id NUMBER(10,0) VISIBLE NOT NULL,
name VARCHAR2(255 BYTE) VISIBLE,
create_time TIMESTAMP(6) VISIBLE DEFAULT CURRENT_TIMESTAMP NOT NULL,
update_time TIMESTAMP(6) VISIBLE DEFAULT CURRENT_TIMESTAMP NOT NULL,
del NUMBER(1,0) VISIBLE DEFAULT 0 NOT NULL,
primary key(id)
) TABLESPACE IDM_SPC;
-- 設(shè)置字段備注
COMMENT ON COLUMN IAM.test_table.id IS '主鍵id';
COMMENT ON COLUMN IAM.test_table.name IS '名稱';
COMMENT ON COLUMN IAM.test_table.create_time IS '創(chuàng)建時(shí)間';
COMMENT ON COLUMN IAM.test_table.update_time IS '修改時(shí)間';
COMMENT ON COLUMN IAM.test_table.del IS '是否已刪除 0:否 1:是';
-- 設(shè)置主鍵自增 表名:test_table (主鍵已存在就不需要再創(chuàng)建,在創(chuàng)建報(bào)錯(cuò),可以跳過(guò)這步操作)
-- alter table test_table add constraint id primary key (id);
-- 自定義序列名 test_table_seq
create sequence test_table_seq
increment by 1
start with 1
nomaxvalue
nominvalue
nocache;
-- 創(chuàng)建觸發(fā)器
create or replace trigger test_table_seq
before insert on test_table for each row
begin
select test_table_seq.nextval into :new.id from dual;
end;
以下是docker 安裝oracle常用命令
什么是pdb數(shù)據(jù)庫(kù)?什么是CDB?
參照https://blog.csdn.net/weixin_39876739/article/details/111605869
如何切換PDB數(shù)據(jù)庫(kù)
1. 用sys用戶登錄數(shù)據(jù)庫(kù)
2. show con_name;
3. 3、 執(zhí)行select name,open_mode from v$pdbs;
切換為pdb數(shù)據(jù)庫(kù)
alter session set container=ORCLPDB;
查找用戶
select userName from dba_users;
查看有哪些表空間
select tableSpace_name from Dba_tablespaces;
刪除用戶以及表結(jié)構(gòu)(參照:https://blog.csdn.net/daxiang52/article/details/50408312)
drop user ZS_EDU cascade;
5 創(chuàng)建臨時(shí)表空間
create temporary tablespace edu tempfile '/opt/oracle/oradata2' size 50m autoextend on next 50m maxsize 20480m extent management local;
===============================
oracle23
create temporary tablespace eduTem tempfile '/opt/oracle/oradata6' size 50m autoextend on next 50m maxsize 20480m extent management local;
6 創(chuàng)建表空間(查看有哪些表空間select tableSpace_name from Dba_tablespaces;)
create tablespace 表空間名稱 logging datafile '數(shù)據(jù)存儲(chǔ)路徑' size 50m
autoextend on next 50m maxsize 20480m extent management local
=======================================
create tablespace edu1 logging datafile '/opt/oracle/oradata1' size 50m autoextend on next 50m maxsize 20480m extent management local;
----------------------------
oracle23
create tablespace edu logging datafile '/opt/oracle/oradata7' size 50m autoextend on next 50m maxsize 20480m extent management local;
刪除表空間 Oracle刪除表空間語(yǔ)法結(jié)構(gòu):(https://www.php.cn/faq/489137.html)
drop tablespace tab_name [including contents][cascade constraints]
案例、刪除student表空間,并刪除表空間的數(shù)據(jù)文件和完整性
drop tablespace student including contents cascade constraints;
7 創(chuàng)建用戶并指定表空間
create user 用戶名 identified by 密碼 default tablespace 表空間名稱 temporary tablespace 臨時(shí)表空間名稱;
create user ze_edu identified by zsShang886 default tablespace edu temporary tablespace eduTem;
=============================
create user zs_edu identified by zsShang886 default tablespace edu temporary tablespace eduTem;
8給用戶授權(quán)
grant connect,resource,dba to 用戶名;
9 用創(chuàng)建的用戶名進(jìn)行連接。
文章來(lái)源:http://www.zghlxwxcb.cn/news/detail-656982.html
docker文件復(fù)制(參照https://blog.51cto.com/u_16175455/6724342)
docker cp /home/sql/ZS_EDU.sql oracle19c:/home
docker 執(zhí)行sql文件
如果sql文件不在當(dāng)前目錄下,可以使用絕對(duì)路徑:
>@D:/demo.sql
查看表空間有哪些表
select TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLESPACE_NAME='表空間名';
docker 安裝參照
https://blog.csdn.net/arcsin_/article/details/123707618
http://www.inspinia.net/a/14909.html?action=onClick(這個(gè)文章可以系統(tǒng)使得系統(tǒng)登陸)
https://blog.csdn.net/weixin_44032384/article/details/131404349(可以使得docker oracle執(zhí)行sql)推薦這個(gè)老哥文章文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-656982.html
到了這里,關(guān)于docker oracle linux命令執(zhí)行sql的文章就介紹完了。如果您還想了解更多內(nèi)容,請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章,希望大家以后多多支持TOY模板網(wǎng)!