We value your privacy

We use cookies to enhance your browsing experience, serve personalised content, and analyse our traffic. By clicking "Accept All", you consent to our use of cookies.

Customise Consent Preferences

We use cookies to help you navigate efficiently and perform certain functions. You will find detailed information about all cookies under each consent category below.

The cookies that are categorised as "Necessary" are stored on your browser as they are essential for enabling the basic functionalities of the site. ... 

Always Active

Necessary cookies are required to enable the basic features of this site, such as providing secure log-in or adjusting your consent preferences. These cookies do not store any personally identifiable data.

No cookies to display.

Functional cookies help perform certain functionalities like sharing the content of the website on social media platforms, collecting feedback, and other third-party features.

No cookies to display.

Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics such as the number of visitors, bounce rate, traffic source, etc.

No cookies to display.

Performance cookies are used to understand and analyse the key performance indexes of the website which helps in delivering a better user experience for the visitors.

No cookies to display.

Advertisement cookies are used to provide visitors with customised advertisements based on the pages you visited previously and to analyse the effectiveness of the ad campaigns.

No cookies to display.

  Master in PostgreSQL DBA |Join Free Demo Session on 28 April 2025 at 7.30 PM IST| Register for Free Demo

Know More

How To Recover A Dropped User Using Flashback in Oracle.

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarKiran Dalvi
  • 11 Oct, 2023
  • 0 Comments
  • 3 Mins Read

How To Recover A Dropped User Using Flashback in Oracle.

Using flashback method, we can restore an accidentally dropped users in oracle. 

Basically, we will flashback the database to past, when the user was available, Then take an export dump of the schema, and restore the database to same current state. 

Once database is up, we can import the dump. 

PREREQUISITE:

  1. Database Must be in Archive log mode
  2. Flashback must be enabled for the database.
  3.  all the flashback log and archives should be available, from the time, the user is dropped
  1. Make sure flashback and archive mode is enable.
1
2
3
4
5
SQL> select flashback_on,log_mode from v$database;
 
FLASHBACK_ON LOG_MODE
------------------ ------------
YES ARCHIVELOG
  1. Lets drop a user, to test the scenarios:
1
2
3
4
5
6
7
8
9
10
11
SQL> select table_name from dba_tables where owner='TESTUSER';
 
TABLE_NAME
----------------------------------------------------------------------------
TABLE1
TABLE2
 
 
SQL> drop user testuser cascade;
 
User dropped.
  1. flashback the database to past, when the user was available.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
 SQL>
 SQL>
 SQL> startup mount;
ORACLE instance started.
 
Total System Global Area 1.1107E+10 bytes
Fixed Size 7644464 bytes
Variable Size 9294584528 bytes
Database Buffers 1711276032 bytes
Redo Buffers 93011968 bytes
Database mounted.
 
 SQL> flashback database to timestamp to_date('20-JAN-2023 04:47:14','DD-MON-YYYY HH24:MI:SS');
 
Flashback complete.
4. Open the database in read only mode:
1
2
3
4
5
6
7
8
9
10
SQL> ALTER DATABASE OPEN READ ONLY;
 
Database altered.
 
SQL> select table_name from dba_tables where owner='TESTUSER';
 
TABLE_NAME
--------------------------------------------------------------------------------
TABLE1
TABLE2
  1. Take export backup of the schema TESTUSER:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
# exp owner=TESTUSER file=testuser.dmp
 
Export: Release 12.1.0.2.0 - Production on Tue Jan 20 05:17:45 2023
 
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
 
Username: / as sysdba
 
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
 
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TESTUSER
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TESTUSER
About to export TESTUSER's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TESTUSER's tables via Conventional Path ...
. . exporting table TEST1 86452 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table TEST2 35 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.
  1. Now restore the database to current stage:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
 
Total System Global Area 1.1107E+10 bytes
Fixed Size 7644464 bytes
Variable Size 9294584528 bytes
Database Buffers 1711276032 bytes
Redo Buffers 93011968 bytes
Database mounted.
 
SQL> recover database;
Media recovery complete.
 
SQL> alter database open;
 
Database altered.
  1. Now create an empty user and import the dump.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SQL> create user testuser identified by testuser;
 
User created.
 
SQL> grant connect,resource to testuser;
 
Grant succeeded.
 
# imp file=testuser.dmp fromuser=TESTUSER TOUSER=TESTUSER
 
Import: Release 12.1.0.2.0 - Production on Tue Jan 17 05:23:59 2017
 
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
 
Username: / as sysdba
 
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
 
Export file created by EXPORT:V12.01.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing TESTUSER's objects into TESTUSER
. . importing table "TEST1" 86452 rows imported
. . importing table "TEST2" 35 rows imported

Hope it Helps!

How To Move AUD$ Table To Another Tablespace Using DBMS_AUDIT_MGMT
How To Use DBMS_PRIVILEGE_CAPTURE To Capture Privs In Oracle 12c