Categories
dba news support

future release dates of 11gR2

HPUX Itanium, Sun Sparc/x86_64, AIX –> october-december 2009
Windows, HPUX Risc –> april-june 2010
Ref: Release Schedule of Current Database Patch Sets

Dates will change, keep an eye on the schedule, and do not base your business on expected dates ! Please be patient 🙂

Categories
dba installation news

11.1.0.7 is the latest patchset for 11gR1

You will get CPU and even PSU (Patch Set Updates are proactive cumulative patches comprised of recommended bug fixes that are released on a regular and predictable schedule)

But no more patchset. This the first time I see a release with only 1 pachset.

Well, if you use Linux, you can go to 11gR2. Otherwise you may better stick to 10.2.0.4 for a few more months

Ref: 742060.1
11.1.0.7 is the last patch set for Release 11.1

Categories
dba security

CONNECT no longer has CREATE VIEW, what’s next?

In my current project we are going to move from 9i to 10g really soon. One of the typical issue is to get the right privileges, because the schema owners typically had only CONNECT and RESOURCE, and that does no longer include CREATE VIEW in 10gR2.

I was just reading the 11gR2 readme today :
7.2 UNLIMITED TABLESPACE Privilege Changes
The UNLIMITED TABLESPACE system privilege will be removed from the RESOURCE role in a future Oracle Database release (reference Bug 7614645).

So, probably I should ask for TABLESPACE quotas before we go to 12g 😉

Categories
dba sql

How to tune WHERE NAME LIKE ‘%ABC%’

More than once customers wanted me to tune queries where the LIKE clause made the query very slow…

The easy answer is : you cannot. If you want to search for some characters in the middle of the string, Oracle will not use an index.

Is it a correct answer? Maybe not.

Let’s imagine my application allow you to search for a string in the middle of the name, but to avoid scanning too large amount of data, the application enforces the string to be at least 3 characters long. For example ABC.

In this case, instead of doing a full table scan of the table to retrieve only a few rows, we can use an hint to tell Oracle to use an index on the name:

CREATE TABLE lsc_t AS
SELECT
ROWNUM ID,
SUBSTR(DBMS_RANDOM.STRING ('n', DBMS_RANDOM.VALUE (2, 8))
|| ' '
|| DBMS_RANDOM.STRING ('n', DBMS_RANDOM.VALUE (2, 8))
,1,17) NAME,
TRUNC(SYSDATE-ABS(10000*DBMS_RANDOM.NORMAL)) birthdate,
LPAD('X',4000,'X') address
FROM DUAL
CONNECT BY LEVEL <= 1e5; ALTER TABLE lsc_t ADD PRIMARY KEY(ID); CREATE INDEX lsc_i ON lsc_t(NAME); EXEC dbms_stats.gather_table_stats(user,'LSC_T',cascade=>true)

let’s measure the time for a full table scan
SQL> set timi on
SQL> SELECT ID, NAME, birthdate
FROM lsc_t WHERE NAME LIKE '%ABC%';

ID NAME BIRTHDATE
--------- ----------------- ---------
60249 ABCBIFAB KRKBCRN 11-MAR-90
16714 AF YABCG 09-OCT-95
55571 BABCIQ GESGLW 27-MAR-50
77561 BP GABC 24-APR-90
80027 DALSABC TZLOAWDV 05-NOV-01
49817 EABCTFIY XWB 10-FEB-88
23283 EMMOGGBF DABCB 20-DEC-87
39530 FMABCKB AB 18-SEP-87
68605 FTPGOHE ABCC 28-SEP-28
74615 KIFDWABC CSSUQ 08-AUG-82
31772 KNOABCT BO 08-SEP-77
68730 KRYIEN LMABC 10-APR-07
43317 LUFJKZJT AUABCZR 19-DEC-88
76851 MZABC TEIFG 14-SEP-92
54589 NXE YABCDX 03-MAY-88
6940 OIWABCZ DLFFXY 29-MAR-88
59070 ONIB ADGABCI 29-JUL-07
27264 PGHOABC ZY 05-OCT-90
38157 QABC OPZHE 13-JUN-87
17511 QPDKD CIABCJ 08-AUG-69
25507 RX OWULOABC 24-FEB-92
62159 SEABC DAILK 25-JUN-02
3845 SK CCABCG 22-JAN-80
50059 SPABC BVHRHW 18-MAR-86
54700 UABCPC WUHAJS 28-OCT-71
70207 UKY OIDUABC 23-APR-88
39484 WABC TJLYHVJZ 14-MAR-78
14561 WDRWABC XZKDH 29-MAR-86
61501 YBYU RYABCGI 28-JUN-78
30578 YEWENGX ABCHARA 12-SEP-67
35397 YHBEABC HFKO 25-AUG-85
26450 YOABCVG HJT 23-DEC-98
87224 ZKNLNY YAABC 13-NOV-61

33 rows selected.

Elapsed: 00:00:02.56

about 3 seconds for retrieving 33 rows out of 100000

let’s try with an index

SQL> SELECT /*+INDEX(LSC_T,LSC_I)*/ ID, NAME, birthdate
FROM lsc_t WHERE NAME LIKE '%ABC%';

