导出数据库备份文件
1. 备份服务器数据，采用并行方式，加快备份速度(文件日期根据具体操作日期修改)
expdp jhpt/XXXX directory=databackup dumpfile=dpfile_201511271500_%U.dmp filesize=5G parallel=8 compression=all
导出为多个文件，最大一个文件5g.
4. 执行导入脚本，进行数据库导入，排除索引和主键（文件日期根据导出文件修改）
impdp jhpt/XXX directory=datapump dumpfile=dpfile_201511271500_%U.dmp exclude=index,constraint parallel=8 cluster=no
可以新建一个存放索引的表
-- Create table
create table TEMP1210
(
content1 CLOB,
content2 NVARCHAR2(100)
)
tablespace TS_DATAANALYSE
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
因为分区的索引比较多，所以字段类型为CLOB 读的时候可以用to_char 吧字段转换为string
select to_char(a.content1) from TEMP1210 a ;
plsql 中执行
begin
-- 重建普通索引
for cur in (select index_name
from all_indexes
where owner = 'JHPT'
and tablespace_name is not null) loop
insert into TEMP1210
select dbms_metadata.get_ddl('INDEX', cur.index_name), ' ' from dual;
end loop;
commit;
-- 重建分区索引
for cur in (select * from all_ind_partitions where index_owner = 'JHPT') loop
insert into TEMP1210
select dbms_metadata.get_ddl('INDEX', cur.index_name), ' ' from dual;
end loop;
commit;
-- 重建复合分区索引
for cur in (select * from all_ind_subpartitions where index_owner = 'JHPT') loop
insert into TEMP1210
select dbms_metadata.get_ddl('INDEX', cur.index_name), ' ' from dual;
end loop;
commit;
end;
下面的是可以重建索引，因为索引时间长了可能对查询变慢，所以可以把这些索引重建一次；
begin
execute immediate 'alter session enable parallel dml';
-- 重建普通索引
for cur in (select index_name from all_indexes where owner = 'JHPT' and tablespace_name is not null) loop
execute immediate 'alter index ' || cur.index_name || ' rebuild online parallel 24';
end loop;
-- 重建分区索引
for cur in (select * from all_ind_partitions where index_owner = 'JHPT') loop
execute immediate 'alter index ' || cur.index_name || ' rebuild partition '|| cur.partition_name ||' online parallel 24';
end loop;
-- 重建复合分区索引
for cur in (select * from all_ind_subpartitions where index_owner = 'JHPT') loop
execute immediate 'alter index ' || cur.index_name || ' rebuild subpartition '|| cur.subpartition_name ||' online parallel 24';
end loop;
end;