Hi all, I need some help, what commands exactly can I use to accomplish the foll

Question

Hi all, I need some help, what commands exactly can I use to accomplish the following below? Thanks in advance Get connected as user SYSTEM or some other user to whom you have granted the DBA role.

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

2 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?

3 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.

4 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?

Explanation / Answer

PSB the answers below:
1. Let the Undo Tablespace name be : undotspnm

CREATE UNDO TABLESPACE undotspnm
DATAFILE '<datafile_pathname>' SIZE 256K REUSE AUTOEXTEND OFF;

2.

CREATE DATABASE <dbname>
UNDO TABLESPACE undotspnm DATAFILE '<datafile_pathname>';

3.
alter undotspnm
undotbs1
add datafile
'<DATAFILEPATHNAME>'
size 256K
autoextend on;

4. The answer of query after deletion is related to tablespace space grow area.

Well this is becaus ethe REUSE and autoextend option we used while creating.

Thanks.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.