为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:
【操作系统】:
【CPU】:
【问题描述】*:
一.实验步骤
#创建表
create table fuwa.deptno(id int,name varchar(100),addr varchar(200));
#创建索引
create index fuwa.idx_deptno on fuwa.deptno (id);
#批量插入数据
declare
sqlstr varchar;
begin
for i in 1..10000 loop
sqlstr:='insert into fuwa.deptno values ';
for j in 1..100 loop
sqlstr=sqlstr || '(' ||j|| ',''test'',''testestewt''),';
end loop;
sqlstr=rtrim(sqlstr,',') ;
execute immediate sqlstr;
end loop;
commit;
end;
select count(*) from fuwa.deptno;
#备份表
backup table fuwa.deptno backupset '/dmdb8/dmdata/fuwa/bak/tab_fuwa_deptno_bak';
#模拟删除表fuwa.deptno
drop table fuwa.deptno ;
#执行表结构还原。表备份和表中都包含索引,如果直接执行表数据还原会报错:还原表中存在二级索引或冗余约束。
restore table fuwa.deptno struct from backupset '/dmdb8/dmdata/fuwa/bak/tab_fuwa_deptno_bak';
#执行表数据还原
restore table fuwa.deptno from backupset '/dmdb8/dmdata/fuwa/bak/tab_fuwa_deptno_bak';
二.报错
SQL> backup table fuwa.deptno backupset '/dmdb8/dmdata/fuwa/bak/tab_fuwa_deptno_bak';
executed successfully
used time: 00:00:03.271. Execute id is 1158.
SQL> SQL> drop table fuwa.deptno ;
executed successfully
used time: 15.366(ms). Execute id is 1159.
SQL> select count(*) from fuwa.deptno;
select count(*) from fuwa.deptno;
[-2106]:Error in line: 1
Invalid table or view name [deptno].
used time: 1.103(ms). Execute id is 0.
SQL> restore table fuwa.deptno from backupset '/dmdb8/dmdata/fuwa/bak/tab_fuwa_deptno_bak';
restore table fuwa.deptno from backupset '/dmdb8/dmdata/fuwa/bak/tab_fuwa_deptno_bak';
[-2106]:Error in line: 1
Invalid table or view name [deptno].
used time: 0.324(ms). Execute id is 0.
SQL> restore table fuwa.deptno struct from backupset '/dmdb8/dmdata/fuwa/bak/tab_fuwa_deptno_bak';
restore table fuwa.deptno struct from backupset '/dmdb8/dmdata/fuwa/bak/tab_fuwa_deptno_bak';
[-2106]:Error in line: 1
Invalid table or view name [deptno].
used time: 0.384(ms). Execute id is 0.
SQL>
restore table 后面不加表名。
指定表名还原时数据库中必须存在该表,否则报错,不会从备份集判断是否存在目标表。
试下:
restore table DEPTNO from backupset '/dmdb8/dmdata/fuwa/bak/tab_fuwa_deptno_bak';