:: Stracca Blog :: Stefano Stracuzzi TECH blog

Oracle: drop a big TEMP tablespace

In order to drop a TEMPORARY tablespace the always working way is to create a new one, set as default and the after all opened session have finished to use the older one, drop it:

--- Create a New Temporary tablespace
CREATE TEMPORARY TABLESPACE TEMP2
TEMPFILE '/oradata/DBNAME/temp2_01.dbf' SIZE 512M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
/
--- Set as DEFAUL TEMPORARY TABLESPACE
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2
/

--- After some time DROP

  the original one
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES
/

If you want you can recreate the original one and set it as default:

CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE '/oradata/DBNAME/temp_01.dbf' SIZE 512M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
/
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP
/
DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES
/

Oracle: Move tables on an other tablespace

MoveTables.sql

accept uni prompt 'UserName: ' accept ftbsi prompt 'Tablespace Name FROM: ' accept ttbsi prompt 'Tablespace Name TO: ' SET FEEDBACK OFF SET HEAD OFF SET PAGESIZE 10000 SET VERIFY OFF SPOOL %TEMP%\MoveTables(&uni).sql select 'ALTER

 TABLE "' || owner || '"."' || table_name || '" MOVE TABLESPACE ' || upper('&ttbsi') || ' NOLOGGING;' from dba_tables where owner = upper('&uni') and tablespace_name = upper('&ftbsi'); SPOOL OFF SET FEEDBACK 6 SET HEAD ON SET PAGESIZE 24 SET VERIFY ON PAUSE "Press Enter to move tables or CTRL-C to cancel" SPOOL %TEMP%\MoveTables(&uni).err @%TEMP%\MoveTables(&uni).sql SPOOL OFF

Oracle: Move indexes on an other tablespace

MoveIndexes.sql:

accept uni prompt 'UserName: '
accept tbsi prompt 'Tablespace Name where moving indexes: '

SET FEEDBACK OFF
SET HEAD OFF
SET PAGESIZE 10000
SET VERIFY OFF
SPOOL %TEMP%\MoveIndexes(&uni).sql

select 'ALTER INDEX "' || owner || '"."' ||

  index_name || '" REBUILD TABLESPACE ' || upper('&tbsi') || ';' from dba_indexes where owner = upper('&uni') and tablespace_name <> upper('&tbsi');

SPOOL OFF
SET FEEDBACK 6
SET HEAD ON
SET PAGESIZE 24
SET VERIFY ON

PAUSE "Press Enter to move indexes or CTRL-C to cancel"

SPOOL %TEMP%\MoveIndexes(&uni).err
@%TEMP%\MoveIndexes(&uni).sql
SPOOL OFF