Idle Process In Oracle

Most of the time we look at the processes that consume more cpu, more i / o, more buffer gets, etc. But it is also good idea to look for processes that do nothing, especially in large database systems with hundreds or thousands of sessions that are idle. Although they consume many resources, necessary to maintain, manage, monitor them, manage DTD if is active …. In most cases it is healthy they are not. I include below a small script that tells us which carry more than an hour without doing anything. It’s just an example that you can modify to your liking.

 -- This script is for platform UNIX/Linux
SELECT
  S.USERNAME,
  SPID AS "UNIX PID", 
  'ps -ef | grep -v ' || SPID AS "WHAT", 
  'kill -9 ' || SPID AS "KILL FROM OS", 
  'ALTER SYSTEM KILL SESSION '||''''||S.SID||','||S.SERIAL#||''';' "KILL FROM DB",
  TO_CHAR(TRUNC(LAST_CALL_ET/3600,0))||' '||' HOURS '|| TO_CHAR(TRUNC((LAST_CALL_ET - TRUNC(LAST_CALL_ET/3600,0)*3600) / 60,0))|| ' MINS IDLE' AS "TIME IDLE" 
FROM 
  V$SESSION S, 
  V$PROCESS P
WHERE    
  TYPE='USER' AND 
  P.ADDR=S.PADDR AND 
  EVENT <> 'SQL*Net message from client' AND 
  LAST_CALL_ET > 3600   -- more than one hour without activity
ORDER BY LAST_CALL_ET DESC
/

HTH – Antonio NAVARRO

Estimate The Cost Of Create A New Index

Within Oracle We have the DBMS_SPACE package. One function of this package is estimate the cost of create a new indexes on an existing table. See an example;

 SET SERVEROUTPUT ON

DECLARE
  used_bytes NUMBER(38);
  alloc_Bytes NUMBER(38);
BEGIN

DBMS_SPACE.CREATE_INDEX_COST (
   'CREATE INDEX ZETA_INX ON siebel.movimiento(date_movement) ',
   used_bytes,
   alloc_bytes
);

DBMS_OUTPUT.PUT_LINE('Used Bytes (MBs): '       || round (used_bytes/1048576, 2));
DBMS_OUTPUT.PUT_LINE('Allocated Bytes (MBs): '  || round (alloc_Bytes/1048576, 2));

END;
/

HTH – Antonio NAVARRO

How To Remove Oracle Enterprise Manager In 11.2 Manually

This is only one, there are several. Execute the following comands;

 connect / as sysdba
spool remove.log
EXEC DBMS_AQADM.DROP_QUEUE_TABLE(QUEUE_TABLE=>'SYSMAN.MGMT_NOTIFY_QTABLE',FORCE =>TRUE);
SHUTDOWN IMMEDIATE;
STARTUP RESTRICT;
EXEC SYSMAN.EMD_MAINTENANCE.REMOVE_EM_DBMS_JOBS;
EXEC SYSMAN.SETEMUSERCONTEXT('',5);
REVOKE DBA FROM SYSMAN;

DECLARE
  CURSOR C1 IS 
  SELECT 
    OWNER, 
    SYNONYM_NAME NAME
  FROM 
    DBA_SYNONYMS
  WHERE 
    TABLE_OWNER = 'SYSMAN';
BEGIN
  FOR R1 IN C1 LOOP
    IF R1.OWNER = 'PUBLIC' THEN
       EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||R1.NAME;
    ELSE
       EXECUTE IMMEDIATE 'DROP SYNONYM '||R1.OWNER||'.'||R1.NAME;
    END IF;
  END LOOP;
END;
/

DROP USER mgmt_view CASCADE;
DROP ROLE mgmt_user;
DROP USER sysman CASCADE;
ALTER SYSTEM DISABLE RESTRICTED SESSION;
spool off
exit

HTH – Antonio NAVARRO

How To Remove Oracle Spatial In 11.2 Manually

Execute the following comands;

 connect / as sysdba 

spool remove.log

-- Check if any Spatial Indexes --------
SELECT OWNER,INDEX_NAME FROM DBA_INDEXES WHERE ITYP_NAME = 'SPATIAL_INDEX';

-- Check if any Spatial Columns --------
SELECT OWNER, TABLE_NAME, COLUMN_NAME 
FROM DBA_TAB_COLUMNS 
WHERE DATA_TYPE = 'SDO_GEOMETRY' 
AND OWNER != 'MDSYS' 
ORDER BY 1,2,3;

-- If some Spatial index drop ------
DROP INDEX <OWNER>.<INDEXNAME> FORCE; 


-- Drop the user MDSYS ------
DROP USER MDSYS CASCADE;  


-- Drop users link to Spatial cataloge ------
DROP USER MDDATA CASCADE;
DROP USER SPATIAL_CSW_ADMIN_USR CASCADE;
USER SPATIAL_WFS_ADMIN_USR CASCADE;

spool off
exit

HTH – Antonio NAVARRO