数据字典 | |
角色 | |
系统权限 | |
探究SEED PDB | |
| |
从PDB$SEED创建PDB | 语法: CREATE PLUGGABLE DATABASE ADMIN USER IDENTIFIED BY FILE_NAME_CONVERT = ('', '') ROLES = () DEFAULT TABLESPACE [DATAFILE ] EXTENT MANAGEMENT LOCAL <autoallocate |="" uniform="" size="" > STORAGE ];
例子: |
col con_name format a10 col restricted format a10 SELECT v.name, v.open_mode, v.restricted, d.status FROM v$pdbs v, dba_pdbs d WHERE v.guid = d.guid ORDER BY v.create_scn; NAME OPEN_MODE RESTRICTED STATUS ------------------------------ ---------- ---------- ------------- PDB$SEED READ ONLY NO NORMAL
ALTER PLUGGABLE DATABASE pdb1 OPEN; SELECT v.name, v.open_mode, v.restricted, d.status FROM v$pdbs v, dba_pdbs d WHERE v.guid = d.guid ORDER BY v.create_scn; NAME OPEN_MODE RESTRICTED STATUS ------------------------------ ---------- ---------- ------------- PDB$SEED READ ONLY NO NORMAL
SELECT * FROM dba_pdbs;
-- 12.1.0.1 version -- SELECT * FROM cdb$view(dba_pdbs); -- 12.1.0.2 versionSELECT * FROM containers(dba_pdbs); -- 12.1.0.1 version -- SELECT * FROM cdb$view(cdb_pdbs); -- 12.1.0.2 version SELECT * FROM containers(cdb_pdbs);
SELECT table_name, tablespace_name, con_id FROM containers(dba_tables) WHERE rownum < 6; | |
|
CREATE PLUGGABLE DATABASE pdb1 ADMIN USER TEST IDENTIFIED BY TEST FILE_NAME_CONVERT = ('/pdbseed/', '/pdb1/');
SELECT v.name, v.open_mode, NVL(v.restricted, 'n/a') RESTRICTED, d.status FROM v$pdbs v, dba_pdbs d WHERE v.guid = d.guid ORDER BY v1; NAME OPEN_MODE RESTRICTED STATUS ------------------------------ ---------- ---------- ------------- PDB1 MOUNTED NO NORMAL PDB$SEED READ ONLY NO NORMAL
ALTER SESSION SET CONTAINER=pdb1;
sho con_name CON_NAME ------------------- PDB1
ALTER PLUGGABLE DATABASE pdb1 OPEN; SELECT COUNT(*)FROM dba_tables; |
探究数据文件 | desc dba_data_files desc cdb_data_files
SELECT file_name, tablespace_name, online_status FROM dba_data_files;
SELECT file_name, tablespace_name, online_status, con_id FROM cdb_data_files; |
探究用户 | desc dba_users desc cdb_users
SELECT username, account_status, lock_date, expiry_date FROM dba_users ORDER BY 1;
SELECT username, default_tablespace, temporary_tablespace FROM dba_users ORDER BY 1;
SELECT username, default_tablespace, temporary_tablespace, con_id FROM cdb_users ORDER BY 1;
SELECT username, default_tablespace, temporary_tablespace, con_id FROM cdb_users WHERE username like 'TE%' ORDER BY 1;
ALTER PLUGGABLE DATABASE pdb1 OPEN;
SELECT v.name, v.open_mode, NVL(v.restricted, 'n/a') "RESTRICTED", d.status FROM v$pdbs v, dba_pdbs d WHERE v.guid = d.guid ORDER BY v.create_scn;
NAME OPEN_MODE RESTRICTED STATUS ------------------------------ ---------- ---------- ------------- PDB$SEED READ ONLY NO NORMAL PDB1 READ WRITE NO NORMAL
SELECT username, default_tablespace, temporary_tablespace, con_id FROM cdb_users WHERE username like 'TE%' ORDER BY 1; |
连接创建PDB | SQL> conn sys@pdb1 as sysdba Enter password: *********Connected.
SQL> GRANT create session TO TEST; Grant succeeded.
SQL> SELECT COUNT(*) FROM cdb_sys_privs; COUNT(*) 0
SQL> SELECT COUNT(*) FROM dba_sys_privs; COUNT(*) 987
SQL> SELECT privilege, admin_option, common FROM dba_sys_privs WHERE grantee = 'TEST';
PRIVILEGE ADM COM ---------------------------------------- --- --- CREATE SESSION NO NO
col grantee format a30 col granted_role format a30
SELECT * FROM dba_role_privs WHERE grantee = 'TEST'; GRANTEE GRANTED_ROLE ADM DEF COM ------------------------------ ------------------------------ --- --- --- TEST PDB_DBA YES YES NO |
克隆PDB | |
从一个容器现有PDB克隆新的PDB | 语法: CREATE PLUGGABLE DATABASE FROM FILE_NAME_CONVERT=('', '<path');< span="" style="word-wrap: break-word;">
例子: |
ALTER PLUGGABLE DATABASE pdb1 CLOSE; ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY;
CREATE OR REPLACE VIEW pdb_status AS SELECT v.name, v.open_mode, NVL(v.restricted, 'n/a') "RESTRICTED", d.status FROM v$pdbs v, dba_pdbs d WHERE v.guid = d.guid ORDER BY v.create_scn;
SELECT * FROM pdb_status; NAME OPEN_MODE RESTRICTED STATUS ------------------------------ ---------- ---------- ------------- PDB$SEED READ ONLY NO NORMAL PDB1 READ ONLY NO NORMAL
CREATE PLUGGABLE DATABASE pdb1_t FROM pdb1FILE_NAME_CONVERT = ('\uwpdb\', '\uwpdbd\'); SELECT * FROM pdb_status; NAME OPEN_MODE RESTRICTED STATUS ------------------------------ ---------- ---------- ------------- PDB$SEED READ ONLY NO NORMAL PDB1 READ ONLY NO NORMAL | |
| |
创建时的UNPLUG/PLUGPDB | |
Unplug 子句 | |
Unplug 子句
从unplugged创建 | 语法: ALTER PLUGGABLE DATABASE UNPLUG INTO '';
例子: |
conn / as sysdba ALTER PLUGGABLE DATABASE pdb1 CLOSE; ALTER PLUGGABLE DATABASE pdb1 UNPLUG INTO 'pdb1.xml'; -- 将文件ftp到新服务器 -- the xml 文件在 $ORACLE_HOME/database下
SQL> CREATE PLUGGABLE DATABASE pdb2 USING 'pdb1.xml' SOURCE_FILE_NAME_CONVERT = ('\pdb1\', '\pdb2\') NOCOPY STORAGE (MAXSIZE 1G MAX_SHARED_TEMP_SIZE 20M) TEMPFILE REUSE; | |
| |
更改PDB | |
设置子句 | |
默认版本 | 语法: ALTER PLUGGABLE DATABASE
例子: |
conn / as sysdba ALTER SESSION SET CONTAINER = pdb1; CREATE EDITION test_ed; ALTER PLUGGABLE DATABASE pdb1 DEFAULT EDITION = test_ed; | |
默认表空间 | 语法: ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE ;
例子: |
conn / as sysdba
ALTER SESSION SET CONTAINER = pdb1; SELECT tablespace_nameFROM dba_tablespaces; ALTER PLUGGABLE DATABASE pdb1 DEFAULT TABLESPACE oradata; | |
默认表空间类型 | ALTER PLUGGABLE DATABASE SET DEFAULT TABLESPACE; |
conn / as sysdba ALTER SESSION SET CONTAINER = pdb1; ALTER PLUGGABLE DATABASE pdb1 SET DEFAULT BIGFILE TABLESPACE; | |
默认临时表空间 | ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE ;; |
conn / as sysdba ALTER SESSION SET CONTAINER = pdb1; ALTER PLUGGABLE DATABASE pdb1 DEFAULT TEMPORARY TABLESPACEtemp_grp; | |
重命名 | 语法: ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME TO [.domain];
例子: |
conn / as sysdba ALTER SESSION SET CONTAINER = pdb1; SELECT *FROM global_name;
ALTER PLUGGABLE DATABASE pdb1 RENAME GLOBAL_NAME TO pdb$dev; | |
PDB内设置时区 | 语法: ALTER PLUGGABLE DATABASE [] SET TIME_ZONE = ''; ALTER PLUGGABLE DATABASE SET TIME_ZONE = '< | -> HH:MI';
例子: |
conn sys@pdb1 as sysdba ALTER SESSION SET CONTAINER = pdb1; SELECT value$ FROM props$ WHERE name = 'DBTIMEZONE'; ALTER PLUGGABLE DATABASE pdb1 SET TIME_ZONE = 'Japan'; SELECT value$FROM props$WHERE name = 'DBTIMEZONE'; ALTER PLUGGABLE DATABASE pdb1 SET TIME_ZONE = '00:00'; SELECT value$FROM props$WHERE name = 'DBTIMEZONE'; | |
| |
数据文件重命名 | 语法: ALTER PLUGGABLE DATABASE RENAME FILE '' TO ''; |
例子: conn sys@pdb1 as sysdba
ALTER PLUGGABLE DATABASE CLOSE; RENAME FILE 'C:\APP\ORACLE\ORADATA\PDB1\PDB11.DBF' TO 'C:\APP\ORACLE\ORADATA\PDB1\PDB101.DBF'; --原数据文件需手工去除 | |
创建数据文件 | ALTER PLUGGABLE DATABASE CREATE DATAFILE <'' | filenumber> [AS | NEW] |
更改数据文件 | ALTER PLUGGABLE DATABASE DATAFILE <'' | filenumber> <online |="" offline="" [for="" drop]="" resize="" | | END BACKUP>; |
更改临时文件 | ALTER PLUGGABLE DATABASE TEMPFILE <'' | filenumber> <resize |="" | DROP [INCLUDING DATAFILES] | ONLINE | OFFLINE>; |
移除数据文件 | ALTER PLUGGABLE DATABASE MOVE DATAFILE <'' | ASM_file_name | filenumber>TO '' [REUSE] [KEEP] |
补充日志 | 语法: ALTER PLUGGABLE DATABASE SUPPLEMENTAL LOG <data |="" | > 例子: |
ALTER PLUGGABLE DATABASE pdb1 ADD SUPPLEMENTAL LOG DATA; | |
存储参数 | 语法: ALTER PLUGGABLE DATABASE STORAGE <unlimited |="" >; 例子: |
ALTER PLUGGABLE DATABASE pdb1 STORAGE UNLIMITED; | |
例子: ALTER PLUGGABLE DATABASE STORAGE (MAX_SHARED_TEMP_SIZE <unlimited |="" ); 例子: | |
ALTER PLUGGABLE DATABASE pdb1 STORAGE (MAX_SHARED_TEMP_SIZE UNLIMITED); | |
Datafile子句 | |
数据文件联机/脱机 | 语法: ALTER PLUGGABLE DATABASE DATAFILE ALL ; |
例子: conn / as sysdba ALTER SESSION SET CONTAINER = pdb1; ALTER PLUGGABLE DATABASE pdb1 CLOSE; SELECT open_modeFROM v$pdbs; SELECT file#, status, enabledFROM v$datafile; ALTER PLUGGABLE DATABASE pdb1 DATAFILE ALL OFFLINE;
ALTER PLUGGABLE DATABASE pdb1 DATAFILE ALL ONLINE; SELECT file#, status, enabledFROM v$datafile;
ALTER PLUGGABLE DATABASE pdb1 OPEN; | |
Recovery子句 | |
备份和恢复PDB | 语法: ALTER PLUGGABLE DATABASE RECOVER [AUTOMATIC] [FROM '' DATABASE;例子: |
ALTER PLUGGABLE DATABASE pdb1 RECOVER AUTOMATIC DATABASE; | |
语法: ALTER PLUGGABLE DATABASE BACKUP; 例子: | |
conn / as sysdba SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN; ALTER PLUGGABLE DATABASE pdb1 OPEN; ALTER SESSION SET CONTAINER = PDB1; ALTER PLUGGABLE DATABASE pdb1 BEGIN BACKUP; ALTER PLUGGABLE DATABASE pdb1 END BACKUP; | |
Change 子句 | |
读写模式打开 | 语法: ALTER PLUGGABLE DATABASE OPEN READ [WRITE] [UPGRADE] [RESTRICTED] [FORCE][INSTANCES = <('' | )] >); 例子: |
ALTER PLUGGABLE DATABASE pdb1 OPEN; | |
只读模式打开 | 语法: ALTER PLUGGABLE DATABASE OPEN READ ONLY ; 例子: |
ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY; | |
Resetlogs模式打开 | 语法: ALTER PLUGGABLE DATABASE OPEN RESETLOGS ; 例子: |
ALTER PLUGGABLE DATABASE pdb1 OPEN RESETLOGS; | |
打开/关闭PDB | 语法: ALTER PLUGGABLE DATABASE CLOSE [IMMEDIATE]; |
例子: ALTER PLUGGABLE DATABASE pdb1 CLOSE; | |
打开/关闭所有PDB | 语法: ALTER PLUGGABLE DATABASE ALL CLOSE [IMMEDIATE]; |
例子: ALTER PLUGGABLE DATABASE ALL CLOSE; | |
只读模式打开PDB | 语法: ALTER PLUGGABLE DATABASE READ ONLY; 例子: |
conn / as sysdba ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY; | |
以READ ONLY RESTRICTED模式打开PDB | 语法: ALTER PLUGGABLE DATABASE OPEN READ ONLY RESTRICTED; 例子: |
conn / as sysdbaALTER PLUGGABLE DATABASE pdb1 CLOSE;ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY RESTRICTED; | |
| |
移除PDB | |
从容器中删除PDB | 语法: DROP PLUGGABLE DATABASE [INCLUDING DATAFILES]; 例子: |
ALTER PLUGGABLE DATABASE pdb1 CLOSE;DROP PLUGGABLE DATABASE pdb1 INCLUDING DATAFILES; |
CDB_FILE$ | DBA_PDBS | PDB$SEED |
CDB_LOCAL_ADMINAUTH$ | DBA_PDB_HISTORY | PDB_HISTORY$ |
CDB_PDB_SAVED_STATES | DBA_PDB_SAVED_STATES | PDB_PLUG_IN_VIOLATIONS |
CDB_RESOURCE_PLAN$ | DBMS_PDB | PDB_SPFILE$ |
CDB_RESOURCE_PLAN_DIRECTIVE$ | DBMS_PDB_EXEC_SQL | V$CONTAINERS |
CDB_SERVICE$ | GV$CONTAINERS | V$PDBS |
CDB_HIST_PDB_INSTANCE | GV$PDBS | V$PDB_INCARNATION |
CDB_PDBS | GV$PDB_INCARNATION | WRI$_ADV_ADDM_PDBS |
CDB_PDB_HISTORY | GV$SESSIONS_COUNT | WRM$_PDB_INSTANCE |
INT$DBA_PDB_SAVED_STATES | XDB$CDBPORTS | |
DBA_HIST_PDB_INSTANCE | PDB_ALERT$ |
|