Laurent Schneider

EZCONNECT is the easy connect protocol, available in 10g, whenever you want to connect to a database without tnsnames and without ldap.
$ grep -iw directory_path $TNS_ADMIN/sqlnet.ora
names.directory_path=EZCONNECT
$ sqlplus scott/tiger@//srv01:1521/db01
connect to server srv01 on port 1521 for service db01

HOSTNAME was the old-fashion way to connect to a database, where hostname = sid and port = 1521. In this regard EZCONNECT is just an extension of the hostname method.

Typical HOSTNAME usage, that is the same as EZCONNECT with default port 1521.
sqlplus scott/tiger@db01
connect to server db01 on port 1521 for service db01

There is a behavior change between 10g and 11g. In 10g, the default service name defaulted to the DNS alias used to connect. In 11g, the default is null.
$ nslookup db01
Server: ns001.example.com
Address: 198.0.0.30

Name: srv01.example.com
Address: 198.0.0.60
Aliases: db01.example.com

$ nslookup db02
Server: ns001.example.com
Address: 198.0.0.30

Name: srv01.example.com
Address: 198.0.0.60
Aliases: db02.example.com


Both DB01 and DB02 DNS aliases point to the same server.

Let’s try with 10g
$ sqlplus -L scott/[email protected]

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Feb 7 15:46:53 2011

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

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

SQL> select global_name from global_name;
GLOBAL_NAME
---------------------------------------
DB01.EXAMPLE.COM
SQL> quit
$ sqlplus -L scott/[email protected]

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Feb 7 15:47:33 2011

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

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

SQL> select global_name from global_name;
GLOBAL_NAME
---------------------------------------
DB02.EXAMPLE.COM


Let’s try with 11g sqlplus
$ sqlplus -L scott/[email protected]

SQL*Plus: Release 11.2.0.2.0 Production on Mon Feb 7 15:50:27 2011

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

ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus


It no longer works. Period. This is documented as Problem 556996.1 in Metalink.

A 10g tnsping will reveal
$ tnsping db01.example.com:1521

TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production on 07-FEB-2011 15:52:34

Copyright (c) 1997, 2006, Oracle. All rights reserved.

Used parameter files:
/home/lsc/sqlnet.ora

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=db01.example.com))(ADDRESS=(PROTOCOL=TCP)(HOST=198.0.0.60)(PORT=1521)))
OK (80 msec)

In 10g the service_name is the connection dns alias used

In contrary, the 11g tnsping service name is null
$ tnsping db01.example.com:1521

TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production on 07-FEB-2011 15:56:55

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:
/home/lsc/sqlnet.ora

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=198.0.0.60)(PORT=1521)))
OK (10 msec)


The tnsping works, but the service_name is empty.

How to fix this?

1) you specify the SID in easy connect (yes, this is easy!)
$ tnsping db01.example.com:1521/db01.example.com

TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production on 07-FEB-2011 15:59:10

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:
/home/lsc/sqlnet.ora

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=db01.example.com))(ADDRESS=(PROTOCOL=TCP)(HOST=198.0.0.60)(PORT=1521)))
OK (10 msec)


2) you use 10g, or 10g behavior in 11g with patch 9271246 (available only on a limited number of plateforms, os and db versions),

3) you specify a default service for your listener
$ vi listener.ora
DEFAULT_SERVER_LISTENER=DB01
$ lsnrctl reload
$ sqlplus -L scott/tiger@db01

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

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

SQL>


This is a bit confusing as if you are servicing more than one database per listener, all dns aliases will default to the same database. So I would not recommend a default service name if there is more than one service name.

It is a very common challenge for a dba to create scripts that work on every version.

How do you return NULL if a column does not exists?

Imagine I have a view that returns the table_name, column_name and retention_type of my LOBS.


SQL> create table t1(c clob) lob(c) store as (retention);

Table created.

SQL> create table t2(c clob) lob(c) store as (pctversion 10);

Table created.

SQL> create or replace force view v as select table_name,
column_name,retention_type from user_lobs;

View created.

SQL> select * from v where table_name in ('T1','T2');
TAB COL RETENTION_TYPE
--- --- --------------
T1 C YES
T2 C NO


Let’s imagine I try to run this on an antique version of Oracle

SQL> select version from v$instance;
VERSION
-----------------
11.2.0.1.0

