I'm having trouble using the FLASHBACK TABLE functionality.
I'm working from SQL*Plus
=========================
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Nov 29 15:36:09 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
My sqlprompt is...
==================
[Email Address Removed] show sqlprompt
sqlprompt "[Email Address Removed] "
My test table was dropped and appears in the recyclebin as...
=============================================================
[Email Address Removed] select pageview('select * from recyclebin') from dual;
PAGEVIEW('SELECT*FROMRECYCLEBIN')
------------------------------------------------------------------------
--
--Row 1
OBJECT_NAME = BIN$J0m5n45URRCT5bhXcgSL1A==$0
ORIGINAL_NAME = WLSTEST1_FLASHRESTORE
OPERATION = DROP
TYPE = TABLE
TS_NAME = USERS
CREATETIME = 2006-11-29:11:15:21
DROPTIME = 2006-11-29:11:15:57
DROPSCN = 931731348268
PARTITION_NAME =
CAN_UNDROP = YES
CAN_PURGE = YES
RELATED = 100539
BASE_OBJECT = 100539
PURGE_OBJECT = 100539
SPACE = 8
My session privileges are...
============================
[Email Address Removed] select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE ANY TRIGGER
CREATE TYPE
SELECT ANY DICTIONARY
FLASHBACK ANY TABLE
13 rows selected.
My database version is...
=========================
[Email Address Removed] select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
When I attempt to do...
=======================
[Email Address Removed] flashback table wlstest1_flashrestore to before drop;
SP2-0734: unknown command beginning "flashback ..." - rest of line
ignored.
Question:
=========
Does anyone have a suggestion as to what additionally needs to be
addressed in order to successfully use the FLASHBACK TABLE
functionality? Thanks, in advance, for your help.
Bill Salisbury
-----Original Message-----
From: Samarasinghe Deepthi
[mailto:oracledba-ezmlmshield-x59751379.[Email address protected]
Sent: Wednesday, November 29, 2006 8:47 AM
To: LazyDBA Discussion
Subject: RE: getting back
This happens if the table is truncated. If it was deleted or dropped
the "FLASHBACK TABLE table TO TIMESTAMP" and "FLASHBACK TABLE table TO
BEFORE DROP" respectively works without the db being on archivelog and
flashback database enabled mode. It only needs UNDO setup. For the
truncated scenario it will need a restore or flashback database
(provided archive mode and flashback database have been enabled). I was
mistaken with the TRUNCATE scenario.
-----Original Message-----
From: Shaju J. Joseph
[mailto:oracledba-ezmlmshield-x47723606.[Email address protected]
Sent: Wednesday, November 29, 2006 12:05 AM
To: LazyDBA Discussion
Subject: FW: getting back
Hi
I think we can rollback to time which is specified with
UNDO_RETENTION. The value is in seconds.
When I tried FLASHBACK TABLE <table_name> AS OF TIMESTAMP , it
gives an error
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed.
--Joseph
-----Original Message-----
From: Samarasinghe Deepthi
[mailto:oracledba-ezmlmshield-x48477107.[Email address protected]
Sent: Tuesday, November 28, 2006 6:22 PM
To: LazyDBA Discussion
Subject: RE: getting back
If the table is truncated then you can use:
FLASHBACK TABLE <table_name> AS OF TIMESTAMP <
Deepthi Samarasinghe
Database Architect
-----Original Message-----
From: Samarasinghe Deepthi
[mailto:oracledba-ezmlmshield-x85795877.[Email address protected]
Sent: Tuesday, November 28, 2006 9:48 AM
To: LazyDBA Discussion
Subject: RE: getting back
FLASHBACK TABLE <table_name> to BEFORE DROP;
Deepthi Samarasinghe
Database Architect
-----Original Message-----
From: Shaju J. Joseph
[mailto:oracledba-ezmlmshield-x52476026.[Email address protected]
Sent: Tuesday, November 28, 2006 4:30 AM
To: LazyDBA Discussion
Subject: getting back
Hi Gurus
One of our developers has truncated a table in a 10g Database. Which
is in no archive log mode. How can I retrieve it through FLASHBACK QUERY
. Is there any way to get it back??
Thanking you
--Joseph
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Oracle LazyDBA home page