Thanks for the question, Guna.
Asked: May 16, 2017 - 10:01 am UTC
Last updated: May 17, 2017 - 8:52 pm UTC
Version: 11.2.0.4
Viewed 1000+ times
You Asked
Halo Tom,
I made one tablespace offline to drop.
But as a test case i created one table in that offlined tablespace, I was shock i.e; im able to create.
But unable to insert rows, while inserting its throwing error tablespace is offline unable to allocate space in it.
please let me know how to restrict table creation.
And please let me know why it is not throwing error while creating ans erroring out while inserting.
Regards,
Gunasekhar
and Connor said...
Creating a table only writes information into the data dictionary, and of course, your SYSTEM tablespace is not offline or read only.
We only need to actually allocate space (ie, access your offline tablespace) when we first try to create a row in that table.
If you want to disable the ability to create tables, you would revoke the CREATE TABLE privilege from that user. If you wanted to allow tables to be created but NOT in an offline tablespace, then you could write a DDL trigger to interrogate the SQL and raise an error.
See the link below for an example of looking at the DDL text within a DDL trigger
https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9527677800346679012