Dropping all User Objects Example

This is one in the list of my Example Series in this blog. Check this blog for more possible examples

-- A lot of times I want to drop all user objects in the schema. I use the following script

-- remember to connect first as the user/owner of the objects you want to drop. Do NOT run this as another user or SYS

declare
del_objs integer;
obj_del integer;
cursor get_objs is
select object_type,
'"'||object_name||'"'||decode(object_type,'TABLE' ,' cascade constraints',null) obj_nam
from user_objects
where object_type in ('TABLE',
'VIEW',
'PACKAGE',
'SEQUENCE',
'PROCEDURE',
'FUNCTION',
'SYNONYM', 'MATERIALIZED VIEW')
order by object_type;
cursor get_objs2 is
select object_type, '"'||object_name||'"' obj_nam
from user_objects
where object_type in ('TYPE');
begin
for objrec in get_objs loop
execute immediate ('drop '||objrec.object_type||' ' ||objrec.obj_nam);
end loop;

for objrec in get_objs2 loop
begin
execute immediate ('drop '||objrec.object_type||' ' ||objrec.obj_nam);
exception
when others then
null;
end;
end loop;
end;
/

-- Remember to drop the Queues if you are using AQ
declare
cursor active_queues is select OBJECT_NAME from user_objects where OBJECT_TYPE = 'QUEUE';
cursor active_queue_tables is select OBJECT_NAME from user_objects where OBJECT_TYPE = 'TABLE';
begin
for next_queue in active_queues loop
DBMS_AQADM.STOP_QUEUE (queue_name => next_queue.object_name);
DBMS_AQADM.DROP_QUEUE (queue_name => next_queue.object_name);
end loop;

for next_table in active_queue_tables loop
DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => next_table.object_name, force => TRUE);
end loop;
end;
/

-- Remember to Purge the Recylebin if you are using 10g

purge recyclebin
/

-- Comfirm all is dropped

select * from user_objects
/

Enterprise Manager Upgrade Hints

I have just upgraded my test Grid Control from release 2 (10.2.0.2) to release 3 (10.2.0.3). The first thing did was to read the Readme. This provided most of the hints I need to know and to avoid before I start. For instance, patches to apply and those to rollback.

EM Grid Control Release 3 (10.2.0.3.0) Readme

After that, I picked the release note itself and still went through thoroughly. I know we do not all have the time to do it, but I see it as very cost effective because you spend less time in problem diagnosis after meeting all the requirements.

EM Grid Control Release 3 (10.2.0.3.0) Release Note (Linux and Windows)

I did not have a patch to apply or rollback because the ones mentioned in the release note does not apply to my setup.

I have answered a lot of question relating to upgrades in the Oracle forum and most of the time, they are simply related to pre-install requirements and compatibility. Although the Readme and release notes as rightly pointed out by one of the users, is not always correct, but I see it as the first step before I start looking elsewhere.

Although I had a smaller test system, at the end of it, it went smoothly without a hitch. If I have any problems with it later, then I will look into it as a separate issue because I know the upgrade of both OMS, Agent and repository went OK.

Platform: Windows XP SP2
Dell Latitude D810 Laptop
Intel Pentium M 2.0 GHz Processor
1GB RAM
60GB Hard Drive

Environment: Personal Upgrade Testing for Training purpose.

The same Grid Control installation has gone through various upgrades on my system. Release 10.1.0.3, 10.1.0.4, 10.2.0.2 and now 10.2.0.3. I think I am ready for the next upgrade. What do you think ?