您现在的位置是：首页 > 数据库

当前栏目

ORACLE 数据库表空间的管理以及IM列式存储

Oracle 数据库存储管理以及空间 IM 列式

2023-09-14 09:13:29 时间

与Non-CDB相比，CDB中的表空间主要有以下几个特点：

一个永久表空间只能与一个容器相关联。
在当前容器中创建表空间时，表空间将与该容器相关联。
当CDB禁用本地UNDO模式时，CDB只能有一个活动的UNDO表空间，或者Oracle RAC CDB的每一个节点各有一个活动的UNDO表空间。当为CDB启用本地UNDO模式时，CDB中的每个容器都有子句的UNDO表空间。
在ORACLE RAC集群中，每个节点都需要一个本地UNDO表空间。
CDB中的每个容器都有子句的默认临时表空间，包括CDB root、每个PDB、每个应用程序root和每个应用程序PDB。

管理CDB表空间

1.查看默认表空间

SQL> 
SQL> set pagesize 200
SQL> col property_name format a30
SQL> col property_value format a20
SQL> select property_name,property_value from database_properties where property_name in ('DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE');

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DEFAULT_PERMANENT_TABLESPACE   USERS
DEFAULT_TEMP_TABLESPACE        TEMP

SQL>

2.创建临时表空间

SQL> col file_name format a30
SQL> select file_name,tablespace_name from dba_temp_files;

FILE_NAME                      TABLESPACE_NAME
------------------------------ ------------------------------
/u02/oradata/CDB1/temp01.dbf   TEMP

SQL> create temporary tablespace temp02 tempfile '/u02/oradata/CDB1/temp02.dbf' size 5m autoextend off;

Tablespace created.

SQL> select file_name,tablespace_name from dba_temp_files;

FILE_NAME                      TABLESPACE_NAME
------------------------------ ------------------------------
/u02/oradata/CDB1/temp01.dbf   TEMP
/u02/oradata/CDB1/temp02.dbf   TEMP02

SQL>

3.创建永久表空间

SQL> CREATE TABLESPACE CDB_USERS DATAFILE '/u02/oradata/CDB1/cdb_users01.dbf' size 5m autoextend off segment space management auto extent management local;

Tablespace created.

SQL> select name from v$datafile;

NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/u02/oradata/CDB1/system01.dbf
/u02/oradata/CDB1/sysaux01.dbf
/u02/oradata/CDB1/undotbs01.dbf
/u02/oradata/CDB1/pdbseed/system01.dbf
/u02/oradata/CDB1/pdbseed/sysaux01.dbf
/u02/oradata/CDB1/users01.dbf
/u02/oradata/CDB1/pdbseed/undotbs01.dbf
/u02/oradata/CDB1/pdb1/system01.dbf
/u02/oradata/CDB1/pdb1/sysaux01.dbf
/u02/oradata/CDB1/pdb1/undotbs01.dbf
/u02/oradata/CDB1/pdb1/users01.dbf
/u02/oradata/CDB1/pdb2/system01.dbf
/u02/oradata/CDB1/pdb2/sysaux01.dbf
/u02/oradata/CDB1/pdb2/undotbs01.dbf
/u02/oradata/CDB1/pdb2/users01.dbf
/u02/oradata/CDB1/cndbapdb/system01.dbf
/u02/oradata/CDB1/cndbapdb/sysaux01.dbf
/u02/oradata/CDB1/cndbapdb/undotbs01.dbf
/u02/oradata/CDB1/cndbapdb/cndba01.dbf
/u02/oradata/CDB1/cndbapdb2/system01.dbf
/u02/oradata/CDB1/cndbapdb2/sysaux01.dbf
/u02/oradata/CDB1/cndbapdb2/undotbs01.dbf
/u02/oradata/CDB1/cndbapdb2/cndba01.dbf
/u02/oradata/CDB1/EE966433CFF1B7D7E0538A08A8C04375/datafile/o1_mf_system_krc75pcx_.dbf
/u02/oradata/CDB1/EE966433CFF1B7D7E0538A08A8C04375/datafile/o1_mf_sysaux_krc75pd1_.dbf
/u02/oradata/CDB1/EE966433CFF1B7D7E0538A08A8C04375/datafile/o1_mf_undotbs1_krc75pd1_.dbf
/u02/oradata/CDB1/EE966433CFF1B7D7E0538A08A8C04375/datafile/o1_mf_cndba_krc75pd2_.dbf
/u02/oradata/CDB1/cndbapdb4_fresh/system01.dbf
/u02/oradata/CDB1/cndbapdb4_fresh/sysaux01.dbf
/u02/oradata/CDB1/cndbapdb4_fresh/undotbs01.dbf
/u02/oradata/CDB1/cndbapdb4_fresh/users01.dbf
/u02/oradata/CDB1/EECFD8030E829AE2E0538A08A8C02B62/datafile/o1_mf_system_krlrcwxh_.dbf
/u02/oradata/CDB1/EECFD8030E829AE2E0538A08A8C02B62/datafile/o1_mf_sysaux_krlrcwxm_.dbf
/u02/oradata/CDB1/EECFD8030E829AE2E0538A08A8C02B62/datafile/o1_mf_undotbs1_krlrcwxn_.dbf
/u02/oradata/CDB1/EECFD8030E829AE2E0538A08A8C02B62/datafile/o1_mf_users_krlrcwxn_.dbf
/u02/oradata/CDB1/cdb_users01.dbf

