Pro Oracle Database 11g Administration (40 page)

Oracle’s documentation states, “to restart a sequence at a different number, you must drop and recreate it.” That’s not entirely accurate (as this section shows). In most cases, you should avoid dropping a sequence because you must re-grant 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 users that need to be re-granted select permission.

The technique in this section 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 1000 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;

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 sets the sequence back 1000 integers:

SQL> alter sequence myseq increment by -1000;

SQL> select myseq.nextval from dual;

SQL> alter sequence myseq increment by 1;

210

CHAPTER 9 ■ VIEWS, SYNONYMS, AND SEQUENCES

Resetting a Sequence Back to a Lower Value

Sometimes you need to reset a sequence back to a starting point. For example, you may have a test database in which you occasionally truncate all the table data and reset the sequence the application uses back to 1. Oracle doesn’t provide a way to reset a sequence. You have two options in this situation:

• Drop the sequence and re-create it at the desired starting number.

• As a one-time operation, alter the sequence to increment by a large negative number, increment it once by that number, and then set the increment value back to a positive 1.

To quickly reset a sequence, you can drop and re-create it:

SQL> drop sequence myseq;

SQL> create sequence myseq;

The default starting number for a sequence is 1:

SQL> select myseq.nextval from dual;

NEXTVAL

----------

1

Or you can re-create the sequence with a starting value. For example, this creates a sequence starting with the number 1000:

SQL> create sequence myseq start with 1000;

One side effect of dropping and re-creating a sequence is that if other schemas access the sequence, you must re-grant them select access to the sequence. This is because when you drop an object, any grants that were associated with that object are also dropped. Dropping and re-creating a sequence like this can have an adverse impact on your application if you haven’t done a good job of determining which users need access to the dropped and re-created sequence.

An alternative approach to dropping and re-creating the sequence is to alter the sequence’s INCREMENT BY value to one integer below where you want to reset it, and then alter the sequence’s INCREMENT BY value to 1. Doing so effectively resets the sequence without having to drop and re-create it and removes the need to re-grant select access to the sequence. This technique is shown in the next several lines of SQL code:

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;

--

211

CHAPTER 9 ■ VIEWS, SYNONYMS, AND SEQUENCES

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.

Summary

Views, synonyms, and sequences are used extensively in Oracle database applications. These objects (along with tables and indexes) provide the technology for creating sophisticated applications.

Views provide a way to create and store complex multi-table join queries that can then be used by databases users and applications. Views can be used to update the underlying base tables or can be created read-only for reporting requirements.

Synonyms (along with appropriate privileges) provide a mechanism to transparently allow a user to access objects that are owned by a separate schema. The user accessing a synonym only needs to know the synonym name, regardless of the underlying object type and owner. This lets the application designer seamlessly separate the owner of the objects from the users that access the objects.

Sequences generate unique integers that are often used by applications to populate primary-key and foreign-key columns. Oracle guarantees that when a sequence is accessed, it will always return a unique value to the selecting user.

After installing the Oracle binaries and creating a database and tablespaces, usually you create an application that consists of the owning user and corresponding tables, constraints, indexes, views, synonyms, and sequences. Metadata regarding these objects is stored internally in the data dictionary.

The data dictionary is used extensively for monitoring, troubleshooting, and diagnosing issues. You must be thoroughly fluent with retrieving information from the data dictionary. Retrieving and analyzing data-dictionary information is the topic of the next chapter.

212

CHAPTER 10 ■ DATA DICTIONARY BASICS

SQL> set long 5000

SQL> select text from dba_views where view_name='DBA_VIEWS';

Here’s the output:

select u.name, o.name, v.textlength, v.text, t.typetextlength, t.typetext, t.oidtextlength, t.oidtext, t.typeowner, t.typename,

decode(bitand(v.property, 134217728), 134217728,

(select sv.name from superobj$ h, "_CURRENT_EDITION_OBJ" sv where h.subobj# = o.obj# and h.superobj# = sv.obj#), null),

decode(bitand(v.property, 32), 32, 'Y', 'N'),

decode(bitand(v.property, 16384), 16384, 'Y', 'N')

from sys."_CURRENT_EDITION_OBJ" o, sys.view$ v, sys.user$ u, sys.typed_view$ t where o.obj# = v.obj#

and o.obj# = t.obj#(+)

and o.owner# = u.user#

■
Note
If you manually create a database (not using the database-creation assistant), you must be connected as the SYS schema when you run the catalog.sql and catproc.sql scripts. The SYS schema is the owner of all objects in the data dictionary.

Dynamic Performance Views

The dynamic performance data-dictionary views are often referred to as the V$ and GV$ views. These views are constantly updated by Oracle and reflect the current condition of the instance and database.

Dynamic views are critical for diagnosing real-time performance issues.

The V$ and GV$ views are indirectly based on the underlying X$ tables, which are internal memory structures that are instantiated when you start your Oracle instance. Some of the V$ views are available the moment the Oracle instance is started. For example, V$PARAMETER contains meaningful data after the STARTUP NOMOUNT command has been issued, and doesn’t require the database to be mounted or open.

Other dynamic views depend on information in the control file and therefore contain meaningful information only after the database has been mounted (like V$CONTROLFILE). Some V$ views provide kernel-processing information (like V$BH) and thus have useful results only after the database has been opened.

At the top layer, the V$ views are actually synonyms that point to underlying SYS.V_$ views. At the next layer down, the SYS.V_$ objects are views created on top of another layer of SYS.V$ views. The SYS.V$ views in turn are based on the SYS.GV$ views. At the bottom layer, the SYS.GV$ views are based on the X$ memory structures.

The top-level V$ synonyms and SYS.V_$ views are created when you run the catalog.sql script, which you usually run after the database is initially created. Figure 10–2 shows the process for creating the V$ dynamic performance views.

215

CHAPTER 10 ■ DATA DICTIONARY BASICS

If you know roughly the name of the view from which you want to select information, you can first query from DBA_OBJECTS. For example, if you’re troubleshooting an issue regarding materialized views, and you can’t remember the exact names of the data-dictionary views associated with materialized views, you can do this:

select

object_name

from dba_objects

where object_name like '%MV%'

and owner='SYS';

Sometimes that’s enough to get you in the ballpark. But often you need more information about each view. This is where the DICTIONARY and DICT_COLUMNS views can be invaluable. The DICTIONARY view stores the name of the data-dictionary views. It has two columns:

SQL> desc dictionary

Name Null? Type

----------------------------------------- -------- ----------------------------

TABLE_NAME VARCHAR2(30)

COMMENTS VARCHAR2(4000)

For example, say you’re troubleshooting an issue with materialized views, and you want to determine the name of data-dictionary views related to the materialized-view feature. You can run a query such as this:

select

table_name

,comments

from dictionary where table_name like '%MV%';

Here’s a short snippet of the output:

TABLE_NAME COMMENTS

------------------------- -------------------------------------------------------

DBA_MVIEW_LOGS All materialized view logs in the database

DBA_MVIEWS All materialized views in the database

DBA_MVIEW_ANALYSIS Description of the materialized views accessible to dba DBA_MVIEW_COMMENTS Comments on all materialized views in the database In this manner, you can quickly determine which view you need to access. If you want further information about the view, you can describe it. For example:

SQL> desc dba_mviews

If that doesn’t provide you with enough information regarding the column names, you can query the DICT_COLUMNS view. This view provides comments about the columns of a data-dictionary view. For example:

select

column_name

,comments

from dict_columns

where table_name='DBA_MVIEWS';

Here’s a fraction of the output:

COLUMN_NAME COMMENTS

217