ORACLE: ORA-01187: cannot read from file because it failed verification tests

 ORA-01187: cannot read from file  because it failed verification tests

ORA-01110: data file 3001: '/usr/opt/app/oracle/admin/gentest3/temp01/temp01.dbf'

ORA-06512: at "SYS.DBMS_LOB", line 741

ORA-06512: at "SYS.DBMS_DATAPUMP", line 5420

ORA-06512: at line 1



SQL> select FILE#,NAME  from  v$tempfile;

     FILE#
----------
NAME
--------------------------------------------------------------------------------
         1
/usr/opt/app/oracle/admin/gentest3/temp01/temp01.dbf




SQL> SELECT PROPERTY_VALUE   FROM DATABASE_PROPERTIES  WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP

SQL> ALTER TABLESPACE temp ADD TEMPFILE '/usr/opt/app/oracle/admin/gentest3/temp01/temp02.dbf' size 10M;

Tablespace altered.




SQL> ALTER DATABASE TEMPFILE '/usr/opt/app/oracle/admin/gentest3/temp01/temp01.dbf' DROP INCLUDING DATAFILES;

Database altered.






SQL> ALTER DATABASE TEMPFILE '/usr/opt/app/oracle/admin/gentest3/temp01/temp02.dbf' autoextend on maxsize 10024M;





SQL>  @/usr/opt/app/oracle/orahome/datafileusage.sql

***********************
*** DATAFILE STATUS ***
***********************

                                                                                               File Size     Max Size  Pct
Filename                                                               Tablespace Name              (MB)         (MB)  Max
---------------------------------------------------------------------- -------------------- ------------ ------------ ----
/usr/opt/app/oracle/admin/gentest3/temp01/temp02.dbf                   TEMP                           10       10,024    0



Comments

Post a Comment

Popular posts from this blog

Oracle: To clean up WRI$_ADV_OBJECTS

 set linesize 200; select segment_name, segment_type, bytes from dba_segments where tablespace_name='SYSAUX' order by bytes asc; COL OCCUPANT_NAME FORMAT A30 SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC; COL SEGMENT_NAME FORMAT A30 COL OWNER FORMAT A10 COL TABLESPACE_NAME FORMAT A10 COL SEGMENT_TYPE FORMAT A15 SELECT * FROM (SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,BYTES/1024/1024 "SIZE(MB)",SEGMENT_TYPE FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='SYSAUX' ORDER BY BYTES DESC) WHERE ROWNUM<=10; 1.  automatic clean-up default 31 days           SELECT dbms_stats.get_stats_history_availability from Dual; 2. modify retention policy        exec  dbms_stats.alter_stats_history_retention (14); 3. a manual cleaning  exec dbms_stats.purge_stats (sysdate-14);   ALTER TABLE WRI$_ADV_OBJECTS MOVE; ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD; ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 R...
Read more