This content has been marked as final. Show 7 replies
How can I actually turn off the recycle bin so I can use SQL Developer?How fast does SELECT object_name from all_objects; return on your machine through SQL*Plus?
If the system is slow overall, then it's an Oracle issue.
If it's fast, then this is an SQL Developer problem and you should stick to the thread on
that forum - sounds like the case to me.
I meant to use SQL*Plus directly on the server to rule out network issues also.
Seems to work for me. Who owns the stuff in your recyclebin? Maybe the DBA didn't give himself enough privs?
SYS@TTST> select count(*) from dba_recyclebin; COUNT(*) ---------- 5 SYS@TTST> alter system set recyclebin=off scope=memory; System altered. SYS@TTST> purge dba_recyclebin; DBA Recyclebin purged. SYS@TTST> alter system set recyclebin=on scope=memory; System altered. SYS@TTST> select count(*) from dba_recyclebin; COUNT(*) ---------- 0 SYS@TTST>
this is the query produced by SQL Dev with bind variables replaced:
FROM (SELECT object_name,
CAST (last_ddl_time AS TIMESTAMP) last_modified,
DECODE (status, 'INVALID', 'TRUE', 'FALSE') invalid,
FROM sys.all_objects o, all_plsql_object_settings s
WHERE o.owner = 'TRAIN'
AND s.owner(+) = 'TRAIN'
AND s.name(+) = o.object_name
AND s.TYPE(+) = 'FUNCTION'
AND object_type = 'FUNCTION'
AND subobject_name IS NULL
AND object_id NOT IN (SELECT purge_object FROM recyclebin) );
The first 2 times this runs it returns 117 rows in 5 sec. The third and subsequent times it takes over10 minutes.
removing the statement " AND object_id NOT IN (SELECT purge_object FROM recyclebin"
allows the query to run in under one second every time.
So I don't see this evidence pointing to the network or to SQL Dev, except that it insists on checking the recycle bin when looking for objects to fill its tree.
the issue seems to be that the entries in the sys.recyclebin$ table do not exist in the dba_recyclebin view, and therefore can not be purged.
select count(*) from dba_recyclebin;
select * from sys.recyclebin$;
Interesting. You should perhaps ask support about this.
You can look at your view definition of dba_recyclebin, which may be different than mine. Mine has a where clause:
So there is an outer join for ts$, but not for recyclebin$. The view would not see some items. I couldn't say whether the bug is in developer looking at the table instead of the view, or if dropping a tablespace orphans objects. The latter sounds familiar somehow... you might google such a thing. The drop tablespace is supposed to purge the recyclebin, so that would be a bug.
from sys.obj$ o, sys.recyclebin$ r, sys.user$ u, sys.ts$ t where o.obj# = r.obj# and r.owner# = u.user# and r.ts# = t.ts#(+)
Edit: Just some more thinking out loud. In recyclebin$, there is the owner and a couple of TS fields. You might have the DBA sample some of those, and perhaps count and group them by tablespace number. Then he can look up the tablespace number and purge that tablespace. Or maybe the user or dates dropped will give a clue.
Other questions to ask: Have tablespaces been dropped or made read-only, is deferred segment creation in use, has there been an upgrade (particularly involving DMT to LMT), datafiles been manipulated...
Edited by: jgarry on May 14, 2012 1:33 PM
after contacting oracle support, my DBA rebuilt the indexes on SYS.RECYCLEBIN$
This corrected the issue.