Home » SQL & PL/SQL » SQL & PL/SQL » FlashBack query (merged threads)
FlashBack query (merged threads) [message #169388] Wed, 26 April 2006 10:36 Go to next message
sauami
Messages: 54
Registered: October 2005
Location: india
Member
Hi,

I have a problem with FlashBack query. when i try to execute my query i got the given below message.
---------------------------------------
execute dbms_flashback.enable_at_time(sysdate, 5/1440);
BEGIN dbms_flashback.enable_at_time(sysdate, 5/1440); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_FLASHBACK' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
-----------------------------
pls. let me know how to fix this problem.
Thanks for your valuable time,
Saurabh

Report message to a moderator

Re: FlashBack query [message #169408 is a reply to message #169388] Wed, 26 April 2006 11:57 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
what is your oracle version?
can you see sys.dbms_flashback?

Report message to a moderator

Re: FlashBack query [message #169537 is a reply to message #169408] Thu, 27 April 2006 09:03 Go to previous messageGo to next message
kmsalih
Messages: 197
Registered: March 2006
Senior Member
hi,

type desc dbms_flashback.
if not working login as sys and
grant execute on dbms_flashback to user;

Salih Km

Report message to a moderator

Re: FlashBack query [message #169898 is a reply to message #169537] Sun, 30 April 2006 04:30 Go to previous messageGo to next message
sauami
Messages: 54
Registered: October 2005
Location: india
Member
Dear Sir,
pls. see blow sys schema desc and when i execute query it show below given error. pls. help more.

in sys schema
----------------------
SQL> desc DBMS_FLASHBACK;
PROCEDURE DISABLE
PROCEDURE ENABLE_AT_SYSTEM_CHANGE_NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUERY_SCN NUMBER IN
PROCEDURE ENABLE_AT_TIME
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUERY_TIME TIMESTAMP IN
FUNCTION GET_SYSTEM_CHANGE_NUMBER RETURNS NUMBER
--------------------
* query and error
---------------------------------
SQL> execute dbms_flashback.enable_at_time(sysdate,5/1440);
BEGIN dbms_flashback.enable_at_time(sysdate,5/1440); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'ENABLE_AT_TIME'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
----------------

Thanks,
Saurabh

Report message to a moderator

Re: FlashBack query [message #169899 is a reply to message #169408] Sun, 30 April 2006 04:31 Go to previous messageGo to next message
sauami
Messages: 54
Registered: October 2005
Location: india
Member
yes sir, even i grant execute on dbms_flashback to hr

Thanks,
Saurabh

Report message to a moderator

Re: FlashBack query [message #169923 is a reply to message #169899] Sun, 30 April 2006 14:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
The procedure only accepts one parameter, but you are tryiing to pass two parameters to it. I am guessing that you want to do something like this:

DBMS_FLASHBACK.ENABLE_AT_TIME (TRUNC (SYSDATE) + (5/1440))

The procedure actually calls for a timestamp, not a date, but seems to handle the conversion implicitly.

Report message to a moderator

FlashBack query not execute [message #170637 is a reply to message #169388] Thu, 04 May 2006 10:11 Go to previous messageGo to next message
sauami
Messages: 54
Registered: October 2005
Location: india
Member
Dear Sir,

According to Oracle Book given query is ok but when i run, it give some error.

execute dbms_flashback.enable_at_time(sysdate, 5/1440);Error is
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'ENABLE_AT_TIME'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

pls. tell me how to fix the problem.
i am using oracle 10g

Thanks,
Saurabh

Report message to a moderator

Form TNS problem [message #170638 is a reply to message #169388] Thu, 04 May 2006 10:13 Go to previous messageGo to next message
sauami
Messages: 54
Registered: October 2005
Location: india
Member
Dear Sir,

According to Oracle Book given query is ok but when i run, it give some error. even i give the grant to execute dbms_flashback to my user.

execute dbms_flashback.enable_at_time(sysdate, 5/1440);Error is
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'ENABLE_AT_TIME'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

pls. tell me how to fix the problem.
i am using oracle 10g

Thanks,
Saurabh

Report message to a moderator

Re: Form TNS problem [message #170640 is a reply to message #170638] Thu, 04 May 2006 10:17 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
The enable_at_time procedure accepts only 1 parameter. You mention 'Oracle book' which says that your query is correct. Which book, please post where it says that the procedure will accept 2 arguments.

Cheers
Jim

PS what ha this got to do with the title of the thread?

[Updated on: Thu, 04 May 2006 10:18]

Report message to a moderator

Re: FlashBack query not execute [message #170733 is a reply to message #170637] Fri, 05 May 2006 00:32 Go to previous messageGo to next message
kmsalih
Messages: 197
Registered: March 2006
Senior Member
hi,

there is only one argument for dbms_flashback.enable_at_time

Example 

SQL>create table e as select * from scott.emp;
SQL>delete from e; /* at 10:50:59 */
SQL>commit;
SQL>exec dbms_flashback.enable_at_time(to_timestamp('05-MAY-2006 10:50:58','DD-MON-YYYY HH24:MI:SS'));
SQL>select count(*) from e;
SQL>exec dbms_flashback.disable;



Rgds
SALIH KM Cool

Report message to a moderator

Re: Form TNS problem [message #170829 is a reply to message #170640] Fri, 05 May 2006 06:52 Go to previous messageGo to next message
sauami
Messages: 54
Registered: October 2005
Location: india
Member
UR right sir,
now the problem comes when i use timestamp its execute and undo the deleted rows but when i disable the dbms_flashback.disable after then undo data not show in what does it means should i make a copy of the data and disable the query or have some other technique.

Thanks,
Saurabh

Report message to a moderator

Re: Form TNS problem [message #171030 is a reply to message #170829] Mon, 08 May 2006 00:47 Go to previous messageGo to next message
kmsalih
Messages: 197
Registered: March 2006
Senior Member
which is your oracle version?

Report message to a moderator

Re: FlashBack query not execute [message #171139 is a reply to message #170733] Mon, 08 May 2006 11:46 Go to previous messageGo to next message
sauami
Messages: 54
Registered: October 2005
Location: india
Member
thanx its work, but the problem when i disable my flashback mode my all recovered data also remove even i commit it.
should i make a copy of my recovered data or their is any other option to permanent save my retrieve data.

Thanks for your time ans support,
Saurabh

Report message to a moderator

Re: FlashBack query not execute [message #171311 is a reply to message #171139] Tue, 09 May 2006 06:51 Go to previous message
kmsalih
Messages: 197
Registered: March 2006
Senior Member
if you are using 10g there is one method.

Example.

SQL>set time on
17:06:30 TESTING@ORCL>create table test
17:14:41   2  (
17:14:42   3  t number
17:14:46   4  );

Table created.

17:14:48 TESTING@ORCL>insert into test values(1);

1 row created.

17:15:24 TESTING@ORCL>commit;

Commit complete.

17:15:28 TESTING@ORCL>delete from test;

1 row deleted.

17:15:40 TESTING@ORCL>commit;

Commit complete.
17:17:28 TESTING@ORCL>select * from test 
17:17:41   2  as of timestamp
17:17:49   3  to_timestamp('09-MAY-2006 17:15:39','DD-MON-YYYY HH24:MI:SS');

         T
----------
         1

17:22:03 TESTING@ORCL>create table test_temp as 
17:23:01   2  select * from test as of timestamp
17:23:06   3  to_timestamp('09-MAY-2006 17:15:39','DD-MON-YYYY HH24:MI:SS');

Table created.


In the to_timestamp give a second before the delete command.
Delete command is execute at 17:15:40.
Give 17:15:39

Rgds
Salih Km Cool

[Updated on: Tue, 09 May 2006 06:55]

Report message to a moderator

Previous Topic: data integrity
Next Topic: ftp file from database server(unix) to local directory thru pl/sql
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Dec 13 17:41:05 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.