根据报错现象，处理思路一般是扩大表空间即可。
但实际登录后发现，ogg表空间只有一个数据文件，而且已经增长到最大32G.所以执行DLL操作无法成功。
尝试增加数据文件，报同样错误。
所以考虑删除可清理的大表数据，以释放一些空间出来。
通过查询oggadm用户下的大表情况，其中GGS_DDL_HIST表占用23G。按rownum条件进行快速删除部分数据。然后才可以执行truncate清空表数据，得以释放23G空间。
[oracle@dbserver ~]$ more tbs.sql
set line 132
set wrap off
select t.*
from (SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - USED_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
...skipping one line
ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE
FROM V$SORT_USAGE
GROUP BY TABLESPACE) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t
order by "USED_RATE(%)" desc;
[oracle@dbserver ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 2 11:03:26
Copyright (c) 1982, , Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> @tbs.sql
truncating (as requested) before column USED_RATE(%)
truncating (as requested) before column FREE_SPACE(M)
TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M)
------------------------------------------------------------------------------------------ ------------ ---------- -------------
OGG32708.31 4186664 32704.12
SYSTEM 1590 203520 1573.12
EAS_D_ZTB80_INDEX 3695 472960 3518.87
USERS 50 640047.56
EAS_D_ZTB80_STANDARD 11030 1411840 10432.5
SYSAUX 1600 204800 1507.56
EAS_D_ZTBHH80_STANDARD 2000 256000 776.06
EAS_D_ZTBJJ80_STANDARD 2000 256000769
EAS_D_ZTBHH80_INDEX 500 64000 148.06
EAS_D_ZTBJJ80_INDEX 500 64000 143.37
TEMP 346 4428852
TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M)
------------------------------------------------------------------------------------------ ------------ ---------- -------------
UNDOTBS17460 954880 308.62
EAS_D_ZTB80_TEMP2 1075 1376005.06
EAS_D_ZTBHH80_TEMP2 500 640002.12
EAS_D_ZTBJJ80_TEMP2 500 64000 1
EAS_T_ZTBHH80_STANDARD50 6400
EAS_T_ZTBJJ80_STANDARD55 7040
EAS_T_ZTB80_STANDARD1430 183040
18 rows selected.
SQL> alter tablespace ogg add datafile '/oradata/ORCL/datafile/ogg02.dbf' size 8192M autoextend on;
alter tablespace ogg add datafile '/oradata/ORCL/datafile/ogg02.dbf' size 8192M autoextend on
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table OGGADM.GGS_MARKER by 8192 in tablespace OGG
ORA-06512: at line 1314
ORA-01653: unable to extend table OGGADM.GGS_MARKER by 8192 in tablespace OGG
SQL> truncate table OGGADM.GGS_MARKER;
truncate table OGGADM.GGS_MARKER
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> alter database datafile '/oradata/ORCL/datafile/ogg01.dbf' autoextend off;
Database altered.
SQL> alter tablespace ogg add datafile '/oradata/ORCL/datafile/ogg02.dbf' size 8192M autoextend on;
alter tablespace ogg add datafile '/oradata/ORCL/datafile/ogg02.dbf' size 8192M autoextend on
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table OGGADM.GGS_MARKER by 8192 in tablespace OGG
ORA-06512: at line 1314
ORA-01653: unable to extend table OGGADM.GGS_MARKER by 8192 in tablespace OGG
SQL> truncate table OGGADM.GGS_MARKER;
truncate table OGGADM.GGS_MARKER
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> alter tablespace ogg add datafile '/oradata/ORCL/datafile/ogg02.dbf' size 8192M autoextend on;
alter tablespace ogg add datafile '/oradata/ORCL/datafile/ogg02.dbf' size 8192M autoextend on
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table OGGADM.GGS_MARKER by 8192 in tablespace OGG
ORA-06512: at line 1314
ORA-01653: unable to extend table OGGADM.GGS_MARKER by 8192 in tablespace OGG
SQL> select segment_name,sum(bytes)/1024/1024/1024 from dba_segments where owner='OGGADM' group by segment_name order by sum(bytes) desc;
SEGMENT_NAMESUM(BYTES)/1024/1024/1024
------------------------------ -------------------------
GGS_DDL_HIST 23.1064453
GGS_MARKER 4.90820313
GGS_DDL_HIST_i4 .75
GGS_DDL_HIST_i5 .5234375
GGS_DDL_HIST_i2.5
GGS_DDL_HIST_index1 .375
GGS_DDL_HIST_i6 .3125
GGS_DDL_HIST_i3 .3046875
GGS_DDL_HIST_i1 .296875
SYS_C00478392 .28125
GGS_MARKER_IND1 .1796875
SEGMENT_NAMESUM(BYTES)/1024/1024/1024
------------------------------ -------------------------
GGS_DDL_HIST_ALT_u1.1171875
GGS_DDL_HIST_ALT_u3.1171875
GGS_DDL_HIST_ALT_u2.09375
GGS_DDL_HIST_ALT.0703125
GGS_SETUP_UKEY.000061035
GGS_SETUP .000061035
17 rows selected.
SQL> truncate table GGS_DDL_HIST;
truncate table GGS_DDL_HIST
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> truncate table oggadm.GGS_DDL_HIST;
truncate table oggadm.GGS_DDL_HIST
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table OGGADM.GGS_MARKER by 8192 in tablespace OGG
ORA-06512: at line 1314
ORA-01653: unable to extend table OGGADM.GGS_MARKER by 8192 in tablespace OGG
SQL> delete from oggadm.GGS_DDL_HIST where rownum<1000;
999 rows deleted.
SQL> commit;
Commit complete.
SQL> delete from oggadm.GGS_DDL_HIST where rownum<10000;
9999 rows deleted.
SQL> commit;
Commit complete.
SQL> delete from oggadm.GGS_DDL_HIST where rownum<10000;
9999 rows deleted.
SQL> commit;
Commit complete.
SQL> delete from oggadm.GGS_MARKER where rownum<10000;
9999 rows deleted.
SQL> commit;
Commit complete.
SQL> delete from oggadm.GGS_MARKER where rownum<10000;
9999 rows deleted.
SQL> commit;
Commit complete.
SQL> delete from oggadm.GGS_DDL_HIST where rownum<10000;
9999 rows deleted.
SQL> commit;
Commit complete.
SQL> truncate table oggadm.GGS_DDL_HIST;
Table truncated.
SQL> @tbs.sql
truncating (as requested) before column USED_RATE(%)
truncating (as requested) before column FREE_SPACE(M)
TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M)
------------------------------------------------------------------------------------------ ------------ ---------- -------------
SYSTEM 1590 203520 1573.12
EAS_D_ZTB80_INDEX 3695 472960 3518.87
USERS 50 640047.56
EAS_D_ZTB80_STANDARD 11030 1411840 10432.5
SYSAUX 1600 204800 1507.56
EAS_D_ZTBHH80_STANDARD 2000 256000 776.06
EAS_D_ZTBJJ80_STANDARD 2000 256000769
EAS_D_ZTBHH80_INDEX 500 64000 148.06
EAS_D_ZTBJJ80_INDEX 500 64000 143.37
OGG32708.31 4186664 5973.87
TEMP 346 4428851
TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M)
------------------------------------------------------------------------------------------ ------------ ---------- -------------
UNDOTBS17460 954880 433.62
EAS_D_ZTB80_TEMP2 1075 1376005.06
EAS_D_ZTBHH80_TEMP2 500 640002.12
EAS_D_ZTBJJ80_TEMP2 500 64000 1
EAS_T_ZTBHH80_STANDARD50 6400
EAS_T_ZTBJJ80_STANDARD55 7040
EAS_T_ZTB80_STANDARD1430 183040
18 rows selected.
SQL> !more tbs.sql
set line 132
set wrap off
select t.*
from (SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - USED_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE,
ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE
FROM V$SORT_USAGE
GROUP BY TABLESPACE) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t
order by "USED_RATE(%)" desc;
SQL> truncate table oggadm.GGS_MARKER;
Table truncated.
SQL> col TABLESPACE_NAME for a30
SQL> @tbs.sql
TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
------------------------------ ------------ ---------- ------------- ------------ -------------
EAS_D_ZTB80_INDEX 3695 472960 3518.87 95.23 176.13
USERS 50 640047.56 95.122.44
EAS_D_ZTB80_STANDARD 11030 1411840 10432.5 94.58597.5
SYSAUX 1600 204800 1507.56 94.2292.44
SYSTEM 1590 203520 979.06 61.58 610.94
EAS_D_ZTBHH80_STANDARD 2000 256000 776.0638.8 1223.94
EAS_D_ZTBJJ80_STANDARD 2000 256000769 38.451231
EAS_D_ZTBHH80_INDEX 500 64000 148.06 29.61 351.94
EAS_D_ZTBJJ80_INDEX 500 64000 143.37 28.67 356.63
UNDOTBS17460 954880 456.626.12 7003.38
TEMP 346 44288 92.6337
TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
------------------------------ ------------ ---------- ------------- ------------ -------------
OGG32708.31 4186664 413.931.27 32294.38
EAS_D_ZTB80_TEMP2 1075 1376005.06.47 1069.94
EAS_D_ZTBHH80_TEMP2 500 640002.12.42 497.88
EAS_D_ZTBJJ80_TEMP2 500 64000 1.2499
EAS_T_ZTBHH80_STANDARD50 6400 0
EAS_T_ZTBJJ80_STANDARD55 7040 0
EAS_T_ZTB80_STANDARD1430 183040 0
18 rows selected.
SQL>
