Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 22-05-2009
Member
 
Join Date: Apr 2008
Posts: 49
Problem to retrieve data from a datafile

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
Reply With Quote
  #2  
Old 22-05-2009
Member
 
Join Date: Jan 2008
Posts: 1,515
Re: Problem to retrieve data from a datafile

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
Reply With Quote
  #3  
Old 22-05-2009
Member
 
Join Date: May 2008
Posts: 2,008
Re: Problem to retrieve data from a datafile

In addition you used

Code:
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
Reply With Quote
  #4  
Old 22-05-2009
Member
 
Join Date: Apr 2008
Posts: 49
Re: Problem to retrieve data from a datafile

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.
Reply With Quote
  #5  
Old 22-05-2009
Member
 
Join Date: May 2008
Posts: 2,008
Re: Problem to retrieve data from a datafile

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 ...
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Problem to retrieve data from a datafile"
Thread Thread Starter Forum Replies Last Post
Retrieve data after reformat and installing XP B.Prajapati Operating Systems 4 30-09-2011 05:52 PM
Retrieve selected Data From a Table samualres Software Development 5 30-01-2010 10:04 AM
Way to retrieve the data from WD external hdd Sujit15 Hardware Peripherals 4 24-03-2009 06:05 PM
How to access and retrieve data from HDD ? Caden Hardware Peripherals 3 21-02-2009 06:22 PM
EasyPHP : retrieve data Yaropolk Software Development 2 22-11-2008 03:44 PM


All times are GMT +5.5. The time now is 05:06 AM.