Question

Simulate a problem with undo. You will need to be connected as user

SYSTEM or some other user to whom you have granted the DBA role.

.

Create an undo tablespace with a single datafile of only 200 KB. Do not set the file to autoextend.

.

Set the database to use this small undo tablespace. Confirm that an undo

segment has been created in this tablespace and that it is online by querying

the views DBA_ROLLBACK_SEGS and V$ROLLSTAT.

Why do you think that only one undo segment was created?

.

Create an empty table based on the ALL OBJECTS view with this statement:

create table undotest as select * from all_objects where 1=2;

.

Populate this table with the contents of the ALL OBJECTS view:

insert into undotest select * from all_objects ;

.

The statement will fail with an error "ORA-30036: unable to extend segment by 8 in undo tablespace."

Query the new table, and you will find that it is still empty. Why is this?

.

Adjust the undo tablespace's datafile to autoextend, and attempt to insert the contents of the ALL_OBJECTS view into the new table. This time, the insertion will succeed. Find out how many blocks of undo are needed to support the transaction so far by querying V$TRANSACTION as well as how big the new datafile is by querying V$DATAFILE.

.

Delete every row in the new table and then repeat the queries against V$TRANSACTION and V$DATAFILE.

You will see that the deletion generated many more blocks of undo and required a much larger extension of the undo

datafile than the insertion. Why is this?

.

Tidy up by switching back to the original undo tablespace and then dropping the test table and tablespace

.

(3) Include in the assignments all queries and their results that you execute in this part. Also include your answers to the questions written in italic thought the exercise.

Answer 1

1- creating undo table space- (I am taking random name- UNDO_TEST)-

create undo tablespace UNDO_TEST datafile '/path/to/your/dbf/file/xyz.dbf' size 200K;

Now, you can be use autoextend off manually to be reassured about it-

ALTER DATABASE DATAFILE /path/to/your/dbf/file/xyz.dbf' AUTOEXTEND OFF;

2- Now, let us set our new undo tablespace to use by database, we can switch between undo tablesspaces because this is a dynamic variable. Use below query to do this-

ALTER SYSTEM SET UNDO_TABLESPACE = UNDO_TEST;

Now, you can query DBA_ROLLBACK_SEGS to know about your rollback segments-

select segment_name, segment_id,status from DBA_ROLLBACK_SEGS;

3- Query-

create table undotest as select * from all_objects where 1=2;

4- insert into undotest select * from all_objects ;

5- This query will fail because we have set autoextend to off. If we set it on or we increase size then our query will run fine as it will get proper space to insert logs.

6- write a simple select query against both view to see the desired output-

select file,blocks,checkpoint_time,status from V$DATAFILE;

7- deletion will take more blocks than insertion for sure. Because if you want to insert something then undo action would be deletion, for that you only need to store rowid of record. But if you want to perform deletion then undo action will be insertion again, for that we need whole record. Hence deletion generates more number of blocks.

8- Use below command to switch back to original table space. After firing below command, wait until everything rollbacks properly. (note originalTS is just a reference, you can give from your database.)

Alter System SET Undo_Tablespace=originalTS;

drop table undotest; //to drop table

drop tablespace UNDO_TEST; //to delete table space