Oracle12c新特性之基本操作

1.  服务器端连接并启动数据库：

sqlplus / as sysdba

startup;

2.  服务器端连接并关闭数据库：

sqlplus / as sysdba

shutdown immediate;

3.  连接指定的数据库

Sqlplus username/passwd@tnsname

4.  查看当前连接的数据库或容器

show con_name

5.  查看当前所有的容器

Select * from v$containers;

6.  查看当前所有的pdb

Select * from v$pdbs;

7.  切换连接到不同的容器

Alter session set container=pdb1;

Alter session set container=cdb$root;

Sqlplus username/passwd@tnsname

8.  打开pdb

Alter pluggable database pdb1 open;

Alter pluggable database all open;

9.  关闭pdb

Alter pluggable database pdb1 close;

Alter pluggable database all close;

10. 创建一个用户

--全局用户

Alter session set container=cdb$root;

Create user c##username identified by test default tablespace userstemporary tablespace temp;

Grant connect to c##test;

--本地用户

Alter session set container=pdb1;

Create user test identified by test default tablespace users temporarytablespace temp;

Grant connect to test;

11.  修改参数

--修改当前容器参数

Alter system set para_name=para_value;

Alter system set para_name=para_value container=current;

--修改所有容器参数

Alter system set para_name=para_value container=all;

--查看能被pdb修改的参数

Select name,value,ispdb_modifiable from v$system_parameter

Where ispdb_modifiable='TRUE';

12.   Listener及tnsname配置范例

--listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = ora12c)

      (ORACLE_HOME =/home/oracle/product/12.1.0/db1)

      (SID_NAME = ora12c)

    )

    (SID_DESC=

      (GLOBAL_DBNAME = pdb1)

      (ORACLE_HOME =/home/oracle/product/12.1.0/db1) 

      (SID_NAME=ora12c)) 

  )

 

LISTENER =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL =TCP)(HOST = localhost)(PORT = 1521))

    )

ADR_BASE_LISTENER = /home/oracle

--tnsnames.ora

ORA12C1 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.1)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = ora12c)

    )

  )

PDB1 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.1)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = pdb1)

    )

  )