|
Re: Need to know in which tablespace/datafile my table is located [message #464072 is a reply to message #464070] |
Tue, 06 July 2010 03:23 |
srinivasb
Messages: 2 Registered: July 2010 Location: Hyderabad
|
Junior Member |
|
|
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.
|
|
|
|
|
|
|
|
Re: Need to know in which tablespace/datafile my table is located [message #465384 is a reply to message #464070] |
Tue, 13 July 2010 23:49 |
|
rahulvb
Messages: 924 Registered: October 2009 Location: Somewhere Near Equator.
|
Senior Member |
|
|
Sorry Michel In Not in user_tables ,
SQL> create table t (col integer primary key)
2 ORGANIZATION INDEX
3 /
SQL> select TABLE_NAME,TABLESPACE_NAME from user_tables where table_name='T';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T
Elapsed: 00:00:00.59
My Version :-
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production
Elapsed: 00:00:00.57
[Updated on: Tue, 13 July 2010 23:50] Report message to a moderator
|
|
|
|
|