I'm working on a huge database consist of more than a 1000 table, the database is increasing dramatically and I need to put some restrictions on creating new tables. thinking out loudly, a prompt message could show up when the developer create new table or creating a rule for adding the purpose behind creating the table? Also I need to find a way that temp tables or testing tables will be scheduled somewhere for a clean up process later on. Please don't hesitate to share any idea could be useful! :)
2
-
1Why is this even a problem? Surely new tables should have a purpose, and first be created in a development environment rather than directly in production? This is really more of a workplace cultural problem– PhilᵀᴹAug 24, 2016 at 9:43
-
Right Phil, it's mostly a workplace cultural problem but same time when you discover after couple of running projects a not small number of temp tables have been created for integration and migration purposes and most of them don't need to keep them for a long term then you need to come up with a solution regardless the workplace cultural behavior.– KhalilAug 24, 2016 at 10:21
Add a comment
|
2 Answers
If this is a production database, you should remove developer's access rights to create tables. This can be achieved via the REVOKE command. You may have further work to do if the developers are adminstrators.
Developers should then create new tables in a developement database and these are migrated to production via the DBA team following a code review and test cycle.
Alternatively, you could create a DDL trigger that could stop users creating tables via a ROLLBACK and create a warning message for the developers as you suggest.
Finally, to clean up test tables, you will need some method to identify them (table name pattern, owner etc). You can then automate a script to drop such tables.
If this is mainly about temporary tables from migration and integration activities then I suggest ensuring that they are named consistently so that you can create a script that will periodically drop them. You could schedule this via DBMS_JOB or whatever scheduler is normally used and it will call a package to find the tables and drop them.
If this is about developers creating temporary tables 'at will' in the production database then revoke their privileges ! Now ! Only privileged users should be creating any objects in production and then only through tested and reviewed scripts.
The review process should be able to ensure that only tables fulfilling a business need are created and that the data the table holds is not already available elsewhere. A message to the developer will likely just be ignored where a review action that means the code cannot progress to test will need to be acknowledged and accepted by the reviewer.
