0

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)?

1

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
0

you don't need to 2TB temp space , the temp space that used by database while sort or query 3 tables now is enough .

  • This is more suited to being a comment - you could flag it for attention by the moderators for changing it to one? – Vérace Mar 27 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.