Drop Tables and Indexes

Learn how to delete a table or index that you have created in Oracle NoSQL Database.

To drop a table or index, use the DROP TABLE or

DROP
                INDEX

DDL statements. For example:

/* Drop the table named users */
DROP TABLE users

/* Drop the index called nameIndex on the table users */
DROP INDEX IF EXISTS nameIndex ON users

The following example considers that the default compartment is specified in NoSQLHandleConfig while obtaining the NoSQL handle. See Obtaining a NoSQL Handle. To explore other options of specifying a compartment for the NoSQL tables, see About Compartments .

To drop a table using the TableRequests.setStatement method:

/* create the TableRequest to drop the users table */
TableRequest tableRequest = new TableRequest().setStatement("drop table users");

/* start the operation, it is asynchronous */
TableResult tres = handle.tableRequest(tableRequest);

/* wait for completion of the operation */
tres.waitForCompletion(handle,
                        60000,  /* wait for 60 sec */
                        1000);  /* delay in ms for poll */

You can drop an MR Table using the DROP statement in the same manner as you drop any other Oracle NoSQL Database table. If you choose to drop an MR Table in a particular region, it continues to remain an MR Table in the other participating regions. In a case where you want to drop a particular MR Table from multiple regions, you must execute the DROP TABLE statement in each region separately.

Note:

If you drop an MR Table in all the regions except one, it becomes an MR Table linked with a single region. The difference between an MR Table with a single region and a local table is that you can add new regions to the MR Table with a single region in the future.

To drop a table or index from your application, you use the TableRequest class. For example to drop the users table:

/* Drop the table identified by the tableName */
final String dropStatement = "drop table " + users; 

/* Pass the dropStatement string to the TableRequest.setStatement method */
TableRequest tableRequest = new TableRequest().setStatement(dropStatement);

/* Wait for the table state to change to DROPPED. */
TableResult tres = handle.tableRequest(tableRequest);
tres = TableResult.waitForState(handle,tres.getTableName(),
       TableResult.State.DROPPED,
                           30000, /* wait 30 sec */
                           1000); /* delay ms for poll */

The following example drops the table users.

from borneo import TableRequest 
# the drop statement 
statement = 'drop table users'
request = TableRequest().set_statement(statement) 
# perform the operation, wait for 40 seconds, polling every 3 seconds 
result = handle.do_table_request(request, 40000, 3000)

The following example drops the given table.

// Drop the table
dropReq := &nosqldb.TableRequest{Statement: "DROP TABLE IF EXISTS " + tableName}
tableRes, err = client.DoTableRequestAndWait(dropReq, 60*time.Second, time.Second)
if err != nil {
    fmt.Printf("failed to drop table: %v\n", err)
    return
}
fmt.Println("Dropped table " + tableName)

The following example drops the given table and index.

const NoSQLClient = require('oracle-nosqldb').NoSQLClient;
const TableState = require('oracle-nosqldb').TableState;
.....
const client = new NoSQLClient('config.json');

async function dropNameIndexUsersTable() {
    try {
        let result = await client.tableDDL('DROP INDEX nameIndex ON users');
        // Before using the table again, wait for the operation completion
        // (when the table state changes from UPDATING to ACTIVE)
        await client.forCompletion(result);
        console.log('Index dropped');
    } catch(error) {
        //handle errors
    }
}

async function dropTableUsers() {
    try {
        // Here we are waiting until the drop table operation is completed
        // in the underlying store
        let result = await client.tableDDL('DROP TABLE users', {
            completion: true
        });
        console.log('Table dropped');
    } catch(error) {
        //handle errors
    }
}

To drop tables, use ExecuteTableDDLAsync and ExecuteTableDDLWithCompletionAsync.

var client = new NoSQLClient("config.json");
try {
    // Drop index "nameIndex" on table "users".
    var result = await client.ExecuteTableDDLAsync(
        "DROP INDEX nameIndex ON users");
    // The following may print: Table state is Updating.
    Console.WriteLine("Table state is {0}", result.TableState);
    await result.WaitForCompletionAsync();    
    // Expected output: Table state is Active.
    Console.WriteLine("Table state is {0}.", result.TableState);
    // Drop table "TestTable".
    result = await client.ExecuteTableDDLWithCompletionAsync(
        "DROP TABLE TestTable");
    // Expected output: Table state is Dropped.
    Console.WriteLine("Table state is {0}.", result.TableState);    
}
catch(Exception ex) {
    // handle exceptions
}

To drop the tables and Indexes, you use NosqlTemplate.runTableRequest() or NosqlTemplate.dropTableIfExists() methods. For details, see SDK for Spring Data API Reference.

Create the AppConfig class that extends AbstractNosqlConfiguration class to provide the connection details of the database. For more details, see Obtaining a NoSQL connection.

In the application, you instantiate the NosqlTemplate class by providing the NosqlTemplate create(NosqlDbConfig nosqlDBConfig) method with the instance of the AppConfig class. You then drop the table using the NosqlTemplate.dropTableIfExists() method. The NosqlTemplate.dropTableIfExists() method drops the table and returns true if the result indicates a change of the table's state to DROPPED or DROPPING.

import com.oracle.nosql.spring.data.core.NosqlTemplate;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ConfigurableApplicationContext;
 
/* Drop the Users table.*/
 
try {
    AppConfig config = new AppConfig();
    NosqlTemplate tabledrop = NosqlTemplate.create(config.nosqlDbConfig());
    Boolean result = tabledrop.dropTableIfExists("Users");
    if (result == true) {
        System.out.println("Table dropped successfully");
    } else {
        System.out.println("Failed to drop table");
    }
} catch (Exception e) {
    System.out.println("Exception creating index" + e);
}