oracle 查询以显示数据库和数据文件中的所有表空间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36209102/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query to display all tablespaces in a database and datafiles
提问by mlhazan
How to write a query to display all tablespaces in database suppose db and it's corresponding datafiles belong to user SCOTT. I can connect to SCOTT account and see it but I like to connect to sys as sysdba to see all tablespaces and datafiles belong to SCOTT.
如何编写查询以显示数据库中的所有表空间，假设 db 及其相应的数据文件属于用户 SCOTT。我可以连接到 SCOTT 帐户并查看它，但我喜欢以 sysdba 身份连接到 sys 以查看属于 SCOTT 的所有表空间和数据文件。
回答by MMKarami
In oracle, generally speaking, there are number of facts that I will mention in following section:
在 oracle 中，一般来说，我将在以下部分提到一些事实：
- Each database can have many Schema/User (Logical division).
- Each database can have many tablespaces (Logical division).
- A schema is the set of objects (tables, indexes, views, etc) that belong to a user.
- In Oracle, a user can be considered the same as a schema.
- A database is divided into logical storage units called tablespaces, which group related logical structures together. For example, tablespaces commonly group all of an application's objects to simplify some administrative operations. You may have a tablespace for application data and an additional one for application indexes.
- 每个数据库可以有多个Schema/User（逻辑划分）。
- 每个数据库可以有多个表空间（逻辑分区）。
- 模式是属于用户的一组对象（表、索引、视图等）。
- 在 Oracle 中，用户可以被视为与模式相同。
- 数据库被划分为称为表空间的逻辑存储单元，这些单元将相关的逻辑结构组合在一起。例如，表空间通常将应用程序的所有对象分组以简化某些管理操作。您可能有一个用于应用程序数据的表空间和一个用于应用程序索引的附加表空间。
Therefore, your question, "to see all tablespaces and datafiles belong to SCOTT" is s bit wrong.
因此，您的问题“查看属于 SCOTT 的所有表空间和数据文件”有点错误。
However, there are some DBA views encompass information about all database objects, regardless of the owner. Only users with DBA privileges can access these views: DBA_DATA_FILES, DBA_TABLESPACES, DBA_FREE_SPACE, DBA_SEGMENTS.
但是，有一些 DBA 视图包含有关所有数据库对象的信息，而不管所有者如何。只有具有 DBA 权限的用户才能访问这些视图：DBA_DATA_FILES、DBA_TABLESPACES、DBA_FREE_SPACE、DBA_SEGMENTS。
So, connect to your DB as sysdba and run query through these helpful views. For example this query can help you to find all tablespaces and their data files that objects of your user are located:
因此，作为 sysdba 连接到您的数据库并通过这些有用的视图运行查询。例如，此查询可以帮助您查找用户对象所在的所有表空间及其数据文件：
SELECT DISTINCT sgm.TABLESPACE_NAME , dtf.FILE_NAME
FROM DBA_SEGMENTS sgm
JOIN DBA_DATA_FILES dtf ON (sgm.TABLESPACE_NAME = dtf.TABLESPACE_NAME)
WHERE sgm.OWNER = 'SCOTT'
回答by Muhammad Arslan
SELECT a.file_name,
substr(A.tablespace_name,1,14) tablespace_name,
trunc(decode(A.autoextensible,'YES',A.MAXSIZE-A.bytes+b.free,'NO',b.free)/1024/1024) free_mb,
trunc(a.bytes/1024/1024) allocated_mb,
trunc(A.MAXSIZE/1024/1024) capacity,
a.autoextensible ae
FROM (
SELECT file_id, file_name,
tablespace_name,
autoextensible,
bytes,
decode(autoextensible,'YES',maxbytes,bytes) maxsize
FROM dba_data_files
GROUP BY file_id, file_name,
tablespace_name,
autoextensible,
bytes,
decode(autoextensible,'YES',maxbytes,bytes)
) a,
(SELECT file_id,
tablespace_name,
sum(bytes) free
FROM dba_free_space
GROUP BY file_id,
tablespace_name
) b
WHERE a.file_id=b.file_id(+)
AND A.tablespace_name=b.tablespace_name(+)
ORDER BY A.tablespace_name ASC;
回答by EdStevens
Neither databases, nor tablespaces nor data files belong to any user. Are you coming to this from an MS SQL background?
数据库、表空间和数据文件都不属于任何用户。你是从 MS SQL 背景来的吗？
select tablespace_name,
file_name
from dba_tablespaces
order by tablespace_name,
file_name;
