ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces

I am a great fan of IOTs aka Oracle's Index Organized Tables.
As written in the documentation, they are most useful when accessed by their primary key, for the simple fact that the table's rows are kept "in order" according to such index, which means they can be scanned very efficiently.
Typical candidates for IOTs are lookup tables, that is tables where you know the value of their primary key in advance.
A very common scenario for an IOT is to store simple data like a table of cities (city_id and city_name) and you want to display the name of a store along with the city name where the store is located.
Normally you don't put the city name in the stores table, but you put the city_id, that is the foreign key to the cities table and then you look up city_name by means of a join (hence the name lookup table...).

So, back to my problem, i was trying to execute a DDL statement like:

create table yocoya_notifications
(application_id  number,
language_code   varchar2(5),
message_text    varchar2(4000),
last_updated_on date default sysdate,
last_updated_by varchar2(255),
constraint yocoya_notifications_pk primary key (application_id, language_code)
) organization index
/

but with some surprise i got:

ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces

Hmm, i was sure i had already created many other IOTs in the past with such minimal instructions.

I went to the error description and it says:

Cause: No overflow segment defined.

Action: Add overflow segment.

Why i do i need to add an overflow segment? I didn't need it in the past!

The fact is easily explained.
If i recreate the table with a smaller size for column MESSAGE_TEXT, say VARCHAR2(2000) instead of VARCHAR2(4000), Oracle accepts the DDL statement without a hitch.

If you read the documentation about CREATE TABLE, where it starts talking about the IOT overflow segment, then you'll find a reference to a possible error condition that can be raised when Oracle estimates that the row size might be larger than it fits in a single block.
Note also that i didn't specify a value for PCTTHRESHOLD, this means that Oracle defaulted it to 50 (% of block size), which in my case would mean 4096 bytes because the tablespace is made up of blocks of 8192 bytes.

At this point, in order to store up to 4000 bytes in this column, i'll have to specify an overflow segment that can accommodate MESSAGE_TEXT along with all the subsequent columns (LAST_UPDATED_ON and LAST_UPDATED_BY) in a separate block.

In practice, in the simplest case, this translates into adding the keyword OVERFLOW at the end of the statement above.

-------------------------------------------------

ORA-01429: Tabella organizzata a indice: nessun segmento dati per memorizzare row-pieces di overflow
ORA-01429: Tabla organizada por índice: no hay ningún segmento de datos para almacenar las partes de filas desbordadas
ORA-01429: Taula organitzada en índexos: no hi ha segment de dades per a emmagatzemar peces de fila de sobreeiximent
ORA-01429: Table organisée en index : pas de segment de données pour stocker les données de ligne en dépassement
ORA-01429: Index-organisierte Tabelle: Kein Datensegment zum Speichern überlaufender Zeilenstücke
ORA-01429: Πίνακας με Οργάνωση Ευρετηρίου: δεν υπάρχει τμήμα (segment) δεδομένων για αποθήκευση κομματιών γραμμών υπερχείλισης
ORA-01429: Indeksorganiseret tabel: Intet datasegment til lagring af overløbsrækkestykker
ORA-01429: Indexerad tabell: inga datasegment vid lagring av  radspill
ORA-01429: Indeksert tabell: intet datasegment for lagring av ekstra radstykker
ORA-01429: Indeksi-organisoitu taulu: ei tieto-osaa, johon tallentaa riviylivuodot
ORA-01429: Index-szervezésű tábla: nincs adatszegmens a túlcsorduló sor-részek tárolásához
ORA-01429: Tabelă Organizată Index: nu există segment de date ptr. stocarea liniilor în plus
ORA-01429: Tabel met indexordening: geen gegevenssegment om overloop van rijonderdelen op te slaan.
ORA-01429: Tabela Organizada por Índice: não há segmento de dados para armazenar componentes de linha de overflow
ORA-01429: Tabela Organizada por Índices: não existe segmento de dados para armazenar excesso de row-pieces
ORA-01429: Индексная таблица: отсутствует сегмент данных для записи дополнительных отрезков строки
ORA-01429: Tabulka org. podle indexu: není datový segment pro uložení přetékajících částí řádku
ORA-01429: Indexovo organizovaná tabuľka: žiadny dátový segment na uloženie pretečených riadkových kusov
ORA-01429: Tabela wyłącznie indeksowa: brak segmentu danych pozwalającego przechować nadmiarowe wiersze
ORA-01429: Dizin-Düzenli Tablo: taşan satır parçalarını saklayacak veri segmenti yok

