ORACLE-12C简单了解

分手后的思念是犯贱 2022-08-11 00:42 45阅读 0赞

dbca时，已经建PDB一个  
1：SYS@ orcl>select \* from v$version;  
BANNER                                                 CON\_ID  
\-------------------------------------------------- ----------  
Oracle Database 12c Enterprise Edition Release 12.          0  
1.0.1.0 - 64bit Production  
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  
   
2:   12C中有CDB与PDB区别，用户有Common User、Local User区别  
SYS@ orcl>select DBID,NAME,OPEN\_MODE,CON\_ID,CON\_DBID from v$database;  
      DBID NAME      OPEN\_MODE                CON\_ID   CON\_DBID  
\---------- --------- -------------------- ---------- ----------  
1367207217 ORCL      READ WRITE                    0 1367207217  
   
SYS@ orcl>select CON\_ID,DBID,NAME ,open\_mode from v$pdbs;  
    CON\_ID       DBID NAME                           OPEN\_MODE  
\---------- ---------- ------------------------------ ----------  
         2 4082003135 PDB$SEED                       READ ONLY       -------SEED容器-相当于模板  
         3 2286601015 PDBORCL                        READ WRITE      -------PDB  
   
SYS@ orcl>show con\_name                        ----------为CDB  
CON\_NAME  
\------------------------------  
CDB$ROOT  
SYS@ orcl>create user test identified by test;           ---test为Local User，不能再CDB中建立  
create user test identified by test  
            \*  
ERROR at line 1:  
ORA-65096: invalid common user or role name  
  
  
SYS@ orcl>create user c\#\#test identified by test;-------c\#\#+username 为Common User可以在CDB中建立  
User created.  
   
3：从CDB切换至PDB查看c\#\#test 用户  
SYS@ orcl>alter session set container =pdborcl;           ------切换PDB  
Session altered.  
SYS@ orcl>show con\_name  
CON\_NAME  
\------------------------------  
PDBORCL  
   
SYS@ orcl>select username from dba\_users where username like 'C\#\#TEST';  
USERNAME  
\----------------        ----CDB中建立Common User可以在PDB中查到，反之Local User则不可以；  
C\#\#TEST

4：安装数据库中，选择创建sample，安装完没有看到scott用户，从新创建sample

netca配置监听与TNSoracle@lx03 admin\]$ more listener.ora  
\# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db\_1/network/ad  
min/listener.ora  
\# Generated by Oracle configuration tools.  
LISTENER =  
  (DESCRIPTION\_LIST =  
    (DESCRIPTION =  
      (ADDRESS = (PROTOCOL = TCP)(HOST = lx03)(PORT = 1521))  
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))  
    )  
  )  
\[oracle@lx03 admin\]$ more tnsnames.ora  
\# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db\_1/network/ad  
min/tnsnames.ora  
\# Generated by Oracle configuration tools.  
PDBORCL =  
  (DESCRIPTION =  
    (ADDRESS\_LIST =  
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.97)(PORT = 1521))  
    )  
    (CONNECT\_DATA =  
      (SERVICE\_NAME = pdborcl)  
    )  
  )  
LISTENER\_ORCL =  
  (ADDRESS = (PROTOCOL = TCP)(HOST = lx03)(PORT = 1521))  
  
  
ORCL =  
  (DESCRIPTION =  
    (ADDRESS = (PROTOCOL = TCP)(HOST = lx03)(PORT = 1521))  
    (CONNECT\_DATA =  
      (SERVER = DEDICATED)  
      (SERVICE\_NAME = orcl)  
    )  
  )  
   
修改\[oracle@lx03 admin\]$ vi /u01/app/oracle/product/12.1.0/db\_1/rdbms/admin/utlsampl.sql  
\-----------------------------------------  
DROP PUBLIC SYNONYM PARTS;  
CONNECT SCOTT/tiger@pdborcl           -----添加链接至PDB数据库的连接串  
CREATE TABLE DEPT  
\------------------------------------------  
   
SYS@ orcl>@?/rdbms/admin/utlsampl.sql                    -----------------执行脚本  
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production  
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options  
\[oracle@lx03 admin\]$ sqlplus scott/tiger@pdborcl  
SCOTT@ pdborcl>select tname from tab;  
TNAME  
\-----------------------  
DEPT  
EMP  
BONUS  
SALGRADE  
   
  
  
5：关闭开启PDB数据库；SYS@ orcl>select name ,open\_mode from v$pdbs;  
NAME                           OPEN\_MODE  
\------------------------------ ----------  
PDB$SEED                       READ ONLY  
PDBORCL                        READ WRITE              ----------PDB库开启状态  
   
SYS@ orcl>alter session set container=pdborcl;  
Session altered.  
SYS@ orcl>show con\_name  
CON\_NAME  
\------------------------------  
PDBORCL  
SYS@ orcl>shutdown immediate                        -------在PDB中执行，同之前版本数据库操作相同，  
Pluggable Database closed.  
SYS@ orcl>select name ,open\_mode from v$pdbs;  
NAME                           OPEN\_MODE  
\------------------------------ ----------  
PDB$SEED                       READ ONLY  
PDBORCL                        MOUNTED                           ----------------PDB库关闭  
SYS@ orcl>alter pluggable database  pdborcl  (all) close;        -----可以在CDB中关闭PDB数据库