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.

In Oracle 19c, the `DESCRIBE` command (or its shorthand `DESC`) is used to display the structure of a table, view, or other database objects. To show the structure of the `DBA_USERS` view, you can use the following command:

DESC DBA_USERS;

Explanation:

• DESC is a shorthand for DESCRIBE.
• DBA_USERS is a predefined Oracle data dictionary view that contains information about all users in the database.

Output: The output will display the following columns for the `DBA_USERS` view:

• Column Name: The name of the column in the view.
• Null?: Indicates whether the column allows null values (Y for yes, N for no).
• Type: The data type of the column (e.g., VARCHAR2, NUMBER, DATE).

Example Output:

Name           Null?    Type
-------------- -------- ------------
USERNAME       NOT NULL VARCHAR2(128)
USER_ID        NOT NULL NUMBER
PASSWORD                VARCHAR2(4000)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)

Notes:

1. Permissions: To query the DBA_USERS view, you must have the appropriate privileges, such as SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE.
2. Scope: The DBA_USERS view contains information about all users in the database. If you want information about the current user, you can use the USER_USERS view instead.

If you don't have access to the `DBA_USERS` view, you can check your privileges or contact your database administrator.

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
  
  1. schema: Represents the schema where the object resides. If you omit schema, SQL*Plus assumes you own object.
  2. object: Represents the table, view, type, procedure, function, package or synonym you wish to describe.
  3. @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;

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 examples below.
Now that you have seen the theory of how the DESCRIBE command can work, those of you who are running SQL*Plus or SQL Developer should try it out yourself.

• Oracle DESCRIBE Command
  
  1. Enter DESCRIBE dba_users on the last line, and then press Enter.
  2. Enter DESCRIBE utl_field on the last line and press Enter.
  3. Enter DESCRIBE utl_file.fflush on the last line and press enter.
  4. 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 

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,