i wanted to find the objects present in the 4 dataspaces and i run the following query....
SELECT OWNER,TABLESPACE_NAME, SEGMENT_NAME,SEGMENT_TYPE,round(sum(bytes)/1024/1024) USED
GROUP BY OWNER,TABLESPACE_NAME,SEGMENT_NAME,SEGMENT_TYPE
ORDER BY SEGMENT_NAME;
Now the problem is with the output .....
i am getting weird object names....
owner tablespace_name segment_name segmen_type space_used
HQAUAT1 LETTER_INDEX BIN$R+eo/M1NZbvgRAAXpHb+mQ==$0 INDEX 1
HQAUAT1 LETTER_DATA BIN$R+eo/M1OZbvgRAAXpHb+mQ==$0 TABLE 1
HQAUAT1 LETTER_INDEX BIN$R+eo/M1QZbvgRAAXpHb+mQ==$0 INDEX 1
Now my question is what is these present under segment name... as they r not the object names present in the database......
Can someone help me with it?
06-12-2008, 11:51 AM
Your database has recycle bin feature active. These are names Oracle asign to dropped objects.
Do "show parameter recyclebin" in SQL*Plus and see it for yourself.
06-12-2008, 11:54 AM
Now how do i proceed about it .....
i don not want that objects when i run the query......
can you guide me through it?
06-12-2008, 12:03 PM
-- or --
-- and --
ALTER SYSTEM SET RECYCLEBIN=OFF;
06-12-2008, 12:14 PM
that was quick....
but i think u have to have the sysdba privalages to run this purge command, which i unfortunately dont :(
anyways thank you so much
06-12-2008, 12:15 PM
You can still use the PURGE on your schema. :p
06-12-2008, 01:14 PM
if you did not have dba privs, how did you access dba_segments in your query above?
06-12-2008, 01:46 PM
i have used the following commmand but it doesnt work:
sql> purge tablespace tablespace_name;
SP2-0734: unknown command beginning "purge tabl..." - rest of line ignored.
i have no idea why this is happening?
i donot have the sysdba privilages. They created a user with some kind of privilages and gave it to me.
06-12-2008, 02:05 PM
Take a moment to read the fine Oracle SQL reference manual on this subject.