doubt
A reader, May 23, 2005 - 9:48 pm UTC
Hi Tom,
Sorry , still i am not clear.
I opened two sessions and did the following activities.How oracle handles the commited information on the modified blocks even the datafiles are in offline.
SESSION - I
SQL> conn test/test
Connected.
SQL> select * from t1;
NAME
------------------------------------------------------------
jeya
SQL> select tablespace_name,table_name from user_tables where table_name='T1';
TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------
DATA_DBDOC T1
SQL> insert into t1 values('b4off');
1 row created.
SQL> select * from t1;
NAME
------------------------------------------------------------
jeya
b4off
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='DATA_DBDOC';
TABLESPACE_NAME STATUS
------------------------------ ---------
DATA_DBDOC ONLINE
SQL> -- I made the tablespace offline thru another session
SQL> /
TABLESPACE_NAME STATUS
------------------------------ ---------
DATA_DBDOC OFFLINE
SQL> seelct * from t1;
SP2-0734: unknown command beginning "seelct * f..." - rest of line ignored.
SQL> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/applications/DBDOC/dbs/data/DBDOC_data01.dbf'
SQL> commit;
Commit complete.
SQL> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/applications/DBDOC/dbs/data/DBDOC_data01.dbf'
SQL> -- I made the tablespace online thru another session;
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='DATA_DBDOC';
TABLESPACE_NAME STATUS
------------------------------ ---------
DATA_DBDOC ONLINE
SQL> select * from t1;
NAME
------------------------------------------------------------
jeya
b4off
SESSION -II
SQL> conn / as sysdba
Connected.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
TEMP_DBDOC ONLINE
DATA_DBDOC ONLINE
INDEX_DBDOC ONLINE
UNDOTBS_01 ONLINE
SPOTLIGHT ONLINE
AUDITS ONLINE
7 rows selected.
SQL> alter tablespace data_dbdoc offline;
Tablespace altered.
SQL> alter tablespace data_dbdoc online;
Tablespace altered.
SQL> conn test/test
Connected.
SQL> select * from t1;
NAME
----------
jeya
b4off
May 24, 2005 - 7:29 am UTC
but the first offline checkpointed the information, your commit just made it so we could release the ROLLBACK DATA, that is all. The commit doesn't need to touch the blocks on disk, just marks the transaction "finished" and allows us to reuse the rollback data.
Hence offlining the datafile is a very "safe" operation there -- it checkpointed the data, you committed it (which doesn't need to touch it). Later when we onlined it we noted there were no transactions to be rolled back and that was that.
What if rollback?
Christo Kutrovsky, May 24, 2005 - 1:08 pm UTC
Tom,
What will happen when we rollback our transaction with the tablespace still offline ?
Are we still holding some data in rollback segments?
If yes, can we get a "rollback full" by such holded data?
May 24, 2005 - 1:45 pm UTC
yup, the RBS information would be copied into the system rollback segment at that point for a deferred rollback to be performed if and when the tablespace comes online assuming the rbs fits, else it'll stay there (where it is) and jam the rbs eventually as we cannot wrap around
Christo Kutrovsky, May 24, 2005 - 2:43 pm UTC
It's very hypotethical, but it's always good to clear the details out.
So you are saying if there's enought space in the system RBS to hold the undo data (i assumed not the entire rbs segment involved) it will hold it for a deffered operation.
Is there a limit on how much data the system RBS will hold or is it only limited by the autoextend size of the system datafiles?
If no, Can we adjust the size of the system RBS ?
May 24, 2005 - 3:57 pm UTC
right, only the rbs data needed is moved.
you size the system rbs like any other rbs.
Rollback with a small rbs
A reader, May 25, 2005 - 11:38 am UTC
Tom,
In the rollback situation, what will happen if my undo is not sized correctly, and the information required to rollback is overwritten?
Thanks.
Rollback with a small rbs
A reader, May 25, 2005 - 11:43 am UTC
Please ignore my question. You already answered it.
but where commit goes
raman, October 22, 2009 - 5:54 am UTC
ok i got it about defrred rolback segment for uncommitted data, what about commit
-update data (session 1)
-from another session offline that respective tablespace (session 2)
-commit those updated record (session 1)
now where this committed data goes?
when i turn back online the tablespace ,i see committed data
October 23, 2009 - 1:26 pm UTC
... when you offline the file, all dirty buffers are written to disk and no more
changes are permitted to that file.
...
the committed data was already on disk
respective tablespace offline where commit goes
raman, October 24, 2009 - 2:56 am UTC
but respective tabelspace turned offline before commit, where does this commit will go on disk on which tablespace?
session1
---------
update emp sal from 100 to 200
session2
--------
respective tablepsace turn offline
session1
--------
commit updated data <-------well goes on which tabelsapce its tablespace is offline
October 26, 2009 - 1:50 pm UTC
when session 2 took the tablespace offline, the modifications made by session 1 were written to the datafiles first.
(that is a repeat, said that a couple of times)
Now, tell me, what needs to be saved? We've *already checkpointed the uncommitted data to the datafiles*.
We have the redo to redo that in the event of media failure, we'll get that from the archived redo logs if need.
But we don't need it when we online the datafile 50 days from now - the redo to redo that change just isn't necessary anymore - we already wrote the data to disk.
I'm not really sure what you are asking - what do you think we need to "save" in step three there?