DDL (stands for Data Definition Language) statements are used for objects creation. I might write a book on this topic because there are many different objects in a database and every object has its own specifics. Not only there is a specific definition per object but also various ways how to define the object. I will not describe every single possibility and elaborate on every option you have for each object. Instead, I will focus on basic definition with some elementary recommendations and I will point out basic mistakes I noticed people tend to do.
All DDL commands do NOT support transactions! That means they are auto-committed and every change is permanently written.
I listed all DDL commands in the very first article about DBMS and other relational models and I will now pick the most commonly used. Regarding the rest, I might write DDL statements II and familiarize you with them there 🙂
Here, in this article I will focus on the following list of commands:
- Alter / Rename
I mentioned that Oracle has many different objects. I will limit this article to the most commonly used objects by analysts and developers to:
I will briefly but still sufficiently describe each DDL with regard to the list of objects mentioned above. The rest of objects will have its own dedicated articles.
CREATE command will help you create all objects. Let me show you several examples of usage.
You can create an empty table structure as well as an empty structure that will be automatically populated with data.
-- Empty structure creation CREATE TABLE my_table ( id INT NOT NULL, name VARCHAR2(50), desc VARCHAR2(255), dtime_inserted DATE DEFAULT sysdate NOT NULL );
There are many other options you can use to create the table properly or to enforce data integrity. You can see them in the article about Constraints.
-- CTAS (Create Table As Select) CREATE TABLE my_table AS SELECT id , name , desc , sysdate FROM source_table;
This way is really effective and as long as you already have the data inside the database I prefer using this approach. You don’t have to define any data types and everything is automatically created. Besides, it does not generate UNDO and therefore is way faster and resource friendly, compared to CREATE and INSERT combination. You can define your constraints and do the other configuration tasks rights after. If you still prefer to do that right away, you can easily do that this way
-- CTAS (Create Table As Select) CREATE TABLE my_table (id, name, desc NOT NULL, dtime_inserted DEFAULT sysdate NOT NULL) AS SELECT id , name , desc , sysdate FROM source_table;
-- view creation CREATE OR REPLACE VIEW v_data_set AS SELECT * FROM my_source_table ;
CREATE INDEX idx_name ON my_table(column_name);
ALTER command will help you change the structure of your objects.
-- add column ALTER TABLE my_table ADD col1 DATE; -- add column with constraints ALTER TABLE my_table ADD col1 DATE DEFAULT sysdate; -- add multiple columns ALTER TABLE my_table ADD ( col1 DATE, col2 VARCHAR2(50) DEFAULT 'XNA', col3 INT); -- change column definition (col1 originaly varchar2(50)) -- you might get an error if you wanna apply incompatible data types or use constraint that can't be enforced (already have NULL values, etc) -- you have to first fix the data inside the table -- you can as well modify multiple columns as well the same way as you can add multiple columns ALTER TABLE my_table MODIFY col1 varchar2(100) NOT NULL; -- drop column ALTER TABLE my_table DROP col1; -- rename column ALTER TABLE my_table RENAME COLUMN col1 TO col_1; -- rename table ALTER TABLE my_table RENAME TO my_table_new; -- but it's better to use the RENAME command RENAME my_table TO my_table_new; -- I prefer this one
Drop command removes the object from the database.
-- general template DROP <object_type> <object_name> -- table DROP TABLE my_table; -- view DROP VIEW my_view; -- index DROP INDEX my_index;
The truncate command deletes the content of a table. See the difference between truncate and delete commands.
|DML command||DDL command (no triggers can be fired – non SYS; SYS triggers are fired)|
|Can be rolled back (undone)||Cannot be rolled back|
|Can use WHERE clause||Cannot use WHERE clause|
|Requires commit||Does not require commit (all changes are automatically written)|
|Worse performance||Minimal logging in a transcation log (better performance)|
|Anyone can perform (if granted)||Only owner can perform|
|Uses row-lock||Uses table-lock|
|Identity key is not reset||Identity key is reset to the initial defined values|
|Does not reset HWM||Resets HWM|