Creating, Dropping, and Editing Tables in Oracle Database (2024)

This topic describes how to:

• create, drop, and edit a table in a database with the help of dbForge Studio for Oracle
• create, drop, and edit a table using the CREATE TABLE, DROP, and UPDATE statements respectively

Creating a Table

To create a table, do the following:

1. On the Start Page, switch to the Database Design tab and click New Database Object.

2. In the New Object dialog box, select the Table type.

3. In the Name field, specify the table name and click Create. If the specified table name is unique, the table editor opens.

4. Add columns to the table and specify its properties.

   

5. To save the table, click Apply Changes.

   -or-

6. In Database Explorer, right-click the Tables folder and select New Table.
7. In the Name field, specify the table name.
8. Add at least one column to the table and specify its properties.

9. To save the table, click Apply Changes.

   -or-

On the Standard toolbar, click New Database Object and select the Table object type from the drop-down list and then follow the steps from the procedure listed above.

Note

To learn more about how to add columns, constraints, or indexes to the table and to define other table parameters, see Defining Table Structure.

Alternatively, you can create a table in the schema using the CREATE statement.

Creating a Table Using the CREATE TABLE Statement

To create a table using the CREATE TABLE statement, do the following:

1. On the Start Page > SQL Development tab, click SQL Editor.

   See Also

   How to Create a Table in SQL Server – Data to FishPLSQL: CREATE TABLE AS Statement

   -or-

   On the Standard toolbar, click New SQL.

2. In the SQL editor, type the query. The syntax is as follows:

    CREATE TABLE schema_name.table_name ( column_1 data_type column_constraint, column_2 data_type column_constraint, ... table_constraint );

3. Add the table name and schema name to which a new table will refer.
4. Specify a column name, its type (for example, NUMBER, VARCHAR, etc) and column constraint (for example, NULL, primary key, etc). The columns should be separated by a comma.
5. Add a table constraint if applicable (for example, primary key, foreign key, etc).
6. To run the query, click .

Note

Keep in mind if you want to create a table in the schema of another user, you should have corresponding privileges. For more information, refer to Granting and Revoking Privileges on Database Objects.

Editing a Table

Editing a table implies editing its structure: changing, removing, or adding columns, indexes, modifying table options. Table editor allows performing all these actions and viewing changes in the script of the table.

To edit a table and view changes, do the following:

1. In the Database Explorer window, expand the Tables folder.
2. Right-click a required table and select Edit Table on the shortcut menu.
3. Make necessary changes in the table structure.
4. To view the changes, click Script Changes.

5. To save the changes, click Apply Changes. If you want to undo all actions made since table document (or any another object editor) was opened or saved, click Undo on the Standard toolbar. However, you cannot issue the Redo command for database objects, because when you click Undo, a database object is reloaded from server, and all unsaved changes are lost.

   

Optional: You can alter the table name, change the database (owner), or set a comment for the table.

Note

Keep in mind that to change the database owner, you should have privileges to create database objects in a schema of another user.

Editing a Table Using the UPDATE Statement

To change table data using the UPDATE statement, do the following:

1. On the Start Page > SQL Development tab, click SQL Editor.

   -or-

   On the Standard toolbar, click New SQL.

2. In the SQL editor, type the query. The syntax is as follows:

    UPDATE table_name SET column1 = value1, column2 = value2, column3 = value3, ... WHERE condition;

3. In the UPDATE clause, add a table name you want to update.
4. In the SET clause, specify the name of the column(s) to be changed and define a new value. The columns should be separated by a comma.

5. In the WHERE clause, indicate the condition that determines rows to be updated.

   Note
   If you ignore the WHERE condition, the specified columns of all rows will be updated.

6. To run the query, click .

Dropping a Table

To drop a table, do the following:

1. In the Database Explorer window, expand the Tables folder.

2. Right-click a required table and then click Delete on the shortcut menu.

   

3. In the pop-up window, do the following:
   • click Yes to confirm the deletion of the table.
   • click No to cancel the deletion of the table.

Note

While dropping a table, all its data will be lost.

Dropping a Table Using the DROP TABLE Statement

To remove a table from the database, do the following:

1. On the Start Page > SQL Development tab, click SQL Editor.

   -or-

   On the Standard toolbar, click New SQL.

2. In the SQL editor, type the query. The syntax is as follows:

    DROP TABLE [schema_name].table_name [ CASCADE CONSTRAINTS ] [ PURGE ];

3. Specify a schema name containing the table. If you don’t specify the schema name, your schema will be automatically set.
4. Indicate a table name you want to remove.
5. Optional: Add the CASCADE CONSTRAINTS clause to remove all referenced constraints of the table. Otherwise, dropping the table fails if there is a reference to primary and/or foreign keys, and you didn’t indicate the constraints clause in the statement.
6. Optional: Add the PURGE clause to remove the table and its dependent objects from a recyle bin. In this case, you won’t be able to recover the table if needed. If you don’t add the PURGE clause, the table with its dependent objects will be moved to a recyle bin and you will be able to recover it if needed.
7. To drop the table, click .

Want to Find out More?

• Previous
• Next