Oracle 12c CDB&PDB 基本維護

羽化殘虹發表於2016-07-20
Oracle

CDB&PDB介紹

 

CDB元件（Components of a CDB）

ROOT元件

ROOT又叫CDB$ROOT, 儲存著ORACLE提供的後設資料和Common User,後設資料的一個例子是ORACLE提供的PL/SQL包的原始碼，Common User 是指在每個容器中都存在的使用者。

SEED元件

  Seed又叫PDB$SEED,這個是你建立PDBS資料庫的模板，你不能在Seed中新增或修改一個物件。一個CDB中有且只能有一個Seed. 這個感念，個人感覺非常類似SQL SERVER中的model資料庫。

PDBS

    CDB中可以有一個或多個PDBS，PDBS向後相容，可以像以前在資料庫中那樣操作PDBS，這裡指大多數常規操作。這些元件中的每一個都可以被稱為一個容器。因此，ROOT(根)是一個容器，Seed(種子)是一個容器，每個PDB是一個容器。每個容器在CDB中都有一個獨一無二的的ID和名稱。

 

一 連線CDB

連線方式與早起版本的方式一樣

[oracle@12crac1 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 5 17:22:58 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

SQL>

[oracle@12crac1 ~]$ sqlplus sys/oracle as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 5 17:23:23 2016

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

SQL>

二 檢視資料庫是否為多租戶資料庫（CDB）

簡單的方式，如果查詢結果是yes，則表示為cdb，NO則表示和傳統資料庫型別一樣 

SELECT CDB FROM V$DATABASE;
CDB
--- 
YES

 

三 檢視當前容器名字

 

SQL> show con_name

 

CON_NAME

------------------------------

CDB$ROOT

 

或者

SQL> select sys_context('userenv', 'con_name') "Container DB" from dual;

 

Container DB

--------------------------------------------------------------------------------

CDB$ROOT

 

四 pdb管理

4.1 通過模板建立pdb

SQL>  CREATE PLUGGABLE DATABASE hrpdb ADMIN USER woqu IDENTIFIED BY woqutech roles=(dba);

 

SQL> CREATE PLUGGABLE DATABASE crmpdb ADMIN USER woqu IDENTIFIED BY woqutech roles=(dba);

 

SQL> select con_id, dbid, guid, name , open_mode from v$pdbs;

 

    CON_ID       DBID GUID                             NAME                           OPEN_MODE

---------- ---------- -------------------------------- ------------------------------ ----------

         2 2475472073 36CCE0F673E360C7E053E8460A0A0B89 PDB$SEED                       READ ONLY

         3  893854447 36CD081F2B325A26E053E6460A0A6A9B QDL_PDB1                       READ WRITE

         4 4263003850 36E101302F7C425AE053E6460A0AD322 HRPDB                          MOUNTED

         5 2570002520 36E101302F7D425AE053E6460A0AD322 CRMPDB                         MOUNTED

 

   注意：mounted狀態的pdb表示資料庫是關閉狀態

4.2 clone一個pdb

4.2.1 Clone一個本地pdb

SQL> CREATE PLUGGABLE DATABASE pdb2 FROM hrpdb;

 

Pluggable database created.

 

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 QDL_PDB1                       READ WRITE NO

         4 HRPDB                          READ WRITE NO

         5 CRMPDB                         READ WRITE NO

         6 SALEPDB                        READ WRITE NO

         7 PDB2                           MOUNTED

4.2.2 Clone一個本地的pdb，但不clone資料 

ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY;
CREATE PLUGGABLE DATABASE pdb2 FROM pdb1 NO DATA;

4.2.3 通過克隆遠端的pdb來建立pdb

通過dblink連線到遠端的pdb1 來建立pdb2，主要這裡的pdb1 也是CDB 中的pdb

  

CREATE PLUGGABLE DATABASE test12c FROM pdb1@pdb_test12_dblink;

4.2.4 通過克隆一個遠端的非CDB 建立pdb

通過dblink方式，遠端的Non-CDB 的名字叫mydb，目標的pdb名字為pdb2 

CREATE PLUGGABLE DATABASE pdb2 FROM mydb@mydb_link;

如果源端的資料庫是Non-CDB ，那可以使用NON$CDB來代替遠端Non-CDB名字，下面的語句是和上面的語句功能相同， 

CREATE PLUGGABLE DATABASE pdb2 FROM NON$CDB@mydb_link;
建立之後要切換當前容器為,新建立的pdb2
ALTER SESSION SET CONTAINER=pdb2;
建立之後要跑一個轉換指令碼
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

 

下面是一個操作例子

 

首先通過root容器建立個dblink

 

create public database link pdb_test12_dblink connect to system identified by oracle using 'orcl';

 

通過dblink克隆一個pdb，這裡源端資料庫記得要為read only狀態。

 

SQL> CREATE PLUGGABLE DATABASE test12c FROM NON$CDB@pdb_test12_dblink;

 

Pluggable database created.

 

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         8 TEST12C                        MOUNTED

 

切換容器為新pdb

ALTER SESSION SET CONTAINER=test12c;

 

執行轉換指令碼 

@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

開啟新建立的pdb

SQL> alter pluggable database test12c open;

 

Pluggable database altered.

 

檢視狀態

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         5 TEST12C                        READ WRITE NO

 

 

4.3 使用Non-CDB建立一個pdb

4.3.1 克隆一個NON-CDB 來建立一個pdb

參見通過克隆一個遠端的非CDB 建立pdb

 

4.3.2  使用  DBMS_PDB package 生成xml檔案建立pdb

注意，此功能要求資料庫版本為12c，

 如果資料庫還沒有被unplugged，則需要執行DBMS_PDB.DESCRIBE儲存過程來生成一個xml pdb描述檔案

下面就是建立一個test12c的描述檔案，注意再生成描述檔案前資料要read only狀態！ 

 BEGIN
      DBMS_PDB.DESCRIBE(
        pdb_descr_file => '/home/oracle/3.xml');
    END;
   /

關閉源端test12c資料庫

sys@TEST12C>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@TEST12C>

 

 

將拔出的資料庫test12c ，插入到cdb qdl中,這裡需要注意的是我的目標cdb使用的是ASM 磁碟組，+wxh/qdl/test12c 中的test12c 目錄要在asm 中預先手動建立。

 

SQL> CREATE PLUGGABLE DATABASE test12c USING '/home/oracle/3.xml' COPY FILE_NAME_CONVERT = ('/expdpdir/u02/oracle/oradata/test12c/','+wxh/qdl/test12c/');

 

Pluggable database created.

 

 

 

切換到test12c pdb

 

Alter session set  container=test12c;

 

執行轉換noncdb_to_pdb.sql script:

@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

 

如果此pdb已經被拔出，則需要通過函式BMS_PDB.CHECK_PLUG_COMPATIBILITY 檢查是否和cdb相容，此函式需要提供兩個引數

1 pdb_descr_file 注意這裡必須是絕對路徑

2 pdb_name 

SET SERVEROUTPUT ON
DECLARE
  compatible CONSTANT VARCHAR2(3) := 
    CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
           pdb_descr_file => '/home/oracle/3.xml',
           pdb_name       => 'test12c')
    WHEN TRUE THEN 'YES'
    ELSE 'NO'
