Hi, we have an AVDF 12.2.0.9.0 appliance (not in production). Some of the users reported problems with the web interface, and while trying to troubleshoot this, I found that tablespace AVSYS is full:
Tue Aug 13 18:27:15 2019
Errors in file /var/lib/oracle/diag/rdbms/dbfwdb/dbfwdb/trace/dbfwdb_j000_21173.trc:
ORA-12012: error on auto execute of job "AVSYS"."SYSTEM_METRICS_JOB"
ORA-01653: unable to extend table AVSYS.SYSTEM_METRICS by 128 in tablespace AVSPACE
ORA-06512: at "AVSYS.METRICS_COLLECTION", line 196
It is a bigfile tablespace, and the lone datafile's size is 44.88G:
SQL> select file#, name, bytes/1048576 "Mb"
2 from v$datafile
3 where name like '%avspace%'
4 /
FILE# NAME Mb
---------- ------------------------------------------------------------ ----------
5 +EVENTDATA/DBFWDB/DATAFILE/avspace.256.990984471 45963.625
As a matter of fact, that file has taken almost all of the diskgroup's space:
[grid@avs005056905575 ~]$ asmcmd lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 47878 58 0 58 0 N EVENTDATA/
MOUNTED EXTERN N 512 4096 1048576 71817 68143 0 68143 0 N RECOVERY/
MOUNTED EXTERN N 512 4096 1048576 47877 40941 0 40941 0 N SYSTEMDATA/
I checked the biggest segments within it, and I see these 2 (a table and its index):
SQL> r
1 select owner, segment_name, segment_type, bytes/1048576 "Mb"
2 from dba_segments
3 where tablespace_name = 'AVSPACE'
4 and bytes/1048576 > 10240
5* order by 4 desc
OWNER SEGMENT_NAME SEGMENT_TYPE Mb
-------------------- ------------------------------ ------------------ ----------
AVSYS UE_USER_OBJ_PRIVS TABLE 22208
AVSYS UE_USER_OBJ_PRIVS_SRCTBL_IDX INDEX 19904
The table has +300 million rows.
I have a few questions:
- What is the purpose of table UE_USER_OBJ_PRIVS?
- Can it be purged?
- Since non of the data is important (this was used for a training), can we "reset" it, without doing a re-install?
Thanks!
Hiroshi Komatsu