Chapter 4: databae objects: details of this Chapter

A table is the database object that holds your data.

The data dictionary holds information about every table; Oracle uses its data dictinary to ensure the correct type of data is placed in Oracle tables.

 

Views -- a special look at your data

A view is a database object that allow you to create a customized since of a table or a collection of tables.

Bytes -----------------------------------------------------------------------------------------------------------

Indexes have a sorted order

Its is typically much quicker for Oracle to go to the index, find all the records for a desired date, then bring back the information to you.

Index can guarantee uniqueness

There are two types of indexs you can create. You can create unique index and a nonunique index. A unique index does not allow duplicates; A nonunique index allows duplicates.

Two columns are better than one

Oracle allows you to create concatenated indexes. These indexes are made up of more than one column.

Bytes -----------------------------------------------------------------------------------------------------------

Synonyms -- a new identity

A synonyms is a database object that allow you to create alternnate names for Oracle tables views.

Private synonyms and public synonyms

Private synonyms is the only one that can use the synonym toast to point to the table.

Public whic all oracle users are able to use.

Bytes ------------------------------------------------------------------------------------------------------------

Grants -- may I please have a access?

Grants are used to give one user privileges to work with another user's data. Once privileges have been granted, recipients of the grant have the ability to work with someone else's objects.

Recipients of grants

Throughout this section, we have shown a number of grant statements where the recipients were either public or an assortment of database users.

Imagine an installation where there are two distinct classes of users for a financial management system.

Bytes -------------------------------------------------------------------------------------------------------------

Roles -- a way to group users together

You can create a databaes object called a role, then grant privileges to that role, then grant that role to individual users. sounds complicated, but it's very simple.

Example:

Grant insert on tablea to nurse;

Grant insert on tableb to nurse;

Grant update on tabled to nurse;

Let's give a test;

Grant nurse to OPS $ Abbey;

Grant nurse to OPS $ teplow;

Bytes -------------------------------------------------------------------------------------------------------------

Other object you may encounter in Oracle

Up until now, we have discussed object in the orcle datbase that all readers will become familiar with, this sction features a nubmer of objects that are closely related to the two main objects, but some may never have the need or opportunity to create.

Bytes ------------------------------------------------------------------------------------------------------------

Snapshots

A snapshots is a recent copy of a table from another database or, in some cases, a subset of that table. typeticaly, in a distributed computing environment, DBA's define snapshots for one or both of the following reasons;

1. Response Time improve when a local read-only copy of table exists -- this can reset times fater than reading data directly from a remote database.

4. once a snapshot is built on a remote database, if the node containing the data from which the snapshot is builtd is not availble, The snpashot can be used without the need to access the down database.

Addtinal: to define and indistribuate snapshots into your applications, you must install Oracle distributed database.

Bytes --------------------------------------------------------------------------------------------------------------

Stored objects

Stored Procedures, packages, and functions. The code for these special object is written using Oracle procedural SQL offering call PL/SQL feature in stored object;

Just like tables and views, users of the Oracle Database need privileges to run these code segments setting in the database. owners of stored object must issue a SQL statement similiar to the followiing to allow users to run their code:

VIP:

Just like tables and views, users need a synorym created to point at other user's stored object before they can be excuted.

Bytes -------------------------------------------------------------------------------------------------------------

Database links

Database links are closely coupled to distributed database feature.

Database links allow users to work with data in remote database without the need to know where that data resides.

Bytes -------------------------------------------------------------------------------------------------------------

Sequences

Sequences help relieve the need for disk I/O by caching numbers in memory, thereby making them available to systems requring A sequentia numbered primary key.

Bytes --------------------------------------------------------------------------------------------------------------

Clusters

Cluster allow for a diffenent method of storing table data.

Bytes ------------------------------------------------------------------------------------------------------------

Viewing objec information in the data dictionary

Object List:

All_catalog: All Tables, views, synonyms, sequences accessible to the user;

All_indexs: Descriptions of indexes on tables accessible to the user;

All_ind_columns: columns comprising indexes on accessible tables objects accessible to the user;

All_object: Objects accessible to the users;

All_sequences: Description of sequences accessible to the user;

All_synonyms: all synonyms accessible to the user;

All_tables: Description of tables accessible to the user;

User_catalog: tables, views, synonyms and sequences owned by the user;

User_cluseters: Descriptions of user's own clusters;

User_clu_columns: mapping of table columns to cluster colulmns;

User_indexes: Description of the user's indexes or on user's tables;

User_ind_columns: columns comprising user's indexes or on user's tables;

User_objects: objects owned by the user;

User_sequences: Description of the user's own sequenences;

User_synonms: Ther user's private synonmn;

User_tables: Description of the user's own talbes;

User_views: Description of the user's own views;