Locate Table

[Fiza15]

Hi PPl
I create a table under Scott...Now how can i find out which table space and datafile that table belongs to on svrmgr screen...Thanks

[feroz]

Hi,

Try query dba_tables and dba_data_files table. I suppose you can get answer from that tables.

regards,
feroz

[Fiza15]

Thanks Feroz been thru these queries but the question is how can you pin point your table lets say a tablesspace consist of more than one datafiles how can you say which datafile your table gone into...without knowing SCOTT'S default tablespace....Thanks Again

[sreddy]

Table and Tablespace are logical structures. All you need to know is which tablespace its belongs, not which data file(physical file) it belongs. I don't understand why do you want to locate the datafile where the table exists ?

Try DBA_SEGMENTS view.

I don't think you can locate a particular datafile on which table exists if tablespace has multiple datafiles. Depending upon the size of the table it might be on all the files too...

[feroz]

Hi again,

good point there. try use table called dba_extents and use colume "owner" "segment_name" "segment_type" "tablespace_name" and "file_id".

When you get the file_id number then you query dba_data_files table to get the actual datafile it stored into.

regards,
feroz

[Shestakov]

U may use a couple of ways to do that:

1) as posted Sam:
U have to use dba_segments + dba_data_files and join tables thru fields -->
dba_segments(header_file) + dba_data_files(file_id)

2) as posted Feroz:U have to use dba_extents + dba_data_files and join tables thru fields -->
dba_extents(file_id) + dba_data_files(file_id)

3) if u want to know where place each row from some table
u have to use rowid:
-- select rowid for each row
-- convert it to character string
-- extract fileid from rowid
-- convert HEX fileid to number (+1)
-- use dba_data_files for get filename