Home » RDBMS Server » Server Administration » how to find a table located in particular datafile (xp)
how to find a table located in particular datafile [message #328997] Mon, 23 June 2008 12:31 Go to next message
imraan
Messages: 21
Registered: June 2008
Location: ayangudi
Junior Member
how to find a particular table is located in particular datafile pls explain me.

[Updated on: Mon, 23 June 2008 12:33]

Report message to a moderator

Re: how to find a table located in particular datafile [message #329005 is a reply to message #328997] Mon, 23 June 2008 12:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A table is not located in a datafile but its related extents are.
Query dba_extents.

Regards
Michel
Re: how to find a table located in particular datafile [message #329006 is a reply to message #328997] Mon, 23 June 2008 12:53 Go to previous messageGo to next message
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 #329008 is a reply to message #329006] Mon, 23 June 2008 13:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Varu,

As table can be partitioned they can be related to several tablespaces, so your explaination is not completly correct.

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.

Regards
Michel
Re: how to find a table located in particular datafile [message #329010 is a reply to message #328997] Mon, 23 June 2008 13:18 Go to previous messageGo to next message
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 #329300 is a reply to message #329006] Tue, 24 June 2008 23:58 Go to previous messageGo to next message
ora110
Messages: 42
Registered: September 2007
Location: China
Member

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.


perfect.
Re: how to find a table located in particular datafile [message #329498 is a reply to message #328997] Wed, 25 June 2008 10:43 Go to previous messageGo to next message
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 #329503 is a reply to message #329498] Wed, 25 June 2008 10:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
- Never ever use SYS but for maintenance purpose (startup, shutdown, backup, recover)
- SYS is special
- SYS is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS" and you'll see the immediate answer)
- SYS does not act like any other user
- When you use SYS Oracle deactivates some code path and activates others
- Whatever you do with SYS will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS for anything that can be done by another user.
Use SYS ONLY for something that can't be done by someone else.


Even for demo purpose.

Regards
Michel
icon7.gif  Re: how to find a table located in particular datafile [message #329505 is a reply to message #328997] Wed, 25 June 2008 10:58 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Michel ,
Thanks to point out that.I made a scenario while trying to simulate other situation Smile
Re: how to find a table located in particular datafile [message #329531 is a reply to message #328997] Wed, 25 June 2008 12:55 Go to previous messageGo to next message
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 #329537 is a reply to message #329531] Wed, 25 June 2008 13:06 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
varu123 wrote on Wed, 25 June 2008 10:55

If you have multiple datafiles,i bet your system will hang.


want to bet?


  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 = 'TBLREPORTDELTA')
SQL> /

FILE_NAME
--------------------------------------------------------------------------------
/u04/oradbf/orcl/ora1d/inlogic04.dbf
/u04/oradbf/orcl/ora1d/inlogic05.dbf
/u04/oradbf/orcl/ora1d/inlogic01.dbf
/u04/oradbf/orcl/ora1d/inlogic02.dbf
/u04/oradbf/orcl/ora1d/inlogic03.dbf

Re: how to find a table located in particular datafile [message #329599 is a reply to message #328997] Thu, 26 June 2008 00:08 Go to previous messageGo to next message
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 #329601 is a reply to message #329599] Thu, 26 June 2008 00:16 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
varu123 wrote on Wed, 25 June 2008 22:08
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?


You are babbling without specifics.

>SQL> select d.FILE_NAME from dba_data_files d,dba_extents e
YOU included above line
I just properly reworked your inefficient SQL
Your SELECT did not return any data from DBA_EXTENTS
Re: how to find a table located in particular datafile [message #329605 is a reply to message #328997] Thu, 26 June 2008 00:30 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
That was not my query.
WHom are you responding to?
Re: how to find a table located in particular datafile [message #329610 is a reply to message #329599] Thu, 26 June 2008 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How does oracle internally query dba_tables.

Where? I don't follow you there.

Regards
Michel

[Updated on: Thu, 26 June 2008 00:35]

Report message to a moderator

Re: how to find a table located in particular datafile [message #329614 is a reply to message #329605] Thu, 26 June 2008 00:42 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
varu123 wrote on Wed, 25 June 2008 22:30
That was not my query.
WHom are you responding to?

your post from
>Re: how to find a table located in particular datafile [message #329531 is a reply to message #328997

contained:
SQL> select d.FILE_NAME from dba_data_files d,dba_extents e
Re: how to find a table located in particular datafile [message #329619 is a reply to message #329610] Thu, 26 June 2008 00:57 Go to previous messageGo to next message
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

Re: how to find a table located in particular datafile [message #329620 is a reply to message #329614] Thu, 26 June 2008 00:59 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Ana,
Thats not my query
I copy pasted the query from the reply I was responding to.
Re: how to find a table located in particular datafile [message #329623 is a reply to message #329620] Thu, 26 June 2008 01:11 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
varu123 wrote on Wed, 25 June 2008 22:59
Ana,
Thats not my query
I copy pasted the query from the reply I was responding to.

I responded to what YOU posted.

Quote:

varu123 wrote on Wed, 25 June 2008 10:55

If you have multiple datafiles,i bet your system will hang.



No hang resulted!
Re: how to find a table located in particular datafile [message #329624 is a reply to message #329619] Thu, 26 June 2008 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
WHy two different results?

Your tablespace has 2 datafiles but the extents of your table are only in one of them.

Regards
Michel
Re: how to find a table located in particular datafile [message #329633 is a reply to message #329601] Thu, 26 June 2008 02:08 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Quote:
>SQL> select d.FILE_NAME from dba_data_files d,dba_extents e
YOU included above line
I just properly reworked your inefficient SQL
Your SELECT did not return any data from DBA_EXTENTS


Thats not me.
I am not the author of that code.

Thats not my inefficient sql.

[Updated on: Thu, 26 June 2008 02:09]

Report message to a moderator

Re: how to find a table located in particular datafile [message #329777 is a reply to message #329531] Thu, 26 June 2008 08:48 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
varu123 wrote on Wed, 25 June 2008 10:55
If you have multiple datafiles,i bet your system will hang.


Please post reproducible proof.
Re: how to find a table located in particular datafile [message #329805 is a reply to message #328997] Thu, 26 June 2008 10:47 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
That was a wrong analysis.
My response was to gkrishn also.
Anyways,
Why are you so much interested?

Re: how to find a table located in particular datafile [message #329810 is a reply to message #329805] Thu, 26 June 2008 10:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> Why are you so much interested?
It is the purpose of a forum. To have a discussion.
Re: how to find a table located in particular datafile [message #329811 is a reply to message #329805] Thu, 26 June 2008 10:56 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
varu123 wrote on Thu, 26 June 2008 08:47

Why are you so much interested?


Incorrect 'solution' is a disservice to everyone
Re: how to find a table located in particular datafile [message #329822 is a reply to message #328997] Thu, 26 June 2008 11:54 Go to previous message
varu123
Messages: 754
Registered: October 2007
Senior Member
Quote:
Incorrect 'solution' is a disservice to everyone

Look who is saying,who hardly contributes except some sarcastic replies.
Previous Topic: ORA-12154: TNS:could not resolve the connect identifier specified
Next Topic: DBMS_SPACE expects partition
Goto Forum:
  


Current Time: Sat Dec 03 13:48:06 CST 2016

Total time taken to generate the page: 0.17165 seconds