Creating a View

You can create views on tables, materialized views, or other views. To create a view, your user account must have the CREATE VIEW system privilege. If you want to create a view in another user’s schema, then you must have the CREATE ANY VIEW privilege.

For reference, the view creation example in this section depends on the following base table:

create table sales(
 sales_id number primary key
,amnt    number
,state   varchar2(2)
,sales_person_id number);

Also assume that the table has the following data initially inserted into it:

insert into sales values(1, 222, 'CO', 8773);
insert into sales values(20, 827, 'FL', 9222);

Then, the CREATE VIEW statement is used to create a view. The following code creates a view (or replaces it if the view already exists) that selects a subset of columns and rows from the SALES table:

create or replace view sales_rockies as
select sales_id, amnt, state
from sales
where state in ('CO','UT','WY','ID','AZ'),

Now, when you select from SALES_ROCKIES, it executes the view query and returns data from the SALES table as appropriate:

SQL> select * from sales_rockies;

Given the view query, it’s intuitive that the output show only the following columns and one row:

  SALES_ID       AMNT ST
---------- ---------- --
         1        222 CO

What isn’t as apparent is that you can also issue UPDATE, INSERT, and DELETE statements against a view, which results in modification of the underlying table data. For example, the following insert statement against the view results in the insertion of a record in the SALES table:

insert into sales_rockies(
 sales_id, amnt, state)
values
(2,100,'CO'),

Additionally, as the owner of the table and view (or as a DBA), you can grant DML privileges to other users on the view. For instance, you can grant SELECT, INSERT, UPDATE, and DELETE privileges on the view to another user, which will allow the user to select and modify data referencing the view. However, having privileges on the view does not give the user direct SQL access to the underlying table(s).

Thus, any users granted privileges on the view will be able to manipulate data through the view but not issue SQL against the objects the view is based on.

Note that you can insert a value into the view that results in a row in the underlying table that isn’t selectable by the view:

insert into sales_rockies(
 sales_id, amnt, state)
values (3,123,'CA'),
  
SQL> select * from sales_rockies;
  
  SALES_ID       AMNT ST
---------- ---------- --
         1        222 CO
         2        100 CO

In contrast, the query on the underlying table shows that rows exist that aren’t returned by the view:

SQL> select * from sales;
  
  SALES_ID       AMNT ST SALES_PERSON_ID
---------- ---------- -- ---------------
         1        222 CO            8773
        20        827 FL            9222
         2        100 CO
         3        123 CA

If you want the view to only allow insert and update statements that result in data modifications that are selectable by the view statement, then use the WITH CHECK OPTION (see the next section, “Checking Updates”).

Checking Updates

You can specify that a view should allow modifications to the underlying table data only if those data are selectable by the view. This behavior is enabled with the WITH CHECK OPTION:

create or replace view sales_rockies as
select sales_id, amnt, state
from sales
where state in ('CO','UT','WY','ID','AZ')
with check option;

Using the WITH CHECK OPTION means that you can only insert or update rows that would be returned by the view query. For example, this UPDATE statement works because the statement isn’t changing the underlying data in a way would result in the row’s not being returned by the view query:

SQL> update sales_rockies set state='ID' where sales_id=1;

However, this next update statement fails because it attempts to update the STATE column to a value that isn’t selectable by the query on which the view is based:

SQL> update sales_rockies set state='CA' where sales_id=1;

In this example, the following error is thrown:

ORA-01402: view WITH CHECK OPTION where-clause violation

I’ve rarely seen the WITH CHECK OPTION used. Having said that, if your business requirements mandate that updatable views only have the ability to update data selectable by the view query, then, by all means, use this feature.

Creating Read-Only Views

If you don’t want a user to be able to perform INSERT, UPDATE, or DELETE operations on a view, then don’t grant those object privileges on the view to that user. Furthermore, you should also create a view with the WITH READ ONLY clause for any views for which you don’t want the underlying tables to be modified. The default behavior is that a view is updatable (assuming the object privileges exist).

This example creates a view with the WITH READ ONLY clause:

create or replace view sales_rockies as
select sales_id, amnt, state
from sales
where state in ('CO','UT','WY','ID','AZ')
with read only;

