在解决权限分配后(权限释放后上篇有提及)表空间内存分为一次性扩孔和自动扩充两种方法

--查看表的空间使用

SELECT TABLESPACE_NAME "表空间",

To_char(Round(BYTES / 1024, 2), '99990.00')

|| '' "实有",

To_char(Round(FREE / 1024, 2), '99990.00')

|| 'G' "现有",

To_char(Round(( BYTES - FREE ) / 1024, 2), '99990.00')

|| 'G' "使用",

To_char(Round(10000 * USED / BYTES) / 100, '99990.00')

|| '%' "比例"

FROM (SELECT A.TABLESPACE_NAME TABLESPACE_NAME,

Floor(A.BYTES / ( 1024 * 1024 )) BYTES,

Floor(B.FREE / ( 1024 * 1024 )) FREE,

Floor(( A.BYTES - B.FREE ) / ( 1024 * 1024 )) USED

FROM (SELECT TABLESPACE_NAME TABLESPACE_NAME,

Sum(BYTES) BYTES

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME) A,

(SELECT TABLESPACE_NAME TABLESPACE_NAME,

Sum(BYTES) FREE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) B

WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME)

--WHERE TABLESPACE_NAME LIKE 'CDR%' --这一句用于指定表空间名称

ORDER BY Floor(10000 * USED / BYTES) DESC;

--查看oracle的用户

select username, default_tablespace, temporary_tablespace

from dba_users;

--查看用户的存储空间

select tablespace_name, file_id, file_name,

round(bytes/(1024*1024),0) total_space

from dba_data_files

order by tablespace_name;

--查看表的使用情况

SELECT a.tablespace_name "表空间名",

total "表空间大小",

free "表空间剩余大小",

(total - free) "表空间使用大小",

total / (1024 * 1024 * 1024) "表空间大小(G)",

free / (1024 * 1024 * 1024) "表空间剩余大小(G)",

(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",

round((total - free) / total, 4) * 100 "使用率 %"

FROM (SELECT tablespace_name, SUM(bytes) free

FROM dba_free_space

GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM(bytes) total

FROM dba_data_files

GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name ;

--扩容

alter tablespace 表空间名称

add datafile 表空间存放路径 size 表空间大小 autoextend on next 增长的大小 maxsize 空间最大值(如果不限制空间最大值就用unlimited)

alter tablespace MES

add datafile 'E:\ B.DBF'

size 20000M autoextend on next 20000M maxsize unlimited;