Oracle destroy test database mixes tablespace, database and tables.

[JorisBenschop]

I'm trying to setup oracle test tables using the extra paramters:

'ENGINE': 'django.db.backends.oracle',
'NAME': '10.1.0.1:1521/BIOIMD',
'USER': 'JB_DEV_USER,
'PASSWORD': 'x',
'TEST': {
    'USER': 'JB_UNITTEST',
    'PASSWORD': 'y',
    #'TBLSPACE': 'other_test_tbls',
    #'TBLSPACE_TMP': 'other_test_tbls_tmp', 
    'DEPENDENCIES': [],
    'CREATE_DB': False,
    'CREATE_USER': False,

This is because it is generally not allowed for devs to create tablespaces in oracle or even to create databases. Now above settings work perfectly fine when creating the test database, but not for destroying it. The problem is also that logging is (for me at least) not intuitive.

When running the test runner, it calls teardown_databases, which -in the end- calls a function called _destroy_test_db in db/backends/oracle/creation.py.
I have three things that I believe are incorrect in this function

• the actual destruction of tables depends on this line if self._test_database_create():. However, the CREATE_DB governs the ability to create the database, not the tables within. That is at least the behaviour when the test tables are created. Again, this makes sense because in Oracle, you generally do not let everyone create random databases... The problem here is that setting CREATE_DB to False blocks the ability to clean up the tables... (but setting it to True makes django try to create a tablespace, which results in an error...)
• the logger states: Destroying test database tables..., but subsequent code tries to destroy the entire tablespace, not the table. I think the logger should reflect this action.
• After this logging, _execute_test_db_destruction is called. This code does not in fact destroy a database, but destroy the entire tablespace, this is not the same thing:

  statements = [
      'DROP TABLESPACE %(tblspace)s INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS',
      'DROP TABLESPACE %(tblspace_temp)s INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS',]
  self._execute_statements(cursor, statements, parameters, verbosity)
  

As for most Oracle devs, there is no way I will be allowed to get DB-create permissions of tablespace create permissions. Thus, I need a method to remove the tables without destroying the tablespace or the database. In my view, the parameters of SETTINGS are interpreted differently when creating or destroying the test database, which I believe is a bug. Currently it seems my only resolution is addingt hard "DROP TABLE" commands into the teardown logic, but it just feels bad practice to do so...

As a note, I really tried hard not to judge other peoples code, and to put in a much effort as I could before reaching out to this report. I have searched mailinglists and help pages extensively too. If I am completely wrong, please let me know so I can learn, but kindly do not dismiss this bluntly. Thanks for your time

[Mariusz Felisiak]

It's ​documented that to run a project's test suite in on Oracle the user needs additional privileges, e.g. "CREATE TABLESPACE".

the actual destruction of tables depends on this line if self._test_database_create():

Not really, all tables are created in a test user scope, so they should be dropped when we drop test user.

However, the CREATE_DB governs the ability to create the database, not the tables within.

It's about creating/dropping test tablespaces, see ​documentation. Django doesn't manage pluggable databases.

As for most Oracle devs, there is no way I will be allowed to get DB-create permissions of tablespace create permissions.

It's a matter of your company policy, I worked 10 years with Oracle DBs and it has never been an issue.

To sum up if you don't have permissions to create/drop tablespaces, then you need at least permissions to create/drop a test user. In such case settings like

'TEST': {
    'USER': 'JB_UNITTEST',
    'PASSWORD': 'y',
    'CREATE_DB': False,
    'CREATE_USER': True,
}

should work properly.

Closing per TicketClosingReasons/UseSupportChannels.