注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oracle、MySQL资料及经验

.

 
 
 

日志

 
 

12C使用初步  

2015-02-09 16:22:32|  分类: 默认分类 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

原文:http://www.luocs.com/archives/815.html

CDB和PDB只有一个参数文件,确切说只有CDB有参数文件,所以修改参数只能在CDB修改。
CDB和PDB共用控制文件、undo、redo,其他数据文件pdb有自己单独的。

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.

  评论这张
 
阅读(586)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018