How to Create TEMPORARY tablespace and drop existing temporary tablespace in oracle 11g/12c

1. Create Temporary Tablespace Temp

create temporary tablespace temp2 tempfile '/mnt/mnt04/oradata/temp01.dbf'size 2000M; 

2. Move Default Database temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

3. Make sure No sessions are using your Old Temp (TEMP) tablespace

   a.  Find Session Number from V$SORT_USAGE: 
       SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE; 

SQL> SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;

USERNAME                       SESSION_NUM        SESSION_ADDR
----------------------------------------------------------------------------- 
SYS                                       45684           0000000CE2EA7CC8

   b.  Find Session ID from V$SESSION:

       If the resultset contains any tows then your next step will be to find the SID from the V$SESSION view. You can find session id by using SESSION_NUM or SESSION_ADDR from previous resultset.

       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;
       OR
       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR; 
OR
SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;

c.  Kill Session:

Provide above inputs to following query, and kill session’s.
SQL> alter system kill session 'SID_NUMBER, SERIAL#NUMBER';
For example:
SQL> alter system kill session '633,45684';

4. Drop TEMP tablespace

drop tablespace TEMP including contents and datafiles;

5. Recreate Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE  '/mnt/mnt04/oradata/temp_01.dbf' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;

6. Move Tablespace Temp, back to new temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

7. Drop temporary for tablespace temp

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

No need to do shutdown when drop temp tablespace and the recreate it. If something happen with temp tablespaces e.g. : crash, corrupt, etc. Oracle database will ignore the error, but DML (insert,update,delete) and SELECT Query will suffer.

Cleaning Oracle SYSAUX Tablespace Usage - SYSAUX tablespace is 97.51%

Normally the SYSAUX tablespace is more or less stable so it would be smart to check what is eating the space in there. Connected as a DBA user, run the script ${ORACLE_HOME}/rdbms/admin/utlsyxsz to get the current usage of the SYSAUX tablespace and see how it will grow when you change certain parameters for which you are asked to enter values.

 
OR

select 
   * 
from 
   (select 
      owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024) meg 
   from 
      dba_segments 
      where tablespace_name = 'SYSAUX' 
   order by 
      blocks desc);

In my case, below 1  occupying most of the space :-

1. SM/AWRSM/AWR — It refers to Automatic Workload Repository.Data in this section is retained for a certain amount of time (default 8 days). Setting can be checked through DBA_HIST_WR_CONTROL.

Retrieve the oldest and latest AWR snapshot

SELECTsnap_id, begin_interval_time, end_interval_timeFROMSYS.WRM$_SNAPSHOTWHEREsnap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT)UNIONSELECTsnap_id, begin_interval_time, end_interval_timeFROMSYS.WRM$_SNAPSHOTWHEREsnap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT)/

Now use the dbms_workload_repository package to remove the AWR snapshots.

BEGINdbms_workload_repository.drop_snapshot_range(low_snap_id => 7521, high_snap_id=>7888);END;/

Speed up ‘removal’ of old AWR reports

@#$%^&*()_ removing the entries takes ages and fails on undo errors … Metalink note Doc ID: 852028.1 states that I can safely remove the AWR metadata tables and recreate them.If none of the above suits as everything is set proper then consider clean up and rebuild AWR repository to clear all the space.

SQL> connect / as sysdba 
SQL> @?/rdbms/admin/catnoawr.sql 
SQL> @?/rdbms/admin/catawrtb.sql

Reference :-WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (Doc ID 387914.1)Suggestions if Your SYSAUX Tablespace Grows Rapidly or Too Large (Doc ID 1292724.1)

ORA-28040: No matching authentication protocol

When connecting to 10g ,11g or 12c databases using a  JDBC thin driver , fails with following errors:

- The Network Adapter could not establish the connection

- ORA-28040: No matching authentication protocol

This happens because you are using a non compatible version of JDBC driver.To resolve this issue, make sure that you are using the latest version of Oracle JDBC driver, if not use JDBC 12c or higher versions. 

Current Interoperability Support Situation

The matrix below summarizes client and server combinations that are supported for the most commonly used product versions. 

ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

KUP-11024: This external table can only be accessed from within a Data Pump job.

Alert Log details: 

Trace file details:

Cause:

The primary cause of this issue is that an external table existed at some point in time but does not now. However, the database still believes the table exists since the dictionary information about the object has not been modified to reflect the change. When DBMS_STATS is run against the table in question, it makes a call out to the external table which fails because the object is not there.

There are many reasons that an external table may not exist including:

• - Temporary Datapump external tables have not been cleaned up properly. The dictionary information should have been dropped when the DataPump jobs completed.
• - An OS file for an External table has been removed without clearing up the corresponding data dictionary information. 
• Solution:
• Essentially the solution to this issue is to clean up the orphaned dictionary entries.
• SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' ORDER BY 1,2;
• or 
• select OWNER,OBJECT_NAME,OBJECT_TYPE, status,
  to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created
  ,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time
  from dba_objects
  where object_name like 'ET$%'
  /
  
  select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
  from dba_external_tables
  order by 1,2
  /
• 
  Drop external temporary table
• 
  Ref. Doc: ID 1274653.1
•