Home » RDBMS Server » Server Administration » Need to know in which tablespace/datafile my table is located (8.1.7,win 2003 server)
Need to know in which tablespace/datafile my table is located [message #464070] Tue, 06 July 2010 03:16 Go to next message
ultgnp
Messages: 106
Registered: May 2010
Location: Germany
Senior Member
Hi,

I was wondering if there is any way to know in which Tablespace and Datafile my Table is located. I have exported a table and about to delete it as i am partitioning it. Please reply..


Best Regards,
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 Go to previous messageGo to next message
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 #464076 is a reply to message #464072] Tue, 06 July 2010 03:33 Go to previous messageGo to next message
ultgnp
Messages: 106
Registered: May 2010
Location: Germany
Senior Member
Thanks, I know in which tablespace my table is. I would like to know, in which datafiles my table is located. Will my table extend in different datafiles?
Re: Need to know in which tablespace/datafile my table is located [message #464086 is a reply to message #464070] Tue, 06 July 2010 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Query DBA_EXTENTS with SEGMENT_NAME equals to your table name.

Regards
Michel
Re: Need to know in which tablespace/datafile my table is located [message #464098 is a reply to message #464086] Tue, 06 July 2010 04:30 Go to previous messageGo to next message
ultgnp
Messages: 106
Registered: May 2010
Location: Germany
Senior Member
thank you Michel.
Re: Need to know in which tablespace/datafile my table is located [message #464174 is a reply to message #464070] Tue, 06 July 2010 09:49 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
if your table is IOT then table space is not show in user_tables check for user constraint table.
Re: Need to know in which tablespace/datafile my table is located [message #464182 is a reply to message #464174] Tue, 06 July 2010 10:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t (col integer primary key)
  2  ORGANIZATION INDEX 
  3  /

Table created.

SQL> select * from user_tables where table_name='T';
TABLE_NAME                     Tablespace        CLUSTER_NAME                   IOT_NAME                       STATUS
------------------------------ ----------------- ------------------------------ ------------------------------ --------
  PCT_FREE   PCT_USED  INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS
---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ----------
FREELIST_GROUPS LOG B   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS
--------------- --- - ---------- ---------- ------------ ---------- ---------- ----------- -------------------------
NUM_FREELIST_BLOCKS DEGREE     INSTANCES  CACHE TABLE_LO SAMPLE_SIZE LAST_ANALYZED       PAR IOT_TYPE     T S NES
------------------- ---------- ---------- ----- -------- ----------- ------------------- --- ------------ - - ---
BUFFER_ ROW_MOVE GLO USE DURATION        SKIP_COR MON CLUSTER_OWNER                  DEPENDEN COMPRESS DRO
------- -------- --- --- --------------- -------- --- ------------------------------ -------- -------- ---
T                                                                                                              VALID
         0          0          0          0
                    N
                             1          1     N ENABLED                                  NO  IOT          N N NO
        DISABLED NO  NO                  DISABLED YES                                DISABLED DISABLED NO

1 row selected.

But yes it is not in user_EXTENTS.
SQL> select object_id from dba_objects where object_name='T';
 OBJECT_ID
----------
     54683

SQL> select * from user_extents where segment_name like '%54683';
SEGMENT_NAME                   PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ ------------------------------
 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
SYS_IOT_TOP_54683                                             INDEX              TS_D01
         0      65536          8

Regards
Michel

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 Go to previous messageGo to next message
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

Re: Need to know in which tablespace/datafile my table is located [message #465402 is a reply to message #465384] Wed, 14 July 2010 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ah! you meant tablespace name, I thought you meant table name!
NEVER search tablespace name in %_tables, it is the wrong place, use %_SEGMENTS or %_EXTENTS which give physical information of the table object and tablespace is a physical information.

Regards
Michel
Re: Need to know in which tablespace/datafile my table is located [message #465412 is a reply to message #465402] Wed, 14 July 2010 01:57 Go to previous message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Smile Indeed.
Previous Topic: error in shutting down the dB
Next Topic: ORA-24909
Goto Forum:
  


Current Time: Sun May 19 14:18:31 CDT 2024