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.

  • I am having trouble with; Verify the temporary table space for user "SCOTT" by performing query on "user_users" table. Determine details of the temporary tablespace by performing query on "user_tablespaces". also. Commented Feb 13, 2015 at 19:32
  • so using the above answer I found the size of the TEMP file. Now I want to create another temporary file called TEMP2 that is the same size. what I entered was; create bigfile tablespace TEMP2 tempfile '/u01/oradata/TEMP2.dbf' size 2048m autoextend off extent management local uniform size 512k; and was told I had insufficient privileges. So now I am working on figuring out the priveleges. gheeze Commented Feb 14, 2015 at 1:29
  • Login as sysdba. "connect / as sysdba" in SQLPlus. If you're using SQL Developer, login as SYS and check the "as SYSDBA" box
    – Philᵀᴹ
    Commented Feb 14, 2015 at 10:09
  • yeah for some reason it wont let me log in as anything other than sysman or scott. So since I am using virtual box I am gonna dump it and reload a new one and try to figure it out. I submitted the assignment already. Commented Feb 14, 2015 at 21:31
  • Thanks @Phil, I didnt understand how to log in "connect / as sysdba" at the time. I did figure it out and appreciate your answer. Commented Feb 16, 2015 at 15:44

2 Answers 2


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.


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;
  • thanks, I have finally figured out the deal with my instructor and this class. I have learned so much but feel like this forum is not the place to pose questions and learn the answers or figure things out. Too much attitude or distaste for newbs I think. Appreciate your response though. I am not sure how to mark this question as answered though. Commented Mar 3, 2015 at 5:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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