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
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
To drop a table or index from your application, you use 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.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);
}