Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to create a "Disposable/non critical" tablespace ?

Re: How to create a "Disposable/non critical" tablespace ?

From: <Kenneth>
Date: Wed, 29 Sep 2004 15:49:24 GMT
Message-ID: <415ad870.850687@news.inet.tele.dk>


On Wed, 29 Sep 2004 10:38:34 -0400, "G Dahler" <gordon.dalher_at_spamex.com> wrote:

>Hello,
>
>Running 8.1.7.4 on Solaris 2.6
>
>For testing purpose of a particular application, I need to copy the data
>from an application on my production database to a development database (A
>schema). I plan to copy the data to one or two _specific_ tablespaces in my
>development database.
>
>Unfortunately, this data is quite BIG. It is also disposable, I will have to
>refresh it from time to time.
>
>The development database is cold backuped (O/S backups) every night and
>contains other stuff as well. I don't want to create another database just
>for the purpose of testing this app.
>
>Backuping this "disposable" data is a waste of time and a waste of tape. I
>we ever lose it, we can easily refresh it from the production database.
>
>I was wondering what would happen if for some reason, I have to restore a
>cold backup of the development database, and the "disposable" tablespaces
>datafiles are missing from the backup ?
>
>I presume the database will not start up after the restore.
>
>Is there a way to tell oracle: just ignore the missing datafiles/tablespace
>and start without it ?
>
>In essence: Is it possible to DROP a tablespace that is missing a datafile ?
>
>Thanks for your comments/suggestions

Yep. Restore the datafiles minus the ones belonging to the tablespace you don't want/need. When trying to open the database, it will complain about the missing datafiles. Then issue :

SQL> alter database datafile 'not_wanted_001.dbf' offline drop; SQL> alter database datafile 'not_wanted_002.dbf' offline drop; ...
...
Etc until you have offline dropped *ALL* the missing files. The database can now be opened :

SQL> Alter database open;

And you can now drop the obsolete tablespace :

SQL> drop tablespace not_wanted;

Received on Wed Sep 29 2004 - 10:49:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US