to_char(interval)

There is no to_char function available for intervals.

Or at least it does not work as expected

SQL> select to_char(interval '1234' second, 'HH24:MM') from dual;
TO_CHAR(INTERVAL'12
-------------------
+00 00:20:34.000000

I just write my own one, with some new format elements

For interval day to second, I have DDD number of days, HH number of hours (0-24), HHH total number of hours (0-99999999999999), etc

here it is


SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 9 17:15:59 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL>
SQL> create or replace function tochards(f_int interval day to second,f_fmt varchar2) return varchar2 is
2 -- valid formats are DDD, HHH, HH, MMM, MM, SSS, SS, FF
3 ret varchar2(4000);
4 f varchar2(4000);
5 i interval day(9) to second(9);
6 begin
7 if (f_fmt is null or f_int is null) then
8 return null;
9 end if;
10 f := upper(f_fmt);
11 if (translate(f,'XDHMSF,.:;/- ','X') is not null) then
12 raise_application_error(-20001,'Invalid format');
13 end if;
14 if (extract(day from i)<0) then
15 ret:='-';
16 i:=f_int*(-1);
17 else
18 ret:='';
19 i:=f_int;
20 end if;
21 while (f is not null) loop
22 if (f like 'DDD%') then
23 ret:=ret||to_char(extract(day from i),'FM999999999999999999');
24 f:=substr(f,4);
25 elsif (f like 'HHH%') then
26 ret:=ret||to_char(extract(day from i)*24+extract(hour from i),'FM999999999999999999');
27 f:=substr(f,4);
28 elsif (f like 'HH%') then
29 ret:=ret||to_char(extract(hour from i),'FM999999999999999999');
30 f:=substr(f,3);
31 elsif (f like 'MMM%') then
32 ret:=ret||to_char(extract(day from i)*24*60+extract(hour from i)*60+extract(minute from i),'FM999999999999999999');
33 f:=substr(f,4);
34 elsif (f like 'MM%') then
35 ret:=ret||to_char(extract(minute from i),'FM999999999999999999');
36 f:=substr(f,3);
37 elsif (f like 'SSS%') then
38 ret:=ret||to_char(extract(day from i)*24*60*60+extract(hour from i)*60*60+extract(minute from i)*60+trunc(extract(second from i)),'FM999999999999999999');
39 f:=substr(f,4);
40 elsif (f like 'SS%') then
41 ret:=ret||to_char(trunc(extract(second from i)),'FM999999999999999999');
42 f:=substr(f,3);
43 elsif (f like 'FF%') then
44 ret:=ret||to_char(mod(extract(second from i),1),'FM999999999999999999');
45 f:=substr(f,3);
46 elsif (substr(f,1,1) in (' ', '-', ':', ';', ',', '.', '/')) then
47 ret:=ret||substr(f,1,1);
48 f:=substr(f,2);
49 else
50 raise_application_error(-20001,'Invalid format : '||f_fmt);
51 end if;
52 end loop;
53 return ret;
54 end;
55 /

Function created.

SQL>
SQL> sho err
No errors.
SQL>
SQL> create or replace function tocharym(f_int interval year to month,f_fmt varchar2) return varchar2 is
2 -- valid formats are YYY, MMM, MM
3 ret varchar2(4000);
4 f varchar2(4000);
5 i interval year to month;
6 begin
7 if (f_fmt is null or f_int is null) then
8 return null;
9 end if;
10 f := upper(f_fmt);
11 if (translate(f,'XYM,.:;/- ','X') is not null) then
12 raise_application_error(-20001,'Invalid format');
13 end if;
14 if (extract(year from i)<0) then
15 ret:='-';
16 i:=f_int*(-1);
17 else
18 ret:='';
19 i:=f_int;
20 end if;
21 while (f is not null) loop
22 if (f like 'YYY%') then
23 ret:=ret||to_char(extract(year from i),'FM999999999999999999');
24 f:=substr(f,4);
25 elsif (f like 'MMM%') then
26 ret:=ret||to_char(extract(year from i)*12+extract(month from i),'FM999999999999999999');
27 f:=substr(f,4);
28 elsif (f like 'MM%') then
29 ret:=ret||to_char(extract(month from i),'FM999999999999999999');
30 f:=substr(f,3);
31 elsif (substr(f,1,1) in (' ', '-', ':', ';', ',', '.', '/')) then
32 ret:=ret||substr(f,1,1);
33 f:=substr(f,2);
34 else
35 raise_application_error(-20001,'Invalid format : '||f_fmt);
36 end if;
37 end loop;
38 return ret;
39 end;
40 /

Function created.

SQL>
SQL> sho err
No errors.
SQL>
SQL> select tochards(current_timestamp - timestamp '2000-01-01 00:00:00', 'HHH:MM') from dual;

TOCHARDS(CURRENT_TIMESTAMP-TIM
------------------------------
53561:15

SQL> select tochards(current_timestamp - timestamp '2000-01-01 00:00:00', 'DDD HH:MM:SS') from dual;

TOCHARDS(CURRENT_TIMESTAMP-TIM
------------------------------
2231 17:15:59

SQL> select tocharym(interval '25' month, 'YYY:MM') from dual;

TOCHARYM(INTERVAL'25'MONTH,'YY
------------------------------
2:1

SQL> select tocharym(interval '-25' month, 'MMM') from dual;

TOCHARYM(INTERVAL'-25'MONTH,'M
------------------------------
-25

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Application Server 10gR3 on Suse Linux 10

I just download and installed Oracle Application 10g Release 3 on my linux box.

The version released this week is very light! No ldap, no database, well, a light one…


Application Server 10g Release 3 x86: 531 MB
Included:
* Oracle HTTP Server
* Oracle Application Server Containers for J2EE (OC4J)
* Oracle Enterprise Manager 10g Application Server Control
* Oracle Business Rules
* Oracle TopLink

Application Server 10g Release 2 x86: 2 GB
Included:
* Oracle HTTP Server Oracle Application Server Containers for J2EE (OC4J)
* Oracle Application Server Web Cache
* Oracle Application Server Portal
* Oracle Application Server Wireless
* Oracle Sensor Edge Server
* Oracle Enterprise Manager 10g Application Server Control
* Oracle Database Server 10g (10.1.0.4.2)
* Oracle Internet Directory
* Oracle Application Server Single Sign-On
* Oracle Application Server Directory Integration Provisioning
* Oracle Application Server Delegated Administration Services
* Oracle Application Server Certificate Authority
* Oracle Application Server Forms Services
* Oracle Application Server Reports Services
* Oracle Application Server Personalization
* Oracle Business Intelligence Discoverer
* Oracle Security Developer Tools
* Oracle Application Server Guard
* OracleAS Backup and Recovery Tool

I tried to install on my notebook, and, o surprise, it works…
the only thing that was missing and required was public domain korn shell, pdksh, which is easy to find by googling

Recursive PL/SQL

it will be a good week !

I found an elegant way to solve a query with recursive pl/sql.

an user wanted to have DHSGHDADSFDF translated in DHSGAF, that is, duplicated chars removed, order retained.

here is my function :

create or replace function f(v varchar2) return varchar2 is
begin
if (v is null) then return null;
else return substr(v,1,1)||f(replace(substr(v,2),substr(v,1,1));
end if;
end;
/

ref: using recursion with PL/SQL

static expression

Using Static Expressions with Conditional Compilation defines the kind of expression you can use with conditional compilation…

I got one question on forums.oracle.com today, an user wanted to use bitand and could not.

I wrote my answer but, … , the site is down and eventually my answer did not get accepted.

So, to translate bitand(x,y)=z, using static expression, when x(
(
(x=0) or
(y=0) or
(x=1 and (y=2 or y=4 or y=6)) or
(x=2 and (y=1 or y=3 or y=5 or y=7)) or
(x=3 and y=4) or
(x=4 and (y=1 or y=2 or y=3)) or
(x=5 and y=2) or
(x=6 and y=1)
) and z=0
) or (
(
(x=1 and (y=1 or y=3 or y=5 or y=7)) or
(x=3 and (y=1 or y=5)) or
(x=5 and (y=1 or y=3)) or
(x=7 and y=1)
) and z=1
) or (
(
(x=2 and (y=2 or y=3 or y=6 or y=7)) or
(x=3 and (y=2 or y=6)) or
(x=6 and (y=2 or y=3)) or
(x=7 and (y=2))
) and z=2
) or (
(
(x=3 and (y=3 or y=7)) or
(x=7 and y=3)
) and z=3
) or (
(
(x=4 and (y=4 or y=5 or y=6 or y=7)) or
(x=5 and (y=4 or y=6)) or
(x=6 and (y=4 or y=5)) or
(x=7 and y=4)
) and z=4
) or (
(
(x=5 and (y=5 or y=7)) or
(x=7 and y=5)
) and z=5
) or (
(
(x=6 and (y=6 or y=7)) or
(x=7 and y=6)
) and z=6
) or (
x=7 and y=7 and z=7
)

quite big, but this is evaluated only once, at compilation time, so it should be an acceptable workaround in some case !

difference between EXPLAIN PLAN and SET AUTOTRACE TRACEONLY EXPLAIN

most of the time I use set autot trace exp in order to get the execution plan. It seems more easy than explain plan for [query]; followed by select * from table (dbms_xplan.display);.

However, take care, set autotrace traceonly explain does modify the rows if you explain a plan for insert/update/delete.

SQL> set autot trace exp
SQL> delete emp;

14 rows deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 3538878155

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 14 | 98 | 2 (0)| 00:00:01 |
| 1 | DELETE | EMP | | | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL> set autot off
SQL> select count(*) from emp;
0
SQL> roll
Rollback complete.

but explain plan does not
SQL> select count(*) from emp;
14

SQL> explain plan for delete emp;

Explained.

SQL> select * from table (dbms_xplan.display);
Plan hash value: 3538878155

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 14 | 98 | 2 (0)| 00:00:01 |
| 1 | DELETE | EMP | | | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL> select count(*) from emp;
14

change MAXDATAFILES, MALOGFILES, MAXINSTANCES, MAXLOGMEMBERS without downtime

this is a 10gR2 new feature. It is no longer necessary to recreate the control file to increase those parameters. Actually, you do not have to change them, they change “automatically”

Demo

SQL> CREATE DATABASE
MAXDATAFILES 5
MAXINSTANCES 1
MAXLOGFILES 2
MAXLOGMEMBERS 1
extent management local
default tablespace users
default temporary tablespace temp
undo tablespace undotbs1;

Database created.

SQL> create tablespace t1;

Tablespace created.

SQL> create tablespace t2;

Tablespace created.

SQL> select count(*) from v$datafile;
COUNT(*)
----------
6

datafiles exceeded, but no error!


SQL> alter database add logfile
('/dbms/oracle/LSC75/redo/f1.sql',
'/dbms/oracle/LSC75/redo/f2.sql') size 16M;

Database altered.

SQL> select group#, members from v$log;
GROUP# MEMBERS
---------- ----------
1 1
2 1
3 2

logfiles and logmembers exceeded, but no error!


SQL> alter database add logfile instance 'I2';

Database altered.

SQL> alter database add logfile instance 'I2';

Database altered.

SQL> alter database enable instance 'I2';

Database altered.

SQL> select count(*) from v$INSTANCE_LOG_GROUP;
COUNT(*)
----------
2

instances exceeded, but no error!

so well, then why bother any more about specifying a big MAXDATAFILES and MAXLOGFILES at db creation?

select * from test where my_long like ‘%toto%’

A good way to learn is to try to answer user questions. Instead of referencing other posts, I tried today to answer that frequently asked question myself on developpez.com (french forum)


SQL> create table test ( my_long long);

Table created.

SQL> insert into test values ('hello toto !');

1 row created.

SQL> exec for r in ( select my_long from test )
loop if (r.my_long like '%toto%') then
dbms_output.put_line(r.my_long); end if;
end loop
hello toto !

oem grid control 10gR2 on Suse10

Last friday I installed RAC on my Suse10. Today, I installed Grid Control.

Of course it is not supported.

The packages I neeeded additionaly are db1 and openmotif.

It did not work 100%. I finally started the iasconsole (emctl start iasconsole), and within the iasconsole, I restarted the failed elements.

I created the repository manually (sysman/admin/emdrep/bin/RepManager)

Suse10 10gR2 Laptop RAC

Hi,
[EDIT]
I did read Dizwell article about Linux 10g Laptop RAC. The article is no longer available
[/EDIT]

I have just installed RAC 10gR2 on Suse10.

The document above guided me thru the steps.

Specific Suse10 :
– raw devices are configured by editing /etc/raw with values like raw1:loop1 and must be activated with rcraw start (+ chkconfig -a raw for persitence)

To take care :
– read point 8 to survive reboot…

Specific 10gR2 :
In 10gR2, MAXINSTANCES must not be specified by create controlfiles. In 10gR2, the controlfile can grow dynamically. Even if I created my controlfile with maxinstances=1, I can do alter database add logfile instance 'RAC2' ; alter database add logfile instance 'RAC2' ; alter database enable instance 'RAC2';, which is simply great! CREATE CONTROLFILE just belongs to the past!

Installation:
I had to run vipca manually as root at the end of the root script of the crs installation. I also removed my existing oracle_homes and rebooted before installing database software again. The VIP interface must be configured in /etc/hosts and DOWN (ifconfig down eth0:2).

Conclusion:
The doc written by Amit Poddar on Howad site is usefull. Enjoy rac’ing on your pocket desk calculator!

lock system, restrict dbsnmp

An unlocked user is a security problem. Currently, all my unlocked users have only CREATE SESSION as system privilege, evtl ALTER SESSION.

Except SYS, SYSTEM and DBSNMP

To minimize this security problem, I implemented the following strategy on my test system.

1) delete password file, set remote_login_passwordfile=NONE, O7_DICTIONARY_ACCESSIBILITY=FALSE
2) alter user SYSTEM account lock;
3a) in 10gR2 :
alter user dbsnmp quota 1T on sysaux;
create role secure_oem_role;
grant advisor, analyze any, analyze any dictionary, create job, create procedure, create session, create table, manage any queue, select any dictionary to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_AQ” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_AQADM” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_DRS” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_MONITOR” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_SERVER_ALERT” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_SYSTEM” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_WORKLOAD_REPOSITORY” to secure_oem_role;
exec SYS.DBMS_AQADM.GRANT_QUEUE_PRIVILEGE(‘DEQUEUE’, ‘ALERT_QUE’, ‘SECURE_OEM_ROLE’)
revoke EXECUTE ON “SYS”.”DBMS_SERVER_ALERT” from dbsnmp;
revoke EXECUTE ON “SYS”.”DBMS_SYSTEM” from dbsnmp;
revoke UNLIMITED TABLESPACE from dbsnmp;
revoke SELECT ANY DICTIONARY from dbsnmp;
revoke CREATE PROCEDURE from dbsnmp;
revoke CREATE TABLE from dbsnmp;
revoke OEM_MONITOR from dbsnmp;
grant secure_oem_role to dbsnmp;
3b) in other versions, you probably can remove more and grant less, I think only in 10g it is necessary to have “quota”. In my other databases, dbsnmp have 0 segments.

Check what system privileges are potentially dangerous to the system :

select path
from
(
select
grantee,
sys_connect_by_path(privilege, ‘:’)||’:’||grantee path
from (select grantee, privilege, 0 role from dba_sys_privs union all select grantee, granted_role, 1 role from dba_role_privs)
connect by privilege=prior grantee
start with role=0
)
where
grantee in (
select username from dba_users
where lock_date is null
and password != ‘EXTERNAL’
and username != ‘SYS’)
or grantee=’PUBLIC’
/
:ADVISOR:SECURE_OEM_ROLE:DBSNMP
:ANALYZE ANY:SECURE_OEM_ROLE:DBSNMP
:ANALYZE ANY DICTIONARY:SECURE_OEM_ROLE:DBSNMP
:CREATE JOB:SECURE_OEM_ROLE:DBSNMP
:CREATE PROCEDURE:SECURE_OEM_ROLE:DBSNMP
:CREATE SESSION:USER1
:CREATE SESSION:USER2
:CREATE SESSION:SECURE_OEM_ROLE:DBSNMP
:CREATE TABLE:SECURE_OEM_ROLE:DBSNMP
:MANAGE ANY QUEUE:SECURE_OEM_ROLE:DBSNMP
:SELECT ANY DICTIONARY:SECURE_OEM_ROLE:DBSNMP

it sounds better…

the forums I use

on otn : forums.oracle.com
1) SQL and PL/SQL
2) Database General
3) iSQL*Plus
4) Documentation Feedback

