oracle - Result of the V$tablespace is confusing-

Question

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 –</p>

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

标签： oracledatabase-connectionoracle-sqldeveloperoracle12cmulti-tenant

Answer 1

首先是 TS# 列 .... 它显示的是什么数字？

它是表空间的 ID。

我通过“CDB$ROOT”数据库使用“Sys”用户连接到我的数据库。

这是关键点：您将看到有关为所有数据库容器定义的表空间的信息。

如果您以 SYSTEM 用户的身份连接了一个特定的可插入数据库，那么您在 V$TABLESPACE 上的查询中只会看到五行。

查询重复表空间的名称，我不明白为什么

答案在于 CON_ID 的值。让我们解码它，看看结果告诉我们什么：

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#
/

输出

       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 

这一切都包含在文档中。了解更多。