oracle19c docker镜像

docker pull registry.cn-hangzhou.aliyuncs.com/zhuyijun/oracle:19c

docker-compose

  oracle19c:
    container_name: oracle19c
    image: "registry.cn-hangzhou.aliyuncs.com/zhuyijun/oracle:19c"
    restart: always
    privileged: true
    ports:
      - 1521:1521
      - 5500:5500
    volumes:
      - /etc/localtime:/etc/localtime
      - $PWD/oracle19c/oradata:/opt/oracle/oradata

查看日志获取密码：

ORACLE PASSWORD FOR SYS, SYSTEM AND PDBADMIN: JKF+Zf3e6YM=1

修改密码

docker exec oracle19c ./setPassword.sh oracle

使用Navicat Premium登录sys as sysdba需要注意用户名填写sys，然后在高级里面选择角色。

Navicat Premium连接sqlserver不用填写端口

CDB/PDB

ORCLCDB
ORCLPDB1

查询PDB

SELECT con_id, dbid, NAME, OPEN_MODE FROM v$pdbs;
ALTER SESSION SET container = ORCLPDB1;

创建表空间

CREATE tablespace acgist datafile '/opt/oracle/oradata/ORCLCDB/acgist.dbf' SIZE 1024M autoextend ON NEXT 128M;

创建用户

CREATE USER user IDENTIFIED BY 123456 DEFAULT tablespace acgist;

授权

GRANT CONNECT, RESOURCE TO user;
GRANT CREATE ANY sequence TO user;
GRANT CREATE ANY TABLE TO user;
GRANT DELETE ANY TABLE TO user;
GRANT INSERT ANY TABLE TO user;
GRANT SELECT ANY TABLE TO user;
GRANT UPDATE ANY TABLE TO user;
GRANT CREATE ANY VIEW TO user;
GRANT unlimited tablespace TO user;
GRANT execute ANY PROCEDURE TO user;
GRANT dba TO user;

删除

-- DROP tablespace acgist;
-- DROP tablespace acgist including contents and datafiles;
-- DROP user name;

Linux连接Oracle19c问题

Caused by: oracle.net.ns.NetException: Got minus one from a read call
	at oracle.net.ns.NSProtocolNIO.doSocketRead(NSProtocolNIO.java:557)
	at oracle.net.ns.NIOPacket.readHeader(NIOPacket.java:258)
	at oracle.net.ns.NIOPacket.readPacketFromSocketChannel(NIOPacket.java:190)
	at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:132)
	at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:105)
	at oracle.net.ns.NIONSDataChannel.readDataFromSocketChannel(NIONSDataChannel.java:91)
	at oracle.net.ano.AnoCommNIO.p(Unknown Source)
	at oracle.net.ano.AnoCommNIO.e(Unknown Source)
	at oracle.net.ano.AnoComm.readUB4(Unknown Source)
	at oracle.net.ano.Ano.c(Unknown Source)
	at oracle.net.ano.Ano.negotiation(Unknown Source)
	at oracle.net.ns.NSProtocol.connect(NSProtocol.java:368)
	at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1600)
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:592)
	... 39 common frames omitted

这个问题非常奇怪，window上面没有问题，linux就有问题，解决办法修改连接：

# 错误连接
url: jdbc:oracle:thin:@192.168.1.100:1521/acgist
# 正确连接
url: jdbc:oracle:thin:@tcp://192.168.1.100:1521/acgist?oracle.net.disableOob=true 

数据导入

使用sqlplus连接进入数据库，然后使用@/sql.sql执行导入。
如果提示Enter value for语句里面含有转义符，关闭转义符set define off。

sqlplus连接命令sqlplus username/password@host:port/服务名称

重要：完了千万不要忘了commit

乱码

select userenv('language') from dual;
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8