SQL> ALTER TABLESPACE TEMP ADD TEMPFILE size 3072M;
ALTER TABLESPACE TEMP ADD TEMPFILE size 3072M
*
ERROR at line 1:
ORA-01119: error in creating database file '+DATA'
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup "DATA" space exhausted
solution :-
[oracle@dba ~]$ sqlplus sys/***@QA as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Dec 1 06:10:22 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c EE High Perf Release 12.2.0.1.0 - 64bit Production
SQL> truncate table AUD$;
Table truncated.
SQL> commit;
Commit complete.
...........................................................................................................
Check ASM disk status
ERROR:
ORA-02002: error while writing to audit trail
ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM
ORA-02002: error while writing to audit trail
ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM
ORA-02002: error while writing to audit trail
ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM
----------------------or---------------.............
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE size 3072M;
ALTER TABLESPACE TEMP ADD TEMPFILE size 3072M
*
ERROR at line 1:
ORA-01119: error in creating database file '+DATA'
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup "DATA" space exhausted
>>>>>>>>>>>>>>>>>>>>>>>>>>>>SOLUTION>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SQL> select group_number, name, total_mb, free_mb from v$ASM_DISKGROUP;
GROUP_NUMBER NAME TOTAL_MB FREE_MB
------------ ------------------------------ ---------- ----------
1 DATA 262144 600
2 RECO 262144 257936
SQL> select TABLESPACE_NAME, BYTES_USED/1024/1024, BYTES_FREE/1024/1024 from V$TEMP_SPACE_HEADER;
TABLESPACE_NAME BYTES_USED/1024/1024 BYTES_FREE/1024/1024
------------------------------ -------------------- --------------------
TEMP 33 0
SQL> select name, total_mb, free_mb, state from v$asm_disk;
NAME TOTAL_MB FREE_MB STATE
------------------------------ ---------- ---------- --------
RECODISK1 65536 49028 NORMAL
RECODISK2 65536 49028 NORMAL
RECODISK3 65536 49044 NORMAL
DATA_0003 65536 152 NORMAL
DATA_0000 65536 140 NORMAL
DATA_0001 65536 172 NORMAL
DATA_0002 65536 136 NORMAL
RECODISK4 65536 49020 NORMAL
8 rows selected.
SQL>
SQL>
SQL>
SQL> select name, total_mb, free_mb, required_mirror_free_mb, usable_file_mb ,type from v$asm_diskgroup where name='DATA';
NAME ---TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB TYPE
-------------- ------
DATA 262144 600 0 600 EXTERN
SQL> select path, free_mb,total_mb,state from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name='DATA');
PATH FREE_MB TOTAL_MB STATE
---------- ---------- --------
/dev/DATADISK4 152 65536 NORMAL
/dev/DATADISK3 140 65536 NORMAL
/dev/DATADISK2 172 65536 NORMAL
/dev/DATADISK1 136 65536 NORMAL
SQL> select group_number, operation, state, error_code from v$asm_operation;
no rows selected
SQL> select TABLESPACE_NAME, BYTES_USED/1024/1024, BYTES_FREE/1024/1024 from V$TEMP_SPACE_HEADER;
TABLESPACE_NAME BYTES_USED/1024/1024 BYTES_FREE/1024/1024
------------------------------ -------------------- --------------------
TEMP 33 0
SQL>
SQL> select * from dba_temp_files where tablespace_name like 'TEMP';
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- -------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
SHARED INST_ID
------------- ----------
+DATA/CDB/TEMPFILE/temp.264.1029707049 1 TEMP 34603008 4224 ONLINE
1 YES 3.4360E+10 4194302 80 33554432 4096 SHARED
No comments:
Post a Comment