Data File/Table Relation [message #496165] |
Fri, 25 February 2011 00:18 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/0edc5401ff22b4cca6a249f6b1bd46cc?s=64&d=mm&r=g) |
gxeon
Messages: 53 Registered: January 2011 Location: Mumbai
|
Member |
|
|
Hello
i need a help. i have 2 schemas in my database with over 500 tables in each.i am trying to know which tables actually belong to which datafile in which tablespace. i am new to DBA area.
please help.
Gaurav
|
|
|
Re: Data File/Table Relation [message #496186 is a reply to message #496165] |
Fri, 25 February 2011 02:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Hi - there is no direct relationship between the logical storage (tables) and the physical storage (datfiles). You can relate them by querying dba_extents. This will show the files containing extents of the table hr.regions:
select file_id,extent_id from dba_extents where
owner='HR' and segment_name='REGIONS';
You can take it from here! There are many other views you'll need to join in order to get a full, well documented, description of your storage.
|
|
|
Re: Data File/Table Relation [message #496193 is a reply to message #496165] |
Fri, 25 February 2011 03:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> select distinct
2 e.owner, e.segment_name, e.partition_name, e.segment_type,
3 e.tablespace_name, f.file_name
4 from dba_extents e, dba_data_files f
5 where f.file_id = e.file_id
6 and owner in ('MICHEL','SCOTT')
7 order by 1, 2, 3 nulls first, 6
8 /
OWNER SEGMENT_NAME PART SEGMENT_TYPE TABLESPACE FILE_NAME
------ ------------------------- ---- ------------------ ---------- -----------------------------------
MICHEL A TABLE TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
ABC TABLE TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
B TABLE TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
DELETE_ME TABLE TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
DELETE_PK INDEX TS_I01 C:\ORACLE\BASES\MIKA\TS_I0101.DBF
DEPT TABLE TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
DEPT_PK INDEX TS_I01 C:\ORACLE\BASES\MIKA\TS_I0101.DBF
DONOR TABLE TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
DONOR_STATUS TABLE TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
EMP TABLE TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
EMP_PK INDEX TS_I01 C:\ORACLE\BASES\MIKA\TS_I0101.DBF
ROLE_ASSIGN TABLE TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
SCRIPTS TABLE TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
SOURCE TABLE TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
SYS_C005537 INDEX TS_I01 C:\ORACLE\BASES\MIKA\TS_I0101.DBF
SYS_IL0000051377C00001$$ LOBINDEX TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
SYS_IOT_TOP_52184 INDEX TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
SYS_LOB0000051377C00001$$ LOBSEGMENT TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
TM TABLE TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
TM INDEX TS_I01 C:\ORACLE\BASES\MIKA\TS_I0101.DBF
TMPDIMENSION TABLE TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
TMPFACT TABLE TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
TR TABLE TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
USERS TABLE TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
USERS_PK INDEX TS_I01 C:\ORACLE\BASES\MIKA\TS_I0101.DBF
VW_ACAO_CRITICAL TABLE TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
SCOTT BONUS TABLE TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
DEPT TABLE TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
EMP TABLE TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
PK_DEPT INDEX TS_I01 C:\ORACLE\BASES\MIKA\TS_I0101.DBF
PK_EMP INDEX TS_I01 C:\ORACLE\BASES\MIKA\TS_I0101.DBF
SALGRADE TABLE TS_D01 C:\ORACLE\BASES\MIKA\TS_D0101.DBF
|
|
|