Even if a user (including the owner) has privileges to delete, insert, or update the underlying table, if such an operation is attempted, the following error is thrown:

ORA-42399: cannot perform a DML operation on a read-only view

If you use views for reporting, and never intend for the views to be used as a mechanism for modifying the underlying table’s data, then you should always create the views with the WITH READ ONLY clause. Doing so prevents accidental modifications to the underlying tables through a view that was never intended to be used to modify data.

Updatable Join Views

If you have multiple tables defined in the FROM clause of the SQL query on which the view is based, it’s still possible to update the underlying tables. This is known as an updatable join view.

For reference purposes, here are the CREATE TABLE statements for the two tables used in the examples in this section:

create table emp(
 emp_id number primary key
,emp_name varchar2(15)
,dept_id number);
--
create table dept(
 dept_id number primary key
,dept_name varchar2(15),
 constraint emp_dept_fk
 foreign key(dept_id) references dept(dept_id));

And, here are some seed data for the two tables:

insert into dept values(1,'HR'),
insert into dept values(2,'IT'),
insert into dept values(3,'SALES'),
insert into emp values(10,'John',2);
insert into emp values(20,'Bob',1);
insert into emp values(30,'Craig',2);
insert into emp values(40,'Joe',3);
insert into emp values(50,'Jane',1);
insert into emp values(60,'Mark',2);

Here is an example of an updatable join view, based on the two prior base tables:

create or replace view emp_dept_v
as
select a.emp_id, a.emp_name, b.dept_name, b.dept_id
from emp a, dept b
where a.dept_id = b.dept_id;

There are some restrictions regarding the columns on which DML operations are permitted. For instance, columns in the underlying tables can be updated only if the following conditions are true:

• The DML statement must modify only one underlying table.
• The view must be created without the READ ONLY clause.
• The column being updated belongs to the key-preserved table in the join view (there is only one key-preserved table in a join view).

An underlying table in a view is key preserved if the table’s primary key can also be used to uniquely identify rows returned by the view. An example with data will help illustrate whether an underlying table is key preserved. In this scenario the primary key of the EMP table is the EMP_ID column; the primary key of the DEPT table is the DEPT_ID column. Here are some sample data returned by querying the view listed previously in this section:

    EMP_ID EMP_NAME        DEPT_NAME          DEPT_ID
---------- --------------- --------------- ----------
        10 John            IT                       2
        20 Bob             HR                       1
        30 Craig           IT                       2
        40 Joe             SALES                    3
        50 Jane            HR                       1
        60 Mark            IT                       2

As you can see from the output of the view, the EMP_ID column is always unique. Therefore, the EMP table is key preserved (and its columns can be updated). In contrast, the view’s output shows that it’s possible for the DEPT_ID column to be not unique. Therefore, the DEPT table isn’t key preserved (and its columns can’t be updated).

When you update the view, any modifications that result in columns that map to the underlying EMP table should be allowed because the EMP table is key preserved in this view. For example, this UPDATE statement is successful:

SQL> update emp_dept_v set emp_name = 'Jon' where emp_name = 'John';

However, statements that result in updating the DEPT table’s columns aren’t allowed. The next statement attempts to update a column in the view that maps to the DEPT table:

SQL> update emp_dept_v set dept_name = 'HR West' where dept_name = 'HR';

Here is the resulting error message that’s thrown:

ORA-01779: cannot modify a column which maps to a non key-preserved table

To summarize, an updatable join view can select from many tables, but only one of the tables in the join view is key preserved. The primary key and foreign key relationships of the tables in the query determine which table is key preserved.

Creating an INSTEAD OF Trigger

For views that aren’t read-only, when you issue a DML statement against a view, Oracle attempts to modify the data in the table that the view is based on. It’s also possible to instruct Oracle to ignore the DML statement and instead execute a block of PL/SQL. This feature is known as an INSTEAD OF trigger. It allows you to modify the underlying base tables in ways that you can’t with regular join views.

