ORA-02303: cannot drop or replace a type with type or table dependents

You can't replace type sources if it has dependent object(s).
for example another type is produced from this type.

If you want to replace new source you must drop dependent objects before.
and create again that after replaced main type source.

ORA-02303: cannot drop or replace a type with type or table dependents

for example

drop TYPE appuser.table_logtable;

CREATE OR REPLACE 
TYPE bankdb.type_hpl_heslog AS OBJECT
        (col1                                           DATE,
         col2                                           VARCHAR2(14)         
)
/

CREATE OR REPLACE 
TYPE appuser.table_logtable AS TABLE OF type_logtable;

Also you can find dependent object with below sql

select * from dba_dependencies
where name = 'TYPE_LOGTABLE' and owner='APPUSER';

Temporary Tablespace Usage

v$sort_usage dynamic performance views deprecated in 9.2

 http://docs.oracle.com/cd/B19306_01/server.102/b14238/changes.htm#i639263

can use V$TEMPSEG_USAGE to obtain temp segment usage by session.

col machine format a20
col osuser format a20

select s.sid,s.username, s.osuser, s.machine, sum(u.extents) from V$TEMPSEG_USAGE u, v$session s
where s.saddr=u.session_addr
group by sid,s.username, s.osuser, s.machine
order by 5 desc;


       SID USERNAME                       OSUSER               MACHINE              SUM(U.EXTENTS)
---------- ------------------------------ -------------------- -------------------- --------------
      1718 APPUSER                        orauser2             node1                          7561
      2170 RAMAZAN                        rozturk              DOMAIN\PCRAMAZAN                120
      3358 OTHERUSER                      SYSTEM               node3                            11

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'

You have enough undo tablespace but you can't use free area althoug it is free.
or
there is no active transaction in your database but undo tablespace is full?


--firstly select free space by this select

SQL> select sum(bytes) from dba_free_space where tablespace_name='UNDOTBS';

SUM(BYTES)
----------
1080098816

--select extent total size by grouping status

SQL> SELECT DISTINCT STATUS, SUM (BYTES), COUNT (*)
  FROM DBA_UNDO_EXTENTS
GROUP BY STATUS;

STATUS    SUM(BYTES)         COUNT(*)                               
--------- ------------------ ---------------- 
ACTIVE              18481152               17 
EXPIRED             14417920              220 
UNEXPIRED         2064187392             8695 

SQL> show parameter undo

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- -----------
undo_management                      string                           AUTO
undo_retention                       integer                          3600
undo_tablespace                      string                           UNDOTBS

you can set "_undo_autotune" and undo_retention parameters  to trigger clearing unexpired extents which no one in use.

ALTER SYSTEM SET "_undo_autotune"=FALSE;
ALTER SYSTEM SET undo_retention=3600;

Kill inactive sessions

If you want to kill all inactive session in database which has been wait 10 minutes.
You can use below statements.

SQL> select count(*) from v$session;

  COUNT(*)
----------
      503

SQL> select 'alter system kill session '''||sid||','||serial#||''' immediate;' cmd
from v$session where last_call_et>600 and status='INACTIVE';

CMD
----------------------------------------------------------------------
alter system kill session '3803,3' immediate;
alter system kill session '3806,5' immediate;
alter system kill session '3811,3' immediate;
alter system kill session '3813,10' immediate;
alter system kill session '3814,20' immediate;
alter system kill session '3816,14' immediate;
alter system kill session '3823,135' immediate;
alter system kill session '3824,25' immediate;
.....