when otn is down or too slow, I read the metalink forums. Especially SQL*Plus and Oracle PL/SQL. If necessary, I post questions on the DBA Administration forum.

to post questions/comments specific to security, I go to Pete Finnigan’s Oracle Security Forum.

Sometimes I post comments on the asktom site. About leap years, number to octal, number to words…

Occasionaly, I visit the Dizwell Forum, just to grab some interesting post…

When it is nice weather, I go to usenet comp.databases.oracle.server

And of course, I use google a lot to find new places to go !

idle events in 10gR2

I just noticed this morning that idle events are very easily identifiable by a new column called wait_class in 10gR2

To ignore idle event, I just wrote

select WAIT_CLASS, event
from (
select *
from V$SYSTEM_EVENT
where WAIT_CLASS#!=6
order by TIME_WAITED_MICRO desc)
where rownum<6 ; WAIT_CLASS EVENT ------------- ---------------------------------------- System I/O log file parallel write Configuration log file switch (checkpoint incomplete) Configuration log file switch completion System I/O db file parallel write System I/O control file parallel write

new metalink interface

I just discovered that new interface today.

Hopefully, my old bookmarks still work.

I did not find the “Tar” button. Well, it is now named “Service Request”.

I tried the ORA-600 lookup tool, Doc id 153788.1
but it does not work today, both Firefox and Explorer failed.