I’m not a huge fan of INSTEAD OF triggers. In my opinion, if you’re considering using them, you should rethink how you’re issuing DML statements to modify base tables. Maybe you should allow the application to issue INSERT, UPDATE, and DELETE statements directly against the base tables instead of trying to build PL/SQL INSTEAD OF triggers on a view.

Think about how you’ll maintain and troubleshoot issues with INSTEAD OF triggers. Will it be difficult for the next DBA to figure out how the base tables are being modified? Will it be easy for the next DBA or developer to make modifications to the INSTEAD OF triggers? When an INSTEAD OF trigger throws an error, will it be obvious what code is throwing the error and how to resolve the problem?

Having said that, if you determine that you require an INSTEAD OF trigger on a view, use the INSTEAD OF clause to create it, and embed within it the required PL/SQL. This example creates an INSTEAD OF trigger on the EMP_DEPT_V view:

create or replace trigger emp_dept_v_updt
instead of update on emp_dept_v
for each row
begin
  update emp set emp_name=UPPER(:new.emp_name)
  where emp_id=:old.emp_id;
end;
/

Now, when an update is issued against EMP_DEPT_V, instead of the DML’s being executed, Oracle intercepts the statement and runs the INSTEAD OF PL/SQL code; for example,

SQL> update emp_dept_v set emp_name='Jonathan' where emp_id = 10;
1 row updated.

Then, you can verify that the trigger correctly updated the table by selecting the data:

SQL> select * from emp_dept_v;
  
    EMP_ID EMP_NAME        DEPT_NAME          DEPT_ID
---------- --------------- --------------- ----------
        10 JONATHAN        IT                       2
        20 Bob             HR                       1
        30 Craig           IT                       2
        40 Joe             SALES                    3
        50 Jane            HR                       1
        60 Mark            IT                       2

This code is a simple example, but it illustrates that you can have PL/SQL execute instead of the DML that was run on the view. Again, be careful when using INSTEAD OF triggers; be sure you’re confident that you can efficiently diagnose and resolve any related issues that may arise.

Implementing an Invisible Column

Starting with Oracle Database 12c, you can create or modify a column in a table or view to be invisible (see Chapter 7 for details on adding an invisible column to a table). One good use for an invisible column is to ensure that adding a column to a table or view won’t disrupt any of the existing application code. If the application code doesn’t explicitly access the invisible column, then it appears to the application as if the column doesn’t exist.

A small example will demonstrate the usefulness of an invisible column. Suppose you have a table created and populated with some data as follows:

create table sales(
 sales_id number primary key
,amnt    number
,state   varchar2(2)
,sales_person_id number);
--
insert into sales values(1, 222, 'CO', 8773);
insert into sales values(20, 827, 'FL', 9222);

And, furthermore, you have a view based on the prior table, created as shown:

create or replace view sales_co as
select sales_id, amnt, state
from sales where state = 'CO';

For the purpose of this example, suppose you also have a reporting table such as this:

create table rep_co(
 sales_id number
,amnt    number
,state   varchar2(2));

And, it is populated with this insert statement, which uses SELECT *:

SQL> insert into rep_co select * from sales_co;

Sometime later, a new column is added to the view:

create or replace view sales_co as
select sales_id, amnt, state, sales_person_id
from sales where state = 'CO';

Now, consider what happens to the statement that is inserting into REP_CO. Because it uses a SELECT *, it breaks because there hasn’t been a corresponding column added to the REP_CO table:

SQL> insert into rep_co select * from sales_co;
  
ORA-00913: too many values

The prior insert statement no longer is able to populate the REP_CO table because the statement doesn’t account for the additional column that has been added to the view.

Now, consider the same scenario, but with the column added to the SALES_CO view with an invisible column:

 create or replace view sales_co
(sales_id, amnt, state, sales_person_id invisible)
as
select
 sales_id, amnt, state, sales_person_id
from sales
where state = 'CO';

When a view column is defined as invisible, this means that the column will not show up when describing the view or in the output of SELECT *. This ensures that the insert statement based on a SELECT * will continue to work.

One could successfully argue that you should never create an insert statement based on a SELECT * and that you therefore would never encounter this issue. Or, one could argue that the REP_CO table in this example should also have a column added to it to avoid the problem. However, when working with third-party applications, you oftentimes have no control over poorly written code. In this scenario, you can add an invisible column to a view without fear of breaking any existing code.

