12c pdb_profiles$&In-Memory

来源：互联网发布：只有我知bilibili电影编辑：程序博客网时间：2024/06/11 23:34

[oracle@ora12 ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 7 19:31:20 2015Copyright (c) 1982, 2014, Oracle.  All rights reserved.Connected to an idle instance.SQL> startup ORACLE instance started.Total System Global Area 1862270976 bytesFixed Size                  2925648 bytesVariable Size             553651120 bytesDatabase Buffers         1291845632 bytesRedo Buffers               13848576 bytesDatabase mounted.Database opened.SQL> SQL> show con_nameCON_NAME------------------------------CDB$ROOTSQL> alter system set open_cursors=500 container=all;System altered.SQL> show parameter open_cursors;NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------open_cursors                         integer     500SQL> 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                           MOUNTEDSQL> conn / as sysdbaConnected.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                         MOUNTEDSQL> 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 fixedtables or views onlySQL> 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 WRITESQL> show parameter open_cursors;NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------open_cursors                         integer     120SQL>  COL OWNER FOR A10SQL> select con_id,owner,object_type from cdb_objects where object_name='PDB_SPFILE$';    CON_ID OWNER      OBJECT_TYPE---------- ---------- -----------------------         3 SYS        TABLESQL> conn / as sysdbaConnected.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        TABLESQL> COL DB_UNIQ_NAME FOR A10SQL> COL NAME FOR A15SQL>  COL VALUE$ FOR A10SQL> SELECT DB_UNIQ_NAME,PDB_UID,NAME,VALUE$ FROM PDB_SPFILE$;DB_UNIQ_NA    PDB_UID NAME            VALUE$---------- ---------- --------------- ----------D12C1       575147372 open_cursors    120SQL> SELECT DB_UNIQ_NAME,PDB_UID,NAME,VALUE$ FROM PDB_SPFILE$;DB_UNIQ_NA    PDB_UID NAME            VALUE$---------- ---------- --------------- ----------D12C1       575147372 open_cursors    120SQL>  select value$ from pdb_spfile$ where name='open_cursors';VALUE$----------120SQL> 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 selectedSQL>

删除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                         0PL/SQL Release 12.1.0.2.0 -                                                               0CORE    12.1.0.2.0                                                                        0TNS for Linux: Version 12.1.0.2.0 -                                                       0NLSRTL Version 12.1.0.2.0 -                                                               0In-Memory Aggregation                                            TRUESQL> select parameter,value from v$option where parameter like '<span style="color:#ff6666;">In-Memory%</span>';PARAMETER                                                        VALUE---------------------------------------------------------------- ----------In-Memory Column Store                                           TRUEIn-Memory Aggregation                                            TRUESQL> 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 areainmemory_clause_default                                       Default in-memory clause for new tablesinmemory_force                                     DEFAULT    Force tables to be in-memory or notinmemory_query                                     ENABLE     Specifies whether in-memory queries are allowedinmemory_max_populate_servers                      1          maximum inmemory populate serversinmemory_trickle_repopulate_servers_percent        1          inmemory trickle repopulate servers percent6 rows selected.SQL> SQL> show parameter <span style="color:#ff6666;">inmemory</span>;NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------inmemory_clause_default              stringinmemory_force                       string      DEFAULTinmemory_max_populate_servers        integer     1inmemory_query                       string      ENABLEinmemory_size                        big integer 208Minmemory_trickle_repopulate_servers_ integer     1percentoptimizer_inmemory_aware             boolean     TRUESQL> 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_MEMORYSQL> 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的属性

0 0