DB Creation   «Prev  Next»

Lesson 10The DESCRIBE Command
Objective Oracle DESCRIBE command to show table structure using SQL* Plus

Oracle DESCRIBE Command using SQL*PLus

The Oracle DESCRIBE command is a powerful and essential tool for database administrators and developers to inspect the structure of database objects, including tables. In SQL*Plus, Oracle’s interactive command-line interface, the DESCRIBE command can be employed to display the metadata of a table, revealing details such as column names, data types, and constraints. To effectively use the DESCRIBE command in SQL*Plus to reveal the structure of a table, follow these guidelines:
  1. Connect to the Database: Ensure that you are connected to the Oracle Database using SQL*Plus. You should log in with a user account that has sufficient privileges to access the table you intend to describe.
    sqlplus username/password@connect_identifier
  2. Set the SQL*Plus Environment (Optional): You may choose to configure the SQL*Plus environment to enhance the display of the DESCRIBE command’s output. For example:

    This configuration adjusts the line size and page size of the SQL*Plus session, ensuring that the output is presented in a readable format.
  3. Execute the DESCRIBE Command: To display the structure of a table, use the DESCRIBE command followed by the table name. The basic syntax is:
    DESCRIBE table_name;

    For instance, if you want to describe a table named `EMPLOYEES`, the command would be:
  4. Interpret the Output: The output of the DESCRIBE command will list the columns of the table, along with their data types, and any constraints or defaults that are applied to the columns. Here’s how to interpret the key components of the output:
    • Column Name: The name of the column in the table.
    • Data Type: The data type of the column, which could be numeric, character, date, etc.
    • Nullable: Indicates whether the column can contain null values. A value of ‘Yes’ denotes that null values are allowed, while ‘No’ denotes that they are not.
    • Default: If a default value is set for the column, it will be displayed here.
  5. Exit SQL*Plus (Optional): Once you have obtained the required information, you can exit SQL*Plus using the EXIT or QUIT command.
The DESCRIBE command in SQL*Plus is an invaluable tool for examining the structure of tables within an Oracle Database. By following the outlined steps, database professionals can effortlessly procure a comprehensive understanding of table configurations, aiding in database management, troubleshooting, and development endeavors. It is imperative to maintain adherence to best practices and security protocols, ensuring that access to database structures is managed judiciously and responsibly. By doing so, the integrity and security of the database environment are upheld, fostering a robust and efficient data management ecosystem.

Viewing a list of Columns

A DBA can use SQL*Plus to execute the DESCRIBE command to show the structure of a table. DESCRIBE is usually used to view a list of columns in a database table or view. The following example shows how it would be used to list the column definitions for the DBA_USERS view.
Question: How do I use the DESCRIBE command to show the structure of a table for the DBA_USERS view?
To use the Oracle DESCRIBE command to show the structure of a table for the DBA_USERS view, you would type the following command at the SQL*Plus prompt:

This would display a list of all of the columns in the DBA_USERS view, along with their data types and other information.
Here is an example of the output of the DESCRIBE DBA_USERS command:
Output of the DESCRIBE DBA_USERS command
Name Null? Type

14 rows selected.

This output shows that the DBA_USERS view has 14 columns. The columns are:
  1. USERNAME: The name of the database user.
  2. CREATED: The date and time when the database user was created.
  3. DEFAULT_TABLESPACE: The default tablespace for the database user.
  4. TEMPORARY_TABLESPACE: The temporary tablespace for the database user.
  5. STATUS: The status of the database user. Possible values include: OPEN, CLOSED, LOCKED, and EXPIRED.
  6. PROFILE: The profile for the database user.
  7. PASSWORD_LIFE_TIME: The number of days before the database user's password expires.
  8. CREATION_TIME: The date and time when the database user was created.
  9. RESOURCE_LIMIT: The resource limit for the database user.
  10. PASSWORD_VERSIONS: The number of versions of the database user's password that are stored.
  11. EDITIONS_ENABLED: The editions that the database user is enabled to use.
  12. AUTHENTICATION_TYPE: The authentication type for the database user. Possible values include: PASSWORD, EXTERNAL, and KERBEROS.
  13. LAST_LOGIN: The date and time of the database user's last login.

You can use the DESCRIBE command to show the structure of any table or view in Oracle Database. This can be useful for understanding the data that is stored in a table or view, and for troubleshooting problems with SQL queries.

Syntax for the Oracle DESCRIBE Command

DESC[RIBE] {[schema.]object[@db_link]}

Lists the column definitions for the specified table, view or synonym, or the specifications for the specified function or procedure.


schema: Represents the schema where the object resides. If you omit schema, SQL*Plus assumes you own object.
object: Represents the table, view, type, procedure, function, package or synonym you wish to describe.
@db_link: Consists of the database link name corresponding to the database where object exists. For more information on which privileges allow access to another table in a different schema, refer to the Oracle Database SQL Reference.


The description for tables, views, types and synonyms contains the following information:
  1. each column's name
  2. whether or not null values are allowed (NULL or NOT NULL) for each column
  3. datatype of columns, for example, CHAR, DATE, LONG, LONGRAW, NUMBER, RAW, ROWID, VARCHAR2 (VARCHAR), or XMLType
  4. precision of columns (and scale, if any, for a numeric column)
When you do a DESCRIBE, VARCHAR columns are returned with a type of VARCHAR2.
The DESCRIBE command enables you to describe objects recursively to the depth level set in the SET DESCRIBE command. You can also display the line number and indentation of the attribute or column name when an object contains multiple object types. For more information, see the SET command. To control the width of the data displayed, use the SET LINESIZE command. Columns output for the DESCRIBE command are typically allocated a proportion of the linesize currently specified. Decreasing or increasing the linesize with the SET LINESIZE command usually makes each column proportionally smaller or larger. This may give unexpected text wrapping in your display. For more information, see the SET command.
The description for functions and procedures contains the following information:
  1. the type of PL/SQL object (function or procedure)
  2. the name of the function or procedure
  3. the type of value returned (for functions)
  4. the argument names, types, whether input or output, and default values, if any
  5. the ENCRYPT keyword to indicate whether or not data in a column is encrypted

SQL> DESCRIBE dba_users

The DBA_USERS view is a data dictionary view that returns information about the users who can log in to an Oracle database.

Viewing function, procedure, and package headers

The DESCRIBE command can also be used to view function, procedure, and package headers. The following command for example, would tell you about all the entry points in the UTL_FILE package, which is used to read and write files from within stored procedures.

SQL> DESCRIBE utl_file

You can also retrieve the entire command that can be used to recreate the table:
select dbms_metadata.get_ddl('TABLE','< my table name>','<table owner>') 
from dual;

Viewing more specific information

You can also retrieve information on a specific procedure or function in a package. Just name the procedure or function as a parameter to the DESCRIBE command.
A period is used to separate the specific procedure or function name from the package name, as shown in the following example:

SQL> DESCRIBE utl_file.fflush

Go ahead and try these these commands yourself, using the simulation below.
Ora Describe Command
Now that you have seen a simulation of how the DESCRIBE command can work, those of you who are running SQL*Plus or SQL Developer should try it out yourself.