Re: table space drop/create

Jay Collins <jcollin_at_exis.net> wrote in message news:<Pine.LNX.4.33.0204111005300.7882-100000_at_tarpon.exis.net>...
> I have user named sqqadmin who owns a tablespace called goldprod. Can
> anyone give me a example (or step by step) how to drop this table space,
> recreate it with the same sizes/files it had before and make sure the
> sqqadmin user still has accesss to it?

users do not OWN a tablespace. They only can have quota on a tablespace
You need to do the following
- get the tablespace definitions from dba_tablespaces and dba_data_files
- or alternatively perform a full database export with rows=n and import the (empty) dumpfile with show=y log=<any filename> full=y. <any filename> will contain the create tablespace statement

- drop tablespace goldprod including contents cascade constraints
- create tablespace goldprod as determined above
- alter user sqqadmin quota <n>|unlimited on goldprod.

That is all.

--
Regards

Sybrand Bakker,
Senior Oracle DBA