Tablespace Creation and Reassignment

Question

It has been determined that user "SCOTT" should use a new temporary tablespace called TEMP2 size equivalent to TEMP tablespace (refer to Notes below for determining the size of TEMP). Create a script that performs the following

Create one Bigfile tablespace for TEMP2. size equal to the size of TEMP Change the default temporary tablespace for "SCOTT" to TEMP2

This is what I have come up with so far, I have used a combination of the Oracle 11g DBA Handbook, also the Oracle website knowlege base at http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN011 for example.

I cannot find how to determine the size of "TEMP". I have been able to locate the temp tablespace though. I just havent figured out the query to find out the size.

what I have so far is:

create bigfile tablespace TEMP2
    datafile '/u06/oradata/TEMP2.dbf' size ?g;

alter user SCOTT temporary tablespace TEMP2;

any help or insight would be appreciated, the script is for 11g and SQL Plus. Thanks, I am a newb.

Answer 1

To get the size of TEMP, in bytes (obviously divide by a few 1024s to get Gigabytes):

select sum(bytes)
from dba_temp_files
where tablespace_name = 'TEMP';

Doc link for the view here.

Answer 2

Below will show temp file size in megabytes

select sum(bytes)/1024/1024 as sizemb,tablespace_name 
from dba_temp_files order by tablespace_name;