36 rows selected.

SQL>

4.创建UNDO表空间

SQL> create undo tablespace undotbs02 datafile '/u02/oradata/CDB1/undotbs02.dbf' size 5m reuse autoextend off extent management local;

Tablespace created.

SQL> select name from v$datafile;

NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/u02/oradata/CDB1/system01.dbf
/u02/oradata/CDB1/sysaux01.dbf
/u02/oradata/CDB1/undotbs01.dbf
/u02/oradata/CDB1/pdbseed/system01.dbf
/u02/oradata/CDB1/pdbseed/sysaux01.dbf
/u02/oradata/CDB1/users01.dbf
/u02/oradata/CDB1/pdbseed/undotbs01.dbf
/u02/oradata/CDB1/pdb1/system01.dbf
/u02/oradata/CDB1/pdb1/sysaux01.dbf
/u02/oradata/CDB1/pdb1/undotbs01.dbf
/u02/oradata/CDB1/pdb1/users01.dbf
/u02/oradata/CDB1/pdb2/system01.dbf
/u02/oradata/CDB1/pdb2/sysaux01.dbf
/u02/oradata/CDB1/pdb2/undotbs01.dbf
/u02/oradata/CDB1/pdb2/users01.dbf
/u02/oradata/CDB1/cndbapdb/system01.dbf
/u02/oradata/CDB1/cndbapdb/sysaux01.dbf
/u02/oradata/CDB1/cndbapdb/undotbs01.dbf
/u02/oradata/CDB1/cndbapdb/cndba01.dbf
/u02/oradata/CDB1/cndbapdb2/system01.dbf
/u02/oradata/CDB1/cndbapdb2/sysaux01.dbf
/u02/oradata/CDB1/cndbapdb2/undotbs01.dbf
/u02/oradata/CDB1/cndbapdb2/cndba01.dbf
/u02/oradata/CDB1/EE966433CFF1B7D7E0538A08A8C04375/datafile/o1_mf_system_krc75pcx_.dbf
/u02/oradata/CDB1/EE966433CFF1B7D7E0538A08A8C04375/datafile/o1_mf_sysaux_krc75pd1_.dbf
/u02/oradata/CDB1/EE966433CFF1B7D7E0538A08A8C04375/datafile/o1_mf_undotbs1_krc75pd1_.dbf
/u02/oradata/CDB1/EE966433CFF1B7D7E0538A08A8C04375/datafile/o1_mf_cndba_krc75pd2_.dbf
/u02/oradata/CDB1/cndbapdb4_fresh/system01.dbf
/u02/oradata/CDB1/cndbapdb4_fresh/sysaux01.dbf
/u02/oradata/CDB1/cndbapdb4_fresh/undotbs01.dbf
/u02/oradata/CDB1/cndbapdb4_fresh/users01.dbf
/u02/oradata/CDB1/EECFD8030E829AE2E0538A08A8C02B62/datafile/o1_mf_system_krlrcwxh_.dbf
/u02/oradata/CDB1/EECFD8030E829AE2E0538A08A8C02B62/datafile/o1_mf_sysaux_krlrcwxm_.dbf
/u02/oradata/CDB1/EECFD8030E829AE2E0538A08A8C02B62/datafile/o1_mf_undotbs1_krlrcwxn_.dbf
/u02/oradata/CDB1/EECFD8030E829AE2E0538A08A8C02B62/datafile/o1_mf_users_krlrcwxn_.dbf
/u02/oradata/CDB1/cdb_users01.dbf
/u02/oradata/CDB1/undotbs02.dbf