Apex's sVista

If you have a multi language application in Apex 3.0.0.00.20 and you execute the following query:

select WORKSPACE, APPLICATION_ID, TRANSLATABLE_MESSAGE,
LANGUAGE_CODE, MESSAGE_TEXT, LAST_UPDATED_ON, LAST_UPDATED_BY
from APEX_APPLICATION_TRANSLATIONS
where LAST_UPDATED_ON is not null;

if you get any rows back, you can notice that the two columns names do not match their real content data type:

LAST_UPDATED_ON contains a user name (VARCHAR2) while LAST_UPDATED_BY contains a date.

Just a minor bug i suppose, but who knows if oracle will keep the view as it is for backward compatibility...

That's all for today.

PS: "svista" in italian sounds like "mistake", whereas "vista" is the translation of view.

Accessibility, TABINDEX, ACCESSKEY and a weird Apex quirk

I am eventually back to my desk after an exciting week spent traveling across USA, where most of my time was spent either on a aircraft or in a queue waiting to be searched by security agents, plus a couple of days in Daytona Beach, at the ODTUG Kaleidoscope 2007, so now you know why i didn't update my blog in the last 9 days.
Actually if i reckon all the hours wasted in the long lines of people at the airports, i could have written a dozen new blog entries!

You can find a variety of detailed comments on the ODTUG Kaleidoscope 2007 in other blogs, see the work done by Dimitri Gielis for example (by the way, thank you Dimitri, i enjoyed the meetings with you and the other APEX gurus!), including photos of the attendees and snapshots of the slides being shown.

Even if many of the topics were already known to me, it was an opportunity to learn about certain details that i had overlooked in the past and HTML's TABINDEX attribute is one of such cases.
Scott Spendolini, President of Sumnertech, included a reference to this form element property in his presentation (that spanned many aspects of Oracle Application Express by the way) and i realized that i had never paid too much attention on web accessibility issues previously.

TABINDEX plays an important role in the keyboard navigation of a web page and you can read more about the rules that a W3C HTML 4.0 compliant browser should follow when it comes to move the so-called cursor focus.

If you are mousaholic, it can be of scarce importance to you, however there are a lot of people out there, especially the visually impaired ones, who are forced to use this approach when browsing pages, consisting in hopping from one form element to the next one, in the order specified by the various TABINDEXex, so it is something that you might want to consider carefully when creating pages with Oracle APEX.

Not only, if you are developing web pages on behalf of governmental institutions, you may be bound to certain standards like Section 508 of the Rehabilitation Act in the USA or other similar requirements by other countries.

If you are not yet convinced, then imagine that your mouse breaks down or it runs out of batteries and you there are no replacements at hand.

You could be forced to navigate web sites using the keyboard.
So it is not something that you can quickly dismiss without any concerns.

There is nothing particularly difficult in the subject but it is important to understand how the whole thing works in an APEX page, where there is no specific TABINDEX property defined as such, with the only exception of the page level property called Cursor Focus, that allows either to set the focus on the first item of a page (in Apex Region/Items order) or to not focus any particular item.



So, how does a typical Apex page work as far as TABINDEX is concerned?

If you look at the page source of an Apex page, you will find a lot of elements with TABINDEX="999", that is default value provided by Apex.
If you opted for having the focus on first item of page, then you should find a script element near the bottom of the page that looks like the following:

<script type="text/javascript">
<!--    first_field('item');

//-->
<script>

