Problem to retrieve data from a datafile

[harakim]

I explain my problem ,,, I work on oracle version 9.2

I deleted a tablespace (drop tablespace including contents ...)
I kept the datafiles, they are in the directory oradata

Consequence of the drop: the table attached to this tablespace has disappeared.

Step 1: I recreate the tablespace with datafiles in oradata with the reuse option

Step 2: I relaunch the script to create my table

problem: my table is empty. I thought when using the reuse recreated on the relationship between datafile and tablespace and move on recovering the data tables attached to the tablespace. (I say I started in database administration)

Questions:

- How to recover data from my table?
- The datafile in oradate is emptied when you drop the tablespace?
- What does the reuse option, if you can not retrieve data from a datafile? especially that we must again, the size allocated to the datafile

I would like to understand the principle that

[Modifier]

We must restore the basis for a while before deleting the tablespace.

The REUSE option is documented in the SQL Reference:

REUSE

Specify REUSE to allow Oracle to reuse an existing file. You must specify REUSE if you specify a filename that already exists.

If the file already exists, then Oracle reuses the filename and applies the new size (if you specify SIZE) or retains the original size. If the file does not exist, then Oracle ignores this clause and creates the file. Restriction on the REUSE Clause . You can not specify REUSE unless you have specified filenam

[Katty]

In addition you used

DROP tablespace ...  including contents

As indicated, including contents, deletes the contents of tablespace ...

The REUSE option avoids an error specifying that the TABLESPACE / DATAFILE is already at its creation ...

The 2 SQL commands that you have started forcing your deleted data.

Sorry, but as you say pifor can release a good old backup.

If you're 9i/10g may be you can take a look on the following features:

• RECYCLEBIN
• FLASHBACK TABLE

But without safeguards

[harakim]

The solution for which I opted not to have backup was to export the structure of my tables in another environment (in other DEV) to rebuild recipe .

Then recharge my table with an ETL.

Last question: How To export only data of a pattern?

I know export structures of a schema or export the data (just a table) but the data (and not only structre) a scheme ?

Thank you in advance.

[Katty]

In 10g there are parameters as data_only, metadata_only if I remember correctly (using the expdp / impdp)

In general the export is not a problem, you can just export everything you want ... In your case you exported a schema or a table.

By or against the risk of this jam is when importing ...

I think in your case, before starting the import, you make sure you import the tables empty (logically yes if you have already imported the structure parameters with rows .

With these parameters:
rows = y -> You imported the data well
ignore = y -> to ignores errors in the creation of tables, in your case which already exist.
fromuser / all -> pattern source / target. Maybe Optional Features ...


Be aware that some entries will be rejected may be because of the primary keys / foreign or triggers ...
I had to work around a small script which disables all this before the import ...