CODEWITHSUNDEEP
sqldatabaseoracle-database
Lan Chan
Lan Chan

Reputation: 1

SYS account can not view all tables

As you can see in picture I am connecting to my database as SYS with the SYSDBA below

SQL Developer connection with SYS and SYSDBA

The filtered tables shown in the picture 2 seems are system tables, right, as all tables names are ended with $?

SQL Developer table list in connections plus query and error

I am wondering why SYS does not have permission to view the tables we created e.g filetable (as shown in the picture above). Is it because this ID does not have permission to select all tables? If yes, how can I allow the ID have permission to view all tables, including filetable?

Does this SYS account have FULL privileges in the database or not? I tried to use the below command to grant privileges to user but that didn't work, getting the error shown:

SQL> grant SELECT on filetable to user

grant SELECT on filetable to user

        *

ERROR at line 1:

ORA-00942: table or view does not exist

What I need to do is create a user and grant select permission on all tables to this user.

What I need to do ?

Upvotes: 0

Views: 412

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21075

Simple run

select owner, table_name from dba_tables where table_name= 'FILETABLE';

or fallback to query below if it is not a table, but a view or a materialized view

select owner, object_name, object_type from dba_objects where object_name= 'FILETABLE';

than take what you see in the column owner and query with (substitue [owner] with the owner you see) - if the query above return no rows the object does not exists.

 select * from [owner].FILETABLE

Upvotes: 1

Related Questions