|
?
一般信息
|
数据字典
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
CONTAINERS
INT$DBA_PDB_SAVED_STATES
XDB$CDBPORTS
DBA_HIST_PDB_INSTANCE
PDB_ALERT$
?
角色
CDB_DBA
?
?
系统权限
CREATE PLUGGABLE DATABASE
SET CONTAINER
SET CONTAINER
?
探究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,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,NVL(v.restricted,‘n/a‘) RESTRICTED,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,online_status
FROM dba_data_files;
?
SELECT file_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,temporary_tablespace,con_id
FROM cdb_users
ORDER BY 1;
?
SELECT username,con_id
FROM cdb_users
WHERE username like ‘TE%‘
ORDER BY 1;
?
ALTER PLUGGABLE DATABASE pdb1 OPEN;
?
SELECT v.name,‘n/a‘) "RESTRICTED",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,con_id
FROM cdb_users
WHERE username like ‘TE%‘
ORDER BY 1;
连接创建PDB
SQL> conn [email?protected] 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,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 [email?protected] 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 [email?protected] as sysdba
?
ALTER PLUGGABLE DATABASE CLOSE;
?
?ALTER PLUGGABLE DATABASE?pdb1
RENAME FILE?‘C:APPORACLEORADATAPDB1PDB11.DBF‘
TO ‘C:APPORACLEORADATAPDB1PDB101.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#,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;
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
