How to specify tablespace name in SQLPlus Oracle select
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 would like to SELECT * FROM MYSPACE.MYTABLENAME;
, but doesn't seem to be the way 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 this table. As would I if it were MySQL.
thank
source to share
You are selecting from a tablespace that does not match the Owner / Schema name. That's why. For example, a tablespace SYSTEM
has an owner SYS
. You choose 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 mentioning: select .. from dba_tables
/ user_tables
)
source to share
The tables do not belong to the tablespace. Tablespace is the logical storage structure of the store . When you create a table (or index), you specify which tablespace should contain its contents stored in . Unless you are doing DBA tasks, you really don't need to know which tablespaces your data is stored in.
You might just be misleading the terms. Tables belong to schemas . If you ask for a column owner
instead 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 then you can query 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 a schema; you can just choose FROM MYTABLENAME
. (You may also have synonyms or session settings that make the schema prefix unnecessary, but that gets a little off topic.) And if you have a table, you will see it in the USER_TABLES
.
source to share