oracle 如何从单个查询中获取所有表空间名称、分配大小、可用大小、容量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19240276/
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
How to Get all table space name, Allocated size, Free size, Capacity from single query?
提问by Regon
How can i get the information about all tables space in my database in below format.
如何以以下格式获取有关我的数据库中所有表空间的信息。
TABLESPACE_NAME | FILE_NAME | ALLOCATED_MB | FREE_MB | CAPACITY |
Is there way to store daily size of all table-space in another table automatically ?. Actually i need to prepare checklist regarding table space on daily basis. So I wan't to create front end which email me the table space size details automatically on the basis of that table which store the information about table space size on daily basis..
有没有办法自动将所有表空间的每日大小存储在另一个表中？实际上，我需要每天准备有关表空间的清单。所以我不想创建前端，它会根据每天存储有关表空间大小的信息的表自动向我发送表空间大小详细信息。
回答by Dba
Try the below query to get all tablespace details in oracle. Assuming that you have the necessary privileges to access dba tables.
尝试以下查询以获取 oracle 中的所有表空间详细信息。假设您具有访问 dba 表的必要权限。
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 user3649154
Select a.tablespace_name,a.file_name,a.bytes/1024/1024 TABLESPACE_SIZE_MB,
Sum(b.bytes)/1024/1024 FREE_IN_MB from dba_free_space b,dba_data_files a
Where a.tablespace_name = b.tablespace_name
AND a.file_id = b.file_id
GROUP by a.tablespace_name, a.file_name,a.bytes/1024/1024
Order by a.tablespace_name, a.file_name;
you can run this query this may help.
您可以运行此查询，这可能会有所帮助。
回答by Sapna
Above are useful. Hope this may also helpful here:
以上有用。希望这对这里也有帮助：
https://ora-data.blogspot.in/2016/12/how-to-find-details-of-tablespace.html
https://ora-data.blogspot.in/2016/12/how-to-find-details-of-tablespace.html
Check the Tablespace details with different command, above command may not work:
使用不同的命令检查表空间详细信息，上述命令可能不起作用：
SQL>select round((bytes/1024)/1024,0) "Used Space(MB)",
round(total,0) "Allocated size(MB)",
round(max,0) "Maximum allowable(MB)",
round(max-(BYTES/1024)/1024,0) "Effective free(MB)",
round(((max-(BYTES/1024)/1024)/max)*100,2) "FREE(%)"
from SYS.SM$TS_USED,
(select sum((BYTES/1024)/1024) total, sum((decode(MAXBYTES,0,bytes,maxbytes)/1024)/1024) max
from dba_data_files where tablespace_name='&1') where tablespace_name='&1';
回答by bgs
In Oracle refer below link :
在 Oracle 中，请参阅以下链接：
How do I calculate tables size in Oracle
https://forums.oracle.com/thread/2160787
https://forums.oracle.com/thread/2160787
COLUMN TABLE_NAME FORMAT A32
COLUMN OBJECT_NAME FORMAT A32
COLUMN OWNER FORMAT A10
SELECT
owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type = 'TABLE'
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type = 'INDEX'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX')
WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */
ORDER BY SUM(bytes) desc
;
In SQLrefer below
在SQL 中，请参阅下面
