small cases

external table of an oracle_datapump type

 

create directory dir as '/u01/oracle/directory';

grant read,write on dir to public;

 

as sys :

create table ext_tab organization external (type oracle_datapump default directory dir location('ext1.dmp','ext2.dmp')) as  select /*+ parallel 2 */ object_name, owner from dba_objects;

 

as another user system:

create table ext_tab( object_name varchar2(130), owner varchar2(30))  organization external (type oracle_datapump default directory dir location ('ext1.dmp','ext2.dmp'));

 

SQL> select count(*) from ext_tab;

  COUNT(*)
----------
      9604

this method is useful for a very large table , with this method , no need to do the actual import work which may consume a lot of tablespace space.

 

flash database on  

 

 

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/oracle/flash/db
db_recovery_file_dest_size           big integer 3G
recovery_parallelism                 integer     0

 

startup mount;

alter database flashback on;

startup mount;

flashback database to time 'xxxx'

flash back database to scn 'xxxx';

alter database open resetlogs;