Home » RDBMS Server » Backup & Recovery » relation b/w redo log,undo tablespace,rollback segment
relation b/w redo log,undo tablespace,rollback segment [message #222530] Mon, 05 March 2007 05:28 Go to next message
p.bhaskar
Messages: 33
Registered: February 2007
Location: kolkata
Member
if i use as of timestamp to_timestamp in a sql query we get committed data.here undo tablespace gives data.
i cannot clearly understand which tablespace gives uncommitted data?

[Updated on: Mon, 05 March 2007 05:39]

Report message to a moderator

Re: working connection b/w redo log,undo tablespace,rollback segment [message #222531 is a reply to message #222530] Mon, 05 March 2007 05:31 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Not sure i understand your question.
Please re-phrase
Re: relation b/w redo log,undo tablespace,rollback segment [message #222680 is a reply to message #222530] Mon, 05 March 2007 22:35 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
if i use as of timestamp to_timestamp in a sql query we get committed data.here undo tablespace gives data.


Yes, use flashback option.
Flashback Query 

you must choose an undo retention interval that is long enough to enable users to construct a snapshot of the database for the oldest version of the database that they are interested in.


SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter system set undo_retention = 3600;
System altered.
SQL> conn hr/hr
Connected.
SQL> alter session set nls_date_format = 'hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
--------
19:04:49
SQL> create table hrtest ( no number, name varchar2(20));
Table created.
SQL> begin
2 insert into hrtest values ( 1, 'aaa');
3 insert into hrtest values ( 2, 'bbb');
4 insert into hrtest values ( 3, 'ccc');
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select sysdate from dual;
SYSDATE
--------
19:06:23
SQL> delete from hrtest where name = 'ccc';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from hrtest;
NO NAME
---------- --------------------
1 aaa
2 bbb

SQL> edWrote file afiedt.buf
1 select * from hrtest AS OF TIMESTAMP 2 to_timestamp ('2006-11-16 19:06:23','YYYY-MM-DD HH24:MI:SS')
3* where name = 'ccc'
4 /
NO NAME
---------- --------------------
3 ccc
SQL> insert into hrtest 2 ( select * from hrtest AS OF TIMESTAMP 3 to_timestamp ('2006-11-16 19:06:23','YYYY-MM-DD HH24:MI:SS')
4 where name = 'ccc');
1 row created.
SQL> select * from hrtest;
NO NAME
---------- --------------------
1 aaa
2 bbb
3 ccc


http://dbataj.blogspot.com/search/label/Flashback%20Feature
regards
Taj
Previous Topic: Can not allocate log error (?)
Next Topic: Online backup on standby database
Goto Forum:
  


Current Time: Mon Dec 05 08:54:54 CST 2016

Total time taken to generate the page: 0.12394 seconds