END;
BEGIN
  DBMS_OUTPUT.PUT_LINE(compatible);
END;
/

 

如果此檢查的結果是YES ,我們可以進行插入操作。如果此結果是NO，需要再次檢查

4.4 刪除pdb

 刪除pdb之前必須要確保兩個條件

1         pdb 要在mount狀態或者是在unplugged狀態

2         當前的使用者必須具有sysdba或者sysoper許可權，此許可權或者是共同授予或者在pdb本地授予，在連線pdb的時候使用 as sysdba或者as sysoper

 

  刪除pdb分為保留資料檔案和不保留資料檔案兩種情況

 

Dropping PDB salespdb While Keeping Its Data Files 

DROP PLUGGABLE DATABASE salespdb
  KEEP DATAFILES;

Dropping PDB salespdb and Its Data Files 

DROP PLUGGABLE DATABASE salespdb
  INCLUDING DATAFILES;

4.5 Unplugging a PDB

  拔出pdb的要求如下

1 當前的使用者必須具有sysdba或者sysoper許可權，此許可權或者是共同授予或者在pdb本地授予，在連線pdb的時候使用 as sysdba或者as sysoper

2         至少又一次pdb是正常開啟過的

3         Pdb 必須是關閉的狀態，在rac環境中所有的例項也必須是關閉的、

 

