Re: Can I safely get rid of the EXAMPLE tablespace?

From: ohaya <ohaya_at_cox.net>
Date: Wed, 11 Jan 2006 17:25:32 -0500
Message-ID: <43C585DC.9387A4A2@cox.net>

gazzag wrote:
>
> >> I used DBCA to create a "general" database, and I noticed a large
> >> (~150MB) tablespace named "EXAMPLE".
>
> >> Can I safely get rid of that tablespace (to get the 150MB back)? If so,
> >> what's the easiest way to accomplish that?
>
> Oracle version?

It's 9i/9.2.0.1.0

> Anyway, if you're sure you don't need it, of course you can drop it.
> I'd probably double-check with the following couple of queries:
>
> select username
> from dba_users
> where default_tablespace='EXAMPLE';

It gave me:

USERNAME

---

HR
OE
PM
SH
QS_ADM
QS

QS_WS
QS_ES
QS_OS
QS_CBADM
QS_CB

USERNAME

---

QS_CS

12 rows selected.

> and
>
> select owner, segment_name, segment_type
> from dba_segments
> where tablespace_name='EXAMPLE';

That gave me a ton of output, e.g.:

SEGMENT_TYPE

---

INDEX PARTITION SH
COSTS_TIME_BIX
INDEX PARTITION SH

OWNER

---

SEGMENT_NAME

---

SEGMENT_TYPE

---

COSTS_TIME_BIX
INDEX PARTITION SH
COSTS_TIME_BIX
INDEX PARTITION OWNER

---

SEGMENT_NAME

---

SEGMENT_TYPE

---

SH
COSTS_TIME_BIX
INDEX PARTITION SH
COSTS_TIME_BIX
INDEX PARTITION OWNER

---

>
> If you're then sure you want to drop it:
>
> drop tablespace EXAMPLE;
>
> Depending on the version of Oracle, "and datafiles" can be added to the
> statement to delete the associated O/S file as well.

Given the version I posted above, can I do the "and datafiles"? I want to eliminate the OS file space if I'm not needing it, to free up my drive.

This is on Win2K...

Jim Received on Wed Jan 11 2006 - 16:25:32 CST