Create temporary tablespace using a query to determine size in Oracle

Question

I'm trying to create a temporary tablespace that would be half the size the 'TEMP' tablespace.

So something like :

create temporary tablespace Temptest
TempFile 'somepath'
size ?M;

where ? =

select bytes/2/1024/1024 
  from dba_temp_files 
 where tablespace_name='TEMP';

Answer 1

You could write a PL/SQL block that used dynamic SQL. Something like

DECLARE
  l_current_temp_size_mb NUMBER;
  l_sql_stmt             VARCHAR2(1000);
BEGIN
  SELECT SUM(bytes)/1024/1024
    INTO l_current_temp_size
    FROM dba_temp_files
   WHERE tablespace_name = 'TEMP';

  l_sql_stmt :=
    'CREATE TEMPORARY TABLESPACE tempTest TEMPFILE <<somepath>> size ' || 
       to_char( l_current_temp_size_mb / 2 ) ||
       ' M';
  -- Print out the SQL statement or write it to a table so that if there is an error, 
  -- you know what SQL statement was generated and can debug it.
  dbms_output.put_line( l_sql_stmt ); 
  EXECUTE IMMEDIATE l_sql_stmt;
END;