版本

Oracle19c 多了一个 CDB 和 PDB的概念（从12C开始出现） CDB 与 PDB是 Oracle 12C 引入的新特性，在 ORACLE 12C 数据库引入的多租用户环境（Multitenant Environment）中，允许一个数据库容器（CDB）承载多个可插拔数据库（PDB）。 CDB 全称 ContainerDatabase，中文翻译为数据库容器， PDB 全称为 Pluggable Database，即可插拔数据库。

注意：cdb 创建用户用户名前面必须要加上 C##或 c##，不然会返回一个错误。

修改字符集

# 查看字符集
select userenv('language') from dual;
select * from nls_database_parameters 
where parameter in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');
​
# 关闭数据库实例
shutdown immediate;
# 打开控制文件
startup mount;
# 开启限制会话模式
alter system enable restricted session;
# 设定job队列可以启动的进程数
alter system set JOB_QUEUE_PROCESSES=0;
# 禁用队列监视器协调器
alter system set  AQ_TM_PROCESSES=0;
# 修改并打开数据库
alter database open;
# 修改数据库字符集
ALTER DATABASE CHARACTER SET ZHS16GBK;
# 当提示我们：新字符集必须为旧字符集的超集合，使用以下命令：
ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;
# 设置完成后，关闭数据库
shutdown immediate;
# 再次打开数据库
startup open;

建库

以 sysdba 登录连接：

C:\Users\15195>sqlplus/nolog
​
SQL> conn as sysdba;

sqlplus / as sysdba
​
#修改sys用户密码
ALTER USER sys IDENTIFIED BY "Jiutian.123";
​
# 或者交互式更改
password sys
​
# 查询当前数据库名
show con_name
​
# 查询PDB数据库名
select name,open_mode from v$pdbs;
​
# 切换到PDB
alter session set container=PDB;
# 切换回CDB数据库
alter session set container=CDB$ROOT;
​
# 删除表空间
drop tablespace JTSERVICESPACE including contents and datafiles cascade constraint;
​
# 创建表空间 (CDB)
create tablespace JTSERVICESPACE 
datafile 'D:/envirment/Oracle/oradata/orcl/JTSERVICESPACE.dbf' 
size 1024m 
autoextend on 
next 100m 
maxsize unlimited;
​
# 在CDB内创建用户分配表空间时，所分配的表空间必须在PDB和CDB中同时存在，否则会报错
# 切换到PDB
alter session set container=PDB;
​
# 创建表空间 (PDB),数据文件名不能一样
create tablespace JTSERVICESPACE 
datafile 'D:/envirment/Oracle/oradata/orcl/JTSERVICESPACE_p.dbf' 
size 1024m 
autoextend on 
next 100m 
maxsize unlimited;
​
# 切换回CDB数据库
alter session set container=CDB$ROOT;
​
#创建用户 ()
create user c##jiutian identified by "Jiutian.123"  default tablespace JTSERVICESPACE profile default account unlock;
​
#给用户授权
grant connect, resource to c##jiutian;
grant unlimited tablespace to c##jiutian;

注意：

• 密码默认有效期为 180 天，可设置永不过期：

• CDB 创建用户名必须带 C##或 c##（公共用户报错），而 PDB 创建用户名不能带 C## 和 c##（本地用户报错）。

参考：

修改密码、设置密码永久有效

创建表空间和用户

建表

DECLARE
    TABLE_EXISTS NUMBER;
BEGIN
SELECT COUNT(*)
INTO TABLE_EXISTS
FROM USER_TABLES
WHERE TABLE_NAME = 'SYS_USER';
​
IF TABLE_EXISTS = 0 THEN
EXECUTE IMMEDIATE '
        CREATE TABLE SYS_USER
    (
        ID          NUMBER(20)   NOT NULL PRIMARY KEY,
        USERNAME    VARCHAR2(30) NOT NULL,
        PASSWORD    VARCHAR2(100) NOT NULL,
        NICKNAME    VARCHAR2(30) NOT NULL,
        EMAIL       VARCHAR2(50)  DEFAULT NULL,
        PHONE       VARCHAR2(15)  DEFAULT NULL,
        SEX         NUMBER(1)     DEFAULT 0,
        AVATAR      VARCHAR2(100) DEFAULT NULL,
        STATUS      NUMBER(1)     DEFAULT 0,
        CREATE_TIME DATE          DEFAULT NULL,
        UPDATE_TIME DATE          DEFAULT NULL,
        IS_DELETED  NUMBER(1)     DEFAULT 0,
        CONSTRAINT IDX_UNAME UNIQUE (USERNAME)
    )
