Oracle12c使用初体验

来源：互联网发布：java mvc框架下载架包编辑：程序博客网时间：2024/05/16 22:59

安装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              0PL/SQL Release 12.1.0.1.0 - Production                                                    0CORE    12.1.0.1.0      Production                                                                0TNS for Linux: Version 12.1.0.1.0 - Production                                            0NLSRTL 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 namesys@LUOCS12C> !oerr ora 6509665096, 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 a10sys@LUOCS12C> col ACCOUNT_STATUS for a6sys@LUOCS12C> col LAST_LOGIN for a20sys@LUOCS12C> set line 150 pages 9999sys@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 11Gsys@LUOCS12C> grant connect to c##luocs;Grant succeeded.sqlplus连接尝试：[oracle@12crac1 ~]$ sqlplus c##luocs/oracleSQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 4 14:59:18 2013Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Advanced Analytics and Real Application Testing optionsc##luocs@LUOCS12C> col USERNAME for a15c##luocs@LUOCS12C> col GRANTED_ROLE for a10c##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 a20sys@LUOCS12C> select GRANTEE,PRIVILEGE  from dba_sys_privs where GRANTEE='CONNECT';GRANTEE              PRIVILEGE-------------------- ----------------------------------------CONNECT              SET CONTAINERCONNECT              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切换到SEEDsys@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容器切回ROOTsys@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 LUOCSORA-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.819650827sys@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      STANDARDluocs@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 invalidORA-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                  STABLEora.RACCRS.dg               ONLINE  ONLINE       12crac1                  STABLEora.RACDATA.dg               ONLINE  ONLINE       12crac1                  STABLEora.RACFRA.dg               ONLINE  ONLINE       12crac1                  STABLEora.asm               ONLINE  ONLINE       12crac1                  Started,STABLEora.net1.network               ONLINE  ONLINE       12crac1                  STABLEora.ons               ONLINE  ONLINE       12crac1                  STABLE--------------------------------------------------------------------------------Cluster Resources--------------------------------------------------------------------------------ora.12crac1.vip      1        ONLINE  ONLINE       12crac1                  STABLEora.12crac2.vip      1        ONLINE  INTERMEDIATE 12crac1                  FAILED OVER,STABLEora.LISTENER_SCAN1.lsnr      1        ONLINE  ONLINE       12crac1                  STABLEora.LISTENER_SCAN2.lsnr      1        ONLINE  ONLINE       12crac1                  STABLEora.LISTENER_SCAN3.lsnr      1        ONLINE  ONLINE       12crac1                  STABLEora.MGMTLSNR      1        ONLINE  ONLINE       12crac1                  169.254.88.173 192.1                                                             68.80.150,STABLEora.cvu      1        ONLINE  ONLINE       12crac1                  STABLEora.luocs12c.db      1        OFFLINE OFFLINE                               Instance Shutdown,ST                                                             ABLE      2        ONLINE  ONLINE       12crac1                  Open,STABLEora.mgmtdb      1        ONLINE  ONLINE       12crac1                  Open,STABLEora.oc4j      1        ONLINE  ONLINE       12crac1                  STABLEora.scan1.vip      1        ONLINE  ONLINE       12crac1                  STABLEora.scan2.vip      1        ONLINE  ONLINE       12crac1                  STABLEora.scan3.vip      1        ONLINE  ONLINE       12crac1                  STABLE--------------------------------------------------------------------------------关闭数据库：[grid@12crac1 ~]$ srvctl stop instance -d luocs12c -i luocs12c1数据库重新启动到UPGRADE模式为了启动到UPGRADE模式，RAC中应如下操作：[oracle@12crac1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 4 17:14:30 2013Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to an idle instance.idle> startup mountORACLE instance started.Total System Global Area  835104768 bytesFixed Size                  2293880 bytesVariable Size             750784392 bytesDatabase Buffers           75497472 bytesRedo Buffers                6529024 bytesDatabase mounted.idle> show parameter cluster_databaseNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------cluster_database                     boolean     TRUEcluster_database_instances           integer     2idle> alter system set cluster_database=false scope=spfile;System altered.idle> startup upgrade;ORACLE instance started.Total System Global Area  835104768 bytesFixed Size                  2293880 bytesVariable Size             725618568 bytesDatabase Buffers          100663296 bytesRedo Buffers                6529024 bytesDatabase mounted.Database opened.修改MAX_STRING_SIZE参数，指定为EXTENDEDMAX_STRING_SIZE参数为静态参数idle> alter system set MAX_STRING_SIZE=EXTENDED scope=spfile;System altered.执行脚本：$ORACLE_HOME/rdbms/admin/utl32k.sqlidle> @$ORACLE_HOME/rdbms/admin/utl32k-- 该脚本执行时间稍长，请耐心等待容器切换到SEEDidle> ALTER SESSION SET CONTAINER = PDB$SEED;Session altered.idle> @$ORACLE_HOME/rdbms/admin/utl32k启动pdb luocssys@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 immediateDatabase 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我们启动pdbsys@LUOCS12C> alter pluggable database luocs open;Pluggable database altered.连接pdb并创建表sys@LUOCS12C> conn luocs/oracle@luocsConnected.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        2020 rows selected.luocs@LUOCS> select col from t2 fetch first 10 rows only;       COL----------         1         2         3         4         5         6         7         8         9        1010 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        1110 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        1510 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        1510 rows selected.

OK。

关于TOP-N查询，更详细了解请阅官方文档，这里再提供一篇：

Row Limiting Clause for Top-N Queries in Oracle Database 12c Release 1 (12.1)