Also can be done using dynemic sql
A Reader, March 05, 2003 - 9:57 am UTC
By joining dba_tablespaces and dba_data_files you can easily create a spool file which will dump create statements for tablespaces.
It may take some efforts to write sql but once you have it you can reuse quiet easily.
That's exactly what I was looking for!
Jenny, March 05, 2003 - 12:37 pm UTC
Thank you!!!
REUSE
Thiru, August 24, 2004 - 1:59 pm UTC
What does the word REUSE in creating tablespaces do? Does it mean that if the datafile already exists then overwrite it? And if we dont use the word REUSE and if the datafile exists, does it give error?
Thanks.
August 24, 2004 - 3:33 pm UTC
reuses the existing datafile if one exists.
if the file exists and you didn't reuse, it would bomb, yes.
why isn't reuse faster than a normal create?
Justin, April 08, 2005 - 12:11 am UTC
system@DEV> create tablespace data datafile 'C:\ORADATA\DEV\DATA01.DBF' size 500m;
Tablespace created.
Elapsed: 00:00:34.04
system@DEV> drop tablespace data;
Tablespace dropped.
Elapsed: 00:00:02.00
system@DEV> create tablespace data datafile 'C:\ORADATA\DEV\DATA01.DBF' size 500m reuse;
Tablespace created.
Elapsed: 00:00:42.03
There's nothing else going on on this box during these tests.
Any thoughts?
Thanks much
April 08, 2005 - 6:53 am UTC
reuse just lets Oracle overwrite the existing file.
it still needs to be "initialized", wiped out, set to "new datafile status", written.