Vit Spinka technical blog

Quite often, a procedure needs to know some information about the session it is running in - at least for logging some information. It seems to be easy to find this information - just look at SYS_CONTEXT documentation, and find the impressive list of attributes of USERENV context. And if something is missing, just grab the SESSIONID, SID or AUDSID and query v$session or other view. Well, this works nicely, as long as you don't do it in a job. Unfortunately, in a job you always get 0. You can try to battle with FG_JOB_ID and BG_JOB_ID, but I think this deserves a more elegant solution - and that's why I always query v$mystat to get the SID of my process. Just fetch the first line: select * from v$session where sid = (select sid from v$mystat where rownum=1) You need a select grant on v_$mystat by SYS, but as this view does not expose anything important, it's not a security issue. The next question that comes is: if I am run by a job (let's talk about dbms_job now), what...

Last week, I found a not-so-nice bug in 10gR2. Just try this short test case, creating a table with two list partitions, default and null: alter session set nls_territory=America; alter session set nls_language=American; select version from product_component_version; drop table th3; prompt Creating TH3... create table TH3 ( DF DATE, DT DATE, N NUMBER ) partition by list (DT) ( partition THTBL_CURRENT values (NULL) tablespace USERS, partition THTBL_OLD values (default) tablespace USERS ) ; prompt Loading TH3... insert into TH3 (DF, DT, N) values (to_date('01-01-2000', 'dd-mm-yyyy'), to_date('10-04-2008', 'dd-mm-yyyy'), 1); insert into TH3 (DF, DT, N) values (to_date('01-01-1000', 'dd-mm-yyyy'), to_date('01-03-3000', 'dd-mm-yyyy'), 2); insert into TH3 (DF, DT, N) values (to_date('10-04-2008', 'dd-mm-yyyy'), to_date('10-04-2008', 'dd-mm-yyyy'), 10); insert into TH3 (DF, DT, N) value...

Just a few issues you should be aware of when trying to install 10gR2 RAC on RHEL 5.1: See Metalink 465001.1 for raw device configuration for RHEL 5. It's not explicitly said, but all disks used must be partitioned! Otherwise, if you don't partition the OCR disk, the root.sh from clusterware installation will fail (fail with “Failed to upgrade Oracle Cluster Registry configuration” error, with “Failed to call clsssinit” in log.) The ASMLib will also refuse to stamp whole disk, a partition is required. For OS configuration, see Metalink 421308.1. However, the sysctl parameters listed there do not exist on RHEL 5.1, you will have to use: kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 fs.file-max = 65536 net.ipv4.ip_local_port_range = 1024 65000 net.core.rmem_default = 262144 net.core.rmem_max = 262144 net.core.wmem_default = 262144 net.core.wmem_max = 262144 net.ipv4.tcp_rmem = 4194304 4194304 4194304 net.ipv4.tcp_wmem = 262144 262144 262144 You will als...

Not long time ago, I came across a usage of dbms_alert to manage running jobs. As the solution implemented must work also for RAC, I wanted to know whether dbms_alert works on RAC across instances. The documentation nor Metalink does not say anything (contrary to dbms_pipe, which does NOT work on RAC). So, if they don't warn, it should work... However, Julian Dyke says, that dbms_alert does not work and is the same as dbms_pipe (sources: http://juliandyke.com/Presentations/Presentations.html#ARoughGuideToRAC , page 17, or Pro Oracle Database 10g RAC on Linux, page 426). You know, never trust anybody, so I conducted a test case (10.2.0.3 on Linux x86_64, VMware ESX server, 2-node RAC): You will need two simultaneous sessions, I mark them with DWH1> and DWH2> here. DWH1> select instance_name from v$instance; INSTANCE_NAME ---------------- DWH1 DWH2> select instance_name from v$instance; INSTANCE_NAME ---------------- DWH2 DWH2> exec dbms_alert.register('TST'); ...

The new Oracle 11g New Features exam is available in beta: http://www.oracle.com/global/cz/education/certification/11g_nfbeta.html , open till December, 15th. The first opportunity to earn the 11g OCP credential... By the way, the full 11g OCP track is announced too, and it looks similiar to the 10g track - 1 exam for OCA, 1 exam and hans-on course for OCP. OCM is promised too, but no date is given.

I've prepared a presentation about 11g new features - it's ment for an internal workshop at my company, but perhaps you will find it interesting, too. So, the direct link is here: 11g New Features presentation .

Everyday, I learn something new. Or maybe I just learn again something I forgot in the meantime... A year ago or so, I was reading throught the problems you must face when crossing the 4GB memory size. One of the question asked was: 32-bit or 64-bit? After reading the things needed to make it work on 32-bit (you must use indirect buffers, for example, thus you loose automatic SGA tuning), the answer was simple - 64-bit. Not long before, I was asked what has to be done to use 16GB SGA on 64-bit (EM64T) Linux. Well, it's much simpler then on the 32-bit, and simple sga_target=16g will basically work. The catch is in the word basically - the database will work, but the Linux kernel (kswapd) will have a hard time managing all the memory. Thus, you need to use huge pages (same was for 32-bit, by the way) to get optimal performance. I'm just getting an impression, that the DBA has to know more and more about things outside the database - in this case, of OS. With ASM, about the NAS/...