CODEWITHSUNDEEP
oracle-databaseschemaoracle11g
philrabin
philrabin

Reputation: 809

Drop Schema in Oracle: SQL Error: No more data to read from socket

I'm trying to drop a schema in oracle 11g on our dev environment and I get back SQL Error: No more data to read from socket. There is no load on the schema as it's just a dev db. It's a small db without anything crazy going on. I see this error all the time. Restarting the instance sometimes resolves the problem. I can't seem to find any information that would point to a solution. Thanks!

Upvotes: 1

Views: 3441

Answers (3)

student0495
student0495

Reputation: 181

Also had this problem, got fixed by setting "PLScope identifiers:" to "None" in Tools->Preferences ->Database->PL/SQL Compiler

Upvotes: 0

philrabin
philrabin

Reputation: 809

A dba at my company gave me this one. It's

CREATE OR REPLACE  PROCEDURE "SYS"."DROP_SCHEMA_FAST" (pSchema IN
    VARCHAR2)
IS 
    cnt NUMBER(5) := 0;
  sql1 varchar2(4000);
  x PLS_INTEGER;
--disable constraints:
  cursor cur1 is select 'alter table ' || OWNER ||'.'||table_name||' disable constraint '||constraint_name sql2
          from all_constraints where owner=pSchema and status='ENABLED'
          and table_name not like 'BIN$%' and constraint_name not like 'SYS_%' and constraint_name not like '%PK%';

  cursor cur2 is select 'alter table ' || OWNER ||'.'||table_name||' disable constraint '||constraint_name sql2 
          from all_constraints where owner=pSchema and status='ENABLED'
          and table_name not like 'BIN$%' and constraint_name not like 'SYS_%';  

--truncate all tables:
cursor cur3 is select 'truncate table ' || OWNER ||'.'||table_name sql2 from all_tables where owner=pSchema 
and table_name not like 'BIN$%';

BEGIN
    SELECT COUNT(*) INTO cnt FROM dba_users WHERE UPPER(username) = UPPER(pSchema);
    IF (cnt <= 0) THEN
        RETURN;
    END IF;
    sql1 := 'ALTER USER ' || UPPER(pSchema) || ' ACCOUNT LOCK';
    EXECUTE IMMEDIATE sql1;

--disable constraints:  
FOR ao_rec IN cur1 LOOP
        EXECUTE IMMEDIATE ao_rec.sql2;
END LOOP;

FOR ao_rec IN cur2 LOOP
        EXECUTE IMMEDIATE ao_rec.sql2;
END LOOP;
--truncate all tables:
FOR ao_rec IN cur3 LOOP
        EXECUTE IMMEDIATE ao_rec.sql2;
END LOOP;

--drop schema:
sql1 := 'DROP USER ' || UPPER(pSchema) || ' CASCADE';
    EXECUTE IMMEDIATE sql1;
exception when others then null; 
END;

Upvotes: 1

John Doyle
John Doyle

Reputation: 7793

I understand that this message often arises due to a bug. Also, when it appears an entry in your alert log and/or a trace file will contain more detail on what the error might actually be. To find your trace file for the session run:

select    U_DUMP.value
       || '/'
       || DB_NAME.value
       || '_ora_'
       || V$PROCESS.SPID
       || nvl2(V$PROCESS.TRACEID, '_' || V$PROCESS.TRACEID, null)
       || '.trc'
         "Trace File"
  from V$PARAMETER U_DUMP
       cross join V$PARAMETER DB_NAME
       cross join V$PROCESS
       join V$SESSION
         on V$PROCESS.ADDR = V$SESSION.PADDR
 where U_DUMP.NAME = 'user_dump_dest'
       and DB_NAME.NAME = 'db_name'                   
       and v$session.audsid=sys_context('userenv','sessionid');

Upvotes: 1

Related Questions