Temp Usage

Temp Usage by Temp Tablespace:-

SELECT A.tablespace_name TABLESPACE,
  D.mb_total,
  SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
  D.mb_total         - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
  (SELECT B.name,
    C.block_size,
    SUM (C.bytes) / 1024 / 1024 mb_total
  FROM v$tablespace B,
    v$tempfile C
  WHERE B.ts#= C.ts#
  GROUP BY B.name,
    C.block_size
  ) D
WHERE A.tablespace_name = D.name
GROUP BY A.tablespace_name,
  D.mb_total;

Temp Usage by Users:-


SELECT b.tablespace,
       ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
       a.inst_id as Instance,
       a.sid||','||a.serial# AS sid_serial,
       NVL(a.username, '(oracle)') AS username,
       a.program,
       a.status,
       a.sql_id
FROM   gv$session a,
       gv$sort_usage b,
       gv$parameter p
WHERE  p.name  = 'db_block_size'
AND    a.saddr = b.session_addr
AND    a.inst_id=b.inst_id
AND    a.inst_id=p.inst_id
ORDER BY b.tablespace, b.blocks;

Hope this Helps..

ORA-39346: data loss in character set conversion for object DATABASE_EXPORT/SCHEMA/TABLE/COMMENT

Recently, we have seen this error while importing application schemas from one environment to another(which are of same Platforms). the import log had this below error.
we are on 12.1.0.2.4(both environments on same version). we see below error because we have soem undefined or some characters that not able to convert while importing.. check and see if you see any text with different characters in comments that look ODD.

ORA-39346: data loss in character set conversion for object DATABASE_EXPORT/SCHEMA/TABLE/COMMENT

Oracle has a patch for Bug# 21342624 ...

Note :- download the one that you need for ..because oracle has this patch for different versions..

once you aplly this patch.. 21342624. do the import again(only comments) and see what all columns with tables are reported. and try to alter those comments...

Hope this Helps.......