| Data File/Table Relation [message #496165] |
Fri, 25 February 2011 00:18  |
 |
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  |
John Watson
Messages: 8964
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 |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
