Linux-x86_64 Error: 25: Inappropriate ioctl for device
ERROR at line 29:
ORA-01114: IO error writing block to file 202 (block # 1473756)
ORA-27072: File I/O error
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 1473756
Additional information: 90112
ORA-01114: IO error writing block to file 202 (block # 1473756)
ORA-27072: File I/O error
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 1473756
Additional information: 90112
---------------
select tablespace_name,file_name,bytes/1024/1024,status,autoextensible,increment_by,user_bytes/1024/1024 from dba_temp_files;
----------------------------------------------Existing Temp Tablespace------------------------------------
/ldccm_data1/ora11g/ldccmd/temp/ldccm_temp01.dbf
1024 ONLINE NO 0 1023
/ldccm_temptbs/ora11g/ldccmd/temp/ldccm_temp02.dbf
23028 ONLINE YES 1 23027
/ldccm_temptbs/ora11g/ldccmd/temp/ldccm_temp03.dbf
23026 ONLINE YES 1 23025
FILE_NAME
----------------------------------------------------------------
BYTES/1024/1024 STATUS AUT INCREMENT_BY USER_BYTES/1024/1024
--------------- ------- --- ------------ --------------------
/ldccm_temptbs/ora11g/ldccmd/temp/ldccm_temp04.dbf
23026 ONLINE YES 1 23025
/ldccm_temptbs/ora11g/ldccmd/temp/ldccm_temp05.dbf
23026 ONLINE YES 1 23025
/ldccm_temptbs/ora11g/ldccmd/temp/ldccm_temp06.dbf
23026 ONLINE YES 1 23025
---------------------------------Solution-------------------
/ldccm_temptbs/ora11g/ldccmd/temp/ldccm_temp01.dbf
1)
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/ldccm_temptbs/ora11g/ldccmd/temp/ldccm_temp10.dbf' SIZE 1024M REUSE
AUTOEXTEND ON NEXT 500M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 500M;
2)
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
3)
DROP TABLESPACE temporary INCLUDING CONTENTS AND DATAFILES;
---if dropping hang then restart oracle services or do following
SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;
alter system kill session 'SID_NUMBER, SERIAL#NUMBER'; kill those session that are not being used actually.
Now dropping the previous tablespace
DROP TABLESPACE
4)
CREATE TEMPORARY TABLESPACE temporary
TEMPFILE '/ldccm_temptbs/ora11g/ldccmd/temp/ldccm_temp01.dbf' SIZE 20480M REUSE
AUTOEXTEND ON NEXT 1024M;
5)
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temporary;
6)
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
7)
SELECT tablespace_name, file_name, bytes
FROM dba_temp_files WHERE tablespace_name = 'temporary';
-------------------------------------------