執行Unplug pdb

  

ALTER PLUGGABLE DATABASE salespdb UNPLUG INTO '/oracle/data/salespdb.xml';

 

 

五 檢視容器中pdb

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 QDL_PDB1                       READ WRITE NO

         4 HRPDB                          READ WRITE NO

         5 CRMPDB                         READ WRITE NO

         6 SALEPDB                        READ WRITE NO

 

select con_id, dbid, guid, name , open_mode from v$pdbs;

 

        CON_ID       DBID GUID                             NAME                       OPEN_MODE

---------- ---------- -------------------------------- ------------------------------ ----------

         2 2475472073 36CCE0F673E360C7E053E8460A0A0B89 PDB$SEED                         READ ONLY

         3  893854447 36CD081F2B325A26E053E6460A0A6A9B QDL_PDB1                       READ WRITE

 

六 開啟一個pdb

SQL> alter pluggable database crmpdb open;

 

Pluggable database altered.

SQL> select con_id, dbid, guid, name , open_mode from v$pdbs;

 

    CON_ID       DBID GUID                             NAME                           OPEN_MODE

---------- ---------- -------------------------------- ------------------------------ ----------

         2 2475472073 36CCE0F673E360C7E053E8460A0A0B89 PDB$SEED                        READ ONLY

         3  893854447 36CD081F2B325A26E053E6460A0A6A9B QDL_PDB1                       READ WRITE

         4 4263003850 36E101302F7C425AE053E6460A0AD322 HRPDB                           READ WRITE

         5 2570002520 36E101302F7D425AE053E6460A0AD322 CRMPDB                         READ WRITE

七 在容器間切換

 

SQL> alter session set container=crmpdb;

 

Session altered.

 

SQL> show con_name; 

 

CON_NAME

------------------------------

CRMPDB

SQL>

SQL> alter session set container=CDB$ROOT;

 

Session altered.

 

SQL> show con_name

 

CON_NAME

------------------------------

CDB$ROOT

 

八 CDB重啟後pdb狀態保留

 當cdb重啟後，pdb的預設的狀態為最開始pdb執行時候的狀態，一般為是關閉狀態。我們可以設定在cdb重啟後，pdb的狀態是為open還是關閉等狀態。

 

SQL>  select name,open_mode from v$pdbs;

 

NAME                           OPEN_MODE

------------------------------ ----------

PDB$SEED                       READ ONLY

QDL_PDB1                       MOUNTED

HRPDB                          MOUNTED

CRMPDB                         MOUNTED

SALEPDB                         MOUNTED

SQL> alter pluggable database hrpdb open;

 

Pluggable database altered.

 

SQL> ALTER PLUGGABLE DATABASE hrpdb SAVE STATE;

 

Pluggable database altered.

 

SQL>  select name,open_mode from v$pdbs;

 

NAME                           OPEN_MODE

------------------------------ ----------

PDB$SEED                       READ ONLY

QDL_PDB1                       MOUNTED

HRPDB                          READ WRITE

CRMPDB                         MOUNTED

SALEPDB                        MOUNTED

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area 5016387584 bytes

Fixed Size                  2934696 bytes

