I just discover about:config (type it in your location bar). It is a very simple configuration editor, with user settings in bold.
For example I like to use a local google version to search (google.ch instead of google.com), so I configured browser.search.defaulturl
duplicate post of ./soug-last-week-2.html
The documents from last SOUG special interest group with Tom Kyte are now available for downloadable on the soug.ch homepage (under history).
The day started with Sven Vetter, who talked about SLA management pack for Enterprise Manager Grid Control 10gR2. We saw also how to define a custom shell script and let OEM generate a graphic over time. He talked about “beacons”, a kind of interface to define application interaction with the grid.
Than came an interesting presentation about Performance Tuning from Patrick Schwanke. He talked about views like DBA_HIST_SQLTEXT and DBA_HIST_SQLBIND. I also learnt the virtual index trick : There is a parameter called _use_nosegment_indexes with allow you to generate EXPLAIN PLAN for a segment without segment. There is a magic keyword called NOSEGMENT. This mechanism is used internally by Oracle Tuning Pack, and by other tools like the ones from Quest.
SQL> create table t as select * from all_objects;
Table created.
SQL> create index i on t(object_id) NOSEGMENT;
Index created.
SQL> alter session set "_use_nosegment_indexes"=true;
Session altered.
SQL> select * from t where object_id=1;
Execution Plan
---------------------------
Plan hash value: 1984501315
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 | INDEX RANGE SCAN | I |
--------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1)
SQL> alter session set "_use_nosegment_indexes"=false;
Session altered.
SQL> select * from t where object_id=1;
Execution Plan
---------------------------
Plan hash value: 2153619298
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| T |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1)
Note
-----
- dynamic sampling used for this statement
Later, Tim Polland talked about Texas Memory and the RAMdisks. It is a piece of hardware which use memory instead of disk to store the datas. The hardware has a kind of RAID mechanism for consistency, and it is not supposed to lose data (!). It is quite different from a typical disk cache, because it does not “cache” the datas, it just saves them in memory. Well, you are not supposed to use this to stores a terabyte of datas, but you could store only your most time-critical datas there, for example move a table to a different tablespace, and store that table on the RAMDISK, also the redo logs, and eventually the undo/system tablespaces too. The RAMDisk hardware is not specific for Oracle. Some “entry-level” ramdisk can offer something like 400 Megabytes/seconds data with nanoseconds access time. Well, it is the speed of memory, so it is also the price of memory. So do not expect to have this at home to play your favorite games !
In the afternoon, we started with Oracle Benchmarks with Manfred Drozd. Very interesting presentation, which shows that there is no “quick” benchmark, but a benchmark should consider I/O, PL/SQL performance, amount of disks, volume manager/ASM, cpu types/count, OS, architecture, Oracle Version, etc. The performance for select, for insert, for update, all those may differ from one system to another.
Real world example just followed with the presentation of a study case in the swiss post. The comparison was mainly between Sun Solaris on Sparc and Linux on x86_64. Well, there was no Better/Worst answer, you know, like is real world…
Last but not least, Tom Kyte, the “Tom” behind asktom, did talk about Instrumentation and the advantage -the need- of using debugging info, with DBMS_APPLICATION_INFO, with your own debug procedure, with Log4Plsql/Log4J packages. The tools we show were DBMS_MONITOR (10g) and trcsess tool (located in $ORACLE_HOME/bin), DBMS_TRACE and PL/SQL conditional compilation. DBMS_TRACE is usefull for detecting catched exception :
SQL> @?/rdbms/admin/dbmspbt
SQL> @?/rdbms/admin/prvtpbt.plb
SQL> @?/rdbms/admin/tracetab
SQL> exec dbms_trace.set_plsql_trace
(dbms_trace.trace_all_exceptions)
PL/SQL procedure successfully completed.
SQL> begin dbms_output.put_line(1/0);
exception when others then null;
end;
/
PL/SQL procedure successfully completed.
SQL> select EXCP,EVENT_UNIT_KIND,ERRORSTACK
from plsql_trace_events
where ERRORSTACK is not null;
EXCP EVENT_UNIT_KIND
---------- --------------------
ERRORSTACK
----------------------------------------
1476 ANONYMOUS BLOCK
ORA-01476: divisor is equal to zero
Sunday, November 26th, 2006, I will defend my Title in the Chinese Chess Swiss Championship.
It is free and open to anyone !
Oracle Database 10g Release 2 (10.2.0.2)
Enterprise/Standard Edition for Solaris Operating System (x86)
There is a bug about NLS_LANG and SWITZERLAND in 10g.
This is because the tausend separator has changed from 9i (.) to 10g (‘).
Check Note 4598613.8
The workaround is to not use NLS_LANG=german_switzerland, but if you have different NLS parameters on the client and on the server, than you will have other problems, ex: questionable statistics by exp/imp.
OK, here is the bug :
$ export NLS_LANG=german_switzerland.we8iso8859p1
$ sqlplus -L scott/tiger
SQL*Plus: Release 10.2.0.2.0 - Production on Do Sep 21 13:50:05 2006
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
ERROR:
ORA-00604: Fehler auf rekursiver SQL-Ebene 1
ORA-02248: Ungültige Option für ALTER SESSION
SP2-0751: Anmeldung bei Oracle nicht möglich. SQL*Plus wird beendet
and my workaround
$ export NLS_LANG="" NLS_TERRITORY=switzerland NLS_LANGUAGE=german
$ sqlplus -L scott/tiger
SQL*Plus: Release 10.2.0.2.0 - Production on Thu Sep 21 13:51:30 2006
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
With the Partitioning and Data Mining options
SQL> select * from NLS_SESSION_PARAMETERS ;
PARAMETER VALUE
---------------------- -----------
NLS_LANGUAGE GERMAN
NLS_TERRITORY SWITZERLAND
NLS_CURRENCY SFr.
NLS_ISO_CURRENCY SWITZERLAND
NLS_NUMERIC_CHARACTERS .'
NLS_CALENDAR GREGORIAN
NLS_DATE_LANGUAGE GERMAN
NLS_SORT GERMAN
NLS_DUAL_CURRENCY SF
17 Zeilen ausgewahlt.
SQL> select to_char(1000,'9G999') from dual;
TO_CHA
------
1'000
SQL> quit
Verbindung zu Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
With the Partitioning and Data Mining options beendet
So it is possible to use german_switzerland. Not sure if this workaround is bullet-proof. Metalink says it is fixed in 11g
[edit]fixed in 10.2.0.3 and later [/edit]
blogger meetup organized by Mark Rittman
I often see questions like
How do you get the row of each department with the highest salary
In case you only want 1 row, you have two modern solutions :
Analytics, which is trend, and KEEP, which is not very known
The old fashion would be something like where s in (select max())
Ok, let’s start with analytics
SQL> select ename,deptno,sal
2 from (select ename,deptno,sal,
3 row_number() over (partition by deptno
4 order by sal desc,empno) r from emp)
5 where r=1;
ENAME DEPTNO SAL
---------- ---------- ----------
KING 10 5000
SCOTT 20 3000
BLAKE 30 2850
and the KEEP method, which is a special aggregation
SQL> select max(ename) keep (dense_rank first
2 order by sal desc,empno) ename,
3 deptno,max(sal) sal
4 from emp group by deptno;
ENAME DEPTNO SAL
---------- ---------- ----------
KING 10 5000
SCOTT 20 3000
BLAKE 30 2850
the second one should be more performant
SQL> select count(*) from emp2;
COUNT(*)
----------
917504
SQL> select max(ename) keep (dense_rank first
2 order by sal desc,empno) ename,
3 deptno,max(sal) sal
4 from emp group by deptno;
ENAME DEPTNO SAL
---------- ---------- ----------
KING 10 5000
SCOTT 20 3000
BLAKE 30 2850
Elapsed: 00:00:01.00
SQL> select ename,deptno,sal
2 from (select ename,deptno,sal,
3 row_number() over (partition by deptno
4 order by sal desc,empno) r from emp)
5 where r=1;
ENAME DEPTNO SAL
---------- ---------- ----------
KING 10 5000
SCOTT 20 3000
BLAKE 30 2850
Elapsed: 00:00:01.43
On wednesday, there is a DBA day in the SOUG community.
DBA-SIG 06/4
The highlight of this session will be Tom Kyte, speaking about Oracle System Architecture.
Check the homepage of SOUG.CH for registration
Tomorrow LC Systems is organizing an event about Identity Management in Zurich.
The focus will be on Sun Java System Directory Server and Oracle Virtual Directory.
wget is a well-known command line utility for downloading files from the internet/intranet.
I like to use the limit-rate, so that my coworkers still can surf. Until today, I never succeeded to download from otn, because wgetting software required me to log in and accept the export restriction
How to automate login is actually extremly easy :
1) open your mozilla browser, go to the page, login, accept the export, download the file, click properties, copy paste the “Saving From”, Cancel
2) find out where is located your cookies.txt file
$ find $HOME -name "cookies.txt"
/home/lsc/.mozilla/default/86iy2n5j.slt/cookies.txt
3) wget
$ wget –load-cookies /home/lsc/.mozilla/default/86iy2n5j.slt/cookies.txt –limit-rate 250k http://download-uk.oracle.com/otn/compaq/oracle10g/10202/10202_database_HP-Tru64.zip
100%[++++++++++++++++++++++++++++======>] 1,081,211,566 254.98K/s ETA 00:00
16:48:05 (249.99 KB/s) – `10202_database_HP-Tru64.zip.1′ saved [1,081,211,566/1,081,211,566]
It works!
I just discovered that cool new function in SQL/Developer
just right click on the table and chose export sql insert
-- INSERTING into EMP
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7369
,'SMITH','CLERK',7902,to_date('1980-12-17',
'DD-MON-RR'),800,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7499
,'ALLEN','SALESMAN',7698,to_date('1981-02-20',
'DD-MON-RR'),1600,300,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7521
,'WARD','SALESMAN',7698,to_date('1981-02-22',
'DD-MON-RR'),1250,500,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7566
,'JONES','MANAGER',7839,to_date('1981-04-02',
'DD-MON-RR'),2975,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7654
,'MARTIN','SALESMAN',7698,to_date('1981-09-28',
'DD-MON-RR'),1250,1400,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7698
,'BLAKE','MANAGER',7839,to_date('1981-05-01',
'DD-MON-RR'),2850,null,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7782
,'CLARK','MANAGER',7839,to_date('1981-06-09',
'DD-MON-RR'),2450,null,10);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7788
,'SCOTT','ANALYST',7566,to_date('1987-04-19',
'DD-MON-RR'),3000,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7839
,'KING','PRESIDENT',null,to_date('1981-11-17',
'DD-MON-RR'),5000,null,10);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7844
,'TURNER','SALESMAN',7698,to_date('1981-09-08',
'DD-MON-RR'),1500,0,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7876
,'ADAMS','CLERK',7788,to_date('1987-05-23',
'DD-MON-RR'),1100,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7900
,'JAMES','CLERK',7698,to_date('1981-12-03',
'DD-MON-RR'),950,null,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7902
,'FORD','ANALYST',7566,to_date('1981-12-03',
'DD-MON-RR'),3000,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7934
,'MILLER','CLERK',7782,to_date('1982-01-23',
'DD-MON-RR'),1300,null,10);
It is quite handy to post insert statements on forums, or move a table from one database to another just with copy paste, or export only a subset of the columns, because you can export a view too
Register in Paris (18-19 sep), Zurich (21-22 sep) or Wien (25-26 sep) at oracle university for the tom kyte workshop.
Lutz Hartmann wrote about this event in his blog
How often I did meet ORA-01555: snapshot too old: rollback segment too small ?
I cannot count. On one of the database I am currently administrating it is about once a day.
Back to Oracle 7 and 8, the solution was usually to size the rollback segments properly. Using big rollback segments for big jobs, and many small segments for OLTP.
Oracle 9i introduced the automatic undo management. When you meet ORA-01555, just increase the UNDO tablespace (or/and set it autoextensible). Well, how did I read complains from users which used to have tiny rollback segments, and did not understand why the undo tablespace grows to gigabytes in 9i. There is also a parameter, called UNDO_RETENTION, which prevents Oracle from rewritting old extents before it really needs them.
In 10g, you can also force oracle to keep old undo extents up to the UNDO_RETENTION by using
alter tablespace UNDOTBS1 retention guarantee;
What I learned today is the TUNED_UNDORETENTION mechanism. By setting UNDO_RETENTION to 0, you enable auto tuned retention. That is, as long as your tablespace can autoextend, you will less probably get an ORA-01555. A recommended lecture is Metalink Note 240746.1
Back to my 9i database with ORA-01555, I will try to increase undo_retention to something bigger than
SQL> select max(maxquerylen) from v$undostat;
MAX(MAXQUERYLEN)
----------------
105047
And see how long I will survive until the next ORA-01555…
Patchset 9.2.0.8 is out for a few plateform. I am quite surprised it did not come out on Solaris 32bits first, as I expected, but rather on the following
Microsoft Windows 32bits
Microsoft Windows 64bits
Linux Itanium
Linux x86_64
HP UX PA-RISC
IBM AIX5L
IBM z/OS (OS 390)
This is the terminal release of 9iR2
the sessions I registered with for oracle open world 2006 are the following :
Monday 10:45 : Bryn LLewellyn, PLSQL Manager, will talk about PLSQL future in the next release (11g) : session S281172
Monday 12:30 : Andrew Clarke, Oracle ACE, will talk about unit testing in PL/SQL : session 282112
Monday 15:15 : Thomas Kyte, Oracle ACE and asktom owner, will talk about sql worst practice : session 281206
Monday 16:15 : Steven Feuerstein, Oracle ACE and PL/SQL author, will talk about 10 Things You Should NEVER Do in PL/SQL : session 281918
Tuesday 13:15 : Sue Harper, SQL Developer Product Manager, and Kris Rice, Director of Database Tools Research, will talk about Advanced Database Development with Oracle SQL Developer : session 281142
Tuesday 16:30 : Wim Coekaerts, Oracle ACE and Linux Principal, will talk about Securing Linux for Oracle : session 281224
Thursday 09:30 : Werner Puschitz, Oracle ACE, will talk about Automated Oracle Real Application Clusters Deployment : Session 283479
I have booked a few more sessions, about Virtual Directory, Application Server and Grid Control.
I will be at the OTN lounge to play chess or othello at any time 🙂
cu@oow2k6@sf
Do not switch to blogger beta. It failed, and everything is corrupted on my page, I cannot fix it yet (this message will probably only be readable as feed). Take care!
I created a new group in OpenWorld Connect. In San Francisco I would like to meat other people writing and reading blogs.
If you have not actived openworld connect yet, you can update your profile on http://www.cplan.com/oracleopenworld2006/leverageoptin, than join the Oracle Blogger group at Oracle Blogger
I just had to guide one of our DBA this morning.
How to get free of segments in SYSTEM tablespace :
SQL> alter table scott.t move tablespace users;
Table altered.
SQL> select owner, table_name from dba_tables where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
no rows selected
If the table has a LONG datatype, than I need to exp/drop/create/imp the table
SQL> select owner, table_name from dba_tables where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
OWNER TABLE_NAME
—– ———-
SCOTT T
SQL> alter table scott.t move tablespace users;
alter table scott.t move tablespace users
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
SQL> ! exp file=t.dmp tables=t userid=scott/tiger
Export: Release 10.2.0.2.0 – Production on Mon Aug 14 14:24:29 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path …
. . exporting table T 3 rows exported
Export terminated successfully without warnings.
SQL> select dbms_metadata.get_ddl(‘TABLE’,’T’,’SCOTT’) from dual;
DBMS_METADATA.GET_DDL(‘TABLE’,’T’,’SCOTT’)
—————————————————————————————————————————————
CREATE TABLE “SCOTT”.”T”
( “L” LONG
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “SYSTEM”
SQL> drop table scott.t;
Table dropped.
SQL> CREATE TABLE “SCOTT”.”T”
2 ( “L” LONG
3 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
4 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
5 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
6 TABLESPACE “USERS”;
Table created.
SQL> ! imp file=t.dmp tables=t userid=scott/tiger ignore=y
Import: Release 10.2.0.2.0 – Production on Mon Aug 14 14:27:05 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing SCOTT’s objects into SCOTT
. importing SCOTT’s objects into SCOTT
. . importing table “T” 3 rows imported
Import terminated successfully without warnings.
SQL> select owner, index_name from dba_indexes where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
OWNER INDEX_NAME
—– ———-
SCOTT I
SQL> alter index scott.i rebuild tablespace users;
Index altered.
SQL> select owner, index_name from dba_indexes where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
no rows selected
SQL> alter table scott.t move lob(c) store as (tablespace users);
Table altered.
SQL> select owner, table_name, column_name from dba_lobs where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
no rows selected
SQL> alter table scott.t move partition p1 tablespace users;
Table altered.
SQL> select table_owner, table_name, partition_name from dba_tab_partitions where tablespace_name=’SYSTEM’ and table_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
no rows selected
SQL> alter index scott.i rebuild partition SYS_P32 tablespace users;
Index altered.
SQL> select index_owner, index_name, partition_name from dba_ind_partitions where tablespace_name=’SYSTEM’ and index_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
no rows selected
SQL> alter table scott.t move subpartition SYS_SUBP30 tablespace users;
Table altered.
SQL> select table_owner, table_name, subpartition_name from dba_tab_subpartitions where tablespace_name=’SYSTEM’ and table_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
no rows selected
SQL> alter index scott.i rebuild subpartition SYS_SUBP31 tablespace users;
Index altered.
SQL> select index_owner, index_name, subpartition_name from dba_ind_subpartitions where tablespace_name=’SYSTEM’ and index_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
no rows selected
SQL> alter table scott.t move tablespace users;
Table altered.
SQL> select owner, table_name, index_name, index_type from dba_indexes where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
no rows selected
SQL> alter table scott.t move overflow tablespace users;
Table altered.
SQL> select owner, table_name, iot_name, iot_type from dba_tables where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
no rows selected
SQL> alter table scott.ctt move tablespace users;
Table altered.
SQL> select owner, table_name from dba_tables where nested=’YES’ and tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
no rows selected
SQL> alter table scott.t move lob(c) store as (tablespace users);
alter table scott.t move lob(c) store as (tablespace users)
*
ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object
SQL> select table_owner,table_name,column_name,partition_name from dba_lob_partitions where tablespace_name=’SYSTEM’ and table_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
TABLE_OWNER TABLE_NAME COLUMN_NAME PARTITION_NAME
———– ———- ———– ————–
SCOTT T C P1
SQL> alter table scott.t move partition p1 lob(c) store as (tablespace users);
Table altered.
SQL> select table_owner,table_name,column_name,partition_name from dba_lob_partitions where tablespace_name=’SYSTEM’ and table_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
no rows selected
SQL> select owner, table_name, column_name from dba_lobs where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
OWNER TABLE_NAME COLUMN_NAME
—– ———- —————–
SCOTT T C
the table was ranged partitioned. With a hash partition, i would get an ORA-22877
SQL> alter table scott.t move partition sys_p47 lob(c) store as (tablespace users);
alter table scott.t move partition sys_p47 lob(c) store as (tablespace users)
*
ERROR at line 1:
ORA-22877: invalid option specified for a HASH partition or subpartition of a LOB column
The DBA also wanted to separate indexes and tables for tuning IOs. I had to redirect him to the doc :
Performance Tuning Guide
One popular approach to manual I/O distribution suggests separating a frequently used table from its index. This is not correct.
well, he shows me his Oracle 9i Tuning Course, Chapter 3, Slice 6, put table and indexes on different tablespaces… Ok, this is not a new thema, just google to find explanations !
Huge downtime on forums.oracle.com today. After landing on an Error-500 page, I discovered a quick and neat overview of oracle patchsets, accessible without metalink account :
http://www.oracle.com/technology/support/patches.htm
Still waiting for 9.2.0.8 Solaris by the way, announced for 2006Q3, whatever this mean
I recently posted about Oracle Password Repository (OPR).
I did get a comment from Andreas Piesk about something similar in Oracle, the wallet.
Let’s do a quick test.
First, I create a .sqlnet.ora in my home directory (I do not want to mess up the system-wide sqlnet.ora).
$ cat /home/lsc/.sqlnet.ora
SQLNET.WALLET_OVERRIDE=TRUE
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/lsc)))
I now create the wallet
$ mkstore -create -wrl /home/lsc
Enter password:
Enter password again:
and the credentials
$ mkstore -wrl /home/lsc -createCredential LSC01 scott tiger
Enter password:
Create credential oracle.security.client.connect_string1
now I try to login
$ sqlplus /@LSC01
SQL*Plus: Release 10.2.0.2.0 - Production on Thu Aug 10 11:23:35 2006
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> sho user
USER is "SCOTT"
This sounds to work very nicely. Let’s see if this is more secure than OPR :
$ mkstore -wrl /home/lsc -list
Enter password:
Oracle Secret Store entries:
oracle.security.client.connect_string1
oracle.security.client.password1
oracle.security.client.username1
$ mkstore -wrl /home/lsc -viewEntry oracle.security.client.connect_string1
Enter password:
oracle.security.client.connect_string1 = LSC01
$ mkstore -wrl /home/lsc -viewEntry oracle.security.client.username1
Enter password:
oracle.security.client.username1 = scott
$ mkstore -wrl /home/lsc -viewEntry oracle.security.client.password1
Enter password:
oracle.security.client.password1 = tiger
Definitely! The password is not reveal, unless you know the password of the wallet. Remember in OPR, the application had direct access to the password. Here it is not the case, if you do not know the password of the wallet, you may login, but you cannot find out what the password is. I like this very much.
of course do not forget to protect your wallet
Ok, what I did not achieve until yet is logging in externally when using wallet_override :
$ mv /home/lsc/.sqlnet.ora /home/lsc/.sqlnet.ora.disable
$ sqlplus /
SQL*Plus: Release 10.2.0.2.0 - Production on Thu Aug 10 11:35:56 2006
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> sho user
USER is "OPS$LSC"
$ mv /home/lsc/.sqlnet.ora.disable /home/lsc/.sqlnet.ora
$ sqlplus /
SQL*Plus: Release 10.2.0.2.0 - Production on Thu Aug 10 11:37:13 2006
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
I will update this if I find out how to login externally too.
I just read about a query to retrieve last modification row of a date
SQL> SELECT ora_rowscn FROM tab_test;
ORA_ROWSCN
----------
351744
351744
351744
351744
351744
351744
6 rows selected.
SQL> UPDATE tab_test SET valeur=valeur*1.1 WHERE col_id=1;
3 rows updated.
SQL> commit;
Commit complete
SQL> SELECT ora_rowscn FROM tab_test:
ORA_ROWSCN
----------
351744
351744
351744
371423
371423
371423
6 rows selected.
conclusion of the author : very neat to retrieve last modification date.
Well, I am worried. I have answered so many times on the technical forums here for example, that the only way to retrieve the last rows is to use a date column, and to manually update it (or with a trigger or a default value for insert) with the last modification date.
What should I do?
test it!
14:52:07 SQL> create table t as
select rownum x from all_objects;
Table created.
14:56:23 SQL> select scn_to_timestamp(ora_rowscn) timestamp, count(*) from t group by scn_to_timestamp(ora_rowscn);
TIMESTAMP COUNT(*)
---------------------- ----------
04.08.2006 14:56:23.00 4238
let’s update one row
14:54:12 SQL> update t set x=-1 where rownum=1;
1 row updated.
14:55:04 SQL> commit;
Commit complete.
14:58:03 SQL> select scn_to_timestamp(ora_rowscn) timestamp, count(*) from t group by scn_to_timestamp(ora_rowscn) order by scn_to_timestamp(ora_rowscn);
TIMESTAMP COUNT(*)
---------------------- ----------
04.08.2006 14:56:23.00 3580
04.08.2006 14:57:14.00 658
what? I updated one row, why did it updated so many rows? Let’s look at the block
14:58:16 SQL> select dbms_rowid.rowid_block_number(rowid) block_number,scn_to_timestamp(ora_rowscn) timestamp,count(*) from t group by dbms_rowid.rowid_block_number(rowid),scn_to_timestamp(ora_rowscn) order by scn_to_timestamp(ora_rowscn);
BLOCK_NUMBER TIMESTAMP COUNT(*)
------------ ---------------------- ----------
651 04.08.2006 14:56:23.00 658
652 04.08.2006 14:56:23.00 658
653 04.08.2006 14:56:23.00 658
654 04.08.2006 14:56:23.00 658
655 04.08.2006 14:56:23.00 658
656 04.08.2006 14:56:23.00 290
650 04.08.2006 14:57:14.00 658 <=====================
ok, what has the doc to say about this :
For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is useful for determining approximately when a row was last updated. It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides. You can obtain a more fine-grained approximation of the SCN by creating your tables with row-level dependency tracking
Well, let's try again with row dependencies.
15:04:53 SQL> drop table t;
Table dropped.
15:04:55 SQL> create table t rowdependencies as select rownum x from all_objects;
Table created.
15:05:28 SQL> select scn_to_timestamp(ora_rowscn) timestamp, count(*) from t group by scn_to_timestamp(ora_rowscn) order by scn_to_timestamp(ora_rowscn);
TIMESTAMP COUNT(*)
---------------------- ----------
04.08.2006 15:05:26.00 4241
15:05:42 SQL> update t set x=-1 where rownum=1;
1 row updated.
15:05:58 SQL> commit;
Commit complete.
15:06:09 SQL> select scn_to_timestamp(ora_rowscn) timestamp, count(*) from t group by scn_to_timestamp(ora_rowscn) order by scn_to_timestamp(ora_rowscn);
TIMESTAMP COUNT(*)
---------------------- ----------
04.08.2006 15:05:26.00 4240
04.08.2006 15:06:08.00 1
sounds better! probably not 100% reliable but way better, it seems almost usable ...
I just registered to Oracle OpenWorld in October in San Francisco… Well, I know it is a long long long way to the west, but I will survive again, but this time I will avoid Delta Airlines, they refused to serve me a glass of wine last year in the plane! I am going to take maybe Lufthansa.
As an ACE, I got the Oracle Develop for free this year too. I also took the gold pass, well, waiting hours for keynotes is just too much boring. Since it is the only thing I pay myself (ok wine in the plane too), I guess this 150$ investment is ok.
If available, I will try the Oracle Application Server OCP Beta exam 1Z1-312 in SF. Also, I will ask them why this OCM 10g upgrade is still not available.
I read forums.oracle.com frequently. I have read and answered thousands of questions there.
Those days, they launched a new release, I do not know what the improvements are, but it is fairly unstable. Well, it has been worst in the past, but I keep getting We’re sorry, the server encountered an unexpected condition and timetouts occasionaly.
Another nightmare for users was the blank-trimming effect, it was hardly possible to format the post, all multiple spaces were trimmed to one space, even by using [code] or [pre]. It is now fixed!
There has been also two new icons, Usefull answer and Correct answer, but it has been removed already. It makes me remembers the smilies they introduced in a previous release, where :p bind variable was translated to a smile…
Problem with the downtime is that the experts go away. I post and read hundreds of messages on metalink, especially in sqlplus and plsql forums. The problem on metalink, lot’s of users expect to have an immediate and advanced answer from Oracle, because they pay for support, and they do not invest time to write the question properly nor to say please or thank you.
I recently start writing on the linux and oracle forums of developpez.net, where they put a high value on proper formatting, code of conduct, correct spelling (in french).
I installed OAS 10.1.3 on my notebook. Why? well, just to test if it is installable… and to justify my need of 2Gb of memory to my boss 😉
Ok, SLES10 is not certified yet, Not even planned yet on metalink. But it will definitely be certified one day.
The first step is to install the necessary packages
1) db1 : Berkeley DB Database Library Version 1.85
this package is needed, in order to avoid :
httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory
2) openmotif21-libs, sysstat
as listed in the quick installation guide for SLES9
3) libgnome and libgnome-devel
replace the old gnome-libs and gnome-libs-devel
4) pdksh
ksh-93r seems to be suffisant
5) glibc 2.3 and gcc 3.3
no problem until yet by using glibc 2.4 and gcc 4.1
The second and last step is to launch the installer, with -ignoreSysPrereqs or with an updated install/oraparam.ini
That’s all. I did get some warning about memory and requirements, but it seems that it works.
The orarun package to be installed in SLES10
I checked this tool today :
http://sourceforge.net/projects/opr
This tool provide a simple way of not hardcoding passwords in shell scripts. Hardcoding passwords in shell scripts is a bad practice. The source code may be shared by many developers, may resides on unsecure servers (CVS), may be printed, etc… The passwords may change often too.
This tool uses a simple file to stores the passwords encrypted. Well, I urge you to secure this file to make it non-accessible for others.
I am not going to decode nor rate the encryption algorythm, the fact is, you do not have clear text passwords. It could be also possible to have clear text password in a separate text file, which would not be that less secure, but it is ugly.
To make it clear (I hope), it is a 2-ways encryption, so if you have access to the file and the source code of the algorythm, you can crack the password. Sounds weak? Well, Oracle Proxy Users with Internet Directory, Application Server, Portal and all those products are not much different. The only 100% passwordless solution I am aware of is the external OS identification.
Well, that said, let’s look how it works.
# ./configure # make # make install
let’s create the repository
$ export OPRREPOS=$ORACLE_HOME/dbs/oprrepos $ opr -c
store the password for scott on LSC01
$ opr -a LSC01 SCOTT lsc please enter the password : please re-enter the password : entry (LSC01, scott, lsc) added.
let’s test
$ sqlplus scott/$(opr -r LSC01 SCOTT)@LSC01 SQL*Plus: Release 10.2.0.2.0 - Production on Tue Jul 25 13:51:48 2006 Copyright (c) 1982, 2005, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, OLAP and Data Mining options
This is quite convenient. I will use this to store the RMAN password to connect to the RMAN repository in my backup scripts
How neat is Suse!
I downloaded the DVD SLES10 on Novell.com. At the installation time, appart from Gnome, Development, X Window, there are a few new categories. One of them is called Oracle Database, and guess what, it installs all what I needed for Oracle. It creates an oracle account, with oinstall as primary group and dba as secondary group, it sets usefull things like ORACLE_HOME.
Ok, let’s unlock the account
chltlxlsc1:~ # usermod -s /bin/bash oracle chltlxlsc1:~ # passwd oracle Changing password for oracle. New Password: Reenter New Password: Password changed.
Ok, let’s install. SLES10 is not recognized as a certified OS yet. The DISPLAY thing has to be manually set too.
oracle@chltlxlsc1:~> ./runInstaller -ignoreSysPrereqs
Starting Oracle Universal Installer...
Checking installer requirements...
Checking operating system version: must be redhat-3, SuSE-9,
redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
Failed <<<<
>>> Ignoring required pre-requisite failures. Continuing...
this is one of the friendliest installation I have ever made…
I successfully applied patch 5225799 on my notebook
SQL> select action,version,id,comments from dba_registry_history;
ACTION VERSION ID COMMENTS
------ ---------- -------- ----------
CPU 10.2.0.2.0 5225799 CPUJul2006