Re: Incredible ! Oracle HELP PLEASE !

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Thu, 27 Jun 2002 16:15:38 +0100
Message-ID: <3d1b2c1a$0$8511$ed9e5944@reading.news.pipex.net>

"Christian GILBERT" <externe.gilbert_at_francetelecom.com> wrote in message news:1105_1025190395_at_10.193.118.17...
> Sur Thu, 27 Jun 2002 12:55:01 +0200, "Oscar" <oscar_ll_at_jofre.com> a écrit:
> > what does it means ? How can I used. With NOCACHE still with problems.
>
> > pin the sequence using DBMS_SHARED_POOL.KEEP
>
> connect sys (or internal)
>
> SQL>exec dbms_shared_pool.keep('owner.sequence','Q');
>
> For more infos, please refer to the Oracle documentation : Supplied PL/SQL
packages reference

It still won't work.

SQL>
SQL> drop table LGC02
2 ;

Table dropped.

SQL> drop sequence ID_LGC02
2 ;

Sequence dropped.

SQL> create table LGC02 (

  2      ID_LGC02         DEC (9,0)    NULL,
  3      TIARLG           CHAR (1)     NULL,
  4      TITRLG           CHAR (1)     NULL,
  5      FECHLG           DEC  (6, 0)  NULL,
  6      HORALG           DEC  (6, 0)  NULL,
  7      WSIDLG           CHAR (10)    NULL,
  8      USERLG           CHAR (8)     NULL,
  9      DATA_A           CHAR (200)   NULL

10 )
11 ;

Table created.

SQL> create unique index IID_LGC02 on LGC02 (ID_LGC02) 2 ;

Index created.

SQL> create sequence ID_LGC02 nocache start with 1;

Sequence created.

SQL> conn sys as sysdba
Connected.
SQL> exec dbms_shared_pool.keep('niall.ID_LGC02','Q');

PL/SQL procedure successfully completed.

SQL> conn niall/niall
Connected.
SQL> insert into LGC02 ( ID_LGC02,TIARLG, TITRLG, FECHLG, HORALG, WSIDLG, USERLG,
2 DATA_A)

  3     VALUES (ID_LGC02.nextval,  'O',
  4             'A',  260602,  165225,  'SERVIDOR  ',
  5             'CGIFS   ',
  6     'VC11020601000011020601

0000000000000000000000000000000000000000000000000000000000000000000000000000

8 ');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into LGC02 ( ID_LGC02,TIARLG, TITRLG, FECHLG, HORALG, WSIDLG, USERLG,
2 DATA_A)

  3     VALUES (ID_LGC02.nextval,  'O',
  4             'A',  260602,  165225,  'SERVIDOR  ',
  5             'CGIFS   ',
  6     'VC11020601000011020601

0000000000000000000000000000000000000000000000000000000000000000000000000000

8 ');

1 row created.

SQL> rollback;

Rollback complete.

SQL> insert into LGC02 ( ID_LGC02,TIARLG, TITRLG, FECHLG, HORALG, WSIDLG, USERLG,
2 DATA_A)

  3     VALUES (ID_LGC02.nextval,  'O',
  4             'A',  260602,  165225,  'SERVIDOR  ',
  5             'CGIFS   ',
  6     'VC11020601000011020601

0000000000000000000000000000000000000000000000000000000000000000000000000000

8 ');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into LGC02 ( ID_LGC02,TIARLG, TITRLG, FECHLG, HORALG, WSIDLG, USERLG,
2 DATA_A)

  3     VALUES (ID_LGC02.nextval,  'O',
  4             'A',  260602,  165225,  'SERVIDOR  ',
  5             'CGIFS   ',
  6     'VC11020601000011020601

0000000000000000000000000000000000000000000000000000000000000000000000000000

8 ');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into LGC02 ( ID_LGC02,TIARLG, TITRLG, FECHLG, HORALG, WSIDLG, USERLG,
2 DATA_A)

  3     VALUES (ID_LGC02.nextval,  'O',
  4             'A',  260602,  165225,  'SERVIDOR  ',
  5             'CGIFS   ',
  6     'VC11020601000011020601

0000000000000000000000000000000000000000000000000000000000000000000000000000

8 ');

1 row created.

SQL> rolback;
SP2-0042: unknown command "rolback" - rest of line ignored. SQL> rollback;

Rollback complete.

SQL> select ID_LGC02 from LGC02;

ID_LGC02

         1
         3
         4

SQL> spool off

As you refer the poster to the documentaion, can I refer you to the docs as well. At the end of the explanation of sequences there is this word of caution (at least in 9i)

      Caution:
      If accountability for all sequence numbers is required, that is, if

your application can never lose sequence numbers, then you cannot use Oracle sequences and you may choose to store sequence numbers in database tables.

Be careful when implementing sequence generators using database tables. Even in a single instance configuration, for a high rate of sequence values generation, a performance overhead is associated with the cost of locking the row that stores the sequence value.

In other words sequences cannot be used if you absolutely must have all values from the sequence. The only way to ensure this is to serialise access on a database table.

For this reason I consider the often seen requirement that we must have consecutive id numbers as a requirement that we reduce the performance of the system unnecessarily.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************

Received on Thu Jun 27 2002 - 10:15:38 CDT