ID NAME BIRTHDATE
--------- ----------------- ---------
60249 ABCBIFAB KRKBCRN 11-MAR-90
16714 AF YABCG 09-OCT-95
55571 BABCIQ GESGLW 27-MAR-50
77561 BP GABC 24-APR-90
80027 DALSABC TZLOAWDV 05-NOV-01
49817 EABCTFIY XWB 10-FEB-88
23283 EMMOGGBF DABCB 20-DEC-87
39530 FMABCKB AB 18-SEP-87
68605 FTPGOHE ABCC 28-SEP-28
74615 KIFDWABC CSSUQ 08-AUG-82
31772 KNOABCT BO 08-SEP-77
68730 KRYIEN LMABC 10-APR-07
43317 LUFJKZJT AUABCZR 19-DEC-88
76851 MZABC TEIFG 14-SEP-92
54589 NXE YABCDX 03-MAY-88
6940 OIWABCZ DLFFXY 29-MAR-88
59070 ONIB ADGABCI 29-JUL-07
27264 PGHOABC ZY 05-OCT-90
38157 QABC OPZHE 13-JUN-87
17511 QPDKD CIABCJ 08-AUG-69
25507 RX OWULOABC 24-FEB-92
62159 SEABC DAILK 25-JUN-02
3845 SK CCABCG 22-JAN-80
50059 SPABC BVHRHW 18-MAR-86
54700 UABCPC WUHAJS 28-OCT-71
70207 UKY OIDUABC 23-APR-88
39484 WABC TJLYHVJZ 14-MAR-78
14561 WDRWABC XZKDH 29-MAR-86
61501 YBYU RYABCGI 28-JUN-78
30578 YEWENGX ABCHARA 12-SEP-67
35397 YHBEABC HFKO 25-AUG-85
26450 YOABCVG HJT 23-DEC-98
87224 ZKNLNY YAABC 13-NOV-61

33 rows selected.

Elapsed: 00:00:00.06

Much better 🙂

reposted due to % in url

Categories
dba sql

on recycle bin

more than one user may wondered who created those BIN$ when they first connected to a 10g database.


create table lsc_t(x number)
partition by range(x)
(partition LESS_THAN_ONE values less than (1));

drop table lsc_t;

select object_name, subobject_name, created
from user_objects
where object_name like 'BIN$%';
OBJECT_NAME SUBOBJECT_NAME CREATED
------------------------------ --------------- ---------
BIN$bh2VJ6FqFJ3gRAAUT+rFpg==$0 LESS_THAN_ONE 07-JUL-09

Ok, it is quite easy to get rid of it. Either at DROP time with a DROP TABLE LSC_T PURGE or later with PURGE RECYCLEBIN. Most of the objects disappear from USER_OBJECTS when dropped actually. The recyclebin view is called : RECYCLEBIN.


purge recyclebin;

select object_name, subobject_name, created
from user_objects
where object_name like 'BIN$%';

no rows selected.

select * from recyclebin;

no rows selected.

So far so good…

Let’s see what’s happening with my primary keys

purge recyclebin;
create table lsc_t(x number constraint lsc_t_pk primary key);
drop table lsc_t;
select object_name, original_name, type from recyclebin;

OBJECT_NAME ORIGINAL_NAME TYPE
------------------------------ ------------- -----
BIN$bh23ggtBHALgRAAUT+rFpg==$0 LSC_T TABLE
BIN$bh23ggtAHALgRAAUT+rFpg==$0 LSC_T_PK INDEX

The primary key index is now in the recycle bin as well.

let’s recover the recycle bin version :

flashback table lsc_t to before drop;
select index_name from user_indexes where table_name='LSC_T';

INDEX_NAME
------------------------------
BIN$bh3GgNi1HR3gRAAUT+rFpg==$0

select object_name, original_name, type from recyclebin;

no rows selected.

👿

So if you cannot exclude BIN$ objects from your dba maintenance scripts, you will need to deal with thoses as they may be recovered indexes!

Categories
dba unix

How to reuse connection in shell

It is Friday, I wanted to give my readers some stuff for the week-end 😉

Imagine that piece of code :

