Oracle Tablespace Interview Questions and Answers
Basic Level
Q: What is a tablespace in Oracle?
A: A tablespace is a logical storage unit in Oracle that groups related logical structures such as tables and indexes.
Q: What is the purpose of a tablespace?
A: It organizes data storage at the logical level, allowing efficient management of space allocation and access control.
Q: What are the types of tablespaces in Oracle?
A: Permanent, Temporary, Undo, Bigfile, and Smallfile tablespaces.
Q: What is the difference between SYSTEM and SYSAUX tablespaces?
A: SYSTEM stores core data dictionary tables; SYSAUX holds auxiliary metadata.
Q: What is the USERS tablespace used for?
A: It's the default tablespace for storing user data.
Q: How is a tablespace different from a datafile?
A: A tablespace is logical; a datafile is the physical file storing actual data.
Q: How do you create a tablespace?
A: CREATE TABLESPACE example_tbs DATAFILE 'example_tbs01.dbf' SIZE 100M;
Q: Can a table span multiple tablespaces?
A: No, unless it's partitioned, in which case each partition can be in a different tablespace.
Intermediate Level
Q: What is the difference between temporary and permanent tablespaces?
A: Permanent stores persistent schema objects; Temporary is for intermediate operations like sorting.
Q: How does undo tablespace differ from temporary tablespace?
A: Undo stores data for rollback; Temporary supports joins and sorts.
Q: What is a bigfile tablespace, and when would you use it?
A: Bigfile uses a single large datafile, ideal for large databases using ASM.
Q: What command would you use to check available tablespaces?
A: SELECT tablespace_name FROM dba_tablespaces;
Q: How do you resize a tablespace or its datafile?
A: ALTER DATABASE DATAFILE 'filename.dbf' RESIZE 500M;
Q: How can you make a tablespace read-only?
A: ALTER TABLESPACE example_tbs READ ONLY;
Q: What happens when a tablespace runs out of space?
A: DML operations may fail unless autoextend is enabled or more space is added.
Advanced Level
Q: What is the role of tablespaces in Oracle ASM environments?
A: Tablespaces use ASM-managed datafiles while ASM handles physical storage.
Q: How do tablespaces impact performance?
A: They reduce I/O contention and isolate workloads for better performance.
Q: What are locally managed and dictionary managed tablespaces?
Oracle Tablespace Interview Questions and Answers
A: Locally managed use bitmaps; dictionary managed use data dictionary (obsolete).
Q: How do you move a table to a different tablespace?
A: ALTER TABLE table_name MOVE TABLESPACE new_tbs;
Q: How can you monitor space usage in a tablespace?
A: SELECT tablespace_name, used_space, free_space FROM dba_tablespace_usage_metrics;
