Oracle 删除表中记录 如何释放表及表空间大小
( 1.查看一个表所占的空间大小:
SELECT bytes/1024/1024 ||'MB' TABLE_SIZE ,u.* FROM USER_SEGMENTS UWHERE U.SEGMENT_NAME='JK_TEST';
2.查看一个表空间所占的实际大小:
SELECT SUM(BYTES) / 1024 / 1024 ||'MB' FROM USER_SEGMENTSU
WHERE TABLESPACE_NAME = 'DATA01';
3.查看一个表空间对应的数据文件:
SELECT * FROM DBA_DATA_FILES D WHERE D.TABLESPACE_NAME ='DATA01';
4.查看表空间的使用情况:
SELECTA.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 (SELECTTABLESPACE_NAME,
COUNT(FILE_ID)FILENUM,
SUM(BYTES / (1024 * 1024))TOTAL,
SUM(MAXBYTES) / 1024 / 1024MAXSIZES
FROMDBA_DATA_FILES
GROUP BY TABLESPACE_NAME)A,
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / (1024 * 1024)))FREE
FROMDBA_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