SQL> create table t1(c clob) lob(c) store as (retention);

Table created.

SQL> create table t2(c clob) lob(c) store as (pctversion 10);

Table created.

SQL> create or replace force view v as select table_name,column_name,retention_type from user_lobs;

Warning: View created with compilation errors.

SQL> select * from v where table_name in ('T1','T2');
select * from v where table_name in ('T1','T2')
*
ERROR at line 1:
ORA-04063: view "SCOTT.V" has errors


Obviously the RETENTION_TYPE did not exist in that version.

Let’s default this to NULL !

SQL> create or replace function retention_type return varchar2 is
begin return null; end;
/

Function created.

SQL> select * from v where table_name in ('T1','T2');
TAB COL RETENTION_TYPE
--- --- --------------
T1 C
T2 C


Very simple workaround, is not it?

Before you start reading : do not rebuild all your indexes and reorganize all your tables every Sunday morning. One day you may find one of your table missing or one index invalid.

Ok, let’s take a case where table reorg and index rebuild is good.

One of your table was never cleaned up, it grew to 100000000 rows over the last 5 years and you need only the last 2 weeks.

One of your task will be to create a job to clean up your table on a weekly basis to delete rows older than 14 days. This is beyond the scope of this post.

Now you have deleted more than 99% of your rows and you want to reorganize your table and rebuild the index, to gain disk space and performance.

Here is the demo


SQL> DROP TABLE t1;

Table dropped.

SQL>
SQL> CREATE TABLE t1
2 (
3 r NUMBER,
4 txt VARCHAR2 (4000),
5 y NUMBER
6 );

Table created.

SQL>
SQL> CREATE INDEX i1
2 ON t1 (r);

Index created.

SQL>
SQL> INSERT INTO t1
2 WITH t
3 AS ( SELECT *
4 FROM DUAL
5 CONNECT BY LEVEL < 1001) 6 SELECT ROWNUM r, LPAD ('X', 100, '.') txt, MOD (ROWNUM, 2) y 7 FROM t, t; 1000000 rows created. SQL>
SQL> DROP TABLE t2;

Table dropped.

SQL>
SQL> CREATE TABLE t2
2 (
3 r NUMBER,
4 txt VARCHAR2 (4000),
5 y NUMBER
6 )
7 PARTITION BY HASH (r)
8 (PARTITION T2_P1);

Table created.

SQL>
SQL> CREATE INDEX i2
2 ON t2 (r)
3 LOCAL (PARTITION i2_p1);

Index created.

SQL>
SQL> INSERT INTO t2
2 WITH t
3 AS ( SELECT *
4 FROM DUAL
5 CONNECT BY LEVEL < 1001) 6 SELECT ROWNUM r, LPAD ('X', 100, '.') txt, MOD (ROWNUM, 2) y 7 FROM t, t; 1000000 rows created. SQL>
SQL> DROP TABLE t3;

Table dropped.

SQL>
SQL> CREATE TABLE t3
2 (
3 r NUMBER,
4 txt VARCHAR2 (4000),
5 y NUMBER
6 )
7 PARTITION BY RANGE (r)
8 SUBPARTITION BY HASH (r)
9 SUBPARTITION TEMPLATE (SUBPARTITION s1 )
10 (PARTITION T3_P1 VALUES LESS THAN (maxvalue));

Table created.

SQL>
SQL> CREATE INDEX i3
2 ON t3 (r)
3 LOCAL (PARTITION i3_p1
4 (SUBPARTITION i3_p1_s1));

Index created.

SQL>
SQL> INSERT INTO t3
2 WITH t
3 AS ( SELECT *
4 FROM DUAL
5 CONNECT BY LEVEL < 1001) 6 SELECT ROWNUM r, LPAD ('X', 100, '.') txt, MOD (ROWNUM, 2) y 7 FROM t, t; 1000000 rows created. SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT segment_name,
2 segment_type,
3 partition_name,
4 sum(bytes),
5 count(*)
6 FROM user_extents
7 WHERE segment_name IN ('T1', 'T2', 'T3', 'I1', 'I2', 'I3')
8 group by
9 segment_name,
10 segment_type,
11 partition_name
12 ORDER BY segment_name, partition_name;

