I wrote it already :
Laurent Schneider » Blog Archive » TO_CHAR(…, ‘D’)
I thought the one I found out this night was fun
select to_char(sysdate,'DAY',
'NLS_DATE_LANGUAGE=''numeric date language''')
from dual;
T
-
3
[edit]this feature is not documented, do not use in prod[/edit]
I first thought of adding Klingon. Well, finally I added Romansh, which is the fourth official language in my country.
Ok, here we go :
$ $ORACLE_HOME/nls/lbuilder/lbuilder &
The Oracle Locale Builder tool is started.
File –> New… –> Language
You specify the language, the spelling for January, for Monday, etc… Most fields are mandatory. If you do not know about one field, like EBCDIC, just find one in the “Show existing definition…” Dialog
File –> Save as…
You save your nlt file
File –> Tools –> Generate NLB
Three NLB files are generated.
$ ls -l *.nl*
-rw-r--r-- 1 lsc users 934 2008-02-19 18:17 lx003eb.nlb
-rw-r--r-- 1 lsc users 3843 2008-02-19 18:17 lx003eb.nlt
-rw-r--r-- 1 lsc users 128 2008-02-19 18:17 lx0boot.nlb
-rw-r--r-- 1 lsc users 428 2008-02-19 18:17 lx0boot.nlt
-rw-r--r-- 1 lsc users 22528 2008-02-19 18:17 lx1boot.nlb
Copy all your the NLB files to your ORACLE_HOME/data/nls.
Restart your instance.
Let’s test it 😉
select
to_char(
sysdate,
'fmday dd month yyyy','NLS_DATE_LANGUAGE=romansch'
)
from dual;
TO_CHAR(SYSDATE,'FMDAYDDMONTHYY
-------------------------------
mardis 19 favrer 2008
In Oracle11g the alert log is an XML file. The old style alertSID.log is created out of the log.xml for backward compatibility only. However, some exciting enhancement are not noticable in the old one.
$ tail -3 alert*.log
Mon Feb 04 15:52:38 2008
ALTER SYSTEM SET recyclebin='OFF' SCOPE=SPFILE;
ALTER SYSTEM SET recyclebin='ON' SCOPE=SPFILE;
If I check in the xml file, I get more info about the execution time, which contains milliseconds
$ adrci exec="show alert -term"|tail -4
2008-02-04 15:52:38.366000 +01:00
ALTER SYSTEM SET recyclebin='OFF' SCOPE=SPFILE;
2008-02-04 15:52:42.101000 +01:00
ALTER SYSTEM SET recyclebin='ON' SCOPE=SPFILE;
This timestamp could be really usefull !
There is also a command -tail for adrci, but it outputed the whole log for some unkown reason
using trunc and round I cannot get lower than -4800 nor higher than 10001 😈
SQL> select trunc(date '-4712-1-1','CC') from dual;
TRUNC(DATE'-4712-1-1','CC')
----------------------------------
Thursday 1st January 4800 B.C.
SQL> select round(date '9999-01-01','CC') from dual;
ROUND(DATE'9999-01-01','CC')
----------------------------------
Monday 1st January 10001 A.D.
Is there any rule where you can use scalar subquery?
You can use a scalar subquery expression in most syntax that calls for an expression (expr).
Well, you cannot use it as the second argument of sys_connect_by_path
select sys_connect_by_path(ename,
(select '/' from dual)) from emp
connect by prior empno=mgr;
*
ERROR at line 1:
ORA-30003: illegal parameter in SYS_CONNECT_BY_PATH
function
You cannot use it in the ITERATE or in the RULES clause of model
SQL> select * from dual model
dimension by (0 x) measures (0 y)
rules iterate ( (select 1 from dual) ) (y[0]=0);
*
ERROR at line 1:
ORA-32607: invalid ITERATE value in MODEL clause
SQL> select * from dual model
dimension by (0 x) measures (0 y)
(y[0]=(select 1 from dual));
*
ERROR at line 1:
ORA-32620: illegal subquery within MODEL rules
Also impossible is in the DATAOBJ_TO_PARTITION function that is used in System Partitioning :
SQL> insert into t partition (
dataobj_to_partition("T",
(select :partition_id from dual) ))
values ('x') ;
*
ERROR at line 1:
ORA-14198: rowid column must refer to table
specified in 1st parameter
Another documented limitation is the GROUP BY clause :
SQL> SELECT (SELECT COUNT(*) FROM EMP),
COUNT(*) FROM DEPT ;
*
ERROR at line 1:
ORA-00937: not a single-group group function
SQL> SELECT (SELECT COUNT(*) FROM EMP), COUNT(*)
FROM DEPT GROUP BY (SELECT COUNT(*) FROM EMP);
*
ERROR at line 1:
ORA-22818: subquery expressions not allowed here
SQL> SELECT (SELECT COUNT(*) FROM EMP),
COUNT(*) FROM DEPT GROUP BY ();
(SELECTCOUNT(*)FROMEMP) COUNT(*)
----------------------- ----------
14 4
I made a funny typo today 🙂
SQL> select * from t1;
| X |
|---|
| 0 |
SQL> select 1.x from t1;
| X |
|---|
| 1 |
Should not I get ORA-904 invalid identifier 😕 ?
The 11g OCP certification should be available this year. You can register for the production exam 1Z0-050 New Features on Prometrics and pass the exam on Mon Feb 18th or later, the OCA exams 1Z1-051 SQL Fundamentals and 1Z1-052 Admin I are in beta and the OCP exam 1Z1-053 Admin II is planned for early 2008 (well, the OCA exams are planned for late 2007, so do not rely on this timeframe).
Check the official page :
Oracle Education 11g certification
The 11g OCM is planned for a later date (whatever that means). Well, I am no longer going to wait for the 10g OCM upgrade for 9i OCM… I am not going to do a certification for an older release since 11g exams are available. I will rather do the 11g ocp this year. As I wrote earlier, the 10g OCM was planned for late 2004…
This morning I had to solve an ORA-09925: Unable to create audit trail file and it was not as straightforward as usual…
There is a note 69642.1 on Metalink, [edit]which is now up to date for 10gR2[/edit].
1) AUDIT_FILE_DEST is not writable
$ env
_=/usr/bin/env
ORACLE_SID=FOO
TERM=dtterm
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_3
PWD=/u01/app/oracle/product/10.2.0/db_3
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"
SQL> startup
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 9925
$ grep -i audit_file_dest $ORACLE_HOME/dbs/*$ORACLE_SID.ora
audit_file_dest=/bar
$ ls -lad /bar
/bar not found
$ su -
root's Password:
# mkdir /bar
# exit
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"
SQL> startup
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
$ su -
root's Password:
# chown oracle /bar
# exit
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"
SQL> startup quiet nomount
ORACLE instance started.
SQL> shutdown abort
ORACLE instance shut down.
2) $ORACLE_BASE/admin/$ORACLE_SID/adump exists and is not writable ❗
$ ls -lad $ORACLE_BASE/admin/$ORACLE_SID/adump
drwxr-xr-x 2 root dba .../admin/FOO/adump
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"
SQL*Plus: Release 10.2.0.3.0 -
Production on Mon Dec 17 09:02:29 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus
$ su -
root's Password:
# chown oracle /bar/admin/FOO/adump
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"
SQL*Plus: Release 10.2.0.3.0 -
Production on Mon Dec 17 09:02:48 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> quit
3) $ORACLE_HOME/rdbms/audit is not writable
$ cat $ORACLE_HOME/dbs/init$ORACLE_SID.ora
db_name=FOO
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"
SQL*Plus: Release 10.2.0.3.0 -
Production on Mon Dec 17 08:48:09 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus
$ ls -lad $ORACLE_HOME/rdbms/audit
drwxr-x--- 2 root dba ... $ORACLE_HOME/rdbms/audit
$ cd $ORACLE_HOME; su
root's Password:
# chown oracle ./rdbms/audit
# exit
$ $ORACLE_HOME/bin/sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.3.0 -
Production on Mon Dec 17 08:49:12 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> quit
I read a user question on forums.oracle.com this morning :
As we know NOT EQUAL operations by pass indexes and cause full table scans in queries
I did not know that. Do I need to use hints?
set autotrace traceonly explain
create table t as
select
sign(rownum-1) r, rpad(rownum,4000,'.') t
from dual connect by level<10000/*00*/;
create index i on t(r);
exec dbms_stats.gather_table_stats(user,'T',cascade=>true)
select /*+ INDEX(T,I) */ r,t from t where r!=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3993571787
--------------------------------------------------------------
----------------------
| Id | Operation | Name | Rows | Bytes | C
ost (%CPU)| Time |
--------------------------------------------------------------
----------------------
| 0 | SELECT STATEMENT | | 1 | 4004 |
1979 (2)| 00:00:24 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 4004 |
1979 (2)| 00:00:24 |
|* 2 | INDEX FULL SCAN | I | 1 | |
1979 (2)| 00:00:24 |
--------------------------------------------------------------
----------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("R"<>1)
SQL> select r,t from t where r!=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298
--------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 1 | 4004 | 271K (1)
| 00:54:18 |
|* 1 | TABLE ACCESS FULL| T | 1 | 4004 | 271K (1)
| 00:54:18 |
--------------------------------------------------------------
------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("R"<>1)
Online Patching : you can apply or roll back online patches while the RDBMS instance is running
1) download an interim patch for 11g, f.ex. dummy patch 6198642
2) unzip p6198642_111060_LINUX.zip
3) cd 6198642
4) $ORACLE_HOME/OPatch/opatch apply -silent -connectString LSC08 -runSql
Invoking OPatch 11.1.0.6.0
Oracle Interim Patch Installer version 11.1.0.6.0
Copyright (c) 2007, Oracle Corporation. All rights reserved.
Oracle Home : /opt/oracle/product/11/db_4
Central Inventory : /opt/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.1.0.6.0
OUI version : 11.1.0.6.0
OUI location : /opt/oracle/product/11/db_4/oui
Log file location : /opt/oracle/product/11/db_4/cfgtoollogs/
opatch/opatch2007-12-05_21-23-42PM.log
RollbackSession rolling back interim patch '6198642' from OH
'/opt/oracle/product/11/db_4'
Running prerequisite checks...
OPatch detected non-cluster Oracle Home from the inventory
and will patch the local system only.
Please shutdown Oracle instances running out of this
ORACLE_HOME on the local system.
(Oracle Home = '/opt/oracle/product/11/db_4')
Is the local system ready for patching? [y|n]
Y (auto-answered by -silent)
User Responded with: Y
Backing up files affected by the patch '6198642' for restore.
This might take a while...
Execution of 'sh /opt/oracle/product/11/db_4/.patch_storage/
6198642_May_07_2007_00_50_36/original_patch/custom/
scripts/pre -rollback 6198642 ':
Return Code = 0
Patching component oracle.rdbms, 11.1.0.6.0...
Copying file to "/opt/oracle/product/11/db_4/cpu/CPUDummy2007/
catcpu.sql"
RollbackSession removing interim patch '6198642' from inventory
---------------------------------------------------------------
This is a dummy patch for testing only
---------------------------------------------------------------
Execution of 'sh /opt/oracle/product/11/db_4/.patch_storage/
6198642_May_07_2007_00_50_36/original_patch/custom/scripts/
post -rollback 6198642 ':
Return Code = 0
Running the "apply" sql script "/opt/oracle/product/11/db_4/cpu/
CPUDummy2007/catcpu.sql" with reference to 'patchmd.xml' file
for the patch "6198642"...
The local system has been patched and can be restarted.
OPatch succeeded.
OPatch did run the necessary script (catcpu for Dummy2007) on the various instances (LSC08). It needed only one step and 34 seconds on my notebook. This patch is a dummy patch, let’s wait for CPU January to see if it is online applicable 😉
If I need to generate 3 rows called AAA, BBB, CCC, I could use dual and union all. Another method is to use Extensibility Types
select * from table
(sys.ODCIVarchar2List('AAA','BBB','CCC'));
COLUMN_VALUE
------------
AAA
BBB
CCC
« Ask Tom Live » European Tour 2008
| City | Date | Price | Register | ||||||||||||||||
|
|||||||||||||||||||
Note that if you register after Feb 29th for the Madrid event (which is in January), the price is no longer valid 😈
I am also surprised to see such a price difference for the same course between the different events.
One may pretend bigfile tablespace is bad, because you cannot backup/restore in parallel.
SQL> create bigfile tablespace big datafile size 1g;
Tablespace created.
RMAN> configure device type disk parallelism 4;
using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE
TO COMPRESSED BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE
TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored
RMAN> backup tablespace big section size 100m;
Starting backup at 30.11.2007 07:23:30
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=42 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=41 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=40 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007
name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 1 through 12800
channel ORA_DISK_1: starting piece 1 at 30.11.2007 07:23:37
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00007
name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 12801 through 25600
channel ORA_DISK_2: starting piece 2 at 30.11.2007 07:23:41
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00007
name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 25601 through 38400
channel ORA_DISK_3: starting piece 3 at 30.11.2007 07:23:45
channel ORA_DISK_4: starting compressed full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00007
name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 38401 through 51200
channel ORA_DISK_4: starting piece 4 at 30.11.2007 07:23:48
channel ORA_DISK_1: finished piece 1 at 30.11.2007 07:23:49
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_1_1
tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:12
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007
name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 51201 through 64000
channel ORA_DISK_1: starting piece 5 at 30.11.2007 07:23:53
channel ORA_DISK_2: finished piece 2 at 30.11.2007 07:23:53
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_2_1
tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:12
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00007
name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 64001 through 76800
channel ORA_DISK_2: starting piece 6 at 30.11.2007 07:23:57
channel ORA_DISK_3: finished piece 3 at 30.11.2007 07:23:57
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_3_1
tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:12
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00007
name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 76801 through 89600
channel ORA_DISK_3: starting piece 7 at 30.11.2007 07:24:01
channel ORA_DISK_1: finished piece 5 at 30.11.2007 07:24:01
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_5_1
tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007
name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 89601 through 102400
channel ORA_DISK_1: starting piece 8 at 30.11.2007 07:24:05
channel ORA_DISK_2: finished piece 6 at 30.11.2007 07:24:05
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_6_1
tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00007
name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 102401 through 115200
channel ORA_DISK_2: starting piece 9 at 30.11.2007 07:24:09
channel ORA_DISK_4: finished piece 4 at 30.11.2007 07:24:09
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_4_1
tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:21
channel ORA_DISK_4: starting compressed full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00007
name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 115201 through 128000
channel ORA_DISK_4: starting piece 10 at 30.11.2007 07:24:13
channel ORA_DISK_1: finished piece 8 at 30.11.2007 07:24:13
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_8_1
tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007
name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 128001 through 131072
channel ORA_DISK_1: starting piece 11 at 30.11.2007 07:24:18
channel ORA_DISK_2: finished piece 9 at 30.11.2007 07:24:18
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_9_1
tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:09
channel ORA_DISK_3: finished piece 7 at 30.11.2007 07:24:18
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_7_1
tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:17
channel ORA_DISK_4: finished piece 10 at 30.11.2007 07:24:18
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_10_1
tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:05
channel ORA_DISK_1: finished piece 11 at 30.11.2007 07:24:19
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_11_1
tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 30.11.2007 07:24:19
This works fine 😉
SECTION SIZE requires 11gR1 or later
In 2006, I blogged about 11g new features part II. At that time 11g was in alpha or early beta stage.
One of the new feature announced at OpenWorld 2006 was multiversioning. Like you have multiple versions of your packages running at the same time.
Unfortunately, the feature has not been implemented in 11g Release 1.
As you can see, the CREATE EDITION command does not work :
SQL> create edition v1;
create edition v1
*
ERROR at line 1:
ORA-00901: invalid CREATE command
SQL> alter session set edition=v1;
ERROR:
ORA-38802: edition does not exist
The error messages ORA-38801 to ORA-38816 refer to EDITIONS.
SQL> select * from dba_editions;
EDITION_NAME PARENT_EDITION_NAME USABLE
------------ ------------------- ------
ORA$BASE YES
SQL> alter session set edition=ora$base;
Session altered.
Apparently the feature is implemented, but not ready to be used. Be patient …
I blogged here that 10.2.0.4 was announced for 2007Q4. Well, I have rechecked metalink and the tentative date for Linux is now 2008Q1 😕
Lutz posted about some 10.2.0.4 Beta tests, I did not even know that some customers got beta version of the metalink patchsets 😈
I played the Chinese Chess Championship in Zurich today. It was a all-play-all tournament with Siegfried Huber (2nd with 1.5/3), Luca Rusconi (2nd with 1.5/3) and Beat Sprenger (4th with 0/3). I finished first with 3 wins out of 3 games.
It was an intense day, thanks to Beat for the organization and to Luca for providing the Trophy.
I read the ultimate excuse database and checked if I can use model to do generate random statements:
with t as (
select '%E and %E are travelling to %L.' fmt
from dual union all
select 'About %N employees live in %L.'
from dual)
select str from t
model
reference
dept on (
select loc,count(*) over () c,rownum r
from dept)
dimension by (r) measures (loc,c)
reference
emp on (
select ename,count(*) over () c, rownum r
from emp)
dimension by (r) measures (ename, c)
partition by (fmt)
dimension by (1 x)
measures (cast(fmt as varchar2(4000)) str)
rules
iterate (1000)
until str[1] not like '%/%%' escape '/'
(str[1]=substr(str[1],1,
decode(instr(str[1],'%'),0,4000,
instr(str[1],'%')-1))||
decode(substr(str[1],nullif(
instr(str[1],'%'),0)+1,1),
'N',to_char(trunc(
dbms_random.value(0,1+emp.c[1]))),
'E',initcap(emp.ename[trunc(
dbms_random.value(1,1+emp.c[1]))]),
'L',initcap(dept.loc[trunc(
dbms_random.value(1,1+dept.c[1]))]))||
substr(str[1],nullif(
instr(str[1],'%'),0)+2))
/
STR
----------------------------------------
James and Ward are travelling to Dallas.
About 1 employees live in Chicago.
/
STR
--------------------------------------------
King and Blake are travelling to New York.
About 3 employees live in Dallas.
Is rownum=1 the first row returned? well, it depends :
select rownum, ename
from emp
order by ename;
ROWNUM ENAME
---------- ----------
11 ADAMS
2 ALLEN
6 BLAKE
7 CLARK
13 FORD
12 JAMES
4 JONES
9 KING
5 MARTIN
14 MILLER
8 SCOTT
1 SMITH
10 TURNER
3 WARD
The ROWNUM is evaluated before the order by
select rownum
from emp
group by rownum
having rownum between 3 and 6;
ROWNUM
----------
6
5
3
4
the rownum is selected before the GROUP BY and before the HAVING.
select
rownum,
sal,
median(sal) over ()
from emp;
ROWNUM SAL MEDIAN(SAL)OVER()
---------- ---------- -----------------
1 800 1550
12 950 1550
11 1100 1550
3 1250 1550
5 1250 1550
14 1300 1550
10 1500 1550
2 1600 1550
7 2450 1550
6 2850 1550
4 2975 1550
8 3000 1550
13 3000 1550
9 5000 1550
The optimiser may chose to resort the result, here as a WINDOW SORT operation for the MEDIAN analytic function
you want to BIT_OR multiple rows. For example you have a table with 3 rows that you want to aggregate with BIT_OR
1010 (10)
1100 (12)
0110 (6)
=========
1110 (14)
Let’s try
with t as (
select 10 n from dual union all
select 12 from dual union all
select 6 from dual)
select
utl_raw.cast_to_binary_integer(
sys.mvaggrawbitor(
utl_raw.cast_from_binary_integer(
n
)
)
) N
from t;
N
---
14
It is that easy !
disclaimer: mvaggrawbitor is not documented
I have enjoyed speaking at the unconference very much. I chose not to use anything else than a SQL file to demonstrate the capabilities.
Thanks to the audience for coming. Here is the SQL file I used for the demo
SQL Model – Oracle Wiki
My unconference is Thursday at 2pm
I just arrived at San Francisco and meet my colleague Daniel and Lutz Hartmann. Thanks Dani for driving us to SF and thanks Lutz for the Indian food in the restaurant where we ended last year during Blogger 2006 Meetup 🙂 . By the way do not miss 2007 Meetup organized by Mark Rittman : oow 2007 blogger meetup
I took the cable car from Lutz hotel to the Fishermann’s Wharf. Here it is raining, but well, in Zurich it was snowing and the wind was so cold that they needed to unfreeze the aisles of the plane before take off!
In less than 8 hours the 11g exam cram is starting at Moscone South 104 ! My notebook clock is still set to Sun Nov 11, 10:10 am, I will try to sleep until 7am…
I exported to CSV, formated and printed my schedule
I will have to add the Unconferences too.
Saturday I have a long long day to flight to San Francisco 😎
On Sunday you can assist to the 11g exam cram from 9am to 5pm. It may be too late to register, I tried anyway :
11g db Exam Cram
Then you may want to get your OCP credentials for a discount price.
on-site testing
The Oracle Database 11g New Features for Administrators exam will be available in beta version during the conference. Beta exams are not available at Oracle OpenWorld 2007.
If I read this correctly, it means 11g New Features is available during the conference but not at the conference 😯
Dear Friends,
Planning to appear for Oracle DB 10g: Administration I exam, Would be very grateful if you send across braindumps/questions/tips related to that.
Pl mail to this id : ***@yahoo.com
Many Thanks,
***
When I appeared to my first multiple choice question exam by Prometrics back in 1997, there were no brain dumps, no free question except the 10 sample questions on the vendor site, and no book. I missed Sun JDK 1.0.2 exam because of the many questions related to java.media.Player and other classes totally unknown to me.
In 2000, I did OCP 8.0 and 8i. That was 5 exams. One of the exam was called Oracle 8.0 Network. I had and I still have never used connection pooling, Oracle Names, Oracle Connection Manager.
So I bought a book to prepare for the certification :
Guess what, I mastered that exam. Those books are fairly useless, you do not really learn something, but you learn questions by heart and passing an exam on a topic you do not know is dramatically easy.
On the Internet, there are hundreds of vendors that sell exam questions. Oracle itself has a kind of partnership with SelfTestSoftware. SelfTestSoftware is an official vendor of test simulators. Well, why paying 99$ for a test? A new trend is to post on the official Oracle Technical Network Certification Forum your email address and beg for free questions. I have alerted [email protected] about this behaviour.
Is OCM better? Well, at least you must know how to use your fingers to type quickly. I received not less than 50 emails from people asking for the content of the exam.
I passed the OCM in early 2004. Later in 2004, former Oracle Certification Principal Jim DiIanni revealed the content of the Practicum in Oracle Magazine 😯
Recently I did the Oracle Certification Experts beta exams for RAC and SQL. Those exams are also multiple choice tests and could also be a market for illegal certification sites.
If you want to pass the 11g New Features exam, you could also attend to a new feature course.
In OpenWorld next week there are also some mini-lessons from Oracle University like :
Oracle Database 11g: Can I Do That? Introducing 11g SQL and PL/SQL Enhancements
Oracle Ace Lutz Hartmann is giving 11g courses in Switzerland : http://sysdba.ch/aktuell.php
I am in the processing in adding logfiles to a 10gR2 database.
SQL> alter database add logfile group 10 size 1e7;
Database altered.
$ ls -l
-rw-r----- 1 oracle 10000896 Nov 1 15:00
o1_mf_10_3lmq05ld_.log
The file size is 10,000,896 bytes.
What about this :
SQL> alter database drop logfile group 10;
Database altered.
SQL> alter database add logfile size 1e;
alter database add logfile size 1e
*
ERROR at line 1:
ORA-00741: logfile size of (2251799813685248) blocks
exceeds maximum logfile size
No way! Oracle does not want me to add a logfile of 1 Exabyte !
Remember the logfile blocks are OS blocks of 512 bytes. Not Oracle blocks.
Ok, let’s try something else
SQL> alter database add logfile size 1p;
alter database add logfile size 1p
*
ERROR at line 1:
ORA-00741: logfile size of (2199023255552) blocks
exceeds maximum logfile size
No, one Petabyte is not a realistic size for a log file.
I have one more try, but unfortunately it works 👿
SQL> alter database add logfile size 1t;
...
It just takes ages…
$ ls -l
-rw-r----- 1 oracle dba 1099511628288 Nov 1 14:49
o1_mf_5_3lmpb6w6_.log
$ du -g o1_mf_5_3lmpb6w6_.log
6.09 o1_mf_5_3lmpb6w6_.log
$ df -gt .
Filesystem GB blocks Used Free %Used Mounted on
/dev/u02_lv 140.00 19.32 120.68 14% /u02
The ls shows the file size has been set to 1T and 6 Gigabytes have been allocated yet. Since I do not want to fill my filesystem, I just shutdown-abort my instance and remove that file…
I posted about Unexpected results in June 2005. Here are more results from NOT IN and NULL
select * from dual
WHERE (1) NOT IN (SELECT NULL FROM DUAL);
no rows selected
select * from dual
WHERE (1,1) NOT IN (SELECT NULL,1 FROM DUAL);
no rows selected
However, and this surprised me,
select * from dual
WHERE (1,1) NOT IN (SELECT NULL,2 FROM DUAL);
D
-
X
🙄
Probably the expression
where (a,b) not in (select c,d from t)
is translated into
where (a!=c or b!=d) — first row of t
and (a!=c or b!=d) — second row of t
— and …
What is doing the MIN(DISTINCT X) call? Basically, every distinct value of X is passed to the MIN function. Well, it is probably of very little interest as the MIN function is very fast and processing less rows than MIN(X) should not boost the performance because of the overhead of sorting distinct values.
However, if you write your own aggregate, distinct may be interesting!
create type myudag_type as object
(
myudag INTEGER,
static function ODCIAggregateInitialize(
sctx IN OUT myudag_type)
return number,
member function ODCIAggregateIterate(
self IN OUT myudag_type,
value IN INTEGER)
return number,
member function ODCIAggregateTerminate(
self IN myudag_type,
returnValue OUT INTEGER,
flags IN number)
return number,
member function ODCIAggregateMerge(
self IN OUT myudag_type,
ctx2 IN myudag_type)
return number
);
/
create or replace type body myudag_type is
static function ODCIAggregateInitialize(
sctx IN OUT myudag_type)
return number is
begin
sctx := myudag_type(0);
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(
self IN OUT myudag_type,
value IN INTEGER)
return number is
begin
-- doing nothing will cost you a lot !!!
for i in 1..1000000 loop null; end loop;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(
self IN myudag_type,
returnValue OUT INTEGER,
flags IN number) return number is
begin
returnValue := self.myudag;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(
self IN OUT myudag_type,
ctx2 IN myudag_type)
return number is
begin
return ODCIConst.Success;
end;
end;
/
CREATE FUNCTION myudag (
input INTEGER)
RETURN INTEGER
AGGREGATE USING myudag_type;
/
SQL> select myudag(deptno) from emp;
MYUDAG(DEPTNO)
--------------
0
Elapsed: 00:00:00.57
SQL> select myudag(distinct deptno) from emp;
MYUDAG(DISTINCTDEPTNO)
----------------------
0
Elapsed: 00:00:00.13
10.2.0.4 should be available this year on Linux x86. Check Metalink Certification for different plateforms.
Certify – Additional Info Oracle Database – Enterprise Edition Version 10gR2 On Linux (x86)
Existing patch sets:
10.2.0.2
10.2.0.3
10.2.0.4 Q4CY2007
Yesterday I installed ContentDB. The installation is fairly straightforward.
Download and install Oracle Identity Management
This will create a 10.1.0.5 database. The default parameters are too low for contentDB.
alter system set
processes=250
scope=spfile
sga_max_size=629145600
scope=spfile
shared_pool_size=184549376
scope=spfile
java_pool_size=125829120
scope=spfile
db_cache_size=150994944
scope=spfile
db_file_multiblock_read_count=32
scope=spfile
db_create_file_dest='/u02/oradata'
scope=spfile
job_queue_processes=10
scope=spfile
session_max_open_files=50
scope=spfile
open_cursors=400
scope=spfile
star_transformation_enabled=true
scope=spfile
pga_aggregate_target=203423744
scope=spfile;
It is recommended to stop the application server infrastructure before restarting the database to make the parameters above effective.
$ $ORACLE_HOME/bin/emctl stop iasconsole
$ $ORACLE_HOME/opmn/bin/opmnctl stopall
$ sqlplus / as sysdba
SQL> shutdown immediate
SQL> startup
$ $ORACLE_HOME/opmn/bin/opmnctl startall
$ $ORACLE_HOME/bin/emctl start iasconsole
It is now possible to install ContentDB in this database. Of course the ContentDB could be installed in a separate database, it does not have to be the same as the infrastructure database.
Now, download and install Oracle ContentDB
That’s all. There now two application server instances, one for the infrastructure and one for the content database.
To launch the Content Database web interface, just go to the http server of the ContentDB installation, something like http://server:7779.
$ /app/oracle/product/10.1.2/cdb_1/bin/opmnctl status -l
Processes in Instance: CONTENTDB01.srv01
-------------------+----------+------------------------
ias-component | status | ports
-------------------+----------+------------------------
DSA | Down | N/A
HTTP_Server | Alive | http1:7779,http2:7202
LogLoader | Down | N/A
dcm-daemon | Alive | N/A
OC4J | Alive | ajp:12503,rmi:12403,...
WebCache | Alive | http:7778,invalidati...
WebCache | Alive | administration:9400
Content | Alive | node_dms_http:53900,...
Content | Alive | node_manager_locator...
Login with user ORCLADMIN and the password you specified for IAS_ADMIN.
ContentDB interface let you upload and download files. You can use it to keep your documentation in a single location. It has versioning capabilities too.
Oracle Database Download