CODEWITHSUNDEEP
javaoracle-databasejdbcoracle11g
Huy Tran
Huy Tran

Reputation: 619

Datafile in OS is not deleted when use JDBC run Drop Tablespace command

I write an Java app using ojdbc6.jar library to drop a tablespace in Oracle with command:

drop tablespace test including contents and datafiles cascade constraints;

But when command is finished (successed with no error), the datafiles still exist in my OS.
When I run this command directly on sqlplus or SQL Developer, the datafiles will gone in a blink of eyes.

This is my SQL Script to create tablespace (I don't think it's the reason)

    CREATE TABLESPACE UC4_112_DATA
DATAFILE 'D:\app\oradata\testimport\tablespace_bond_75.dbf'
SIZE 128M
AUTOEXTEND ON NEXT 100M MAXSIZE 5000M
DEFAULT STORAGE (INITIAL 512K NEXT 2048K MAXEXTENTS UNLIMITED);

I use Oracle 11G2 install on Windows 8.
Do you guys have met this symptom before and how to fix this?
Code java to run this command (in both way run script file and run command)

SQLRunner sqlRunner = null;
    try {
        logger.info("Executing SQL query/script ...");
        sqlRunner = new SQLRunner(connection);
        sqlRunner.setStopOnError(true);
        if (scriptFile != null) {
            sqlRunner.setScriptDelimiter(scriptSeperator);

            File tempFile;
            URI uri = new URI(scriptFile);
            if (uri.getScheme() == null){
                tempFile = new File(scriptFile);
            }else{
                String fileInURI = URLEncoder.encode(uri.toURL().getFile(), "UTF-8");
                String filename = URLDecoder.decode(fileInURI, "UTF8");
                filename = normalizePath(filename, charMap, false, scriptFile.length() - filename.length());
                filename = filename.replace("BACK_TICK", "`");
                tempFile = new File(filename);
            }
            sqlRunner.executeScript(tempFile);

        } else {
            String query = parser.getOptionValue(queryArg);
            query = decryptIfEncrypted(query);
            String outputSeperator = parser.getOptionValue(outputSeperatorArg);
            if (outputSeperator == null) {
                outputSeperator = "#!#";
            }
            SQLResult result = sqlRunner.executeSQL(query);
            printResult(result, outputSeperator);
        }

        sqlRunner.commit();
        logger.info("Executed SQL query/script successfully.");
    } catch (SQLException e) {
        sqlRunner.rollback();
        logger.error(e);
        if (e.getMessage().contains(" exist"))
            returnCode = ErrorCodes.SQL_EXISTS_ERROR;
        else
            returnCode = ErrorCodes.EXCEPTION;

    } catch (URISyntaxException e) {
        logger.error(String.format("Invalid URI file \"%s\" !!!", scriptFile), e);
        returnCode = ErrorCodes.PARAMSMISMATCH;
    } finally {
        if (sqlRunner != null)
            sqlRunner.close();
    }

Upvotes: 0

Views: 81

Answers (1)

atokpas
atokpas

Reputation: 3351

This behavior is expected in Windows platform(actually a limitation of Windows platform) where a file cannot be deleted until all the processes that has it open release it.

Issue ALTER DATABASE DATAFILE '<file_name>' OFFLINE DROP; before dropping the tablespace.

Bug 2338968 : DROP TABLESPACE DOES NOT ALWAYS AUTOMATICALLY DROP DATAFILES.

Upvotes: 3

Related Questions