Update user_synonyms oracle

Create Synonym or Replace You may wish to create a synonym so that users do not have to prefix the table name with the schema name when using the table in a query. Remember though that the user must first have the appropriate privileges to the object to use the synonym. If this phrase is omitted, Oracle assumes that you are referring to your own schema. It can be one of the following: table view sequence stored procedure function package materialized view java class schema object user-defined object synonym Example Let's look at an example of how to create a synonym in Oracle.

With some restrictions, rows can be inserted into, updated in, or deleted from a base table using a view. Restrictions on DML operations for views use the following criteria in the order listed:. When a user attempts to reference an invalid view, the database returns an error message to the user:. This error message is returned when a view exists but is unusable due to errors in its query whether it had errors when originally created or it was created successfully but became unusable later because underlying objects were altered or dropped.

The rules for updatable join views are shown in the following table. Views that meet these criteria are said to be inherently updatable.

There are data dictionary views that indicate whether the columns in a join view are inherently updatable. Additionally, if a view is a join on other nested views, then the other nested views must be mergeable into the top level view. For a discussion of mergeable and unmergeable views, and more generally, how the optimizer optimizes statements that reference views, see the Oracle Database Performance Tuning Guide.

Examples illustrating the rules for inherently updatable join views, and a discussion of key-preserved tables, are presented in following sections. The examples in these sections work only if you explicitly define the primary and foreign keys in the tables, or define unique indexes. The following statements create the appropriately constrained table definitions for emp and dept. The concept of a key-preserved table is fundamental to understanding the restrictions on modifying join views.

A table is key-preserved if every key of the table can also be a key of the result of the join. So, a key-preserved table has its keys preserved through a join. The key-preserving property of a table does not depend on the actual data in the table.

It is, rather, a property of its schema. For example, if in the emp table there was at most one employee in each department, then deptno would be unique in the result of a join of emp and dept , but dept would still not be a key-preserved table.

In this view, emp is a key-preserved table, because empno is a key of the emp table, and also a key of the result of the join. In general, all updatable columns of a join view must map to columns of a key-preserved table. You can delete from a join view provided there is one and only one key-preserved table in the join.

The key-preserved table can be repeated in the FROM clause. In the following view, a DELETE operation is permitted, because although there are two key-preserved tables, they are the same table. That is, the key-preserved table is repeated. In this case, the delete statement operates on the first table in the FROM list e1 , in this example :. This statement works because only one key-preserved base table is being modified emp , and 40 is a valid deptno in the dept table thus satisfying the FOREIGN KEY integrity constraint on the emp table.

In this view, emp is no longer a key-preserved table, because the empno column in the result of the join can have nulls the last row in the preceding SELECT statement. In the case of views containing an outer join on other nested views, a table is key preserved if the view or views containing the table are merged into their outer views, all the way to the top. A view which is being outer-joined is currently merged only if it is "simple. For example:. The views described in the following table can assist you to identify inherently updatable join views.

If you want to change the definition of a view, see "Replacing Views". To ensure that the alteration does not affect the view or other objects that depend on it, you can explicitly recompile a view after altering one of its base tables.

You can drop any view contained in your schema. Sequences are database objects from which multiple users can generate unique integers. The sequence generator generates sequential numbers, which can help to generate unique primary keys automatically, and to coordinate keys across multiple rows or tables. Without sequences, sequential values can only be produced programmatically.

A new primary key value can be obtained by selecting the most recently produced value and incrementing it. This method requires a lock during the transaction and causes multiple users to wait for the next value of the primary key; this waiting is known as serialization.

If developers have such constructs in applications, then you should encourage the developers to replace them with access to sequences. Sequences eliminate serialization and improve the concurrency of an application.

For example, the following statement creates a sequence used to generate employee numbers for the empno column of the emp table:. Notice that several parameters can be specified to control the function of sequences. Also, sequence numbers that have been used but not saved are lost as well. Oracle might also skip cached sequence numbers after an export and import. See Oracle9i Database Utilities for details.

You can alter a sequence to change any of the parameters that define how it generates sequence numbers except the sequence's starting number. To change the starting point of a sequence, drop the sequence and then re-create it. When you perform DDL on sequence numbers you lose the cache values. You can drop any sequence in your schema. When a sequence is dropped, its definition is removed from the data dictionary.

Any synonyms for the sequence remain, but return an error when referenced. A synonym is an alias for a schema object. Synonyms can provide a level of security by masking the name and owner of an object and by providing location transparency for remote objects of a distributed database.

Also, they are convenient to use and reduce the complexity of SQL statements for database users. Synonyms allow underlying objects to be renamed or moved, where only the synonym needs to be redefined and applications based on the synonym continue to function without modification. You can create both public and private synonyms.

A private synonym is contained in the schema of a specific user and available only to the user and the user's grantees. The underlying schema object need not exist, nor do you need privileges to access the object.

You can drop any private synonym in your own schema. For example, the following statement drops the private synonym named emp :. When you drop a synonym, its definition is removed from the data dictionary. All objects that reference a dropped synonym remain.

However, they become invalid not usable. For more information about how dropping synonyms can affect other schema objects, see "Managing Object Dependencies". ALL view is restricted to views accessible to the current user. USER view is restricted to views owned by the current user. See Also: Oracle9i Database Reference for complete descriptions of these views.

Notice that the sales public synonym hides the name of the sales table and its schema lion. Skip to content Home » Oracle Synonym. Up Next.