OraFAQ Forum: Server Administration » how to find out table names from the given datafile?

Home » RDBMS Server » Server Administration » how to find out table names from the given datafile?

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

how to find out table names from the given datafile? [message #64444]

Sun, 30 January 2005 07:43

som
Messages: 18
Registered: April 2002

Junior Member

how to find out table names from the given datafile???
I have one datafile and I want to know what are the tables in that datafile.

Here are the details:

I have one tablespace:
TABLESPACE1

I have three datafiles included in that tablespace:
DATAFILE1
DATAFILE2
DATAFILE3

I deleted one datafile since It is giving some media recovery error.

Now I want to recover all the data ( all the objects that were ther into the datafile).
I have the clean backup and Im planning to spool all the table into flat file from the backup and load them into the new database.
But I am not getting how to get the name of the tables from that datafile.
Please help.
thanks

Report message to a moderator

Re: how to find out table names from the given datafile? [message #64450 is a reply to message #64444]

Mon, 31 January 2005 00:59

Sreedhar Reddy
Messages: 55
Registered: January 2002

Member

if you are having the backup of the particular datafile, you can make an media recovery, and making sure that your database in archive log mode.

copy your old copy of your datafile to the existing location, then shutdown the database..

startup in mount stage
recover
startup database.

Report message to a moderator

Re: how to find out table names from the given datafile? [message #64459 is a reply to message #64450]

Mon, 31 January 2005 03:05

som
Messages: 18
Registered: April 2002

Junior Member

hi shreedhar,
My problem is not database recovery.
I am having a cold backup.
and What I planned to do is that I wil export the tables into text files which are there in the missing datafile.
so I want to know which tables are there in the datafiles so that i can take there flat files and load them

Report message to a moderator

Re: how to find out table names from the given datafile? [message #64460 is a reply to message #64459]

Mon, 31 January 2005 03:11

Sreedhar Reddy
Messages: 55
Registered: January 2002

Member

I dont think that therz a way to find out what tables or objects are lying in the data files. Any way let me check....
i will get back with approriate solution

Report message to a moderator

Re: how to find out table names from the given datafile? [message #64479 is a reply to message #64460]

Tue, 01 February 2005 07:09

Satheesh Babu.S
Messages: 35
Registered: July 2004

Member

if you join dba_extents and dba_data_files you can get the segment, which are belonging to that particular datafiles.

Thanks and Regards,
Satheesh Babu.S
Bangalore.

Report message to a moderator

Re: how to find out table names from the given datafile? [message #64484 is a reply to message #64479]

Tue, 01 February 2005 20:06

Sreedhar Reddy
Messages: 55
Registered: January 2002

Member

Hi Sateesh,
We can even use dba_segments,dba_data_files .

select file_name,segment_name,s.tablespace_name
from dba_segments s,dba_data_files d
where s.tablespace_name=d.tablespace_name
and owner='MKT'

where this query has better performance that dba_extents and dba_data_files

Report message to a moderator

Previous Topic:	Old archive logs
Next Topic:	Resource Usage Calculation.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 25 03:16:11 CST 2025