Oracle DDL Commands

The COMMENT Command

COMMENT adds comments to table, view or column-level data dictionary objects.

COMMENT ON COLUMN <tablename>.<columnname> IS 'This is a comment';

The DESCRIBE Command

The DESCRIBE command allows description and display of database object structure.

DESC[RIBE] [<user_name>.]<table_name>

Below is an example of the DESCRIBE command.

SQL> describe emp;
 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPNO                           NOT NULL NUMBER(4)
 ENAME                                    VARCHAR2(10)
 JOB                                      VARCHAR2(9)
 MGR                                      NUMBER(4)
 HIREDATE                                 DATE
 SAL                                      NUMBER(7,2)
 COMM                                     NUMBER(7,2)
 DEPTNO                                   NUMBER(2)

Another interesting command of similar ilk to that of the DESCRIBE command is the DEFINE command as shown in the example below.

SQL> define test = number;
SQL> define test;
DEFINE TEST            = "number" (CHAR)

TRUNCATE

The TRUNCATE command allows deletion of records from a table without logging and thus is faster than a delete command but is also non-recoverable, ie. TRUNCATE cannot be rollback back. Also TRUNCATE preserves table structure as opposed to dropping the entire table.

SET TRANSACTION

The SET TRANSACTION command affects the current session (user) transaction and any settings set are removed when a COMMIT or ROLLBACK is issued.

• ISOLATION LEVEL.
  • SERIALIZABLE - DML serialisation implies that any other DML statement updating anything the current serialised transaction is attempting to update will cause the current DML transaction to fail, ie. the current transaction does not wait for completion of other lock conflicting transactions already possessing locks.
  • READ COMMITED - this is the default. The current transaction will wait until release of row locks held by other transactions.
• USE ROLLBACK SEGMENT - allows the assignment of the current transaction to a current rollback segment. Since better efficiency is gained by using rollback segments within a database of consistent sizes it is possibly better when assigning of large transaction to large rollback segments that those large rollback segments should be normally held offline and only placed online as required, ie. switched online immediately prior to use of the SET TRANSACTION command and then placed OFFLINE again after completion of the current transaction. Normally Oracle will automatically select the next available rollback segment in a round-robin fashion regardless off rollback segment size. Differing rollback segment sizes is completely non-sensical unless using the SET TRANSACTION command to specifically assign transactions to specific rollback segments.

Other SQL Commands

• PL/SQL - administering of PL/SQL stored procedures, functions, triggers and packages.
• Data Types - user-defined data type definitions.
• Contexts - context namespace creation.
• Directories - an alias for a physical operating system directory generally for storage of external objects such as BFILE LOB objects.
• Dimensions - defines parent-child relationships between pairs of column sets.
• Libraries - an alias for associating a schema with an external operating system shared library.
• Java - java objects management.
• Operators - user-defined operators can operate on database objects.
• Outlines - a stored outline which is used by the optimiser.
• Snapshots and Materialised Views - a snapshot is a table containing query results. Snapshot implies a frozen picture of a set of data at a specific point in time.