SEGMENT_NA SEGMENT_TYPE PARTITION_ SUM(BYTES) COUNT(*)
---------- ------------------ ---------- -------------- --------------
I1 INDEX 16,777,216 31
I2 INDEX PARTITION I2_P1 16,777,216 31
I3 INDEX SUBPARTITION I3_P1_S1 16,777,216 31
T1 TABLE 134,217,728 87
T2 TABLE PARTITION T2_P1 134,217,728 16
T3 TABLE SUBPARTITION T3_P1_S1 134,217,728 16


I created 3 tables, T1, T2 which is partitioned, T3 which is subpartitioned. There is a slight difference in the number of extents between partitioned and non-partitioned table, but this ASSM, so it is fine.


SQL> DELETE FROM t1
2 WHERE r > 1;

999999 rows deleted.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> DELETE FROM t2
2 WHERE r > 1;

999999 rows deleted.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> DELETE FROM t3
2 WHERE r > 1;

999999 rows deleted.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT segment_name,
2 segment_type,
3 partition_name,
4 sum(bytes),
5 count(*)
6 FROM user_extents
7 WHERE segment_name IN ('T1', 'T2', 'T3', 'I1', 'I2', 'I3')
8 group by
9 segment_name,
10 segment_type,
11 partition_name
12 ORDER BY segment_name, partition_name;

SEGMENT_NA SEGMENT_TYPE PARTITION_ SUM(BYTES) COUNT(*)
---------- ------------------ ---------- -------------- --------------
I1 INDEX 16,777,216 31
I2 INDEX PARTITION I2_P1 16,777,216 31
I3 INDEX SUBPARTITION I3_P1_S1 16,777,216 31
T1 TABLE 134,217,728 87
T2 TABLE PARTITION T2_P1 134,217,728 16
T3 TABLE SUBPARTITION T3_P1_S1 134,217,728 16


I deleted the completed table but one row, however the size of the table and the number of extents did not change.


SQL> ALTER TABLE t1 MOVE;

Table altered.

SQL>
SQL> ALTER INDEX I1 REBUILD;

Index altered.

SQL>
SQL> ALTER TABLE t2 MOVE PARTITION T2_P1;

Table altered.

SQL>
SQL> ALTER INDEX I2 REBUILD PARTITION I2_P1;

Index altered.

SQL>
SQL> ALTER TABLE t3 MOVE SUBPARTITION T3_P1_S1;

Table altered.

SQL>
SQL> ALTER INDEX I3 REBUILD SUBPARTITION I3_P1_S1;

Index altered.

SQL>
SQL> SELECT segment_name,
2 segment_type,
3 partition_name,
4 sum(bytes),
5 count(*)
6 FROM user_extents
7 WHERE segment_name IN ('T1', 'T2', 'T3', 'I1', 'I2', 'I3')
8 group by
9 segment_name,
10 segment_type,
11 partition_name
12 ORDER BY segment_name, partition_name;

SEGMENT_NA SEGMENT_TYPE PARTITION_ SUM(BYTES) COUNT(*)
---------- ------------------ ---------- -------------- --------------
I1 INDEX 65,536 1
I2 INDEX PARTITION I2_P1 65,536 1
I3 INDEX SUBPARTITION I3_P1_S1 65,536 1
T1 TABLE 65,536 1
T2 TABLE PARTITION T2_P1 8,388,608 1
T3 TABLE SUBPARTITION T3_P1_S1 8,388,608 1


Now I have reorganized my tables and rebuilt my indexes.

The sized dropped to 64K or 8M and the fragmentation disappeard as the number of extents dropped to 1.

Note you cannot rebuild a whole partitioned index (ORA-14086) nor reorganize a whole partitioned table (ORA-14511). You need to loop through each partition or subpartition.

I am not sure whether some tuning guy at Oracle decided to ignore any SELECT statement after execute immediate to save time doing nothing.
exec execute immediate 'select 1/0 from dual connect by level<9999999999999' PL/SQL procedure successfully completed. Elapsed: 00:00:00.00

But it is really annoying... and not documented as far as I know.

Imagine I want to increase all my sequences by 1000

SQL> create sequence s;

Sequence created.

SQL> select s.nextval from dual;

NEXTVAL
----------
1

