ORACLE 数据归档之二

Oracle 数据归档之二

移动分区支持数据过滤

原文转载

概念

Partition maintenance operations can now be combined with data filtering. For example, a partition can be compressed and moved to a different tablespace, but only the data satisfying the specific filter criteria is actually moved.
分区维护操作现在可以与数据过滤相结合。例如，可以将分区压缩并移动到不同的表空间，但只有满足特定筛选条件的数据才实际移动。
 
Partition maintenance operations with data filtering combine two of the most common data maintenance operations. This combination not only makes the partition maintenance operation more flexible and powerful, but also makes it more performant and less resource intensive compared to the two separate data management operations.
 
分区维护操作与数据过滤结合了两个最常见的数据维护操作。这种组合不仅使分区维护操作更加灵活和强大，而且与两个独立的数据管理操作相比，它还使分区维护更加高效，资源更少。
 

实验

1) 创建分区并插入数据
[leo@www.cndba.cn1 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 12.2.0.1.0 Production on Sun Aug 27 15:24:20 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> conn test/test@pdbcndba
Connected.
SQL> CREATE TABLE test_tbl
(
 id1 NUMBER,
 id2 NUMBER,
 id3 NUMBER
)
PARTITION BY RANGE(id1)
(
  PARTITION p1 VALUES LESS THAN(10),
  PARTITION p2 VALUES LESS THAN(MAXVALUE)
);
 
Table created.
 
 
SQL> INSERT INTO test_tbl VALUES(1, 1, 1);
 
1 row created.
 
SQL> INSERT INTO test_tbl VALUES(2, 2, 2);
 
1 row created.
 
SQL> INSERT INTO test_tbl VALUES(3, 3, 3);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> col table_name for a20
SQL> col partition_name for a20
SQL> SELECT table_name,partition_name
FROM   user_tab_partitions
WHERE  table_name = 'TEST_TBL'; 
 
TABLE_NAME         PARTITION_NAME
-------------------- --------------------
TEST_TBL         P1
TEST_TBL         P2
 
2） 分区p1 有3条数据
SQL> SELECT * FROM test_tbl PARTITION(p1);
 
       ID1      ID2         ID3
---------- ---------- ----------
     1        1           1
     2        2           2
     3        3           3
 
3) 将ID<3 的数据 移到分区p1 。
SQL> ALTER TABLE test_tbl
MOVE PARTITION p1
INCLUDING ROWS WHERE id1 < 3; 
 
Table altered.
4) 可以看到p1分区保存id<3的数据
SQL> SELECT * FROM test_tbl PARTITION(p1);
 
       ID1      ID2         ID3
---------- ---------- ----------
     1        1           1
     2        2           2
 
SQL> SELECT table_name,partition_name
FROM   user_tab_partitions
WHERE  table_name = 'TEST_TBL';  2    3  
 
TABLE_NAME         PARTITION_NAME
-------------------- --------------------
TEST_TBL         P1
TEST_TBL         P2
操作文档

http://docs.oracle.com/database/122/VLDBG/maintenance-partition-can-be-performed.htm#VLDBG-GUID-E5ACAFF8-AB4D-4165-9833-05BB46F149DA

原文链接：https://blog.csdn.net/leo__1990/article/details/89946787