[草稿]执行sql常见的报错

[TOC]

场景01：ORA-65096: invalid common user or role name

• 报错场景：使用sqlplus执行创建用户报错：ORA-65096: invalid common user or role name

[root@oracle21c ~]# su - oracle -c "sqlplus /nolog"
SQL> conn sys/oracle as sysdba;
SQL> create user dba_backup identified by Admin_147 default tablespace tbs_backup temporary tablespace TEMP;

ERROR at line 1:
ORA-65096: invalid common user or role name

• 解决办法1：从12c开始在默认cdb容器中创建用户必须以C##或者c##开头，通过设置 "_ORACLE_SCRIPT"=true 可临时绕过CDB中创建用户必须以 C## 开头的限制。请注意，此设置只在当前会话中有效，新会话仍然会遵循默认规则

SQL> conn sys/oracle as sysdba;
SQL> alter session set "_ORACLE_SCRIPT"=true;

SQL> create user dba_backup identified by Admin_147;
User created.

• 解决办法2：先切换到pdb容器，再创建用户。请注意，重新登录超级管理员 sysdba 之后，当前容器会变回默认的 CDB

SQL> --.1.用sysdba登录,否则权限不够
SQL> conn sys/oracle as sysdba;

SQL> --.2.确认当前容器为 CDB
SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

SQL> --.3.查看已经创建的pdb容器
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        MOUNTED

SQL> --.4.打开 ORCLPDB 容器
SQL> alter pluggable database ORCLPDB open;

SQL> --.5.将当前容器切换到指定的PDB容器
SQL> alter session set container=ORCLPDB;

SQL> --.6.确认当前容器为 ORCLPDB
SQL> show con_name;

CON_NAME
------------------------------
ORCLPDB

SQL> --.7.之后就可以正常创建用户了,不过该用户不在cdb容器,而是在pdb容器下面
SQL> create user dba_backup_222 identified by Admin_147;

SQL> --.8.新用户无法以普通用户登录,必须以超级管理员sysdba进行登录才行
SQL> conn dba_backup_222/Admin_147 as sysdba;

场景02：ORA-01950: no privileges on tablespace 'USERS'

• 报错场景：普通用户插入数据报错：ORA-01950: no privileges on tablespace 'USERS'

SQL> conn pay01/Admin_147@orcl;
SQL> CREATE TABLE tab01(id number(6,0) primary key,name varchar2(10),city varchar(10));
SQL> INSERT INTO tab01 VALUES(1,'sam','beijing');

ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

• 解决办法：用 sysdba 登录，给该用户在 USERS 表空间分配配额

SQL> conn sys/oracle as sysdba;
SQL> ALTER USER pay01 QUOTA UNLIMITED ON USERS;