Friday, July 16, 2010

Creating objects in tablespaces without any quota

Say you've got a schema with privileges on multiple tablespaces and you want to make sure that objects get created in the appropriate one. One way to do that is make the user's default tablespace one that they don't have a quota on.

That way, if they try to create an object without specifying the tablespace, it picks the default one and errors because there is no quota. Or at least it used to.

Apparently in 11gR2 it doesn't work this way any more. With deferred segment creation, you can create objects in tablespaces where you don't have any quota, and it won't fail until a row gets inserted.

I can see another 'gotcha' for this if you are progressing code from DEV/TEST. The creation DDL make succeed by sticking the table in a USERS tablespace (which isn't expected to be used, except maybe for some trivial temporary tables) whereas before it would fail and red-flag it for re-assignment to the appropriate tablespace.


Tom said...

If you want segment creation to work the old way, you could just set the parameter deferred_segment_creation to false instead of true.

Noons said...

Good catch, Gary.
Thanks for that.