Oracle in World: How to know a row of a table belong to which tablespace

If someone ask you a question about "how you will determine a table belong to which tablespace." The answer is easy, query from dba_segments/user_segments/all_segments. Like I want to know table T belongs to which tablespace. Then query like below.

SQL> select tablespace_name from dba_segments where segment_name='T';

TABLESPACE_NAME
------------------------------
USERS

So table T belong to USERS tablespace.

If we want to know a table belongs to which datafile we can also determine it by querying from dba_data_files and dba_extents as below.

SQL> select file_name from dba_data_files where file_id in
2 (select file_id from dba_extents where segment_name='T');

FILE_NAME
-----------------------------------------------------------------------
D:\APP\ARJU\ORADATA\ARJU\USERS01.DBF

But if someone asked you to determine about a row of a table resides on which tablespace then you might think once how to do it. Following is an example of how to do it.

1)Create a partition table named test.

SQL> create table test (col1 number)
2      partition by range (col1)
3      (
4      partition p1 values less than (100) tablespace users,
5      partition p2 values less than (200) tablespace users02,
6      partition p3 values less than (maxvalue) tablespace users03
7      )
8  /

Table created.

2)Insert some rows so that they span into multiple tablespaces.

SQL> insert into test values (1);

1 row created.

SQL> insert into test values (111);

1 row created.

SQL> insert into test values (1000);

1 row created.

3)Query to select row and corresponding tablespace.

SQL> select col1, tablespace_name
2      from test, dba_data_files
3      where dbms_rowid.rowid_to_absolute_fno(test.rowid,user,'TEST') = file_id
4      /

COL1 TABLESPACE_NAME
---------- ------------------------------
1 USERS
111 USERS02
1000 USERS03

4)If you were asked to determine the tablespace name which holds the value 1 and determine it's tablespace_name, file_name then query as,

SQL> set lines 100
SQL> col file_name for a50
SQL> select col1, tablespace_name, file_name
2          from test, dba_data_files
3          where dbms_rowid.rowid_to_absolute_fno(test.rowid,user,'TEST') = file_id
4          and col1=1;

COL1 TABLESPACE_NAME                FILE_NAME
---------- ------------------------------ -------------------------------------------
1 USERS                          D:\APP\ARJU\ORADATA\ARJU\USERS01.DBF

Related Documents

ROWID and UROWID Datatype in Oracle
Oracle Built in Datatypes

Oracle in World

Tuesday, July 7, 2009

How to know a row of a table belong to which tablespace

ROWID and UROWID Datatype in Oracle

Oracle Built in Datatypes

No comments:

Post a Comment