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)

    )

  )