12c pdb_profiles$&In-Memory_pdb inmemory-优快云博客

本文链接：https://blog.youkuaiyun.com/itdba/article/details/42845425

[oracle@ora12 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 7 19:31:20 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup 
ORACLE instance started.

Total System Global Area 1862270976 bytes
Fixed Size                  2925648 bytes
Variable Size             553651120 bytes
Database Buffers         1291845632 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.
SQL> 
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter system set open_cursors=500 container=all;
System altered.
SQL> show parameter open_cursors;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     500
SQL> alter session set container=pdb1;
Session altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         3  575147372 PDB1                           MOUNTED
SQL> conn / as sysdba
Connected.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 3519850196 PDB$SEED                       READ ONLY
         3  575147372 PDB1                           MOUNTED
         4 2788087049 HJ_PDB                         MOUNTED
SQL> alter session set container=pdb1;
Session altered.
SQL> alter system set open_cursors=120 container=current;
alter system set open_cursors=120 container=current
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only

SQL> alter pluggable database pdb1 open;
Pluggable database altered.
SQL> alter system set open_cursors=120 container=current;
System altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         3  575147372 PDB1                           READ WRITE
SQL> show parameter open_cursors;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     120
SQL>  COL OWNER FOR A10
SQL> select con_id,owner,object_type from cdb_objects where object_name='PDB_SPFILE$';
    CON_ID OWNER      OBJECT_TYPE
---------- ---------- -----------------------
         3 SYS        TABLE
SQL> conn / as sysdba
Connected.
SQL> select con_id,owner,object_type from cdb_objects where object_name='PDB_SPFILE$';
    CON_ID OWNER      OBJECT_TYPE
---------- ---------- -----------------------
         3 SYS        TABLE
         1 SYS        TABLE
SQL> COL DB_UNIQ_NAME FOR A10
SQL> COL NAME FOR A15
SQL>  COL VALUE$ FOR A10
SQL> SELECT DB_UNIQ_NAME,PDB_UID,NAME,VALUE$ FROM PDB_SPFILE$;
DB_UNIQ_NA    PDB_UID NAME            VALUE$
---------- ---------- --------------- ----------
D12C1       575147372 open_cursors    120
SQL> SELECT DB_UNIQ_NAME,PDB_UID,NAME,VALUE$ FROM PDB_SPFILE$;
DB_UNIQ_NA    PDB_UID NAME            VALUE$
---------- ---------- --------------- ----------
D12C1       575147372 open_cursors    120
SQL>  select value$ from pdb_spfile$ where name='open_cursors';
VALUE$
----------
120
SQL> delete from  pdb_spfile$ where name='open_cursors';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select value$ from pdb_spfile$ where name='open_cursors';
no rows selected
SQL>

删除PDB_SPFILE$中相关记录,pdb的参数值会自动继续继承cdb中参数值

总结说明:通过上述的一些列试验证明cdb中参数关系,在cdb中修改,会默认所有pdb均自动继承;如果在pdb中修改值会覆盖cdb参数,而且只对当前pdb生效,并记录在PDB_SPFILE$

SQL> select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit                         0
PL/SQL Release 12.1.0.2.0 -                                                               0
CORE    12.1.0.2.0                                                                        0
TNS for Linux: Version 12.1.0.2.0 -                                                       0
NLSRTL Version 12.1.0.2.0 -                                                               0
In-Memory Aggregation                                            TRUE
SQL> select parameter,value from v$option where parameter like '<span style="color:#ff6666;">In-Memory%</span>';

PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------
In-Memory Column Store                                           TRUE
In-Memory Aggregation                                            TRUE

SQL> select NAME,value,DESCRIPTION from v$parameter where NAME like '<span style="color:#ff6666;">inmemory</span>%';
NAME                                               VALUE      DESCRIPTION
-------------------------------------------------- ---------- ------------------------------------------------------------
inmemory_size                                      218103808  size in bytes of in-memory area
inmemory_clause_default                                       Default in-memory clause for new tables
inmemory_force                                     DEFAULT    Force tables to be in-memory or not
inmemory_query                                     ENABLE     Specifies whether in-memory queries are allowed
inmemory_max_populate_servers                      1          maximum inmemory populate servers
inmemory_trickle_repopulate_servers_percent        1          inmemory trickle repopulate servers percent
6 rows selected.
SQL> 
SQL> show parameter <span style="color:#ff6666;">inmemory</span>;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     1
inmemory_query                       string      ENABLE
inmemory_size                        big integer 208M
inmemory_trickle_repopulate_servers_ integer     1
percent
optimizer_inmemory_aware             boolean     TRUE
SQL> create table t_in_memory as select * from dba_objects;
Table created.
SQL> select TABLE_NAME,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION from user_tables;
TABLE_NAME                     INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS
------------------------------ -------- --------------- -----------------
T_IN_MEMORY

SQL> alter table  T_IN_MEMORY <span style="color:#ff6666;">inmemory</span>;
Table altered.
SQL> select TABLE_NAME,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION from user_tables;
 
TABLE_NAME                INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS
------------------------- -------- --------------- -----------------
T_IN_MEMORY      NONE     AUTO DISTRIBUTE FOR QUERY

本问是对于Oracle 12C中的In-Memory Column Store一个整体的汇总
IM可以针对如下级别进行操作
Column
Table
Materialized view
Tablespace
Partition
可以指定In-Memory操作语句
CREATE TABLE
ALTER TABLE
CREATE TABLESPACE
ALTER TABLESPACE
CREATE MATERIALIZED VIEW
ALTER MATERIALIZED VIEW

参考文档:https://docs.oracle.com/database/121/ADMIN/memory.htm#ADMIN14257

IM控制参数
INMEMORY_SIZE 指定IM分配内存大小,默认值为0,如果启动该值最小为100M;如果在CDB环境中使用,CDB级别设置为整个库级别限制,PDB默认继承CDB设置,但是在实际使用中PDB中总数不能超过CDB限制
INMEMORY_FORCE 指定是否允许数据库中对象使用IM,默认是DEFAULT,即可以实现在对象级别定义INMEMORY or NO INMEMORY,如果设置为OFF 即表示表或者物化视图无法使用IM
INMEMORY_CLAUSE_DEFAULT 默认为空,和NO INMEMORY意义相同,表示创建新对象默认不启用IM,如果配置为INMEMORY,表示新创建对象默认启用IM
INMEMORY_QUERY 默认为TRUE,表示查询是否使用IM特性,设置为FALSE表示查询不使用IM特性
INMEMORY_MAX_POPULATE_SERVERS 默认和系统core一致,用途是把你的表中数据写入到IM中
INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT 控制IM中对象数据的重新载入的进程数,该值为INMEMORY_MAX_POPULATE_SERVERS参数的百分比
OPTIMIZER_INMEMORY_AWARE 该参数是控制优化器成本计算时是否考虑IM,默认为TRUE

impdp 操作IM
TRANSFORM=INMEMORY:y 继承IM导出对象属性
TRANSFORM=INMEMORY:n 不继承IM导出对象属性
TRANSFORM=INMEMORY_CLAUSE:string 修改IM导出对象关于IM的属性