这两天由于工作原因,一直跟oracle数据库打交道,踩了很多坑,现在写一些自己的感想,与大家分享,有不正确的地方,欢迎指正!
1、查询表空间信息
select * from dba_tablespaces
2、查看用户和表空间的关系
select username,default_tablespace from user_users;
3、查询表空间大小,剩余量,利用率,包含临时表空间
SELECT
*
FROM
(
SELECT
A .tablespace_name,
TO_CHAR (
A .bytes / 1024 / 1024,
'99,999.999'
) total_bytes,
TO_CHAR (
b.bytes / 1024 / 1024,
'99,999.999'
) free_bytes,
TO_CHAR (
A .bytes / 1024 / 1024 - b.bytes / 1024 / 1024,
'99,999.999'
) use_bytes,
TO_CHAR (
(1 - b.bytes / A .bytes) * 100,
'99.99'
) || '%' USE
FROM
(
SELECT
tablespace_name,
SUM (bytes) bytes
FROM
dba_data_files
GROUP BY
tablespace_name
) A,
(
SELECT
tablespace_name,
SUM (bytes) bytes
FROM
dba_free_space
GROUP BY
tablespace_name
) b
WHERE
A .tablespace_name = b.tablespace_name
UNION ALL
SELECT
c.tablespace_name,
TO_CHAR (
c.bytes / 1024 / 1024,
'99,999.999'
) total_bytes,
TO_CHAR (
(c.bytes - D .bytes_used) / 1024 / 1024,
'99,999.999'
) free_bytes,
TO_CHAR (
D .bytes_used / 1024 / 1024,
'99,999.999'
) use_bytes,
TO_CHAR (
D .bytes_used * 100 / c.bytes,
'99.99'
) || '%' USE
FROM
(
SELECT
tablespace_name,
SUM (bytes) bytes
FROM
dba_temp_files
GROUP BY
tablespace_name
) c,
(
SELECT
tablespace_name,
SUM (bytes_cached) bytes_used
FROM
v$temp_extent_pool
GROUP BY
tablespace_name
) D
WHERE
c.tablespace_name = D .tablespace_name
)
4、扩展临时表空间容量
alter database tempfile 'tempFilePath' resize 2048M;
5、扩展表空间容量
alter database datafile 'filePath' resize 4000m
6、查询表空间文件路径,包含临时表空间
SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 AS "BYTES(M)"
FROM DBA_DATA_FILES
SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 AS "SPACE(M)"
FROM DBA_TEMP_FILES
7、查询表空间所在表大小情况
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name