| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: drop user hangs
Yong Huang schrieb:
> Rainer Herbst <rherbst_@_rz.uni-potsdam.de> wrote in message news:<bif59m$i3m$1_at_zeppelin.rz.uni-potsdam.de>...
>
>>>>3. user was created after the instance started, but the user is >>>>definatly not logged in! >>>>--> the drop user statement hangs without any complains or error messages! >>> >>> >>>Hangs for ever? You're right that you can't drop a user if they are logged >>>in, so how are you certain that they are not logged in? When you say it >>>hangs, and produces no error message, have you nevertheless checked the >>>alert log for any possible warning messages. >>> >> >>No errors or warnings in the alert log. V$SESSION_WAIT shows the 'null >>event' for this session.
1 select sid, event, p1, p2, p3 , seconds_in_wait 2* from v$session_wait where sid=14
SID EVENT
P1 P2
P3 SECONDS_IN_WAIT
----------
----------------------------------------------------------------
---------- ---------- ---------- ---------------
14 null event
1650815232 1
0 1500
I haven't seen any changes in p1, p2 and p3.
>
> Unless I missed, did you trace your session doing DROP USER?
>
> Yong Huang
All information about this session retrieved by oracletool.pl:
Session wait information.
Seq# Event Seconds waiting
35 null event 805
Session I/O information.
Block gets Consistent gets Physical reads Block changes Consistent changes
3 2,129 0
4 0
Open cursors.
SQL text
SELECT COUNT(*) FROM ALL_POLICIES V WHERE V.OBJECT_OWNER
SELECT COUNT(*) FROM USER_POLICIES V WHERE V.OBJECT_NAME
Objects being accessed by SID 14.
Object name Object type Owner ALL_POLICIES SYNONYM PUBLIC DBMS_OUTPUT SYNONYM PUBLIC DBMS_RLS CURSOR PUBLIC SYS NON-EXISTENT PUBLIC SYSTEM CURSOR PUBLIC USER_POLICIES SYNONYM PUBLIC ALL_OBJECTS VIEW SYS ALL_POLICIES VIEW SYS ALL_REPCOLUMN CURSOR SYS ALL_REPGENOBJECTS CURSOR SYS ALL_REPOBJECT CURSOR SYS ALL_SNAPSHOTS VIEW SYS ALL_SYNONYMS VIEW SYS ALL_TABLES VIEW SYS ALL_TAB_COLUMNS VIEW SYS ALL_VIEWS VIEW SYS ATTRCOL$ TABLE SYS AUDIT$ TABLE SYS CCOL$ TABLE SYS CDC_CHANGE_TABLES$ TABLE SYS CDC_SUBSCRIBERS$ TABLE SYS CDEF$ TABLE SYS COL$ TABLE SYS DBA_ANALYZE_OBJECTS CURSOR SYS DBA_INDEXES VIEW SYS DBA_IND_PARTITIONS CURSOR SYS DBA_JOBS VIEW SYS DBA_OBJECTS VIEW SYS DBA_POLICIES VIEW SYS DBA_REGISTERED_SNAPSHOTS CURSOR SYS DBA_REGISTRY CURSOR SYS DBA_REPFLAVOR_OBJECTS CURSOR SYS DBA_REPGROUP_PRIVILEGES VIEW SYS DBA_REPOBJECT CURSOR SYS DBA_REPSITES CURSOR SYS DBA_SNAPSHOTS VIEW SYS DBA_SNAPSHOT_LOGS VIEW SYS DBA_SYNONYMS VIEW SYS DBA_TAB_PARTITIONS CURSOR SYS DBA_TYPES CURSOR SYS DBA_USERS VIEW SYS DBMS_ALERT PACKAGE SYS DBMS_ALERT_INFO TABLE SYS DBMS_APPLICATION_INFO PACKAGE SYS DBMS_ASYNCRPC_PUSH CURSOR SYS DBMS_CDCAPI_LIB CURSOR SYS DBMS_CDC_PUBLISH PACKAGE SYS DBMS_CDC_UTILITY PACKAGE SYS DBMS_DDL PACKAGE SYS DBMS_DEFERGEN CURSOR SYS DBMS_DEFERGEN_UTIL CURSOR SYS DBMS_DEFER_IMPORT_INTERNAL PACKAGE SYS DBMS_DEFER_QUERY_UTL CURSOR SYS DBMS_DEFER_REPCAT CURSOR SYS DBMS_DEFER_SYS CURSOR SYS DBMS_DEFER_SYS_PART1 CURSOR SYS DBMS_FLASHBACK CURSOR SYS DBMS_IJOB PACKAGE SYS DBMS_INTERNAL_TRIGGER PACKAGE SYS DBMS_IREFRESH PACKAGE SYS DBMS_ISNAPSHOT PACKAGE SYS DBMS_JOB PACKAGE SYS DBMS_LOB CURSOR SYS DBMS_LOCK PACKAGE SYS DBMS_LOCK_ALLOCATED CURSOR SYS DBMS_LOCK_ID CURSOR SYS DBMS_LOGSTDBY CURSOR SYS DBMS_OUTPUT PACKAGE SYS DBMS_PIPE PACKAGE SYS DBMS_REFRESH CURSOR SYS DBMS_REGISTRY CURSOR SYS DBMS_REGISTRY_SYS PACKAGE SYS DBMS_REPCAT_ADMIN PACKAGE SYS DBMS_REPCAT_CACHE CURSOR SYS DBMS_REPCAT_COMMON_UTL PACKAGE SYS DBMS_REPCAT_DECL PACKAGE SYS DBMS_REPCAT_FLA CURSOR SYS DBMS_REPCAT_FLA_UTL CURSOR SYS DBMS_REPCAT_INTERNAL PACKAGE SYS DBMS_REPCAT_INTERNAL_PKG_LIB CURSOR SYS DBMS_REPCAT_OBJ_UTL CURSOR SYS DBMS_REPCAT_RGT CURSOR SYS DBMS_REPCAT_RGT_CHK CURSOR SYS DBMS_REPCAT_RGT_CUST CURSOR SYS DBMS_REPCAT_RGT_UTL PACKAGE SYS DBMS_REPCAT_SNA_UTL PACKAGE SYS DBMS_REPCAT_SQL_UTL CURSOR SYS DBMS_REPCAT_UTL PACKAGE SYS DBMS_REPUTIL PACKAGE SYS DBMS_REPUTIL2 CURSOR SYS DBMS_RLS CURSOR SYS DBMS_SESSION PACKAGE SYS DBMS_SNAPSHOT CURSOR SYS DBMS_SNAP_INTERNAL CURSOR SYS DBMS_SQL PACKAGE SYS DBMS_STANDARD PACKAGE SYS DBMS_SYSTEM PACKAGE SYS DBMS_SYS_ERROR CURSOR SYS DBMS_SYS_SQL PACKAGE SYS DBMS_UTILITY PACKAGE SYS DEFPROPAGATOR VIEW SYS DEPENDENCY$ TABLE SYS DUAL TABLE SYS DUC$ TABLE SYS GENERATOR$_S CURSOR SYS GET_DEP_DT1 CURSOR SYS GET_DEP_DT2 CURSOR SYS GV$ENABLEDPRIVS VIEW SYS ICOL$ TABLE SYS IND$ TABLE SYS JOB$ TABLE SYS MLOG$ TABLE SYS MLOG_REFCOL$ TABLE SYS OBJ$ TABLE SYS OBJAUTH$ TABLE SYS OPQTYPE$ TABLE SYS PLITBLM PACKAGE SYS PROFILE$ TABLE SYS PROFNAME$ TABLE SYS REGISTRY$ TABLE SYS REG_SNAP$ TABLE SYS REPCAT$_CDEF CURSOR SYS REPCAT_GENERATED VIEW SYS REPCAT_REPOBJECT CURSOR SYS REPCAT_REPOBJECT_BASE CURSOR SYS RGCHILD$ TABLE SYS RGROUP$ TABLE SYS RGROUPSEQ CURSOR SYS RLS$ TABLE SYS SEG$ TABLE SYS SESSION_CONTEXT CURSOR SYS SLOG$ TABLE SYS SNAP$ TABLE SYS SNAP_COLMAP$ TABLE SYS SNAP_OBJCOL$ TABLE SYS SNAP_REFOP$ TABLE SYS SNAP_REFTIME$ TABLE SYS SNAP_SITE$ TABLE SYS STANDARD PACKAGE SYS SYN$ TABLE SYS SYS NON-EXISTENT SYS SYSAUTH$ TABLE SYS SYSTEM CURSOR SYS SYSTEM_PRIVILEGE_MAP TABLE SYS SYS_STUB_FOR_PURITY_ANALYSIS PACKAGE SYS TAB$ TABLE SYS TS$ TABLE SYS TYPE$ TABLE SYS TYPED_VIEW$ TABLE SYS USER$ TABLE SYS USER_ASTATUS_MAP TABLE SYS USER_POLICIES VIEW SYS UTL_RAW CURSOR SYS V$ACTIVE_INSTANCES CURSOR SYS V$ENABLEDPRIVS VIEW SYS V$INSTANCE VIEW SYS V$PARAMETER VIEW SYS VIEW$ TABLE SYS X$KGLOB TABLE SYS X$KZSPR TABLE SYS X$KZSRO TABLE SYS _DBA_REPL_NESTED_TABLE_NAMES VIEW SYS DEF$_AQCALL TABLE SYSTEM DEF$_DESTINATION TABLE SYSTEM DEF$_PROPAGATOR TABLE SYSTEM REPCAT$_AUDIT_ATTRIBUTE TABLE SYSTEM REPCAT$_AUDIT_COLUMN TABLE SYSTEM REPCAT$_COLUMN_GROUP TABLE SYSTEM REPCAT$_CONFLICT TABLE SYSTEM REPCAT$_DDL TABLE SYSTEM REPCAT$_EXTENSION TABLE SYSTEM REPCAT$_FLAVORS TABLE SYSTEM REPCAT$_FLAVOR_OBJECTS TABLE SYSTEM REPCAT$_GENERATED TABLE SYSTEM REPCAT$_GROUPED_COLUMN TABLE SYSTEM REPCAT$_KEY_COLUMNS TABLE SYSTEM REPCAT$_OBJECT_TYPES TABLE SYSTEM REPCAT$_PARAMETER_COLUMN TABLE SYSTEM REPCAT$_PRIORITY CURSOR SYSTEM REPCAT$_PRIORITY_GROUP TABLE SYSTEM REPCAT$_REFRESH_TEMPLATES TABLE SYSTEM REPCAT$_REFRESH_TEMPLATES_S CURSOR SYSTEM REPCAT$_REPCAT TABLE SYSTEM REPCAT$_REPCATLOG TABLE SYSTEM REPCAT$_REPCOLUMN TABLE SYSTEM REPCAT$_REPGROUP_PRIVS TABLE SYSTEM REPCAT$_REPOBJECT TABLE SYSTEM REPCAT$_REPPROP TABLE SYSTEM REPCAT$_REPPROP_KEY CURSOR SYSTEM REPCAT$_REPSCHEMA TABLE SYSTEM REPCAT$_RESOLUTION TABLE SYSTEM REPCAT$_RESOLUTION_METHOD TABLE SYSTEM REPCAT$_RESOLUTION_STATISTICS CURSOR SYSTEM REPCAT$_RESOL_STATS_CONTROL CURSOR SYSTEM REPCAT$_SITES_NEW TABLE SYSTEM REPCAT$_SITE_OBJECTS TABLE SYSTEM REPCAT$_TEMPLATE_OBJECTS TABLE SYSTEM REPCAT$_TEMPLATE_OBJECTS_S CURSOR SYSTEM REPCAT$_TEMPLATE_PARMS TABLE SYSTEM REPCAT$_TEMPLATE_PARMS_S CURSOR SYSTEM REPCAT$_TEMPLATE_SITES TABLE SYSTEM REPCAT$_TEMPLATE_SITES_S CURSOR SYSTEM REPCAT$_TEMPLATE_TYPES CURSOR SYSTEM REPCAT$_USER_AUTHORIZATIONS CURSOR SYSTEM REPCAT$_USER_AUTHORIZATIONS_S CURSOR SYSTEM REPCAT$_USER_PARM_VALUES CURSOR SYSTEM REPCAT$_USER_PARM_VALUES_S CURSOR SYSTEM REPCATLOGTRIG TRIGGER SYSTEM SYS NON-EXISTENT SYSTEM ALL_POLICIES NON-EXISTENT XDB DBMS_OUTPUT CURSOR XDB DBMS_RLS CURSOR XDB DBMS_XDBZ PACKAGE XDB DBMS_XDBZ0 PACKAGE XDB SECURITY_LIB CURSOR XDB USER_POLICIES NON-EXISTENT XDB Session statistics for SID '14'. Only non-zero values displayed. Parameter name Value Class bytes received via SQL*Net from client 2,881 User bytes sent via SQL*Net to client 2,850 User CPU used by this session 7 User logons cumulative 1 User logons current 1 User opened cursors cumulative 109 User opened cursors current 53 User recursive calls 3,382 User recursive cpu usage 74 User session connect time 1,061,908,281 User session logical reads 2,132 User session pga memory 951,840 User session pga memory max 1,017,376 User session uga memory 667,344 User session uga memory max 667,344 User SQL*Net roundtrips to/from client 16 User user calls 20 User user commits 1 User redo entries 2 Redo redo size 556 Redo enqueue conversions 8 Enqueue enqueue releases 62 Enqueue enqueue requests 79 Enqueue commit cleanouts 1 Cache commit cleanouts successfully completed 1 Cache consistent gets 2,129 Cache consistent gets - examination 316 Cache db block changes 4 Cache db block gets 3 Cache free buffer requested 1 Cache redo synch time 5 Cache redo synch writes 1 Cache shared hash latch upgrades - no wait 218 Cache switch current to new buffer 1 Cache calls to get snapshot scn: kcmgss 403 Parallel server cluster key scan block gets 136 SQL cluster key scans 74 SQL execute count 312 SQL parse count (hard) 33 SQL parse count (total) 132 SQL parse time cpu 44 SQL parse time elapsed 45 SQL rows fetched via callback 17 SQL sorts (memory) 123 SQL sorts (rows) 259 SQL table fetch by rowid 451 SQL table scan blocks gotten 789 SQL table scan rows gotten 64,630 SQL table scans (short tables) 36 SQL workarea executions - optimal 122 SQL buffer is not pinned count 1,819 buffer is pinned count 274 calls to kcmgas 2 Debug CPU used when call started 7 Debug cursor authentications 10 Debug deferred (CURRENT) block cleanout applications 1 Debug index fetch by key 96 Debug index scans kdiixs1 218 Debug messages sent 1 Debug no work - consistent read gets 1,492 Debug process last non-idle time 1,061,908,281 Debug
-- ------------------------------------------------ Rainer Herbst Linux - Registered ZEIK User #319157 Universität Potsdam Usual disclaimers applies! ------------------------------------------------Received on Tue Aug 26 2003 - 10:03:38 CDT
 |
 |