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:
   

   SET LINESIZE 150;
   SET PAGESIZE 50;
   

   
   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:

   DESCRIBE EMPLOYEES;
   

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.
   

   EXIT;
   

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:

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.

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

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.

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.

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

select dbms_metadata.get_ddl('TABLE','< my table name>','<table owner>') 
from dual;

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.