Variable Size            1090521176 bytes

Database Buffers         3909091328 bytes

Redo Buffers               13840384 bytes

Database mounted.

Database opened.

SQL> select name,open_mode from v$pdbs;

 

NAME                           OPEN_MODE

------------------------------ ----------

PDB$SEED                       READ ONLY

QDL_PDB1                       MOUNTED

HRPDB                          READ WRITE

CRMPDB                         MOUNTED

SALEPDB                        MOUNTED

 

所有pdb 改變成open狀態

SQL> alter pluggable database all open;

Pluggable database altered.

 

 SQL> select name,open_mode from v$pdbs;

 

NAME                           OPEN_MODE

------------------------------ ----------

PDB$SEED                       READ ONLY

QDL_PDB1                       READ WRITE

HRPDB                          READ WRITE

CRMPDB                         READ WRITE

SALEPDB                        READ WRITE

 

保留所有的pdb狀態都為open

 alter pluggable database all save state;

 

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area 5016387584 bytes

Fixed Size                  2934696 bytes

Variable Size            1090521176 bytes

Database Buffers         3909091328 bytes

Redo Buffers               13840384 bytes

Database mounted.

Database opened.

SQL>  select name,open_mode from v$pdbs;

 

NAME                           OPEN_MODE

------------------------------ ----------

PDB$SEED                       READ ONLY

QDL_PDB1                       READ WRITE

HRPDB                          READ WRITE

CRMPDB                         READ WRITE

SALEPDB                        READ WRITE

 

只保留部分pdb的open mode

ALTER PLUGGABLE DATABASE salespdb, hrpdb SAVE STATE;

保留所有的pbd狀態，除了salesdb，hrdb

ALTER PLUGGABLE DATABASE ALL EXCEPT salespdb, hrpdb SAVE STATE;

 

九cdb&pdb連線方式

9.1 連線CDB和pdb的方式和以前的版本一樣的


SQL> -- EZCONNECT

SQL> CONN system/password@//localhost:1521/cdb1

Connected.

SQL>

 

SQL> -- tnsnames.ora

SQL> CONN system/password@cdb1

Connected.

SQL>

 

SQL> -- EZCONNECT

SQL> CONN system/password@//localhost:1521/pdb1

Connected.

SQL>

 

SQL> -- tnsnames.ora

SQL> CONN system/password@pdb1

Connected.

SQL>

 

 

 

9.2 在cdb中檢視service

SQL> COLUMN name FORMAT A30

SQL> SELECT name, pdb

  2  FROM   v$services

  3  ORDER BY name;

 

NAME                           PDB

------------------------------ ------------------------------

SYS$BACKGROUND                 CDB$ROOT

SYS$USERS                      CDB$ROOT

crmpdb                         CRMPDB

hrpdb                          HRPDB

qdl                            CDB$ROOT

qdlXDB                         CDB$ROOT

qdl_pdb1                       QDL_PDB1

salepdb                        SALEPDB

 

9.3 在監聽裡檢視service

[root@12crac1 ~]# su - grid

[grid@12crac1 ~]$ lsnrctl services

 

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-JUL-2016 22:24:21

 

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

Services Summary...

Service "+APX" has 1 instance(s).

  Instance "+APX1", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0 state:ready

         LOCAL SERVER

Service "crmpdb" has 1 instance(s).

  Instance "qdl_1", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0 state:ready

         LOCAL SERVER

Service "hrpdb" has 1 instance(s).

  Instance "qdl_1", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0 state:ready

         LOCAL SERVER

Service "qdl" has 1 instance(s).

  Instance "qdl_1", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0 state:ready

         LOCAL SERVER

Service "qdlXDB" has 1 instance(s).

  Instance "qdl_1", status READY, has 1 handler(s) for this service...

    Handler(s):

      "D000" established:0 refused:0 current:0 max:1022 state:ready

         DISPATCHER <machine: 12crac1, pid: 15904>

         (ADDRESS=(PROTOCOL=tcp)(HOST=12crac1)(PORT=53627))

