Pro Oracle Database 11g Administration (39 page)

You can also use the GET_DDL function of the DBMS_METADATA package to display synonym metadata. If you want to display the DDL for all synonyms for the currently connected user, run this SQL: SQL> select dbms_metadata.get_ddl('SYNONYM', synonym_name) from user_synonyms; You can also display the DDL for a particular user. You must provide as input to the GET_DDL

function the object type, object name, and schema:

select

dbms_metadata.get_ddl(object_type=>'SYNONYM', name=>'VDB', schema=>'INV') from dual;

Renaming a Synonym

You may want to rename a synonym so it conforms to naming standards or so you can determine whether it’s being used. Use the RENAME statement to change the name of a synonym: SQL> rename inv_s to inv_st;

Notice that the output displays this:

Table renamed.

This message is somewhat misleading. It indicates a table has been renamed, when in this scenario it was a synonym.

203

CHAPTER 9 ■ VIEWS, SYNONYMS, AND SEQUENCES

Dropping a Synonym

Use the DROP SYNONYM statement to drop a private synonym:

SQL> drop synonym inv;

If it’s a public synonym, then you need to specify PUBLIC when you drop it: SQL> drop public synonym inv_pub;

Managing Sequences

A
sequence
is a database object that users can access to select unique integers. Sequences are typically used to generate integers for populating primary-key and foreign-key columns. You increment a sequence by accessing it via a SELECT, INSERT, or UPDATE statement. Oracle guarantees that a sequence number is unique when selected; no two user sessions can select the same sequence number.

There is no way to guarantee that occasional gaps won’t occur in the numbers generated by a sequence. Usually, some number of sequence values are cached in memory, and in the event of an instance failure (power failure, shutdown abort), any unused values still in memory are lost. Even if you don’t cache the sequence, nothing stops a user from acquiring a sequence as part of a transaction and then rolling back that transaction. But for most applications, it’s acceptable to have a mostly gap-free unique integer generator. Just be aware that gaps can exist.

Creating a Sequence

For many applications, creating a sequence can be as simple as this:

SQL> create sequence inv_seq;

If you don’t specify a starting number and a maximum number for a sequence, by default the starting number is 1, the increment is 1, and the maximum value is 10^27. This example specifies a starting value of 1000 and a maximum value of 1000000:

SQL> create sequence inv2 start with 10000 maxvalue 1000000;

Table 9–1 lists the various options available when you’re creating a sequence.

204

CHAPTER 9 ■ VIEWS, SYNONYMS, AND SEQUENCES

Table 9–1.
Sequence-Creation Options

Option Description

INCREMENT BY

Specifies the interval between sequence numbers.

START WITH

Specifies the first sequence number generated.

MAXVALUE

Specifies the maximum value of the sequence.

NOMAXVALUE

Sets the maximum value of a sequence to a really big number (10^28 -1).

MINVALUE

Specifies the minimum value of sequence.

NOMINVALUE

Sets the minimum value to 1 for an ascending sequence; sets the value to –

(10^28–1) for a descending sequence.

CYCLE

Specifies that when the sequence hits a maximum or minimum value, it

should start generating numbers from the minimum value for an

ascending sequence and from the maximum value for a descending

sequence.

NOCYCLE

Tells the sequence to stop generating numbers after a maximum or

minimum value is reached.

CACHE

Specifies how many sequence numbers to preallocate and keep in

memory. If CACHE and NOCACHE aren’t specified, the default is CACHE 20.

NOCACHE

Specifies that sequence numbers aren’t to be cached.

ORDER

Guarantees that the numbers are generated in the order of request.

NOORDER

Used if you don’t need to guarantee that sequence numbers are generated in the order of request. This is usually acceptable and is the default.

Using Sequence Pseudo-columns

After a sequence is created, you can use two pseudo-columns to access the sequence’s value:

• NEXTVAL

• CURRVAL

You can reference these pseudo-columns in any SELECT, INSERT, or UPDATE statements. To retrieve a value from the INV_SEQ sequence, access the NEXTVAL value as shown:

SQL> select inv_seq.nextval from dual;

205

CHAPTER 9 ■ VIEWS, SYNONYMS, AND SEQUENCES

Now that a sequence number has been retrieved for this session, you can use it multiple times by accessing the CURRVAL value:

SQL> select inv_seq.currval from dual;

The following example uses a sequence to populate the primary-key value of a parent table, and then uses the same sequence to populate the corresponding foreign-key values in a child table. The sequence can be accessed directly in the INSERT statement. The first time you access the sequence, use the NEXTVAL pseudo-column.

SQL> insert into inv(inv_id, inv_desc) values (inv_seq.nextval, 'Book'); If you want to reuse the same sequence value, you can reference it via the CURRVAL pseudocolumn. Next, a record is inserted into a child table that uses the same value for the foreign-key column as its parent primary-key value:

insert into inv_lines

(inv_line_id,inv_id,inv_item_desc)

values

(1, inv_seq.currval, 'Tome1');

--

insert into inv_lines

(inv_line_id,inv_id,inv_item_desc)

values

(2, inv_seq.currval, 'Tome2');

Autoincrementing Columns

I occasionally get this request from a developer: “I used to work with another database, and it had a really cool feature that would allow you to create a table and as part of the table definition specify that a column should always be populated with an automatically incrementing number.” I usually reply something like, “Oracle has no such feature. If you have an issue with this, please send an email to Larry at….” Or I inform the developer that they can either use the sequence number directly in an INSERT

statement (as shown in the prior section) or select the sequence value into a variable and then reference the variable as needed.

