Oracle: how to query a table if I know only its (non-default) tablespace? So. SQLPlus simply gives for my first try (after a little bit of postgresql experience):

SQL&gt; 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&gt; SELECT * FROM (SELECT * FROM dba_tables WHERE tablespace_name='MYTABLESPACE') WHERE ROWNUM&lt;=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&gt; SELECT * FROM MYTABLE;
SELECT * FROM MYTABLE
              *
ERROR at line 1:
ORA-00942: table or view does not exist
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.