MS Explorer reports a Java Script error.

document.forms.0.tool_type.value is null or is not an object

Just too sad… it is a very important tool. Anyway, I could search by using “ora-600 4883” to find out what I am looking for.

sys_connect_by_path in 8i or the danger to use undocumented parameters…

I have been posting on metalink technical forum about a query that I run against all my test databases but did not work in production.

as it simpliest form

select sys_connect_by_path(dummy,’:’) from dual connect by 1=2;

well, there is nothing wrong with this query. I tried it on 8i, 9iR2, 10gR1 10gR2 and it worked fine. In production, it just refused to work on 8i.

Well, I have an other bitset of 8i on my test environment, and an other operating system version. So I supposed it must be related to the old production 64bits AIX4 os.

After reading the docs again, I discovered that SYS_CONNECT_BY_PATH is not documented in 8i. So why did this work on my test system then? Because I have _new_connect_by_enabled = true in my init.ora.

Ok, on the one hand, I found out an (unsupported) way to let my query run in 8i

alter session set “_new_connect_by_enabled”=TRUE;

On the other hand, I have been tricked by my own parameter file, setting one hidden parameter ages ago, forgetting about it, and the query “unhopefully succeeded” in my test environment… revealing the bug only once distributed !

oracle10gR2 on suse10

