CODEWITHSUNDEEP
oracle-databasedatabase-connectionoracle-sqldeveloperoracle12cmulti-tenant
sudhirkaushik
sudhirkaushik

Reputation: 21

Result of the V$tablespace is confusing-

Lately, I am checking the information about tablespaces for that I executed a query on v$tablespace. Everything is understandable except two things that confuse me –

First the column TS# according to the documentation this column shows “Number of Tablespaces” I don’t understand what that means – I only have 5 tablespaces but it does not correspond to that. What numbers it is showing?

Second, the query repeating the names of tablespaces, I don’t understand why? By the way, I am connected to my database using the “Sys” user through “CDB$ROOT” database. The query I am using 

SELECT * FROM v$database;

Here is the result that it returns

Result returned by V$tablespace screenshot

Upvotes: 0

Views: 428

Answers (1)

APC
APC

Reputation: 146289

First the column TS# .... What numbers it is showing?

It's the ID of the tablespace.

I am connected to my database using the “Sys” user through “CDB$ROOT” database.

This is the key point: you are seeing information about tablespaces defined for all the database containers.

If you had connected as say the SYSTEM user for a particular pluggable DB you would only have seen five rows in a query on V$TABLESPACE.

the query repeating the names of tablespaces, I don’t understand why

The answer lies in the value of CON_ID. Let's decode it and see what the results tell us:

select ts.* 
       ,case 
          when ts.con_id = 0 then 'CDB' 
          when ts.con_id = 1 then 'Root' 
          when ts.con_id = 2 then 'Seed'
          else 'PDB' end as scope
from v$tablespace ts
order by ts.con_id
        ,ts.ts#
/

Output

       TS# NAME                           INC BIG FLA ENC     CON_ID SCOP
---------- ------------------------------ --- --- --- --- ---------- ----
         0 SYSTEM                         YES NO  YES              1 Root
         1 SYSAUX                         YES NO  YES              1 Root
         2 UNDOTBS1                       YES NO  YES              1 Root
         3 TEMP                           NO  NO  YES              1 Root
         4 USERS                          YES NO  YES              1 Root
         0 SYSTEM                         YES NO  YES              2 Seed
         1 SYSAUX                         YES NO  YES              2 Seed
         2 UNDOTBS1                       YES NO  YES              2 Seed
         3 TEMP                           NO  NO  YES              2 Seed
         0 SYSTEM                         YES NO  YES              3 PDB 
         1 SYSAUX                         YES NO  YES              3 PDB 
         2 UNDOTBS1                       YES NO  YES              3 PDB 
         3 TEMP                           NO  NO  YES              3 PDB 
         5 USERS                          YES NO  YES              3 PDB

It's all covered in the documentation. Find out more.

Upvotes: 1

Related Questions