L1 Level Questions

1. What is a Tablespace in Oracle?

   • Explain the concept of a tablespace and its role in Oracle database storage.

2. What are the different types of Tablespaces in Oracle?

   • Discuss the SYSTEM, SYSAUX, UNDO, TEMP, and user-defined tablespaces.

3. How do you create a new Tablespace in Oracle?

   • Provide the basic SQL command for creating a tablespace.

4. What is the default Tablespace for a new user in Oracle?

   • Explain how to set and change the default tablespace for a user.

5. What is a Datafile in Oracle?

   • Describe what a datafile is and its relationship to a tablespace.

6. How do you add a Datafile to an existing Tablespace?

   • Provide the SQL command to add a datafile to a tablespace.

7. What is the difference between a Tablespace and a Schema?

   • Explain the differences and relationships between a tablespace and a schema.

8. How do you check the size of a Tablespace?

   • Describe the SQL query or method to find the current size and usage of a tablespace.

9. What is the use of TEMP Tablespace?

   • Discuss the purpose of the TEMP tablespace in Oracle.

10. How do you resize a Datafile in Oracle?

    • Provide the SQL command to resize an existing datafile.

11. What happens when a Tablespace becomes full?

    • Explain the actions an Oracle DBA can take when a tablespace reaches its maximum size.

12. How do you drop a Tablespace?

    • Provide the SQL command to drop a tablespace and discuss the implications.

L2 Level Questions

1. Explain the difference between locally managed and dictionary managed Tablespaces.

   • Discuss the differences in extent management and advantages of locally managed tablespaces.

2. What are the advantages of using Bigfile Tablespaces?

   • Describe the benefits and use cases for bigfile tablespaces.

3. How do you monitor Tablespace usage and growth?

   • Explain the methods and tools used to monitor and predict tablespace usage and growth trends.

4. What is the significance of the SYSTEM and SYSAUX Tablespaces?

   • Discuss the roles and differences between these two system tablespaces.

5. How do you handle Tablespace fragmentation?

   • Describe the techniques used to manage and reduce tablespace fragmentation.

6. What is an Undo Tablespace and how is it different from Rollback Segments?

   • Explain the purpose of undo tablespaces and how they replace rollback segments in modern Oracle databases.

7. How do you move a Datafile to a different location?

   • Provide the steps and commands needed to relocate a datafile to a different disk or directory.

8. What is the role of the DBA_EXTENTS and DBA_SEGMENTS views?

   • Discuss how these views can be used to analyze tablespace and segment usage.

9. How do you convert a Tablespace from dictionary managed to locally managed?

   • Explain the procedure and considerations for converting tablespace management.

10. What are the common causes of Tablespace corruption and how do you recover from it?

    • Discuss the potential causes of tablespace corruption and the steps to recover from it.

11. Describe the process of tablespace point-in-time recovery (TSPITR).

    • Explain the scenarios where TSPITR is used and the steps involved in performing it.

12. How do you manage temporary tablespace groups?

    • Discuss the creation, management, and benefits of using temporary tablespace groups.

13. What is the significance of the AUTOEXTEND option for Datafiles?

    • Explain how the AUTOEXTEND option works and its advantages and potential pitfalls.

14. How do you migrate data between Tablespaces?

    • Provide methods and tools used to move objects from one tablespace to another, including the use of Data Pump and ALTER TABLE MOVE commands.

15. What is the impact of tablespace encryption and how do you enable it?

    • Discuss the steps to enable tablespace encryption and its impact on performance and security.

16. How do you handle the ORA-01555: snapshot too old error?

    • Explain the causes of this error and the strategies to prevent and resolve it.

17. What are tablespace quotas and how do you manage them?

    • Describe the use of quotas to limit the amount of space a user can use within a tablespace.

18. Explain the significance of the segment space management options (AUTOALLOCATE and UNIFORM) for a tablespace.

    • Discuss the differences between AUTOALLOCATE and UNIFORM extent management.

19. How do you monitor and optimize temporary tablespace usage?

    • Provide strategies for monitoring and optimizing the use of temporary tablespaces.

20. What are the steps to perform a transportable tablespace export/import?

    • Explain the procedure for transporting tablespaces between different Oracle databases using Data Pump.

Advanced Level (L3) Questions

1. Explain the architecture and benefits of Bigfile Tablespaces compared to traditional Smallfile Tablespaces.

   • Discuss the internal structure, advantages, and specific scenarios where Bigfile Tablespaces are beneficial over Smallfile Tablespaces.

