Dev Corner

Problem:
Oracle tables are composed by extents. Extents are allocated from tablespace. You are given a table and you need to find in which tablespace it is stored.

Solution
To find tablespace for tables in current user’s schema:

SELECT table_name, tablespace_name, STATUS
  FROM user_tables
 WHERE table_name LIKE :p_table_pattern;

To find tablespaces for tables from all schemas that current user can access:

SELECT table_name, tablespace_name, STATUS
  FROM all_tables
 WHERE table_name LIKE :p_table_pattern;

To find tablespaces for all tables from all schemas, you need to be granted SYSDBA system privilege:

SELECT table_name, tablespace_name, STATUS
  FROM dba_tables
 WHERE table_name LIKE :p_table_pattern;