DROP command

 The DROP command in Oracle Database is used to permanently delete database objects, such as tables, views, indexes, or even the entire database schema. Once a table or other object is dropped, it is removed from the database and cannot be recovered (unless you have enabled features like Oracle's Recycle Bin).

1. Purpose of DROP Command

  • Completely removes the specified object from the database.
  • Deletes the structure and data of the object.
  • Drops dependent objects like constraints, indexes, and triggers associated with the table.

2. Syntax

DROP TABLE table_name [CASCADE CONSTRAINTS] [PURGE];

3. Parameters

  1. table_name:

    • Specifies the name of the table to drop.

  2. CASCADE CONSTRAINTS:

    • Drops the table along with all constraints (e.g., foreign key constraints) referencing it.

  3. PURGE:

    • Removes the table from the Oracle Recycle Bin, making it unrecoverable.

4. Examples

A. Basic Drop Table

Deletes the table and all its data.

DROP TABLE EMP1;

B. Drop Table with Constraints

Deletes the table and also removes any constraints referencing it.

DROP TABLE EMP1 CASCADE CONSTRAINTS;

C. Drop Table Permanently

Deletes the table without moving it to the Recycle Bin.

DROP TABLE EMP1 PURGE;

5. Behavior and Notes

  • Dependent Objects:

    • When you drop a table, all dependent objects such as constraints, triggers, and indexes are also dropped automatically.

  • Foreign Keys:

    • If other tables reference the table being dropped (via foreign keys), you must use CASCADE CONSTRAINTS.

  • Recycle Bin:

    • By default, Oracle moves dropped tables to the Recycle Bin, allowing them to be restored using the FLASHBACK TABLE command.
    • Use the PURGE keyword to bypass the Recycle Bin.

  • Irreversible Action:

    • Dropping a table with PURGE is irreversible. Use it cautiously.

6. Verification

Check Table Existence

To confirm whether a table exists:

SELECT table_name FROM user_tables WHERE table_name = 'EMP1';

7. Recycle Bin

When you drop a table without PURGE, it moves to the Recycle Bin. You can restore or manage it.

  • View Recycle Bin:

    SHOW RECYCLEBIN;

  • Restore Table:

    FLASHBACK TABLE EMP1 TO BEFORE DROP;

  • Permanently Remove from Recycle Bin:

    PURGE TABLE EMP1;

8. Common Errors

  1. ORA-02449: Unique/primary keys in table referenced by foreign keys:

    • Occurs when the table being dropped is referenced by foreign keys in another table. Use CASCADE CONSTRAINTS to resolve this: 
      DROP TABLE EMP1 CASCADE CONSTRAINTS;

  2. ORA-00942: Table or view does not exist:

    • The specified table does not exist or the user does not have sufficient privileges.

9. Best Practices

  • Backup: Always back up important data before dropping tables.
  • Dependency Check: Ensure that no essential objects or processes depend on the table.
  • Recycle Bin Usage: Avoid using PURGE unless absolutely necessary to maintain the ability to recover dropped tables.


Comments

Post a Comment

Popular posts from this blog

dbms exercise 2

 welcome ---------------------------------------------------------------------- CREATE TABLE EMPLOYEE (     ENO NUMBER (4),     ENAME VARCHAR2(50) NOT NULL,     DEPTNO NUMBER (2),     JOBTITLE VARCHAR2(50),     DOB DATE,     SALARY NUMBER(8,2),     COMM NUMBER(8,2),     CONSTRAINT PM_KEY PRIMARY KEY(ENO),     CONSTRAINT CK CHECK (SALARY > 0),     CONSTRAINT CKR CHECK (DEPTNO BETWEEN 10 AND 90) );
Read more

alter table

 The ALTER TABLE command in Oracle Database is used to modify the structure of an existing table . You can use it to add, modify, or drop columns, constraints, or even rename the table itself. 1. Syntax of ALTER TABLE ALTER TABLE table_name [ADD column_definition] [MODIFY column_definition] [DROP COLUMN column_name] [RENAME COLUMN old_column_name TO new_column_name] [ADD CONSTRAINT constraint_definition] [DROP CONSTRAINT constraint_name] [RENAME TO new_table_name]; 2. Key Use Cases of ALTER TABLE A. Adding a Column You can add one or more columns to an existing table. Syntax: ALTER TABLE table_name ADD column_name datatype [constraint]; Example: ALTER TABLE EMP1 ADD phone_number VARCHAR2(15); B. Modifying a Column You can modify the data type, size, or constraints of an existing column. Syntax: ALTER TABLE table_name MODIFY column_name new_datatype [constraint]; Example: ALTER TABLE EMP1 MODIFY NAME VARCHAR2(30) NOT NULL; C. Dropping a Column You...
Read more

DDL create table

Here’s a comprehensive guide to the CREATE TABLE command in Oracle Database, including its syntax, options, constraints, and examples. Purpose of CREATE TABLE The CREATE TABLE command is used to define a new table in the database. It specifies the table name, its columns, data types, constraints, and other attributes. Syntax CREATE TABLE table_name ( column1 datatype [constraint], column2 datatype [constraint], ..., [table_constraint] ); Key Components table_name : Name of the table being created. It must be unique in the schema. column_name : Name of a column in the table. datatype : Data type of the column (e.g., NUMBER , VARCHAR2 , DATE , etc.). constraint : Optional constraints applied to columns (e.g., NOT NULL , PRIMARY KEY ). table_constraint : Constraints at the table level (e.g., PRIMARY KEY , FOREIGN KEY ). Supported Data Types in Oracle Common Data Types: Data Type Description Example NUMBER Stores num...
Read more