10g additional maintenace activities

[prakashs43]

Hi friends ,

if for some reason any application xyz has migrated its database from 8i to 10g there are some additional maintenance that need to be performed on database like

PURGING TABLES , INDEXES , ETC ..

does any one have a generic cleanup script that needs to be doen periodically .

one of my application dynamically creates and drops tables , indexes etc ...

i am worrying if this is going to increase database recycle bin ..


also just a quick question is recyclebin part of 10g database or undo tablespace ..

anyhow this is not a priority question .

thanks

[davey23uk]

how about changing your code (or deisgn) to drop table purge instead of not doing it?

[adrianomp]

Recyclebin resides on the same tablespace where the objects are. In fact, when you issue a DROP TABLE you're just renaming the object (that's why DROP TABLE is so fast).

In theory, one don't need to worry about recyclebin, since object are automatically aged out when there's a need for extra space. But, as Davey said, you can simply use DROP TABLE PURGE, which does the same as DROP TABLE on older versions.

[prakashs43]

Yeah , That is true ....
i have around several forms ( 100+ ) and many application scripts . don't know which script is dynamucally dropping tables ...

so i was thinking if there are any generic scripts without changing the application ..

purge recyclebin ;

i think this is specific to the user ...

1) do i need to execute this command for all users ?
2) will purge recyclebin also deletes table data and index data ..

am i missing ianything else ..

[adrianomp]

Purging on a per-user basis is not a bad thing if each user has its own schema. You can also use PURGE DBA_RECYCLEBIN instead, but you need to run it as SYSDBA.

Anyway, I'd rather keep the things as they are, since recyclebin will be automatically purged when a situation of space pressure occurs.