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
-
Log in to SQL*Plus:
-
Run the
DESCRIBE
Command:
Additional Notes
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:
- USERNAME: The name of the database user.
- CREATED: The date and time when the database user was created.
- DEFAULT_TABLESPACE: The default tablespace for the database user.
- TEMPORARY_TABLESPACE: The temporary tablespace for the database user.
- STATUS: The status of the database user. Possible values include: OPEN, CLOSED, LOCKED, and EXPIRED.
- PROFILE: The profile for the database user.
- PASSWORD_LIFE_TIME: The number of days before the database user's password expires.
- CREATION_TIME: The date and time when the database user was created.
- RESOURCE_LIMIT: The resource limit for the database user.
- PASSWORD_VERSIONS: The number of versions of the database user's password that are stored.
- EDITIONS_ENABLED: The editions that the database user is enabled to use.
- AUTHENTICATION_TYPE: The authentication type for the database user. Possible values include: PASSWORD, EXTERNAL, and KERBEROS.
- 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]}
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
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;