Where item is the name of the first item in the displayed sequence of items defined in Apex.
As you see, Apex doesn't tamper with TABINDEXex to obtain the result but instead it uses a javascript program to show the cursor in the relevant position.

However, while working on this topic, i found out a really strange quirk affecting this configuration setting. The problem seems to be known to some extent, i found a couple of messages in the OTN forum where people talk of a similar problem but the real reason was not clearly identified, so i invested some time to figure out where was the catch exactly.

I could not try with other Apex versions, so what i am saying applies to version 3.0.0.00.20 basically, however i do believe that it holds for previous ones as well.

In short, this piece of Javascript is not included even if you selected the relevant option as outlined above, when the item on which you want to put the focus is of type "text field always submits page when Enter is pressed".

In other words, if item P1_XXX is of that special type *AND* it is also the first one to be displayed, then it cannot receive the focus because, for some unknown reason, the link to the javascript code will be omitted.

Is it a bug or is it a feature? Don't ask me.

Aside from this Apex alleged quirk, the general rule (explained in the document linked above) defines that the focus will move between items in character stream order, that is in order of appearance within the HTML source files when all TABINDEX values are the same and, by default, in APEX they are all set to 999.

If the focus is on the first element of the page (whatever this means...), pressing the TAB keys (TAB or SHIFT TAB), will take you to the next or previous element as they appear in the source file, which can be a consistent behavior for most pages actually.

Frankly speaking i made several attempts with different browsers in order to understand if their behavior was consistent with the aforementioned rules but i had to give up.

It seems to me that both latest versions of Internet Explorer and Firefox do not follow these rules when it comes to decide which element is the first to receive the focus, but once you have manually set the focus on some page element, then the cursor is moved consistently according to the rules.

So, unless you are satisfied with the built-in order that comes off the region/items sequencing combination, you may want to rearrange items using a different order that is more consistent from the user experience point of view.

For instance, if you have a page where the main input fields items are in a central region surrounded by other regions and items on both sides, you are probably better off setting the TABINDEX of each page item manually.
Enabling cursor focus on the first item of a page doesn't interfere with TABINDEX ordering although you can easily achieve inconsistent settings because there is no direct relationship between Apex's item sequence and the TABINDEX values you define in the item's attributes.
For example, you may have a page where you set TABINDEX="10" for the first item displayed and TABINDEX="1" on an the fifth element displayed.
Clearly this doesn't make much sense, but there is nothing to stop you from doing it.

Setting TABINDEX or ACCESSKEY (see below for more on ACCESSKEY) is accomplished in the following ways, depending on the item element type:

1. Standard items:
   
   
2. Standard buttons (either HTML, image or template based buttons):
   
   
3. Special button items (HTML buttons):
4. Special buttons (image or template based buttons):
   
   

Note that if you click on the Attributes label in these forms, the online help will suggest exactly this type of usage for this attribute property. Also, in order to successfully set the property, the button template must contain the #BUTTON_ATTRIBUTES# substitution string.

All right, but what kind of page elements come with this TABINDEX attribute?
As per the W3C document, eligible HTML tags are only: A, AREA, BUTTON, INPUT, OBJECT, SELECT, and TEXTAREA.

• A maps to links, either internal pages or external URLs, including pop-up select lists and date pickers, as well as image or template based buttons and labels with help.
• AREA is not mapped to any built-in page element.
• BUTTON maps to HMTL buttons (both for region and item buttons).
• INPUT maps to text fields, checkboxes and radiobuttons in their various forms.
• OBJECT is not mapped to any built-in page element.
  
• SELECT maps to combo boxes in their various forms.
• TEXTAREA maps to textarea items in their various forms.

Another thing worth mentioning is the fact that in Apex items are often preceded by a label.
If the label is created using a template and the template includes a link to a pop-up help window, you must consider that the link is made up of an A tag embedding an image or text and that it can receive the focus. If you look at one of these label templates you will probably see a hardcoded TABINDEX="999", if any.