37 rows selected.

SQL>

5.指定默认表空间

ALTER DATABASE DEFAULT TABLESPACE cdb_users;

6.指定默认临时表空间

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp01;

7.查看内存池的大小


SQL> col pool format a9
SQL> col populate_status format a15
SQL> select con_id,pool,trunc(ALLOC_BYTES/(1024*1024*1024),2) "ALLOC_GB",
  2  TRUNC(USED_BYTES/(1024*1024*1024),2) "USED_GB",
  3  POPULATE_STATUS
  4  FROM V$INMEMORY_AREA;

    CON_ID POOL        ALLOC_GB    USED_GB POPULATE_STATUS
---------- --------- ---------- ---------- ---------------
         1 1MB POOL           0          0 OUT OF MEMORY
         1 64KB POOL          0          0 OUT OF MEMORY
         2 1MB POOL           0          0 OUT OF MEMORY
         2 64KB POOL          0          0 OUT OF MEMORY
         3 1MB POOL           0          0 OUT OF MEMORY
         3 64KB POOL          0          0 OUT OF MEMORY
         4 1MB POOL           0          0 OUT OF MEMORY
         4 64KB POOL          0          0 OUT OF MEMORY
         5 1MB POOL           0          0 OUT OF MEMORY
         5 64KB POOL          0          0 OUT OF MEMORY
         6 1MB POOL           0          0 OUT OF MEMORY

    CON_ID POOL        ALLOC_GB    USED_GB POPULATE_STATUS
---------- --------- ---------- ---------- ---------------
         6 64KB POOL          0          0 OUT OF MEMORY
         7 1MB POOL           0          0 OUT OF MEMORY
         7 64KB POOL          0          0 OUT OF MEMORY
         8 1MB POOL           0          0 OUT OF MEMORY
         8 64KB POOL          0          0 OUT OF MEMORY
         9 1MB POOL           0          0 OUT OF MEMORY
         9 64KB POOL          0          0 OUT OF MEMORY

18 rows selected.

SQL>

8.查看当前IM内存的大小

SQL> SELECT NAME,VALUE/(1024*1024*1024) "SIZE_IN_GB" FROM V$SGA;

NAME                 SIZE_IN_GB
-------------------- ----------
Fixed Size           .008510396
Variable Size         .36328125
Database Buffers      .20703125
Redo Buffers          .00711441

SQL>

启动IM 列式存储的具体步骤：

9.查看当前INMEMORY_SIZE的大小

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL> set lines 120
SQL> show parameter inmemory_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_size                        big integer 0
SQL>

10.修改INMEMROY_SIZE的大小，INMEMRORY_SIZE最小为100MB

SQL> 
SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL> set lines 120
SQL> show parameter inmemory_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_size                        big integer 0
SQL> 
SQL> 
SQL> ALTER SYSTEM SET INMEMORY_SIZE=100M SCOPE=SPFILE;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  629145392 bytes
Fixed Size                  9137968 bytes
Variable Size             251658240 bytes
Database Buffers          255852544 bytes
Redo Buffers                7639040 bytes
In-Memory Area            104857600 bytes
Database mounted.
Database opened.
SQL> show parameter inmemory_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_size                        big integer 100M
SQL>

11.动态修改IM列式存储的大小

