1 以 oracle 用户登录 XXXX 主机
2 创建目录用于存放临时导出文件（这里以导出目录为/expdp为例）
mkdir /expdp
3 查询信息
$ sqlplus / as sysdba
SQL>
3.1 查询有无会话连接数据库
sqlplus / as sysdba
set lines 10000
select username, machine, count(*) from v$session where type = ‘USER’ group by username, machine order by username, machine;
3.2 查询重建的表有无外键被引用
select c.owner, c.table_name, c.constraint_name, c.r_owner, l.table_name r_table_name, c.r_constraint_name, l.column_name r_column_name
from dba_constraints c, dba_cons_columns l
where c.r_owner = l.owner and c.r_constraint_name = l.constraint_name
and c.constraint_type = ‘R’
and c.owner = ‘POSP’ --用户名根据实际情况修改
and l.table_name in (‘BTH_CUP_TXN_BDT’, ‘TBL_T_TXN’) --表名根据实际情况修改
order by r_owner, r_table_name;
3.3 查询重建的表有无被其它对象引用
select owner, type, name, referenced_owner, referenced_name
from dba_dependencies d
where referenced_owner = ‘POSP’ --用户名根据实际情况修改
and referenced_name in (‘BTH_CUP_TXN_BDT’, ‘TBL_T_TXN’) --表名根据实际情况修改
order by referenced_owner, referenced_name;
3.4 查询表的大小
set lines 1000
select owner, segment_name, round(sum(bytes)/1024/1024/1024,2) size_gb
from dba_segments
where owner = ‘POSP’ --用户名根据实际情况修改
and segment_name in (‘BTH_CUP_TXN_BDT’, ‘TBL_T_TXN’) --表名根据实际情况修改
group by owner, segment_name
order by owner, segment_name;
4 待重建表数据导出
4.1 编辑数据导出脚本（以posp用户的表 BTH_CUP_TXN_BDT 为例，操作时根据实际情况修改）
vi exp_table.sh 添加如下内容：
#!/bin/sh
cd /expdp
rm -f *.dmp
rm -f *.log
exp_spdb.log
echo “Start export BTH_CUP_TXN_BDT:” date >> exp_spdb.log
exp userid="/ as sysdba" direct=y recordlength=65535 statistics=estimate compress=n file=exp_bth_cup_txn_bdt.dmp log=exp_bth_cup_txn_bdt.log tables=posp.bth_cup_txn_bdt
echo “End export BTH_CUP_TXN_BDT:” date >> exp_spdb.log
4.2 执行数据导出脚本
cd /expdp
nohup ./exp_table.sh &
tail -f nohup.out
查看导出操作有无错误，导出操作是否完成
mv nohup.out nohup.out_exp
5 重命名对象（导出成功完成后）
sqlplus / as sysdba
alter session set current_schema = POSP;
– BTH_CUP_TXN_BDT
alter index IND_BTH_CUP_TXN_BDT rename to BAK_IND_BTH_CUP_TXN_BDT;
alter index IND_BTH_CUP_TXN_BDT_1 rename to BAK_IND_BTH_CUP_TXN_BDT_1;
alter index I_BTH_CUP_TXN_BDT_1 rename to BAK_I_BTH_CUP_TXN_BDT_1;
alter index PK_BTH_CUP_TXN_BDT rename to BAK_PK_BTH_CUP_TXN_BDT;
alter table BTH_CUP_TXN_BDT rename constraint PK_BTH_CUP_TXN_BDT to BAK_PK_BTH_CUP_TXN_BDT;
alter table BTH_CUP_TXN_BDT rename to BAK_BTH_CUP_TXN_BDT;
6 重新导入表数据
cd /expdp
imp userid="/ as sysdba" full=y buffer=104857600 recordlength=65535 commit=y skip_unusable_indexes=y statistics=always grants=y file=exp_bth_cup_txn_bdt.dmp log=imp_bth_cup_txn_bdt.log
7 比较重建后的表和备份表的记录数和结构
7.1 比较重建后的表和备份表的记录数
$ sqlplus / as sysdba
set lines 100
alter session force parallel query parallel 8;
alter session set current_schema = POSP;
select count() from BTH_CUP_TXN_BDT;
select count() from BAK_BTH_CUP_TXN_BDT;
7.2 比较重建后的表和备份表的结构
开启 PL/SQL Developer 软件，打开 File -> New -> Command Window，
view <表名>
view BAK_<表名>
比对表结构、索引、约束
8 编译数据库对象
sqlplus / as sysdba
SQL>
set lines 10000
select compile_invalid_sql from (
select owner, object_name, 2 compile_type, ‘alter ’ || decode(object_type, ‘PACKAGE BODY’, ‘PACKAGE’, object_type) || ’ ’ || owner || ‘.’ || object_name || ’ compile;’ compile_invalid_sql from all_objects where object_type in (‘FUNCTION’, ‘PROCEDURE’, ‘PACKAGE BODY’, ‘PACKAGE’, ‘TRIGGER’, ‘VIEW’, ‘MATERIALIZED VIEW’, ‘JAVA SOURCE’) and status = ‘INVALID’
union all
– Unusable Normal Index
select owner, index_name, 8, ‘alter index ’ || owner || ‘.’ || index_name || ’ rebuild online;’ from all_indexes where index_type not in (‘LOB’, ‘IOT - TOP’, ‘DOMAIN’) and partitioned = ‘NO’ and temporary = ‘N’ and table_type <> ‘CLUSTER’ and status = ‘UNUSABLE’
)
where object_name not like ‘BIN$%’ and owner not in (‘SYS’, ‘SYSTEM’, ‘SYSMAN’, ‘MGMT_VIEW’, ‘DBSNMP’, ‘WMSYS’)
– and owner in upper(user)
order by owner, compile_type, object_name;
执行终端输出的 SQL 语句
9 收集数据库统计信息
9.1 创建统计信息收集脚本
vi gather_stats.sh 添加如下内容：
#!/bin/sh
cd /expdp
sqlplus /nolog <<EOF
connect / as sysdba
set time on
set timing on
spool gather_stats.log
exec dbms_stats.gather_table_stats( ownname => ‘POSP’, tabname => ‘BTH_CUP_TXN_BDT’, estimate_percent => 100, method_opt => ‘for all columns size auto’, degree => 16, granularity => ‘ALL’, cascade => true, no_invalidate => true );
spool off
exit
EOF
9.2 执行统计信息收集
nohup ./gather_stats.sh &
tail -f nohup.out
回退步骤：
以 oracle 用户登录 XXXX 主机
sqlplus “/as sysdba”
SQL> drop table BTH_CUP_TXN_BDT;
– BTH_CUP_TXN_BDT
alter index BAK_IND_BTH_CUP_TXN_BDT rename to IND_BTH_CUP_TXN_BDT;
alter index BAK_IND_BTH_CUP_TXN_BDT_1 rename to IND_BTH_CUP_TXN_BDT_1;
alter index BAK_I_BTH_CUP_TXN_BDT_1 rename to I_BTH_CUP_TXN_BDT_1;
alter index BAK_PK_BTH_CUP_TXN_BDT rename to PK_BTH_CUP_TXN_BDT;
alter table BAK_BTH_CUP_TXN_BDT rename constraint BAK_PK_BTH_CUP_TXN_BDT to PK_BTH_CUP_TXN_BDT;
alter table BAK_BTH_CUP_TXN_BDT rename to BTH_CUP_TXN_BDT;
