安装Oracle 12c已经多日了，都没怎么尝试去使用。今天有时间，就在12c上尝试了下基础的一些东西。
下面就把这个实验过程分享一下。
sys@LUOCS12C> select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0 PL/SQL Release 12.1.0.1.0 - Production 0 CORE 12.1.0.1.0 Production 0 TNS for Linux: Version 12.1.0.1.0 - Production 0 NLSRTL Version 12.1.0.1.0 - Production 0 创建一个表空间，大家想想该表空间创建于CDB还是PDB？ sys@LUOCS12C> create tablespace luocs datafile size 100M autoextend on; Tablespace created. sys@LUOCS12C> select name from v$datafile; NAME ---------------------------------------------------------------------------------------------------- +RACDATA/LUOCS12C/DATAFILE/system.261.819647163 +RACDATA/LUOCS12C/DATAFILE/sysaux.269.819647079 +RACDATA/LUOCS12C/DATAFILE/undotbs1.265.819647271 +RACDATA/LUOCS12C/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/system.267.819647373 +RACDATA/LUOCS12C/DATAFILE/users.268.819647269 +RACDATA/LUOCS12C/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/sysaux.259.819647373 +RACDATA/LUOCS12C/DATAFILE/undotbs2.257.819648905 +RACDATA/LUOCS12C/E0701B9C2C674F73E0439601A8C0CB5C/DATAFILE/system.272.819650131 +RACDATA/LUOCS12C/E0701B9C2C674F73E0439601A8C0CB5C/DATAFILE/sysaux.273.819650131 +RACDATA/LUOCS12C/E0701B9C2C674F73E0439601A8C0CB5C/DATAFILE/users.275.819650827 +RACDATA/LUOCS12C/DATAFILE/luocs.276.819903043 当我们在CDB中使用传统方式创建用户会遇到错误 sys@LUOCS12C> create user luocs identified by oracle default tablespace luocs; create user luocs identified by oracle default tablespace luocs * ERROR at line 1: ORA-65096: invalid common user or role name sys@LUOCS12C> !oerr ora 65096 65096, 00000, "invalid common user or role name" // *Cause: An attempt was made to create a common user or role with a name // that wass not valid for common users or roles. In addition to // the usual rules for user and role names, common user and role // names must start with C## or c## and consist only of ASCII // characters. // *Action: Specify a valid common user or role name. // 根据错误提示了解，在CDB中用户得以C##开头，如下： sys@LUOCS12C> create user c##luocs identified by oracle default tablespace luocs; User created. sys@LUOCS12C> col USERNAME for a10 sys@LUOCS12C> col ACCOUNT_STATUS for a6 sys@LUOCS12C> col LAST_LOGIN for a20 sys@LUOCS12C> set line 150 pages 9999 sys@LUOCS12C> select USERNAME, ACCOUNT_STATUS, PASSWORD_VERSIONS, LAST_LOGIN from dba_users where username='C##LUOCS'; USERNAME ACCOUN PASSWORD_VER LAST_LOGIN ---------- ------ ------------ -------------------- C##LUOCS OPEN 10G 11G sys@LUOCS12C> grant connect to c##luocs; Grant succeeded. sqlplus连接尝试： [oracle@12crac1 ~]$ sqlplus c##luocs/oracle SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 4 14:59:18 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options c##luocs@LUOCS12C> col USERNAME for a15 c##luocs@LUOCS12C> col GRANTED_ROLE for a10 c##luocs@LUOCS12C> select * from USER_ROLE_PRIVS; USERNAME GRANTED_RO ADM DEF OS_ COM --------------- ---------- --- --- --- --- C##LUOCS CONNECT NO YES NO NO 查看下CONNECT角色有什么权限： sys@LUOCS12C> col GRANTEE for a20 sys@LUOCS12C> select GRANTEE,PRIVILEGE from dba_sys_privs where GRANTEE='CONNECT'; GRANTEE PRIVILEGE -------------------- ---------------------------------------- CONNECT SET CONTAINER CONNECT CREATE SESSION 明显发现跟11g比较多了个SET CONTAINER的权限。 11g： SQL> select GRANTEE,PRIVILEGE from dba_sys_privs where GRANTEE='CONNECT'; GRANTEE PRIVILEGE -------------------- -------------------- CONNECT CREATE SESSION
关于CONTAINER容器这个概念，官方介绍：
The data dictionary in each container in a CDB is separate, and the current container is the container whose data dictionary is used for name resolution and for privilege authorization. The current container can be the root or a PDB. Each session has exactly one current container at any point in time, but it is possible for a session to switch from one container to another.
Each container has a unique ID and name in a CDB. You can use the CON_ID and CON_NAME parameters in the USERENV namespace to determine the current container ID and name with the SYS_CONTEXT function.
sys@LUOCS12C> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL; SYS_CONTEXT('USERENV','CON_NAME') ---------------------------------------------------------------------------------------------------- CDB$ROOT 我们可以通过ALTER SESSION SET CONTAINER 指定其他容器，如下： sys@LUOCS12C> select con_id,dbid,NAME,OPEN_MODE from v$pdbs; CON_ID DBID NAME OPEN_MODE ---------- ---------- ------------------------------ ---------- 2 4062250843 PDB$SEED READ ONLY 3 611934728 LUOCS MOUNTED 将Pdb open： sys@LUOCS12C> alter pluggable database luocs open; Pluggable database altered. sys@LUOCS12C> select con_id,dbid,NAME,OPEN_MODE from v$pdbs; CON_ID DBID NAME OPEN_MODE ---------- ---------- ------------------------------ ---------- 2 4062250843 PDB$SEED READ ONLY 3 611934728 LUOCS READ WRITE 切换容器到pdb: sys@LUOCS12C> alter session set container=luocs; Session altered. sys@LUOCS12C> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL; SYS_CONTEXT('USERENV','CON_NAME') ---------------------------------------------------------------------------------------------------- LUOCS 切换到SEED sys@LUOCS12C> ALTER SESSION SET CONTAINER = PDB$SEED; Session altered. sys@LUOCS12C> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL; SYS_CONTEXT('USERENV','CON_NAME') ---------------------------------------------------------------------------------------------------- PDB$SEED 容器切回ROOT sys@LUOCS12C> ALTER SESSION SET CONTAINER = CDB$ROOT; Session altered. sys@LUOCS12C> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL; SYS_CONTEXT('USERENV','CON_NAME') ---------------------------------------------------------------------------------------------------- CDB$ROOT
继续讨论权限问题。
sys@LUOCS12C> grant resource to c##luocs; Grant succeeded. c##luocs@LUOCS12C> create table t1 (id number, name varchar2(20)); Table created. c##luocs@LUOCS12C> insert into t1 values(1,'LUOCS'); insert into t1 values(1,'LUOCS') * ERROR at line 1: ORA-01950: no privileges on tablespace 'LUOCS' sys@LUOCS12C> alter user c##luocs quota unlimited on luocs; alter user c##luocs quota unlimited on luocs * ERROR at line 1: ORA-65048: error encountered when processing the current DDL statement in pluggable database LUOCS ORA-00959: tablespace 'LUOCS' does not exist
在这里我们了解下COMMON USERS和LOCAL USERS。
1）COMMON USERS(普通用户)：经常建立在CDB层，用户名以C##或c##开头；
2）LOCAL USERS(本地用户)：仅建立在PDB层，建立的时候得指定CONTAINER。
上面我们创建的c##luocs明显为COMMON USER，那如何解决上述错误，我们只要指定一下容器就可以了
sys@LUOCS12C> alter user c##luocs quota unlimited on luocs container=current; User altered. 这样我们也可以插入数据 c##luocs@LUOCS12C> insert into t1 values(1,'LUOCS'); 1 row created. c##luocs@LUOCS12C> commit; Commit complete. c##luocs@LUOCS12C> select * from t1; ID NAME ---------- -------------------- 1 LUOCS
下面演示一下创建LOCAL USER:
首先容器指定到PDB： sys@LUOCS12C> alter session set container=luocs; Session altered. 我们都知道PDB有独立的system、sysaux和users、temp表空间 sys@LUOCS12C> select name from v$datafile; NAME ---------------------------------------------------------------------------------------------------- +RACDATA/LUOCS12C/DATAFILE/undotbs1.265.819647271 +RACDATA/LUOCS12C/E0701B9C2C674F73E0439601A8C0CB5C/DATAFILE/system.272.819650131 +RACDATA/LUOCS12C/E0701B9C2C674F73E0439601A8C0CB5C/DATAFILE/sysaux.273.819650131 +RACDATA/LUOCS12C/E0701B9C2C674F73E0439601A8C0CB5C/DATAFILE/users.275.819650827 sys@LUOCS12C> select name from v$tempfile; NAME ---------------------------------------------------------------------------------------------------- +RACDATA/LUOCS12C/E0701B9C2C674F73E0439601A8C0CB5C/TEMPFILE/temp.274.819650243 因此创建用户的时候，我们不能指定CDB中创建的表空间，如下： sys@LUOCS12C> create user luocs identified by oracle quota 50M on luocs; create user luocs identified by oracle quota 50M on luocs * ERROR at line 1: ORA-00959: tablespace 'LUOCS' does not exist 指定PDB中的表空间即可正确创建，注意，这里container指定的不是容器名字，而是写current，或者不写也可以 sys@LUOCS12C> create user luocs identified by oracle quota 50M on users container=current; User created. sys@LUOCS12C> grant connect, resource to luocs; Grant succeeded. 当然我们也可以在PDB中创建独立表空间： sys@LUOCS12C> create tablespace luocs; Tablespace created. sys@LUOCS12C> select name from v$datafile; NAME ---------------------------------------------------------------------------------------------------- +RACDATA/LUOCS12C/DATAFILE/undotbs1.265.819647271 +RACDATA/LUOCS12C/E0701B9C2C674F73E0439601A8C0CB5C/DATAFILE/system.272.819650131 +RACDATA/LUOCS12C/E0701B9C2C674F73E0439601A8C0CB5C/DATAFILE/sysaux.273.819650131 +RACDATA/LUOCS12C/E0701B9C2C674F73E0439601A8C0CB5C/DATAFILE/users.275.819650827 +RACDATA/LUOCS12C/E0701B9C2C674F73E0439601A8C0CB5C/DATAFILE/luocs.277.819908067
下面我们看看12c新特性部分 Increased Size Limit for VARCHAR2, NVARCHAR2, and RAW Data Types
官方如下介绍：
The maximum size of the VARCHAR2, NVARCHAR2, and RAW data types has been increased from 4,000 to 32,767 bytes.
Increasing the allotted size for these data types allows users to store more information in character data types before switching to large objects (LOBs). This is especially useful for brief textual data types and the capabilities to build indexes on these types of columns.
也就是说，在12c中varchar2、NVARCHAR2和RAW类型从原先的4000字节增长到32767字节了。
但这取决于参数MAX_STRING_SIZE的设置，默认为STANDARD，表示依然是4000字节上限。
sys@LUOCS12C> show parameter MAX_STRING_SIZE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_string_size string STANDARD luocs@LUOCS> create table t2 (col1 varchar2(4001)); create table t2 (col1 varchar2(4001)) * ERROR at line 1: ORA-00910: specified length too long for its datatype
该参数设置为EXTENDED，上述类型才能支持到32767字节。
但该参数并不是简单使用alter去修改就能解决的事情，如下：
sys@LUOCS12C> alter system set MAX_STRING_SIZE=EXTENDED; alter system set MAX_STRING_SIZE=EXTENDED * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration
修改该参数必须遵循如下几个步骤：
1）关闭数据库
2）数据库重新启动到UPGRADE模式
3）修改MAX_STRING_SIZE参数，指定为EXTENDED
4）执行脚本：$ORACLE_HOME/rdbms/admin/utl32k.sql
5）数据库正常重启
OK，下面演示一下，我这里是12c RAC环境，目前仅开启节点1：
[grid@12crac1 ~]$ crsctl stat res -t -------------------------------------------------------------------------------- Name Target State Server State details -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.LISTENER.lsnr ONLINE ONLINE 12crac1 STABLE ora.RACCRS.dg ONLINE ONLINE 12crac1 STABLE ora.RACDATA.dg ONLINE ONLINE 12crac1 STABLE ora.RACFRA.dg ONLINE ONLINE 12crac1 STABLE ora.asm ONLINE ONLINE 12crac1 Started,STABLE ora.net1.network ONLINE ONLINE 12crac1 STABLE ora.ons ONLINE ONLINE 12crac1 STABLE -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.12crac1.vip 1 ONLINE ONLINE 12crac1 STABLE ora.12crac2.vip 1 ONLINE INTERMEDIATE 12crac1 FAILED OVER,STABLE ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE 12crac1 STABLE ora.LISTENER_SCAN2.lsnr 1 ONLINE ONLINE 12crac1 STABLE ora.LISTENER_SCAN3.lsnr 1 ONLINE ONLINE 12crac1 STABLE ora.MGMTLSNR 1 ONLINE ONLINE 12crac1 169.254.88.173 192.1 68.80.150,STABLE ora.cvu 1 ONLINE ONLINE 12crac1 STABLE ora.luocs12c.db 1 OFFLINE OFFLINE Instance Shutdown,ST ABLE 2 ONLINE ONLINE 12crac1 Open,STABLE ora.mgmtdb 1 ONLINE ONLINE 12crac1 Open,STABLE ora.oc4j 1 ONLINE ONLINE 12crac1 STABLE ora.scan1.vip 1 ONLINE ONLINE 12crac1 STABLE ora.scan2.vip 1 ONLINE ONLINE 12crac1 STABLE ora.scan3.vip 1 ONLINE ONLINE 12crac1 STABLE -------------------------------------------------------------------------------- 关闭数据库： [grid@12crac1 ~]$ srvctl stop instance -d luocs12c -i luocs12c1 数据库重新启动到UPGRADE模式 为了启动到UPGRADE模式，RAC中应如下操作： [oracle@12crac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 4 17:14:30 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. idle> startup mount ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2293880 bytes Variable Size 750784392 bytes Database Buffers 75497472 bytes Redo Buffers 6529024 bytes Database mounted. idle> show parameter cluster_database NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cluster_database boolean TRUE cluster_database_instances integer 2 idle> alter system set cluster_database=false scope=spfile; System altered. idle> startup upgrade; ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2293880 bytes Variable Size 725618568 bytes Database Buffers 100663296 bytes Redo Buffers 6529024 bytes Database mounted. Database opened. 修改MAX_STRING_SIZE参数，指定为EXTENDED MAX_STRING_SIZE参数为静态参数 idle> alter system set MAX_STRING_SIZE=EXTENDED scope=spfile; System altered. 执行脚本：$ORACLE_HOME/rdbms/admin/utl32k.sql idle> @$ORACLE_HOME/rdbms/admin/utl32k -- 该脚本执行时间稍长，请耐心等待 容器切换到SEED idle> ALTER SESSION SET CONTAINER = PDB$SEED; Session altered. idle> @$ORACLE_HOME/rdbms/admin/utl32k 启动pdb luocs sys@LUOCS12C> alter pluggable database luocs open upgrade; Pluggable database altered. idle> ALTER SESSION SET CONTAINER = luocs; Session altered. idle> @$ORACLE_HOME/rdbms/admin/utl32k 如果pdb上没执行过该脚本，在打开pdb的时候将会报： sys@LUOCS12C> alter pluggable database luocs open; alter pluggable database luocs open * ERROR at line 1: ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration 数据库正常重启 将RAC集群激活： idle> alter system set cluster_database=true scope=spfile; System altered. idle> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. [oracle@12crac1 ~]$ srvctl start instance -d luocs12c -i luocs12c1
就这样，数据库varchar2、nvarchar2和raw类型支持32k长度字符了。
举个例子看看：
sys@LUOCS12C> select con_id,dbid,NAME,OPEN_MODE from v$pdbs; CON_ID DBID NAME OPEN_MODE ---------- ---------- ------------------------------ ---------- 2 4062250843 PDB$SEED READ ONLY 3 611934728 LUOCS MOUNTED 我们启动pdb sys@LUOCS12C> alter pluggable database luocs open; Pluggable database altered. 连接pdb并创建表 sys@LUOCS12C> conn luocs/oracle@luocs Connected. luocs@LUOCS> create table t1 (col1 varchar2(32767)); Table created.
– OK。
下面再看一下另一个新特性：Native SQL Support for Query Row Limits and Row Offsets
官方介绍如下：
The FETCH FIRST and OFFSET clauses provides native SQL language support to limit the number of rows returned and to specify a starting row for the return set.
Many queries need to limit the number of rows returned or offset the starting row of the results. For example, top-N queries sort their result set and then return only the first n rows. FETCH FIRST and OFFSET simplify syntax and comply with the ANSI SQL standard.
我们就简单做个测试：
luocs@LUOCS> create table t2 (col number); Table created. luocs@LUOCS> insert into t2 select level from dual connect by level <=20; 20 rows created. luocs@LUOCS> commit; Commit complete. luocs@LUOCS> select * from t2; COL ---------- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 20 rows selected. luocs@LUOCS> select col from t2 fetch first 10 rows only; COL ---------- 1 2 3 4 5 6 7 8 9 10 10 rows selected. luocs@LUOCS> select col from t2 order by col desc fetch first 10 rows only; COL ---------- 20 19 18 17 16 15 14 13 12 11 10 rows selected. luocs@LUOCS> select col from t2 offset 5 rows fetch next 10 rows only; COL ---------- 6 7 8 9 10 11 12 13 14 15 10 rows selected. 传统我们通过如下方式： luocs@LUOCS> select col from 2 (select col, rownum as rn from 3 (select col from t2) where rownum <=15) 4 where rn >=6; COL ---------- 6 7 8 9 10 11 12 13 14 15 10 rows selected.
OK。
关于TOP-N查询，更详细了解请阅官方文档，这里再提供一篇：
5181
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
