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 (sometimes called service name) in tnsnames.ora, you'll get "ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor" error in SQL*Plus, even though 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.
Oracle Documentation says, "Restriction: You cannot change the values of the INITIAL and MINEXTENTS for an existing rollback segment". But look at this:
SQL> select segment_name, min_extents from dba_rollback_segs; SEGMENT_NAME MIN_EXTENTS ------------------------------ ----------- SYSTEM 2 R01 2 R02 2 R03 2 R04 10 SQL> alter rollback segment r04 storage (minextents 2); Rollback segment altered. SQL> alter rollback segment r04 storage (minextents 20); Rollback segment altered. SQL> alter rollback segment r02 storage (minextents 13); Rollback segment altered. SQL> select segment_name, min_extents from dba_rollback_segs; SEGMENT_NAME MIN_EXTENTS ------------------------------ ----------- SYSTEM 2 R01 2 R02 13 R03 2 R04 20 |
I guess the documentation is not changed even though the Oracle core developers already lifted this limitation. Changing minextents of an existing rollback segment may not be a good idea though.
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 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.
DDL invalidates cursors. But the scope of invalidation differs in 9i and 10g. In 9i, a DDL causes the cursor body (heap 6) to be flushed out leaving cursor head (heap 0) as viewed in v$sql. 10g DDL flushes the entire cursor out. 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; see Note:435458.1).
alter database datafile <file#> resize <filesize> will return Statement processed if file# is between 0 and 2000000 (2 million), inclusive, but the datafile will not be resized. On the other hand, you get ORA-2827 ("Invalid file number") and ORA-2236 ("invalid file name") if file# is greater than 2000000 and less than 0, respectively. (This test is done on Oracle 8.1.6 for Solaris and 8.1.5 for NT). The documented and correct way to resize a datafile is using full-path qualified filename.
filesize in this statement has to be less than or equal to 2147483647. Greater than that, you get ORA-2490 ("missing required file size in RESIZE clause").
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 Windows2000 Professional Advanced Configuration and Implementation, p.157.
If you exec dbms_lock.sleep(<a long time>) in one session, then alter system kill session from another session usually won't kill the session. Although the sleeping session is not using CPU, apparently it's in a "deep" sleep. On the other hand, it's very easy to wake the session up, in effect ending the sleep. At the OS level, you can send the process a SIGALRM signal by kill -14 pid or kill -s ALRM pid in UNIX, where pid is the shadow process id for the sleeping session. However this is not possible in Windows since Windows has no concept of signals (therefore orakill or NT Resource Kit kill does not accept any signal). In Linux, you can easily find that Oracle ignores signals as much as it can but accepts SIGALRM by, e.g., running strace on the server process of the sleeping session (look at the rt_sigprocmask() call). If you're on Solaris, [credit to Casper Dik] you have to run truss in verbose mode on sigprocmask() (truss -v sigprocmask -p pid). Right under sigprocmask, signal mask bit set is shown as 0x00002000. You'll see the 14th bit is set when it's converted to a binary number. Signal 14 is SIGALRM.
Needless to say, if you want to brutally kill the process, kill -9 pid in UNIX or orakill instance_id thread_id in Windows does the trick.
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, alter table <tablename> move tablespace fails with ORA-997 ("illegal use of LONG datatype"). Charles Fisher proposed a script that allegedly works around the problem.
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 Metalink 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.
The moral of the story is that, 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).
Syntax of LOG_ARCHIVE_DEST_n in Oracle documentation, Reference manual, is incorrect when you use LOCATION to specify a filesystem location. 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. The documentation syntax may make you think you need (( and )) instead of quotes; if you did that, you would get ORA-1078 and LRM-116. In summary, the correct syntax is log_archive_dest_1 = 'location=d:\mydir'. (Tested on Oracle 9.0.1 for Windows)
A null event is a mistake by Oracle kernel developers who forgot to update some performance tables or perhaps inadvertently hardcoded 0 into those tables. 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.). 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$ 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.
To my Computer Page