TEMP Tablespace Removal

Question

Oracle Version: 12.1.0.2

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?

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;