[[email protected] ~]$ 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的屬性
![ORACLE 12c索引分裂引起的會話夯[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)