oracle 备份删除分区

目录

前言：

一，定位分区

1，user_tab_partitions

2，使用参数说明

3，命令语句示例

4，根据月份找指定分区

4.1 long to char

4.2 如何使用

4.3 sqlplus执行过程

二，备份

1，使用参数说明

2，命令语句示例

三，删除分区

四，恢复分区

1，table_exists_action

2，使用参数说明

3，命令语句示例

---

前言：

分区方式为：自动分区按月份

CREATE TABLE TEST3(
    ADD_DATE VARCHAR2(6),
    ADD_DATE_TIMESTAMP TIMESTAMP(0) GENERATED ALWAYS AS (TO_TIMESTAMP(ADD_DATE, 'YYYYMM')),
    
)
PARTITION BY RANGE (ADD_DATE_TIMESTAMP ) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(PARTITION P_MAY2021 VALUES LESS THAN (TIMESTAMP '2022-10-10 00:00:00'));

一，定位分区

1，user_tab_partitions

user_tab_partitions 视图存储当前用户下所有分区的信息

2，使用参数说明

partition_position	记录的是分区定位（在分区变化时会跟这变化）
partition_name	分区表的名称
table_name	表名称
high_value	分区的上边界

3，命令语句示例

由于边界无法删除，且每次删除的是时间最久的分区，partition_position =2

根据边界可对定位字段进行验证 high_value 

select table_name,partition_name from user_tab_partitions 
where partition_position=2;

4，根据月份找指定分区

4.1 long to char

high_value 分区边界值是LONG类型，oracle中没有对应函数，只能自定义

CREATE OR REPLACE FUNCTION UTP_HIGH_VALUE_LONG_TO_CHAR(
       in_prarm varchar2)
RETURN varchar AS
       text_c1 varchar2(32767);
       sql_cur varchar2(2000);
begin
      DBMS_OUTPUT.ENABLE (buffer_size=>null);
      sql_cur := 'select high_value from USER_TAB_PARTITIONS where table_name||partition_name = '||chr(39)||in_prarm ||chr(39);
      dbms_output.put_line (sql_cur);
      execute immediate sql_cur into text_c1;
      text_c1 := substr(text_c1, 12, 10);
      text_c1 := substr(TO_CHAR(TO_DATE(text_c1,'YYYY-MM-DD')-1,'YYYY-MM-DD'),1,7);
      RETURN TEXT_C1;
END;

4.2 如何使用

获取table1的2022年12月份数据的分区是哪个

SELECT * FROM (
select table_name,partition_name,UTP_HIGH_VALUE_LONG_TO_CHAR(table_name||partition_name) high_value_str
from user_tab_partitions   WHERE table_name =  'table1'
)S  WHERE high_value_str='2022-12'

4.3 sqlplus执行过程

SQL> CREATE OR REPLACE FUNCTION UTP_HIGH_VALUE_LONG_TO_CHAR(in_prarm varchar2)
  2  RETURN varchar AS
  3         text_c1 varchar2(32767);
       sql_cur varchar2(2000);
  4    5  begin
  6        DBMS_OUTPUT.ENABLE (buffer_size=>null);
  7        sql_cur := 'select high_value from USER_TAB_PARTITIONS where table_name||partition_name = '||chr(39)||in_prarm||chr(39);
      dbms_output.put_line (sql_cur);
      execute immediate sql_cur into text_c1;
  8    9   10        text_c1 := substr(text_c1, 12, 10);
 11        text_c1 := substr(TO_CHAR(TO_DATE(text_c1,'YYYY-MM-DD')-1,'YYYY-MM-DD'),1,7);
      RETURN TEXT_C1;
 12   13  END;
 14  /

Function created.

SQL> SELECT * FROM (
  2  select table_name,partition_name,UTP_HIGH_VALUE_LONG_TO_CHAR(table_name||partition_name) high_value_str
  3  from user_tab_partitions   WHERE table_name =  'TEST1'
)S  WHERE high_value_str='2022-07'
  4    5  ;

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
HIGH_VALUE_STR
--------------------------------------------------------------------------------
TEST1   SYS_P84  2022-07

二，备份

1，使用参数说明

tableName：表名

P1：分区名称

2，命令语句示例

expdp directory=EXPDP_PATH dumpfile=tableName_P1.dmp tables=tableName:P1;

三，删除分区

alter table tableName drop partition P1 update global indexes;

删除分区会破坏索引，需要刷新索引；当然也可以分开执行

alter table tableName drop partition P1;

alter index 索引名 rebuild;

注：使用truncate效果相同

四，恢复分区

1，table_exists_action

而使用impdp完成数据库导入时，若表已经存在，有四种的处理方式：

1)  replace：先drop表，然后创建表，最后插入数据

2)  append：在原来数据的基础上增加数据

3)  truncate：先truncate，然后再插入数据

2，使用参数说明

tableName：表名

P1：分区名称

table_exists_action=append :拼接方式，因为要与之前的数据共存

3，命令语句示例

impdp directory=EXPDP_PATH dumpfile=tableName_P1.dmp tables=tableName:P1
 table_exists_action=append;