我在导入别个的表的时候 不小心没有指定表空间,结果导入到默认的表空间,一下子把我 C盘装满了 。先找到你被自增长的表空间的。删除里面的表并且recycle bin中的都要清除,还有你建的索引之类的都清楚了,删除表空间下没有用的表和索引。
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
WHERE D.TABLESPACE_NAME = '表空间的名字')) M,
(SELECT VALUE / 1024 BLOCK_SIZE
FROM V$PARAMETER
WHERE NAME = 'db_block_size') B
查询表空间的实际使用的数量。
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
查询你所有的表空间的使用情况。
然后缩小你表空间 alter database datafile 'F:\app\you\oradata\orcl\USERS01.DBF' resize 1024m;