I prefer and recommend using Oracle on a supported version, like Suse Entreprise 9, because the installation is fair. The Installer does complain on SLES9 what is missing.

Ok, I just received a brand new notebook, I decided to go to Suse 10, and, Ô Miracle, the installation was pretty easy! I surely installed C++ development tools (how can I survive without a C compiler), but I missed the libaio and libaio-devl. Well, I installed it afterwards and my create database statement succeeded.

Sincerly, installing Oracle 10gR2 on Suse 10 is no longer a pain as it was before with non-supported versions.

I am so happy 😉

Well, my gnome evolution ms-exchange connector is still not working, but this I will fix asap

row generators performance

I wrote a few generators, and listed some existant in
http://laurentschneider.blogspot.com/2005/08/pivot-table.html

I decided to test them

note that this is not a “good” test, it is simply an overview. I am doing a count(*), another operation may be better in one or worst in another one.

all_objects is so slow (6 seconds for 50K rows) that I did not include it in my test

generate 1000 rows : all method are fast, except xquery…

generate 1000000 rows : model and cube never ends, xquery is very slow, connect and union are slow, plsql is ok, simple heap table is very fast (!)

I would like to point out that “generating rows” is rarely a business requirement. If you need to outer join with every day of the year, than create a table and insert every day of the year in it. It will be very fast, it is maintenable, extensible, supported, and self-documented…

