|
|
|
|
| Re: how to find a table located in particular datafile [message #329006 is a reply to message #328997] |
Mon, 23 June 2008 12:53   |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
Tables are logical structures and hence are stored in another logical structures called tablespaces.
Data files are physical structures that you can see physically in the OS.
Tables are not located in datafiles.
As they are logical structures,they are stored in tablespaces.
Datafiles store just the data. They do not know anything about tables.They just know about the tablespaces they are attched with and the data they contain.
So your question
| Quote: | how to find a particular table is located in particular datafile pls explain me.
|
is not correct.
The question should be
how to find a particular table located in particular tablespace for which the answer is:
select table_name,tablespace_name from user_tables where table_name='yourtablename'; |
|
|
|
|
|
| Re: how to find a table located in particular datafile [message #329010 is a reply to message #328997] |
Mon, 23 June 2008 13:18 |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
Hmm,
In the end again we have a tables related to tablespaces. I just said this thing.
| Quote: | Tables are built of one or several segments, each segment is located in one tablespace that may have several files and each segment is built of one or several extents, each one is located in one datafile.
|
Yes execellent.
@OP this line stated by Michel cadot explains everything.
If you read it,you will find that data is distributed in datafiles.
That is it is not necessary that whatevere data is getting inserted
through a table is going only to one datafile.
Oracle uses datafiles in random fashion.if there are multiple files
associated with the tablespace,oracle can use any of them randomly to insert that data.
So when you run a query,the data that you see in the form of a table might be coming from one or more datafiels associated with the tablespace.
|
|
|
|
|
|
| Re: how to find a table located in particular datafile [message #329498 is a reply to message #328997] |
Wed, 25 June 2008 10:43 |
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
|
Senior Member |
|
|
SQL> show user
USER is "SYS"
SQL> create table test_table(Name varchar2(10));
Table created.
SQL> select d.FILE_NAME from dba_data_files d,dba_extents e
2 where e.segment_name='TEST_TABLE' and d.file_id=e.file_id;
FILE_NAME
---------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
@OP - a very simple scenario on what you asked. I agree with others, its extents(not table) which are getting allocated in tablespace-->datafiles
|
|
|
|
|
|
|
|
| Re: how to find a table located in particular datafile [message #329531 is a reply to message #328997] |
Wed, 25 June 2008 12:55 |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
| Quote: | SQL> show user
USER is "SYS"
SQL> create table test_table(Name varchar2(10));
Table created.
SQL> select d.FILE_NAME from dba_data_files d,dba_extents e
2 where e.segment_name='TEST_TABLE' and d.file_id=e.file_id;
FILE_NAME
---------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
@OP - a very simple scenario on what you asked. I agree with others, its extents(not table) which are getting allocated in tablespace-->datafiles
|
Insert some data in it.I guess you have only one system datafile.
If you have multiple datafiles,i bet your system will hang.
|
|
|
|
|
|
| Re: how to find a table located in particular datafile [message #329599 is a reply to message #328997] |
Thu, 26 June 2008 00:08 |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
ok,
I didn't notice you were querying dba_extents.
That would list the extents of the table which is fine.
But what about querying dba_data_files and dba_tables.
It does work but why?
As to a datafile,nothing is like table, its all extents
How does oracle internally query dba_tables.
Does it go to dba_extents?
|
|
|
|
|
|
|
|
|
|
|
|
| Re: how to find a table located in particular datafile [message #329619 is a reply to message #329610] |
Thu, 26 June 2008 00:57 |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
Michel,
This query queries dba_tables and return two rows.
SQL>
1 select d.file_name from dba_data_files d,dba_tables t
2 where d.tablespace_name=t.tablespace_name
3* and owner='TREAS' and t.table_name='KOTB'
SQL> /
FILE_NAME
--------------------------------------------------------------------------------
/u1/app/oracle/oradata/test/treas.dbf
/u1/app/oracle/oradata/test/treas1.dbf
This query return only one row.
SQL>
1 select d.FILE_NAME
2 from dba_data_files d
3 where d.file_id in (select e.file_id from dba_extents e
4* where e.segment_name = 'KOTB')
SQL> /
FILE_NAME
--------------------------------------------------------------------------------
/u1/app/oracle/oradata/test/treas.dbf
WHy two different results?
[Updated on: Thu, 26 June 2008 00:58] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