';
END IF;
END;
​
COMMENT ON TABLE SYS_USER IS '用户表';
COMMENT ON COLUMN SYS_USER.ID IS '主键';
COMMENT ON COLUMN SYS_USER.USERNAME IS '用户账号';
COMMENT ON COLUMN SYS_USER.PASSWORD IS '密码';
COMMENT ON COLUMN SYS_USER.NICKNAME IS '用户昵称';
COMMENT ON COLUMN SYS_USER.EMAIL IS '用户邮箱';
COMMENT ON COLUMN SYS_USER.PHONE IS '手机号码';
COMMENT ON COLUMN SYS_USER.SEX IS '用户性别（0未知 1男 2女）';
COMMENT ON COLUMN SYS_USER.AVATAR IS '头像地址';
COMMENT ON COLUMN SYS_USER.STATUS IS '帐号状态（0正常 1停用）';
COMMENT ON COLUMN SYS_USER.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN SYS_USER.UPDATE_TIME IS '更新时间';
COMMENT ON COLUMN SYS_USER.IS_DELETED IS '逻辑删除（0代表存在 1代表删除）';
​
-- 创建自增序列
DECLARE
    SEQUENCE_COUNT NUMBER;
BEGIN
SELECT COUNT(*)
INTO SEQUENCE_COUNT
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'SEQ_USER_ID';
​
IF SEQUENCE_COUNT = 0 THEN
EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_USER_ID START WITH 1 INCREMENT BY 1';
END IF;
END;
​
​
-- 创建自增ID触发器
CREATE OR REPLACE TRIGGER SYS_USER_TRIGGER
    BEFORE INSERT ON SYS_USER FOR EACH ROW
BEGIN
SELECT SEQ_USER_ID.NEXTVAL
INTO : NEW.ID
FROM DUAL;
END;
​
-- DROP TRIGGER SYS_USER_TRIGGER;
-- DROP SEQUENCE SEQ_USER_ID;

坑

自增ID

使用序列+触发器实现自增id时，truncate table 不会重置自增序列的 next 值，且又因为其无法激活触发器，需要手动进行重置。

-- 创建重置自增序列的存储过程
CREATE OR REPLACE PROCEDURE RESET_SEQ(P_SEQ_NAME IN VARCHAR2) AS
L_VAL NUMBER;
BEGIN
    EXECUTE IMMEDIATE 'SELECT ' || P_SEQ_NAME || '.NEXTVAL FROM DUAL' INTO L_VAL;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || P_SEQ_NAME || ' INCREMENT BY -' || L_VAL || ' MINVALUE 0';
    EXECUTE IMMEDIATE 'SELECT ' || P_SEQ_NAME || '.NEXTVAL FROM DUAL' INTO L_VAL;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || P_SEQ_NAME || ' INCREMENT BY 1 MINVALUE 0';
END;
​
-- 查看序列的下个值，执行会修改
-- SELECT SEQ_USER_ID.NEXTVAL from dual;
​
-- 重置自增序列
CALL RESET_SEQ('SEQ_USER_ID');
​
-- DROP PROCEDURE RESET_SEQ;

大小写问题

• CREATE TABLE sys_user -> 表名：SYS_USER

• CREATE TABLE "sys_user" -> 表名：sys_user 坑，需要 SELECT * FROM "sys_user"

• CREATE TABLE "SYS_USER" -> 表名：SYS_USER

• CREATE TABLE SYS_USER -> 表名：SYS_USER

插入数据

INSERT all 
INTO sys_user (username, password, nickname, create_time, update_time ) VALUES ('lisi', '123456', '666','', '')
INTO sys_user (username, password, nickname, create_time, update_time ) VALUES ('zhangsan', '123456', '777', null, null )
SELECT 1 FROM DUAL;