The objects in Oracle you create are available only in your schema unless you grant access to the objects explicitly to other users. We’ll discuss privileges and user access in the next section. However, even when you grant permission to other users for using an object, the boundary created by schema ownership will force other users to prefix the object name with your schema name in order to access your object. For example, SCOTT owns the EMP table. If TURNER wants to access SCOTT’s EMP table, he must refer to EMP as SCOTT.EMP. If TURNER doesn’t, the following happens:

SELECT * FROM emp WHERE empno = 7844;

So, TURNER can’t even see his own employee data—in fact, Oracle tells him that the EMP table doesn’t even exist (pretty sneaky, eh?). Yet, as soon as TURNER prefixes the EMP table with its schema owner, SCOTT, a whole world of data opens up for TURNER, as you can see in the following code block:
SELECT empno, ename, sal FROM SCOTT.emp   2  WHERE empno = 7844;
If remembering which user owns which table seems unnecessarily complicated, synonyms can be used on the database for schema transparency. Synonyms are alternative names that can be created as database objects in Oracle to refer to a table or view. You can refer to a table owned by another user using synonyms. Creating a synonym eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, procedure, or other objects. Synonyms are also used to shorten lengthy object names.
Two types of synonyms exist in Oracle: private synonyms and public synonyms. You can use a private synonym within your own schema to refer to a table or view by an alternative name. Private synonyms are exactly that—they are private to your schema and therefore usable only by you. A private synonym name must be distinct from all other objects owned by the same user.
Think of private synonyms as giving you the ability to develop “pet names” for database objects in Oracle. You can use public synonyms to enable all users in Oracle to access database objects you own without having to prefix the object names with your schema name. This concept of referencing database objects without worrying about the schema the objects are part of is known as schema transparency. Public synonyms are publicly available to all users of Oracle; however, you need special privileges to create public synonyms. We’ll talk more about the privilege required for creating public synonyms in the next section. For now, the following code block demonstrates how to create private and public synonyms, respectively:
 create synonym all_my_emps for emp;
Tip     
Synonyms do not give you access to data in a table that you do not already have access to. Only privileges can do that. Synonyms simply enable you to refer to a table without prefixing the schema name to the table reference. When resolving a database table name, Oracle looks first to see whether the table exists in your schema. If Oracle doesn’t find the table, Oracle searches for a private synonym. If none is found, Oracle looks for a public synonym.
Drop Synonyms
Synonyms are dropped using the drop synonym command, as shown in the following code block:
Drop synonym emp;
A sequence is a database object that generates integers according to rules specified at the time the sequence is created. A sequence automatically generates unique numbers and is sharable between different users in Oracle. Sequences have many purposes in database systems—the most common of which is to generate primary keys automatically. However, nothing binds a sequence to a table’s primary key, so in a sense it’s also a sharable object
Sequences are created with the create sequence statement
CREATE SEQUENCE
START WITH
INCREMENT BY
MINVALUE
MAXVALUE
CYCLE
ORDER
CACHE

1. Start with n Enables the creator of the sequence to specify the first value generated by the sequence. Once created, the sequence will generate the value specified by start with the first time the sequence’s NEXTVAL virtual column is referenced. If no start with value is specified, Oracle defaults to a start value of 1.
2. Increment by n Defines the number by which to increment the sequence every time the NEXTVAL virtual column is referenced. The default for this clause is 1 if it is not explicitly specified. You can set n to be positive for incrementing sequences or negative for decrementing or countdown sequences.
3. Minvalue n Defines the minimum value that can be produced by the sequence. If no minimum value is specified, Oracle will assume the default, nominvalue.
4. Maxvalue n Defines the maximum value that can be produced by the sequence. If no maximum value is desired or specified, Oracle will assume the default, nomaxvalue.
5. Cycle Enables the sequence to recycle values produced when maxvalue or minvalue is reached. If cycling is not desired or not explicitly specified, Oracle will assume the default, nocycle. You cannot specify cycle in conjunction with nomaxvalue or nominvalue. If you want your sequence to cycle, you must specify maxvalue for incrementing sequences or minvalue for decrementing or countdown sequences.
6. Cache n Enables the sequence to cache a specified number of values to improve performance. If caching is not desired or not explicitly specified, Oracle will assume the default, which is to cache 20 values.
7. Order Enables the sequence to assign values in the order in which requests are received by the sequence. If order is not desired or not explicitly specified, Oracle will assume the default, noorder.
Example 1:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

This would create a sequence object called supplier_seq. The first sequence number that it would use is 1 and each subsequent number would increment by 1 (ie: 2,3,4,…}. It will cache up to 20 values for performance.
Example 2:
The below sequence is a dercrment one. It starts with 100 and decreases by 1.
CREATE SEQUENCE XX_Notification_number
START WITH 100
INCREMENT BY -1
MAXVALUE 100
MINVALUE 1
CYCLE
CACHE 20

Referencing Sequences in Data Changes
Sequence-value generation can be incorporated directly into data changes made by insert and update statements. This direct use of sequences in insert and update statements is the most common use for sequences in a database. In the situation where the sequence generates a primary key for all new rows entering the database table, the sequence would likely be referenced directly from the insert statement. Note, however, that this approach sometimes fails when the sequence is referenced by triggers. Therefore, it is best to reference sequences within the user interface or within stored procedures. The following statements illustrate the use of sequences directly in changes made to tables:
INSERT INTO expense(expense_no, empid, amt, submit_date)
VALUES(countdown_20.nextval, 59495, 456.34, ’21-NOV-99′);

SEQUENCE_NAME.NEXTVAL & SEQUENCE_NAME.CURRVAL
Once the sequence is created, it is referenced using the CURRVAL and NEXTVAL pseudocolumns. The users of the database can view the current value of the sequence by using a select statement. Similarly, the next value in the sequence can be generated with a select statement. Because sequences are not tables—they are only objects that generate integers via the use of virtual columns—the DUAL table acts as the “virtual” table from which the virtual column data is pulled. As stated earlier, values cannot be placed into the sequence; instead, they can only be selected from the sequen
Example 3:
Select XX_Notification_number.NEXTVAL from dual
Select XX_Notification_number.CURRVAL from dual
Alter sequence
The time may come when the sequence of a database will need its rules altered in some way. For example, you may want sequence XX_Notification_number to decrement by a different number. Any parameter of a sequence can be modified by issuing the alter sequence statement. The following is an example:

Alter Sequence sequence_name//Write new values of the sequence parameters
START WITH 100
INCREMENT BY -1
MAXVALUE 100
MINVALUE 1
CYCLE
CACHE 20

Example 4:
alter sequence XX_Notification_number
increment by -2;

Some of the objects that are part of the relational database produced by Oracle and that are used in the functions just mentioned are as follows:

  • Tables, views, and synonyms Used to store and access data. Tables are the basic unit of storage in Oracle. Views logically represent subsets of data from one or more tables. Synonyms provide alternate names for database objects.
  • Indexes and the Oracle RDBMS Used to speed access to data.
  • Sequences Used for generating numbers for various purposes.
  • Triggers and integrity constraints Used to maintain the validity of data entered.
  • Privileges, roles, and profiles Used to manage database access and usage.
  • Packages, procedures, and functions Application PL/SQL code used in the database.