What is the DDL Command in Oracle? With Examples

DDL Command in Oracle

Database administrators use DDL, a subset of SQL, to define, edit, and destroy database object structures. DDL provides the architectural design for your database, allowing you to set up its core components before adding data. Classic DDL commands like CREATE, ALTER, and DROP affect schema objects like tables, views, and triggers.

DDL statements perform several administrative tasks outside object construction. They let you to grant and revoke privileges and roles, control database set auditing settings, and add data dictionary comments to improve documentation. Before and after executing a DDL statement, Oracle Database implicitly commits any current transaction. When a DDL command runs successfully, its results are permanent and cannot be undone, unlike DML statements.

For instance, an ALTER TABLE statement requires exclusive table access. ALTER will fail if another user has an open transaction on that table. Some DDL procedures can cascade, recompiling or reauthorising schema objects that depend on the updated item. DBMS_SQL packages can programmatically support DDL statements in PL/SQL blocks for more dynamic database management.

In contrast, Data Manipulation Language (DML) commands like SELECT, INSERT, UPDATE, DELETE, and MERGE alter data within schema objects without changing their structure. DML statements can be committed or rolled back by the user because they do not implicitly commit transactions. This DDL/DML distinction is crucial to database operations and transactional integrity.

CREATE TABLE

Database architecture hinges on the CREATE TABLE statement, which defines data storage containers. New tables are created using this DDL command, which specifies their names, columns, data types, and initial constraints.

The basic table syntax is:

CREATE TABLE table_name (
    column_name1 datatype [NOT NULL],
    column_name2 datatype [NOT NULL],
    ...
    column_nameN datatype [NOT NULL],
    [CONSTRAINT constraint_name PRIMARY KEY (column_name)],
    [CONSTRAINT constraint_name CHECK (condition)]
);

Breaking down the essentials:

Table_name: Name your new table. It must be unique in Oracle’s schema and follow its naming conventions.
column_name: Each table has one or more columns, each of which needs a name. These names should describe their data.
Datatype: Configure each column’s data type. To maintain data integrity, performance, and avoid difficulties, choose the right data type. Arithmetic operations are allowed on NUMBER types but not RAW types in Oracle. A common data type is:
- VARCHAR2(size): For variable-length character strings. Set a maximum size (e.g., VARCHAR2(50) for 50 characters). Strings longer than the defined size are stored incorrectly. You can store more than 4000 bytes in LONG (up to 2GB) or CLOB (up to 4GB minus 1 block, or 128TB for bigfile tablespaces).
- CHAR(size): Use CHAR(size) for fixed-length character strings. A shorter string will be padded with spaces to the desired size.
- NUMBER(precision, scale): Stores integers and decimals. Precision is the total number of significant digits, and scale is the ones right of the decimal point. Number(6,2) may hold 1234.56. Max precision is 38 digits, max scale is 127.
- DATE: By default, DATE stores date and time information (day/month/year).
- TIMESTAMP: TIMESTAMP may store date and time with greater precision, including fractional seconds and time zone information.
- Large Object (LOB) Datatypes: Such as CLOB for character data, BLOB for binary data, and BFILE for external binary files, are used for storing very large amounts of unstructured data.
NOT NULL Constraint: A column must always have a value. Oracle errors if you insert a row without a NOT NULL column value. Data quality and business standards, such as never leaving a product name unfilled, depend on this constraint.
Other Constraints: To guarantee data integrity, other constraints are usually defined during table creation:
- PRIMARY KEY: A primary key uniquely identifies each row in a table. Both NOT NULL and UNIQUE limitations apply.
- UNIQUE: All values in a column or collection of columns must be unique, but NULL values are allowed.
- FOREIGN KEY: A foreign key links data in two tables, ensuring referential integrity by requiring values to match in both tables.
- CHECK: Sets and enforces rules for column values. See CHECK (Salary > 0).
TABLESPACE Clause: You can define the tablespace where the table’s data will be kept. Not specified, the table will be created in the user’s default permanent tablespace. For better management, performance, and recovery, DBAs build independent tablespaces for different applications or data types (e.g., HR_DATA, HR_INDEX).
Invisible Columns: Oracle Database 12c allows INVISIBLE columns. The absence of these columns in DESCRIBE output and SELECT * queries allows schema evolution without affecting existing applications.
CREATE TABLE … AS SELECT (CTAS): A powerful variation that generates a new table and populates it with data by performing a SELECT statement against one or more tables or views.

ALTER TABLE

ALTER TABLE is a fundamental DDL command that lets you change the structure of a generated table. Flexibility is needed to change your database structure to changing business needs without dropping and re-creating tables (which would lose data). Tables can be defined by adding, updating, or removing columns and adjusting characteristics.

Common ALTER TABLE operations:

