环境：oracle12c
1、常用字典
DBA_xxx All objects in the root or a pluggable database ALL_xxx Objects accessible by the current user in a PDB USER_xxx Objects owned by the current user in a PDB [oracle@12c ~]$ sqlplus sys/oracle@192.168.95.150:1521/pdb01.oracle.com as sysdba SQL> SELECT file_name FROM dba_data_files; FILE_NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/YH/AC560BDC2E9C51A9E053965FA8C00201/datafile/o1_mf_system_hlw5ttv4_.dbf /u01/app/oracle/oradata/YH/AC560BDC2E9C51A9E053965FA8C00201/datafile/o1_mf_sysaux_hlw5ttvb_.dbf /u01/app/oracle/oradata/YH/AC560BDC2E9C51A9E053965FA8C00201/datafile/o1_mf_undotbs1_hlw5ttvc_.dbf /u01/app/oracle/oradata/YH/AC560BDC2E9C51A9E053965FA8C00201/datafile/o1_mf_users_hlw5v9dm_.dbf [oracle@12c oradata]$ sqlplus sys/oracle@192.168.95.150:1521/yh.oracle.com as sysdba SQL> SELECT file_name FROM dba_data_files; FILE_NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/YH/datafile/o1_mf_system_hlw5gytl_.dbf /u01/app/oracle/oradata/YH/datafile/o1_mf_sysaux_hlw5jdj2_.dbf /u01/app/oracle/oradata/YH/datafile/o1_mf_undotbs1_hlw5k5x7_.dbf /u01/app/oracle/oradata/YH/datafile/o1_mf_users_hlw5k769_.dbf CDB_xxx All objects in the CDB (new column CON_ID) SQL> SELECT file_name,con_id FROM cdb_data_files FILE_NAME CON_ID ---------------------------------------------------------------------------------------------------- ---------- /u01/app/oracle/oradata/YH/datafile/o1_mf_system_hlw5gytl_.dbf 1 /u01/app/oracle/oradata/YH/datafile/o1_mf_sysaux_hlw5jdj2_.dbf 1 /u01/app/oracle/oradata/YH/datafile/o1_mf_undotbs1_hlw5k5x7_.dbf 1 /u01/app/oracle/oradata/YH/datafile/o1_mf_users_hlw5k769_.dbf 1 /u01/app/oracle/oradata/YH/AC560BDC2E9C51A9E053965FA8C00201/datafile/o1_mf_system_hlw5ttv4_.dbf 3 /u01/app/oracle/oradata/YH/AC560BDC2E9C51A9E053965FA8C00201/datafile/o1_mf_sysaux_hlw5ttvb_.dbf 3 /u01/app/oracle/oradata/YH/AC560BDC2E9C51A9E053965FA8C00201/datafile/o1_mf_undotbs1_hlw5ttvc_.dbf 3 /u01/app/oracle/oradata/YH/AC560BDC2E9C51A9E053965FA8C00201/datafile/o1_mf_users_hlw5v9dm_.dbf 3
2、cdb、pdb服务查看
Every PDB has a default service. SQL> SELECT name, pdb FROM cdb_services; NAME PDB -------------------- -------------------- SYS$BACKGROUND CDB$ROOT SYS$USERS CDB$ROOT yhXDB CDB$ROOT yh.oracle.com CDB$ROOT pdb01.oracle.com PDB01
3、cdb、pdb链接
[oracle@12c ~]$ lsnrctl status Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.95.150)(PORT=1521))) 使用EZ方式连接 sqlplus sys/oracle@192.168.95.150:1521/yh.oracle.com as sysdba sqlplus sys/oracle@192.168.95.150:1521/pdb01.oracle.com as sysdba 编辑tnsnames.ora文件 YH = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.95.150)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = yh.oracle.com) ) ) PDB01 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.95.150)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb01.oracle.com) ) $ tnsping yh $ tnsping pdb01 [oracle@12c oradata]$ sqlplus sys/oracle@yh as sysdba SQL> show con_name CON_NAME ------------------------------ CDB$ROOT [oracle@12c ~]$ sqlplus sys/oracle@pdb01 as sysdba SQL> show con_name CON_NAME ------------------------------ PDB01 Connection with SQL*Developer SQL> CONNECT system@salespdb SQL> EXEC DBMS_SERVICE.CREATE_SERVICE('hrpdb', 'hrpdb') SQL> EXEC DBMS_SERVICE.START_SERVICE('hrpdb') ALTER SESSION SET CONTAINER=CDB$ROOT; ALTER SESSION SET CONTAINER=PDB01;
4、cdb、pdb启动 停止
Starting Up a CDB Instance SQL> CONNECT sys@CDB1 AS SYSDBA SQL> STARTUP NOMOUNT SQL> STARTUP MOUNT SQL> ALTER DATABASE cdb1 MOUNT; SQL> STARTUP SQL> ALTER DATABASE cdb1 OPEN; PDBS处于mount状态 pdb$seed处于OPEN READ ONLY When a CDB is mounted, the root is mounted, which means that the control files are opened, as well as the PDBs. Opening a PDB SQL> ALTER SESSION SET CONTAINER=PDB01; $ sqlplus sys/oracle@pdb01 as sysdba SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN; SQL> ALTER PLUGGABLE DATABASE ALL OPEN; SQL> ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1, pdb2 OPEN; Closing a PDB SQL> ALTER PLUGGABLE DATABASE pdb1 CLOSE IMMEDIATE; SQL> ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1, pdb2 CLOSE; SQL> ALTER PLUGGABLE DATABASE ALL CLOSE; Shutting Down a CDB Instance SQL> CONNECT sys@CDB1 AS SYSDBA SQL> SHUTDOWN IMMEDIATE SQL> CONNECT sys@PDB1 AS SYSDBA SQL> SHUTDOWN IMMEDIATE Automatic PDB Opening ? AFTER STARTUP → ON DATABASE CREATE OR REPLACE TRIGGER Open_All_PDBs after startup on database begin execute immediate 'alter pluggable database all open'; end Open_All_PDBs; / ALTER PLUGGABLE DATABASE OPEN RESTRICTED; ALTER PLUGGABLE DATABASE ALL OPEN READ ONLY; Changing PDB Mode: With SQL Developer
5、修改pdb设置
Modifying a PDB Settings ? Bring a PDB datafile online SQL> CONNECT sys@pdb1 AS SYSDBA SQL> ALTER PLUGGABLE DATABASE DATAFILE '/u03/pdb1_01.dbf' ONLINE; ? Change the PDB default tablespace ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE pdb1_tbs; ? Change the PDB default temporary tablespace SQL> ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE temp_tbs; ? Set the PDB storage limit SQL> ALTER PLUGGABLE DATABASE STORAGE (MAXSIZE 2G); ? Change the global name SQL> ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME TO pdbAPP1; Instance Parameter Change Impact ISSES_MODIFIABLE VARCHAR2(5) ISSYS_MODIFIABLE VARCHAR2(9) ISPDB_MODIFIABLE VARCHAR2(5) Using ALTER SYSTEM Statement on PDB 在PDB执行Alter system命令 ALTER SYSTEM FLUSH SHARED_POOL; ALTER SYSTEM FLUSH BUFFER_CACHE; ALTER SYSTEM ENABLE RESTRICTED SESSION; ALTER SYSTEM SET parameter ALTER SYSTEM KILL SESSION ALTER SYSTEM CHECKPOINT; ALTER SYSTEM SWITCH LOGFILE; ---不能在PDB数据库执行
6、cdb、pdb的表空间管理
在CDB中创建永久表空间 SQL> CREATE TABLESPACE yh_tbs01 DATAFILE 2 '/u01/app/oracle/oradata/YH/datafile/yh_tbs0101.dbf' SIZE 100M; SQL> SELECT tablespace_name FROM dba_tablespaces 2 WHERE contents='PERMANENT'; SQL> ALTER DATABASE DEFAULT TABLESPACE yh_tbs01; --设置默认的表空间 SELECT property_name,property_value FROM database_properties WHERE property_name='DEFAULT_PERMANENT_TABLESPACE'; 在PDB中创建永久表空间 CREATE TABLESPACE pdb01_tbs01 DATAFILE '/u01/app/oracle/oradata/YH/AC560BDC2E9C51A9E053965FA8C00201/datafile/pdb01_tbs01.dbf' SIZE 100M; SQL> ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE pdb01_tbs01; --设置默认的表空间 SQL> ALTER DATABASE DEFAULT TABLESPACE pdb01_tbs01; SELECT property_name,property_value FROM database_properties WHERE property_name='DEFAULT_PERMANENT_TABLESPACE'; Creating Local Temporary Tablespaces Only one default temporary tablespace or tablespace group is allowed per CDB or PDB. Each PDB can have temporary tablespaces or tablespace groups. SELECT property_name,property_value FROM database_properties WHERE property_name='DEFAULT_TEMP_TABLESPACE'; CDB: 在CDB中创建临时表空间 CREATE TEMPORARY TABLESPACE yh_temp02 TEMPFILE '/u01/app/oracle/oradata/YH/datafile/yh_temp0201.dbf' SIZE 100M; SELECT property_name,property_value FROM database_properties WHERE property_name='DEFAULT_TEMP_TABLESPACE'; DEFAULT_TEMP_TABLESPACE TEMP ALTER DATABASE DEFAULT TEMPORARY TABLESPACE yh_temp02; --修改CDB的默认的临时表空间 PDB中 在CDB中创建临时表空间 CREATE TEMPORARY TABLESPACE pdb01_temp02 TEMPFILE '/u01/app/oracle/oradata/YH/AC560BDC2E9C51A9E053965FA8C00201/datafile/pdb01_temp02.dbf' SIZE 100M; ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE pdb01_temp02; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE pdb01_temp02; SELECT property_name,property_value FROM database_properties WHERE property_name='DEFAULT_TEMP_TABLESPACE';
7、cdb、pdb用户及权限管理
Managing Security in CDB and PDBs common and local The commands to create local users and roles in a PDB are the same as for a non-CDB. Create a Local User PDB中创建local user 不能在root创建create local user SELECT username,common FROM dba_users; CREATE USER pdbuser01 IDENTIFIED BY oracle; --CREATE USER pdbuser01 IDENTIFIED BY oracle CONTAINER=CURRENT; SELECT * FROM dba_users WHERE username='PDBUSER01'; The DROP USER and ALTER USER commands are identical, as the commands in a nonCDB. A common user can only be created in the root container. show parameter common_user_prefix NAME TYPE VALUE ---------------------------- ------- ----------------------------------------------- common_user_prefix string C## CREATE USER c##yh01 IDENTIFIED BY oracle; --CREATE USER c##yh01 IDENTIFIED BY oracle CONTAINER=ALL; SELECT * FROM dba_users WHERE username='C##YH01'; --a common privilege GRANT CREATE SESSION TO c##yh01 CONTAINER=ALL; [oracle@12c ~]$ sqlplus c##yh01/oracle@pdb01 [oracle@12c ~]$ sqlplus c##yh01/oracle@yh --a local privielge GRANT CREATE TABLE TO c##yh01 CONTAINER=CURRENT; GRANT CREATE TABLE TO pdbuser01; --GRANT CREATE TABLE TO pdbuser01; CONTAINER=CURRENT REVOKE a common privilege REVOKE CREATE TABLE FROM c##yh01 CONTAINER=ALL; REVOKE a local prvilege REVOKE CREATE TABLE FROM pdbuser01 CONTAINER=CURRENT; COMMON ROLE and LOCAL ROLE CREATE ROLE c##role1 CONTAINER=ALL; --COMMON ROLE CREAT ROLE pdb01_role1 CONTAINER=CURRENT --LOCAL ROLE Local roles can be granted to local or common users Common roles can be granted to local or common users. Local roles can be granted to common roles. Common roles can be granted to local roles. Common role GRANT CREATE TABLE TO c##role1 CONTAINER=ALL; GRANT CREATE VIEW TO c##role1 CONTAINER=CURRENT; Creating Common and Local Profiles A common profile A local profile CREATE PROFILE C##profile1 LIMIT password_life_time 1000 CONTAINER=ALL; CREATE PROFILE profile1 LIMIT password_life_time 1000 CONTAINER=CURRENT; ALTER USER c##yh01 PROFILE c##profile1 CONTAINER=ALL; ALTER USER c##yh01 PROFILE c##profile1 CONTAINER=CURRENT; ALTER USER pdbuser01 PROFILE profile1 CONTAINER=CURRENT; ALTER USER c##yh01 PROFILE profile1 CONTAINER=CURRENT;