Oracle 删除表中记录 如何释放表及表空间大小
来源:互联网 发布:js控制input 框不可写 编辑:程序博客网 时间:2024/05/22 02:30
1.查看一个表所占的空间大小:
SELECT bytes/1024/1024 ||'MB' TABLE_SIZE ,u.* FROM USER_SEGMENTS U WHERE U.SEGMENT_NAME='JK_TEST';
2.查看一个表空间所占的实际大小:
SELECT SUM(BYTES) / 1024 / 1024 ||'MB' FROM USER_SEGMENTS U WHERE TABLESPACE_NAME = 'DATA01';
3.查看一个表空间对应的数据文件:
SELECT * FROM DBA_DATA_FILES D WHERE D.TABLESPACE_NAME = 'DATA01';
4.查看表空间的使用情况:
SELECT A.TABLESPACE_NAME,
FILENUM,
TOTAL "TOTAL (MB)",
F.FREE "FREE (MB)",
TO_CHAR(ROUND(FREE * 100 / TOTAL, 2), '990.00') "FREE%",
TO_CHAR(ROUND((TOTAL - FREE) * 100 / TOTAL, 2), '990.00') "USED%",
ROUND(MAXSIZES, 2) "MAX (MB)"
FROM (SELECT TABLESPACE_NAME,
COUNT(FILE_ID) FILENUM,
SUM(BYTES / (1024 * 1024)) TOTAL,
SUM(MAXBYTES) / 1024 / 1024 MAXSIZES
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / (1024 * 1024))) FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE A.TABLESPACE_NAME = F.TABLESPACE_NAME
5.查看数据文件的实际使用情况:
SELECT CEIL(MAX_BLOCK * BLOCK_SIZE / 1024)
FROM (SELECT MAX(BLOCK_ID) MAX_BLOCK
FROM DBA_EXTENTS
WHERE FILE_ID IN (SELECT FILE_ID
FROM DBA_DATA_FILES D
SELECT bytes/1024/1024 ||'MB' TABLE_SIZE ,u.* FROM USER_SEGMENTS U WHERE U.SEGMENT_NAME='JK_TEST';
2.查看一个表空间所占的实际大小:
SELECT SUM(BYTES) / 1024 / 1024 ||'MB' FROM USER_SEGMENTS U
3.查看一个表空间对应的数据文件:
SELECT * FROM DBA_DATA_FILES D WHERE D.TABLESPACE_NAME = 'DATA01';
4.查看表空间的使用情况:
SELECT A.TABLESPACE_NAME,
5.查看数据文件的实际使用情况:
SELECT CEIL(MAX_BLOCK * BLOCK_SIZE / 1024)