Page 91
information, while not showing Social Security number and salary data), and to make complicated queries easier to understand and use. Views can also be used to hide distributed database objects by creating views on remote database tables. Any statement that can be executed as a SQL query can be created as a view.
NOTE |
Views can be very helpful when designing applications because they can be used to hide complicated query logic in a table format that is much easier to query. They can be created with optimizer hints embedded in them, to ensure top query performance. |
The DBA_VIEWS view holds information on views created in the database.
Sequences are database objects that are used to generate unique numbers. A sequence is created with a starting value, an increment, and a maximum value. Each time a number is recalled from a sequence, the current sequence value is incremented by one. Each sequence-generated number can be up to 38 digits long.
You use a sequence by selecting the NEXTVAL or CURRVAL pseudocolumns from it. If you have a sequence named EMP_SEQ, for example, issuing SELECT EMP_SEQ.NEXTVAL FROM DUAL will return the next integer value of the sequence and increment the current value of the sequence by one. You can SELECT EMP_SEQ.CURRVAL FROM DUAL to return the current integer value of the sequence. Note that to use CURRVAL, you must have initialized the sequence for your user session by previously issuing a query on the NEXTVAL pseudocolumn of the sequence.
The most common usage of sequences is to provide unique numbers for primary key columns of tables. Information on sequences is stored in the DBA_SEQUENCES view.
Triggers are stored procedures that fire when certain actions occur against a table. Triggers can be coded to fire for inserts, updates, or deletes against a table and can also occur for each row that is affected or for each statement. Triggers are most often used to enforce data integrity constraints and business rules that are too complicated for the built-in Oracle referential integrity constraints. Information on database triggers can be found in the DBA_TRIGGERS view.
Synonyms are database pointers to other database tables. When you create a synonym,
you specify a synonym name and the object the synonym references. When you reference
the synonym name, the Oracle server automatically replaces the synonym name with the name
of the object for which the synonym is defined.
There are two types of synonyms: private and public. Private synonyms are created in a specific schema and are only accessible by the schema that owns it. Public synonyms are owned by the PUBLIC schema, and all database schemas can reference them.
Page 92
It's important to understand the order in which an object name is resolved within a SQL statement. If the SQL statement SELECT * FROM EMP_SALARY is issued, the Oracle server attempts to resolve the EMP_SALARY object in the following way:
Public synonyms should be used with care. Because all schemas can use public synonyms to resolve object names, unpredictable results can occur.
Information on public synonyms is stored in DBA_SYNONYMS. Note that the owner of public synonyms will be listed as PUBLIC in this view.
Database links are stored definitions of connections to remote databases. They are used to query remote tables in a distributed database environment. Because they are stored in the Oracle database, they fall under the category of database object. More information on database links can be found in Chapter 28, "Distributed Database Management."
Information on database links can be found in the DBA_DB_LINKS data dictionary view.
CAUTION |
DBA_DB_LINKS is one of the views that can store passwords in clear text, if the database link is defined with a specific UserID and password to connect to the remote database. Care should be taken when allowing end users access to this database view. |
Stored packages, procedures, and functions are stored in the data dictionary, along with their source code. A stored procedure is a code unit that does work, can be passed arguments, and can return values. A stored function is a code unit that is passed an argument and returns one value. A package is a collection of procedures, variables, and functions, logically grouped by function. See Chapter 10, "PL/SQL Fundamentals," for more information.
You can access information on stored packages, procedures and functions through the DBA_OBJECTS and DBA_SOURCE views.