_______________________________________________________________________________

Managing Undo:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/undo.htm#ADMIN013
_______________________________________________________________________________

****************************************************************************************
*** DROPPING BIG UNDO TABLESPACES NEEDS TIME (ALSO THE RELEASE OF THE DISK SPACE)!!! ***
*** DROPPING BIG UNDO TABLESPACES NEEDS TIME (ALSO THE RELEASE OF THE DISK SPACE)!!! ***
*** DROPPING BIG UNDO TABLESPACES NEEDS TIME (ALSO THE RELEASE OF THE DISK SPACE)!!! ***
*** DROPPING BIG UNDO TABLESPACES NEEDS TIME (ALSO THE RELEASE OF THE DISK SPACE)!!! ***
*** DROPPING BIG UNDO TABLESPACES NEEDS TIME (ALSO THE RELEASE OF THE DISK SPACE)!!! ***
*** DROPPING BIG UNDO TABLESPACES NEEDS TIME (ALSO THE RELEASE OF THE DISK SPACE)!!! ***
*** DROPPING BIG UNDO TABLESPACES NEEDS TIME (ALSO THE RELEASE OF THE DISK SPACE)!!! ***
*** DROPPING BIG UNDO TABLESPACES NEEDS TIME (ALSO THE RELEASE OF THE DISK SPACE)!!! ***
*** DROPPING BIG UNDO TABLESPACES NEEDS TIME (ALSO THE RELEASE OF THE DISK SPACE)!!! ***
*** DROPPING BIG UNDO TABLESPACES NEEDS TIME (ALSO THE RELEASE OF THE DISK SPACE)!!! ***
****************************************************************************************
_______________________________________________________________________________
_______________________________________________________________________________

QUICKIE
=======

-------------------------------------------------------------
--   SWITCH OFF FLASHBACK OR DROP ALL RESTORE POINTS!!!   ---
--   SWITCH OFF FLASHBACK OR DROP ALL RESTORE POINTS!!!   ---
--   SWITCH OFF FLASHBACK OR DROP ALL RESTORE POINTS!!!   ---
-------------------------------------------------------------

define s_undo_tbs=UNDOTS
define i_tbs_size=2G
define s_undo_tbs_tmp=UNDOTSTMP
define i_tbs_tmp_size=2G

CREATE BIGFILE UNDO TABLESPACE &s_undo_tbs_tmp DATAFILE SIZE 2G AUTOEXTEND ON;
ALTER SYSTEM SET UNDO_TABLESPACE = &s_undo_tbs_tmp SCOPE=MEMORY;

DROP TABLESPACE &s_undo_tbs INCLUDING CONTENTS AND DATAFILES;
CREATE BIGFILE UNDO TABLESPACE &s_undo_tbs DATAFILE SIZE 2G AUTOEXTEND ON;
ALTER SYSTEM SET UNDO_TABLESPACE = &s_undo_tbs SCOPE=MEMORY;

DROP TABLESPACE &s_undo_tbs_tmp INCLUDING CONTENTS AND DATAFILES;
_______________________________________________________________________________
_______________________________________________________________________________

col file_name for a70
select tablespace_name, file_name from dba_data_files where tablespace_name like '%UNDO%';

select 'select dbms_metadata.get_ddl(''TABLESPACE'',''' ||  tablespace_name || ''') from dual;' "STMT" from dba_tablespaces;
_______________________________________________________________________________

set heading off;
set echo off;
set pages 5000;
set lines 200;
set long 999999;

begin
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES', FALSE);
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE', FALSE);
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE', FALSE);
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', true);
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS', true);
end;
/


select dbms_metadata.get_ddl('TABLESPACE','UNDOTS1') from dual;

  CREATE BIGFILE UNDO TABLESPACE "UNDOTS1" DATAFILE
  SIZE 104857600
  AUTOEXTEND ON NEXT 104857600 MAXSIZE 25559040M
  BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
   ALTER DATABASE DATAFILE
  '/app/orasid/oradata/ORASID/datafile/o1_mf_undots1_b1008d5c_.dbf' RESIZE 74867277824
_______________________________________________________________________________

CREATE BIGFILE UNDO TABLESPACE "UNDOTS02" DATAFILE SIZE 2G AUTOEXTEND ON;

ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTS02 SCOPE=BOTH;
_______________________________________________________________________________

set lines 10000
column name format a10

SELECT a.name,b.status 
FROM   v$rollname a,v$rollstat b
WHERE  a.usn = b.usn
AND    a.name IN ( 
		  SELECT segment_name
		  FROM dba_segments 
		  WHERE tablespace_name = 'UNDOTS1'
		 );


column username format a6

SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE  a.usn = b.usn
AND    a.usn = c.xidusn
AND    c.ses_addr = d.saddr
AND    a.name IN ( 
		  SELECT segment_name
		  FROM dba_segments 
		  WHERE tablespace_name = 'UNDOTS1'
		 );

   SID    SERIAL#
 ----- ----------
   193       2775

select process, program from v$session where sid=193 and serial#=2775;

alter system kill session '193,2775' immediate;
_______________________________________________________________________________

DROP TABLESPACE UNDOTS1 INCLUDING CONTENTS AND DATAFILES;

CREATE BIGFILE UNDO TABLESPACE "UNDOTS1" DATAFILE SIZE 2G AUTOEXTEND ON;

ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTS1 SCOPE=BOTH;

DROP TABLESPACE UNDOTS02 INCLUDING CONTENTS AND DATAFILES;
_______________________________________________________________________________