Oracle DESCRIBE Command

Enter DESCRIBE dba_users on the last line, and then press Enter.
Enter DESCRIBE utl_field on the last line and press Enter.
Enter DESCRIBE utl_file.fflush on the last line and press enter.
You have now seen how the DESCRIBE command can retrieve information for you.

DESC[RIBE] (SQL*Plus command)
Describe an Oracle Table, View, Synonym, package or Function.
Note that because this is a SQL*Plus command you don't need to terminate it with a semicolon.
Syntax:

 DESC table
 DESC view
 DESC synonym
 DESC function
 DESC package

In Oracle 7 you could describe individual procedures e.g. desc DBMS_UTILITY.GET_PARAMETER_VALUE In Oracle 8/9/10 you can only describe the whole package: desc DBMS_UTILITY. It is also possible to describe objects in another schema or via a database link (i.e.)

DESCRIBE user.table@db_link

Describe Objects Recursively

The DESCRIBE command allows you to describe objects recursively to the depth level set in the SET DESCRIBE command. For example use the SET commands:

SET LINESIZE 80
SET DESCRIBE DEPTH 2
SET DESCRIBE INDENT ON
SET DESCRIBE LINE OFF

To display these settings use:
SHOW DESCRIBE

Data Types

The description for functions and procedures contains the type of PL/SQL object (function or procedure) the name of the function or procedure, the type of value returned (for functions) the argument names, types, whether input or output, and default values, if any. DESC user.object_name will always identify a distinct database object because a user's database objects must have unique names. i.e. you cannot create a FUNCTION with the same name as a TABLE in the same schema.

Data Dictionary

An alternative to the DESC command is selecting directly from the data dictionary:

DESC MY_TABLE

is equivalent to

SELECT 
column_name "Name", 
nullable "Null?",
concat(concat(concat(data_type,'('),data_length),')') "Type"
FROM user_tab_columns
WHERE table_name='TABLE_NAME_TO_DESCRIBE';

Column Comments

To view column comments:

SELECT comments
FROM user_col_comments 
WHERE table_name='MY_TABLE';

SELECT 'comment on column 
'||table_name||'.'||column_name||' 
is '''||comments||''';' 
FROM user_col_comments
WHERE comments is not null;

Writing code and find yourself typing in a series of column names?
Why bother when it's all available in the data dictionary.
The script below will help out:

COL.SQL

List all the columns of a table.

select chr(9)||lower(column_name)||',' 
from USER_tab_columns 
where table_Name = UPPER('&1')

So now if you want a list of the columns in the EMP table simply type:

@col emp

This will produce a list of columns:

empno, 
ename, 
job, 
mgr, 
hiredate, 
sal, 
comm, 
deptno,