8/6/2019 Oracle Certified Associates Exam

1/2

1) There is a table having 25 million records. Session 'A' has issued a query against the tableand it would take approximately 8 minutes. Mean time, session 'B' has dropped the table.

Q1) Is it possible to drop table as such?Q2) If not possible, why?Q3) If possible, what will happen to the executing query?Q4) If possible, how does the query fetches the data, if there is not sufficient buffer cache toaccommodate entire table data in the SGA?

2) Undo management

Q1) What is ORA-01555 error?Q2) Why does the DB require UNDO retention period?Q3) During what situation you would need to increase the UNDO retention time?Q4) What will happen if there is insufficient space in UNDO tablespace?Q5) During DML operation, without issuing commit, if another session is trying to manipulatesame data what will happen?

If it has to wait till the transaction A completes, how does the session B recognizes as it needsto wait?Q6) What will happen to a transaction X which is not committed for more than the time mentionedin UNDO retention policy, and same session tries to rollback the transaction?

3) Dead Locks

Q1) Explain Dead lockQ2) Dead locks managed automatically by Oracle, or DBA does need to do any thing with it?Q3) What is your recommendation, if the dead lock occurs frequently?Q4) How do you identify the dead lock is related to which queries and by what users?Q6) How do you investigate and resolve latch / Enqueue contention related issues?

4) Locks

Q1) If there is a table, and after you issue an update statement against it for all it's million rows,what are the locks get established with all respective rows?Q2) Will there be locks for each and every row?Q3) If your update is about specific rows, and the user session-1 did not issue commit, doesanother session able to update remaining rows without completion of session-1 transaction?Q4) What is lock, latch and Enqueue, and what are the differences among?Q5) How do you investigate and resolve latch / Enqueue contention related issues?Q6) Can you explain, how a transaction initiates and completes for a DML statement? Explainhow the BUFFER CACHE, UNDO and REDO managed for huge DML transaction.

5) Basics

Q1) If session-A has issued select statement, and mean while another session-B dropped the

table what will happen?Q2) Is it possible to drop a table from Session-B, while the table was being queried by session-A?Q3) If session-A is being accessed table TEST, and session-B has updated some of the rows of TEST and issued commit. Which data (post / pre) gets reflected from session-A query ?Q4) Can you explain, how a transaction initiates and completes for a DML statement? Explainhow the BUFFER CACHE, UNDO and REDO managed for huge DML transaction.

5A) Blocks & Extents

Q1) What is the difference between Oracle block and Oracle Extent?

8/6/2019 Oracle Certified Associates Exam

2/2

Q2) What is PCTFREE, and PCTUSED?Q3) What is high water mark in a block?Q4) What is Row chaining and Row migration? How do you address these issues?

5B) Segments / Objects

Q1) What is an Oracle segment?Q2) What is the difference between Oracle object and segment?Q3) How do you realize about how much space is being utilized by a segment on a

tablespace?Q4) When do you need to rebuild an Oracle index and why?

6) PT

Q1) What is the purpose of Statistics?Q2) Where does the statistics stores, and what information statistics covers?

7) Backup & Recovery

a: After you took complete user managed cold backup of entire databaseb: Added two datafiles next dayc: Third day database got crashed.d: database is in archive log mode.

Q1) Till when you will be able to restore / recover the database?Q2) Would you be able to open the database?Q3) Will you be able to recover the newly added datafiles as well?

Q4) How do you recognize the media corrupted blocks in the database?Q5) What are the steps to recover the block?

Q6) How many types of block corruption can cause?

Q7) Why does Oracle generates more redo while taking hot backup?Q8) What is fractured block?