Re: Add datafile to a tablespace while EXP utility is running?

Jean-Marc Pietrzyk wrote:
> Configuration: WinNT SP6, Oracle 8.1.7 EE.
>
> Issue: Would there be any undesirable side effects if I execute a few 'ALTER
> TABLESPACE ADD DATAFILE' to allocate additional space to some partitions
> while a full database backup (using EXP utility) is going on?
> For our database the EXP usually takes 8-10 hours to finish. And I would
> like to allocate additional datafiles because we need to fix some "ORA-1683:
> unable to extend index <stringA> partition <stringB> by 128 in tablespace
> <stringC> that we were getting earlier
>
> Any help would be greatly appreciated.
>
> Thanks

I hope export isn't your only backup strategy?! Export is not a particularly good backup utility, as recovery inevitably means 'restore from the last export', and there is no possibility of rolling that restore forward. So you are more or less guaranteed to lose data.

As an adjunct to a physical backup of datafiles, it has its uses, for sure. But on its own, it's not a particularly resilient method.

That said, you should be OK to add in datafiles to a tablespace in mid-export. Export is merely creating a file which has within it a bunch of 'create table....', 'create index....' and so on statements; followed by a bunch of 'insert into ....' statements to populate the segments with data. It is, in other words, entirely unaware of the physical construction or layout of your database, and so changing that in mid-flight should not be an issue.

I notice that in another post you say that you don't do any inserts during the export. That's really irrelevant, because (by default) as export arrives at each segment, it takes a snapshot of the table at the time it arrives at it. So if export starts working on EMP at 10.00am, and DEPT at 10.03am, and SALES at 10.06am, your export file will contain EMP data as it was at 10.00am, DEPT data as it was at 10.03am and so on. The fact that the EMP table took 3 minutes to process, and during those 3 minutes another 500 records were inserted into EMP, makes no difference: those records, freshly inserted and committed, will NOT be in the export file.

If you set CONSISTENT=Y, by the way, then the export file will only contain data as it was at the time that the entire export started. So, in that case, only the data that already existed at 10.00am would make it into the dump file.

What I'm getting at, therefore, is WHY the addition of extra datafiles during the export won't stuff up your export. You say you are(or want to be)adding datafiles because your users are getting out of space errors. Presumably, this is because they are doing updates (you ruled out inserts) which are causing records to grow and hence acquire extra space. Well, as I've just explained, export sees the data in a table as it was at the time that it arrived at that table. So all those updates and so on will be invisible to it.

You of course would have problems if export COULD see those updates, because then export would be storing data which presumably doesn't fit in the originally-configured tablespace, and you'd rather (I'm sure) that export had within it a 'create tablespace' statement which provided sufficient room for the table on import -which it won't, since the create tablespace statements are some of the first things it includes in the export file.

In other words, the order of events if significant. If, whilst exporting EMP, you were to add a datafile to the tablespace where DEPT lives, so that updates to DEPT were to procede (and be committed) without error; and assuming you've done the default CONSISTENT=N; then when the export arrives at the DEPT table, it's about to export a table requiring the extra space, and can see the updated records that *need* that space.

But if in the middle of the EMP export, your users updating EMP got error messages, so you made EMP's tablespace bigger, it wouldn't be an issue: export can't 'see' those updates, and therefore doesn't 'need' the extra space.

If you do your exports with CONSISTENT=Y, of course, then no updates whatsoever, for any table, are visible to export, and therefore none of the extra space would be required by export for a subsequent successful import.

The worst that is going to happen is that on import, you might get 'out of space' errors. But it wouldn't be a total disaster: you would simply manually re-create the extra datafiles in the database, and re-run import (probably with IGNORE=Y) and the rows would then be successfully loaded into the table.

With all that said, however, remember that export is not a robust or sensible backup strategy on its own. I also question the wisdom of letting loose a massive export whilst users are still working on the database: their performance is undoubtedly being affected. And I also question why you are running out of space in the first place, since even marginal DBAing requires that you should really allocate space *proactively*.

So there's a whole lot going not very right in your setup, and being able to add datafiles in the middle of an export is the least of your worries, I think!

Regards
HJR Received on Tue Jan 28 2003 - 00:42:01 CST