Ok, here is my test

create type t_number as TABLE OF number;
/

create function f_number(n number) return t_number pipelined is
begin for i in 1..n loop pipe row(i); end loop; return; end;
/

create table t1000 as select 0 n from xmltable(‘for $i in 1 to 1000 return $i’ );
create table t1000000 as select 0 n from xmltable(‘for $i in 1 to 1000000 return $i’ );

set timi on feedb off echo off head off

prompt model 1000
select count(*)
from (
select null
from dual
model
dimension by (1 h)
measures (1 c)
rules( c[FOR h FROM 1 to 1000 INCREMENT 1] = 1)
)
/

prompt xquery 1000
select count(*)
from
xmltable(‘for $i in 1 to 1000 return $i’ )
/

prompt xquery 1000000
select count(*)
from
xmltable(‘for $i in 1 to 1000000 return $i’ )
/

prompt union 2*2*… 1K
with s as (select null from dual union all select null from dual)
select count(*)
from s,s,s,s,s,s,s,s,s,s
/

prompt union 1M
with s as (select null from dual union all select null from dual)
select count(*)
from
s,s,s,s,s,s,s,s,s,s,
s,s,s,s,s,s,s,s,s,s
/

prompt cube 1K
select count(*)
from (
select 1,2,3,4,5,6,7,8,9,10
from dual
group by cube(1,2,3,4,5,6,7,8,9,10)
)
/

