dba_object查询到表名，select ...from 表名查询不到

来源：互联网发布：防止微信屏蔽分享域名编辑：程序博客网时间：2024/05/01 03:50

问题原因：表test100对象在dba_objects中存在，为何select * from test100;报错表或视图不存在？

SQL> select owner,object_name,object_id from dba_objects where object_name = 'test100';OWNER                          OBJECT_NAME                                                                       OBJECT_ID------------------------------ -------------------------------------------------------------------------------- ----------SCOTT                          test100                                                                               77185

SQL> select * from test100;
select * from test100
ORA-00942: 表或视图不存在

思考问题经过：
1.是否因为没有加属主，建表用户和查询用户是同一用户，被否定了。
2.dba_objects表里test100是大写的记录是否存在：

SQL> select owner,object_name,object_id from dba_objects where object_name = 'TEST100';OWNER                          OBJECT_NAME                                                                       OBJECT_ID------------------------------ -------------------------------------------------------------------------------- ----------

SQL> select * from TEST100;select * from TEST100ORA-00942: 表或视图不存在

其结果是表TEST100在dba_objects中没有记录且不存在。正常情况下test100建表成功同时写一条记录到dba_objects，
并且object_name记录的是TEST100（大写形式）。

3.为了验证建表写值object_name为大写形式，做了下面的实验：

SQL> create table test101 as select 1 as id from dual;Table createdSQL> select owner,object_name,object_id from dba_objects where object_name = 'TEST101';OWNER                          OBJECT_NAME                                                                       OBJECT_ID------------------------------ -------------------------------------------------------------------------------- ----------SCOTT                          TEST101                                                                               77267SQL> select * from test101;        ID----------1

SQL> select owner,object_name,object_id from dba_objects where object_name = 'test101';OWNER                          OBJECT_NAME                                                                       OBJECT_ID------------------------------ -------------------------------------------------------------------------------- ----------

已经可以明显看出表test101，存入是TEST101（大写形式）。

4.怀疑表test100是否做了处理，做了下面的实验：

SQL> create table "test100" as select 1 as id from dual;Table createdSQL> select owner,object_name,object_id from dba_objects where object_name = 'test100';OWNER                          OBJECT_NAME                                                                       OBJECT_ID------------------------------ -------------------------------------------------------------------------------- ----------SCOTT                          test100                                                                               77269SQL> select owner,object_name,object_id from dba_objects where object_name = 'TEST100';OWNER                          OBJECT_NAME                                                                       OBJECT_ID------------------------------ -------------------------------------------------------------------------------- ----------SQL> SQL> select * from test100;select * from test100ORA-00942: 表或视图不存在SQL> select * from "test100";        ID---------- 1

结论：create table test100 做了双引号处理 create table “test100”，object_name 写了带双引号的值test100

0 0