Re: Drop Tablespace

From: Tanel Poder <tanel.poder.003_at_mail.ee>
Date: Tue, 01 Jul 2003 14:19:56 -0700
Message-ID: <F001.005BD9BE.20030701140508@fatcity.com>

Yep, if you type drop tablespace <name> including contents and datafiles; the datafiles are removed.

Also, if you are using OMF (oracle managed files) by some odd reason then dropping tablespace also deletes the datafiles, even if you don't specify "including contents and datafiles" option.

A small example below..
Tanel.

SQL> alter session set db_create_file_dest="G:\";

Session altered.

SQL> create tablespace g;

Tablespace created.

SQL> select name from v$datafile;

NAME

C:\ORACLE\ORA92\ORCL\SYSTEM01.DBF
C:\ORACLE\ORA92\ORCL\UNDOTBS01.DBF
C:\ORACLE\ORA92\ORCL\CWMLITE01.DBF
C:\ORACLE\ORA92\ORCL\DRSYS01.DBF
C:\ORACLE\ORA92\ORCL\EXAMPLE01.DBF
C:\ORACLE\ORA92\ORCL\INDX01.DBF
C:\ORACLE\ORA92\ORCL\ODM01.DBF
C:\ORACLE\ORA92\ORCL\TOOLS01.DBF
C:\ORACLE\ORA92\ORCL\USERS01.DBF
C:\ORACLE\ORA92\ORCL\XDB01.DBF
C:\ORACLE\ORA92\ORCL\TS2K01.DBF

NAME

G:\O1_MF_G_ZJ3XXJ00_.DBF 12 rows selected.

SQL> ho dir G:\O1_MF_G_ZJ3XXJ00_.DBF
Volume in drive G has no label.
Volume Serial Number is 7042-6228

Directory of G:\

02.07.2003  00:05          104 865 792 O1_MF_G_ZJ3XXJ00_.DBF
               1 File(s)    104 865 792 bytes
               0 Dir(s)     298 082 304 bytes free

SQL> drop tablespace g;

Tablespace dropped.

SQL> ho dir G:\O1_MF_G_ZJ3XXJ00_.DBF
Volume in drive G has no label.
Volume Serial Number is 7042-6228

Directory of G:\

File Not Found

SQL>

Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, July 02, 2003 12:28 AM

> There's a new feature in 9i that can remove the os file also
>
> Ramon E. Estevez
> [EMAIL PROTECTED]
> 809-535-8994
>
>
>
> -----Original Message-----
> Jose Luis Delgado
> Sent: Tuesday, July 01, 2003 5:13 PM
> To: Multiple recipients of list ORACLE-L
>
>
> It dissappered from the data dict...
>
> but it should exist at os level...
>
> you need to rm it...
>
> check it...
>
> HTH
> JL
>
> --- Hamid Alavi <[EMAIL PROTECTED]> wrote:
> > Hi List,
> >
> > Today I have dropped a big tablespace(4 Gig) as soon
> > as I dropped it the
> > data file also disappeared from the list of
> > datafiles, as I know the
> > datafile must be removed manually Any idea?
> >
> > I am using 9.0.1 on solaris 2.8
> >
> > Thanks,
> >
> > Hamid Alavi
> >
> > Office : 818-737-0526
> > Cell phone : 818-416-5095
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.net
> > --
> > Author: Hamid Alavi
> > INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services -- 858-538-5051
> > http://www.fatcity.com
> > San Diego, California -- Mailing list and web
> > hosting services
> >
> ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
>
>
> __________________________________
> Do you Yahoo!?
> SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jose Luis Delgado
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
> message BODY, include a line containing: UNSUB ORACLE-L (or the name of
> mailing list you want to be removed from). You may also send the HELP
> command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Ramon E. Estevez
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Jul 01 2003 - 16:19:56 CDT