Moving tables to new tablespace needs temp space or any kind of addictional space? (Oracle 11g)

alter-tabledisk-spaceoracleoracle-11g-r2tablespaces

I've been trying to search for this info on oracle documents but they just don't talk about it.

I need to move about 2TB of different tables from 3 different tablespaces to a new tablespace (encrypted one). Do I need to have temp space to do it (2TB)?

Best Answer

Moving a table does not use the temporary tablespace.

SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
parallel_servers_target              integer     64
pga_aggregate_target                 big integer 256M
sga_target                           big integer 1G

SQL> select bytes/1024/1024 as bytes_mb, tablespace_name from user_segments where segment_name = 'SALES2';

  BYTES_MB TABLESPACE_NAME
---------- ------------------------------
      2240 EXAMPLE2

SQL> select file_id from dba_temp_files;

   FILE_ID
----------
         1

SQL> alter database tempfile 1 drop;

Database altered.

SQL> select file_id from dba_temp_files;

no rows selected

SQL> select sys_context('userenv', 'sid') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
191

SQL> select serial# from v$session where sid = 191;

   SERIAL#
----------
       415

SQL> alter table sales2 move tablespace example3;

Table altered.

SQL> select bytes/1024/1024 as bytes_mb, tablespace_name from user_segments where segment_name = 'SALES2';

  BYTES_MB TABLESPACE_NAME
---------- ------------------------------
      2240 EXAMPLE3

SQL> select max(temp_space_allocated), count(*) from v$active_session_history where session_id = 191 and session_serial# = 415;

MAX(TEMP_SPACE_ALLOCATED)   COUNT(*)
------------------------- ----------
                        0         33
Related Question