Oracle: how to query a table if I know only its (non-default) tablespace?

Question

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

Answer 1

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.

Answer 2

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