I have been shocked by Paul Moen article on MONTHS_BETWEEN.
Here is my own case :
SQL> select months_between( date '2000-03-01', date '2000-02-28') * 31 from dual;
4
Incredible! I have always been very careful with months_between because of the documented end_of_month behaviour (there is one month between 30-APR and 31-MAY), but I did not know the fractional part of add_months was based on a 31-day month 👿
update: but it is documented
I want to monitor the output of one script in OEM.
Let’s say my script is that easy :
#!/bin/ksh
echo $RANDOM
So I go to
That’s all, your generic service is ready. Wait a bit, you will see this in your targets list. You can then click on perform in your “random” homepage and chose “custom metric 1” to have a graph of your values ($RANDOM).
Happy Holidays !
For those who like free games about skiing with really good graphics, check this :
Ski Challenge 2007
go to http://www.tsr.ch/tsr/index.html?siteSect=860020
click Télécharger le jeu (about 41Mb)
Is -0 a meaningful output ?
SQL> set numf 9
SQL> select -.1 from dual;
-.1
---
-0
not really 😡
I am in the process in installing Oracle 10gR2 patch 2 with response file. There is no plenty of disk available, but installing a patch does not require as much space as specified by the runInstaller.
when I start in silent mode, I get :
$ ./runInstaller -responseFile /home/oracle/10202.rsp -silent
Starting Oracle Universal Installer...
-----------------------------------------------------------------------------
You may not have enough space on your drive for a successful install. Do you still want to continue?
Where should I answer yes? There is a secret parameter called -ignoreDiskWarning which does the trick. The parameter is not listed in ./runInstaller -help.
So I installed my 10.2.0.1 + patch 10.2.0.2 and still have 500Mb free. It is ok for Oracle Home. Check your diskspace before using this to avoid filesystem full.
What is the length of an empty string?
According to Oracle documentation,
Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls
And therefore the length of null is null, not 0 (there are no character value with a length of zero :oops:).
However, this is not true for clobs 😈
SQL> create table t(x clob);
Table created.
SQL> insert into t values (empty_clob());
1 row created.
SQL> select x,length(x) from t where x is not null;
X LENGTH(X)
- ----------
0
this is often asked on the forums. I also needed this a while ago while reverse engineering a database model.
Here is my today solution:
1) select * and extract the first column found per table with regexp (10g)
SQL> select table_name,column_name from (select rownum,table_name, regexp_substr(dbms_xmlgen.getxml(‘select * from “‘||table_name||'”‘),'<[^>]*>&string</[^<]*>’) column_name from user_tables) where length(column_name)!=0;
Enter value for string: 20
TABLE_NAME COLUMN_NAME
———- ——————————
DEPT <DEPTNO>20</DEPTNO>
EMP <DEPTNO>20</DEPTNO>
SQL> select table_name,column_name from (select rownum,table_name, regexp_substr(dbms_xmlgen.getxml(‘select * from “‘||table_name||'”‘),'<[^>]*>&string</[^<]*>’) column_name from user_tables) where length(column_name)!=0;
Enter value for string: KING
TABLE_NAME COLUMN_NAME
———- ——————————
EMP <ENAME>KING</ENAME>
BONUS <ENAME>KING</ENAME>
2) add a where condition. much slower of course, because scanning the table more often. somehow nicer output. More sensible to datatypes. Here for number.
SQL> select table_name, column_name from (select rownum,table_name, column_name, dbms_xmlgen.getxml(‘select 1 from “‘||table_name||'” where “‘||column_name||'”=&number’) x from user_tab_columns where data_type=’NUMBER’) where length(x)!=0;
Enter value for number: 3000
TABLE_NAME COLUMN_NAME
———- ——————————
EMP SAL
BONUS COMM
SALGRADE HISAL
While browsing on the support/desupport notice, I find out that exp is going to be desupported in 10gR2.
The original import will still be supported for upgrade from older releases. For upgrade to newer releases (11g,12g), use expdp.
How long is Oracle 9iR2/10g going to be supported? Infinitely!
Metalink updated the upcoming desupport advisory.
For 8iR3, you had something like :
Error Correction Support (ECS): 31-DEC-2004 Extended Support (ES): 31-DEC-2007 Extended Maintenance Support (EMS): 31-DEC-2006
Now for 9iR2, 10gR1, 10gR2, you have this :
Release GA Date Premier Extended Sustaining
Support Support Support
------- -------- -------- -------- ----------
DB 9.2 Jul 2002 Jul 2007 Jul 2010 Indefinite
DB 10.1 Jan 2004 Jan 2009 Jan 2012 Indefinite
DB 10.2 Jul 2005 Jul 2010 Jul 2013 Indefinite
Sustaining support is less than extended support, you do not get upgrade script and compatibility with new products (ex: connection from 9i client to Oracle 14x is not supported). But it let you keep your very old stuff supported by Oracle.
Read more :
Oracle Lifetime Support Policy Datasheet PDF 110K
Oracle Technical Support Policies PDF 258K
How to switch user in Oracle ?
One approach is to change the password :
SQL> connect / as sysdba
Connected.
SQL> select password from dba_users where username='SCOTT';
PASSWORD
------------------------------
F894844C34402B67
SQL> alter user scott identified by abc123;
User altered.
SQL> connect scott/abc123
Connected.
SQL> create table t...
SQL> connect / as sysdba
Connected.
SQL> alter user scott identified by values 'F894844C34402B67';
User altered.
but this is unfair. The user will be prevented from logging for a while, the password expire policy will be reset.
Sometimes you can simply use
SQL> alter session set current_schema=scott;
Session altered.
but this does not really mean a “su”, because you do not change your privileges. You just change the “default” schema.
Another approach is to use the BECOME USER privilege and undocumented upicui OCI function. But this will not work in sqlplus.
Thanks to Jonathan Lewis post today Proxy Users, I could imagine using the proxy functionality in sqlplus to do a su
SQL> create user su identified by secret123;
User created.
SQL> alter user scott grant connect through su;
User altered.
SQL> connect su[scott]/secret123;
Connected.
The connect username[proxy]/password@db is documented in the 10gR2 SQL*Plus reference
I had a question in my mailbox today about using TNS resolution with an unsupported LDAP Server like Sun Java System Directory Server.
Supported in 9i and above are only Microsoft Active Directory and Oracle Internet Directory. In 8i also Novell.
So I have done this once with OVID.
1) download Oracle Virtual Directory
2) Install the OVID on your server (or on a separate server). The latest release is 10.1.4. I used 3.0.3.
$ sh ./ovid303j.bin -i console
Preparing to install...
...
Enter a uniquely descriptive name for the server.
used only by ovid, not related to hostname or whatever
Server Name (DEFAULT: Virtual Directory 1):
Enter the port number on which to provide administrative services.
a console port, used only by ovid manager
Port Number (DEFAULT: 8888):
a credential, only for ovid
Root User DN (DEFAULT: cn=Admin):
Enter a port number to provide LDAP services on (e.g. 389, 636).
this is what will be used by your TNS client, any free port will rule
Port (DEFAULT: 389):
Please enter the base entry of your directory (e.g. o=YourCompany,c=US)
not your Server ldap, chose one db domain, here oracle.world.
Directory Base Suffix[dc=YourCompany,dc=com]: dc=oracle,dc=world
...
3) start the OVID
$ ./vde.sh start
Starting VDE...
4) install the OVID manager (GUI-console) on your PC / workstation. I used Windows. It is available for Windows. If you do not have windows, you can edit the XML files directly (good luck)
5) in the ovid manager, create a new project, add your ovid, and start creating adapters. For each possible DB domain, you must create one adaptor, for example if you try to tnsping LSC01.PROD.DB and you have the description stored as cn=lsc01, cn=oracleContext, cn=PROD.DB, ou=tns, ou=appl, dc=lcsys, dc=ch you will need have
root: dc=PROD,dc=DB
remoteBase: cn=PROD.DB,ou=tns,ou=appl,dc=lcsys,dc=ch
This should work. Not sure if you will like OViD, the interface is pretty awful.
Also it is not too flexible, you need to add an adaptor for each db domain. Still better than providing a TopDomain for each DB Domain with anonymous access to root.
Yes, one more point, tns client access the ldap anonymously, so OViD does help to improve security, you set anonymous access to your OViD, you disable ACI on your OViD, and you use a credential to log in to your LDAP. It is safer, because you use a remoteBase, so anonymous access is granted only to that branch of your directory.
Check the oracle magazine on otn.oracle.com/oramag
In the peer-to-peer, you can read more about 3 Oracle Aces, Steve Karam, Nicolas Gasparotto, Dave Moore.
In the technology columns, three more aces talk about Oracle technlogies. Steven Feuerstein about The Right Place for PL/SQL, Arup Nanda about Recover in a Flash, and Tom Kyte about On Top-n and Pagination Queries. Tom also remind us that group by does not sort. However, if you have one application which used to expect sorted result in 9i and is now produced unexpected result in 10g, you can still read the Note:345048.1 in Metalink to find some workarounds, for example by deactivating hash-group-by with _gby_hash_aggregation_enabled parameter set to false
Today is a good day, I have been accepted to the beta program and will be able to download 11gR1 beta 4 shortly.
There is a Confidential Disclosure Agreement, so do not expect to read more on 11g here before the production release.
My blog articles about 11g in OpenWorld 2006:
11g New Features
11g New Features part II
I successfully installed 10gR2 patchset 2 on my notebook :
SQL> select ACTION,VERSION,COMMENTS from registry$history; ACTION VERSION COMMENTS ------- ---------- ------------------------------ CPU 10.2.0.2.0 CPUOct2006 UPGRADE 10.2.0.3.0 Upgraded from 10.2.0.2.0 SQL> select COMP_NAME,VERSION,STATUS from dba_registry; COMP_NAME VERSION STATUS ----------------------------------- ---------- ----------- Oracle Expression Filter 10.2.0.3.0 VALID Oracle Database Catalog Views 10.2.0.3.0 VALID Oracle Database Packages and Types 10.2.0.3.0 VALID Oracle Database Java Packages 10.2.0.3.0 VALID JServer JAVA Virtual Machine 10.2.0.3.0 VALID Oracle XDK 10.2.0.3.0 VALID
900M in size for a Linux patch is getting quite big. Expect 2G for HPUX/AIX5L, guess this 2G limit, if reached, is going to be a problem for many users !
Hint : use FileZilla to download software
For Windows and Linux x86, Oracle Database 10.2.0.3 is available
Inspired by How Much Is My Blog Worth?
This is all the pain by changing domain, bye bye top position in google, bye bye backlinks…
My old blog is still active, and I hope to get equivalent ranking until end of 2007. I think it was a smart move, I have now more freedom to customize my site with any content, to edit my feed, my pages, my pictures, etc. I wish I had done it before 😮
Download my feed :
I was honored to receive this trophy yesterday :
More results under http://www.sxv.ch/news_2006.php
If I have a limited number of db clients which are authorized to access my listener, I can restrict access to my listener by setting only two parameters in sqlnet.ora.
TCP.VALIDNODE_CHECKING = yes
TCP.INVITED_NODES = (dbclient001,chltlxlsc1)
chltlxlsc1 is my db server, I include it in the list, it is required to start the listener locally.
From dbclient001, I can connect :
$ sqlplus scott/tiger@lsc02
SQL*Plus: Release 10.1.0.4.2 - Production on Wed Nov 22 09:47:43 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production
From dbclient002, I cannot connect
$ sqlplus scott/tiger@lsc02
SQL*Plus: Release 10.1.0.4.2 - Production on Wed Nov 22 09:48:26 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12547: TNS:lost contact
You surely have not missed the “Amazing November 2006 Blog Catastrophe” of Mark Rittman.
I use blogger and by typing “backup blogger” in google, I found [edit]something that has been removed in the meantime (and I do not use blogger anymore) [/edit, janv 2011].
It let you change the formatting of your blog to have all your posts (max 999) and all your comments in one page. If you do not have your own ISP but use blogspot.com, then it will replace your blog by this not-fancy page. But just for the time to save it on your local disk and then you restore your template. Surely less than 5 minutes , unless you surf with a 100 bps or 480 bps underwater accoustic modem like this one.
I have now a dump of my blog. The size of the html file is 440k right now.
First I apologize for the confusion with previous post of mines, where I defined row generators as pivot table.
Here I talking about transposing rows into columns PIVOT and transposing columns into rows UNPIVOT
Ok, back in 2003, I had a data model were all attributes were stored in same table (a miracle of java tools generating data models)
select person.name, property.type, property.value from person, property where person.id=property.person; NAME TYPE VALUE ---- -------- ------ John gender male Mary category junior Mary gender female
for datawarehousing purpose, I had to get the attributes, if set, as a column, so I started with outer joining for each attribute (they were plenty, not just two)
select name, gender.value gender, category.value category from person, property gender, property category where person.id = gender.person(+) and gender.type(+)='gender' and person.id = category.person(+) and category.type(+)='category'; NAME GENDER CATEGO ---- ------ ------ Mary female junior John male
By using the Tom Kyte method described on asktom, I could have used aggregation.
select name, max(decode(type,'gender',value)) gender, max(decode(type,'category',value)) category from person , property where person.id = property.person (+) group by name; NAME GENDER CATEGO ---- ------ ------ John male Mary female junior
To do the opposite, I posted once in a forum
select deptno,
decode(x,1,'DNAME','LOC') type,
decode(x,1,dname,loc) value
from dept,
(select 1 x from dual union all
select 2 from dual);
DEPTNO TYPE VALUE
---------- ----- --------------
10 DNAME ACCOUNTING
20 DNAME RESEARCH
30 DNAME SALES
40 DNAME OPERATIONS
10 LOC NEW YORK
20 LOC DALLAS
30 LOC CHICAGO
40 LOC BOSTON
Well, in the next generation database, this is going to be easier, maybe.
With the introduction of pivot keyword, the following should work
select name, type, value from person , property pivot (max(value) for type in ( 'gender' as gender, 'category' as category)) where person.id = property.person (+);
and with the unpivot keyword
select * from dept unpivot (value for type in ( dname as 'DNAME', loc as 'LOC'));
It would be interesting to compare the execution plans !
I have been looking for this note for ages : Metalink note 295949.1
Now I know how to rename my targets in Grid Control! the trick is to stop the agent
AH/emctl stop agent
to remove the upload and state files/subdirectories
cd AH/sysman/emd
find state upload recv agntstmp.txt lastupld.xml protocol.ini -type f -exec rm {} ';'
rm -r state/*
to remove the agent in the grid
col TARGET_TYPE for a17
col TARGET_NAME for a60
set lin 100 pages 0 emb on newp none head on autop on
select TARGET_TYPE,TARGET_NAME
from MGMT$TARGET
where TARGET_TYPE='oracle_emd'
order by TARGET_NAME;
var target_name varchar2(40)
prompt Enter the target_name to delete
exec :target_name:='&TARGET_NAME';if (:target_name is not null) then mgmt_admin.cleanup_agent(:target_name); end if
to edit the targets.xml
vi AG/sysman/emd/targets.xml ### Get the source of the traditional vi
to rename your targets (listener, host, db), and restart your agent
AH/emctl start agent
hth
check Oracle SQL Developer 1.1 Evaluation Release is Now Available!
PS: there is now a RPM for Linux, which I installed on my SLES 10 notebook with SUN JDK 1.6.
How can i get the difference in days between two dates, d1 and d2 ?
–
for example :
with t as (select
to_date('2000-02-01','YYYY-MM-DD') d1,
to_date('2000-01-23','YYYY-MM-DD') d2
from dual)
select d1,d2,d2-d1
from t;
D1 D2 D2-D1
---------- ---------- ----------
01.02.2000 23.01.2000 -9
How can i get the difference in hours:minutes:seconds between two dates, d1 and d2 ?
Use the interval datatype. To have a format different than the default +000000000 00:00:00.000000, use extract
with t as (select
to_date(‘2000-02-01 11:22:02’,
‘YYYY-MM-DD HH24:MI:SS’) d1,
to_date(‘2000-01-23 12:00:03’,
‘YYYY-MM-DD HH24:MI:SS’) d2
from dual)
select d1,d2,case
when d1d2 then
‘-‘||
(extract(day from ((d1 – d2)
day to second))*24 +
extract(hour from ((d1 – d2)
day to second)))||’:’||
extract(minute from ((d1 – d2)
day to second))||’:’||
extract(second from ((d1 – d2)
day to second))
when d1=d2 then
‘0:0:0’
end “D2-D1”
from t
/
D1 D2 D2-D1
——————- ——————- ———-
01.02.2000 11:22:02 23.01.2000 12:00:03 -215:21:59
Thanks to an anonymous comment in my post yesterday, I can now provide a more flexible version of my alias, which do not require the database to be running nor the sysdba privilege
$ alias oraver
oraver=’echo ‘\”ORACLE_SID VERSION %CPU RSZ VSZ START_TIME’\”;awk -F: ‘\”/^[^ *#]/{print “printf 42%-9s %11s %5s %8s %8s %s\\n42″,$1,”$(ORACLE_HOME=”$2,$2″/bin/sqlplus -v 2>/dev/null|cut -d47 47 -f3) $(ps -eo pcpu,rsz,vsz,start_time,args|sed -n 42s/ [o]ra_pmon_”$1″.*//p42)”}’\” /etc/oratab |sh’
$ oraver
ORACLE_SID VERSION %CPU RSZ VSZ START_TIME LSC01 10.2.0.2.0 0.0 12184 508448 10:09 LSC02 9.2.0.8.0 0.0 8420 303320 10:10 LSC03 9.2.0.8.0
Well, it is based on the sqlplus version, which is not necessarly the same as database version, like 8.1.7.3 does show 8.1.7.0, but in most of the recent versions, it should be ok
ps parameters may not work on all os, you can use -ef if you prefer
I just wrote a new alias to check if the databases are up and running. I added the version and a dash for non-running database. Needed is /etc/oratab + sysdba access to the database.
Here it is :
awk -F: ‘/^[^*# ]/{system(“echo 42select 47+ “$1″ 1147||version from v\\$instance;42|ORACLE_SID=”$1″ ORACLE_HOME=”$2” “$2″/bin/sqlplus -s 42/ as sysdba42 2>/dev/null|grep 42^+42||echo 42- “$1″42”)}’ /etc/oratab
+ LSC01 10.2.0.2.0 + LSC02 9.2.0.8.0 - LSC03
Or, as an alias :
alias oraver=’awk -F: ‘\”/^[^*# ]/{system(“echo 42select 47+ “$1″ 1147||version from v\\$instance;42|ORACLE_SID=”$1″ ORACLE_HOME=”$2” “$2″/bin/sqlplus -s 42/ as sysdba42 2>/dev/null|grep 42^+42||echo 42- “$1″42″)}’\” /etc/oratab’
Werner Puschitz talked yesterday about Automated Oracle Real Application Clusters Deployment: How Dell Does IT.
The result of the automation is, install a 6 nodes cluster in 30 minutes. Which is quite impressive.
Werner divided the automation in three RPM packages.
1) configure the host: set up private and virtual interface (according to a naming convention, for example private is hostname-priv, vip is hostname-vip). Set up the system configuration (shared memory, etc). Set up the SAN, shared devices. Set up ssh. Define a cronjob to check if all nodes are configured and accessible. Write an email to the sysadmin when all nodes are ready
2) install the clusterware. the RPM is build with a response file + the oracle software. The response file is recorded with ./runInstaller -record.
3) install the database software. the RPM is build with a response file too
Werner said it is rather worth to build those packages for large companies, where you have many RAC installations. In my opinion, even if you have only one or two rac, you can benefit from this. It provides you an easy way to reinstall the system, upgrade it to a new version, create two systems (dev+prod) with the same configuration.
Thanks Werner for this very good one!
In the Metreon Theater, Quest announced toad 9. Steven Feuerstein presented one feature : Unit Testing in TOAD. Go to http://www.toadworld.com, unless you are a vi man
I like this one. It will really ease the migration to Linux. A very good news for me.
Go to oracle.com/linux
I have been following 2 sessions with Juan Loaiza about new features.
A very few of them here :
SQL> create edition v1_0;
SQL> alter session set edition=v1_0;
You can create a versioning of your objects, so you will be able to have many versions of the same package at the same time. It is a bit similar to workspace, where you have many versions of the data at the same time. It is very profitable in release management and availability.
About the cache result hint, you can also use
alter session cache results;, which can be useful for lookup table. As commented on a previous post, you can still use the old alter table cache; or the keep buffer pool. It depends.
Performance enhancement in the RAC interconnect.
Server side connection pooling. An additional layer to the shared server, to enable faster [actually to bypass] session creation.
edit: check Database Resident Connection Pooling
Securefiles. This is a good one. It is a huge improvement to BLOB. Faster, with compression, encryption.
Partition interval. This is a marvelous one ! You can now partition by date, one partition per month for example, with automatic partition creation.
Rman backup can bypass undo. Undo tablespaces are getting huge, but contain lots of useless information. Now rman can bypass those types of tablespace. Great for exporting a tablespace from backup.
Capture/replay workload. Sounds appealing. You can capture the workload in prod and apply it in development.
Many other enhancement.
Juan expect to see a Petabyte single database, with a terabyte of memory and 1000 cpus before the end of the decade
this was a 2006 announcement, not all features announced have been implemented in prod
I tried to watch the keynotes yesterday, but it is just to difficult to sit and not sleep for me… so I have seen only a few minutes of each one.
The PL/SQL 11g session was excellent, I wrote an overview in a separate post.
I started the afternoon with APC session, about the importance of testing and the power of the utPLSQL package. He presented that methodology and also the concept of testing. Fixing a bug at the development stage is fine, fixing it in production is very costly. I resume the method in those steps. First, checkout your package and your testing procedure. Second, modify your package. Never commit before testing. Improve your tests. Finally, commit your changes in both the source and the tests. APC was somehow unlucky with a laggy sql-developer and an unregistered textpad. Thanks Andrew for this presentation.
Than I went to Tom Kyte session about the worst things to do. Instead of telling what to do, Tom ironised on what we Should Always Do. He started with The Experts Are Always Right. Funny presentation, lots of laugh in the audience. I have meet Ken Jacobs during Tom Session. A good moment.
I have been to the exhibition hall, and I talked to the egenera team about RAC on BladeFrame and will go to the session S283094 on Wednesday.