Home » Infrastructure » Linux » To find existing tables located at which .dbf file (Oracle 10g)
To find existing tables located at which .dbf file [message #497066] Thu, 03 March 2011 20:08 Go to next message
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;
Re: To find existing tables located at which .dbf file [message #497070 is a reply to message #497066] Thu, 03 March 2011 20:32 Go to previous messageGo to next message
BlackSwan
Messages: 26668
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?
Re: To find existing tables located at which .dbf file [message #497109 is a reply to message #497070] Fri, 04 March 2011 00:47 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 66928
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: To find existing tables located at which .dbf file [message #497169 is a reply to message #497123] Fri, 04 March 2011 04:19 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 66928
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just add the segment name and owner in the WHERE clause of the query I posted.

Regards
Michel
Re: To find existing tables located at which .dbf file [message #497685 is a reply to message #497171] Sun, 06 March 2011 19:45 Go to previous messageGo to next message
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


Re: To find existing tables located at which .dbf file [message #497758 is a reply to message #497685] Mon, 07 March 2011 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 66928
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: To find existing tables located at which .dbf file [message #497765 is a reply to message #497758] Mon, 07 March 2011 01:43 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: To find existing tables located at which .dbf file [message #497771 is a reply to message #497767] Mon, 07 March 2011 02:01 Go to previous messageGo to next message
John Watson
Messages: 8201
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?
Re: To find existing tables located at which .dbf file [message #497773 is a reply to message #497767] Mon, 07 March 2011 02:13 Go to previous messageGo to next message
flyboy
Messages: 1884
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.
Re: To find existing tables located at which .dbf file [message #497866 is a reply to message #497773] Mon, 07 March 2011 11:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
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>

Re: To find existing tables located at which .dbf file [message #497869 is a reply to message #497866] Mon, 07 March 2011 11:26 Go to previous messageGo to next message
Michel Cadot
Messages: 66928
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: To find existing tables located at which .dbf file [message #498185 is a reply to message #497869] Wed, 09 March 2011 03:30 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8201
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.
Re: To find existing tables located at which .dbf file [message #498203 is a reply to message #498185] Wed, 09 March 2011 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 66928
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: To find existing tables located at which .dbf file [message #498212 is a reply to message #498189] Wed, 09 March 2011 04:31 Go to previous messageGo to next message
John Watson
Messages: 8201
Registered: January 2010
Location: Global Village
Senior Member
Here's an ER diagram that shows it.
Re: To find existing tables located at which .dbf file [message #498402 is a reply to message #498189] Wed, 09 March 2011 19:18 Go to previous messageGo to next message
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.
Re: To find existing tables located at which .dbf file [message #498404 is a reply to message #498203] Wed, 09 March 2011 19:35 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 21546
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.
Re: To find existing tables located at which .dbf file [message #498460 is a reply to message #498404] Thu, 10 March 2011 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 66928
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: To find existing tables located at which .dbf file [message #498705 is a reply to message #498460] Thu, 10 March 2011 20:25 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 26668
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
Re: To find existing tables located at which .dbf file [message #498712 is a reply to message #498707] Thu, 10 March 2011 20:52 Go to previous messageGo to next message
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 Go to previous message
BlackSwan
Messages: 26668
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?
Previous Topic: Can not see the ASM disks while using dbca
Next Topic: Install & configure RHEL5.4 OS Cluster
Goto Forum:
  


Current Time: Sun Mar 29 00:48:32 CDT 2020