How to specify tablespace_name in SQLPlus Oracle select

Question

My setup looks like this

SQL> SELECT tablespace_name, table_name
     FROM all_tables
     WHERE tablespace_name = 'MYSPACE';

TABLESPACE_NAME            TABLE_NAME
-------------------------- ------------------------------
MYSPACE                    MYTABLENAME
MYSPACE                    MYOTHERTABLENAME

Now I'd like to SELECT * FROM MYSPACE.MYTABLENAME; but that's apparently not how you do it.

ERROR at line 1:
ORA-00942: table or view does not exist

My expected result would be to get all records from that table. Like I would if it was MySQL.

Thanks

Answer 1

You are selecting from tablespace, which is not the same as your Owner/Schema name. Thats why. For example the tablespace SYSTEM has owner SYS. You do select from Sys.xxx;

Ok.

SELECT owner, tablespace_name, table_name
FROM all_tables
WHERE tablespace_name = 'MYSPACE';

And then

select * from [ owner ].[ table_name ];

(worth to mention: select .. from dba_tables / user_tables)

Answer 2

Tables are not owned by a tablespace. A tablespace is a logical storage storage structure. When you create a table (or index) you specify which tablespace its contents should be stored in. Unless you're doing DBA tasks, you don't really need to know which tablespace(s) your data is stored in.

You may just be confusing terms. Tables are owned by schemas. If you query the owner column instead of tablespace_name you might see something like:

SQL> SELECT owner, table_name
     FROM all_tables
     WHERE tablespace_name = 'MYSPACE';

OWNER                      TABLE_NAME
-------------------------- ------------------------------
MYUSER                     MYTABLENAME
MYUSER                     MYOTHERTABLENAME

And you can then query from that with

SELECT * FROM MYOWNER.MYTABLENAME;

Of course, if the owner is actually you anyway, then you don't need to prefix the table name with the schema; you can just select FROM MYTABLENAME. (You may also have synonyms or session settings that make the schema prefix unnecessary, but that's getting a a bit off-topic). And if you own the table you'll see it in USER_TABLES as well.