2. How do you perform online migration of a tablespace to a different storage system with minimal downtime?

   • Describe the process and tools used for online tablespace migration, including the use of features like Oracle Data Guard, RMAN, and Transportable Tablespaces.

3. What are the implications of different block sizes for tablespaces, and how do you determine the optimal block size for a specific workload?

   • Discuss how block sizes affect I/O performance, storage efficiency, and how to choose the appropriate block size based on the nature of the database workload.

4. How do you handle tablespace-level encryption, and what are the performance considerations?

   • Explain the steps to enable tablespace encryption using Transparent Data Encryption (TDE), and discuss the performance impact and best practices for managing encrypted tablespaces.

5. Describe the process of extending a tablespace using ASM (Automatic Storage Management) and the considerations involved.

   • Detail the steps for adding space to a tablespace in an ASM environment, including considerations for disk group management, redundancy, and performance.

6. How do you perform a point-in-time recovery (PITR) for a specific tablespace?

   • Explain the detailed steps for performing a tablespace point-in-time recovery using RMAN, including the prerequisites and potential challenges.

7. What are the strategies for managing and optimizing the usage of temporary tablespaces in a highly concurrent environment?

   • Discuss techniques for monitoring temporary tablespace usage, preventing temporary tablespace exhaustion, and optimizing performance in environments with high concurrent temporary space demands.

8. Explain the impact of segment space management (SSM) options, such as AUTOALLOCATE vs. UNIFORM extents, on tablespace performance and storage efficiency.

   • Provide a detailed comparison of AUTOALLOCATE and UNIFORM extent management, and how each option affects tablespace performance, storage fragmentation, and administration.

9. How do you diagnose and resolve tablespace-related performance issues, such as high I/O wait times or contention?

   • Describe the approach to identifying tablespace performance bottlenecks, using tools like AWR (Automatic Workload Repository), ADDM (Automatic Database Diagnostic Monitor), and OS-level diagnostics.

10. What is the role of Oracle Managed Files (OMF) in tablespace and datafile management, and what are the advantages and limitations of using OMF?

    • Discuss how OMF simplifies database file management, the configuration steps to enable OMF, and any trade-offs or limitations associated with its use.

11. Describe the process and considerations for converting a dictionary-managed tablespace to a locally managed tablespace.

    • Explain the steps to migrate from dictionary-managed to locally managed tablespaces, including potential risks, benefits, and any downtime required.

12. How do you implement and manage tablespace quotas, and what are the implications for database users?

    • Detail the steps to set tablespace quotas, monitor their usage, and manage scenarios where users exceed their allocated quotas.

13. Explain the significance of the High Water Mark (HWM) in tablespace management and strategies for dealing with HWM-related issues.

    • Discuss how the HWM affects tablespace performance, fragmentation, and space reclamation, and the methods to manage or reset the HWM.

14. How do you handle the scenario where a datafile in a tablespace is corrupted?

    • Describe the steps to diagnose datafile corruption, methods to recover the datafile using RMAN, and strategies to prevent future corruption.

15. What are the best practices for managing tablespaces in a multi-tenant (Pluggable Database - PDB) environment?

    • Discuss the unique challenges and best practices for tablespace management in a multi-tenant architecture, including considerations for PDB creation, management, and consolidation.

16. How do you monitor and manage the performance of tablespaces in a highly transactional database environment?

    • Explain the tools and techniques used to monitor tablespace performance, manage I/O load, and optimize performance in environments with high transaction rates.

17. Describe the steps to perform a cross-platform tablespace transport and the challenges involved.

    • Provide a detailed process for transporting tablespaces across different platforms, including endian conversion, using tools like RMAN and Data Pump.

18. What are the considerations for setting up and managing tablespaces in a Data Guard configuration?

    • Discuss the impact of Data Guard on tablespace management, including considerations for redo transport, tablespace creation, and failover scenarios.

19. Explain the process and best practices for reclaiming unused space within a tablespace.

    • Describe techniques for identifying and reclaiming unused space, such as shrinking segments, moving objects, and coalescing free space.

20. How do you approach the migration of tablespaces from a non-CDB to a CDB architecture?

    • Outline the steps and considerations for migrating tablespaces from a traditional single-instance database to a Container Database (CDB) with Pluggable Databases (PDBs).