Dangerous autoextensive tempfile

Developer feedback running a sql in a recently cloned database 11.2.0.2 ADMB2 and hit funny error ora-01114.

Error as follows.

ORA-01114: IO error writing block to file (block # )

ORA-01114: IO error writing block to file 201 (block # 2321408)

ORA-27072: File I/O error

Additional information: 4

Additional information: 2321408

Additional information: -1

Tested it in source database ,there is no such error.

Invoked oerr to lookup the error message, the file id complained is 201,  which is not found in dba_data_files.


dbsvr9:ADMB2:/software/oracle1/admin/scripts/performance> oerr ora 1114
01114, 00000, "IO error writing block to file %s (block # %s)"
// *Cause:  The device on which the file resides is probably offline. If the
//          file is a temporary file, then it is also possible that the device
//          has run out of space. This could happen because disk space of
//          temporary files is not necessarily allocated at file creation time.
// *Action: Restore access to the device or remove unnecessary files to free
//          up space.

Then focuse on checking temporary files.

However, the tempfile is about 18GB already and almost 100% free as show below.
and, the mount pointing of tempfile is 100% used.


SYS@ADMB2> select * from dba_temp_files;

FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS  RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY
---------- ------------------------------ ---------- ---------- ------- ------------ --- ---------- ---------- ------------
USER_BYTES USER_BLOCKS
---------- -----------
/admadm/ADMB2/temp01.dbf
         1 TEMP                           1.9018E+10    2321536 ONLINE             1 YES 3.4360E+10    4194302           80
1.9017E+10     2321408


SYS@ADMB2> @chk_temp_by_user.sql
check tempoary space used by session

no rows selected


no rows selected

SYS@ADMB2> @chk_temp_usage.sql

percent used
------------
           0


TABLESPACE             MB_TOTAL    MB_USED    MB_FREE
-------------------- ---------- ---------- ----------
TEMP                      18137          0      18137



Funny.
What does file_id 201 stand for ?
No session is hogging the temp tablespace.


The next intuition  is why the tempfile is antoextensive.

Trying to turn id off, and shank it down.

SYS@ADMB2> alter database tempfile '/admadm/ADMB2/temp01.dbf' autoextend off;

Database altered.

SYS@ADMB2> alter database tempfile '/admadm/ADMB2/temp01.dbf' resize 2g;

Database altered.

At last, run the sql again. wow, 15429 rows selected, and right close 7pm, and luckily this is not a production database.



From this case, it seems to even when there is free space in temporary tablespace, Oracle database always acquires space from file system, although there is no more disk space in the file system, and can't look from existing tablespace.  Such behaviour is "silly" and dangerous.

How to Compare Two Versions of the Same Word 2010 Documents

useful link I found, mark here.

How to Compare Two Versions of the Same Word 2010 Document

 Compare documents side by side

Prerequisites of privilege needed for creating a view

re-read the oracle document and gain the following and highlight in bold for underlying base table owned another schema.

Prerequisites
To create a view in your own schema, you must have the CREATE VIEW system privilege. To create a view in another user's schema, you must have the CREATE ANY VIEW system privilege.
To create a subview, you must have the UNDER ANY VIEW system privilege or the UNDER object privilege on the superview.
The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.