我正在上一门课程,遇到了困难。我们需要编写一个与DESCRIBE命令完全相同的Oracle脚本。我们使用的教材非常不好地描述了如何处理数据字典。我并不是要答案,只是想指出正确的方向。
我正在上一门课程,遇到了困难。我们需要编写一个与DESCRIBE命令完全相同的Oracle脚本。我们使用的教材非常不好地描述了如何处理数据字典。我并不是要答案,只是想指出正确的方向。
USER_TAB_COLUMNS
,它可以列出查询所在架构中所有表的列及其描述信息。或者是 ALL_TAB_COLUMNS
,它会列出用户有权限查看的所有表的列和描述信息。select *
from user_tab_columns
where table_name = 'MY_TABLE'
order by column_id
column_id
是表格中列的“顺序”。
除非您一直在使用大小写添加表格(这是个坏主意),否则应确保将'MY_TABLE'大写,否则需要使用类似于= "MyTable"
的内容。
具体来说,desc
等同于我从ss64上窃取的以下内容,该网站是一个很好的Oracle资源:
select column_name as "Name"
, nullable as "Null?"
, concat(concat(concat(data_type,'('),data_length),')') as "Type"
from user_tab_columns
where table_name = 'MY_TABLE';
你可以通过执行 select * from dictionary
命令,在 数据字典 的顶层查找所有这类视图,或者查看文档。
此外还有 DBA_TAB_COLUMNS
,它与 ALL_TAB_COLUMNS
相同,但是包含数据库中每张表的列信息。前提是你对该表有足够的权限。如果你无法访问该表,则需要获取DBA授予你 SELECT ANY DICTIONARY
权限。
你还可以检索用于重新创建表的完整命令:
select dbms_metadata.get_ddl('TABLE','<my table name>','<table owner>') from dual;
select regexp_replace(dbms_metadata.get_ddl('TABLE','<table name>','<table owner>'), '\t|\s{2}', CHR(13)) as ddl from dual
- Andreas CovidiotOracle SQLcl 中新引入了 information
命令或者简单地说是 INFO table_name
。它的语法与 DESC[RIBE]
类似:
SQL> info
INFORMATION
--------
This command is like describe but with more details about the objects requested.
INFO[RMATION] {[schema.]object[@connect_identifier]}
INFO+ will show column statistics
DESCRIBE
相比要出色且描述更加详细。它会列出有关表、视图或同义词的列定义,或函数或过程的规范的更多详细信息。info employees
时,这是我在SQLcl: Release 18.1.1中获得的输出。SQL> info employees;
TABLE: EMPLOYEES
LAST ANALYZED:2018-05-26 15:07:58.0
ROWS :107
SAMPLE SIZE :107
INMEMORY :DISABLED
COMMENTS :employees table. Contains 107 rows. References with departments,
jobs, job_history tables. Contains a self reference.
Columns
NAME DATA TYPE NULL DEFAULT COMMENTS
*EMPLOYEE_ID NUMBER(6,0) No Primary key of employees table.
FIRST_NAME VARCHAR2(20 BYTE) Yes First name of the employee. A not null column.
LAST_NAME VARCHAR2(25 BYTE) No Last name of the employee. A not null column.
EMAIL VARCHAR2(25 BYTE) No Email id of the employee
PHONE_NUMBER VARCHAR2(20 BYTE) Yes Phone number of the employee; includes country
code and area code
HIRE_DATE DATE No Date when the employee started on this job. A not
null column.
JOB_ID VARCHAR2(10 BYTE) No Current job of the employee; foreign key to job_id
column of the jobs table. A not null column.
SALARY NUMBER(8,2) Yes Monthly salary of the employee. Must be greater
than zero (enforced by constraint emp_salary_min)
COMMISSION_PCT NUMBER(2,2) Yes Commission percentage of the employee; Only
employees in sales department elgible for
commission percentage
MANAGER_ID NUMBER(6,0) Yes Manager id of the employee; has same domain as
manager_id in departments table. Foreign key to
employee_id column of employees table.(useful for
reflexive joins and CONNECT BY query)
DEPARTMENT_ID NUMBER(4,0) Yes Department id where employee works; foreign key to
department_id column of the departments table
Indexes
INDEX_NAME UNIQUENESS STATUS FUNCIDX_STATUS COLUMNS
HR.EMP_JOB_IX NONUNIQUE VALID JOB_ID
HR.EMP_NAME_IX NONUNIQUE VALID LAST_NAME, FIRST_NAME
HR.EMP_EMAIL_UK UNIQUE VALID EMAIL
HR.EMP_EMP_ID_PK UNIQUE VALID EMPLOYEE_ID
HR.EMP_MANAGER_IX NONUNIQUE VALID MANAGER_ID
HR.EMP_DEPARTMENT_IX NONUNIQUE VALID DEPARTMENT_ID
References
TABLE_NAME CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE VALIDATED GENERATED
DEPARTMENTS DEPT_MGR_FK NO ACTION ENABLED NOT DEFERRABLE VALIDATED USER NAME
EMPLOYEES EMP_MANAGER_FK NO ACTION ENABLED NOT DEFERRABLE VALIDATED USER NAME
JOB_HISTORY JHIST_EMP_FK NO ACTION ENABLED NOT DEFERRABLE VALIDATED USER NAME
info+
的屏幕截图:
Oracle有一组包含数据库结构元数据的表。有一个表格的表格。视图表。列的表。您可以通过使用视图查询这些表,例如USER_TABLES(模式中的表),ALL_TABLES(您有权限查看的表),DBA_TABLES(所有表,如果您具有特权)。更普遍地,许多数据库供应商支持“信息模式”,该模式提供了跨供应商的元数据的一致视图。在此处搜索“ALL_TABLES”,并查看所有其他可用信息http://docs.oracle.com/cd/B28359_01/server.111/b28320/toc.htm