Having said that, the invisible column isn’t entirely invisible. If you know the name of an invisible column, you can select from it directly; for example,

SQL> select sales_id, amnt, state, sales_person_id from sales_co;

In this sense, the invisible column is only unseen to poorly written application code or to users that don’t know the column exists.

Modifying a View Definition

If you need to modify the SQL query on which a view is based, then either drop and recreate the view, or use the CREATE OR REPLACE syntax, as in the previous examples. For instance, say you add a REGION column to the SALES table:

SQL> alter table sales add (region varchar2(30));

Now, to add the REGION column to the SALES_ROCKIES view, run the following command to replace the existing view definition:

create or replace view sales_rockies as
select sales_id, amnt, state, region
from sales
where state in ('CO','UT','WY','ID','AZ')
with read only;

The advantage of using the CREATE OR REPLACE method is that you don’t have to reestablish access to the view for users with previously granted permissions. The alternative to CREATE OR REPLACE is to drop and recreate the view with the new definition. If you drop and recreate the view, you must regrant privileges to any users or roles that were previously granted access to the dropped and recreated object. For this reason, I almost never use the drop-and-recreate method when altering the structure of a view.

What happens if you remove a column from a table, and there is a corresponding view that references the removed column?; for example,

SQL> alter table sales drop (region);

If you attempt to select from the view, you’ll receive an ORA-04063 error. When modifying underlying tables, you can check to see if a view is affected by the table change by compiling the view; for example,

SQL> alter view sales_rockies compile;
  
Warning: View altered with compilation errors.

In this way, you can proactively determine whether or not a table change affects dependent views. In this situation, you should recreate the view sans the dropped table column:

create or replace view sales_rockies as
select sales_id, amnt, state
from sales
where state in ('CO','UT','WY','ID','AZ')
with read only;

Displaying the SQL Used to Create a View

Sometimes, when you’re troubleshooting issues with the information a view returns, you need to see the SQL query on which the view is based. The view definition is stored in the TEXT column of the DBA/USER/ALL_VIEWS views. Note that the TEXT column of the DBA/USER/ALL_VIEWS views is a LONG data type and that, by default, SQL*Plus only shows 80 characters of this type. You can set it longer, as follows:

SQL> set long 5000

Now, use the following script to display the text associated with a particular view for a user:

select view_name, text
from dba_views
where owner = upper('&owner')
and view_name like upper('&view_name'),

You can also query ALL_VIEWS for the text of any view you have access to:

select text
from all_views
where owner='MV_MAINT'
and view_name='SALES_ROCKIES';

If you want to display the view text that exists within your schema, use USER_VIEWS:

select text
from user_views
where view_name=upper('&view_name'),

You can also use the DBMS_METADATA package’s GET_DDL function to display a view’s code. The data type returned from GET_DDL is a CLOB; therefore, if you run it from SQL*Plus, make sure you first set your LONG variable to a sufficient size to display all the text. For example, here is how to set LONG to 5,000 characters:

SQL> set long 5000

Now, you can display the view definition by invoking DBMS_METADATA.GET_DDL with a SELECT statement, as follows:

SQL> select dbms_metadata.get_ddl('VIEW','SALES_ROCKIES') from dual;

If you want to display the DDL for all views for the currently connected user, run this SQL:

SQL> select dbms_metadata.get_ddl('VIEW', view_name) from user_views;

Renaming a View

There are a couple of good reasons to rename a view. You may want to change the name so that it better conforms to standard, or you may want to rename a view before dropping it so that you can better determine whether it’s in use. Use the RENAME statement to change the name of a view. This example renames a view:

SQL> rename sales_rockies to sales_rockies_old;

You should see this message:

Table renamed.

The prior message would make more sense if it said, “View renamed”; just be aware that the message, in this case, doesn’t exactly match the operation.

Dropping a View

Before you drop a view, consider renaming it. If you’re certain that a view isn’t being used anymore, then it makes sense to keep your schema as clean as possible and drop any unused objects. Use the DROP VIEW statement to drop a view:

