Oracle Concepts - finding table and index tablespace locations

Home
E-mail Us
Oracle Articles
New Oracle Articles

Oracle Training
Oracle Tips
Oracle Forum
Class Catalog

Remote DBA
Oracle Tuning
Emergency 911
RAC Support
Apps Support
Analysis
Design
Implementation
Oracle Support

SQL Tuning
Security
Oracle UNIX
Oracle Linux
Monitoring
Remote support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

Don Burleson Blog

Oracle Concepts - finding table and index tablespace locations

Oracle Tips by Burleson Consulting

Table and tablespace location

Finding out who owns a table and what tablespace it is in is a pretty common need of the DBA. In this query, we use the dba_tables view to find the owner and tablespace name of the EMP table.

SQL> select owner, table_name, tablespace_name

2 from dba_tables

3 where table_name='EMP';

OWNER TABLE_NAME TABLESPACE_NAME

-------------------- -------------------- -------------

SCOTT EMP USERS

POLL EMP USERS

As we can see from this query, we have two tables called EMP, owned by two different users (Scott and Poll). Both tables are contained in the USERS tablespace.

A good exercise for you might be to try to join this query with a view like DBA_EXTENTS and figure out just how big these tables are allocated.

This is an excerpt from the bestselling "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle Certified Master). It?s only $19.95 when you buy it directly from the publisher here.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

��