Oracle | DDL: Data Definition Language

Oracle Data Definition Language (DDL) Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

DDL Statements

ALTER	CREATE	IF [NOT] EXISTS
ANALYZE	CREATE OR REPLACE	RENAME
COMMENT	DROP	TRUNCATE

Note To see examples of these DDL statements in the specific context of creating or altering specific object types follow the links at page-bottom for that type of object.

DDL_LOCK_TIMEOUT

Lock Timeout ALTER <SESSION | SYSTEM> DDL_LOCK_TIMEOUT(<time_in_seconds>);

conn / as sysdba

      

      set linesize 121

      col name format a30

      col value format a30

      

      SELECT name, value

      FROM gv$parameter

      WHERE name LIKE 'ddl_lock%';

      

      -- range is 0 (the default) to 100,000

      ALTER SYSTEM SET ddl_lock_timeout=20;

      -- or

      ALTER SESSION SET ddl_lock_timeout=20;

Lock Timeout Demo

conn uwclass/uwclass@pdbdev

      

      CREATE TABLE test1 AS

      SELECT table_name, tablespace_name

      FROM user_tables;

      
      
      
        
          Step
          Session 1
          Session 2
        
        
          1
          conn uwclass/uwclass@pdbdev
          conn uwclass/uwclass@pdbdev
        
        
          2
          desc test1

          

          SELECT * FROM test1;
          desc test1

          

          SELECT * FROM test1;
        
        
          3
          LOCK TABLE test1

          IN exclusive MODE nowait;
          RENAME test1 TO test2;
        
        
          4
          COMMIT;
          RENAME test1 TO test2;
        
        
          5
           
          ALTER SYSTEM SET ddl_lock_timeout=60;

          

          SELECT name, value

          FROM gv$parameter

          WHERE name LIKE 'ddl_lock%';
        
        
          6
          LOCK TABLE test2

          IN exclusive MODE nowait;
          RENAME test2 TO test3;
        
        
          7
          COMMIT;
           
        
      
      
      ALTER SYSTEM SET ddl_lock_timeout=0;

      

      SELECT name, value

      FROM gv$parameter

      WHERE name LIKE 'ddl_lock%';

IF [NOT] EXISTS

Caution If auditing actions test, carefully, the impact of this syntax before using because, in some cases, it may write an audit record saying that an action took place that, in fact, did not.

ALTER ALTER <OBJECT_TYPE> IF [NOT] EXISTS <ACTION>

ALTER TABLE IF EXISTS 
      test ADD (newcol DATE);

CREATE CREATE [OR REPLACE] <OBJECT_TYPE> IF [NOT] EXISTS

CREATE USER IF NOT 
      EXISTS c##test IDENTIFIED BY We!c0me2;

      

      CREATE VIEW IF NOT EXISTS testview AS

      SELECT * FROM test;

      

      -- avoids overwriting a previous version

      CREATE OR REPLACE PROCEDURE IF NOT EXISTS 
      testproc IS

      BEGIN

  dbms_output.put_line('It Didn't Exist');

      END testproc;

      /

DROP DROP <OBJECT_TYPE> IF [NOT] EXISTS

DROP USER IF EXISTS 
      c##test CASCADE;

      

      DROP VIEW IF EXISTS testview;

      

      DROP PROCEDURE IF EXISTS testproc;