SQL> begin
2 for f in (select sequence_name n from user_sequences)
3 loop
4 execute immediate
5 'select '||f.n||'.nextval from dual connect by level<=1000'; 6 end loop; 7 end; 8 / PL/SQL procedure successfully completed. SQL> select s.currval from dual;

CURRVAL
----------
1


Hmm, it does not work. Does SELECT work at all? Yes when it is a SELECT INTO 🙂

SQL> drop sequence s;

Sequence dropped.

SQL> create sequence s;

Sequence created.

SQL> select s.nextval from dual;

NEXTVAL
----------
1

SQL> declare
2 type t is table of number index by pls_integer;
3 c t;
4 begin
5 for f in (select sequence_name n from user_sequences)
6 loop
7 execute immediate
8 'select '||f.n||'.nextval from dual connect by level<=1000' 9 bulk collect into c; 10 end loop; 11 end; 12 / PL/SQL procedure successfully completed. SQL> select s.currval from dual;

CURRVAL
----------
1001


I wonder in which version this optimization/bug was introduced...

I wrote about powershell [xml] yesterady : xml and powershell

Let’s see how to use XPATH expressions in Powershell



John


Jack




With the [xml] datatype, we create a navigator :

(([xml](GC foo.xml)).psbase.createnavigator().evaluate(
'//emplist/emp[@no="1"]/ename/text()'
))|%{$_.Value}

John

I have not been seduced by a Microsoft product since ages, but I must say I felt with love in this goody much more than in perl, cygwin, or whatever python, dos, java, vb…

It is simply great to use on the command line and can do my work.

1:0 for Microsoft

I wrote about the unix command-line utility xmllint there : extract xml from the command line

Let’s do the same exercice in Powershell

PS> gc foo.xml


John


Jack




Simply use [xml] datatype !
(([xml](GC foo.xml)).emplist.emp|Where{$_.no-eq"1"}).ename
John

Powershell rules!

If you want to get rid of self signed certificate, and the annoying security warnings in your browser, here is how to do it in 2 easy steps

1) create a new wallet in [OMS]/sysman/wallet/console.servername/, either with owm (gui) or with orapki (command line)
2) restart opmn http server
opmnctl stopproc process-type=HTTP_Server
opmnctl startproc process-type=HTTP_Server

Later, to access your Enterprise Manager Grid Control homepage, access the Apache server and not the oms upload server

opmnctl status -l
HTTP_Server http1:7779,http2:7201,https1:4445,https2:1159,http3:4890

https1 is Apache (4445) and https2 is Upload (1159)

So the url will be https://yourserver.dom.com:4445/em

Check note 1278231.1

I had a very serious issue last Friday with errors as weird as ORA-00322: log name of thread num is not current copy. After a clone from Prod to Test, the prod crashed. Both databases are located on the same server (I am not a virtualization fanatic) and clone from prod to test have been done by most of my dba readers.

What did change in 11g ?

Incredibly, in 11g, rman issues the following statement before restore
sql clone "alter system set db_name = ''PROD'' ...
restore clone primary controlfile...

This is probably related to the capability of cloning a database without connecting to the target database.

At the end of the clone, rman is setting back the db_name to TEST and recreate the TEST controlfile
sql statement: alter system set db_name = ''TEST'' ...
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TEST" ...
...
LOGFILE
GROUP 1 ('/.../TEST/redo1.dbf')...


So what’s wrong with this? Howcome could a clone from prod to test screw up the prod db???

Simple, the RMAN job did not complete

1) set new name, restore prod controlfile to test
2) restore issue, for instance ORA-19870: error while restoring backup piece archive1234
3) RMAN-03002: failure of Duplicate Db command

At this point, the complete restore was finished, we restored the missing archivelog, recover and open resetlog.
What happened then???
At this point, remember you still have the prod controlfile (and the prod db_name), so by doing an alter resetlogs, the production redologs will get overwritten without notice !

This is a fairly important change that could really hurt if you are cloning two databases on the same server.

In case you are trying to save a failed database clone, make sure you check db_name and also v$logfile before doing an alter database resetlogs!!!

I just upgraded my blog to wordpress 3

Fuadar recently wrote : Grid Control 10.2.0.5 AIX Alert …

Basically, if you have an 10g oms Server (any OS / any release) and aix agents (any release), and according to Note 1171558.1, communication between [10g] Oracle Management Service and [AIX] Management Agents will break due to a default self-signed certificate expiring in 31 Dec 2010.

