在日常的 Oracle 数据库运维工作中，查询表、视图及其字段信息是最基础且最频繁的操作之一。不同权限等级（如 DBA 权限与普通用户权限）下，能够访问的数据字典视图有所不同，对应的 SQL 语句自然也存在差异。以下整理了多种常见场景下的查询脚本，兼顾了 DBA 与普通用户两种权限级别，便于各位根据实际需求直接取用。

1. Oracle 获取表的 SQL 语句

1.1 拥有 DBA 权限时的查询方式

select distinct TABLE_NAME from dba_tables where OWNER ='库名' and  owner NOT IN ('SYS','SYSTEM','OUTLN','XDB','WMSYS','CTXSYS','MDSYS')

1.2 普通用户权限下的查询方式

select distinct TABLE_NAME from all_tables where OWNER ='库名' and  owner NOT IN ('SYS','SYSTEM','OUTLN','XDB','WMSYS','CTXSYS','MDSYS')

2. Oracle 获取表字段的 SQL 语句

2.1 拥有 DBA 权限时的查询方式

SELECT UPPER(T.TABLE_NAME),NVL(cc.comments, ''),C.COLUMN_NAME,CC.COMMENTS,C.DATA_TYPE,C.DATA_LENGTH,C.DATA_PRECISION,C.DATA_SCALE ,NULLABLE,CASE WHEN PK.CONSTRAINT_TYPE = 'P' THEN 'y'  ELSE 'n'  END FROM DBA_tables T JOIN DBA_TAB_COLUMNS C ON T.TABLE_NAME = C.TABLE_NAME AND T.OWNER = C.OWNER LEFT JOIN DBA_COL_COMMENTS CC ON C.OWNER = CC.OWNER AND C.TABLE_NAME = CC.TABLE_NAME AND C.COLUMN_NAME = CC.COLUMN_NAME LEFT JOIN     (SELECT CONS.TABLE_NAME, CONS.OWNER, COLS.COLUMN_NAME, CONS.CONSTRAINT_TYPE     FROM DBA_CONSTRAINTS CONS      JOIN DBA_CONS_COLUMNS COLS ON CONS.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME      WHERE CONS.CONSTRAINT_TYPE = 'P'    ) PK ON C.OWNER = PK.OWNER AND C.TABLE_NAME = PK.TABLE_NAME AND C.COLUMN_NAME = PK.COLUMN_NAME    where UPPER(T.TABLE_NAME) in ( 逗号分隔的表名 ) and t.OWNER NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'XDB', 'WMSYS', 'CTXSYS', 'MDSYS') ORDER BY T.TABLE_NAME, C.COLUMN_ID

2.2 普通用户权限下的查询方式

SELECT UPPER(T.TABLE_NAME),NVL(cc.comments, ''),C.COLUMN_NAME,CC.COMMENTS,C.DATA_TYPE,C.DATA_LENGTH,C.DATA_PRECISION,C.DATA_SCALE ,NULLABLE,CASE WHEN PK.CONSTRAINT_TYPE = 'P' THEN 'y'  ELSE 'n'  END FROM all_tables T JOIN ALL_TAB_COLUMNS C ON T.TABLE_NAME = C.TABLE_NAME AND T.OWNER = C.OWNER LEFT JOIN ALL_COL_COMMENTS CC ON C.OWNER = CC.OWNER AND C.TABLE_NAME = CC.TABLE_NAME AND C.COLUMN_NAME = CC.COLUMN_NAME LEFT JOIN     (SELECT CONS.TABLE_NAME, CONS.OWNER, COLS.COLUMN_NAME, CONS.CONSTRAINT_TYPE     FROM ALL_CONSTRAINTS CONS      JOIN ALL_CONS_COLUMNS COLS ON CONS.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME      WHERE CONS.CONSTRAINT_TYPE = 'P'    ) PK ON C.OWNER = PK.OWNER AND C.TABLE_NAME = PK.TABLE_NAME AND C.COLUMN_NAME = PK.COLUMN_NAME    where UPPER(T.TABLE_NAME) in ( 逗号分隔的表名 ) and t.OWNER NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'XDB', 'WMSYS', 'CTXSYS', 'MDSYS') ORDER BY T.TABLE_NAME, C.COLUMN_ID

3. Oracle 获取视图的 SQL 语句

3.1 拥有 DBA 权限时的查询方式

select distinct view_name from all_views where owner not in ('SYS','SYSTEM','OUTLN','XDB','WMSYS','CTXSYS','MDSYS') and OWNER ='库名';

3.2 普通用户权限下的查询方式

select distinct view_name from all_views where owner not in ('SYS','SYSTEM','OUTLN','XDB','WMSYS','CTXSYS','MDSYS') and OWNER ='库名';

4. Oracle 获取视图字段的 SQL 语句

4.1 拥有 DBA 权限时的查询方式

SELECT c.table_name AS view_name, NVL ( cm.comments, '' ) AS cmt, c.column_name, cm.comments, c.data_type, c.data_length, c.data_precision, c.data_scale, c.nullable,  'n' AS is_primary_key FROM dba_tab_columns c LEFT JOIN dba_col_comments cm ON c.OWNER = cm.OWNER AND c.table_name = cm.table_name AND c.column_name = cm.column_name WHERE c.OWNER = '库名' AND c.table_name IN ( 逗号分隔的表名 )ORDER BY c.table_name,c.column_id 

4.2 普通用户权限下的查询方式

SELECT c.table_name AS view_name, NVL ( cm.comments, '' ) AS cmt, c.column_name, cm.comments, c.data_type, c.data_length, c.data_precision, c.data_scale, c.nullable,  'n' AS is_primary_key FROM all_tab_columns c LEFT JOIN all_col_comments cm ON c.OWNER = cm.OWNER AND c.table_name = cm.table_name AND c.column_name = cm.column_name WHERE c.OWNER = '库名' AND c.table_name IN ( 逗号分隔的表名 )ORDER BY c.table_name,c.column_id 

以上便是针对 Oracle 不同权限下获取表、视图及对应字段信息的 SQL 整理。实际使用时，请根据自身环境替换其中的库名与表名列表，并依据当前账户权限选择对应的数据字典视图即可快速完成查询。