| Lesson 10 | The DESCRIBE Command |
| Objective |
Oracle DESCRIBE command to show table structure using SQL* Plus |
Oracle DESCRIBE Command using SQL*PLus
Viewing a list of Columns
Server Manager has been deprecated since Oracle 9i, and a DBA has to use SQL*Plus instead 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.
Syntax
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:
- each column's name
- whether or not null values are allowed (NULL or NOT NULL) for each column
- datatype of columns, for example, CHAR, DATE, LONG, LONGRAW, NUMBER, RAW, ROWID, VARCHAR2 (VARCHAR), or XMLType
- 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:
- 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
- the ENCRYPT keyword to indicate whether or not data in a column is encrypted
SVRMGR> 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.
SVRMGR> 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:
SVRMGR> DESCRIBE utl_file.fflush
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.
