The treatment of some tablespace problems in Oracle
First, insufficient SYSAUX table space
SYSAUX table space is the auxiliary table space of SYSTEM table space, which mainly stores EM-related contents as well as table statistics, AWR snapshots, audit information, etc., while if SYSAUX table space is not configured by default, it will expand more and more over time.
1. What occupies sysaux space select occupant_name, schema_name, occupant_desc, space_usage_kbytes from v$sysaux_occupants order by space_usage_kbytes desc
As you can see, it is mainly AWR that takes up space.
2. Clear AWRSQL > connect / as sysdbaSQL > @? / rdbms/admin/catnoawr.sqlSQL > @? / rdbms/admin/catawrtb.sql3, check the tablespace occupancy SELECT B.TABLESPACE_NAME tablespace, B.FILE_NAME data file name, B.BYTES / 1024 / 1024 size M, (B.BYTES-SUM (NVL (A.BYTES, 0)) / 1024 / 1024 has used M, SUBSTR ((B.BYTES-SUM (NVL (A.BYTES)) 0)) / (B.BYTES) * 100,1,5) | |'% 'utilization rate FROM DBA_FREE_SPACE A, DBA_DATA_FILES B WHERE A.FILE_ID = B.FILE_ID GROUP BY B.TABLESPACE_NAME, B.FILE_NAME, B.BYTES ORDER BY B.TABLESPACENAMEII, insufficient USER table space 1, insufficient zabbix alarm table space
2. Check the database table space occupancy SELECT B.TABLESPACE_NAME table space, B.FILE_NAME data file name, B.BYTES / 1024 / 1024 size M, (B.BYTES-SUM (NVL (A.BYTES, 0) / 1024 / 1024 M has been used, SUBSTR ((B.BYTES-SUM (NVL (A.BYTES, 0) / (B.BYTES) * 100,1 5) | |'% 'utilization FROM DBA_FREE_SPACE A, DBA_DATA_FILES B WHERE A.FILE_ID = B.FILE_ID GROUP BY B.TABLESPACE_NAME, B.FILE_NAME, B.BYTES ORDER BY B.TABLESPACE_NAME
3. Increase the tablespace Alter tablespace USERS add datafile'/ ordata/orcl/users17.dbf' size 20g 3. Insufficient SYSTEM table space 1. View table space usage SELECT B.TABLESPACE_NAME table space, B.FILE_NAME data file name, B.BYTES / 1024 / 1024 size M, (B.BYTES-SUM (NVL (A.BYTES, 0)) / 1024 / 1024 M has been used, SUBSTR ((B.BYTES-SUM (NVL (A.BYTES, 0) / (B.BYTES) * 100,1 5) | |'% 'usage FROM DBA_FREE_SPACE A, DBA_DATA_FILES B WHERE A.FILE_ID = B.FILE_ID GROUP BY B.TABLESPACE_NAME, B.FILE_NAME, B.BYTES ORDER BY B.TABLESPACE_NAME2, find objects with large occupancy
Find out the top 10 objects that occupy the SYSTEM table space from dba_segments.
SELECT * FROM (SELECT SEGMENT_NAME, SUM (BYTES) / 1024 / 1024 MB FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'SYSTEM' GROUP BY SEGMENT_NAME ORDER BY 2 DESC) WHERE ROWNUM < 10, turn off audit (optional)
After the system table space is full, empty the audit table and overwrite the old one with the new one. If it is of little use, we can choose to turn off the audit function, as follows
Sqlplus / nologconnect sys/sys as sysdbaalter system set audit_trail='NONE' scope=spfile;shutdown immediate;startup;4, clear audit log truncate table SYS.AUD$;5, add data file alter tablespace system add datafile'/ data/orcl/system02.dbf' size 2G to tablespace
