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

    与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.查看默认表空间

    1. SQL> 
    2. SQL> set pagesize 200
    3. SQL> col property_name format a30
    4. SQL> col property_value format a20
    5. SQL> select property_name,property_value from database_properties where property_name in ('DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE');
    6.  
    7. PROPERTY_NAME                  PROPERTY_VALUE
    8. ------------------------------ --------------------
    9. DEFAULT_PERMANENT_TABLESPACE   USERS
    10. DEFAULT_TEMP_TABLESPACE        TEMP
    11.  
    12. SQL>

    2.创建临时表空间

    1. SQL> col file_name format a30
    2. SQL> select file_name,tablespace_name from dba_temp_files;
    3.  
    4. FILE_NAME                      TABLESPACE_NAME
    5. ------------------------------ ------------------------------
    6. /u02/oradata/CDB1/temp01.dbf   TEMP
    7.  
    8. SQL> create temporary tablespace temp02 tempfile '/u02/oradata/CDB1/temp02.dbf' size 5m autoextend off;
    9.  
    10. Tablespace created.
    11.  
    12. SQL> select file_name,tablespace_name from dba_temp_files;
    13.  
    14. FILE_NAME                      TABLESPACE_NAME
    15. ------------------------------ ------------------------------
    16. /u02/oradata/CDB1/temp01.dbf   TEMP
    17. /u02/oradata/CDB1/temp02.dbf   TEMP02
    18.  
    19. SQL>

    3.创建永久表空间

    1. SQL> CREATE TABLESPACE CDB_USERS DATAFILE '/u02/oradata/CDB1/cdb_users01.dbf' size 5m autoextend off segment space management auto extent management local;
    2.  
    3. Tablespace created.
    4.  
    5. SQL> select name from v$datafile;
    6.  
    7. NAME
    8. ------------------------------------------------------------------------------------------------------------------------------------------------------
    9. /u02/oradata/CDB1/system01.dbf
    10. /u02/oradata/CDB1/sysaux01.dbf
    11. /u02/oradata/CDB1/undotbs01.dbf
    12. /u02/oradata/CDB1/pdbseed/system01.dbf
    13. /u02/oradata/CDB1/pdbseed/sysaux01.dbf
    14. /u02/oradata/CDB1/users01.dbf
    15. /u02/oradata/CDB1/pdbseed/undotbs01.dbf
    16. /u02/oradata/CDB1/pdb1/system01.dbf
    17. /u02/oradata/CDB1/pdb1/sysaux01.dbf
    18. /u02/oradata/CDB1/pdb1/undotbs01.dbf
    19. /u02/oradata/CDB1/pdb1/users01.dbf
    20. /u02/oradata/CDB1/pdb2/system01.dbf
    21. /u02/oradata/CDB1/pdb2/sysaux01.dbf
    22. /u02/oradata/CDB1/pdb2/undotbs01.dbf
    23. /u02/oradata/CDB1/pdb2/users01.dbf
    24. /u02/oradata/CDB1/cndbapdb/system01.dbf
    25. /u02/oradata/CDB1/cndbapdb/sysaux01.dbf
    26. /u02/oradata/CDB1/cndbapdb/undotbs01.dbf
    27. /u02/oradata/CDB1/cndbapdb/cndba01.dbf
    28. /u02/oradata/CDB1/cndbapdb2/system01.dbf
    29. /u02/oradata/CDB1/cndbapdb2/sysaux01.dbf
    30. /u02/oradata/CDB1/cndbapdb2/undotbs01.dbf
    31. /u02/oradata/CDB1/cndbapdb2/cndba01.dbf
    32. /u02/oradata/CDB1/EE966433CFF1B7D7E0538A08A8C04375/datafile/o1_mf_system_krc75pcx_.dbf
    33. /u02/oradata/CDB1/EE966433CFF1B7D7E0538A08A8C04375/datafile/o1_mf_sysaux_krc75pd1_.dbf
    34. /u02/oradata/CDB1/EE966433CFF1B7D7E0538A08A8C04375/datafile/o1_mf_undotbs1_krc75pd1_.dbf
    35. /u02/oradata/CDB1/EE966433CFF1B7D7E0538A08A8C04375/datafile/o1_mf_cndba_krc75pd2_.dbf
    36. /u02/oradata/CDB1/cndbapdb4_fresh/system01.dbf
    37. /u02/oradata/CDB1/cndbapdb4_fresh/sysaux01.dbf
    38. /u02/oradata/CDB1/cndbapdb4_fresh/undotbs01.dbf
    39. /u02/oradata/CDB1/cndbapdb4_fresh/users01.dbf
    40. /u02/oradata/CDB1/EECFD8030E829AE2E0538A08A8C02B62/datafile/o1_mf_system_krlrcwxh_.dbf
    41. /u02/oradata/CDB1/EECFD8030E829AE2E0538A08A8C02B62/datafile/o1_mf_sysaux_krlrcwxm_.dbf
    42. /u02/oradata/CDB1/EECFD8030E829AE2E0538A08A8C02B62/datafile/o1_mf_undotbs1_krlrcwxn_.dbf
    43. /u02/oradata/CDB1/EECFD8030E829AE2E0538A08A8C02B62/datafile/o1_mf_users_krlrcwxn_.dbf
    44. /u02/oradata/CDB1/cdb_users01.dbf
    45.  
    46. 36 rows selected.
    47.  
    48. SQL>

    4.创建UNDO表空间

    1. SQL> create undo tablespace undotbs02 datafile '/u02/oradata/CDB1/undotbs02.dbf' size 5m reuse autoextend off extent management local;
    2.  
    3. Tablespace created.
    4.  
    5. SQL> select name from v$datafile;
    6.  
    7. NAME
    8. ------------------------------------------------------------------------------------------------------------------------------------------------------
    9. /u02/oradata/CDB1/system01.dbf
    10. /u02/oradata/CDB1/sysaux01.dbf
    11. /u02/oradata/CDB1/undotbs01.dbf
    12. /u02/oradata/CDB1/pdbseed/system01.dbf
    13. /u02/oradata/CDB1/pdbseed/sysaux01.dbf
    14. /u02/oradata/CDB1/users01.dbf
    15. /u02/oradata/CDB1/pdbseed/undotbs01.dbf
    16. /u02/oradata/CDB1/pdb1/system01.dbf
    17. /u02/oradata/CDB1/pdb1/sysaux01.dbf
    18. /u02/oradata/CDB1/pdb1/undotbs01.dbf
    19. /u02/oradata/CDB1/pdb1/users01.dbf
    20. /u02/oradata/CDB1/pdb2/system01.dbf
    21. /u02/oradata/CDB1/pdb2/sysaux01.dbf
    22. /u02/oradata/CDB1/pdb2/undotbs01.dbf
    23. /u02/oradata/CDB1/pdb2/users01.dbf
    24. /u02/oradata/CDB1/cndbapdb/system01.dbf
    25. /u02/oradata/CDB1/cndbapdb/sysaux01.dbf
    26. /u02/oradata/CDB1/cndbapdb/undotbs01.dbf
    27. /u02/oradata/CDB1/cndbapdb/cndba01.dbf
    28. /u02/oradata/CDB1/cndbapdb2/system01.dbf
    29. /u02/oradata/CDB1/cndbapdb2/sysaux01.dbf
    30. /u02/oradata/CDB1/cndbapdb2/undotbs01.dbf
    31. /u02/oradata/CDB1/cndbapdb2/cndba01.dbf
    32. /u02/oradata/CDB1/EE966433CFF1B7D7E0538A08A8C04375/datafile/o1_mf_system_krc75pcx_.dbf
    33. /u02/oradata/CDB1/EE966433CFF1B7D7E0538A08A8C04375/datafile/o1_mf_sysaux_krc75pd1_.dbf
    34. /u02/oradata/CDB1/EE966433CFF1B7D7E0538A08A8C04375/datafile/o1_mf_undotbs1_krc75pd1_.dbf
    35. /u02/oradata/CDB1/EE966433CFF1B7D7E0538A08A8C04375/datafile/o1_mf_cndba_krc75pd2_.dbf
    36. /u02/oradata/CDB1/cndbapdb4_fresh/system01.dbf
    37. /u02/oradata/CDB1/cndbapdb4_fresh/sysaux01.dbf
    38. /u02/oradata/CDB1/cndbapdb4_fresh/undotbs01.dbf
    39. /u02/oradata/CDB1/cndbapdb4_fresh/users01.dbf
    40. /u02/oradata/CDB1/EECFD8030E829AE2E0538A08A8C02B62/datafile/o1_mf_system_krlrcwxh_.dbf
    41. /u02/oradata/CDB1/EECFD8030E829AE2E0538A08A8C02B62/datafile/o1_mf_sysaux_krlrcwxm_.dbf
    42. /u02/oradata/CDB1/EECFD8030E829AE2E0538A08A8C02B62/datafile/o1_mf_undotbs1_krlrcwxn_.dbf
    43. /u02/oradata/CDB1/EECFD8030E829AE2E0538A08A8C02B62/datafile/o1_mf_users_krlrcwxn_.dbf
    44. /u02/oradata/CDB1/cdb_users01.dbf
    45. /u02/oradata/CDB1/undotbs02.dbf
    46.  
    47. 37 rows selected.
    48.  
    49. SQL>

    5.指定默认表空间

    ALTER DATABASE DEFAULT TABLESPACE cdb_users;

    6.指定默认临时表空间

    ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp01;

    7.查看内存池的大小

    1.  
    2. SQL> col pool format a9
    3. SQL> col populate_status format a15
    4. SQL> select con_id,pool,trunc(ALLOC_BYTES/(1024*1024*1024),2) "ALLOC_GB",
    5.   2  TRUNC(USED_BYTES/(1024*1024*1024),2) "USED_GB",
    6.   3  POPULATE_STATUS
    7.   4  FROM V$INMEMORY_AREA;
    8.  
    9.     CON_ID POOL        ALLOC_GB    USED_GB POPULATE_STATUS
    10. ---------- --------- ---------- ---------- ---------------
    11.          1 1MB POOL           0          0 OUT OF MEMORY
    12.          1 64KB POOL          0          0 OUT OF MEMORY
    13.          2 1MB POOL           0          0 OUT OF MEMORY
    14.          2 64KB POOL          0          0 OUT OF MEMORY
    15.          3 1MB POOL           0          0 OUT OF MEMORY
    16.          3 64KB POOL          0          0 OUT OF MEMORY
    17.          4 1MB POOL           0          0 OUT OF MEMORY
    18.          4 64KB POOL          0          0 OUT OF MEMORY
    19.          5 1MB POOL           0          0 OUT OF MEMORY
    20.          5 64KB POOL          0          0 OUT OF MEMORY
    21.          6 1MB POOL           0          0 OUT OF MEMORY
    22.  
    23.     CON_ID POOL        ALLOC_GB    USED_GB POPULATE_STATUS
    24. ---------- --------- ---------- ---------- ---------------
    25.          6 64KB POOL          0          0 OUT OF MEMORY
    26.          7 1MB POOL           0          0 OUT OF MEMORY
    27.          7 64KB POOL          0          0 OUT OF MEMORY
    28.          8 1MB POOL           0          0 OUT OF MEMORY
    29.          8 64KB POOL          0          0 OUT OF MEMORY
    30.          9 1MB POOL           0          0 OUT OF MEMORY
    31.          9 64KB POOL          0          0 OUT OF MEMORY
    32.  
    33. 18 rows selected.
    34.  
    35. SQL>

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

    1. SQL> SELECT NAME,VALUE/(1024*1024*1024) "SIZE_IN_GB" FROM V$SGA;
    2.  
    3. NAME                 SIZE_IN_GB
    4. -------------------- ----------
    5. Fixed Size           .008510396
    6. Variable Size         .36328125
    7. Database Buffers      .20703125
    8. Redo Buffers          .00711441
    9.  
    10. SQL>

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

    9.查看当前INMEMORY_SIZE的大小

    1. SQL> show con_name;
    2.  
    3. CON_NAME
    4. ------------------------------
    5. CDB$ROOT
    6. SQL> set lines 120
    7. SQL> show parameter inmemory_size
    8.  
    9. NAME                                 TYPE        VALUE
    10. ------------------------------------ ----------- ------------------------------
    11. inmemory_size                        big integer 0
    12. SQL>

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

    1. SQL> 
    2. SQL> show con_name;
    3.  
    4. CON_NAME
    5. ------------------------------
    6. CDB$ROOT
    7. SQL> set lines 120
    8. SQL> show parameter inmemory_size
    9.  
    10. NAME                                 TYPE        VALUE
    11. ------------------------------------ ----------- ------------------------------
    12. inmemory_size                        big integer 0
    13. SQL> 
    14. SQL> 
    15. SQL> ALTER SYSTEM SET INMEMORY_SIZE=100M SCOPE=SPFILE;
    16.  
    17. System altered.
    18.  
    19. SQL> shutdown immediate
    20. Database closed.
    21. Database dismounted.
    22. ORACLE instance shut down.
    23. SQL> startup
    24. ORACLE instance started.
    25.  
    26. Total System Global Area  629145392 bytes
    27. Fixed Size                  9137968 bytes
    28. Variable Size             251658240 bytes
    29. Database Buffers          255852544 bytes
    30. Redo Buffers                7639040 bytes
    31. In-Memory Area            104857600 bytes
    32. Database mounted.
    33. Database opened.
    34. SQL> show parameter inmemory_size
    35.  
    36. NAME                                 TYPE        VALUE
    37. ------------------------------------ ----------- ------------------------------
    38. inmemory_size                        big integer 100M
    39. SQL>

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

    1. SQL> 
    2. SQL> show parameter inmemory_size
    3.  
    4. NAME                                 TYPE        VALUE
    5. ------------------------------------ ----------- ------------------------------
    6. inmemory_size                        big integer 100M
    7. SQL> alter system set inmemory_size = 250m scope=both;
    8.  
    9. System altered.
    10.  
    11. SQL> show parameter inmemory_size
    12.  
    13. NAME                                 TYPE        VALUE
    14. ------------------------------------ ----------- ------------------------------
    15. inmemory_size                        big integer 250M
    16. SQL>

    12.禁用IM列式存储

    1. SQL> 
    2. SQL> ALTER SYSTEM SET INMEMORY_SIZE=0M SCOPE=SPFILE;
    3.  
    4. System altered.
    5.  
    6. SQL> show parameter inmemory_size
    7.  
    8. NAME                                 TYPE        VALUE
    9. ------------------------------------ ----------- ------------------------------
    10. inmemory_size                        big integer 250M
    11. SQL> ALTER SYSTEM SET INMEMORY_SIZE=0M SCOPE=SPFILE;
    12.  
    13. System altered.
    14.  
    15. SQL> shutdown immediate
    16. Database closed.
    17. Database dismounted.
    18. ORACLE instance shut down.
    19. SQL> 
    20. SQL> startup
    21. ORACLE instance started.
    22.  
    23. Total System Global Area  629145392 bytes
    24. Fixed Size                  9137968 bytes
    25. Variable Size             297795584 bytes
    26. Database Buffers          314572800 bytes
    27. Redo Buffers                7639040 bytes
    28. Database mounted.
    29. Database opened.
    30. SQL> show parameter inmemory_size
    31.  
    32. NAME                                 TYPE        VALUE
    33. ------------------------------------ ----------- ------------------------------
    34. inmemory_size                        big integer 0
    35. SQL>

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

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

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

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

    1. [oracle@oracle-db-19c ~]$ sqlplus / as sysdba
    2.  
    3. SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 5 20:17:50 2022
    4. Version 19.3.0.0.0
    5.  
    6. Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    7.  
    8.  
    9. Connected to:
    10. Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    11. Version 19.3.0.0.0
    12.  
    13. SQL> ALTER SYSTEM SET INMEMORY_SIZE=100M SCOPE=SPFILE;
    14.  
    15. System altered.
    16.  
    17. SQL> shutdown immediate;
    18. Database closed.
    19. Database dismounted.
    20. ORACLE instance shut down.
    21. SQL> 
    22. SQL> startup
    23. ORACLE instance started.
    24.  
    25. Total System Global Area  629145392 bytes
    26. Fixed Size                  9137968 bytes
    27. Variable Size             251658240 bytes
    28. Database Buffers          255852544 bytes
    29. Redo Buffers                7639040 bytes
    30. In-Memory Area            104857600 bytes
    31. Database mounted.
    32. Database opened.
    33. SQL> 
    34. SQL> 
    35. SQL> show pdbs;
    36.  
    37.     CON_ID CON_NAME                       OPEN MODE  RESTRICTED
    38. ---------- ------------------------------ ---------- ----------
    39.          2 PDB$SEED                       READ ONLY  NO
    40.          3 PDB1                           READ WRITE NO
    41.          4 PDB2                           MOUNTED
    42.          5 CNDBAPDB                       MOUNTED
    43.          6 CNDBAPDB3                      MOUNTED
    44.          7 CNDBAPDB2                      MOUNTED
    45.          8 CNDBAPDB4_FRESH                MOUNTED
    46.          9 CNDBAPDB6                      MOUNTED
    47. SQL> 
    48. SQL> alter session set container=PDB1;
    49.  
    50. Session altered.
    51.  
    52. SQL> create user cndba identified by cndba;
    53.  
    54. User created.
    55.  
    56. SQL> grant connect,resource,dba to cndba;
    57.  
    58. Grant succeeded.
    59.  
    60. SQL>
    61. USER is "SYS"
    62. SQL> 
    63. SQL> alter session set container=PDB1;
    64.  
    65. Session altered.
    66.  
    67. SQL> create table cndba.cndba INMEMORY PRIORITY LOW as select * from dba_objects;
    68.  
    69. Table created.
    70.  
    71. SQL> select segment_name,populate_status from v$im_segments where segment_name = 'CNDBA';
    72.  
    73. no rows selected
    74.  
    75. ## 查询表，已经将数据加载到内存
    76.  
    77. SQL> select count(1) from cndba.cndba;
    78.  
    79.   COUNT(1)
    80. ----------
    81.      73275
    82.  
    83. SQL>
    84. SQL> col SEGMENT_NAME FORMAT A20
    85. SQL> col populate_status FORMAT A20
    86. SQL> select segment_name,populate_status from v$im_segments where segment_name = 'CNDBA';
    87.  
    88. SEGMENT_NAME         POPULATE_STATUS
    89. -------------------- --------------------
    90. CNDBA                COMPLETED
    91.  
    92. SQL>

    IM列式存储的优先级

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

  • 相关阅读:
    销售人员打开Dynamics 365 CRM点击模块弹出Insufficient Permissions提示
    灵性图书馆：好书推荐-《在荷欧波诺波诺中遇见真正的自己》
    用 Python 这样去创建词云不是更美嘛？
    檢測項目簡體字
    springboot网络安全考核平台毕业设计源码
    BI-SQL丨WHILE
    JDBC教程
    各类值得收藏的开源项目推荐介绍
    7.1-安全保护等级 7.2-安全防护体系 7.3-数据安全策略 7.4-安全防护策略
    android glide实现高斯模糊，毛玻璃效果，加载图片
  • 原文地址：https://blog.csdn.net/u011868279/article/details/128160977