Adding a Column: Use the ADD clause to add a column to a table.
Usually easy. Before adding a NOT NULL column, you must set a default value or update existing rows or the process will fail.
Modifying a Column: The MODIFY clause lets you update a column’s definition. Adjusting its size or data type may be necessary.
Check that no data values exceed the new column size before reducing it. Prior to imposing the NOT NULL constraint, all rows must have a non-null value for the column you’re updating. Under some scenarios, Oracle can convert CHAR to VARCHAR2 and vice-versa.
Dropping a Column: Using the DROP COLUMN clause, you can eliminate a column that is no longer needed.
If the column is a constraint (e.g., a foreign key in another table), the CASCADE CONSTRAINTS option drops all dependent constraints. Oracle Database 12c lets you mark columns as UNUSED and physically dump them during a scheduled downtime to reduce peak-hour effect.
Renaming a Table: The RENAME statement changes a table’s name, however it’s not part of ALTER TABLE.
Renaming a Column: Use the RENAME COLUMN clause to rename a table column.
Renaming Constraints: You can rename constraints.
Enabling/Disabling Constraints: ALTER TABLE can temporarily disable or enable constraints.
Disabling a foreign key constraint in another table before disabling a primary key or unique key constraint is necessary. SQL scripts can automate cascade-delete constraint disabling or enabling.
Management of Tables and Triggers
- ALTER TABLE table_name {ENABLE | DISABLE} TABLE LOCK;: Controls whether DDL operations are allowed on the table.
- ALTER TABLE table_name {ENABLE | DISABLE} ALL TRIGGERS;: Enables or disables all triggers associated with the table.
Storage Properties and Space Management: DBAs can change PCTFREE, PCTUSED, INITRANS, and STORAGE settings using ALTER TABLE. This can also deallocate unused table segment space to free up space. In Oracle Database 10g, the ALTER TABLESPACE command’s RENAME TO clause renames tablespaces. If row movement is enabled and the tablespace employs automated segment space management, the ALTER TABLE…SHRINK SPACE statement can modify the high-water mark for advanced space management.

DROP TABLE

The DDL command DROP TABLE deletes a table including its data, indexes, and constraints.

The basic syntax is straightforward:

DROP TABLE table_name;

Since Oracle Database 10g, DROP TABLE commands do not immediately destroy tables. Instead, it’s in the “recycle bin”. This safety feature recovers dropped tables quickly. For tablespace space reasons, objects stay in the recycling bin until they are expressly purged or automatically eliminated.

Recovering Dropped Tables: Oracle Database 10g and subsequent versions include the FLASHBACK TABLE TO BEFORE DROP command to quickly recover dropped tables.
Permanent Deletion (PURGE Clause): Use the PURGE clause with the DROP TABLE statement to permanently delete a table and free up its space without using the recycling bin.
PURGE insures permanent removal of the table from the recycle bin.
Remember that dropping a table commits the transaction permanently. The only method to undo an index decrease is to rebuild it. Using DROP TABLE in production contexts requires significant caution. Dropping and re-creating a table makes it unavailable until all grants are reissued, therefore it’s only allowed in development or test settings.

Creating a Table and Inserting Values

Give a concrete example to demonstrate these ideas. Create a STUDENTS table, specify its fields with datatypes and a NOT NULL constraint, then insert values.

Creating the STUDENTS Table

We’ll create a table named STUDENTS to store student information. It will include student_id (a unique identifier), first_name, last_name, enrollment_date, and major. We’ll ensure that student_id, first_name, and last_name cannot be left empty.

CREATE TABLE STUDENTS (
    student_id      NUMBER(6)       NOT NULL,
    first_name      VARCHAR2(50)    NOT NULL,
    last_name       VARCHAR2(50)    NOT NULL,
    enrollment_date DATE,
    major           VARCHAR2(100),
    CONSTRAINT pk_student_id PRIMARY KEY (student_id)
);

Inserting Values into the STUDENTS Table

INSERT DML will add student records to our new table. DML INSERT statements require a COMMIT to make changes permanent.

INSERT INTO STUDENTS (student_id, first_name, last_name, enrollment_date, major)
VALUES (100001, 'Alice', 'Smith', TO_DATE('2023-09-01', 'YYYY-MM-DD'), 'Computer Science');

INSERT INTO STUDENTS (student_id, first_name, last_name, enrollment_date, major)
VALUES (100002, 'Bob', 'Johnson', TO_DATE('2023-09-01', 'YYYY-MM-DD'), 'Electrical Engineering');

INSERT INTO STUDENTS (student_id, first_name, last_name, major)
VALUES (100003, 'Charlie', 'Brown', 'History');

COMMIT;

Displaying the Table Contents

To view the data we’ve inserted, we use the SELECT DML statement.

SELECT * FROM STUDENTS;

Output:

STUDENT_ID FIRST_NAME LAST_NAME  ENROLLMENT_DATE MAJOR
---------- ---------- ---------- --------------- --------------------------------
    100001 Alice      Smith      01-SEP-23       Computer Science
    100002 Bob        Johnson    01-SEP-23       Electrical Engineering
    100003 Charlie    Brown                      History

This detailed overview and example should help you grasp Oracle DDL statements for table creation and management.

Page Content

Tutorials