Skip to main content

FLASHBACK TABLE TO BEFORE DROP ORACLE 11G





C:\Users\computer>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jan 10 22:13:29 2013

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant dba to scott;

Grant succeeded.

SQL> conn scott
Enter password:
Connected.
SQL>
SQL> show user;
USER is "SCOTT"
SQL>
SQL> create table bemp as select * from emp;
create table bemp as select * from emp
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> drop table bemp;

Table dropped.

SQL> flashback table bemp to before drop;

Flashback complete.

SQL> desc bemp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> drop table bemp;

Table dropped.

SQL> conn sys as sysdba
Enter password:
Connected.

SQL> show user;
USER is "SYS"

SQL> flashback table scott.bemp to before drop;

Flashback complete.


SQL> DESC SCOTT.BEMP;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> DROP TABLE SCOTT.BEMP;

Table dropped.

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

SQL> FLASHBACK TABLE SCOTT.BEMP TO BEFORE DROP;
FLASHBACK TABLE SCOTT.BEMP TO BEFORE DROP
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN


Comments

Post a Comment

Popular posts from this blog

ORA-28007: THE PASSWORD CANNOT NE REUSED

             - Here will see how to deal with  => ORA-28007: the password cannot be reused -  I got a request from client user, stating his account has been locked. When I check the status of the account I found the below. connect to SYS user & execute the below query of that database. SQL> select password,username,account_status,profile from dba_users where username='TEST'; PASSWORD               USERNAME   ACCOUNT_STATUS                 PROFILE ------------------             ----------------  -------------------------------           -------------------- AB2Aa8AC9971521e3     TEST       EXPIRED(GRACE)&LOCKED    NONAPP_USERS So, then I have unlocked the account and checked the same again & I found the below results. ...
Post a Comment
Read more

ORA-39014: One or more workers have prematurely exited.ORA-00018: maximum number of sessions exceeded

ERROR: I was Performing a full database import and during the import I faced the below error. ORA-39014: One or more workers have prematurely exited. ORA-39029: worker 6 with process name "DW07" prematurely terminated ORA-31672: Worker process DW07 died unexpectedly. Job "SYSTEM"."SYS_IMPORT_FULL_04" stopped due to fatal error at 00:59:40 ORA-39014: One or more workers have prematurely exited. SOLUTION:  Run the import with fewer parallel processes, like PARALLEL=2 instead of 8. I was able to run the import successfully. NOTE 1: This errors occurs when there are less session allocation in the database. check the session,process parameters and increase them accordingly. To avoid such errors again. NOTE 2 : Note: Increasing processes parameter increases the amount of shared memory that needs to be reserved & the OS must be configured to support the larger amount of shared memory. So here we first need to increase the Memory & SG...
Post a Comment
Read more