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

In "Oracle 19c", you can use the `DESCRIBE` command in SQL*Plus to view the structure of a table.
This command provides details about the table's columns, data types, and constraints.
Syntax
DESC table_name;
or
DESCRIBE table_name;

Steps to Use `DESCRIBE` in SQL*Plus
  1. Log in to SQL*Plus:
    • Open a terminal or command prompt and start SQL*Plus.
    • Connect to the database using your credentials:
      sqlplus username/password@database
      
    • If you're using a pluggable database (PDB), you may need to connect like this:
      sqlplus username/password@pdb_service_name
      
  2. Run the DESCRIBE Command:
    • Once logged in, execute the following command to check the structure of a specific table:
                DESC employees;
              
    • Example Output:
                Name             Null?    Type
                -------------- -------- -----------------
                EMPLOYEE_ID    NOT NULL NUMBER(6)
                FIRST_NAME             VARCHAR2(20)
                LAST_NAME      NOT NULL VARCHAR2(25)
                EMAIL          NOT NULL VARCHAR2(50)
                HIRE_DATE      NOT NULL DATE
                SALARY                 NUMBER(8,2)
              

Additional Notes
  • If the table is in another schema, specify the schema name:
          DESC hr.employees;
        
  • For views, DESC works the same way:
          DESC all_users;
        
  • If the table doesn't exist, you will see an error:
          ERROR:
          Object EMPLOYEES does not exist.
        



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.
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:
DESCRIBE DBA_USERS;

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
USERNAME NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
DEFAULT_TABLESPACE VARCHAR2(30)
TEMPORARY_TABLESPACE   VARCHAR2(30)
STATUS NOT NULL VARCHAR2(15)
PROFILE   VARCHAR2(30)
PASSWORD_LIFE_TIME NUMBER
CREATION_TIME NOT NULL DATE
RESOURCE_LIMIT NUMBER
PASSWORD_VERSIONS   NUMBER
EDITIONS_ENABLED   VARCHAR2(15)
AUTHENTICATION_TYPE   VARCHAR2(15)
LAST_LOGIN   DATE

 
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.
  • Terms
    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.
  • Usage
    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.

SEMrush Software 10 SEMrush Banner 10