Oracle | DDL: Data Definition Language

Oracle Data Definition Language (DDL) Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library

DDL Statements

ALTER	CREATE	RENAME
ANALYZE	DROP	TRUNCATE
COMMENT

DDL_LOCK_TIMEOUT

Lock Timeout DDL_LOCK_TIMEOUT(<number_of_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%';