Oracle Version:

We have a discrepancy between our PROD and DR TEMP tablespace data files.

  • PROD = 30G + 30G + 30G data files
  • DR = 30G + 30G + 30G + 21G + 2G + 1G + 15G data files

I want both my environments to only have the 30 GB TEMP tablespace data files only (In total 90 GB). I need to remove the additional 21G +2G +1G +15G tablespace data files in the DR environment.

It is critically important for me to know who added the other non standard tablespaces. Do we have a query to find the culprit?

Also: How safe is it to remove the other non-standard TEMP tablespace data files? Is there a safe way or an Oracle defined process to do that?

  • Do you have AUDIT enabled? Personally, the question "can you find the culprit?" (an AUDIT question), should be in a separate post then "how do you remove files from a `TEMPORARY TABLESPACE?". Sep 19, 2018 at 13:19
  • Well, The primary concern is on how to remove the TEMP in an orderly fashion. Something you can do to help? Sep 20, 2018 at 3:23
  • The general concept that I would use is: move everyone to temporary tablespace TEMP2. Sep 20, 2018 at 9:44
  • Everyone? Thats a good idea for standalone non standbys. I agree 100% but are you sure i can attempt that on a standby? Sep 22, 2018 at 9:45

1 Answer 1


The alert log will show what user added a data file to your DR tablespace, but most likely they logged on as SYS / AS SYSDBA instead of a user that has SYSDBA privileges (one more reason to assign roles to users, especially DBAs. The alert log is usually in $ORACLE_BASE/diag/${ORACLE_UNIQ_NAME}/${ORACLE_SID}/trace/alert_${ORACLE_SID} (I may have those ORACLE_UNIQ_NAME and ORACLE_SID backwards).

To clean up the DR TEMP tablespace, just create a new TEMP tablespace TEMP2, alter the database to use the new temporaray tablespace, drop the old TEMP tablespace, then rename the new temp tablespace TEMP2 to TEMP:

create temporary tablespace temp2
  tempfile '/some_dir_path/temp2_01.dbf' size 30g,
  tempfile '/some_dir_path/temp2_02.dbf' size 30g,
  tempfile '/some_dir_path/temp2_03.dbf' size 30g

alter database default temporary tablespace temp2;

drop tablespace temp including contents and datafiles;

alter tablespace temp2 rename to temp;

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.