How to remove all database objects for a schema in java?

Question

Whats the best way for cleaning (removing all objects, tables, views etc.) for an oracle database schema in java?

I can run something like https://stackoverflow.com/a/1690419/812093 with an Oracle database client but is there a java library or tool which could be used? I know that I could read the connection meta data, iterate over all objects and drop them but I was wondering if somebody did that before and if there is anything out of the box which could be used.

I need this functionality in order for setting up integration tests that - clean a database schema - execute a bunch of scripts against that schema to initialize the database

Answer 1

Flyway

It looks like the Flyway database migration tool offers the functionality that I was looking for - at least if you feel like hacking :) Since I required the feature just for integration testing anyway it is good enough for me. Let me share my code.

Add Flyway and Oracle driver dependency

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
    <version>5.1.1</version>
    <scope>test</scope>
</dependency>       
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc7</artifactId>
    <version>12.1.0.2</version>
    <scope>provided</scope>
</dependency>   

Main class to execute scripts and cleanup db

public class ExecuteScriptsAgainstOracle {

    public static void main(String args[]) throws Exception, SQLException {

        Flyway flyway = new Flyway();
        flyway.setDataSource("jdbc:oracle:thin:@xxx:1521:yyy", "zzz", "zzz");
        flyway.setTable("auto_schema_setup_flyway");

        Database<?> database = DatabaseFactory.createDatabase(flyway.getConfiguration(), false);

// clean the database (remove all objects of the schema)
        flyway.clean();
        flyway.setSkipDefaultResolvers(true);
        flyway.setResolvers(new MyMigrationResolver(database, flyway.getConfiguration()));
        flyway.migrate();
    }

}

The MigrationResolver that lists the to be executed files

package org.flywaydb.core.internal.resolver.sql;

public class MyMigrationResolver implements MigrationResolver {

private Database<?> database;
private Configuration configuration;
private int order = 0;

public MyMigrationResolver(Database<?> database, Configuration configuration) {
    this.database = database;
    this.configuration = configuration;
}

@Override
public Collection<ResolvedMigration> resolveMigrations() {

    Collection<ResolvedMigration> scripts = new LinkedList<ResolvedMigration>();
    scripts.add(script(folder1\\somescript.dml"));
    scripts.add(script("folder2\\someOtherScript.sql"));

    return scripts;
}

private ResolvedMigrationImpl script(String scriptName) {


    String baseFolder = "target\\scripts\\";
    order++;

    ResolvedMigrationImpl migration = new ResolvedMigrationImpl();
    migration.setScript(baseFolder+scriptName);
    migration.setType(MigrationType.SQL);
    migration.setDescription(""+String.format("%03d",order)+" "+scriptName);
    migration.setExecutor(new SqlMigrationExecutor(database,
            new FileSystemResource(migration.getScript(), configuration.getEncoding()),
            new PlaceholderReplacer() {

                @Override
                public String replacePlaceholders(String input) {

                    // just remove parts of the sql that flyway can't deal with
                    input = StringUtils.replace(input, "WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;", "");
                    input = StringUtils.replace(input, "SET DEFINE OFF;", "");

                    return input;
                }

                @Override
                public Map<String, String> getPlaceholderReplacements() {
                    return null;
                }
            }
            , configuration));
    return migration;
}

}

Answer 2

Cause your question is not clear, so I'll draw two directions:

Direction 1 - drop all objects and user as well.

The best way is: drop user <user_name_here> cascade; Run SQL statement under user having DROP USER System privilege.

Direction 2 - drop all objects but still keeping user (all roles & privileges)

First list all privileges and copy to notepad(for example hr user):

-- GETTING ALL SYSTEM PRIVILEGES
SELECT 'GRANT ' || PRIVILEGE || ' TO ' || GRANTEE || 
CASE ADMIN_OPTION 
    WHEN 'YES' THEN ' WITH ADMIN OPTION;'
    ELSE ';'
END
from dba_sys_privs where grantee ='HR';
-- GETTING ALL ROLES
SELECT 'GRANT ' || GRANTED_ROLE || ' TO ' || GRANTEE ||
CASE ADMIN_OPTION 
    WHEN 'YES' THEN ' WITH ADMIN OPTION;'
    ELSE ';'
END
FROM DBA_ROLE_PRIVS
WHERE GRANTEE='HR';
-- GETTING ALL TABLES, EXECUTE, ... PRIVILEGES
SELECT 'GRANT ' || PRIVILEGE || ' ON ' || OWNER || '.' || TABLE_NAME || ' TO ' || GRANTEE ||
CASE GRANTABLE
    WHEN 'YES' THEN ' WITH GRANT OPTION;'
    ELSE ';'
END
FROM DBA_TAB_PRIVS
WHERE GRANTEE='HR';

Then drop user cascade;

Recreate user and run the content of notepad file.

Thanks Alex Poole for your suggestion!