Currently i don't know of any simple way of dynamically setting a different value for TABINDEXex contained in label templates, because the problem is to keep it in sync with the underlying form element's TABINDEX value. My guess is that both values should be the same in order to mantain a consistent navigation order, but probably the only way to do that is to update the label's TABINDEX property values by means of some javascript code that loops on the target elements at onLoad time. But this is easier to say than to do.

Finally, let's talk about ACCESSKEY. This attribute specifies a keyboard shortcut for quickly moving the cursor focus from one element to another. For instance, in a login page you could define value "P" as ACCESSKEY for the User ID text field and "P" for the password. This setting would allow a user to get to the desired field by pressing either ALT+U or ALT+P on a Windows PC equipped with an English version of Internet Explorer 7 .
Practically speaking, one can add ACCESSKEY definitions in the same way as TABINDEX (see pictures above).

Unfortunately this is pure academical theory.

The blunt reality is that browser implementations and language localization differences make ACCESSKEY completely unusable unless you are 100% sure of the browser version that users will use to navigate your application.

Just to make a short list of main ACCESSKEY usage problems i found so far:

• ALT+D will not work because both IE and Firefox perform the address bar activation.
• ALT+U will not work in spanish version of IE because it opens the Help (Ayuda) menu.
  
• ALT+ char for any characters mapped to the built in menu functions will not work either (language edition dependent).
• Firefox implemented changes between versions 1.5 and 2.0., apparently in 2.0 one must press SHIFT+ALT+char, and char must not be a number, but i could not get it to work, no matter what the character was in FF 2.0.0.4.
• in Safari for Mac O/S, you must use Ctrl+char supposedly, however a page working with IE and Opera failed to work for some unknown reason.
• in IE7, when accesskey is attached to an A element, then you must also press Enter to follow the link.
  
• probably many other amenities i had no time to try out.
  

A far more positive experience was with Opera browser, where a user can activate accessibility keys by pressing SHIFT+ESC first and the browser will display a preview list of all the available shortcuts, thereafter by pressing char the cursor focus can be set onto the desired element.

In the end, given the state of the art, i don't see how one can get ACCESSKEY to work reliably in mixed environments, so the only accessibility feature that one can reasonably implement in Apex remains the TABINDEX attribute.

Monitoring free space in Apex

Although in the Monitor Activity section of Apex you can find a report called schema tablespace utilization, sometimes i find it a bit misleading because it doesn't take into account the real quota assigned to a certain schema on a given tablespace.

For instance, in my case, i have an hosted account at Shellprompt.net, and this report is telling me that i have now 43Mb left on my "primary" tablespace, whereas the difference between my quota and the actual amount of allocated space is 73Mb, in other words, there are another 30Mb of additional space that are not considered in the schema utilization report.

So, in order to have all these calculations readily available, i created the following query, that you can easily turn into a view, if you like.

Notice: Use at your own risk!

When i ran this query on apex.oracle.com within SQL Workshop, my browser got locked up, probably because the server was under a heavy load and it never returned a result (or i gave up before it did...), so this query is meant to be run either in batch mode or during off peak hours.

I am warning you of this possibility because i don't want that you come back and complain that you've lost all your unsaved work in another browser session.

select
a.tablespace_name as "TS Name",
decode(max(c.priv), 1, 'unlimited priv',
decode(max(a.max_blocks), -1, 'unlimited quota',
to_char(round(sum(a.max_bytes)/1024/1024)||'Mb')))
as "Total Quota Mb",
to_char(round(sum(a.bytes)/1024/1024)||'Mb') as "Used Quota Mb",
decode(max(c.priv), 1, 'unlimited priv',
decode(max(a.max_blocks), -1, 'unlimited quota',
to_char(round(sum(a.max_bytes-a.bytes)/1024/1024)||'Mb')))
as "Max Free Space Mb",
to_char(round(sum(b.bytes)/1024/1024)||'Mb') as "Current Free Space Mb"
from user_ts_quotas a,
(select tablespace_name, sum(bytes) as bytes
from user_free_space
group by tablespace_name) b,
(select count(*) as priv
from user_sys_privs
where privilege = 'UNLIMITED TABLESPACE') c
where a.max_blocks != 0
and a.tablespace_name = b.tablespace_name
group by a.tablespace_name;

