Tablespace Interview Questions & Answers

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

    Answer :

    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.

  2. Question 2. What Is Coalescing And Which Process Will Handle This?

    Answer :

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

  3. Oracle 8i Interview Questions

  4. Question 3. Can We Create Dictionary Managed Tablespace In 10g/11g?

    Answer :

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

  5. Question 4. Explain Pctfree And Pctused?

    Answer :

    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.

  6. Oracle 8i Tutorial

  7. Question 5. Can We Take System And Sysaux Tablespace Offline?

    Answer :

    We can take SYSAUX, but not SYSTEM.

  8. Oracle Interview Questions

  9. Question 6. How To Change The Segment Management Type For An Existing Tablespace?

    Answer :

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

  10. Question 7. 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?

    Answer :

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

  11. Oracle DBA Tutorial
    Oracle DBA Interview Questions

  12. Question 8. What Happens When You Take A Tablespace/datafile Offline Immediate?

    Answer :

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

  13. Question 9. How To Identify Which Datafiles Are Modified Today?

    Answer :

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

  14. SQL Database Interview Questions

  15. Question 10. Is It Possible To Reduce The Size Of Datafile?

    Answer :

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

  16. SQL Database Tutorial

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

    Answer :

    Encryption of the tablespace.

  18. Oracle 11g Interview Questions

  19. Question 12. What Is Bigfile Tablespace And Its Use?

    Answer :

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

  20. Oracle 8i Interview Questions

  21. Question 13. How Will Do Capacity Planning For Your Production Databases?

    Answer :

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

  22. Oracle 11g Tutorial

  23. Question 14. What Is Ora-1555 Error And How To Avoid That?

    Answer :

    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.

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

    Answer :

    It will start using SYSTEM tablespace.

  25. DB2 Using SQL Interview Questions

  26. Question 16. How To Convert Dictionary Managed Permanent Tablespace To Temporary Tablespace?

    Answer :

    It is not possible.

  27. DB2 Using SQL Tutorial

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

    Answer :

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

  29. SQL Interview Questions

  30. Question 18. What Is Omf? What Are Its Advantages And Disadvantages?

    Answer :

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

  31. Oracle Interview Questions

  32. Question 19. How You Will Get Timezone Of Database?

    Answer :

    NLS_TIMESTAMP_TZ_FORMAT

  33. Performance Management Tutorial

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

    Answer :

    By checking in v$tempseg_usage.

  35. MYSQL DBA Interview Questions

  36. Question 21. How To Drop A Datafile Without Dropping A Tablespace?

    Answer :

    SQL> alter database datafile ‘path’ offline drop;

  37. Question 22. How To Check Scn Of The Database?

    Answer :

    SQL> select current_scn from v$database;

  38. Question 23. Can We Make Temporary Tablespace Read Only?

    Answer :

    Not possible

  39. RMAN (Recovery Manager) Interview Questions

  40. Question 24. What Is The Relation Between Db_files And Maxdatafiles?

    Answer :

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

  41. Oracle DBA Interview Questions

  42. Question 25. When A Tablespace Is Offline, Can We Make It Read Only?

    Answer :

    Not possible.