There is more than one way to solve this

1) you upgrade your oms to 11g. Good luck to do this before end of year…

2) You upgrade your oms to 10.2.0.5, apply patch 10034237 on your oms, create a new certificate, resecure all your agents. Pretty heavy stuff I promise.

3) You use a Third Party Certificate. This may work. I have not tested this for you.

4) You switch from https to http… this is of course not an acceptable workaround as the connection between the agent and the oms will be unsecure, but it may save your Silvester Party.

• allow both secure and unsecure connections to the oms

on all your OMS instances

opmnctl stopall
emctl secure unlock
opmnctl startall

• switch all your agents to http

On all your AIX hosts with an agent installed

emctl unsecure agent -omsurl http://omsserver:4890/em/*
You can find the port for unsecure in your oms server in OMSHOME/sysman/config/emoms.properties under oracle.sysman.emSDK.svlt.ConsoleServerPort.

---

Happy holidays !

---

patch 10098816 is available for download

I was amazed by this oneliner in stackoverflow.

First, let me introduce you my old foe, ORA-04068 :
Session 1:
SQL> CREATE OR REPLACE PACKAGE P AS
2 X NUMBER;Y NUMBER;END;
3 /

Package created.

SQL> exec P.X := 1

PL/SQL procedure successfully completed.

Session 2:
SQL> CREATE OR REPLACE PACKAGE P AS
2 X NUMBER;Z NUMBER;END;
3 /

Package created.

Session 1:
SQL> exec P.X := 2
BEGIN P.X := 2; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "SCOTT.P" has been invalidated
ORA-04065: not executed, altered or dropped package "SCOTT.P"
ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.P"
ORA-06512: at line 1


Changing the package in session 2 did invalidate the package variable in session 1.

And the PRAGMA that saves the world : PRAGMA SERIALLY_REUSABLE

Session 1:
SQL> CREATE OR REPLACE PACKAGE P AS
2 PRAGMA SERIALLY_REUSABLE;X NUMBER;Y NUMBER;END;
3 /

Package created.

SQL> exec P.X := 1

PL/SQL procedure successfully completed.


Session 2:
SQL> CREATE OR REPLACE PACKAGE P AS
3 PRAGMA SERIALLY_REUSABLE;X NUMBER;Z NUMBER;END;
2 /

Package created.


Session 1:
SQL> exec P.X := 2

PL/SQL procedure successfully completed.


Oh yes!

I just install Oracle Enterprise Linux on my new notebook.

I wanted to check how far could I improve the performance of a count(*)


SQL> drop table big_emp;

table big_emp dropped.
258ms elapsed

SQL> create table big_emp as
with l as(select 1 from dual connect by level<=3000) select rownum empno,ename,job,mgr,hiredate,sal,comm,deptno from emp,l,l table big_emp created. 330,390ms elapsed SQL> alter table big_emp add primary key(empno)

table big_emp altered.
481,503ms elapsed

SQL> alter system flush buffer_cache

system flush altered.
2,701ms elapsed

SQL> alter system flush shared_pool
system flush altered.
137ms elapsed

SQL> select count(*) from big_emp
COUNT(*)
----------------------
126000000

9,769ms elapsed

SQL> select count(*) from big_emp
COUNT(*)
----------------------
126000000

8,157ms elapsed

SQL> alter table big_emp drop primary key

table big_emp altered.
905ms elapsed

SQL> alter table big_emp add primary key(empno)
using index (
create index big_i on big_emp(empno)
global partition by hash(empno)
partitions 16 parallel 16)

table big_emp altered.
974,300ms elapsed

SQL> alter system flush buffer_cache

system flush altered.
601ms elapsed

SQL> alter system flush shared_pool

system flush altered.
140ms elapsed

SQL> select count(*) from big_emp

COUNT(*)
----------------------
126000000

5,201ms elapsed

SQL> select count(*) from big_emp

COUNT(*)
----------------------
126000000

2,958ms elapsed


As it is on a notebook, I suppose the benefit of partitioning is not as good as you could get on your server with lots of fast disks and lot’s of CPUs, but I am pretty happy with the results.

It is still counting 126 Million rows in less than 3 seconds 🙂

Thanks for the very kind sponsor of the notebook !