The difference between the built-in report and this new query is simply explained:

TS Name	Total Quota Mb	Used Quota Mb	Max Free Space Mb	Current Free Space Mb
YOCOYA	200Mb	127Mb	73Mb	43Mb

Total quota is the maximum allowance that has been granted on the tablespace and it can return "unlimited quota" or "unlimited priv" in case you have been granted unlimited quota on the tablespace or the unlimited tablespace system privilege, respectively.
Used quota is the sum of the allocated space taken by your objects.
Max Free Space is simply (Total Quota - Used Quota).
Current Free Space is equivalent to the number returned by the tablespace utilization report.

Note that if the datafile(s) pointed to by your tablespace are not set to autoextend on and the tablespace size is lower than your quota, then the theoretical maximum free space calculated in the last column of this query might not be entirely available unless the DBA manually enlarges the datafile(s). And even in that case, there must enough free disk space for the datafile to autoextend!

The purpose of this query is also to give you some visibility on your actual quota, a key parameter of any hosting contract and that is not displayed by Apex unless you run a query on view USER_TS_QUOTAS (see my previous posting on this topic).

I included this query in the latest version (1.01) of my basic Oracle Application Express monitoring package, called Apex simple pager that i made available for download a couple of weeks ago.

See more articles about Oracle Application Express or download tools and utilities.

ORA-02393: exceeded call limit on CPU usage

If you get the following message:

ORA-02393: exceeded call limit on CPU usage

it means that there is a profile attached to the user definition with an enforced limit on the CPU_PER_CALL parameter.

Such restrictions work only if system parameter RESOURCE_LIMIT is set to TRUE, which can also be set dynamically with

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE SCOPE=MEMORY;

but are not enforced for user sessions there were already connected prior to executing the ALTER command.

In other words, if you want to enforce these limits, you'll need first to disconnect all affected user sessions or bounce the database supposing that you made the change permanent by indicating SCOPE=SPFILE or SCOPE=BOTH.

Note also that profile changes incur in the same problem, so if the DBA adjusts your CPU_PER_CALL limit, the change will not affect currently opened sessions.

Unfortunately there is no built-in view that allows a normal user to query his/her profile limits.
Only the DBA or a user with the necessary privileges can query view DBA_PROFILES and the usual alternate views beginning with the prefixes ALL and USER do not exist in this case.

See message translations for ORA-02393 and search additional resources.

---

ORA-02393: superato il limite di chiamate sull'uso della CPU
ORA-02393: ha excedido el límite de llamadas para uso de CPU
ORA-02393: s'ha excedit el límit de crides en l'ús de la CPU
ORA-02393: limite d'appel sur utilisation CPU dépassée
ORA-02393: Aufrufgrenze (Call limit) für CPU-Nutzung überschritten
ORA-02393: ξεπεράσθηκε το όριο κλήσεων για χρήση ΚΜΕ
ORA-02393: overskrider opkaldsgrænsen for CPU-forbrug
ORA-02393: högsta antalet anrop för CPU-bruk har överskridits
ORA-02393: overskred kallgrensen for prosessorbruk
ORA-02393: kutsujen enimmäismäärä keskusyksikössä ylitetty
ORA-02393: CPU hívás szám korlát túllépve
ORA-02393: s-a depăşit limita de apel la folosirea CPU
ORA-02393: Aanroeplimiet voor CPU-gebruik is overschreden.
ORA-02393: limite de chamada excedido no uso de CPU
ORA-02393: excedido limite de chamada na utilização de CPU
ORA-02393: превышен предел вызовов на использование CPU
ORA-02393: překročen limit volání na využití CPU
ORA-02393: prekročený limit spojenia na čas CPU
ORA-02393: przekroczono ograniczenie użycia CPU przez wywołanie
ORA-02393: aramanın CPU kullanımı sınırı aşıldı

