Oracle - Recover a Dropped Table in Oracle

Recover a Dropped Table in Oracle

Q

How To Recover a Dropped Table in Oracle?

✍: FYIcenter.com

A

If you accidentally dropped a table, can you recover it back? The answer is yes, if you have the recycle bin feature turned on. You can use the FLASHBACK TABLE ... TO BEFORE DROP statement to recover a dropped table from the recycle bin as shown in the following SQL script:

SQL> CREATE TABLE emp_dept_90 
  2  AS SELECT * FROM employees WHERE department_id=90;
Table created.

SQL> SELECT COUNT(*) FROM emp_dept_90;
  COUNT(*)
----------
         3

SQL> DROP TABLE emp_dept_90;
Table dropped.

SQL> FLASHBACK TABLE emp_dept_90 TO BEFORE DROP
  2  RENAME TO emp_dept_bck;
Flashback complete.

SQL> SELECT COUNT(*) FROM emp_dept_bck;
  COUNT(*)
----------
         3

The FLASHBASK statement in this script recovered the dropped table "emp_dept_90" to new name "emp_dept_bck". All the data rows are recovered nicely.

⇒Managing Oracle Database Tables

⇒⇒Oracle Database Tutorials

2019-05-14, 645👍, 0💬

Show All Columns in an Existing Table in Oracle
How To View All Columns in an Existing Table in Oracle? If you have an existing table and want to know how many columns are in the table and how they are defined, you can use the system view USER_TAB_COLUMNS as shown in the following tutorial exercise: SQL> COL data_type FORMAT A12; SQL&g... 2019-05-14, 674👍, 0💬

Delete a Column in an Existing Table in Oracle
How To Delete a Column in an Existing Table in Oracle? If you have an existing column in a table and you need that column any more, you can delete it with ALTER TABLE ... DROP COLUMN statement. Here is an example SQL script: SQL> CREATE TABLE emp_dept_90 2 AS SELECT * FROM employees WHERE dep... 2019-05-25, 672👍, 0💬

Recycle Bin - Storage to Hold Dropped Tables in Oracle
What Is Recycle Bin in Oracle? Recycle bin is a logical storage to hold the tables that have been dropped from the database, in case it was dropped in error. Tables in recycle bin can be recovered back into database by the Flashback Drop action. Oracle database recycle save the same purpose as the r... 2019-05-14, 650👍, 0💬

Recover a Dropped Table in Oracle
How To Recover a Dropped Table in Oracle? If you accidentally dropped a table, can you recover it back? The answer is yes, if you have the recycle bin feature turned on. You can use the FLASHBACK TABLE ... TO BEFORE DROP statement to recover a dropped table from the recycle bin as shown in the follo... 2019-05-14, 646👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.