Oracle Idiosyncrasies
This article lists some strange behaviors I have found about Oracle. Most of them are just slightly annoying, or even amusing. But if you're not careful, some of these annoying things may take you hours to find out. Entries are added in chronological order.
SQL> create table t (a number);
Table created.
SQL> insert into t values (123);
1 row created.
SQL> /*select * from t; */
1 row created.
SQL> select * from t;
A
---------
123
123
|
Additionally, # at the beginning of lines silently interprets the current line as SQL and executes it. See Steve Adams' A comment about comments.
If you have a leading space in front of the connection string (now officially called connect identifier) in tnsnames.ora, you'll get "ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor" error in SQL*Plus. (In older versions of Oracle, tnsping still works.) E.g, this won't work:
# D:\ORACLE\ORA81\NETWORK\ADMIN\TNSNAMES.ORA Configuration File:d:\Oracle\Ora81\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle Net8 Assistant
myconnection =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TOY)
)
)
|
In fact, indentation of the lines in the above entry also matters. Try removing all leading spaces on each line and you'll see. Interestingly, if you don't have a working example of a TNS entry at hand, you can join all items on one line
myconnection = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = TOY))) |
Similarly, if you have a leading space in front of LISTENER = (assuming your listener is named listener), you'll get all kinds of TNS errors when you start or reload the listener. If the leading space is in front of SID_LIST_LISTENER =, you'll get TNS-1150 and NL-303 instead.
We know a DDL (data definition language) SQL statement implicitly commits your transaction, even if this DDL fails (implicit means the user doesn't issue commit and 'user commits' statistic does not increment). However, a DDL statement is only one that modifies data dictionary, not including all alter statements. For instance, after an alter system (except alter system set encryption) or alter session statement, regardless whether it fails or succeeds, you can still rollback your transaction. But all other alter statements are DDLs, as are grant, comment, analyze. Analyze, however, is a special DDL that differs from all others; after analyze, xxx_objects.last_ddl_time won't be updated. Note that explain plan is a DML; in fact, I usually rollback after explain plan and @?/rdbms/admin/utlxpls (or select * from table(dbms_xplan.display)) to save space in plan_table. (Update: No need now, since plan_table is a synonym pointing to a global temporary table private to your session.)
DDL invalidates cursors. But the scope of invalidation differs in different versions. In 9i, a DDL causes the cursor body (heap 6) to be purged leaving cursor head (heap 0) as viewed in v$sql. 10g DDL purges the entire cursor out. In 11g and up, nothing is purged; the cursor is just invalidated. Some PL/SQL procedures contain DDL internally, such as dbms_job.submit. Dbms_stats.gather_xxx_stats is too, because it commits the transaction. But these gather_xxx_stats procedures offer you the option no_invalidate to not invalidate cursor bodies. In 10g however, this option defaults to value AUTO_INVALIDATE and Oracle makes the decision based on recent statistics and maybe other factors, but usually it's false; see Note:435458.1;
In 8.1.6 and above, you can execute sys.dbms_space_admin.tablespace_migrate_to_local to migrate to a locally managed tablespace. But it may still show USER in dba_tablespaces.allocation_type. In addition, if it is a READ ONLY tablespace, you have to alter it READ WRITE first, migrate and then alter it READ ONLY; you can't migrate it when it's READ ONLY.
The EDIT window of SQL*Plus, i.e. the window you get when you type EDIT (or ED for short) at SQL> prompt, has two anomalies. One is that you can't have more than one SQL statement in it. For instance,
select * from dual; select * from dual;or
select * from dual / select * from dual /If you run the SQLs in this buffer, you get ORA-911 ("invalid character" pointing at the first semicolon) and ORA-933 ("SQL command not properly ended" pointing at the first slash), respectively. The other anomaly is that you can't use bind variables. For instance, these are legitimate commands in SQL*Plus
var mybind number; exec :mybind := 1; insert into mytable values (:mybind);But if you put them in EDIT buffer and run them, you'll get SP2-552 ("Bind variable not declared").
Officially, a database datafile can only be accessed by exactly the same operating system and database version. This is why you can't copy or ftp datafiles from one OS to another to startup the database instance there; export / import has to be done. But in the Windows world, this statement should be qualified. My test shows that there's no problem if you install Oracle and create a database in Windows NT and access it from Windows 2000 (tested on a dual boot computer with NTFS file system). You can even use the software tree built in the first OS (Windows NT in this case). The trick is that you need to create a service using oradim in the second OS and possibly add some registry keys manually. I'm almost certain that it doesn't matter whether Windows 2000 or NT is my first OS.
Apparently these two OSes and their NTFS's are not that different. For example, both support sparse files as in the case of TEMPFILEs, as long as NT has Service Pack 4 applied. But experts still caution about sharing NTFS between NT and Windows2000. See "Planning A Dual-Boot Configuration Between NT and Windows 2000" in M.S.Nielsen's Windows 2000 Professional Advanced Configuration and Implementation, p.157.
In most cases SCN is called system change number. But sometimes it's called system commit number by Oracle, hopefully unintentionally. Oracle error ORA-8209 has the "Cause: The System Commit Number has not yet been initialized". The dynamic performance view v$type_size shows type KSCN, for which the description is "SYSTEM COMMIT NUMBER". In both sql.bsq and migrate.bsq (at least for 8.1.5), you'll see the comment "global system commit number". And in multiple HTML files of the documentation, such as "Overview of Locking Mechanisms", "Appendix B Enqueue and Lock Names", description of v$lock, "Using Multimaster Replication" and so on, you see the phrase "system commit number".
Steve Adams says "SCN means 'System Change Number' not 'System Commit Number'...However, because the SCN is always incremented at commits and seldom otherwise, it is OK to use the two terms interchangeably." Jonathan Lewis in his Practical Oracle 8i calls it system commit number but he noticed sometimes SCN increments without a commit, i.e., skips some numbers. So he has additional comments on this name. Rama Velpuri et al. in their Oracle8 Backup & Recovery Handbook calls it system change number and says it may increment internally, "for example, [during] block cleanout".p.233 Due to this internal or recursive operation that increments SCN without a transaction commit, it's more appropriate to call SCN system change number than system commit number.
Jeff Markham emailed saying "there is only one System Commit Number, while there could be many System Change Numbers", based on an Oracle Internals instructor's distinction between these two different concepts. Concept 1: "The System Commit Number represents the committed state of the database. For example, the database is always rolled back to a System Commit Number." Concept 2: "System Change Numbers are for in-flight changes to data blocks. The System Change Number is only used for a specific data block and is combined with the sequence# (wrap) to determine the version of the block."
The definition of GV$ROLLSTAT as shown in v$fixed_view_definition clearly indicates a query of x$kturd excluding offline rollback segments, even though the decode function in the select list that returns the status still includes "OFFLINE" as a case. Thus, v$rollstat, derived from gv$rollstat, never shows offline rollback segments, which nevertheless are viewable in dba_rollback_segs.
Many people wonder why v$sgastat has two lines that say "db_block_buffers". My understanding is that the one with "shared pool" in the pool column is actually the total size of the block buffer headers in the shared pool that correspond to the buffers in buffer cache. The one without anything in the pool column is the real db_block_buffers expressed in byte. It's said that Oracle 7 does not have the pool column in v$sgastat; then I don't know how to differentiate except maybe by looking at which number is larger.
Now the fun part.
SQL> select a.bytes/b.bytes
2 from v$sgastat a, v$sgastat b
3 where a.name = 'db_block_buffers' and a.pool is null
4 and b.name = 'db_block_buffers' and b.pool = 'shared pool';
A.BYTES/B.BYTES
---------------
60.235294
SQL> select value/136 from v$parameter where name = 'db_block_size';
VALUE/136
---------
60.235294
|
Apparently this indicates that there's a relationship between Oracle block size and how large a portion of the buffer cache each buffer header corresponds to. I can't explain what's special about the magic number 136. This magic relationship exists in at least 8.1.6 on 32-bit Solaris and 8.1.5 on NT. In 8.1.7 on Sparc Solaris using 8k db_block_size, it's still 136; in 8.1.7 on Linux using 4k db_block_size, this number changes to 156 (still an integer!). This relationship normally doesn't exist in 9i and up, since db_block_buffers is deprecated in favor of db_cache_size and v$sgastat no longer has db_block_buffers.
select dump(sysdate) from dual is strange in several ways. A date value seems to take 8 bytes (Len=8 returned) disk storage while we all know it should only take 7 bytes (based on dump(a_date_column_in_a_table) and a data block dump). Secondly, the last byte is always 0. Lastly, the first two numbers are different from those dumped from a date typed column in a real table. See Metalink Note 69028.1 for an explanation.
This idiosyncrasy is worth more than 1000 words so it's a separate note.
Suppose you installed Oracle TNS listener on your local computer (in fact, I believe just the lsnrctl program and the files it needs would be sufficient). If you can tnsping remoteservice, you can also lsnrctl cmd remoteservice where cmd is status or stop! But start won't work. Here's a screen shot from my PC on which I installed Oracle server
C:\>lsnrctl stop tiny LSNRCTL for 32-bit Windows: Version 8.1.7.0.0 - Production on 18-SEP-2001 22:10: 38 (c) Copyright 1998 Oracle Corporation. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=148.89.148.180)(PORT=152 1))(CONNECT_DATA=(SID=tiny))) The command completed successfully |
... 18-SEP-2001 22:05:59 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=yong))(COMMAND= stop)(ARGUMENTS=64)(SERVICE=tiny)(VERSION=135294976)) * stop * 0 No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=plato)(PORT=15 21))) No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC))) |
As I said, once you stop the remote listener, you can't start it; you have to start it locally on that server.
This problem can only be fixed by setting password on the listener so that commands stop, save_config and reload won't work without the password. But if you do this, be aware that by default the password is logged in listener log in clear text! So you may want to make the log file not world readable.
10g finally fixed the problem. If you use one 10g lsnrctl to stop a 10g listener on another host, you'll get the error new to 10g TNS-1189: The listener could not authenticate the user. The Cause of the error description says "The user is attempting to administer the listener from a remote node". (If you use 9i lsnrctl to remotely stop 10g listener, you'll get TNS-12618: versions are incompatible.)
If a table has a column of LONG data type, flashback table <tablename> to {scn|timestamp} fails with ORA-997 ("illegal use of LONG datatype") as well as ORA-604 ("error occurred at recursive SQL level 1"). (But flashback table <tablename> to before drop works because it doesn't work on the individual column.)
A locally managed tablespace (LMT) created by create tablespace extent management local with or without uniform size is a real LMT in every sense. If you "create" one by migrating a dictionary-managed tablespace (DMT) using dbms_space_admin.tablespace_migrate_to_local, it's still very much like a DMT: dba_tablespaces.allocation_type is 'USER'; you can still run alter tablespace default storage (<storage clause>). Of course you cannot migrate a DMT to a LMT with uniform extent size.
From time to time you see messages on public forums or Oracle Support that complain about problems with alter database close (or close normal) as if it were a legitimate SQL command. My test on Oracle 8.1.7.0 and 9.0.1.1.1 indicate that this command completes "correctly" with feedback "Database altered" and apparently leaves the database in mounted stage (v$database.open_mode = 'MOUNTED'). But after that you can't alter database open, which would return ORA-1531 ("a database already open by the instance"). So you have to shutdown which returns ORA-1109 ("database not open"). After that, startup works fine and everything is back to normal.
So just because you see ALTER DATABASE CLOSE NORMAL in alert.log doesn't mean it's a SQL command you can use. In fact there's never been this command in Oracle documentation SQL Reference (checked in SQL Reference for Oracle7 and later versions). The only place in documentation as well as the message file oraus.msg you can find ALTER DATABASE CLOSE is ORA-1093 and ORA-1094. I think having these two errors is misleading. Other things Oracle can do internally but we cannot are ALTER DATABASE DISMOUNT, SELECT PIECE FROM SYS.IDL_SB4$ (seen in v$sql or SQL trace).
To correctly specify the location, make sure you have no space in the double or single quoted string. Otherwise, you'll get ORA-16024 on database startup. Also, don't use parentheses; if you did that, you would get ORA-1078 and LRM-116.
A null event is most likely due to a bug. You see it in v$session_wait and v$system_event. In the first release of version 9.2, you may see quite a few in v$session_wait that should be displayed as some meaningful events.
SQL> select sid,p1,p2,p3 from v$session_wait where event = 'null event';
SID P1 P2 P3
---------- ---------- ---------- ----------
52 21 27825 5
54 1952673792 1 0
SQL> select sid,p1,p2,p3 from v$session_wait where event = 'null event';
SID P1 P2 P3
---------- ---------- ---------- ----------
52 8 39231 1
54 1952673792 1 0
|
There're many places in Oracle where the word "sort" is used. But sometimes it doesn't refer to sorting of data. For instance, a sort segment should really be called a temporary segment as in other places because intermediate hashing result is also stored in it (v$sort_segment is also better called v$temporary_segment; BTW, v$sort_segment doesn't list a temporary segment created in a permanent tablespace, but that's another issue). People just often forget other uses of a temporary tablespace (an article I read incorrectly says if you see direct path read/write waits, it must be due to sorting).
Even more surprising is v$sort_usage. Many people believe all sessions recorded in this view are doing sorting at the moment. In fact, only if the segtype column is 'SORT' is the session sorting. If it's 'HASH', 'DATA' or 'INDEX', it's hashing, using global temporary table or temporary table index, respectively. Unfortunately, the segtype column is not listed in 8i Reference manual and is listed but without description in 9.0 documentation. (Postscript: Beginning with 9.2, v$sort_usage is not in documentation. A new "view" v$tempseg_usage is listed. But both can be used since they both are public synonyms for sys.v_$sort_usage. In spite of the name change from "sort" to "tempseg" for this view, the name v$sort_segment remains)
This idiosyncrasy is worth more than 1000 words so it's a separate note.
When you use this SQL select 'alter index ' || index_name || ' monitoring usage;' from user_indexes to generate SQLs to monitor index usage, add where index_type not like 'IOT%', because monitoring an index organized table throws ORA-25176 (storage specification not permitted for primary key), a misleading error.
An object in an EXECUTE IMMEDIATE SQL string called by a stored program unit does not show dependency between this object and the stored program unit (package, procedure, function, trigger, java source, etc.), because the embedded SQL is considered "dynamic". For instance, even if you know all your code is inside Oracle, you shouldn't drop all sequences shown by select sequence_name from dba_sequences minus select referenced_name from dba_dependencies where referenced_type = 'SEQUENCE', because a sequence could be used in a string in EXECUTE IMMEDIATE; before you drop them, check xxx_SOURCE. The code below shows that procedure p does not show dependency on table mytable.
SQL> create table mytable (a number);
Table created.
SQL> create procedure p as begin execute immediate('insert into mytable values (123)'); end;
2 /
Procedure created.
SQL> exec p
PL/SQL procedure successfully completed.
SQL> select a from mytable;
A
----------
123
SQL> select referenced_owner || '.' || referenced_name from user_dependencies where name = 'P';
REFERENCED_OWNER||'.'||REFERENCED_NAME
-----------------------------------------------------------------------------------------------
SYS.STANDARD
SYS.SYS_STUB_FOR_PURITY_ANALYSIS
SQL> create procedure pp as begin insert into mytable values (456); end;
2 /
Procedure created.
SQL> select referenced_owner || '.' || referenced_name from user_dependencies where name = 'PP';
REFERENCED_OWNER||'.'||REFERENCED_NAME
-----------------------------------------------------------------------------------------------
SYS.SYS_STUB_FOR_PURITY_ANALYSIS
YONG.MYTABLE
|
Beginning with Oracle9.2, Oracle recommends CREATEing DIRECTORY objects instead of using the deprecated UTL_FILE_DIR for PL/SQL to communicate with the filesystem. Because the first argument to FOPEN function changes from physical path to directory object, your existing code that has utl_file.fopen('/tmp',... is supposed to change to utl_file.fopen('MYDIROBJ',..., where MYDIROBJ is created by create directory mydirobj as '/tmp'. That code change could be a big project. You can avoid it by continuing to use UTL_FILE_DIR until it becomes unsupported, or by creating a directory object with the same name as the physical path with double quotes.
SQL> create directory "/tmp" as '/tmp';
Directory created.
SQL> grant read on directory "/tmp" to public;
Grant succeeded.
SQL> create or replace function f1 return number is
2 v_output_file1 utl_file.file_type;
3 begin
4 v_output_file1 := utl_file.fopen('/tmp', 'NEW.txt', 'a');
5 utl_file .put_line(v_output_file1, 'NATURE and Beauty');
6 utl_file.fclose_all;
7 return 1;
8 end;
9 /
Function created.
SQL> var x number
SQL> exec :x := f1
PL/SQL procedure successfully completed.
|
The code above is tested on Oracle9.2.0.4 on Solaris 2.8. UTL_FILE_DIR does not already have /tmp. The same trick works on Windows; create directory "c:\temp" as 'c:\temp' and subsequently utl_file.fopen('c:\temp',... successfully create the expected file on Oracle9.2.0.1 on XP.
v$temp_space_header is in v$fixed_table. But if you select from v$temp_space_header when the database is only mounted, you get ORA-1219: database not open: queries allowed on fixed tables/views only. The problem is that this view is based on a join between ts$ and a real fixed table. Ts$, a table as part of data dictionary, is not available until the database is open. v$temp_extent_map, v$temp_extent_pool have the same problem. So do v$segment_statistics, v$sql_plan, v$sql_plan_statistics_all and v$streams_capture.
v$temp_space_header has some other strange behaviors before 10g. For instance, select th.tablespace_name, status from v$temp_space_header th, dba_tablespaces t where th.tablespace_name=t.tablespace_name returns no rows in 9i or older versions. You can add /*+ ordered */ hint to make it work or select th.tablespace_name, status from (select tablespace_name from v$temp_space_header) th, dba_tablespaces t where th.table space_name=t.tablespace_name, or equivalently with th as (select tablespace_name from v$temp_space_header) select th.tablespace_name, status from th, dba_tablespaces t where th.tablespace_name=t.tablespace_name . See here for more. The problem does not occur in 10g.
This idiosyncrasy is worth more than 1000 words so it's a separate note.
This idiosyncrasy is worth more than 1000 words so it's a separate note.
Unlike primary, foreign or unique key constraint, a check constraint without a user-provided name (i.e. with system-generated name) can be added more than once without getting a warning. Try alter table yourtable add check (id is not null) multiple times and check user_constraints. In fact, you can even add mutually-exclusive check constraints if you're not careful. Documentation says "Oracle does not verify that conditions of check constraints are not mutually exclusive...Do not assume any particular order of evaluation of the conditions.".
If you really want, you can create two mutually exclusive check constraints novalidate to prevent the column values from being modified or prevent inserts of new rows. But it won't prevent deletes so the table is not completely read only. If you don't mind resorting to such kludge to make a table read only, why not create an intentionally invalid trigger, such as create trigger trigger_name before insert or delete or update on table_name begin cannotcompile; end;? Any DML now will throw ORA-4098. (What if the table already has a trigger? Create your trigger beginning with "zzz" so it fires first. See TriggerFiringOrder.txt.) In 11g, you finally can alter table read only.
To my Computer Page