If you really need an autoincrementing column, you can simulate this functionality by using triggers. For example, say you create a table and sequence as follows: SQL> create table inv(inv_id number, inv_desc varchar2(30));

SQL> create sequence inv_seq;

Next, create a trigger on the INV table that automatically populates the INV_ID column from the sequence:

create or replace trigger inv_bu_tr

before insert on inv

for each row

begin

select inv_seq.nextval into :new.inv_id from dual;

end;

/

Now, insert a couple of records into the INV table:

SQL> insert into inv (inv_desc) values( 'Book');

SQL> insert into inv (inv_desc) values( 'Pen');

206

CHAPTER 9 ■ VIEWS, SYNONYMS, AND SEQUENCES

SQL> create sequence inv_seq_odd start with 1 increment by 2;

SQL> create sequence inv_seq_even start with 2 increment by 2;

SQL> create sequence inv_seq_odd_dwn start with -1 increment by -2; SQL> create sequence inv_seq_even_dwn start with -2 increment by -2; The numbers generated by these four sequences should never intersect. However, this approach is limited to being able to use only four sequences.

If you need more than four unique sequences, you can use ranges of numbers. For example: SQL> create sequence inv_seq_low start with 1 increment by 1 maxvalue 10000000; SQL> create sequence inv_seq_ml start with 10000001 increment by 1 maxvalue 20000000; SQL> create sequence inv_seq_mh start with 20000001 increment by 1 maxvalue 30000000; SQL> create sequence inv_seq_high start with 30000001 increment by 1 maxvalue 40000000; With this technique, you can set up numerous different ranges of numbers to be used by each sequence. The downside is that you’re limited by the number of unique values that can be generated by each sequence.

Using One Sequence or Many

Say you have an application with 20 tables. One question that comes up is whether you should use 20

different sequences to populate the primary-key and/or foreign-key columns for each table, or use just 1

sequence.

I recommend using just one sequence. One sequence is easier to manage than multiple sequences, it’s less DDL code to manage, and it means fewer places to investigate when there are issues.

Sometimes developers raise issues such as

• Performance issues with only one sequence

• Sequence numbers that get too high

If you cache the sequence values, usually there are no performance issues with accessing sequences.

The maximum number for a sequence is 10^28–1, so if the sequence is incrementing by 1, you’ll never reach the maximum value (at least, not in this lifetime).

However, in some scenarios where you’re generating surrogate keys for the primary and child tables, it’s convenient to use more than one sequence. In these situations, multiple sequences per application may be warranted. When you use this approach, you must remember to add a sequence when tables are added and potentially drop sequences as tables are removed. It isn’t a big deal, but it means a little more maintenance for the DBA; and the developers must ensure that they use the correct sequence for each table.

Viewing Sequence Metadata

If you have DBA privileges, you can query the DBA_SEQUENCES view to display information about all sequences in the database. To view sequences that your schema owns, query the USER_SEQUENCES view: select

sequence_name

,min_value

,max_value

,increment_by

from user_sequences;

208

CHAPTER 9 ■ VIEWS, SYNONYMS, AND SEQUENCES

To view the DDL code required to recreate a sequence, access the DBMS_METADATA view. If you’re using SQL*Plus to execute DBMS_METADATA, first ensure that you set the LONG variable: SQL> set long 5000

This example extracts the DDL for INV_SEQ:

SQL> select dbms_metadata.get_ddl('SEQUENCE','INV_SEQ') from dual; Here’s a snippet of the output:

CREATE SEQUENCE "INV_MGMT"."INV_SEQ"

MINVALUE 1 MAXVALUE 9999999999999999999999

999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE

If you want to display the DDL for all sequences for the currently connected user, run this SQL: SQL> select dbms_metadata.get_ddl('SEQUENCE',sequence_name) from user_sequences; You can also generate the DDL for a sequence owned by a particular user by providing the SCHEMA parameter:

select

dbms_metadata.get_ddl(object_type=>'SEQUENCE', name=>'INV_SEQ', schema=>'INV_APP') from dual;

Renaming a Sequence

Occasionally you may need to rename a sequence. For example, a sequence may have been created with an erroneous name, or you want to rename the sequence before dropping it from the database. Use the RENAME statement to do this. This example renames INV_SEQ to INV_SEQ_OLD: SQL> rename inv_seq to inv_seq_old;

You should see the following message:

Table renamed.

In this case, the message indicates that the sequence has been renamed.

Dropping a Sequence

To drop a sequence, use the DROP SEQUENCE statement:

SQL> drop sequence inv;

To reset a sequence number, you can drop the sequence and re-create it with the desired starting point. The following code drops a sequence and then re-creates it to start at the number 1: SQL> drop sequence cia_seq;

SQL> create sequence cia_seq start with 1;

209

CHAPTER 9 ■ VIEWS, SYNONYMS, AND SEQUENCES

■
Tip
See the next section on resetting a sequence for an alternative approach to dropping and re-creating a sequence.

Resetting a Sequence

In a couple of scenarios, you may be required to change the current value of a sequence number:

• Scenario 1: Your task is to create a development environment from a copy of production. You create the copy of production with a Data Pump export. During the export process, some sequences are exported before the tables are exported.

Because the application using the database doesn’t stop transacting during the export, it increments the sequence by several thousand numbers beyond what the exported sequence contains. When you do the import and begin selecting from the sequence, you realize that the current value of the imported sequence is too low and contains numbers that are already in use in the tables. To work around this, you need to increment your sequence(s) one time with a large value beyond that currently used by keys in the tables.

• Scenario 2: You have a test database and periodically need to truncate tables and reset the sequence back to zero.

These two scenarios are detailed in the following subsections.

Setting the Current Value of a Sequence to a Higher or Lower Value