Service "qdl_pdb1" has 1 instance(s).

  Instance "qdl_1", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0 state:ready

         LOCAL SERVER

Service "salepdb" has 1 instance(s).

  Instance "qdl_1", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0 state:ready

         LOCAL SERVER

The command completed successfully

[grid@12crac1 ~]$

 

9.4 使用jdbc連線pdb

JDBC 連線pdb的話必須使用service來連線，因為使用sid的話有導致有部分連線丟失。

連線語法如下

# Syntax

jdbc:oracle:thin:@[HOST][:PORT]:SID

jdbc:oracle:thin:@[HOST][:PORT]/SERVICE

 

# Example

jdbc:oracle:thin:@ol6-121:1521:pdb1

jdbc:oracle:thin:@ol6-121:1521/pdb1

 

當嘗試使用sid去連線pdb時候，會出現如下錯

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

但是可以通過編輯監聽檔案”$ORACLE_HOME/network/admin/listener.ora" 增加下面一行，並且監聽的名字要和你的service匹配。然後重啟監聽，jdbc就可以使用sid方式連線了、

USE_SID_AS_SERVICE_listener=on

$ lsnrctl reload

十 遷移非CDB資料庫到CDB

 

常規匯出/匯入

·         相對較慢

·         對於大型資料庫系統不是推薦的方案

·         可靠的遷移技術，但只建議用於 Oracle 9i 和更早版本的資料庫

資料泵匯出/匯入（於 Oracle 資料庫 10g 版本引入）

·         用於處理大資料量，比較傳統的遷移方式

·         取代了原來的匯出/匯入作為移動 Oracle 資料庫之間資料的最常用的方法，通過可傳輸表空間的方法在效能方面獲得提升，impdp匯入加入@pdb就可以了

可傳輸表空間

·         通過拷貝資料檔案到目標系統來實現更快速的升級/遷移

·         需要額外的步驟，將使用者和系統後設資料移動到目標資料庫

·         非使用者表空間（SYSTEM 和 SYSAUX表空間）相關聯的使用者物件需要手動操作，源和目標有不同的 Endian 格式時，需要執行資料庫檔案的轉換

完整可傳輸匯出/匯入

·         同時使用可傳輸表空間的機制和資料泵功能

·         可傳輸表空間的機制是用來移動資料庫檔案到目標系統來獲得更快的遷移，即使是大資料量遷移。

·         資料泵是用於移動後設資料到目標資料庫

·         單一的 import 命令即可完成整個遷移

·         能夠對所有需要的使用者資料和後設資料進行資料庫遷移

 

十一 相關檢視

11.1 在cdb中檢視每個容器資訊

COLUMN NAME FORMAT A8

SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

NAME         CON_ID       DBID    CON_UID GUID

-------- ---------- ---------- ---------- --------------------------------

CDB$ROOT          1 2155562965          1 FD9AC20F64D344D7E043B6A9E80A2F2F

PDB$SEED          2 2475472073 2475472073 36CCE0F673E360C7E053E8460A0A0B89

QDL_PDB1          3  893854447  893854447 36CD081F2B325A26E053E6460A0A6A9B

TEST12C           4 1522419918  430898493 376802A27D476999E05329460A0AB2F7

PDB2              7 2404214216 2404214216 371CCE693E262080E053E6460A0AE923

11.2 檢視pdb中 pdb資訊 

COLUMN PDB_NAME FORMAT A15
 
SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;

11.3 查詢每個pdb的名稱和狀態資訊 

COLUMN NAME FORMAT A15
COLUMN RESTRICTED FORMAT A10
COLUMN OPEN_TIME FORMAT A40
SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;
NAME            OPEN_MODE  RESTRICTED OPEN_TIME
--------------- ---------- ---------- ----------------------------------------
PDB$SEED        READ ONLY  NO         05-JUL-16 09.54.24.523 PM +08:00
QDL_PDB1        MOUNTED               12-JUL-16 12.14.27.836 PM +08:00
TEST12C         READ WRITE NO         12-JUL-16 07.28.19.097 PM +08:00
PDB2            MOUNTED               08-JUL-16 04.44.56.470 PM +08:00

