CODEWITHSUNDEEP
sqloracle-database
Miguel.G
Miguel.G

Reputation: 387

SYSTEM tablespace in Oracle database

I don't know how to get all tables from SYSTEM tablespace in oracle database.

I'm executing the next command: 

select * 
from dba_tables 
where tablespace_name='SYSTEM';

However, tables like all_users or ALL_TABLES are not listed in this output (I read these tables are from SYSTEM tablespace here: https://www.techonthenet.com/oracle/sys_tables/index.php).

What am I doing wrong? This is what I understand: SYSTEM is a tablespace which contains metadata about database information, that is why it has some tables like "all_tables".

Please, correct me as much as possible if I am wrong.

Upvotes: 0

Views: 884

Answers (2)

Sam M
Sam M

Reputation: 4166

Tablespace in Oracle terms is a physical or in-memory location for data storage. What you are referring to in your question are schemas.

For the most part, the data dictionary contains views, not tables. You can find them by querying all_views instead of all_tables. Because they are dynamic views and do not store data, there is no tablespace. Instead, query the schema owners SYS and 'SYSTEM`.

SELECT *
  FROM all_views
 WHERE owner IN ('SYS', 'SYSTEM')

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

ALL_USERS and ALL_TABLES are views, not tables, and as such they appear in the ALL_VIEWS view. The following queries might be useful to you:

SELECT *
  FROM ALL_TABLES
  WHERE TABLESPACE_NAME = 'SYSTEM';

SELECT OWNER, VIEW_NAME
  FROM ALL_VIEWS
  WHERE VIEW_NAME IN ('ALL_USERS', 'ALL_TABLES')

SELECT OWNER, TABLE_NAME
  FROM ALL_TABLES
  WHERE TABLE_NAME IN ('ALL_USERS', 'ALL_TABLES')

See this dbfiddle

Upvotes: 1

Related Questions