SQL> drop view sales_rockies_old;

Keep in mind that when you drop a view, any dependent views, materialized views, and synonyms become invalid. Additionally, any grants associated with the dropped view are also removed.

Creating a Synonym

A user must be granted the CREATE SYNONYM system privilege before creating a synonym. Once that privilege is granted, use the CREATE SYNONYM command to create an alias for another database object. You can specify CREATE OR REPLACE SYNONYM if you want the statement to create the synonym, if it doesn’t exist, or replace the synonym definition, if it does. This is usually acceptable behavior.

In this example, a synonym will be created that provides access to a view. First, the owner of the view must grant select access to the view. Here, the owner of the view is MV_MAINT:

SQL> show user;
USER is "MV_MAINT"
  
SQL> grant select on sales_rockies to app_user;

Next, connect to the database as the user that will create the synonym.

SQL> conn app_user/foo

While connected as APP_USER, a synonym is created that points to a view named SALES_ROCKIES, owned by MV_MAINT:

SQL> create or replace synonym sales_rockies for mv_maint.sales_rockies;

Now, the APP_USER can directly reference the SALES_ROCKIES view:

SQL> select * from sales_rockies;

With the CREATE SYNONYM command, if you don’t specify OR REPLACE (as shown in the example), and the synonym already exists, then an ORA-00955 error is thrown. If it’s okay to overwrite any prior existing synonym definitions, then specify the OR REPLACE clause.

The creation of the synonym doesn’t also create the privilege to access an object. Such privileges must be granted separately, usually before you create the synonym (as shown in the example).

By default, when you create a synonym, it’s a private synonym. This means that it’s owned by the user that created the synonym and that other users can’t access it unless they’re granted the appropriate object privileges.

Creating Public Synonyms

You can also define a synonym as public (see the previous section, “Creating a Synonym,” for a discussion of private synonyms), which means that any user in the database has access to the synonym. Sometimes, an inexperienced DBA does the following:

SQL> grant all on sales to public;
SQL> create public synonym sales for mv_maint.sales;

Now, any user that can connect to the database can perform any INSERT, UPDATE, DELETE, or SELECT operation on the SALES table that exists in the MV_MAINT schema. You may be tempted to do this so that you don’t have to bother setting up individual grants and synonyms for each schema that needs access. This is almost always a bad idea. There are a few issues with using public synonyms:

• Troubleshooting can be problematic if you’re not aware of globally defined (public) synonyms; DBAs tend to forget or are unaware that public synonyms were created.
• Applications that share one database can have collisions on object names if multiple applications use public synonyms that aren’t unique within the database.
• Security should be administered as needed, not on a wholesale basis.

I usually try to avoid using public synonyms. However, there may be scenarios that warrant their use. For example, when Oracle creates the data dictionary, public synonyms are used to simplify the administration of access to internal database objects. To display any public synonyms in your database, run this query:

select owner, synonym_name
from dba_synonyms
where owner='PUBLIC';

Dynamically Generating Synonyms

Sometimes, it’s useful to dynamically generate synonyms for all tables or views for a schema that needs private synonyms. The following script uses SQL*Plus commands to format and capture the output of an SQL script that generates synonyms for all tables within a schema:

CONNECT &&master_user/&&master_pwd
--
SET LINESIZE 132 PAGESIZE 0 ECHO OFF FEEDBACK OFF
SET VERIFY OFF HEAD OFF TERM OFF TRIMSPOOL ON
--
SPO gen_syns_dyn.sql
--
select 'create or replace synonym ' || table_name ||
       ' for ' || '&&master_user..' ||
  table_name || ';'
from user_tables;
--
SPO OFF;
--
SET ECHO ON FEEDBACK ON VERIFY ON HEAD ON TERM ON;

Look at the &master_user variable with the two dots appended to it in the SELECT statement: what is the purpose of double-dot syntax? A single dot at the end of an ampersand variable instructs SQL*Plus to concatenate anything after the single dot to the ampersand variable. When you place two dots together, that tells SQL*Plus to concatenate a single dot to the string contained in the ampersand variable.

