怎么查oracle那个表空间占用空间比较大 怎么察看Oracle 数据库表空间的使用情况

怎么查oracle那个表空间占用空间比较大

--查询表空间使用情况
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),990.99) || % "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1
--查询表空间的free space
select tablespace_name,
count(*) as extends,
round(sum(bytes) / 1024 / 1024, 2) as MB,
sum(blocks) as blocks
from dba_free_space
group by tablespace_name

--查询表空间的总容量
select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name

--查询表空间使用率
select total.tablespace_name,
round(total.MB, 2) as Total_MB,考试大论坛
round(total.MB - free.MB, 2) as Used_MB,
round((1 - free.MB / total.MB) * 100, 2) || % as Used_Pct
from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_free_space
group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name) total
where free.tablespace_name = total.tablespace_name

怎么察看Oracle 数据库表空间的使用情况

查看的方法和详细的操作步骤如下：
1、首先，因为oracle在Linux系统下运行，所以必须连接到Linux系统，如下图所示，然后进入下一步。

2、其次，完成上述步骤后，连接成功，进入Oracle控制台。
 输入命令“sqlplus / as sysdba”，如下图所示，然后进入下一步。

3、接着，完成上述步骤后，在sql命令行上，输入以下代码，如下图所示，然后进入下一步。

4、最后，完成上述步骤后，就可以查看相应的结果了，如下图所示。这样，问题就解决了。

怎么查询Oracle中一张表占用空间

执行如下语句即可： select OWNER, t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) mmm
from dba_segments t
where t.owner = 你要查询的用户
and t.segment_type=TABLE
group by OWNER, t.segment_name, t.segment_type
order by mmm desc

如何查询oracle表空间使用情况

//查看表空间剩余空间(M)
select tablespace_name,sum(bytes)/1024/1024 free_space
from dba_free_space
group by tablespace_name
//详细查看表空间使用状况，包括总大小，使用空间，使用率，剩余空间
select t.*
from (SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME( )
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - USED_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE,
ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE
FROM V$SORT_USAGE
GROUP BY TABLESPACE) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE( )) t
order by "USED_RATE(%)" desc
SELECT tablespace_name, sum ( blocks ) as free_blk , trunc ( sum ( bytes ) / (1024*1024) ) as free_m, max ( bytes ) / (1024) as big_chunk_k, count (*) as num_chunks
FROM dba_free_space
GROUP BY tablespace_name

如何查看oracle数据库里各个表空间的利用率？

select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc

如何查看oracle表空间大小的使用情况

1. 查看所有表空间大小

SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files
2 group by tablespace_name

2. 已经使用的表空间大小
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space
2 group by tablespace_name

3. 所以使用空间可以这样计算

select a.tablespace_name,total,free,total-free used from
( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
group by tablespace_name) a,
( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name

4. 下面这条语句查看所有segment的大小。
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name

5. 还有在命令行情况下如何将结果放到一个文件里。
SQL> spool out.txt
SQL> select * from v$database
SQL> spool off

ORACLE如何查找表的使用率

最简单的方法，awr报表
SQL> @adminawrrpt.sql
输入 report_type 的值: 输入 num_days 的值: 1 输入 begin_snap 的值: 4965 输入 end_snap 的值: 4966 输入 report_name 的值: Report written to awrrpt_4965_4966.html SQL> exit 生成报表后就可以看到了。

oracle 查询表空间使用情况与查询有哪些数据库实例在运行

查询表空间使用情况 select a.tablespace_name "表空间名称", 100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2) "占用率(%)", round(a.bytes_alloc/1024/1024,2) "容量(M)", round(nvl(b.bytes_free,0)/1024/1024,2) "空闲(M)", round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2) "使用(M)", Largest "最大扩展段(M)", to_char(sysdate,yyyy-mm-dd hh24:mi:ss) "采样时间" from (select f.tablespace_name, sum(f.bytes) bytes_alloc, sum(decode(f.autoextensible,YES,f.maxbytes,NO,f.bytes)) maxbytes from dba_data_files f group by tablespace_name) a, (select f.tablespace_name, sum(f.bytes) bytes_free from dba_free_space f group by tablespace_name) b, (select round(max(ff.length)*16/1024,2) Largest, ts.name tablespace_name from sys.fet$ ff, sys.file$ tf,sys.ts$ ts where ts.ts#=ff.ts# and ff.file#=tf.relfile# and ts.ts#=tf.ts# group by ts.name, tf.blocks) c where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name 25. 查询表空间的碎片程度 select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name having count(tablespace_name)>10 alter tablespace name coalesce alter table name deallocate unused create or replace view ts_blocks_v as select tablespace_name,block_id,bytes,blocks,free space segment_name from dba_free_space union all select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents select * from ts_blocks_v select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space group by tablespace_name 26。查询有哪些数据库教程实例在运行 select inst_name from v$active_instances =========================================================== ######### 创建数据库----look $ORACLE_HOME/rdbms/admin/buildall.sql ############# create database db01 maxlogfiles 10 maxdatafiles 1024 maxinstances 2 logfile GROUP 1 (/u01/oradata/db01/log_01_db01.rdo) SIZE 15M, GROUP 2 (/u01/oradata/db01/log_02_db01.rdo) SIZE 15M, GROUP 3 (/u01/oradata/db01/log_03_db01.rdo) SIZE 15M, datafile u01/oradata/db01/system_01_db01.dbf) SIZE 100M, undo tablespace UNDO datafile /u01/oradata/db01/undo_01_db01.dbf SIZE 40M

如何查询表空间使用情况

快速的查看剩余表空间:

1. 因为oracle运行在Linux系统下，首先，要连接Linux系统。 
2. 连上后，进行oracle控制台。输入命令： sqlplus  / as sysdba
3. 在sql命令行，输入： 
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
　　D.TOT_GROOTTE_MB "表空间大小(M)",
　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),990.99) || % "使用比",
　　F.TOTAL_BYTES "空闲空间(M)",
　　F.MAX_BYTES "最大块(M)"
　　FROM (SELECT TABLESPACE_NAME,
　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
　　FROM SYS.DBA_FREE_SPACE
　　GROUP BY TABLESPACE_NAME) F,
　　(SELECT DD.TABLESPACE_NAME,
　　 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME) D
　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
　　ORDER BY 1
4. 这样就可以查看到相应结果。