Home » RDBMS Server » Server Administration » A row of table is in which tablespace? (Oracle 10g,)
A row of table is in which tablespace? [message #353142] Sat, 11 October 2008 22:51 Go to next message
sabbir_ru
Messages: 6
Registered: August 2008
Junior Member
Is there any way to know , A row of table is in which tablespace?
Re: A row of table is in which tablespace? [message #353143 is a reply to message #353142] Sat, 11 October 2008 23:12 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
I think there is no data dictionary to get this information. You have to use dbms_rowid.rowid_to_absolute_fno to get the file number. have a look on the example...
http://sadatdba.blogspot.com/2008/10/count-of-tables-rows-per-datafile.html
Re: A row of table is in which tablespace? [message #353155 is a reply to message #353142] Sun, 12 October 2008 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> create table t (val number) 
  2  partition by range (val) 
  3  ( partition p1 values less than (10) tablespace ts_d01,
  4  partition p2 values less than (maxvalue) tablespace ts_i01 )
  5  /

Table created.

SQL> insert into t values (1);

1 row created.

SQL> insert into t values (11);

1 row created.

SQL> select val, tablespace_name       
  2  from t, dba_data_files
  3  where dbms_rowid.rowid_to_absolute_fno(t.rowid,user,'T') = file_id
  4  /
       VAL TABLESPACE_NAME
---------- ------------------------------
         1 TS_D01
        11 TS_I01

2 rows selected.

Regards
Michel
Re: A row of table is in which tablespace? [message #353552 is a reply to message #353155] Tue, 14 October 2008 02:58 Go to previous message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
Thats a great information, thanks Michael.
Previous Topic: AWR - oracle 10g is not running.. please help (2 threads merged by bb)
Next Topic: temp segment issue
Goto Forum:
  


Current Time: Fri Nov 29 17:37:06 CST 2024