SQL> 
SQL> show parameter inmemory_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_size                        big integer 100M
SQL> alter system set inmemory_size = 250m scope=both;

System altered.

SQL> show parameter inmemory_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_size                        big integer 250M
SQL>

12.禁用IM列式存储

SQL> 
SQL> ALTER SYSTEM SET INMEMORY_SIZE=0M SCOPE=SPFILE;

System altered.

SQL> show parameter inmemory_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_size                        big integer 250M
SQL> ALTER SYSTEM SET INMEMORY_SIZE=0M SCOPE=SPFILE;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> startup
ORACLE instance started.

Total System Global Area  629145392 bytes
Fixed Size                  9137968 bytes
Variable Size             297795584 bytes
Database Buffers          314572800 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.
SQL> show parameter inmemory_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_size                        big integer 0
SQL>

将对象存储到IM列式存储中

注意以下对象不支持IM列式存储：

索引
索引组织表
散列群集（Hash Cluster）
SYS用户的对象和存储在SYSTEM或SYSAUX表空间中的对象。

默认情况下，需要用户指定需要将某个对象存储到IM列式存储中。如果一个segment（分段）在磁盘上占用的空间小于64KB，那么将不会存储到IM列式存储中。

[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 5 20:17:50 2022
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> ALTER SYSTEM SET INMEMORY_SIZE=100M SCOPE=SPFILE;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> startup
ORACLE instance started.

Total System Global Area  629145392 bytes
Fixed Size                  9137968 bytes
Variable Size             251658240 bytes
Database Buffers          255852544 bytes
Redo Buffers                7639040 bytes
In-Memory Area            104857600 bytes
Database mounted.
Database opened.
SQL> 
SQL> 
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           MOUNTED
         5 CNDBAPDB                       MOUNTED
         6 CNDBAPDB3                      MOUNTED
         7 CNDBAPDB2                      MOUNTED
         8 CNDBAPDB4_FRESH                MOUNTED
         9 CNDBAPDB6                      MOUNTED
SQL> 
SQL> alter session set container=PDB1;

Session altered.

SQL> create user cndba identified by cndba;

User created.

SQL> grant connect,resource,dba to cndba;

Grant succeeded.

SQL>
USER is "SYS"
SQL> 
SQL> alter session set container=PDB1;

Session altered.

SQL> create table cndba.cndba INMEMORY PRIORITY LOW as select * from dba_objects;

Table created.

SQL> select segment_name,populate_status from v$im_segments where segment_name = 'CNDBA';

no rows selected

## 查询表，已经将数据加载到内存

SQL> select count(1) from cndba.cndba;

  COUNT(1)
----------
     73275

SQL>
SQL> col SEGMENT_NAME FORMAT A20
SQL> col populate_status FORMAT A20
SQL> select segment_name,populate_status from v$im_segments where segment_name = 'CNDBA';

SEGMENT_NAME         POPULATE_STATUS
-------------------- --------------------
CNDBA                COMPLETED

SQL>

IM列式存储的优先级

优先级设置适用于整个表、分区、子分区，但不适用于列。

猜你喜欢

【Spring源码学习】spring IOC容器管理
Java 类加载器
C#实现水晶报表绑定数据并实现打印1
“半路出家”的程序猿怎么不被“熊”
Android版OpenCV图像处理技术亲自验证[二十八]之形态操作侵蚀(附源码)
自己写的一个Vue
ant design v4笔记
nvl函数
系统架构：
案例：ELK日志分析系统
java8 lambda 求list最大值、最小值、平均值、求和、中位数、属性排序(空指针异常，空值排前、排后)、去重
jQuery 效果函数
.NET魔法堂：工程构建基石->MSBuild
SAP Spartacus Product 明细页面 meta description 标签页的数据源
linux 从百度网盘下载文件的方法
计网基础 ·运输层
将Python脚本变为命令行--click模块使用
【Pytorch with fastai】第 2 章：从模型到生产
黑盒测试方法论
遥感数据下载
[Mockito] Spring Unit Testing with Mockito

相关主题

Oracle数据库
oracle-密码
oracle数据库备份
oracle 锁系列
oracle的日期蛋

zl程序教程