Tuesday, March 18, 2014

Oracle next extent check for tablespace fails and recyclebin (Grid Control)

If you using maintenance jobs to check does next extent fit in the tablespace and you get errors that they does not.

That means of course that your tablespace (or datafile) is so full that next extent would not fit into it.
"Quite like error ora-01653 unable to extend table in tablespace."

Sometimes this kind of errors can be confusing if you look tablespace usage from DBA_FREE_SPACE (or DBA_EXTENTS) . And this is because DBA_FREE_SPACE does not show dba_recyclebin usage. So if you remove database objects (for example table) from database but does not "purge" it then it will go into recyclebin and still takes same amount of space from same tablespace. This recyclebin used space can be taken into use if space is needed for new data. But it cannot be seen in DBA_FREE_SPACE as used space because it is reclaimable. But you can see this used space if you check for example tablespace (or datafile) usage from DBA_SEGMENTS.

Same thing is with the Grid Control (at least version 11G). If you check tablespaces usage it shows that there is free space to use in tablespace (this does not contain recyclebin space usage) but if you look the datafile space usage it will show real data space usage + recyclebin space usage.

NOTE! You wont get ORA-01653 errors in this kind of situations because Oracle can start to use space used by recyclebin when needed. But is is good to remember that you might get different space usage info if you wont purge dropped objects. 

1 comment:

  1. As claimed by Stanford Medical, It is really the one and ONLY reason this country's women live 10 years more and weigh 19 kilos lighter than us.

    (And realistically, it has absolutely NOTHING to do with genetics or some hard exercise and really, EVERYTHING around "how" they eat.)

    P.S, What I said is "HOW", not "WHAT"...

    CLICK this link to uncover if this brief test can help you discover your true weight loss potential

    ReplyDelete