ITPub博客

首页 > 数据库 > Oracle > Oracle 12c CDB&PDB 基本维护

Oracle 12c CDB&PDB 基本维护

原创 Oracle 作者:羽化残虹 时间:2016-07-20 20:02:24 0 删除 编辑

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,则表示为cdbNO则表示和传统数据库类型一样

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

 

只保留部分pdbopen mode

ALTER PLUGGABLE DATABASE salespdb, hrpdb SAVE STATE;

保留所有的pbd状态,除了salesdbhrdb

ALTER PLUGGABLE DATABASE ALL EXCEPT salespdb, hrpdb SAVE STATE;

 

cdb&pdb连接方式

9.1 连接CDBpdb的方式和以前的版本一样的


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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-08-24

  • 博文量
    69
  • 访问量
    260441