Displaying Synonym Metadata

The DBA/ALL/USER_SYNONYMS views contain information about synonyms in the database. Use the following SQL to view synonym metadata for the currently connected user:

select synonym_name, table_owner, table_name, db_link
from user_synonyms
order by 1;

The ALL_SYNONYMS view displays all private synonyms, all public synonyms, and any private synonyms owned by different users for which your currently connected user has select access to the underlying base table. You can display information for all private and public synonyms in your database by querying the DBA_SYNONYMS view.

The TABLE_NAME column in the DBA/ALL/USER_SYNONYMS views is a bit of a misnomer because TABLE_NAME can reference many types of database objects, such as another synonym, view, package, function, procedure, or materialized view. Similarly, TABLE_OWNER refers to the owner of the object (and that object may not necessarily be a table).

When you’re diagnosing data integrity issues, sometimes you first want to identify what table or object is being accessed. You can select from what appears to be a table, but in reality it may be a synonym that points to a view that selects from a synonym, which in turn points to a table in a different database.

The following query is often a starting point for figuring out whether an object is a synonym, a view, or a table:

select owner, object_name, object_type, status
from dba_objects
where object_name like upper('&object_name%'),

Note that using the wildcard character the percentage sign (%) in this query allows you to enter the object’s partial name. Therefore, the query has the potential to return information regarding any object that partially matches the text string you enter.

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> set long 5000
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 G ET_DDL function the object type, object name, and schema:

SQL> select dbms_metadata.get_ddl(object_type=>'SYNONYM',
             name=>'SALES_ROCKIES', schema=>'APP_USER') from dual;

Renaming a Synonym

You may want to rename a synonym so that it conforms to naming standards or in order to determine whether it’s being used. Use the RENAME statement to change the name of a synonym:

SQL> rename inv_s to inv_st;

Note that the output displays this message:

Table renamed.

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

Dropping a Synonym

If you’re certain that you no longer need a synonym, then you can drop it. Unused synonyms can be confusing to others called on to enhance or debug existing applications. 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;

If successful, you should see this message:

Synonym dropped.

Creating a Sequence

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

SQL> create sequence inv_seq;

By default the starting number is 1, the increment is 1, the default number of sequences cached in memory is 20, and the maximum value is 10^27. You can verify the default values via this query:

select sequence_name, min_value, increment_by, cache_size, max_value
from user_sequences;

You have a great deal of latitude in changing aspects of a sequence when creating it. For example, this command creates a sequence with a starting value of 1,000 and a maximum value of 1,000,000:

SQL> create sequence inv_seq2 start with 10000 maxvalue 1000000;

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

Using Sequence Pseudocolumns

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

• NEXTVAL
• CURRVAL

You can reference these pseudocolumns 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;

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

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.” Prior to Oracle Database 12c, I would reply something like, “Oracle has no such feature. If you have an issue with this, please send an e-mail to Larry at . . .” Or, I would inform the developer that  he or she could either use the sequence number directly in an INSERT statement (as shown in the previous section, “Using Sequence Pseudocolumns”) or select the sequence value into a variable and then reference the variable as needed.

If you’re not able to use the identity column feature in Oracle Database 12c, then you can simulate this automatic incrementing functionality by using triggers. For  instance, 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'),

Select from the table to verify that the INV_ID column is indeed populated automatically by the sequence:

SQL> select * from inv;
    INV_ID INV_DESC
---------- ------------------------------
         1 Book
         2 Pen

I generally don’t like using this technique. Yes, it makes it easier for the developers, in that they don’t have to worry about populating the key columns. However, it’s more work for the DBA to generate the code required to maintain the columns to be automatically populated. Because I’m the DBA, and I like to keep the database code that I maintain as simple as possible, I usually tell the developers that we aren’t using this autoincrementing column approach and that we’ll instead use the technique of directly calling the sequence in the DML statements (as shown in the previous section, “Using Sequence Pseudocolumns”).

Implementing Multiple Sequences That Generate Unique Values

I once had a developer ask if it was possible to create multiple sequences for an application and to guarantee that each sequence would generate numbers unique across all sequences. If you have this type of requirement, you can handle it a few different ways:

• If you’re feeling grumpy, tell the developer that it’s not possible and that the standard is to use one sequence per application (this is usually the approach I take).
• Set sequences to start and increment at different points.
• Use ranges of sequence numbers.

If you’re not feeling grumpy, you can set up a small, finite number of sequences that always generate unique values by specifying an odd or even starting number and then incrementing the sequence by two. For example, you can set up two odd and two even sequence generators; for example,

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 by its ability 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.

Creating 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 foreign key columns for each table or just 1 sequence.

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

Sometimes, developers raise issues such as

• performance problems with only 1 sequence
• sequence numbers that become 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 one, you’ll never reach the maximum value (at least, not in this lifetime).

However, in scenarios in which you’re generating surrogate keys for the primary and child tables, it’s sometimes convenient to use more than 1 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. This 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;

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;

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 instance, a sequence may have been created with an erroneous name, or you may 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, even though the message says, “Table renamed,” it was the sequence that was renamed.

Dropping a Sequence

Usually, you want a drop a sequence either because it’s not used or you want to recreate it with a new starting number. To drop a sequence, use the DROP SEQUENCE statement:

SQL> drop sequence inv_seq;

When an object is dropped, all the associated grants on the object are dropped as well. So, if you need to recreate the sequence, then remember to reissue select grants to other users that may need to use the sequence.

Resetting a Sequence

You may occasionally be required to change the current value of a sequence number. For example, you may work in a test environment in which the developers periodically want the database reset to a previous state. A typical scenario is one in which the developers have scripts that truncate the tables and reseed them with test data and, as part of that exercise, want a sequence set back to a value such as 1.

Oracle’s documentation states, “to restart a sequence at a different number, you must drop and re-create it.” That’s not entirely accurate. In most cases, you should avoid dropping a sequence because you must regrant permissions on the object to users that currently have select permissions on the sequence. This can lead to temporary downtime for your application while you track down those users.

The following technique demonstrates how to set the current value to a higher or lower value, using the ALTER SEQUENCE statement. The basic procedure is as follows:

1. Alter INCREMENT BY to a large number.
2. Select from the sequence to increment it by the large positive or negative value.
3. Set INCREMENT BY back to its original value (usually 1).

This example sets the next value of a sequence number to 1,000 integers higher than the current value:

SQL> alter sequence myseq increment by 1000;
SQL> select myseq.nextval from dual;
SQL> alter sequence myseq increment by 1;

Verify that the sequence is set to the value you desire:

SQL> select myseq.nextval from dual;

You can also use this technique to set the sequence number to a much lower number than the current value. The difference is that the INCREMENT BY setting is a large negative number. For example, this example sets the sequence back 1,000 integers:

SQL> alter sequence myseq increment by -1000;
SQL> select myseq.nextval from dual;
SQL> alter sequence myseq increment by 1;

Verify that the sequence is set to the value you desire:

SQL> select myseq.nextval from dual;

Additionally, you can automate the task of resetting a sequence number back to a value via an SQL script. This technique is shown in the next several lines of SQL code. The code will prompt you for the sequence name and the value you want the sequence set back to:

UNDEFINE seq_name
UNDEFINE reset_to
PROMPT "sequence name" ACCEPT '&&seq_name'
PROMPT "reset to value" ACCEPT &&reset_to
COL seq_id NEW_VALUE hold_seq_id
COL min_id NEW_VALUE hold_min_id
--
SELECT &&reset_to - &&seq_name..nextval - 1 seq_id
FROM dual;
--
SELECT &&hold_seq_id - 1 min_id FROM dual;
--
ALTER SEQUENCE &&seq_name INCREMENT BY &hold_seq_id MINVALUE &hold_min_id;
--
SELECT &&seq_name..nextval FROM dual;
--
ALTER SEQUENCE &&seq_name INCREMENT BY 1;

To ensure that the sequence has been set to the value you want, select the NEXTVAL from it:

SQL> select &&seq_name..nextval from dual;

This approach can be quite useful when you’re moving applications through various development, test, and production environments. It allows you to reset the sequence without having to reissue object grants.