USER_TS_QUOTAS's new column in Oracle 10gR2 and XE

I just noticed that view USER_TS_QUOTAS, giving the list of tablespace quotas on the granted tablespaces, in version 10GR2 comes with an additional column called "DROPPED", containing a YES/NO flag.

I guess this is meant for our convenience, because these tablespace quotas are rather special beasts as you can see below:
for instance, once you have granted a tablespace quota to a user, through the CREATE or ALTER USER statement, the only way to revoke the quota is to lower it to zero:

ALTER USER xyz QUOTA 0 ON yocoya;

This means that any previously created objects will remain in the tablespace and they cannot grow any more from that moment on.

Of course what stated above doesn't apply to users granted UNLIMITED TABLESPACE privilege, as they can create objects everywhere regardless of quotas.

If no objects were created and your quota has been set to zero, you may see a line like the following in the corresponding tablespace entry (on a 10gR1 server):

TABLESPACE_NAME	BYTES	MAX_BYTES	BLOCKS	MAX_BLOCKS
YOCOYA	0	0	0	0

On a XE database, the same view returns also the column DROPPED, mentioned above:

TABLESPACE_NAME	BYTES	MAX_BYTES	BLOCKS	MAX_BLOCKS	DROPPED
YOCOYA	0	0	0	0	YES

So, tablespace quotas are very persistent, indeed they persist *after* dropping the relevant tablespace, but on 10gR2 you can quickly check whether the relevant tablespace is still present or if it has been dropped and this may be useful information to know, i guess.

Interestingly enough, view DBA_TS_QUOTAS will not display empty quotas like that above regardless of the tablespace existence. If you look at the code behind DBA_TS_QUOTAS, you'll see that the view if filtering out rows with MAX_BLOCKS equal to zero.

Different ways of counting clicks in Apex 3.0

As of Apex version 3.0 there is the possibility of enabling the click count declaratively for List entries, a component whose typical purpose is to display a list of external URLs or application page links.

This new declarative approach requires the setting of a couple of properties in the Application Builder page where you edit a single List entry (see the picture below).
The path to the page is Application Builder / Shared Components / Navigation / Lists / List Entries / Create Edit List Entry

The advantage of this new feature is in that you don't need to build the URL on your own and a couple of required parameters are automatically taken care of by Apex (the user and the workspace id).

In Apex 3.0 there is also the possibility of querying a view called APEX_WORKSPACE_CLICKS, for a full fledged report containing all the available information regarding these events.
Indeed the columns provided are more than those displayed in the built-in report accessible from the Monitor Usage Page of Oracle Application Express.

One of the key columns of this view is CLICK_ID, but as you can easily see yourself, if you enable the "automatic" click counting of Apex 3.0, that column will always be empty.

Now, a typical requirement is to reconcile the click with some session information recorded in the access log and i find that column CLICK_ID is a good candidate for tracking clicks, because, typically, the category should only contain a more generic string.

Oddly enough, the Apex 3.0 click count feature doesn't provide a way of setting the click id property available in the Z function (parameter p_id), which the closest candidate for storing a unique click identifier in my humble opinion.

You can see in the picture above that the first property enables the counting and the second one allows you to set the count category, but there is no entry point for the click id.

I have a fancy theory about the lack of this parameter that i'll explain later on.

So, if you don't mind knowing more about who clicked on a certain link, then you can take advantage of this new simplified feature, but if you want more information, then you must stick to the traditional Z function (APEX_UTIL.COUNT_CLICK) or use some dirty trick like storing all the information you need in the click category, a practice that i would just recommend as a last resort.

In order to use Z, the new attributes must be left blank and the URL must built as shown in the picture below.

You can see also that i store the session ID in the p_id parameter of Z which is enough for my user tracking purposes.


I omitted parameter p_user because this is a public page and that information is practically useless in this case.

Having logged the session id in the CLICK_ID column allows me to identify very precisely in APEX_WORKSPACE_ACTIVITY_LOG the internet user who clicked on the link and do my own statistics on who-did-what.

