Add new tag Blogroll installation

patch is out

check on metalink for availability:
patch 4505133

Add new tag Blogroll installation OEM

grid control release 2

I am waiting for eomgc 10gR2 for AIX so I check the download page often. Instead of adding new plateforms, I notice they just removed Solaris !
Oracle Enterprise Manager 10g Grid Control Release 2 ( for Solaris Operating System (SPARC)

google cache

Application Server Blogroll installation linux

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
* 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
* 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 (
* 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

Blogroll sql

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
if (v is null) then return null;
else return substr(v,1,1)||f(replace(substr(v,2),substr(v,1,1));
end if;

ref: using recursion with PL/SQL

Add new tag Blogroll installation sql developer

raptor early adopter release 3.1 is out

raptor product page

I have not checked the differences with 3.0 yet, probably bug fixes with “do not save passwords”

Add new tag Blogroll installation sql developer

raptor early adopter release 3 is out

This is THE release I have been waiting for, a raptor which does NOT save the password on your local PC. Save a password, even encrypted, on you local PC allows every one with physical access to your PC to get access to your productive database, which may be high-secured and with controlled physical access.

Download immediatly on otn !

Blogroll 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 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 !

Blogroll dba


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;
SQL> roll
Rollback complete.

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

SQL> explain plan for delete emp;


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;

Blogroll dba


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”


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;

datafiles exceeded, but no error!

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

Database altered.

SQL> select group#, members from v$log;
---------- ----------
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;

instances exceeded, but no error!

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

Blogroll dba security

security bug revealed

Pete Finnigan just mentioned a bug, which allow any user in any oracle version to get dba privilege Imperva discovers a critical access control bypass in login bug.

This is incredible!

Well, Pete urged you to apply CPU2006January asap.

Blogroll dba sql

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 (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 !

Add new tag Blogroll installation sql developer

new version of raptor

a few improvement described in the readme.txt. One of this is big-tnsfile support.

always missing is a way to NOT SAVE the password on the client!

Blogroll installation linux OEM

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)

Blogroll dba sqlplus

clear screen reports cleared columns, breaks and computes

$ echo clear screen|sqlplus scott/tiger
SQL> columns cleared
breaks cleared
computes cleared

but if I quit properly, it does not report that

$ echo "clear screen
quit"|sqlplus scott/tiger

a good reason to improve the quality of your shell script by quitting at the end 😉

Blogroll installation linux RAC

Suse10 10gR2 Laptop RAC

I did read Dizwell article about Linux 10g Laptop RAC. The article is no longer available

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!

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).

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

Blogroll dba sqlplus

Do you shu or do you spo?

When I quit ftp command line, I do not type quit, nor bye, because it is too long. I rather type “by”.

Does it sound strange and meaningless to abbreviate “bye” in “by” ? Well, I have a few favorites

SQL> set lin 999
SQL> rollb
SQL> spo f
SQL> shu
SQL> spo off

Blogroll dba OEM security

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.


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;
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
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
grantee in (
select username from dba_users
where lock_date is null
and password != ‘EXTERNAL’
and username != ‘SYS’)
or grantee=’PUBLIC’

it sounds better…


the forums I use

on otn :
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

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

Blogroll dba

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 *
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

Add new tag Blogroll installation

oemgc 10 release 2

solaris version now available
Entreprise Manager download (Solaris 32bits)

Add new tag Blogroll

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.

Blogroll dba

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 !

Blogroll installation linux

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

Blogroll sql

row generators performance

I wrote a few generators, and listed some existant in

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
dimension by (1 h)
measures (1 c)
rules( c[FOR h FROM 1 to 1000 INCREMENT 1] = 1)

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

prompt xquery 1000000
select count(*)
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(*)

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

Blogroll event

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.

Blogroll event

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.


Blogroll event


Blogroll event

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

Blogroll dba security


I just tried today to limit power of rman :


It seems I can still do a backup… probably those privilege are not needed by rman, maybe just inherited from Connect in an older released !?

Blogroll dba security

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.


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

$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version – 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
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


PASSWORDS_listener_LSC62 = 1234567890ABCDEF

the encrypted string can no longer be used

$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version – 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
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


PASSWORDS_listener_LSC63 = 1234567890ABCDEF

$ whoami
$ hostname
$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version – 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
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.

Blogroll sqlplus

the sqlplus settings I like

It is monday, I am going to give a list of settings I like in sqlplus

set lin 32767 trimsp on tab off
extends the linesize and avoid line breaks. I use it before SPOoling. But it is annoying before DESCribing. Trimspool is necessary to avoid spaces at the end of the line. Set tab off makes sure sqlplus does not use “tab” for formatting, but spaces.

set emb on pages 0 newp none
this avoid page breaks. there is one header in the top, than no more, and no ^L. newp none is not working in version 7, there you must use newp 0.

set head on
set head off
show or hide column headers

set feedb 6
set feedb off
report result of query, set feedb 6 do not give feedback if a select returned 1 to 5 lines, because it is too easy to count… Set feedback off removes feedback

set ver off
I am never interrested in the translation of my defined variables

set termout on
set termout off
Off avoids screen output. Warning, this does not avoid spool output. Works only in scripts, not in command mode. Note that a command piped thru sqlplus is still a command more.

set echo on
set echo off
Display executed command. Works only in scripts, not in command mode.

sqlplus / <<EOF
set echo on
set termout off
select * from dual;

the echo on and termout off will have no effect, because it is not a sql script (called with @).

def _editor=vi
set editf /tmp/lscfile.sql
Use vi (instead of ed) as editor, and use a file in /tmp (instead of afiedt.buf in working directory) as temp file

set long 1000000000 longc 60000
do not truncate longs nor long chunks. Very usefull with clob in sqlplus.

set serverout on size 1000000
set serverout on size unlimited
allows dbms_output to print to current terminal. Unlimited is a 10gR2 enhancement

change the prompt to contain a dynamic user and connection string.

thursday I am having dinner with tom kyte, drop me a comment there if you want to come