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_NAMESUM(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_DATA38
6 HJJL_LIULIANG_DATA21
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_VALUEFROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER=:TABLE_OWNER AND TABLE_NAME=:TABLE_NAMEAND 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_helpauthid current_userasfunction substr_of( p_query in varchar2,p_fromin 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_helpasg_cursor number := dbms_sql.open_cursor;g_queryvarchar2(32765);procedure bind_variable( p_name in varchar2, p_value in varchar2 )isbeginif ( p_name is not null )thendbms_sql.bind_variable( g_cursor, p_name, p_value );end if;end;function substr_of( p_query in varchar2,p_fromin 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 varchar2asl_buffer varchar2(4000);l_buffer_len number;beginif ( nvl(p_from,0) = 1 (positive numbers)' );end if;if ( nvl(p_for,0) not between 1 and 4000 )thenraise_application_error(-20003, 'For must be between 1 and 4000' );end if;if ( p_query g_query or g_query is NULL )thenif ( upper(trim(nvl(p_query,'x'))) not like 'SELECT%')thenraise_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)thendbms_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’