Re: how to find out table names from the given datafile???

soham wrote:
> 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

I'm not sure why you got replies that went along the lines 'take a look at dba_extents/dba_segments', because what you want to achieve cannot be done *in general*. If you look at the create table syntax there is no way to put a table into a specific datafile, and, with the exception of very small tables, the table (to be precise: it's extents) will eventually be located in *all* datafiles that belong to the tablespace.

Since you ommitted the version of oracle involved, I'll leave out the finer details, too.

And as a last word: If you get an error with a datafile about needing media recovery, then the very last thing you do is just to delete it, or do you shoot yourself in the foot if your toes itch?

Regards

Holger Received on Sat Jan 29 2005 - 08:24:45 CST