countlines() {
c=$(sqlplus -s /nolog <

I can run this

time ./script1
there are 14 lines in EMP
there are 4 lines in DEPT

real 0m0.46s
user 0m0.06s
sys 0m0.09s

Sounds like a regular shell script. How could we optimize the countlines function? Well, we could create the connection only once and use coprocess pipe (with |& that is pipe ampersand)

sqlplus -s /nolog |&

print -p "connect scott/tiger"

read -p line
if [ $line != Connected. ]
then
exit 1
fi

print -p "set feed off head off"

countlines() {
print -p "select count(*) from $1;"
read -p c
echo "there is $c lines in $1"
}

countlines EMP
countlines DEPT

print -p disconnect

A two-ways pipe is opened with sqlplus. There is only one connect, and one disconnect.

Let's check the performance :

$ time ./script2
there is 14 lines in EMP
there is 4 lines in DEPT

real 0m0.23s
user 0m0.00s
sys 0m0.01s

About twice as fast! Note the "Connected" output may not exist in recent version of sqlplus in silent mode. If you have a script that generates hundreds of connections, or which create a connection every 5 seconds or so, think about it 🙂

Enjoy your week-end

Categories
dba linux unix

return code before grep

In my previous post hide-password-from-ps-output-sql-loader I mentioned a way to pass the password to the loader thru a parameter file. As correctly suggested by Brian Tkatch, the password could be passed as standard input

sqlldr control=x.ctl silent=header,feedback <

The Username: prompt is displayed 🙁   🙁

How do we get rid of this ?

sqlldr control=x.ctl silent=header,feedback <

There is no output. But what's the error code
echo $?
1

The return code is 1 🙁

This is not the error code from sqlldr, but the error code from grep !

Ok, here is the trick, a bit cryptic if you are not familiar with file descriptors

( ( (sqlldr control=x <&3) |grep -v "^Username:" >&4 ) 3>&1 |(read x;exit $x) )4>&1
scott/tiger
EOF
echo $?
0

The return code is 0 🙂

Categories
dba

Hide password from ps output : sql loader

By reporting the process status with ps, any Unix user will see the command line arguments

ps -ef
UID PID PPID C STIME TTY TIME CMD
lsc 13837 13825 0 May 11 pts/17 0:01 -ksh
oracle 4698 6294 0 12:00:40 ? 0:00 sqlplus -s system/manager
appluser 4229 4062 0 12:00:03 ? 0:00 sqlldr scott/tiger
applrun0 28445 28281 0 11:54:03 ? 0:00 imp king/gold full=y
...

What you see here above is definitely a security issue. For sqlplus, the trick is to use sqlplus /nolog and then pass connect system/manager as input or script.

For sqlldr (and exp/imp etc…), the trick is to use a parameter file.

To make it as safe as possible, the file must be unique, readable only for owner and removed after usage.

Ex:
umask 0077
TMPFILE=$(mktemp)
echo "userid=scott/tiger" >$TMPFILE
sqlldr parfile=$TMPFILE control=x.ctl silent=header,feedback
rm $TMPFILE

mktemp is an Unix utility that creates temp files with unique names.

Categories
dba plsql

ora-984 and sqlerrm

What’s wrong with this code ?

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

SQL> drop table lsc_t ;

Table dropped.

SQL> create table lsc_t (x varchar2(255), y date);

Table created.

SQL> begin
2 null;
3 exception when others then
4 insert into lsc_t (x,y)
5 values (sqlerrm, sysdate);
6 end;
7 /

PL/SQL procedure successfully completed.

I was in the process of migrating a database with 10g and this piece of code became invalid 🙁

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> drop table lsc_t ;

Table dropped.

SQL> create table lsc_t (x varchar2(255), y date);

Table created.

SQL> begin
2 null;
3 exception when others then
4 insert into lsc_t (x,y)
5 values (sqlerrm, sysdate);
6 end;
7 /
values (sqlerrm, sysdate);
*
ERROR at line 5:
ORA-06550: line 5, column 17:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

SQL>

Ok, the code is wrong. It has been running for half a decade in production but the exception was never reached.

Note it is a bug, and putting sqlerrm after sysdate would have generate the exception even in 9i

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

SQL> begin
2 null;
3 exception when others then
4 insert into lsc_t (y,x)
5 values (sysdate,sqlerrm);
6 end;
7 /
values (sysdate,sqlerrm);
*
ERROR at line 5:
ORA-06550: line 5, column 25:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

Just having fun with 10g migration 😉

Categories
dba security

grant access to trace files

As a developer, you sometimes need to contact your dba to get an user trace. As a dba, sending trace files to developer is not much fun.

But how do you get access to your traces without any dba ?

One way could be to set _trace_files_public=true, but this is bad. It is undocumented, it is unsupported, there is no control to who can access the files, and probably it will not help if you have no access to the database server.

I prefer to provide a function that reads from the trace directory.

Ok, here we go :

CREATE DIRECTORY user_dump_dest AS
'/app/oracle/admin/DB01/udump';

CREATE OR REPLACE FUNCTION get_tracefile (file_name VARCHAR2)
RETURN VARCHAR2
IS
dest_loc CLOB;
src_loc BFILE;
ret VARCHAR2 (4000);
BEGIN
src_loc := BFILENAME ('USER_DUMP_DEST', file_name);
DBMS_LOB.OPEN (src_loc, DBMS_LOB.lob_readonly);
DBMS_LOB.createtemporary (dest_loc, TRUE);
DBMS_LOB.loadfromfile (dest_loc, src_loc, 4000);
ret := DBMS_LOB.SUBSTR (dest_loc, 4000);
DBMS_LOB.CLOSE (src_loc);
RETURN ret;
END;
/

Just a small function that returns the first 4000 characters of the trace file. I could then grant execute on that function to the developers.

it works quite well
SELECT get_tracefile ('db01_ora_6224.trc')
FROM DUAL;
GET_TRACEFILE('DB01_ORA_6224.TRC')
----------------------------------------------------------------------
/app/oracle/admin/DB01/udump/db01_ora_6224.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /app/oracle/product/dbms/DB01
System name: SunOS
Node name: dbsrv01
Release: 5.8
Version: Generic_117000-05
Machine: sun4u
Instance name: DB01
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 6224, image: oracle@dbsrv01 (TNS V1-V3)

*** SESSION ID:(273.54591) 2009-04-27 12:13:57.292
*** 2009-04-27 12:13:57.292
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [17069], [0x3EED05050],
[], [], [], [], [], []
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
Current SQL statement for this session:
alter PACKAGE "LSC_PKG" compile body
----- PL/SQL Call Stack -----
...

I should mention than granting access to trace files is a security issue as a hacker could dump some security modules. But it will be fine for your trusted developers.

Categories
dba sql

package version control

Oracle does not offer any kind of version control like CVS or subversion in the database. CVS and subversion could be used in the filesystem, then the code could be deployed with sqlplus.

To quickly compare packages in Test and Production I used :


select
env,name,type,line,text
from (
select
env,name,type,line,text,
count(distinct text) over(partition by name,type,line) c
from (
select
'TEST' env,name,type,line,text
from
user_source
union all
select
'PROD' env,name,type,line,text
from
user_source@prod
)
)
where c>1 ;

where “PROD” is a database link to production database. My security friends will scream to hear I have a database link to production in my test schema, but you are free to do it the other way round :mrgreen:

I also explained TOAD method in how to compare schema a few months ago.

Categories
dba security sql

track DDL changes

Why is my package being invalidated? This is the question I asked myself a few times those days. In order to find out what is happening in my schema, I created this simple DDL trigger which tracks all DDL changes in my schema.


CREATE TABLE AUDIT_DDL (
d date,
OSUSER varchar2(255),
CURRENT_USER varchar2(255),
HOST varchar2(255),
TERMINAL varchar2(255),
owner varchar2(30),
type varchar2(30),
name varchar2(30),
sysevent varchar2(30),
sql_txt varchar2(4000));

create or replace trigger audit_ddl_trg after ddl on schema
declare
sql_text ora_name_list_t;
stmt VARCHAR2(4000) := '';
n number;
begin
if (ora_sysevent='TRUNCATE')
then
null;
else
n:=ora_sql_txt(sql_text);
for i in 1..n
loop
stmt:=substr(stmt||sql_text(i),1,4000);
end loop;
insert into audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent,sql_txt)
values(
sysdate,
sys_context('USERENV','OS_USER') ,
sys_context('USERENV','CURRENT_USER') ,
sys_context('USERENV','HOST') ,
sys_context('USERENV','TERMINAL') ,
ora_dict_obj_owner,
ora_dict_obj_type,
ora_dict_obj_name,
ora_sysevent,
stmt
);
end if;
end;
/

Thanks Yas for your comment, I added the SQL text

Categories
dba fun sql xml

high cost

What’s wrong with this query ?


select
(t6.column_value).getstringval() t6
from
table(xmlsequence(extract(xmltype(‘<x/>’),’/x’))) t1,
table(xmlsequence(t1.column_value))t2,
table(xmlsequence(t2.column_value))t3,
table(xmlsequence(t3.column_value))t4,
table(xmlsequence(t4.column_value))t5,
table(xmlsequence(t5.column_value))t6;
T6
————————
<x/>

Elapsed: 00:00:00.01

Well, let’s check the plan :

--------------------------------------------------------------------
| Id | Operation | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | 18E| 15E| 18E (0)|999:59:59 |
| 1 | NESTED LOOPS | 18E| 15E| 18E (0)|999:59:59 |
| 2 | NESTED LOOPS | 18E| 15E| 99P (3)|999:59:59 |
| 3 | NESTED LOOPS | 4451T| 31P| 12T (3)|999:59:59 |
| 4 | NESTED LOOPS | 544G| 3045G| 1490M (3)|999:59:59 |
| 5 | NESTED LOOPS | 66M| 254M| 182K (3)| 00:36:31 |
| 6 | COLLECTION I | | | | |
| 7 | COLLECTION I | | | | |
| 8 | COLLECTION IT | | | | |
| 9 | COLLECTION ITE | | | | |
| 10 | COLLECTION ITER | | | | |
| 11 | COLLECTION ITERA | | | | |
--------------------------------------------------------------------

It is returning 18 quadrillions of rows, 15 exabytes, the cost is 1.8E19 and the time is about one month :mrgreen:

Categories
Blogroll dba sql

Stored outlines

Note:
Performance Tuning Guide
Stored outlines will be desupported in a future release in favor of SQL plan management. In Oracle Database 11g Release 1 (11.1), stored outlines continue to function as in past releases. However, Oracle strongly recommends that you use SQL plan management for new applications. SQL plan management creates SQL plan baselines, which offer superior SQL performance and stability compared with stored outlines.


This said, let’s take a small example. If you have a query which is running fast most of the time and sometimes is running very slow due an unexpected plan change, you may want to considering enforcing plan stability with a Stored Outline.

To fake this example, let’s try to enforce a full table scan for select empno from emp where ename=’SCOTT’.

SQL> set autot on exp
SQL> select empno from emp where ename='SCOTT';
EMPNO
----------
7788

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1
1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 By

For the purpose of this exercice, I consider this to be the right plan and I want to enforce Oracle to use this plan for this query.


SQL> create or replace outline o for category emp_scott on
select empno from emp where ename='SCOTT';

Outline created.

SQL> create unique index i on emp(ename)
tablespace my_slow_tape_drive;

Index created.

SQL> set timi on
SQL> set autot on exp
SQL> select empno from emp where ename='SCOTT';
EMPNO
----------
7788

Elapsed: 01:45:59.95

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2
2 1 INDEX (UNIQUE SCAN) OF 'I' (UNIQUE) (Cost=1

Oracle uses an index scan, but the index is located on a tape (which is not possible on 11gR1 and older 😉 ) and it takes ages to complete the query. Let’s try to use the good plan that was used at the time we created the stored outline


SQL> alter session set use_stored_outlines=emp_scott;

Session altered.

Elapsed: 00:00:00.00
SQL> select empno from emp where ename='SCOTT';
EMPNO
----------
7788

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1
1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 By

SQL> SELECT LAST_LOAD_TIME,OUTLINE_CATEGORY,sql_text
from v$sql
where sql_text like 'select%SCOTT%';
LAST_LOAD_TIME OUTLINE_C SQL_TEXT
------------------- --------- ----------------------------
2008-10-16/09:42:30 select empno from emp where
2008-10-16/09:46:50 EMP_SCOTT select empno from emp where

The plan using the outline is now used

Categories
Blogroll dba sql

where is my database link listed?


$ sqlplus scott/tiger@DEVL
SQL> select * from all_db_links;
no rows selected
SQL> select * from dual@PROD;
D
-
X

Hey, why does this work???

Ok, after some research I found out that this seems to be an implicit loopback database link. The fact that the DEVL database has the global name set to PROD is just to annoy more 😉

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------
PROD.LAURENTSCHNEIDER.COM

Categories
11g Add new tag dba installation support

11g release 1 patchset 1

I just notice on Sven Blog that 11.1.0.7 is available. I have recently upgraded my connection at home so it took a bit less than half an hour to download this 1.5G patchset


$ wget -O p6890831_111070_Linux-x86-64.zip http://oracle-updates.oracle…
–10:17:40– http://oracle-updates.oracle.com/ARUConnect/p6890831_111070_Linux-x86-64..
Resolving oracle-updates.oracle.com… 87.248.199.23, 87.248.199.24
Connecting to oracle-updates.oracle.com|87.248.199.23|:80… connected.
HTTP request sent, awaiting response… 200 OK
Length: 1,613,366,248 (1.5G) [application/zip]

100%[==================================>] 1,613,366,248 891.73K/s

10:47:31 (879.89 KB/s) – `p6890831_111070_Linux-x86-64.zip’ saved [1613366248/1613366248]

$ unzip p6890831_111070_Linux-x86-64.zip
$ cd Disk1
$ ./runInstaller
Starting Oracle Universal Installer…
Preparing to launch Oracle Universal Installer from
Oracle Universal Installer, Version 11.1.0.7.0 Production
Copyright (C) 1999, 2008, Oracle. All rights reserved.

$ su –
root’s password:
# /u00/app/oracle/product/11.1/db_1/root.sh
Running Oracle 11g root.sh script…
# exit
$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 – Production on Sat Sep 20 11:10:35 2008

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

Connected to an idle instance.

SQL> startup quiet migrate
ORACLE instance started.
Database mounted.
Database opened.
SQL> set time on
11:42:21 SQL> sho parameter sga_target

NAME TYPE VALUE
———————————— ———– ——————————
sga_target big integer 200M
11:42:25 SQL> alter system set sga_target=300m scope=spfile;

System altered.

11:42:28 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
11:42:43 SQL> startup quiet migrate
ORACLE instance started.
Database mounted.
Database opened.
11:43:01 SQL> @?/rdbms/admin/catupgrd

11:54:03 SQL> Rem END catupgrd.sql
11:54:03 SQL> startup
ORACLE instance started.

Total System Global Area 313159680 bytes
Fixed Size 2159272 bytes
Variable Size 226495832 bytes
Database Buffers 79691776 bytes
Redo Buffers 4812800 bytes
Database mounted.
Database opened.
11:56:28 SQL> select comp_name,status,version from dba_registry;

COMP_NAME STATUS VERSION
———————————– ——– ———-
Oracle Ultra Search VALID 11.1.0.7.0
Oracle XML Database VALID 11.1.0.7.0
Oracle Text VALID 11.1.0.7.0
Oracle Expression Filter VALID 11.1.0.7.0
Oracle Rules Manager VALID 11.1.0.7.0
Oracle Workspace Manager VALID 11.1.0.7.0
Oracle Database Catalog Views VALID 11.1.0.7.0
Oracle Database Packages and Types VALID 11.1.0.7.0
JServer JAVA Virtual Machine VALID 11.1.0.7.0
Oracle XDK VALID 11.1.0.7.0
Oracle Database Java Packages VALID 11.1.0.7.0

My first try with 200Mb sga target did not succeeded. But with 300Mb sga target it worked fine.

Categories
11g Blogroll dba sql

About case sensitivity

SQL is a case insensitive language. That means, you can write any SQL statement in uppercase or lowercase.

SQL> SELECT DUMMY FROM DUAL;
D
-
X

SQL> select dummy from dual;
D
-
X

Some formatters like Toad and some courseware like Skillsoft E-Learning advise to use UPPERCASE for commands and lowercase for tables and columns.


/* Formatted on 2008/09/19 10:00
(Formatter Plus v4.8.8) */
SELECT dummy
FROM DUAL;

Well, Toad decided to put DUAL in uppercase. Anyway, this is no authority, define your own guideline and stick to it. If you use TOAD a lot, it is maybe OK to use lowercase for non-keywords.

The column dummy is actually the uppercase “DUMMY” column of “DUAL”. Putting double quotes around a column of table makes it case sensitive, so “DUMMY” is not “dummy”.

SQL> select "dummy" from "dual";
select "dummy" from "dual"
*
Error at line 1
ORA-00942: table or view does not exist

SQL> SELECT "DUMMY" FROM "DUAL";
D
-
X

OK, something very bad about Toad formatter is (at least the version I am using) that it considers unquoted password to be case insensitive. Which is wrong. The password, in 11g, is case sensitive, even when not in double quotes. Therefore, be sure to put the right case when creating scripts and do not format your CREATE USER statements with Toad or use double quotes for disambiguation!

SQL> ALTER USER scott IDENTIFIED BY BIG;
User altered.
SQL> connect scott/BIG
Connected.
SQL> /* Formatted on 2008/09/19 10:17 (Formatter Plus v4.8.8) */
SQL> ALTER USER scott IDENTIFIED BY big;
SQL> connect scott/BIG
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

Categories
dba sqlplus

Difference between rollbac and rollback

What is the difference between rollbac and rollback?


SQL> create table t as select 1 x from dual;

Table created.

SQL> update t set x=2;

1 row updated.

SQL> savepoint a;

Savepoint created.

SQL> update t set x=3;

1 row updated.

SQL> rollbac to savepoint a;
Rollback complete.
SQL> select * from t;
X
----------
1

WTF! rollbac does not seem to work correctly 😉

Actually, ROLL, ROLLB, ROLLBA and ROLLBAC are not valid SQL statements. However, SQLPLUS recognizes it a sqlplus statement but this behavior is not documented.


SQL> del *
SQL> roll
Rollback complete.
SQL> list
SP2-0223: No lines in SQL buffer.

The statement does not end with semi-column and is not stored in the SQLPLUS buffer.

So it is not a SQL command.

Categories
Blogroll dba sql sqlplus

return size of to_char

The width of a columns is known before execution.

Well, in most cases…


SELECT TO_CHAR(SYSDATE,'DAY','NLS_DATE_LANGUAGE=FRENCH')
FROM DUAL;
TO_CHAR(
--------
SAMEDI

The length is as most 8 characters (VENDREDI). Therefore the width of the column is 8.


SELECT TO_CHAR(SYSDATE,'YEAR') FROM DUAL;
TO_CHAR(SYSDATE,'YEAR')
------------------------------------------
TWO THOUSAND EIGHT

Oracle thinks the length is at most 42 characters. This is a bad guess, the year 7777 is the longest to spell and is only 27 characters. So the width is 42.

SELECT TO_CHAR(SYSDATE,'JSP') FROM DUAL;
TO_CHAR(SYSDATE,'JSP')
---------------------------------------------------------
---------------------
TWO MILLION FOUR HUNDRED FIFTY-FOUR THOUSAND SIX HUNDRED
EIGHTY-EIGHT

again, this is a bad guess, the maximum length of a spelled Julian day is 77 (May 9, 4388) not 78.

let’s try with spelling the nanoseconds :

select to_char(current_timestamp,'FF9SP') from dual;
TO_CHAR(CURRENT_TIMESTAMP,'FF9SP')
-------------------------------------------------------
-----------------------
SEVENTY-FOUR MILLION

here 78 is a really bad guess… the nanoseconds could be 100 character long !

What happened then :

select to_char(timestamp '0001-01-01 00:00:00.777777777',
'FFSP') from dual;
select to_char(timestamp '0001-01-01 00:00:00.777777777',
*
ERROR at line 1:
ORA-01877: string is too long for internal buffer

The query fails. Does it means the maximum length returned by TO_CHAR is 78? Not at all!

It simply means Oracle sets the width column to something too small to contain the result.

By setting the column to something longer, the query should work…

select cast(to_char(timestamp '0001-01-01 00:00:00.
777777777','FFSP') as varchar2(100)) from dual;
CAST(TO_CHAR(TIMESTAMP'0001-01-0100:00:00.777777777','FFSP')
------------------------------------------------------------
ASVARCHAR2(100))
----------------------------------------
SEVEN HUNDRED SEVENTY-SEVEN MILLION SEVEN HUNDRED SEVENTY-
SEVEN THOUSAND SEVEN HUNDRED SEVENTY-SEVEN

Ok, let’s do something perverse :mrgreen:

Let set the CURSOR_SHARING to FORCE !

Have a look :


SQL> alter session set cursor_sharing='FORCE';

Session altered.

SQL> select to_char(to_date(1000000,'J'),'JSP') from dual;
TO_CHAR(TO_DATE(1000000,'J'),'JSP')
----------------------------------------------------------
-----------------
ONE MILLION

the width of the column is evaluated to 75. 75 however is not the maximum length!


SQL> select to_char(to_date(1777777,'J'),'JSP') from dual;
select to_char(to_date(1777777,'J'),'JSP') from dual
*
ERROR at line 1:
ORA-01801: date format is too long for internal buffer

Not only CURSOR_SHARING=FORCE influence the output of the query, but it also make the SQL failing.

Let’s revert to EXACT for confirmation.

SQL> alter session set cursor_sharing='EXACT';

Session altered.

SQL> select to_char(to_date(1000000,'J'),'JSP') from dual;
TO_CHAR(TO_
-----------
ONE MILLION

SQL> select to_char(to_date(1777777,'J'),'JSP') from dual;
TO_CHAR(TO_DATE(1777777,'J'),'JSP')
--------------------------------------------------------------
--------------
ONE MILLION SEVEN HUNDRED SEVENTY-SEVEN THOUSAND SEVEN HUNDRED
SEVENTY-SEVEN

Categories
Blogroll dba sqlplus

SET LONGCHUNKSIZE

I noticed a side effect of SET LONGC today…

The default setting for LONG and LONGC is 80. This is quite annoying when you SELECT TEXT FROM ALL_VIEWS as it truncates the text to 80.

So why not setting it to the maximum?

Let’s first demo the usage of LINESIZE, LONG and LONGCHUNKSIZE

SQL> create view v as select
'12345678910111213141516171819202' x
from dual;

View created.

SQL> set lin 80 long 20 longc 20
SQL> select text from user_views where view_name='V';

TEXT
--------------------
select '123456789101

SQL> set lin 80 long 1000000000 longc 20
SQL> select text from user_views where view_name='V';

TEXT
--------------------
select '123456789101
11213141516171819202
' x from dual

SQL> set lin 80 long 1000000000 longc 1000000000
SQL> select text from user_views where view_name='V';

TEXT
-----------------------------------------------------
select '12345678910111213141516171819202' x from dual

When I use a LONG setting smaller than the length of the TEXT column, I got it truncated. When I use a huge LONG setting but a LONGCHUNKSIZE setting smaller than the length of the TEXT column, I got it wrapped. When both are huge, it seems I am getting the expecting result. So why not setting SET LONG 2000000000 LONGC 2000000000 in your login.sql ?

Have a look :


SQL> SET LONG 10 LONGC 10 LIN 80 TIMI ON
SQL> SELECT TO_CLOB('X') FROM DUAL;

TO_CLOB('X
----------
X

Elapsed: 00:00:00.01
SQL> SET LONG 1000000 LONGC 1000000 LIN 80 TIMI ON
SQL> SELECT TO_CLOB('X') FROM DUAL;

TO_CLOB('X')
------------
X

Elapsed: 00:00:00.00
SQL> SET LONG 1000000000 LONGC 1000000000 LIN 80 TIMI ON
SQL> SELECT TO_CLOB('X') FROM DUAL;

TO_CLOB('X')
------------
X

Elapsed: 00:00:02.06

2 seconds to select 1 character from dual !

I also tried to set it to 2000000000 but since I do not have 2Gb of free memory my notebook started swapping so badly I could not even move my mouse 🙁

Categories
Blogroll dba vista windows

Start Oracle in Vista with one click

I have been using Vista for about 3 months and finally found a way to start my databases with a single click 🙂

The method I used until yesterday was a .BAT file that starts the services, I had then to right click on the shortcut, run as administrator, confirm the UAC warning. 3 Clicks. Way to much…

I have googled quite a lot and found Vista setuid – How to elevate without prompting

Ok, here we go :

1) run mmc
2) file – add snapin
3) Task Scheduler Library – Create Task
4) Name: startoracle
Run with highest privileges
Actions – New – Start a program – Program:net – Arguments: start OracleOraDb11g_home1TNSListener
Actions – New – Start a program – Program:net – Arguments: start OracleServiceLSC01
Actions – New – Start a program – Program:net – Arguments: start OracleServiceLSC02

5) create a batch file that runs : schtasks /run /tn startoracle

Same for stoporacle

It seems to work fine. Do not play too much with mmc if you do not know it. It is a powerful tool ❗

Categories
Blogroll dba sql

Hey Scott, where have you been ?

Today I missed Scott in my emp table. When selecting from EMP, Scott is not there. Gone…

Ok, let’s recreate the scott schema.

C:> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
SYS@lsc02> spool scott.txt
SYS@lsc02> drop user scott cascade;
User dropped.
SYS@lsc02> @?/rdbms/admin/utlsampl
Disconnected from Oracle Database 10g Enterprise Edition

C:> sqlplus scott/tiger
SQL*Plus: Release 10.2.0.3.0 - Production
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
SCOTT@lsc02> select ename from emp;
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
KING
TURNER
JAMES
FORD
MILLER

12 rows selected.

Hey Scott, I miss you!

Let’s see if I can dig out something in the log file I created with spool scott.txt

(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87',
'dd-mm-rr')-85,3000,NULL,20)
ERROR at line 2:
ORA-01858: a non-numeric character was found
where a numeric was expected

Hey! the UTLSAMPL script is not supposed to work where JUL is not a month ?
reg query HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE /s /f NLS_LANG

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
NLS_LANG REG_SZ FRENCH_SWITZERLAND.WE8MSWIN1252

Since FRENCH has no month called JUL the UTLSAMPL failed to insert SCOTT. Let’s fix the issue :
C:>reg add HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
/v NLS_LANG /t reg_sz /d AMERICAN_AMERICA.WE8MSWIN1252
Value NLS_LANG exists, overwrite(Yes/No)? yes
The operation completed successfully.
C:>reg query HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1 /v NLS_LANG

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
NLS_LANG REG_SZ AMERICAN_AMERICA.WE8MSWIN1252

and retry

C:>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
SYS@lsc02> @?/rdbms/admin/utlsampl
Disconnected from Oracle Database 10g Enterprise Edition

C:>sqlplus scott/tiger
SQL*Plus: Release 10.2.0.3.0 - Production
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
SCOTT@lsc02> select ename from emp;
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.

Welcome back Scott!

Categories
Add new tag Blogroll dba support

DITO

I never used Oracle Web Conference OWC, for security reason. Today I created one SR and read first time about Demo It To Oracle (DITO). This is basically a link CamStudio.org, which is a tool that records AVI file and converts AVI to flash SWF file. This is nice to have because you do need to grant access to your desktop to Oracle Support and you can record what’s happening on your screen !

Let’s have a look

defaultsysdate.swf

Categories
dba linux unix

How to cron?

RTFM is not the best answer …

man crontab
SunOS 5.10 Last change: 10 Nov 2005

User Commands crontab(1)
A crontab file consists of lines of six fields each. The
fields are separated by spaces or tabs. The first five are
integer patterns that specify the following:

minute (0-59),
hour (0-23),
day of the month (1-31),
month of the year (1-12),
day of the week (0-6 with 0=Sunday).

so far so good. But read this

Each of these patterns can be either an asterisk (meaning
all legal values) or a list of elements separated by commas.
An element is either a number or two numbers separated by a
minus sign (meaning an inclusive range). Time specified here
is interpreted in the timezone of the cron(1M) daemon, which
is set system-wide in /etc/default/init. Entries do not use
the invoking user's timezone. The specification of days can
be made by two fields (day of the month and day of the
week). Both are adhered to if specified as a list of ele-
ments. See .

See .

Let’s imagine you want to run a job today, Thursday May 15, 2008 at 2:15pm
You write:
15 14 15 05 4 /tmp/run-my-job

The job will be run Today at 2:15, next year Friday May 15, 2009 at 2:15pm and next week Thursday May 22, 2008 at 2:15pm…

The correct method is to use
15 14 15 05 * /tmp/run-my-job

Specifying both day of week and day of month is not what I expected 😈

Categories
Blogroll dba sql

drop all objects

warning: the script below is destructive and not 100% safe

update: it is unsafe to drop SYS_ objects, check for instance note 579399.1

A question was posted on the french forums of developez.net about how to drop all objects of an user. The drop user toto cascade; followed by create user toto identified by tott; was suggested as an easy answer, but I commented that create user must re-grant quotas, roles, system privileges, table privileges. Create user must also set the correct security status of the account (password, case-sensitive password, lock status, expiration status, profile), and the password history and failed login history is lost. Also it must set the default and temporary tablespaces.

It is also important to note that “drop user” requires dba privileges, when dropping the own object does not require privileges

To drop all objects I tried with plsql and a dictionary loop.

select object_type, count(*)
from user_objects
group by object_type;

OBJECT_TYPE COUNT(*)
------------------- ----------
INDEX 6
TYPE BODY 1
INDEXTYPE 1
PROCEDURE 1
JAVA CLASS 2
JAVA RESOURCE 1
JAVA SOURCE 1
FUNCTION 1
TABLE 10
TRIGGER 1
TYPE 1
MATERIALIZED VIEW 1
DATABASE LINK 1
PACKAGE BODY 1
CLUSTER 1
DIMENSION 1
OPERATOR 1
SEQUENCE 1
PACKAGE 1

19 rows selected.

purge recyclebin;

Purge successfully completed.

begin
for f in (
select object_type, object_name from user_objects
where object_type in (
‘MATERIALIZED VIEW’)) loop
execute immediate
‘drop materialized view “‘||f.object_name||'” preserve table’;
end loop;
for f in (
select table_name from user_tables) loop
execute immediate
‘drop table “‘||f.table_name||'” cascade constraints’;
end loop;
for f in (
select object_type, object_name from user_objects
where object_type in (
‘DIMENSION’,’CLUSTER’,’SEQUENCE’,
‘VIEW’,’FUNCTION’,’PROCEDURE’,
‘PACKAGE’,’SYNONYM’,’DATABASE LINK’,
‘INDEXTYPE’)
and object_name like ‘SYS_%$’) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||f.object_name||'”‘;
end loop
for f in (
select object_type, object_name from user_objects
where object_type in (
‘JAVA SOURCE’)) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||f.object_name||'”‘;
end loop;
for f in (
select object_type, object_name from user_objects
where object_type in (
‘JAVA RESOURCE’)) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||f.object_name||'”‘;
end loop;
for f in (
select object_type, object_name from user_objects
where object_type in (
‘JAVA CLASS’)) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||f.object_name||'”‘;
end loop;
for f in (
select object_type, object_name from user_objects
where object_type in (
‘TYPE’,’OPERATOR’)) loop
execute immediate ‘drop ‘||
f.object_type||’ “‘||
f.object_name||'” force’;
end loop;
end;
/

PL/SQL procedure successfully completed.
select * from user_objects

no rows selected.

All objects of the current schema disappeared, do not try this as sys, this script is destructive with no confirmation

Categories
Blogroll dba sql xml

alert log in xml format

The alert log is in xml format in Oracle 11g. If you want to parse the content of the file and use the XML functions to retrieve the data, you can use this way :
SQL> create or replace directory alert as
2 '/app/oracle/diag/rdbms/lsc01/lsc01/alert';

Directory created.

SQL> var c clob
SQL> declare
2 b bfile := bfilename('ALERT','log.xml');
3 begin
4 dbms_lob.open(b,dbms_lob.file_readonly);
5 dbms_lob.createtemporary(:c,true);
6 dbms_lob.loadfromfile(:c,b,dbms_lob.lobmaxsize);
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> select extractvalue(xmlparse(content :c),
2 '/msg[@time="2008-03-30T01:01:13.704+01:00"]/txt/text()')
3 from dual;
EXTRACTVALUE(XMLPARSE(CONTENT:C),'/MSG[@TIME="2008-03-30T01:01:1
----------------------------------------------------------------

Starting ORACLE instance (normal)

Categories
Blogroll dba

oradebug tracefile_name

I have enabled tracing in a session and now I want to retrieve the name of the tracefile.

Ex: my session has sid 335.

How do I retrieve trace file name from sqlplus ?

select pid from v$process where addr in
(select paddr from v$session where sid=335);

PID
----------
47

Now I can use oradebug to reveal tracefile name

SQL> oradebug setorapid 47
Unix process pid: 1372408, image: oracle@dbsrv01 (TNS V1-V3)
SQL> oradebug tracefile_name
/u01/app/oracle/admin/LSC01/udump/lsc01_ora_1372408.trc

Read valuable information about oradebug on this site, amoung others

Categories
Blogroll dba sql

Side effect of cursor sharing

Cursor sharing transform strings in bind variable.

So if you do SELECT * FROM EMP WHERE ENAME='SCOTT'; it will be transformed in SELECT * FROM EMP WHERE ENAME=:sys_b0;

This sometimes improved performance of application that do not use binds, for instance.

Now let’s imagine your application generates trace files. The application generates a SELECT 'LSC-11.1011.000.1110.1.0100.000.110' FROM DUAL;
. Ok, let’s look for the string in trace files :

$ cd udump
$ grep LSC-11 *.trc
$
$ grep DUAL$ *.trc
LSC01_ora_8630490.trc:SELECT :"SYS_B_0" FROM DUAL
LSC01_ora_8630490.trc:SELECT :"SYS_B_0" FROM DUAL
LSC01_ora_8839288.trc:SELECT :"SYS_B_0" FROM DUAL
LSC01_ora_8839288.trc:SELECT :"SYS_B_0" FROM DUAL
LSC01_ora_8933560.trc:SELECT :"SYS_B_0" FROM DUAL
LSC01_ora_8933560.trc:SELECT :"SYS_B_0" FROM DUAL

WTF! The tracing mechanism of the application is no longer usable 😈

Another side effect is the length of the column and is described there : http://asktom…P11_QUESTION_ID:3696883368520