如何查看oracle数据库中的表空间 oracle怎么查询表对应的表空间

如何查看oracle数据库中的表空间

oracle 数据库里查看表空间使用状况；
oracle表空间的事情状况要经常查看，一般空闲比例过低的时候就应该考虑增大表看空间了。查看方法如下SQL:

方法一：

select dbf.tablespace_name,
dbf.totalspace "总量(M)",
dbf.totalblocks as 总块数,
dfs.freespace "剩余总量(M)",
dfs.freeblocks "剩余块数",
(dfs.freespace / dbf.totalspace) * 100 "空闲比例"
from (select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)

方法二：

SELECT Total.name "Tablespace Name",
Free_space, (total_space-Free_space) Used_space, total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name = Total.name

当发现有的表空间不够的错误时，处理如下：
1：找出该表空间对应的数据文件及路径

select * from dba_data_files t
where t.tablespace_name = ARD

2：增大数据文件

alter database datafile 全路径的数据文件名称 resize ***M

3：增加数据文件

alter tablespace 表空间名称

add datafile 全路径的数据文件名称 ***M

注解：表空间尽量让free百分比保持在10%以上，如果低于10%就增加datafile或者resizedatafile，一般数据文件不要超过2G

oracle怎么查询表对应的表空间

1、select TABLE_NAME,DEF_TABLESPACE_NAME from USER_PART_TABLES order by DEF_TABLESPACE_NAME
2、col SEGMENT_NAME format a30
select distinct SEGMENT_NAME,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME=‘TS1’ order by SEGMENT_NAME
3、 select TABLE_NAME,TABLESPACE_NAME from user_tables order by table_name

oracle怎么查看用户所属于的表空间

select username,default_tablespace from dba_users  where username=用户名 ---用户名需要大写
如查询scott用户的所属表空间：

如何查看oracle所有的表空间

SQL:
方法一：

select dbf.tablespace_name,
dbf.totalspace "总量(M)",
dbf.totalblocks as 总块数,
dfs.freespace "剩余总量(M)",
dfs.freeblocks "剩余块数",
(dfs.freespace / dbf.totalspace) * 100 "空闲比例"
from (select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)

方法二：

SELECT Total.name "Tablespace Name",
Free_space, (total_space-Free_space) Used_space, total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name = Total.name

怎样查询oracle数据库中所有的表空间

1、首先需要找到oracle安装目录，打开控制台管理。

2、进入控制台界面，输入用户名和密码进行登录验证。


3、登录成功，进入主界面，点击菜单栏处【服务器】。

4、此时在存储栏，找到表空间选项，点击打开。

5、在搜索栏输入表空间名称，进行搜索。

6、此时在下方结果栏，可以看到表空间状态。

oracle 如何查看表空间最大的大小

select tablespace_name,sum(maxbytes)/1024/1024/1024 from dba_data_files where tablespace_name=XXXXXX(大写，或者用upper)
如果要查看所有表空间的，那么就用group by分组解决。

如何查看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