Oracle buys Sun was an exciting accouncement 20 months ago.

What did change in the Solaris/Oracle Database world?

First, Oracle delivered Exadata on Sun Hardware (x86_64).
Second, they delivered Exadata on Sun Solaris Operating System (x86_64).

But now, they announced a combination of software and hardware that will run Oracle database faster than anything ever before.

I am happy to read Oracle is still investing on R&D on the Sparc processors server line !

This Toad 11 beta new feature made me so happy !

Not only you can specify a color for your connection (ex: red=prod, green=devl), and this color is much more visible than it was in previous version of TOAD, but now you can set your connection to be read-only.

For any reason, bored, ill, under pressure, tired, you may screw up your database one of those days.

I feel so good that I can now setup my prod connection to be read-only. It is REALLY cool.

Setting the connection read-only is a TOAD feature. As it is in beta it may not be 100% bullet-proof. Mostly it allows you to do only SELECT in the editor, even if you are logged in as SYSTEM or SYS.

Anything comparable in SQL Developer?

I wrote about the new defaults in 10gR2 more than 5 years ago
FAILED_LOGIN_ATTEMPTS default to 10 in 10gR2

This new DEFAULT increases the security by preventing the users from trying millions of different passwords. However the locking of application users is noticeably decreasing the database availability 🙁

My recommendation :
– create a separate profile for the application users with high availabilty requirement with a default of 1000
– add 2 characters to the password of those accounts


SQL> create user u identified by a4sec2pw;
User created.
SQL> grant create session to u;
Grant succeeded.

“U” is a critical user in your application where account locking would mean downtime !

Let’s try to make the schema 10 times more secure and 100 times more available :

SQL> create profile failed1000 limit failed_login_attempts 1000;
Profile created.
SQL> alter user u identified by a4sec2pwx1 profile failed1000;
User altered.


OK?

A user mentioned one call is faster in test than in prod. And the table has the same size. How could we tune the production to make it quick?


user007@PROD> select count(*) from t;
COUNT(*)
----------
19832501

Elapsed: 00:03:05.00


Let’s try in test :

user007@TEST> select count(*) from t
select count(*) from t
*
ERROR at line 1:
ORA-00942: table or view does not exist

Elapsed: 00:00:00.16


Wow, 00:00:00.16! This is a much quicker response time. Probably the ORA-942 was catched by the application and the user did not realise it…

Next time someone ask you to tune a count(*), just revoke access to the table to optimize the response time 😈

patch 10098816 is available for download

I just wrote one of my first powershell script yesterday, it has a pretty nice syntax actually, and no need to download anything like cygwin or other unix-like shell to your PC.

PS> $stmt = "set hea off`n"
PS> $stmt += "select 'hello world' from dual;"
PS> $res = ($stmt | sqlplus -s scott/tiger)
PS> $res

hello world


Pretty cool 🙂

Historically and semantically, SQL Developer is a developer tool. There is very little comparison with TOAD for the dba.

But… Oracle is working on this! In the latest release, 3.0 EA1, you will be able to see Tablespaces, Redo logs and controlfile, finally.

Still no session browser, but I am delighted that the database administrators are not getting forgotten !

Thanks Surachart for spreading the news!

I already wrote about jdbc hello world and listener with tcps.

Let’s combine both technologies !
TCPS.java
import java.util.Properties;
import java.security.Security;
import java.sql.*;
import javax.net.ssl.*;

public class TCPS {
public static void main(String argv[]) throws SQLException {
String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(Host=dbsrv001)(Port=12345))(CONNECT_DATA=(SID=DB01)))";
Properties props = new Properties();
props.setProperty("user", "scott");
props.setProperty("password", "tiger");
props.setProperty("javax.net.ssl.trustStore","cwallet.sso");
props.setProperty("javax.net.ssl.trustStoreType","SSO");
Security.addProvider(new oracle.security.pki.OraclePKIProvider());
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
Connection conn = DriverManager.getConnection(url, props);
ResultSet res = conn.
prepareCall("select 'Hello SSL World' txt from dual").
executeQuery();
res.next();
System.out.println(res.getString("TXT"));
}
}


I have an auto-login wallet (cwallet.sso) with the trusted certificate from the server.

There are a few jar’s to use:

$ CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc5.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/jlib/oraclepki.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/jlib/osdt_cert.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/jlib/osdt_core.jar
$ CLASSPATH=$CLASSPATH:.
$ export CLASSPATH
$ javac TCPS.java
$ java TCPS
Hello SSL World


Greatly inspired by Jean de Lavarene’s white paper : SSL With Oracle JDBC Thin Driver

Pretty straightforward, check if dbms_transaction.step_id is null!

SQL> select dbms_transaction.step_id from dual;

STEP_ID
---------------

SQL> insert into t values (1);

1 row created.

SQL> select dbms_transaction.step_id from dual;

STEP_ID
---------------
114352430549782

SQL> commit;

Commit complete.

SQL> select dbms_transaction.step_id from dual;

STEP_ID
---------------

SQL> insert into t values (2);

1 row created.

SQL> select dbms_transaction.step_id from dual;

STEP_ID
---------------
124248035235852

SQL> rollback;

Rollback complete.

SQL> select dbms_transaction.step_id from dual;

STEP_ID
---------------

I just started a new job as a dba and placed some books on my desk.

Applied Mathematics for Database Professionals by Lex de Haan and Toon Koppelaars
SQL Design Patterns by Vadim Tropashko
Beginning Oracle Database 11g Administration by Iggy Fernandez
Expert Oracle Database Architecture by Tom Kyte
Oracle 11g New Features by Brian Carr, John Garmany, V. J. Jain, Steve Karam and Lutz Hartmann
Advanced Oracle SQL Programming my book…

I have not worked as an 11g dba before, I will surely learn a lot in those books (and giveaway my book) !

I have read on Surachart Opun’s blog that 11.2.0.2 is available, but also that the patchset is now a full installation.

In the past, if you wanted to have 9.2.0.8 or 10.2.0.5, you needed to install both the base version, 9.2.0.1 or 10.2.0.1 and the patchset, 9.2.0.8 or 10.2.0.5. Very often the patchset itself was bigger and took more time than the base database installation.
According to Note 1189783.1, it is now possible to install 11.2.0.2 without having to install both 11.2.0.1 + a patchset.

if you are installing Oracle Database on a system without an existing Oracle Database installation, then you can simply install the release 11.2.0.2 patch set

I like this 🙂

I was reading http://blogs.oracle.com/myoraclediary and there was a command about printing the modification details of a file.

In Linux / Cygwin “stat” exists as a command

$ stat /etc/hosts
Access: 2010-08-25 15:20:49.782522200 +0200
Modify: 2010-08-18 14:04:25.868114200 +0200
Change: 2010-08-18 14:04:26.072413100 +0200

Or use the one-liner perl below

### st_atime; /* Time of last access */
$ perl -e 'use POSIX;[-f"/etc/hosts"]&&print ctime((stat(_))[8])'
Wed Aug 25 15:20:08 2010
### st_mtime; /* Time of last data modification */
$ perl -e 'use POSIX;[-f"/etc/hosts"]&&print ctime((stat(_))[9])'
Wed Jun 10 11:36:40 2009
### st_ctime; /* Time of last file status change */
$ perl -e 'use POSIX;[-f"/etc/hosts"]&&print ctime((stat(_))[10])'
Wed Aug 25 01:00:07 2010


I read Paul Sorensen blog : Sun certifications will be renamed next week.

For instance the Sun Certified Network Administrator will be Oracle Certified Expert, Oracle Solaris 10 Network Administrator.

But to get the OCE Solaris title, you need to upgrade your certification […] to receive an Oracle certification title (check more details on the Oracle Certification website here).

I never upgraded my Sun credentials, I am a Solaris 2.6 system admin and Solaris 7 network admin, but this equivalency with OCE surprised me.

There are even two OCM Java certifications : Oracle Certified Master, Java EE 5 Enterprise Architect, originally Sun Certified Enterprise Architect (SCEA) and Oracle Certified Master, Java SE6 Developer, originally Sun Certified Java Developer (SCJD)

Also, since a few months, there is an upgrade to the 9i OCM DBA certification : 11g OCM Upgrade. I hope I can do this one 🙂

In one of my script, I am checking at the end for any ORA- error. And if I have any ORA- error, I quit with an error.

So far so good.

Also when I run a report from the shell, I do set the sqlplus settings I like and I expect the script to receive the output with no ORA- error at the beginning.