prompt table 1000
select count(*)
from t1000
/

prompt table 1000000
select count(*)
from t1000000
/

set termout off
create type t_number as TABLE OF number;
/

create function f_number(n number) return t_number pipelined is
begin for i in 1..n loop pipe row(i); end loop; return; end;
/
set termout on

prompt plsql 1000
select count(*)
from table(f_number(1000))
/

prompt plsql 1000000
select count(*)
from table(f_number(1000000))
/

prompt all_objects
select count(*)
from all_objects
/

prompt connect 1000
select count(*)
from (
select level
from dual
connect by level

Tom Day 3 : read consistency, transparent data encryption, dbms_advanced_rewrite

Day 3 concludes the Tom workshop in Switzerland. We started with read consistency and write consistency, with an interesting example were a single row update could make a big job run thrice slower, because of the write consistency.

Once again, Tom insist on saying that you must understand Oracle to write applications.

Tom demonstrates a few 10g features. Even if I already attended his 10gR2 new features in OpenWorld, I learnt a few interesting stuff about 10gR1, and the demo on his notebook were impressing.

Ok, let’s reveal some of those 😉

You have surely heard of flashback database, but did you ever heard of flashforward ??? Well, if you flashback, open readonly, than you can flashback to the future afterwards !

Did you know about Case Insensitive? Did you try it? By writing the correct index, setting the correct parameters (NLS_COMP=ANSI, NLS_SORT=binary_ci), you will be using a regular index where doing where ENAME=’Scott’, the execution will actually reveal an index RANGE scan.

A life-saving package I have never heard of is DBMS_ADVANCED_REWRITE. This is simply a great way of rewriting a call you cannot rewrite in the application. Each call to the server could be rewritting by the dba. A great tuning capability. Maybe I will never use that, sounds a bit too much frightening to me, but I am so glad to know about it !

Finally, the TDE (transparent data encryption) demo was educative. On the one hand, if you do not have the wallet password, even if you are the DBA, than you cannot select an encrypted column!

Does it mean that you can hide data from the dba? Tom says NO WAY. The dba can always look your bind variable, look your package functions, and catch all your datas. What he think (and I do think that too), is that you can have a non-powerfull dba, which can reset your password, create a new user, drop a datafile, but which does not need to have the DBA role. Kind of pseudo-dba.

I believe that some systems contain data that no one is able to access. Not even the database administrator. In bank, it is not the case, the DBA is trusted, he signed some contracts to not reveal datas, etc… But in justice for example, I could imagine that the dba does not have the power to select any table (so will not be granted the dba role). I have been postings on forums quite a lot of time about it. For example thread 499144.995 in metalink in 2003. But I have no real experience about “secret” data which are hidded from the dba. Just thoughts.

One more great feature I learnt and will try on monday is datapump compression. We have always been compression regular export dumps with named pipe and “compress” in unix, there is finally a way to compress datapump exports.

Tom said: “I learn something about Oracle every single day”. Well, in Tom workshop, I learnt 42 new things in 3 days.

Tom Kyte Day 2

Today I asked : “Is it the responsability of the developper to create the table structure?”

The answer was something like that :
“You have four kind of persons.
– You have the Oracle6 DBA, who says always NO
– You have the Developer, who does not care about database
– You have the DBA/Developer, who understand the logical structures like IOT/Hash Cluster
– You have the Developer/DBA, who understand the database
In a perfect world, the Developer/DBA choose the table at design time and the DBA/Developer inform the developer about the database capabilities.
If you have a developer and a dba/developer, the dba/developer can reorganize the table as a tuning action.”

After, Tom talked about sql technics, show a few examples about “CONNECT BY” without prior generating rows, a pipelined table. Very interesting examples, with a few keyword like FIRST_VALUE and IGNORE NULLS that I have never used before.

Later in the afternoon, he starts talking about binding. Difficult topic, I was almost going to sys.dbms_lock.sleep at the end of the day.

We are a bit late on the program, probably too many questions, palindnilap and myself feel a bit bored about one person always asking a lot of question mostly off topic about experiences he had but nobody cares of…

Well, I am impatient about day three, it is going to be intense, read-write consistency among others themas.

tbc

Tom Kyte Day 1

Day one was quite interesting! We learnt a lot of staff about tuning approach, I have got confirmation that most of the “WE KNOW THAT, IT HAS ALWAYS BEEN SO” were maybe one day true, but are no longer, for example “separate index and tables”, or, delightfull, “you must periodically reorganise your tables”. I also learnt about DBMS_APPLICATION_INFO, which I may use in my dba-scripts too.

I also feel relaxed that designing a table to be IOT or Hash-Clustered is NOT a dba task, but a developer task. Wow, finally, the dba have to look at the database not at the table structure to gain performance…

In the evening, we had a dinner with my friend Fabrice, my ZKB-collegue Roman, my future ex-lc collegue Marc, a reader of tom blog and mine one called Leo, and also Lutz, who is working for Oracle University, and of course Tom, who enjoyed the fishes 😉

soon day 2, so i have to go to bed right now

encrypted listener password

There a few major changes in the database administration and the database security between 9i and 10g.

In 9i, I used to grep in the listener.ora to find out the password.

LISTENER_LSC61 =
(DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10061)(QUEUESIZE=200))
))
PASSWORDS_LISTENER_LSC61 = 1234567890ABCDEF