11.4 查詢指定的schema下的表 

COLUMN PDB_NAME FORMAT A15
COLUMN OWNER FORMAT A15
COLUMN TABLE_NAME FORMAT A30
 
SELECT p.PDB_ID, p.PDB_NAME, t.OWNER, t.TABLE_NAME 
  FROM DBA_PDBS p, CDB_TABLES t 
  WHERE p.PDB_ID > 2 AND
        t.OWNER IN('HR','OE') AND
        p.PDB_ID = t.CON_ID
  ORDER BY p.PDB_ID;
 
  

11.5 查詢pdb中的使用者 

COLUMN PDB_NAME FORMAT A15
COLUMN USERNAME FORMAT A30
 
SELECT p.PDB_ID, p.PDB_NAME, u.USERNAME 
  FROM DBA_PDBS p, CDB_USERS u
  WHERE p.PDB_ID > 2 AND
        p.PDB_ID = u.CON_ID
  ORDER BY p.PDB_ID;

11.6 查詢資料檔案 

COLUMN PDB_ID FORMAT 999
COLUMN PDB_NAME FORMAT A8
COLUMN FILE_ID FORMAT 9999
COLUMN TABLESPACE_NAME FORMAT A10
COLUMN FILE_NAME FORMAT A45
 
SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME
  FROM DBA_PDBS p, CDB_DATA_FILES d
  WHERE p.PDB_ID = d.CON_ID
  ORDER BY p.PDB_ID;
 
PDB_ID PDB_NAME FILE_ID TABLESPACE FILE_NAME
------ -------- ------- ---------- ---------------------------------------------
     3 QDL_PDB1       9 SYSTEM     +WXH/QDL/36CD081F2B325A26E053E6460A0A6A9B/DAT
                                   AFILE/system.280.916335223
 
     3 QDL_PDB1      10 SYSAUX     +WXH/QDL/36CD081F2B325A26E053E6460A0A6A9B/DAT
                                   AFILE/sysaux.279.916335223
 
     4 TEST12C       42 USERS      +WXH/qdl/test12c/users01.dbf
     4 TEST12C       40 SYSTEM     +WXH/qdl/test12c/system01.dbf
     4 TEST12C       41 SYSAUX     +WXH/qdl/test12c/sysaux01.dbf

11.7 查詢CDB 中 temp 檔案 

COLUMN CON_ID FORMAT 999
COLUMN FILE_ID FORMAT 9999
COLUMN TABLESPACE_NAME FORMAT A15
COLUMN FILE_NAME FORMAT A45
 
SELECT CON_ID, FILE_ID, TABLESPACE_NAME, FILE_NAME
  FROM CDB_TEMP_FILES
  ORDER BY CON_ID;
 
 
CON_ID FILE_ID TABLESPACE_NAME FILE_NAME
------ ------- --------------- ---------------------------------------------
     1       1 TEMP            +WXH/QDL/TEMPFILE/temp.269.916334561
     3       3 TEMP            +WXH/QDL/36CD081F2B325A26E053E6460A0A6A9B/TEM
                               PFILE/temp.281.916335227
 
     4       4 TEMP            +WXH/qdl/test12c/temp01.dbf

11.8 查詢pdb可以修改的引數

一般情況下cdb中的資料庫引數是繼承到pdb中， PDB可以手動單獨更改本pdb的引數 

  SELECT NAME FROM V$SYSTEM_PARAMETER
  WHERE ISPDB_MODIFIABLE = 'TRUE'
  ORDER BY NAME;

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26175573/viewspace-2122295/，如需轉載，請註明出處，否則將追究法律責任。

相關文章