[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 418
  • Last Modified:

Objects in BIN/ Invalids

Experts,
I ran the following script to generate drop statment for any invalid synonyms,
****************
rem
rem Exludes SYS and SYSTEM users
rem
select 'drop '||decode (s.owner,'PUBLIC','PUBLIC SYNONYM ',
'SYNONYM'||s.owner||'.')||s.synonym_name||';'
from dba_synonyms s
where table_owner not in('SYSTEM','SYS') and db_link is null and not exists
(select 1 from dba_objects owhere s.table_owner=o.owner and s.table_name=o.object_name)
/
***********************
Out put is as shown below
'DROP'||DECODE(S.OWNER,'PUBLIC','PUBLICSYNONYM','SYNONYM'||S.OWNER||'.')||
--------------------------------------------------------------------------
drop PUBLIC SYNONYM BIN$m3DiTY5IRdazHvvqWXY/IQ==$0;
drop PUBLIC SYNONYM BIN$uMUlc2tERh67hTAOOoPpNw==$0;
drop PUBLIC SYNONYM BIN$4vpJF4iYT5iCA2YQ+ggiUw==$0;
drop PUBLIC SYNONYM BIN$MX2qZYLAQI+Iln1XBRKUxw==$0;
70 rows selected.

since they appeared to be in the recycle bin, i purged the recycle bin, ran the utlrp.sql  , which showed me that there is only 1 object with error.

My question is, after all this i still get the same output from querry i ran first which is returning the same 70 records, what is going on? why do i get these 70 rows returned even after purging teh recycle bin and utlrp ? i guess i am reading it wrong ?? please advice!

0
crishna1
Asked:
crishna1
  • 2
2 Solutions
 
sathyagiriCommented:
Use the purge command to clear these from your recycle bin.

This is a feature in 10g where in if you drop a table, it automatically gets cataloged to u're recyle bin for flash back purposes.


To drop a specific object from the bin

PURGE SYNONYM BIN$Name;

or to clear the bin itself.

PURGE RECYCLEBIN;

Refer this link for additional details on this
http://www.oracle.com/technology/pub/articles/10gdba/week5_10gdba.html

0
 
crishna1Author Commented:
sathyagiri,
i guess you did not the entire posting above , as i mentioned , this is after pruging the recyclebin.
0
 
sathyagiriCommented:
How did you purge it??

If you are a dba , then you could issue the
PURGE DBA_RECYCLEBIN to clean out all the Recycle Bins..
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now