0

So. SQLPlus simply gives for my first try (after a little bit of postgresql experience):

SQL> SELECT * FROM MYTABLESPACE.MYTABLE;
SELECT * FROM MYTABLESPACE.MYTABLE
              *
ERROR at line 1:
ORA-00942: table or view does not exist

I've tried also SELECT * FROM MYTABLESPACE$MYTABLE (intuitive try) and simply SELECT * FROM MYTABLE as well, but nothing succeed. Googling for that gives a lot of info about the tablespace management, but nothing from this trivial question.

What to do?


Extension #1: Well, it looks there was really a minor misconception about the tablespaces and the schemas. But the problem is still very visible: I have access to the dba_tables table, I have access to one of its records, I want to query table described by that, and it doesn't work.

Extension #2: Here is what I did. I called sqlplus with the system account (sqlplus system/****):

SQL> SELECT * FROM (SELECT * FROM dba_tables WHERE tablespace_name='MYTABLESPACE') WHERE ROWNUM<=1;

OWNER                          TABLE_NAME                     TABLESPACE_NAME                CLUSTER_NAME                   IOT_NAME                       STATUS     PCT_FREE   PCT_USED  INI_TRANS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------- ---------- ---------- ----------
 MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS LOG B   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- --- - ---------- ---------- ------------ ---------- ---------- -----------
AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE                                   INSTANCES                                CACHE                TABLE_LO SAMPLE_SIZE LAST_ANA PAR IOT_TYPE     T S
------------------------- ------------------- ---------------------------------------- ---------------------------------------- -------------------- -------- ----------- -------- --- ------------ - -
NES BUFFER_ FLASH_C CELL_FL ROW_MOVE GLO USE DURATION        SKIP_COR MON CLUSTER_OWNER                  DEPENDEN COMPRESS COMPRESS_FOR DRO REA SEG RESULT_
--- ------- ------- ------- -------- --- --- --------------- -------- --- ------------------------------ -------- -------- ------------ --- --- --- -------
EXAMPLEOWNER                   MYTABLE                   MYTABLESPACE                                                                                    VALID            10                     1
       255          65536     1048576           1  2147483645                                         NO  N
                                                       1                                        1                                   N                ENABLED                       NO               N N
NO  DEFAULT DEFAULT DEFAULT DISABLED NO  NO                  DISABLED YES                                DISABLED DISABLED              NO  NO  YES DEFAULT



SQL> SELECT * FROM MYTABLE;
SELECT * FROM MYTABLE
              *
ERROR at line 1:
ORA-00942: table or view does not exist
3

Given your DBA_TABLES query, you have the following:

Owner (Schema Name): EXAMPLEOWNER

Table Name: MYTABLE

The SYSTEM user will not just be able to select from MYTABLE unless there is a Public Synonym pointing to it.

So, to select from the table you have to fully qualify the name by prefixing the table with its schema:

SELECT * FROM EXAMPLEOWNER.MYTABLE;

Note that tablespaces are simply the place the object is stored in, and have no bearing in DML queries.

1

From a query perspective it is unnecessary to name the tablespace. the tablespace is simply a logical name to store the databases datafiles under. What matters is the schema that the table is created in and your privileges against that schema's objects. So if 'MYTABLE' is in the schema named 'USERTABS' then you would need a grant running to give you permissions; e.g.

GRANT SELECT ON usertabs.mytable TO myusername

OR

GRANT SELECT ON usertabs.mytable TO PUBLIC

Then you select from the table qualified by its schema;

SELECT * FROM usertabs.mytable
  • Thank you the info, which can help other googlers in the future a lot. Unfortunately, in my case I think it is not applicable (which is not your fault), because I use this session from a system account. – peterh says reinstate Monica Feb 19 '16 at 12:38
  • @Peterh - The info should still be able to be applied to your case as the key thing is that the user you log in as should have permission to select from the table and the table should be qualified with the owning schema name. That will apply whether you use system account or not. – BriteSponge Feb 19 '16 at 13:34
  • Right, it could be if I wouldn't use sqlplus as system. Thanks! – peterh says reinstate Monica Feb 19 '16 at 13:43
  • @Peterh - you have an answer now and that is the main thing. However, this is not really to do with you logging in as system. It applies regardless. If you want to access an object in a schema other than your own you qualify it with the schema name (or have a synonym to it) – BriteSponge Feb 19 '16 at 14:12
  • Yes, and this is why your answer is also very useful. Thank you! – peterh says reinstate Monica Feb 19 '16 at 14:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.