Delete undo tablespace and create undotablespace

In oracle how to delete the undo tablespace. Whenever I am trying to delete it I am getting the following message

SQL> drop tablespace UNDOTBS1 including contents;

drop tablespace UNDOTBS1 including contents

*

ERROR at line 1:

ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

Below is also is solution if you have delete the undo datafile and after that getting following message on every start up

SQL> startup

ORACLE instance started.

Total System Global Area 544030720 bytes

Fixed Size 1337776 bytes

Variable Size 406849104 bytes

Database Buffers 130023424 bytes

Redo Buffers 5820416 bytes

Database mounted.

ORA-00376: file 13 cannot be read at this time

ORA-01110: data file 13: '/u01/app/oracle/oradata/orcl/undotbs02.dbf'

Following is how you can do this:

SQL> alter system set undo_tablespace = '';

System altered.

SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

Tablespace dropped.

SQL> create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' size 50M reuse autoextend on maxsize 500M;

Tablespace created.

Show all autoextendable tablespaces

In Oracle how to find all the autoextendable tablespaces:

set pages 10000

col tablespace_name format a20

col file_name format a45

col file_mb format 999999

col data_mb format 999999

col free_mb format 999999

col max_mb format 999999

select files.tablespace_name, files.file_name, ceil(files.mb) file_mb, ceil(files.mb - nvl(free.freemb,0)) data_mb,

nvl(free.freemb,0) free_mb, files.maxmb max_mb, autoextensible

from

(select file_id, file_name, tablespace_name, bytes/1024/1024 mb, autoextensible,

decode(autoextensible,'NO',bytes/1024/1024,decode(sign(maxbytes-bytes),-1,bytes/1024/1024,maxbytes/1024/1024)) maxmb

from dba_data_files) files,

(select file_id, sum(bytes)/1024/1024 freemb

from dba_free_space

group by file_id) free

where files.file_id = free.file_id (+)

and files.autoextensible='YES'

order by 1,2;