OraFAQ Forum: Linux » To find existing tables located at which .dbf file

Home » Infrastructure » Linux » To find existing tables located at which .dbf file (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

To find existing tables located at which .dbf file [message #497066]

Thu, 03 March 2011 20:08

alvinng0618
Messages: 16
Registered: March 2011
Location: Malaysia

Junior Member

Hi guys, can someone provide me some information regarding how to identify the specific table is located at which .dbf file?

Let's say

Select table_name, tablespace_name from all_tables;

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #497070 is a reply to message #497066]

Thu, 03 March 2011 20:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Select table_name, tablespace_name from all_tables;
What happens when multiple files comprise a single tablespace?

>how to identify the specific table is located at which .dbf file?
To what use or purpose will the answer be used?

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #497109 is a reply to message #497070]

Fri, 04 March 2011 00:47

alvinng0618
Messages: 16
Registered: March 2011
Location: Malaysia

Junior Member

>Select table_name, tablespace_name from all_tables;
What happens when multiple files comprise a single tablespace?
Yes, this is what i need.

>how to identify the specific table is located at which .dbf file?
To what use or purpose will the answer be used?

The problem is, how to know the tables is stored in which .dbf?
29G users01.dbf
2.5G users02.dbf

if i using the statement like this
select table_name , blocks*8/1024 as MB, blocks from user_tables;

[Updated on: Fri, 04 March 2011 00:48]

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #497123 is a reply to message #497109]

Fri, 04 March 2011 01:21

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

select nvl(sum(e.bytes),0) bytes, f.file_name
from dba_extents e, dba_data_files f
where f.file_id = e.file_id (+)
group by f.file_name
/

Regards
Michel

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #497169 is a reply to message #497123]

Fri, 04 March 2011 04:19

alvinng0618
Messages: 16
Registered: March 2011
Location: Malaysia

Junior Member

Michel Cadot wrote on Fri, 04 March 2011 01:21

select nvl(sum(e.bytes),0) bytes, f.file_name
from dba_extents e, dba_data_files f
where f.file_id = e.file_id (+)
group by f.file_name
/

Regards
Michel

Thanks for your reply Michel.

Okay, here i apologize for you guys that I think i have made you guys confuse about my question. Okay, for instance, i want to check my one of the table named Table123 and i want to know its actual file is located in which .dbf files. Currently i got two types of .dbf files are existed in the server ,there are 29G users01.dbf and 2.5G users02.dbf. So, i need to know which .dbf files is storing the Table123.

Hope you guys forgiving me that i am newbie in oracle sql.

[Updated on: Fri, 04 March 2011 04:19]

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #497171 is a reply to message #497169]

Fri, 04 March 2011 04:24

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Just add the segment name and owner in the WHERE clause of the query I posted.

Regards
Michel

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #497685 is a reply to message #497171]

Sun, 06 March 2011 19:45

alvinng0618
Messages: 16
Registered: March 2011
Location: Malaysia

Junior Member

select nvl(sum(e.bytes),0) bytes, f.file_name
from dba_extents e, dba_data_files f
where f.file_id = e.file_id (+)
group by f.file_name
----- 9 rows selected.-----

select nvl(sum(e.bytes),0) bytes, e.owner, e.segment_name, f.file_name
from dba_extents e, dba_data_files f
where f.file_id = e.file_id (+) group by f.file_name, e.segment_name, e.owner;
----- 4801 rows selected. ------

I have tried this statement by adding segment_name and owner, it shows something like this

Bytes OWNER SEGMENT_NAME FILE_NAME
----- ------- -------------- -----------
65536 SYSMAN SYS_LOB0503010$$ /db/oracle/sysdb.dbf

I still cant get the specific file_name is indicated which tables? Also my current system has 1744 tables which mismatch with the above rows were selected.
SQL> select count(*) from dba_tables;

COUNT(*)
----------
1744

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #497758 is a reply to message #497685]

Mon, 07 March 2011 01:20

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Quote:

I still cant get the specific file_name is indicated which tables?

And what is the last column of your result?

Regards
Michel

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #497765 is a reply to message #497758]

Mon, 07 March 2011 01:43

alvinng0618
Messages: 16
Registered: March 2011
Location: Malaysia

Junior Member

First i have apologize for the informal writing post.

Quote:

And what is the last column of your result?

The last column i got is the file_name, which shows 4801 rows
but my system only have 1744 tables.

I may be doubt in this section, please forgiving me and kindly guide me. Thank you.

[Updated on: Mon, 07 March 2011 01:43]

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #497767 is a reply to message #497765]

Mon, 07 March 2011 01:48

alvinng0618
Messages: 16
Registered: March 2011
Location: Malaysia

Junior Member

Is it possible to have a query that can shows something like the below?

TABLE_NAME        FILE_NAME
-----------      ------------
Table123         /db/oracle/sysdb.dbf

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #497771 is a reply to message #497767]

Mon, 07 March 2011 02:01

John Watson
Messages: 8961
Registered: January 2010
Location: Global Village

Senior Member

One quick comment, alvinng0618: from looking at this thread, I think you may be missing one vital bit of information. This is that one table can have many extents, which may be spread across several datafiles. That is why you have more extents than tables, and is also why the output you are requesting is not possible.
Does that help?

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #497773 is a reply to message #497767]

Mon, 07 March 2011 02:13

flyboy
Messages: 1903
Registered: November 2006

Senior Member

alvinng0618 wrote on Mon, 07 March 2011 08:48

Is it possible to have a query that can shows something like the below?
TABLE_NAME        FILE_NAME
-----------      ------------
Table123         /db/oracle/sysdb.dbf

What about adding a WHERE clause, as suggested by Michel?

WHERE owner = <owner of table>
  AND segment_name = <name of the table>

You might use LIKE operator with appropriate pattern to get multiple ones in one query.

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #497866 is a reply to message #497773]

Mon, 07 March 2011 11:01

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

SCOTT@orcl_11gR2> column table_name format a42
SCOTT@orcl_11gR2> column file_name  format a51
SCOTT@orcl_11gR2> select at.owner || '.' || at.table_name table_name,
  2  	    ddf.file_name
  3  from   all_tables at,
  4  	    dba_extents de,
  5  	    dba_data_files ddf
  6  where  at.owner = de.owner
  7  and    at.table_name = de.segment_name
  8  and    de.file_id = ddf.file_id
  9  order  by table_name
 10  /

TABLE_NAME                                 FILE_NAME
------------------------------------------ ---------------------------------------------------
APEX_030200.SYS_IOT_OVER_70794             C:\APP\OWNER\ORADATA\ORCL\SYSAUX01.DBF
...
OE.CUSTOMERS                               C:\APP\OWNER\ORADATA\ORCL\USERS01.DBF
...
SCOTT.DEPT                                 C:\APP\OWNER\ORADATA\ORCL\USERS01.DBF
SCOTT.EMP                                  C:\APP\OWNER\ORADATA\ORCL\USERS01.DBF
...
SYS.ACCESS$                                C:\APP\OWNER\ORADATA\ORCL\SYSTEM01.DBF
...
XDB.XS$CACHE_DELETE                        C:\APP\OWNER\ORADATA\ORCL\SYSAUX01.DBF

4785 rows selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #497869 is a reply to message #497866]

Mon, 07 March 2011 11:26

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

A question: does not we count iot overfloaw and lob segments (that you don't include in your query) as part of the "parent" table (that is with the same value for the first column)? (...left as an exercise...)

Regards
Michel

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #498185 is a reply to message #497869]

Wed, 09 March 2011 03:30

alvinng0618
Messages: 16
Registered: March 2011
Location: Malaysia

Junior Member

May i know what are the relationship between all_tables, dba_extents and dba_data_files?

when i execute the statement which provided by Barbara,i retrieved 4 times of same tables displayed on screen, some of them only displayed once. Is that mean that the particular table has more than 1 same table_name which existing in the file_name, another word said that "duplication" table with same name??

TABLE_NAME                 FILE_NAME
-------------             -----------------
TABLE_1                   /db/oracle/orafile/users01.dbf
TABLE_1                   /db/oracle/orafile/users01.dbf
TABLE_1                   /db/oracle/orafile/users01.dbf
TABLE_1                   /db/oracle/orafile/users01.dbf
TABLE_2                   /db/oracle/orafile/users01.dbf
TABLE_2                   /db/oracle/orafile/users01.dbf
TABLE_2                   /db/oracle/orafile/users01.dbf
TABLE_2                   /db/oracle/orafile/users01.dbf
TABLE_3                   /db/oracle/orafile/users01.dbf

[Updated on: Wed, 09 March 2011 03:32]

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #498189 is a reply to message #498185]

Wed, 09 March 2011 03:46

John Watson
Messages: 8961
Registered: January 2010
Location: Global Village

Senior Member

I've already told you:

Quote:

one table can have many extents, which may be spread across several datafiles.

And don;t forget that dba_tables shows you only the logical structures, you must query dba_segments to get the physical structures.

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #498203 is a reply to message #498185]

Wed, 09 March 2011 04:18

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

May i know what are the relationship between all_tables, dba_extents and dba_data_files?

A table is one or more segments.
A segment is one or more extents.
An extent is in a file.
So a table can be spread into one or more data files and a file can contain 0 to N extents of a table.

Regards
Michel

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #498212 is a reply to message #498189]

Wed, 09 March 2011 04:31

John Watson
Messages: 8961
Registered: January 2010
Location: Global Village

Senior Member

Here's an ER diagram that shows it.

Attachment: storege_er.jpg
(Size: 20.36KB, Downloaded 1944 times)

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #498402 is a reply to message #498189]

Wed, 09 March 2011 19:18

alvinng0618
Messages: 16
Registered: March 2011
Location: Malaysia

Junior Member

John Watson wrote on Wed, 09 March 2011 03:46

I've already told you:

Quote:
one table can have many extents, which may be spread across several datafiles.

And don;t forget that dba_tables shows you only the logical structures, you must query dba_segments to get the physical structures.

Now i only understand why you said that at the previous reply. That's why the system select many rows which carried same info.

I have clear picture about the table structure, thank you so much for all people who contribute to help me.

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #498404 is a reply to message #498203]

Wed, 09 March 2011 19:35

alvinng0618
Messages: 16
Registered: March 2011
Location: Malaysia

Junior Member

One more question,

Michel Cadot wrote on Wed, 09 March 2011 04:18

So a table can be spread into one or more data files and a file can contain 0 to N extents of a table.

A single table can be spread into different file_name, or another word can say storing at different location ? So, actually is only one table existing in the system??
for instance:

TABLE_NAME          FILE_NAME
----------          ----------
TABLE_1            /db/oracle/orafile/user01.dbf
TABLE_1            /db/oracle/orafile/user02.dbf

[Updated on: Wed, 09 March 2011 19:36]

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #498440 is a reply to message #498404]

Thu, 10 March 2011 00:36

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

actually is only one table existing in the system??

Eh? What do you mean? Every user in your database has its own tables, and most probably more than just one table per user. (Not to mention SYS & SYSTEM. Without them, there's no database at all). So, the answer is no - there isn't only one table in a system.

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #498460 is a reply to message #498404]

Thu, 10 March 2011 01:14

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

A single table can be spread into different file_name, or another word can say storing at different location ?

Yes.

Quote:

for instance:

This can be only one table if the owner is the same one for both lines.

Regards
Michel

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #498705 is a reply to message #498460]

Thu, 10 March 2011 20:25

alvinng0618
Messages: 16
Registered: March 2011
Location: Malaysia

Junior Member

[quote title=Michel Cadot wrote on Thu, 10 March 2011 01:14]
Quote:

This can be only one table if the owner is the same one for both lines.
Yes

For the below display, the TABLE_1 size is 15.5 or 30.1 ? Only one Table_1 existed in system? And it has spread into different file_name location? Are these correct? I'm trying to conclude my question. Feel sorry about poor knowledge in oracle.

TABLE_NAME   FILE_NAME                        SIZE_MB    OWNER 
----------   -----------------------------    -------    ------
TABLE_1      /db/oracle/orafile/user01.dbf     15.5      ORAUSER
TABLE_1      /db/oracle/orafile/user02.dbf     15.5      ORAUSER

[Updated on: Thu, 10 March 2011 20:30]

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #498707 is a reply to message #498705]

Thu, 10 March 2011 20:38

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

when you post results without the SQL that generated it, it is hard to know what we are looking at

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #498712 is a reply to message #498707]

Thu, 10 March 2011 20:52

alvinng0618
Messages: 16
Registered: March 2011
Location: Malaysia

Junior Member

BlackSwan wrote on Thu, 10 March 2011 20:38

when you post results without the SQL that generated it, it is hard to know what we are looking at

select at.table_name, ddf.file_name, round ((at.blocks * dt.block_size / 1048576), 2) SIZE_MB, at.owner 
from dba_tablespaces dt, all_tables at, dba_extents de, dba_data_files ddf 
where at.owner = de.owner and at.table_name = de.segment_name and de.file_id = ddf.file_id 
group by at.owner, at.table_name, ddf.file_name, at.blocks, at.num_rows, dt.block_size 
order by at.blocks DESC;

I used the statement which posted by Barbara Boehmer and try modify abit.

[Updated on: Thu, 10 March 2011 20:52]

Report message to a moderator

Re: To find existing tables located at which .dbf file [message #498716 is a reply to message #498712]

Thu, 10 March 2011 21:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SELECT at.table_name,
       ddf.file_name,
       Round (( at.blocks * dt.block_size / 1048576 ), 2) size_mb,
       at.owner
FROM   dba_tablespaces dt,
       all_tables at,
       dba_extents de,
       dba_data_files ddf
WHERE  at.owner = de.owner
       AND at.table_name = de.segment_name
       AND de.file_id = ddf.file_id
GROUP  BY at.owner,
          at.table_name,
          ddf.file_name,
          at.blocks,
          at.num_rows,
          dt.block_size
ORDER  BY at.blocks DESC;

no mention of
dba_tablespaces dt
dba_data_files ddf
in WHERE clause but exist in FROM clause.
WHY?

Report message to a moderator

Previous Topic:	Can not see the ASM disks while using dbca
Next Topic:	Install & configure RHEL5.4 OS Cluster

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 12 20:10:33 CST 2024