TUNED_UNDORETENTION

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…

oow2k6 sessions

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

tablespace maintenance tasks

I just had to guide one of our DBA this morning.

How to get free of segments in SYSTEM tablespace :

  1. tables

    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;

    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.

  2. Indexes

    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

  3. lob
    warning: in 9i you must join with dba_tables to get tablespace_name

    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

    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

  4. table partition

    SQL> select table_owner, table_name, partition_name from dba_tab_partitions where tablespace_name=’SYSTEM’ and table_owner not in (‘SYS’,'SYSTEM’,'OUTLN’);
    TABLE_OWNER TABLE_NAME PARTITION_NAME
    ———– ———- ————–
    SCOTT       T          P1

    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

  5. index partition

    SQL> select index_owner, index_name, partition_name from dba_ind_partitions where tablespace_name=’SYSTEM’ and index_owner not in (‘SYS’,'SYSTEM’,'OUTLN’);
    INDEX_OWNER INDEX_NAME PARTITION_NAME
    ———– ———- ————–
    SCOTT       I          SYS_P32

    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

  6. table subpartition

    SQL> select table_owner, table_name, subpartition_name from dba_tab_subpartitions where tablespace_name=’SYSTEM’ and table_owner not in (‘SYS’,'SYSTEM’,'OUTLN’);
    TABLE_OWNER TABLE_NAME SUBPARTITION_NAME
    ———– ———- ——————————
    SCOTT       T          SYS_SUBP30

    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

  7. index subpartition

    SQL> select index_owner, index_name, subpartition_name from dba_ind_subpartitions where tablespace_name=’SYSTEM’ and index_owner not in (‘SYS’,'SYSTEM’,'OUTLN’);
    INDEX_OWNER INDEX_NAME SUBPARTITION_NAME
    ———– ———- ——————————
    SCOTT       I          SYS_SUBP31

    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

  8. IOT

    SQL> select owner, table_name, index_name, index_type from dba_indexes where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,'SYSTEM’,'OUTLN’);
    OWNER TABLE_NAME INDEX_NAME INDEX_TYPE
    —– ———- —————– ———-
    SCOTT T          SYS_IOT_TOP_10406 IOT – TOP

    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

  9. IOT Overflow

    SCOTTSQL> select owner, table_name, iot_name, iot_type from dba_tables where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,'SYSTEM’,'OUTLN’);
    OWNER TABLE_NAME IOT_NAME IOT_TYPE
    —– —————— ——– ————
    SCOTT SYS_IOT_OVER_10406 T        IOT_OVERFLOW

    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

  10. cluster
    I did not find out yet :-( Probably not possible, so use exp+drop+create+imp as for long

  11. nested tables
    warning: according to the sql reference manual (not tested), in 9i, the nested table was moved with the parent table automatically

    SQL> select owner, table_name from dba_tables where nested=’YES’ and tablespace_name=’SYSTEM’ and owner not in (‘SYS’,'SYSTEM’,'OUTLN’);
    OWNER TABLE_NAME
    —– ———-
    SCOTT CTT

    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

  12. Lob partitions and subpartitions
    This is getting tricky… I did not achieve to change the default tablespace specification for lob, nor to change a HASH partition. Again, when all failed, use exp/drop/create/imp

    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

    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 !

sqlnet.wallet_override=true

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.

select last rows

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 …

Oracle OpenWorld 2006

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.

OTN forums new release

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