this 64bit encrypted string can be used in 9i to stop the listener

$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 9.2.0.6.0 – Production on 05-DEC-2005 14:33:51

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL> set current_listener listener_lsc61
Current Listener is listener_lsc61
LSNRCTL> set password 1234567890ABCDEF
The command completed successfully
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10061)(QUEUESIZE=200)))
The command completed successfully

As a dba, it is quite handy, because you can use grep (or awk) to find out the password out of the listener.ora. As a security admin, you should make sure the listener.ora is not readable. Note that the default, when created by netmgr, is to be world-readable 🙁

However, this does no longer work in 10g

LISTENER_LSC62 =
(DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10062)(QUEUESIZE=200))
))

PASSWORDS_listener_LSC62 = 1234567890ABCDEF

the encrypted string can no longer be used

$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 10.1.0.4.0 – Production on 05-DEC-2005 14:37:24

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL> set current_listener listener_lsc62
Current Listener is listener_lsc62
LSNRCTL> set password 1234567890ABCDEF
The command completed successfully
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10062)(QUEUESIZE=200)))
TNS-01169: The listener has not recognized the password
TNS-01189: The listener could not authenticate the user

As a security admin, you would think it is better so. But, how are you going to stop the listener in your script? Well, in 10g, we can use local authentification (default). So if the script is started as oracle, we would not need to use password

LISTENER_LSC63 =
(DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10016)(QUEUESIZE=200))
))

PASSWORDS_listener_LSC63 = 1234567890ABCDEF

$ whoami
oracle
$ hostname
dbsrv85a.ex.zkb.ch
$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.1.0 – Production on 05-DEC-2005 14:43:33

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL> set current_listener LISTENER_LSC63
Current Listener is LISTENER_LSC63
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10016)(QUEUESIZE=200)))
The command completed successfully

I read in an Alex Kornbrust post on Pete Finnigan forum, that a LOCAL_OS_AUTHENTICATION “undocumented” parameter could be used to “avoid” local authentication, but in that case, it is going to be a nightmare to “stop” the listener in an automated script, well, we can still use “kill”, but it is not very beautifoul.