But watch this !

$ echo "set feed off hea off newp none
conn scott/tiger
select * from dual;"|sqlplus /nolog
ERROR:
ORA-28002: the password will expire within 10 days

X

My password will expire in 10 days. And this screw up my day 🙁

I see TRUNC(SYSDATE) in the code very often. Is this good or bad?

Over my past two years as a developer, I can tell you it is pretty bad. What does TRUNC(SYSDATE) mean? It is today’s date.

But when does today starts and ends? And at the time of the writing, it is already tomorrow in Thailand.

We could argue about using CURRENT_DATE instead of sysdate, but it does not solve everything. Maybe you want your day to end at 10:30pm and initialize for the next day after some post processing. Maybe you want some components running in different timezones. Maybe you want to rollback and rollforward the date in your test systems…

Also the bad news, if your report hardcodes SYSDATE, kind of SELECT SUM(AMT) FROM T WHERE EXPIRY_DATE>TRUNC(SYSDATE), you will not be able to run it tomorrow in case it fails today.

No, I think that you would better store the date in a table and update it daily.

So you will have SELECT SUM(T.AMT) FROM T, TRADINGDAY WHERE T.EXPIRY_DATE>TRADINGDAY.TRADINGDAY and a daily job running UPDATE TRADEDAY SET TRADINGDAY=TRADINGDAY+1;.

You may want to consider the performance overhead of reading from a table instead of using the very performant SYSDATE function…

If you want to start with developing free software on old technology, you can download the Oracle Database Express Edition.

Actually, Oracle 10g is more than 5 years old and it is the only version available as Express Edition, 11g has not been released at the time of the writing, and no patch has been released, not even for the worst bugs or security breaches.

Most probably you will not want to run this for your sensitive data.

If you are serious about Oracle Development, you could download the Enterprise Edition for free, and get the Personal Edition on Oracle Shop for support and patches.

Last Friday 13th August, a news/rumor (not an official announcement) was posted on the opensolaris forum, apparently OpenSolaris has been cancelled in favor of an Oracle Solaris 11 Express. Not sure if it will come in the same format as the database XE, but this does not sound to please the community.

After the Oracle sues Google, it does not make Oracle very popular in the opensource community at the very moment

YMMV

First surprise, after migration, the size of my subversion folder is double the size of my cvs folder. With a bunch of 2Gb disks shared amoung dozens of unix persons, and regular reminders the current usage reached 100%, you will feel the pain of having each developers doublesizing its home directory…
The reason is a .svn/test-base directory containing a duplicate of your local copy.
The benefit there is that it reduces network usage, for instance when diffing with the working version.

Second surprise, not sure if it is a generic issue or related to the one above, a full checkout take ages 🙁 .

svn and cvs command line interfaces almost have the same options, a bit like vi and vim -vi is my favorite- but the “tagging” massively differs. The is no cvs tag command in subversion, you use a different syntax.
cvs :

$ cd /home/lsc/CVS/proj1/source/pkg
$ cvs tag MYTAG helloworld.pkb helloworld.pks

now enjoy the power of subversion! [updated as I found the –parents option]

$ cd /home/lsc/SVN/proj1/source/pkg
$ svn copy --parents helloworld.pkb http://myserver.domain.com/svn/REPOS1/proj1/tags/MYTAG/proj1/source/pkg -m "my first svn tag"
$ svn copy --parents helloworld.pks http://myserver.domain.com/svn/REPOS1/proj1/tags/MYTAG/proj1/source/pkg -m "my first svn tag"


I always loved the cvs rename command
$ cvs rename pkg1.pkb pkg2.pkb
cvs [rename aborted]: Remote server does not support rename

Joke apart, in CVS I used to physically logon to the server and manually move the pkg1.pkb,v to pkg2.pkb,v
Renaming works in subversion :

$ svn move pkg1.pkb pkg2.pkb
A pkg2.pkb
D pkg1.pkb


Update: One more annoyance, you cannot checkout a single file 🙁
subversion faq

I just discovered this morning this cool utility in my /bin directory : xmllint

You can use it to extract values from your xml files within your shell scripts


$ cat foo.xml


John


Jack


$ echo 'cat //emplist/emp[@no="1"]/ename/text()'|
xmllint --shell foo.xml |
sed -n 3p
John

I like this !