And now for the fancy theory.

If you didn't noticed it before, there is a strange discrepancy between the type of p_id in function Z (a VARCHAR2 parameter) and the numeric type of CLICK_ID in the view APEX_WORKSPACE_CLICK.

What happens if one passes an alphabetic string like ABCD as p_id to Z?
I did some tests and i was ready to get an error message like ORA-01722 while querying the view but nothing of the kind happened, the click was not logged altogether.

Clearly this fact could be exploited by someone to hide the click.
In order to avoid such vulnerability one might want to wrap function Z with a custom procedure where a a checksum or a verification code are automatically created by the system and an attempt to forge a different URL can be detected immediately. Another useful feature that such wrapper function could provide is the obfuscation of the underlying URL.

In conclusion i don't know if parameter p_id was not mapped to any page property in the Edit List Entry page and column CLICK_ID was not included in the standard Apex report on purpose, but as long as you pass numeric values to p_id using function Z, the report will correctly show them in the CLICK_ID column.

Edited September 5,2007:
Check out also this newer blog posting!

See more articles about Oracle Application Express or download tools and utilities.

ORA-01114 and ORA-27063

On an Solaris platform you may see an error message like:

ORA-01114: IO error writing block to file 202 (block # 458633)
ORA-27063: skgfospo: number of bytes read/written is incorrect
SVR4 Error: 28: No space left on device
Additional information: -1
Additional information: 122880

it means that a datafile cannot extend anymore because a disk (or better said, some kind of storage device) is full.

Specifically, the high numbering of the file (in blue), refers to a temporary file.

If the number is in the range between 1 - DB_FILES, where DB_FILES is an initialization parameter whose value can be retrieved either with:

select * from v$parameter
where name = 'db_files';

or in SQL*Plus with

show parameter db_files

it means that we are talking of an ordinary datafile.

In this case DB_FILES was set to 200, which means that 202 refers to a temporary file and tempfiles are numbered starting from DB_FILES + 1.

If the temporary file is set to AUTOEXTEND ON, then the root cause of the problem can be a huge query on a view requiring a big sort operation or the insertion of lots of rows into a temporary table that made the temporary tablespace grow larger than usual until all the space on the device was used up.

See more message translations for ORA-01114, ORA-27063 and search additional resources.

---

ORA-01114: errore I/O scrivendo blocco su file  (blocco n. )
ORA-27063: skgfospo: il numero di byte di read/written non è corretto
ORA-01114: error de E/S al escribir el bloque en el archivo  (bloque número )
ORA-27063: skgfospo: el número de bytes leídos/escritos es incorrecto
ORA-01114: error d'E/S en l'escriptura d'un bloc en el fitxer  (bloc # )
ORA-27063: skgfospo: el número de bytes llegits/escrits és incorrecte
ORA-01114: erreur d'E/S en écriture de blocs dans fichier '' (bloc # )
ORA-27063: skgfospo : le nombre d'octets lus/écrits est incorrect
ORA-01114: EA-Fehler beim Schreiben von Block in die Datei  (Block Nr. )
ORA-27063: skgfospo: Zahl der gelesenen/geschriebenen Bytes falsch
ORA-01114: σφάλμα ΕΕ στο γράψιμο μπλοκ στο αρχείο  (αριθ. μπλοκ )
ORA-27063: skgfospo: ο αριθμός bytes που διαβάσθηκαν/γράφηκαν είναι λανθασμένος
ORA-01114: IO-fejl ved skrivning af blok i filen  (blok # )
ORA-27063: skgfospo: Antal læste/skrevne byte er forkert
ORA-01114: IO-fel vid lagring av block i filen  (blocknr )
ORA-27063: skgfospo: antalet byte som lästs/lagrats är felaktigt
ORA-01114: I/U-feil ved skriving av blokk til filen  (blokk nr )
ORA-27063: skgfospo: feil antall byte lest/skrevet
ORA-01114: IO-virhe tiedoston  lohkon kirjoituksessa (lohkonro )
ORA-27063: skgfospo: kirjoitettujen/luettujen tavujen määrä on virheellinen
ORA-01114: IO hiba a(z)  fájl blokkjának írása közben (blokk: # )
ORA-27063: skgfospo: a kiírt vagy a beolvasott bájtok száma nem egyezik
ORA-01114: eroare de intrare/ieşire la scrierea blocului în fişierul  (bloc nr. )
ORA-27063: skgfospo: numărul de octeţi citiţi/scrişi este incorect
ORA-01114: IO-fout bij schrijven van blok naar bestand  (bloknummer ).
ORA-27063: skgfospo: aantal bytes lezen/schrijven is onjuist.
ORA-01114: erro de IO durante gravação do bloco no arquivo  (bloco # )
ORA-27063: skgfospo: número incorreto de  bytes lidos/gravados
ORA-01114: erro de E/S ao escrever bloco no ficheiro  (nº bloco )
ORA-27063: skgfospo: número de bytes lidos/gravados é incorrecto
ORA-01114: ошибка ввода/вывода при записи блока в файл  (блок # )
ORA-27063: skgfospo: неверное число прочитанных/записанных байт
ORA-01114: chyba vstupu/výstupu při zápisu bloků do souboru  (číslo bloku )
ORA-27063: skgfospo: počet načtených nebo zapsaných bajtů je nesprávný
ORA-01114: IO chyba pri zápise blokov do súboru  (blok # )
ORA-27063: skgfospo: počet načítaných/zapísaných bajtov je nesprávny
ORA-01114: błąd We-Wy przy zapisie bloku do pliku  (blok nr )
ORA-27063: skgfospo: niepoprawna liczba odczytanych/zapisanych bajtów
ORA-01114:  dosyasına blok yazmada GÇ hatası (blok no )
ORA-27063: skgfospo: okunan/yazılan bayt sayısı doğru değil

ORA-32773 and autoextend on/off

On oracle XE (but may be also on other 10g editions i presume), if you execute:

ALTER TABLESPACE demo AUTOEXTEND ON;

or

ALTER TABLESPACE demo AUTOEXTEND OFF;

You can get:

ORA-32773: operation not supported for smallfile tablespace DEMO

However, you can still turn autoextend on or off by issuing a more traditional:

ALTER DATABASE DATAFILE 'G:\demo01.dbf' AUTOEXTEND OFF;

If you create tablespace demo with the BIGFILE option:

CREATE BIGFILE TABLESPACE demo
DATAFILE 'G:\demo01.dbf' SIZE 100M AUTOEXTEND ON;

then you can turn autoextend on or off at will using the alter tablespace command above.

This behavior is consistent with the traditional architecture of Oracle, where a tablespace may have more than one datafile attached and each datafile could have a different autoextend setting. With the "new" bigfile tablespace type instead there is a single datafile, so there is no confusion as to which datafile should be made autoextensible or not.

Indeed, if you want to know if a tablespace can be autoextended, then you'll have to look at the underlying datafiles:

select tablespace_name, file_name, autoextensible
from dba_data_files
where tablespace_name = 'DEMO';

For a comparison between BIGFILE and SMALLFILE (traditional) tablespaces, see the Oracle XE SQL Reference Guide or the Oracle 10GR1 SQL Reference Guide.

See message translations for ORA-32773 and search additional resources.

Tip's Corner: counting how many times a character is in a string

Believe it or not, among the dozens of Oracle's built-in SQL functions, there is none for counting the occurrences of a specific character in a source string.

The lack of this function forced me to come up with a quick and dirty solution:

select
:str as "string",
:chr as "character",
length(:str) - length(translate(:str,chr(0)||:chr,chr(0))) as "count"
from dual;

string	character	count
AB^CDE^FGHI^JK^^LMNOPQ^RST^UVWX^YZ	^	8

Note: this works on the assumption that you are never going to count character zero.

Anyone else with a better/faster idea?