Oracle DESCRIBE (DESC): Structure at a Glance

DESCRIBE (abbrev. DESC) is a client command in SQL*Plus/SQLcl (and supported by many Oracle IDEs) that prints the structure of an object: tables, views, synonyms, sequences, types, procedures/functions, and package specs. It is not a SQL statement executed by the optimizer—it’s a convenience feature of your client.

You’ll typically pair DESC with data-dictionary queries (USER_/ALL_/DBA_* views) and, when you need full DDL, DBMS_METADATA.GET_DDL. This page shows accurate usage patterns, common pitfalls, and production-ready snippets.

-- General form
DESC[RIBE] [schema.]object[@dblink]

\-- Examples
DESC employees
DESC hr.employees
DESC user\_tab\_privs
DESC SYS.DBMS\_LOCK
DESC hr.emp\_pkg                                     -- package spec
DESC hr.emp\_pkg.hire\_employee                       -- procedure/function in a package
DESC "CaseSensitiveTable"                           -- quoted identifiers must match case 

• Resolution: If you DESC a synonym, the client resolves the target if you have privilege; otherwise you’ll see an error.
• Privileges: You need access to the object (e.g., SELECT on a table) to see details. For dictionary views you may need roles such as SELECT_CATALOG_ROLE.
• Shared Server? No special steps—DESC is client-side; shared vs. dedicated server does not change behavior.

Tables and Views

DESC hr.employees
-- Output (typical):
-- Name           Null?    Type
-- -------------- -------- ----------------------------
-- EMPLOYEE_ID    NOT NULL NUMBER(6)
-- FIRST_NAME              VARCHAR2(20)
-- LAST_NAME      NOT NULL VARCHAR2(25)
-- HIRE_DATE      NOT NULL DATE
-- ...

Procedures / Functions / Packages

DESC hr.emp_pkg.hire_employee
-- Shows argument names, modes (IN/OUT), and datatypes from the package spec.

Types (Object, Varray, Nested Table)

DESC hr.address_t
-- Lists attributes and their datatypes.

Use dictionary views for filters, joins, and policy-safe reporting. Swap USER_ for ALL_ or DBA_ as needed.

Columns and Data Types

SELECT column_id,
       column_name,
       data_type ||
       COALESCE('(' || data_precision || ',' || data_scale || ')',
                CASE WHEN data_length IS NOT NULL AND data_type LIKE '%CHAR%'
                     THEN '(' || data_length || ')'
                END) AS data_type_fmt,
       nullable
FROM   user_tab_columns
WHERE  table_name = 'EMPLOYEES'
ORDER  BY column_id;

Primary/Unique Keys

SELECT c.constraint_name,
       LISTAGG(col.column_name, ', ') WITHIN GROUP (ORDER BY col.position) AS columns
FROM   user_constraints c
JOIN   user_cons_columns col
  ON   col.constraint_name = c.constraint_name
WHERE  c.table_name = 'EMPLOYEES'
AND    c.constraint_type IN ('P','U')
GROUP  BY c.constraint_name;

Indexes

SELECT i.index_name,
       i.uniqueness,
       LISTAGG(ic.column_name, ', ') WITHIN GROUP (ORDER BY ic.column_position) AS columns
FROM   user_indexes i
JOIN   user_ind_columns ic
  ON   ic.index_name = i.index_name
WHERE  i.table_name = 'EMPLOYEES'
GROUP  BY i.index_name, i.uniqueness;

Procedure/Function Arguments

SELECT position,
       argument_name,
       in_out,
       data_type,
       data_length,
       data_precision,
       data_scale
FROM   all_arguments
WHERE  owner = 'HR'
AND    package_name = 'EMP_PKG'
AND    object_name  = 'HIRE_EMPLOYEE'
ORDER  BY position;

Full DDL (Tables, Indexes, Views, etc.)

SET LONG 100000
SELECT DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES','HR') AS ddl
FROM   dual;

If DESC name succeeds but you want to see the target:

SELECT owner, table_name
FROM   all_synonyms
WHERE  synonym_name = 'EMP'
AND    owner IN (USER, 'PUBLIC');

For remote objects, append @dblink if your client supports it and you have privileges:

DESC hr.employees@prod_link

SPOOL describe_employees.lst
DESC hr.employees
SPOOL OFF

Use SPOOL when you need to attach results to a ticket or audit trail.

• “Object does not exist” but a SELECT works: You may be seeing a synonym shadowing or a different owner. Prefix with the owner: DESC HR.EMPLOYEES. Check ALL_SYNONYMS.
• Quoted identifiers: If an object was created with double quotes and mixed case, you must match case: DESC "Employee".
• Expecting SQL syntax help from HELP: HELP in SQL*Plus documents SQL*Plus commands (e.g., HELP SPOOL, HELP DESCRIBE), not full SQL grammar. For SQL semantics use the SQL Language Reference.
• Needing constraints/indexes: DESC doesn’t show them; use the dictionary views shown above.
• Privileges: Missing metadata usually indicates missing object privileges or catalog roles. Coordinate with your DBA security policy.

1. Use DESC for fast inspection (columns, nullability, types).
2. Query USER_/ALL_/DBA_ views for keys, indexes, grants, and arguments.
3. Use DBMS_METADATA.GET_DDL for exact DDL.
4. Resolve synonyms and owners when the name is ambiguous.
5. Spool results for reviews and change records.