执行sql常见的报错

场景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;

场景03：ORA-00910: specified length too long for its datatype

• 参考场景04 ...

场景04：ORA-14691: Extended character types are not allowed in this table.

• 报错场景1：oracle 11g 创建表指定varchar2字段长度超过4000报错：ORA-00910: specified length too long for its datatype

SQL> create table test01 (name varchar2(4001));

ERROR at line 1:
ORA-00910: specified length too long for its datatype

• 报错场景2：oracle 19c 调整varchar2字段长度超过4000报错：ORA-14691: Extended character types are not allowed in this table.

SQL> create table test01 (name varchar2(2000));
SQL> insert into test01 values ('xxxx');
SQL> commit;
SQL> alter table test01 modify name varchar2(4001);

ERROR at line 1:
ORA-14691: Extended character types are not allowed in this table.

#.确认参数 max_string_size 目前配置为 STANDARD
SQL> show parameter MAX_STRING_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD

• 发生原因：12c之前的版本，varchar2 最大限制为 4000 字节，超出 4000 就会触发 ORA-00910 错误

在12c之前的版本中，varchar2 和 nvarchar2 的最大长度是 4000 bytes，而 raw 是 2000 bytes
在12c之后的版本中，参数 MAX_STRING_SIZE 控制扩展数据类型 extended data type 的最大长度：
  STANDARD 代表12c之前的长度限制，即 varchar2、nvarchar2 4000 bytes，raw 是2000 bytes
  EXTENDED 代表12c 32k strings新特性，varchar2、nvarchar2、raw最大长度32k bytes

• 解决办法1：当需要存储超过4000个字符的字符串时，可以使用 CLOB 数据类型，最大限制为 4GB

SQL> CREATE TABLE test02 (name CLOB);

• 解决办法2：12c及更高的版本，引入了 VARCHAR2(MAX) 数据类型，最大支持 32767 字节

SQL> CREATE TABLE test03 (name varchar2(32767));

• 解决办法3：12c之后的版本，可以设置 max_string_size=extended 开启数据类型扩展，最大支持 32767 字节

#.故障重现
[root@oracle19c ~]# su - oracle
[oracle@oracle19c ~]$ sqlplus / as sysdba
SQL> create table test04 (name varchar2(2000));
SQL> insert into test04 values ('xxxx');
SQL> commit;
SQL> alter table test04 modify name varchar2(4001);

ERROR at line 1:
ORA-14691: Extended character types are not allowed in this table.

#.确认参数 max_string_size 目前配置为 STANDARD
SQL> show parameter MAX_STRING_SIZE;

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

#.以下是开启扩展的步骤
SQL> alter system set max_string_size=extended scope=spfile;
SQL> shutdown immediate;
SQL> startup upgrade;
SQL> @$ORACLE_HOME/rdbms/admin/utl32k.sql;
SQL> shutdown immediate;
SQL> startup;

#.确认参数 max_string_size 已调整为 EXTENDED
SQL> show parameter MAX_STRING_SIZE;

#.检查有没有产生失效对象,返回0则表示没有失效对象
SQL> select count(*) from dba_objects where status<>'VALID';

#.重试不再报错
SQL> alter table test04 modify name varchar2(4001);