reuse clause in create tablespace command

[adewri]

Hi All,

I have a doubt, what is the purpose of the reuse in create tablespace command and when is it useful.

create tablespace test datafile '/u1/oradata/GMI/test01.dbf' reuse;

regards
Amar

[jovery]

If you already have precreated OS datafiles it will use those, it allows you to pre-create large OS files (overnight for example)

Regards

[KenEwald]

It's a handy option when you get half way through your database creation and something fails (ran out of space, syntax issue, etc.) and you need to re-run it... the previous datafiles have been created, so you just want to "reuse" them.

In some environments datafiles take a long time to create. For example 8.0.x unicode, 500 mb datafile, on HP takes 20 minutes. Reusing an existing datafile is very handy in this scenario.

[adewri]

Thanks

but where do i get the existing datafile from....

can this be from any database... what about the timestamp on the datafile... ??

regards
Amar

[Sameer]

If something goes wrong while adding datafile to tablespace, and if new datafile got created on OS, then you can re-issue the create or alter tablespace using "REUSE" clause so that Oracle will use existing datafile.. If the file doesn't exists then you can't use this option. It must be from same database, you can't share it from OS-to-OS or DB-to-DB.

Sameer

[KenEwald]

Timestamp doesn't matter.

[adewri]

:-)
Thanks to all of u.

Regards
Amar