oracle 备份某个分区的数据,间隔分区表导出指定的分区数据

180大表备份处理方法：

准备备份空间：

SQL> conn zy/xxx

已连接。

SQL> create or replace directory bak as

'/bak/dmp180';

目录已创建。

SQL> grant read,write on directory bak to public;

授权成功。

SQL> exit

查看最大分区表数据：

SEGMENT_NAME SUM(BYTES)/1024/1024/1024

1 IPTV_ZTE_DATA 751

2 IPTV_HW_DATA 266

3 AAA_DATA 63

4 KD_HW_DATA 39

5 HJLL_ONLINE_DATA 38

6 HJJL_LIULIANG_DATA 21

7 KD_FH_DATA 6

参考语句zy用户下：

select segment_name,sum(bytes)/1024/1024/1024 from

user_segments

where segment_type in('TABLE PARTITION')

group by segment_name order by sum(bytes)

desc;

确认可清理备份的数据：

select * from IPTV_ZTE_DATA where

currenttime

select * from IPTV_HW_DATA where PCF_TIMESTAMP

select * from AAA_DATA where

inserttime

select * from KD_HW_DATA where

inserttime

select * from HJLL_ONLINE_DATA where inserttime

select * from HJJL_LIULIANG_DATA where INSERTTIME

select * from KD_FH_DATA where INSERTTIME

准备导出备份脚本：

通过pl/sql 查看分区结构，查看对应表2018年3月1日之前的分区名称

或者通过如下语句查询：

SELECT *

FROM (SELECT TABLE_OWNER,

TABLE_NAME,

PARTITION_NAME,

substr(LONG_HELP.SUBSTR_OF('SELECT

HIGH_VALUE

FROM DBA_TAB_PARTITIONS

WHERE TABLE_OWNER=:TABLE_OWNER

AND TABLE_NAME=:TABLE_NAME

AND PARTITION_NAME=:PARTITION_NAME',

1,

4000,

'TABLE_OWNER',

TABLE_OWNER,

'TABLE_NAME',

TABLE_NAME,

'PARTITION_NAME',

PARTITION_NAME),11,19) HIGH_VALUE

FROM

DBA_TAB_PARTITIONS

where

table_name

in('IPTV_ZTE_DATA','IPTV_HW_DATA','AAA_DATA','KD_HW_DATA','HJLL_ONLINE_DATA','HJJL_LIULIANG_DATA','KD_FH_DATA')

)

b

where

b.high_value='2018-02-28 00:00:00'

order by HIGH_VALUE;

函数准备：

create or replace package long_help

authid current_user

as

function substr_of

( p_query in varchar2,

p_from in number,

p_for in number,

p_name1 in varchar2 default NULL,

p_bind1 in varchar2 default NULL,

p_name2 in varchar2 default NULL,

p_bind2 in varchar2 default NULL,

p_name3 in varchar2 default NULL,

p_bind3 in varchar2 default NULL,

p_name4 in varchar2 default NULL,

p_bind4 in varchar2 default NULL )

return varchar2;

end;

/

create or replace package body long_help

as

g_cursor number :=

dbms_sql.open_cursor;

g_query varchar2(32765);

procedure bind_variable( p_name in varchar2, p_value in

varchar2 )

is

begin

if ( p_name is not null

)

then

dbms_sql.bind_variable( g_cursor, p_name,

p_value );

end if;

end;

function substr_of

( p_query in varchar2,

p_from in number,

p_for in number,

p_name1 in varchar2 default NULL,

p_bind1 in varchar2 default NULL,

p_name2 in varchar2 default NULL,

p_bind2 in varchar2 default NULL,

p_name3 in varchar2 default NULL,

p_bind3 in varchar2 default NULL,

p_name4 in varchar2 default NULL,

p_bind4 in varchar2 default NULL )

return varchar2

as

l_buffer varchar2(4000);

l_buffer_len number;

begin

if ( nvl(p_from,0) <=

0 )

then

raise_application_error

(-20002, 'From must be >= 1 (positive

numbers)' );

end if;

if ( nvl(p_for,0) not

between 1 and 4000 )

then

raise_application_error

(-20003, 'For must be between 1 and 4000'

);

end if;

if ( p_query <>

g_query or g_query is NULL )

then

if ( upper(trim(nvl(p_query,'x'))) not like

'SELECT%')

then

raise_application_error

(-20001,

'This must be a select only' );

end if;

dbms_sql.parse( g_cursor, p_query,

dbms_sql.native );

g_query := p_query;

end if;

bind_variable( p_name1,

p_bind1 );

bind_variable( p_name2,

p_bind2 );

bind_variable( p_name3,

p_bind3 );

bind_variable( p_name4,

p_bind4 );

dbms_sql.define_column_long(g_cursor, 1);

if

(dbms_sql.execute_and_fetch(g_cursor)>0)

then

dbms_sql.column_value_long

(g_cursor, 1, p_for, p_from-1,

l_buffer, l_buffer_len

);

end if;

return l_buffer;

end substr_of;

end;

/

expdp zy/xxx directory=bak tables=IPTV_HW_DATA

dumpfile=IPTV_HW_DATA_201803.dmp logfile=IPTV_HW_DATA_201803.log

parfile=IPTV_HW_DATA_201803.par

文件IPTV_HW_DATA_201803.par内容：

EXCLUDE=STATISTICS,INDEX,TABLE_DATA:"IN (select partition_name

from (select

partition_name,to_number(substr(partition_name,instr(partition_name,'_P',-1)+2))

n from user_tab_partitions a where a.table_name='IPTV_HW_DATA') b

where b.n>20759)"

expdp zy/xxx directory=bak tables=IPTV_ZTE_DATA

dumpfile=IPTV_ZTE_DATA_201803.dmp logfile=IPTV_ZTE_DATA_201803.log

parfile=IPTV_ZTE_DATA_201803.par

文件IPTV_ZTE_DATA_201803.par内容：

EXCLUDE=STATISTICS,INDEX,TABLE_DATA:"IN (select partition_name

from (select

partition_name,to_number(substr(partition_name,instr(partition_name,'_P',-1)+2))

n from user_tab_partitions a where a.table_name='IPTV_ZTE_DATA') b

where b.n>20760)"

nohup ./xxx.sh > ./xxx.out &

准备清理分区脚本：

select 'alter table AAA_DATA drop partition

'||partition_name|| ';' from (select

partition_name,to_number(substr(partition_name,instr(partition_name,'_P',-1)+2))

n from user_tab_partitions a where a.table_name='AAA_DATA') b where

b.n<=20624 order by b.n

查看索引状态：(本地索引不受drop分区影响)

select a.index_name,a.partition_name,a.status from

user_ind_partitions a,user_indexes b

where a.index_name=b.index_name and

b.table_name='KD_FH_DATA' and a.status<>'USABLE'