Top 25 Tablespace Interview Questions You Must Prepare 24.Jun.2024

Q1. What Is The Relation Between Db_files And Maxdatafiles?

Both will specify how many max datafiles can be there in the database.

Q2. Can We Make Temporary Tablespace Read Only?

Not possible

Q3. How To Drop A Datafile Without Dropping A Tablespace?

SQL> alter database datafile ‘path’ offline drop;

Q4. Can We Create Dictionary Managed Tablespace In 10g/11g?

Yes, but SYSTEM tablespace also should be dictionary for that.

Q5. What Happens When You Take A Tablespace/datafile Offline Immediate?

Any existing transactions will be stopped and datafiles of this tablespace need recoverywhen we make them online.

Q6. A Select Query Is Executing And You Observed Temp Tablespace Is Getting Full. You Added 50gb, But Even That Is Eaten Up. What Is Your Next Action?

We need to see what that query is and if required we need to tune it.

Q7. When A Tablespace Is Offline, Can We Make It Read Only?

Not possible.

Q8. How To Convert Dictionary Managed Permanent Tablespace To Temporary Tablespace?

It is not possible.

Q9. How To Identify Which Datafiles Are Modified Today?

By looking at the timestamp of the files at OS level.

Q10. Explain Pctfree And Pctused?

PCTFREE is the reserved space which is to be used by rows for future updates.

PCTUSED is the level of data in the block which allows further data insertion only after the level decreases beyond that.

Q11. What Is The Difference Between Dictionary Managed Tablespace And Locally Managed Tablespace?

In dictionary managed tablespace, free block information is maintained in data dictionary cache there by increases IO. In locally managed tablespace, the same information is maintained in datafile header itself there by reducing the IO. Hence LMT is better than DMT.

Q12. How Will You Find Out The Current Users Who Are Using Temporary Tablespace Segments?

By checking in v$tempseg_usage.

Q13. How Will Do Capacity Planning For Your Production Databases?

We will take every quarter for normal databases and every week for critical and fast growing databases.

Q14. What Is Coalescing And Which Process Will Handle This?

Combining free blocks together is called coalescing and SMON will handle this only if ASSM is enabled for the tablespace.

Q15. What Is Omf? What Are Its Advantages And Disadvantages?

It helps in managing files by oracle automatically. But naming convention will be the problem.

Q16. Can We Take System And Sysaux Tablespace Offline?

We can take SYSAUX, but not SYSTEM.

Q17. What Is Bigfile Tablespace And Its Use?

It gives easy managebility for VLDB by providing terabytes size to a single datafile itself.

Q18. What Is Ora-1555 Error And How To Avoid That?

It is snapshot too old error which will occur if data is not found in undo or datafile by a select statement. Increasing undo tablespace size, undo retention time, using retention gurantee clause with DML statement are the solutions.

Q19. How To Change The Segment Management Type For An Existing Tablespace?

Once defined, we cannot change segment space management for any tablespace.

Q20. If A Transaction Is Running And Dba Has Taken Particular Rollback Segment Offline. What Happens To That Transaction?

It will start using SYSTEM tablespace.

Q21. How You Will Get Timezone Of Database?


Q22. Is It Possible To Reduce The Size Of Datafile?

Yes, but data level should not have been crossed the limit.

Q23. How To Check Scn Of The Database?

SQL> select current_scn from v$database;

Q24. You Are Trying To Add A Datafile, But Its Not Allowing And Saying You Cannot Add It. What Might Be The Reason?

Either MAXDATAFILES controlfile parameter or DB_FILES parameter in pfile might have reached its max value.

Q25. What Is The New Feature Of 11g Tablespace Management?

Encryption of the tablespace.