oracle中user_tab_columns和user_tab_cols的区别

看oracle的元数据

create or replace view sys.user_tab_columns as
select TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER,
       DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID,
       DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE,
       DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,
       CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH,
       GLOBAL_STATS, USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED,
       V80_FMT_IMAGE, DATA_UPGRADED, HISTOGRAM
  from USER_TAB_COLS
 where HIDDEN_COLUMN = 'NO'

 

oracle 在表中建立索引后,会在表里自动新增一个字段,但是这个字段在user_tab_cols里的HIDDEN_COLUMN里是用YES来标识的。

 

所以我们在使用的过程中,要尽量使用user_tab_columns,而不是user_tab_cols。

因为user_tab_cols会有一些隐藏字段.

 

<think>嗯,用户想查询Oracle数据库中指定用户的所有表及其大小字段数量。我需要根据提供的引用内容来构建一个合适的SQL语句。首先,我应该回忆一下如何获取用户的所有表,然后是如何获取表的大小字段数量。 根据引用[2],查看用户建立的表可以用`select table_name from user_tables`。那用户的所有表名应该从这里获取。接下来是表的大小,可能需要查询`dba_segments`视图,因为`dba_segments`中的`bytes`字段可以给出存储大小。不过要注意权限问题,可能需要DBA权限。 然后是字段数量,每个表的字段数可以通过`all_tab_columns`或者`user_tab_columns`来统计。比如对每个表名执行`count(*)`,然后汇总起来。可能需要使用子查询或者连接操作。 现在需要把这些部分组合起来。可能需要用`user_tables`获取所有表名,然后左连接`dba_segments`来获取大小,再左连接一个子查询统计每个表的字段数。不过要注意用户是否有权限访问`dba_segments`,如果没有,可能需要用`user_segments`,但这样可能只能看到当前用户的段信息。 此外,引用[3]中提到可以使用`all_tab_columns`或`all_tab_cols`来查询字段信息。统计每个表的字段数可以用`select table_name, count(*) as column_count from all_tab_columns where owner='指定用户' group by table_name`。同样,表的大小可能需要用`dba_segments`中的`sum(bytes)`,按segment_name分组,但表名segment_name是否一致?可能需要确认,因为有些情况下可能会有分区表的情况,但通常非分区表的segment_name就是表名。 另外,需要注意指定用户的处理,在`dba_segments`中,owner应该是用户名,而`all_tab_columns`中的owner也是用户名。因此,在查询时需要将用户作为变量传入,比如替换成具体的用户名。 综合起来,可能的SQL结构是: SELECT t.table_name, s.bytes AS table_size, c.column_count FROM user_tables t LEFT JOIN dba_segments s ON t.table_name = s.segment_name AND s.owner = '指定用户' LEFT JOIN (SELECT table_name, COUNT(*) AS column_count FROM all_tab_columns WHERE owner='指定用户' GROUP BY table_name) c ON t.table_name = c.table_name; 不过可能存在的问题是,如果用户没有DBA权限,无法访问`dba_segments`,这时候可能需要改用`user_segments`,但这样可能只能获取当前用户的表大小。或者用户可能需要被授予相应的权限。 另外,字段数量的子查询可能需要根据用户调整owner,比如使用`where owner = '指定用户'`。同样,在`user_tables`中,可能只能看到当前用户的表,如果要查其他用户,可能需要使用`all_tables`并过滤owner字段。 哦,用户的问题是指定用户的所有表,所以可能需要用`all_tables`,然后where owner='指定用户'。但根据引用[2],`user_tables`是当前用户的表,而`all_tables`是所有用户有权限访问的表,所以可能需要使用`dba_tables`来获取指定用户的所有表,但这同样需要DBA权限。 所以,正确的做法可能是:如果用户有DBA权限,使用`dba_tables`来获取指定用户的表,否则可能需要使用`all_tables`并结合权限。不过用户的问题明确是查询指定用户的所有表,所以假设有权限的话,应该使用`dba_tables`。 那调整后的SQL可能是: SELECT dt.table_name, ds.bytes AS table_size, (SELECT COUNT(*) FROM all_tab_columns WHERE owner = dt.owner AND table_name = dt.table_name) AS column_count FROM dba_tables dt LEFT JOIN dba_segments ds ON dt.owner = ds.owner AND dt.table_name = ds.segment_name AND ds.segment_type = 'TABLE' WHERE dt.owner = '指定用户'; 这样,从`dba_tables`中选择指定用户的表,然后连接`dba_segments`获取大小,再子查询统计字段数。这里需要注意`dba_segments`的segment_type是否为TABLE,或者可能是TABLE PARTITION等,但如果是普通表,segment_type应该是TABLE。 不过可能需要测试这个查询是否正确,特别是segment_type的值是否正确,以及是否有其他类型的段会影响结果。例如,索引段也可能存在,所以需要确保只选择表类型的段。 此外,如果表被压缩或有其他存储特性,是否会影响`dba_segments`中的bytes数值?不过用户可能只需要大致的存储大小,所以直接取bytes应该可以。 另外,字段数量的子查询中,`all_tab_columns`需要指定ownertable_name,确保统计的是该用户下该表的字段数。 现在,综合所有考虑,最终的SQL应该包括三个部分:表名、大小、字段数,通过连接三个数据源:dba_tables、dba_segments、all_tab_columns的统计。 不过权限问题需要注意,用户可能需要被授予SELECT ANY DICTIONARY权限,或者被授予访问这些数据字典视图的权限,比如SELECT_CATALOG_ROLE。 最后,在编写答案时,要说明这个SQL的使用方法,以及可能的权限问题注意事项。</think>以下是在Oracle数据库中查询指定用户所有表及其大小、字段数量的SQL实现方法: ### 查询指定用户所有表及信息的SQL语句 ```sql SELECT dt.table_name AS "表名", ROUND(ds.bytes/1024/1024, 2) AS "表大小(MB)", -- 字节转MB计算 (SELECT COUNT(*) FROM all_tab_columns WHERE owner = dt.owner AND table_name = dt.table_name) AS "字段数量" FROM dba_tables dt LEFT JOIN dba_segments ds ON dt.owner = ds.owner AND dt.table_name = ds.segment_name AND ds.segment_type = 'TABLE' -- 确保匹配表类型 WHERE dt.owner = '指定用户名' -- 替换为实际用户名 ORDER BY ds.bytes DESC NULLS LAST; ``` ### 关键步骤解析 1. **获取用户表清单** 通过`dba_tables`视图查询指定用户的所有表: ```sql SELECT table_name FROM dba_tables WHERE owner = '用户名' ``` [^2] 2. **查询表存储大小** 使用`dba_segments`视图获取物理存储大小: ```sql SELECT bytes FROM dba_segments WHERE owner='用户名' AND segment_name='表名' AND segment_type='TABLE' ``` [^1] 3. **统计字段数量** 通过`all_tab_columns`统计字段数: ```sql SELECT COUNT(*) FROM all_tab_columns WHERE owner='用户名' AND table_name='表名' ``` [^3] ### 注意事项 1. **权限要求** - 需要`SELECT_CATALOG_ROLE`或`DBA`角色权限 - 普通用户可尝试替换`dba_`为`user_`(如`user_tables`)查看当前用户对象 2. **结果说明** - 表大小可能包含索引等附加存储空间 - 空表可能显示NULL大小值 3. **性能优化** 对大型数据库建议添加过滤条件: ```sql AND dt.table_name NOT LIKE 'BIN$%' -- 排除回收站对象 ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值