ITPub博客

首页 > 数据库 > Oracle > EXP的FLASHBACK_SCN,FLASHBACK_TIME

EXP的FLASHBACK_SCN,FLASHBACK_TIME

原创 Oracle 作者:banana_62 时间:2017-07-05 15:33:06 0 删除 编辑
FLASHBACK_SCN
默认值:none
指定exp使用闪回技术的SCN号。

FLASHBACK_TIME
默认值:none
指定导出对象的时间点。这跟scn原理是一值的,在不知道scn的情况下可用使用时间点导出。
exp FILE=exp.dmp FLASHBACK_TIME="TO_TIMESTAMP('12-02-2005 14:35:00', 'DD-MM-YYYY HH24:MI:SS')"

使用FLASHBACK_SCN示例
创建表分段插入数据并记录scn
SQL> create table scott.lzl_scn(scn number);
Table created.
SQL> insert into scott.lzl_scn select current_scn from v$database;
1 row created.
SQL> commit;
Commit complete.
SQL>  insert into scott.lzl_scn select current_scn from v$database;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from scott.lzl_scn;
       SCN
----------
   1848958
   1848980
使用scott去执行exp发现报错
exp scott/tiger tables=lzl_scn file=lzl_scn.dmp flashback_scn=1848979
Export: Release 11.2.0.3.0 - Production on Wed Jul 5 14:19:26 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
EXP-00103: The FLASHBACK_TIME parameter was invalid
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 14:
PLS-00201: identifier 'DBMS_FLASHBACK' must be declared
ORA-06550: line 1, column 14:
PL/SQL: Statement ignored
EXP-00000: Export terminated unsuccessfully

从报错结果来看,是scott缺少flashback相关包的权限。
SQL> grant dba to scott;
Grant succeeded.
再次导出
exp  scott/tiger tables=scott.lzl_scn file=lzl_scn.dmp flashback_scn=1848979
Export: Release 11.2.0.3.0 - Production on Wed Jul 5 14:34:35 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table                        LZL_SCN          1 rows exported
Export terminated successfully without warnings.
[oracle@lzl ~]$ exp  scott/tiger tables=scott.lzl_scn file=lzl_scn.dmp flashback_scn=1848999
Export: Release 11.2.0.3.0 - Production on Wed Jul 5 14:35:13 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table                        LZL_SCN          2 rows exported
Export terminated successfully without warnings.
导出成功

       SCN
----------
   1848958
   1848980
第一个导出的scn应该在两个scn之间,这样就导出了一个行记录,第二个导出scn在最后一个scn后面。



来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/31461640/viewspace-2141620/ ,如需转载,请注明出处,否则将追究法律责任。

下一篇: EXP的QUERY参数
请登录后发表评论 登录
全部评论

注册时间:2017-05-18

  • 博文量
    71
  • 访问量
    65037