Oracle : Database Objects Introduction

Introduction to Oracle's Database Schema and Non Schema Objects (Total 39)

A Database is a collection of Objects. Objects are the building blocks inside the database. A database consists of one or more database objects. Read more about Oracle Database in Introduction to Oracle Database post.

95% of Java Developers don't know about these 39 Oracle Database Object Types, as they have only used a bunch of these so far. Today we will deep dive and learn about all 39 of them.

Following query gives the list of all the Object Types in Oracle Database:

select distinct object_type from dba_objects order by 1;

What are Database Objects?

• Database objects are the foundation of any database application.
• Database objects house and support everything any database application needs in order to form a working application.
• Objects may include tables, indexes, triggers, procedures, functions, etc.
• Most commonly used objects are Tables, Synonyms, Views, Sequences, Constraints, Indexes, Users,etc

In Oracle, each database object is considered to either be a “Schema Object”, or a “Non-Schema Object”.

What is a Schema?
How is it different from a user account?

• A schema is a collection of logical structures of data, or schema objects, such as tables, indexes, and views, all of which are owned by a user account.
• A schema and a user are pretty much synonymous. A schema has the same name as the user account. You can think of a “schema” as being the same thing as a user account. Oracle Database automatically creates a schema when you create a user.
• A schema is owned by a database user and has the same name as that user. Each user owns a single schema.
• A user account should be seen and used as a logical collection of database objects, driven by business rules, collected into one organized entity, the schema.
• One user will often have one user account, and therefore one schema. But the opposite isn’t necessarily true. There can be more user accounts than there are actual users.
• A schema name (i.e., a user account) might not always represent a physical user, it can be any application process, or some other sort of virtual entity, perhaps a particular background process or whatever makes sense to suit the business rules that are in force.
• In Oracle, users and schemas are essentially the same thing. You can consider that a user is the account you use to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that account.

Oracle Database recognizes objects that are associated with a particular schema as Schema Objects and objects that are not associated with a particular schema as Non-Schema Objects.

What are Schema and Non-Schema Objects?
What is the difference between a Schema Object and a Non-Schema Object?

• All database objects fall into one of two categories, or “types”. These “types”, as the Oracle documentation calls them, are “schema” and “non-schema”. All database objects are said to be either “schema” database objects or “non-schema” database objects.
• Database objects, some of which are owned by a user and are thereby “schema” objects, and some of which are not schema objects but are still database objects nonetheless.
• “Schema” objects are those objects that can be owned by a user account. “Non-schema” objects cannot be owned by a user account and is a property of the database as a whole.
• Schema objects are Tables, Constraints, Indexes, Views, Sequences, Private Synonyms, etc.
• Non-schema objects are Users, Roles, Public Synonyms, etc.

Important - Schema objects are logical data storage structures. Schema objects do not have a one-to-one correspondence to physical files on disk that store their information. However, Oracle stores a schema object logically within a tablespace of the database. The data of each object is physically contained in one or more of the tablespace's datafiles. For some objects, such as tables, indexes, and clusters, you can specify how much disk space Oracle allocates for the object within the tablespace's datafiles.

Fig 1.1 Oracle Database Objects Introduction - Database Objects

What is a Tablespace and a Datafile? What is the relationship between the two?

• An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database's data. Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.
• Read more about Tablespaces and Datafiles along with Control Files in my upcoming Introduction to Tablespaces, Datafiles, and Control Files post.

In Oracle Database, we have following types of Objects-

Schema Objects		Non Schema Objects
Clusters	Object types	Contexts
Constraints	Object views	Directories
Database links	Operators	Parameter files (PFILEs) and server parameter files (SPFILEs)
Database triggers	Packages	Profiles
Dimensions	Queues	Restore points
External procedure libraries	Queues tables	Roles
Indexes	Scheduler	Rollback segments
Index-organized tables	Scheduler group	Tablespaces
Indextypes	Sequences	Users
Java classes, Java resources, Java sources	Stored functions, stored procedures
Jobs	Synonyms
Materialized view logs	Tables
Materialized views	Types
Mining models	Views
Object tables	XML Schema

Let’s take a brief look and understand what are these objects and why they are used.

Schema Objects-

1. Clusters
• A cluster is a schema object that contains data from one or more tables, all of which have one or more columns in common.
• Oracle Database stores together all the rows from all the tables that share the same cluster key.
• All the rows from all the tables that share the same cluster key are stored.
• After you create a cluster, you add tables to it. A cluster can contain a maximum of 32 tables.
• The cluster is transparent, you can access clustered tables with SQL statements just as you can access nonclustered tables.
• Specify one or more names of columns in the cluster key. You can specify up to 16 cluster key columns. These columns must correspond in both datatype and size to columns in each of the clustered tables, although they need not correspond in name.
• A table cluster is a group of tables that share common columns and store related data in the same blocks. When tables are clustered, a single data block can contain rows from multiple tables. For example, a block can store rows from both the employees and departments tables rather than from only a single table.
• You can consider clustering tables when they are primarily queried (but not modified) and records from the tables are frequently queried together or joined. Because table clusters store related rows of different tables in the same data blocks, properly used table clusters offer the following benefits over nonclustered tables:
• Disk I/O is reduced for joins of clustered tables.
• Access time improves for joins of clustered tables.
• Less storage is required to store related table and index data because the cluster key value is not stored repeatedly for each row.
Typically, clustering tables is not appropriate in the following situations:
• The tables are frequently updated.
• The tables frequently require a full table scan.
• The tables require truncating.



Fig 1.2 Oracle Database Objects Introduction - Clustered Vs Unclustered Tables

The following statement creates a cluster named personnel with the cluster key column department, a cluster size of 512 bytes, and storage parameter values:

CREATE CLUSTER personnel 
   (department NUMBER(4))
SIZE 512 
STORAGE (initial 100K next 50K);

The following statement creates the cluster index on the cluster key of personnel:

CREATE INDEX idx_personnel ON CLUSTER personnel;

After creating the cluster index, you can add tables to the index and perform DML operations on those tables. The following statements create some departmental tables from the sample hr.employees table and add them to the personnel cluster created in the earlier example:

CREATE TABLE dept_10
   CLUSTER personnel (department_id)
   AS SELECT * FROM employees WHERE department_id = 10;

CREATE TABLE dept_20
   CLUSTER personnel (department_id)
   AS SELECT * FROM employees WHERE department_id = 20;

TOP
2. Constraints
• A small bit of logic defined by you, to instruct a particular table about how it will accept, modify, or reject incoming data.
• Use a constraint to define an integrity constraint, a rule that restricts the values in a database.
• Constraints help understand how the tables and columns are related to each other.
Oracle Database lets you create six types of constraints and lets you declare them in two ways.
The six types of integrity constraint are described briefly here:
• A NOT NULL constraint prohibits a database value from being null.
• A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.
• A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.
• A foreign key constraint requires values in one table to match values in another table.
• A check constraint requires a value in the database to comply with a specified condition.
• A REF column by definition references an object in another object type or in a relational table. A REF constraint lets you further describe the relationship between the REF column and the object it references.
You can define constraints syntactically in two ways:
• As part of the definition of an individual column or attribute. This is called inline specification.
• As part of the table definition. This is called out-of-line specification.
NOT NULL constraints must be declared inline. All other constraints can be declared either inline or out of line.
Constraint clauses can appear in the following statements:
• CREATE TABLE
• ALTER TABLE
• CREATE VIEW
• ALTER VIEW
• The constraint information is accessible under the USER_CONSTRAINTS view.

select * from USER_CONSTRAINTS;

The constraints include the following columns-

Owner - - - of constraint
Constraint_name
Constraint_type
Table_name
Search_condition
R_Owner - - owner of the foreign key referenced table.
R_constraint_name
Delete_rule
Status

TOP
3. Database links
• A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.
• The central concept in distributed database systems is a database link. A database link is a connection between two physical database servers that allows a client to access them as one logical database.
• A database link is a pointer that defines a one-way communication path from an Oracle Database server to another database server. The link pointer is actually defined as an entry in a data dictionary table. To access the link, you must be connected to the local database that contains the data dictionary entry.
• A database link connection is one-way in the sense that a client connected to local database A can use a link stored in database A to access information in remote database B, but users connected to database B cannot use the same link to access data in database A. If local users on database B want to access data on database A, then they must define a link that is stored in the data dictionary of database B.
• A database link connection allows local users to access data on a remote database. For this connection to occur, each database in the distributed system must have a unique global database name in the network domain. The global database name uniquely identifies a database server in a distributed system.
• Database links are either private or public. If they are private, then only the user who created the link has access; if they are public, then all database users have access.
One principal difference among database links is the way that connections to a remote database occur. Users access a remote database through the following types of links:
• Connected user link: Users connect as themselves, which means that they must have an account on the remote database with the same username and password as their account on the local database.
Following is a public link to the remote supply database. The link uses the userid/password of the connected user. So if scott (identified by tiger) uses the link in a query, the link establishes a connection to the remote database as scott/tiger.

CREATE PUBLIC DATABASE LINK supply.us.acme.com;

• Fixed user link: Users connect using the username and password referenced in the link. For example, if Jane uses a fixed user link that connects to the hq database with the username and password scott/tiger, then she connects as scott, Jane has all the privileges in hq granted to scott directly, and all the default roles that scott has been granted in the hq database.
Following is a private fixed user link called link_2 to the database with service name us_supply. The link connects to the remote database with the userid/password of jane/doe regardless of the connected user.

CREATE DATABASE LINK link_2 
  CONNECT TO jane IDENTIFIED BY doe -- can put in double quotes if case sensitive
  USING '192.168.10.19:1521/us_supply_sid'; -- or use service name 'us_supply'

• Current user link: A user connects as a global user. A local user can connect as a global user in the context of a stored procedure, without storing the global user's password in a link definition. For example, Jane can access a procedure that Scott wrote, accessing Scott's account and Scott's schema on the hq database. Current user links are an aspect of Oracle Advanced Security.
Specify CURRENT_USER to create a current user database link. The current user must be a global user with a valid account on the remote database.
If the database link is used directly, that is, not from within a stored object, then the current user is the same as the connected user.
Following is a private link called link_1 to the database with service name us_supply(tns_service_name). The link uses the userid/password of the current user to log onto the remote database.

CREATE DATABASE LINK link_1 
  CONNECT TO CURRENT_USER
  USING 'us_supply';

How to configure the service name?
Configure the service name by modifying tnsnames.ora file by adding:

US_SUPPLY=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = us_supply)
    )
  )

Other way of writing is :

CREATE PUBLIC DATABASE LINK adcb_link_1 
  CONNECT TO "username" IDENTIFIED BY "password"
  USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.7.10.10)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=adcb)))';

• A shared database link is a link between a local server process and the remote database. The link is shared because multiple client processes can use the same link simultaneously.When a local database is connected to a remote database through a database link, either database can run in dedicated or shared server mode.
Why use Database Links?

• The great advantage of database links is that they allow users to access another user's objects in a remote database so that they are bounded by the privilege set of the object owner. In other words, a local user can access a link to a remote database without having to be a user on the remote database.
  For example, assume that employees submit expense reports to Accounts Payable (A/P), and further suppose that a user using an A/P application needs to retrieve information about employees from the hq database. The A/P users should be able to connect to the hq database and execute a stored procedure in the remote hq database that retrieves the desired information. The A/P users should not need to be hq database users to do their jobs; they should only be able to access hq information in a controlled way as limited by the procedure.
TOP
4. Database triggers
• A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view.
• A trigger stored in the database can include SQL and PL/SQL or Java statements to run as a unit and can invoke stored procedures. However, procedures and triggers differ in the way that they are invoked. A procedure is explicitly run by a user, application, or trigger. Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.
• A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database. For example, when a new record (representing a new worker) is added to the employee's table, new records should also be created in the tables of the taxes, vacations and salaries.
• In addition to triggers that fire when data is modified, Oracle 10g supports triggers that fire when schema level objects (that is, tables) are modified and when user logon or logoff events occur. These trigger types are referred to as "Schema-level triggers".
• Schema-level triggers
• After Creation
• Before Alter
• After Alter
• Before Drop
• After Drop
• Before Insert
The four main types of triggers are:
• Row Level Trigger: This gets executed before or after any column value of a row changes.
• Column Level Trigger: This gets executed before or after the specified column changes.
• For Each Row Type: This trigger gets executed once for each row of the result set affected by an insert/update/delete.
• For Each Statement Type: This trigger gets executed only once for the entire result set, but also fires each time the statement is executed.
Why Triggers are used?
• Triggers supplement the standard capabilities of Oracle to provide a highly customized database management system. For example, a trigger can restrict DML operations against a table to those issued during regular business hours. You can also use triggers to:
• Automatically generate derived column values
• Prevent invalid transactions
• Enforce complex security authorizations
• Enforce referential integrity across nodes in a distributed database
• Enforce complex business rules
• Provide transparent event logging
• Provide auditing
• Maintain synchronous table replicates
• Gather statistics on table access
• Modify table data when DML statements are issued against views
• Publish information about database events, user events, and SQL statements to subscribing applications

For example, you can create a trigger as:

CREATE TRIGGER hr.salary_check
      BEFORE INSERT OR DELETE OR UPDATE OF salary, job_id ON hr.employees
      FOR EACH ROW
         WHEN (new.job_id <> 'AD_VP')
      pl/sql_block

How Triggers Compared with Declarative Integrity Constraints?
• You can use both triggers and integrity constraints to define and enforce any type of integrity rule. However, Oracle strongly recommends that you use triggers to constrain data input only in the following situations:
• To enforce referential integrity when child and parent tables are on different nodes of a distributed database
• To enforce complex business rules not definable using integrity constraints
• When a required referential integrity rule cannot be enforced using the following integrity constraints:
• NOT NULL, UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DELETE CASCADE
• DELETE SET NULL

Read more about Triggers with examples in Sql : Trigger Post.

TOP
5. Dimensions
• A database dimension is a collection of related objects, called attributes, which can be used to provide information about fact data in one or more cubes. For example, typical attributes in a product dimension might be product name, product category, product line, product size, and product price.
• In data warehousing, a dimension is a collection of reference information about a measurable event. These events are known as facts and are stored in a fact table. Dimensions categorize and describe data warehouse facts and measures in ways that support meaningful answers to business questions. They form the very core of dimensional modeling.
• A data warehouse organizes descriptive attributes as columns in dimension tables. For example, a customer dimension’s attributes could include first and last name, birth date, gender, etc., or a website dimension would include site name and URL attributes.
• A dimension table has a primary key column that uniquely identifies each dimension record (row). The dimension table is associated with a fact table using this key. Data in the fact table can be filtered and grouped (“sliced and diced”) by various combinations of attributes. For example, a Login fact with Customer, Website, and Date dimensions can be queried for “number of males age 19-25 who logged in to funsportsite.com more than once during the last week of September 2010, grouped by day.”
• A dimension is a structure that categorizes data in order to enable users to answer business questions. Commonly used dimensions are customers, products, and time. For example, each sales channel of a clothing retailer might gather and store data regarding sales and reclamations of their Cloth assortment. The retail chain management can build a data warehouse to analyze the sales of its products across all stores over time and help answer questions such as:
• What is the effect of promoting one product on the sale of a related product that is not promoted?
• What are the sales of a product before and after a promotion?
• How does a promotion affect the various distribution channels?
• The data in the retailer's data warehouse system has two important components: dimensions and facts. The dimensions are products, customers, promotions, channels, and time. One approach for identifying your dimensions is to review your reference tables, such as a product table that contains everything about a product, or a promotion table containing all information about promotions. The facts are sales (units sold) and profits. A data warehouse contains facts about the sales of each product at on a daily basis.
• A typical relational implementation for such a data warehouse is a star schema. The fact information is stored in what is called a fact table, whereas the dimensional information is stored in dimension tables. In our example, each sales transaction record is uniquely defined as for each customer, for each product, for each sales channel, for each promotion, and for each day (time).
For example, you can create a dimention as:

CREATE DIMENSION products_dim 
        LEVEL product           IS (products.prod_id)
        LEVEL subcategory       IS (products.prod_subcategory) 
        LEVEL category          IS (products.prod_category) 
        HIERARCHY prod_rollup (
                product         CHILD OF 
                subcategory     CHILD OF 
                category
        ) 
        ATTRIBUTE product DETERMINES products.prod_name 
        ATTRIBUTE product DETERMINES products.prod_desc
        ATTRIBUTE subcategory DETERMINES products.prod_subcat_desc
        ATTRIBUTE category    DETERMINES products.prod_cat_desc;

TOP
6. External procedure libraries
• An external procedure is a third-generation-language routine stored in a dynamic link library (DLL), registered with PL/SQL, and called by you to do special-purpose processing. The routine must be callable from C but can be written in any language.
• At run time, PL/SQL loads the library dynamically, then calls the routine as if it were a PL/SQL subprogram. To safeguard your database, the routine runs in a separate address space. But, it participates fully in the current transaction. Furthermore, the routine can call back to the database to do SQL operations.
• External procedures promote reusability, efficiency, and modularity. DLLs already written and available in other languages can be called from PL/SQL programs. The DLLs are loaded only when needed, so memory is conserved. Moreover, the DLLs can be enhanced without affecting the calling programs.
• Typically, external procedures are used to interface with embedded systems, solve scientific and engineering problems, analyze data, or control real-time devices and processes. For example, you might use external procedures to send instructions to a robot, solve partial differential equations, process signals, analyze time series, or create animation on a video display.
• Moreover, external procedures enable you to
• move computation-bound programs from client to server, where they will execute faster thanks to more computing power and less across-network communication
• interface the database server with external systems and data sources
• extend the functionality of the database server itself
TOP
7. Indexes
• An object designed to speed up searches in a table.
• An INDEX performs much the same way as an index to a book, by copying a relatively small, select amount of information, sorting it for speedy reference, and tying it back to locations in the table for supporting quick lookups of rows in the source table.
• An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows. Oracle Database supports several types of index: Normal indexes. (By default, Oracle Database creates B-tree indexes.)
• An index is an optional structure, associated with a table or table cluster, that can sometimes speed data access. By creating an index on one or more columns of a table, you gain the ability in some cases to retrieve a small set of randomly distributed rows from the table. Indexes are one of many means of reducing disk I/O.
• Use the CREATE INDEX statement to create an index on:
• One or more columns of a table, a partitioned table, an index-organized table, or a cluster
• One or more scalar typed object attributes of a table or a cluster
• A nested table storage table for indexing a nested table column
• An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows. Oracle Database supports several types of index:
• Normal indexes. (By default, Oracle Database creates B-tree indexes.)
• Bitmap indexes, which store rowids associated with a key value as a bitmap
• Partitioned indexes, which consist of partitions containing an entry for each value that appears in the indexed column(s) of the table
• Function-based indexes, which are based on expressions. They enable you to construct queries that evaluate the value returned by an expression, which in turn may include built-in or user-defined functions.
• Domain indexes, which are instances of an application-specific index of type indextype
TOP
8. Index-organized tables
• Index Organized Tables (IOTs) are tables stored in an index structure. Whereas a table stored in a heap is unorganized, data in an IOT is stored and sorted by primary key (the data is the index). IOTs behave just like “regular” tables, and you use the same SQL to access them.
• An index-organized table--in contrast to an ordinary table--has its own way of structuring, storing, and indexing data. A comparison with an ordinary table may help to explain its uniqueness.
• A row in an ordinary table has a stable physical location. Once this location is established, the row never completely moves. Even if it is partially moved with the addition of new data, there is always a row piece at the original physical address--identified by the original physical rowid--from which the system can find the rest of the row. As long as the row exists, its physical rowid does not change. An index in an ordinary table stores both the column data and the rowid.
• A row in an index-organized table does not have a stable physical location. It keeps data in sorted order, in the leaves of a B*-tree index built on the table's primary key. These rows can move around to preserve the sorted order. For example, an insertion can cause an existing row to move to a different slot, or even to a different block.
Index Organized Tables (IOT) have their primary key data and non-key column data stored within the same B*Tree structure. Effectively, the data is stored within the primary key index. There are several reasons to use this type of table.
Why Use Index Organized Tables?

• Accessing data via the primary key is quicker as the key and the data reside in the same structure. There is no need to read an index then read the table data in a separate structure.
• Lack of duplication of the key columns in an index and table mean the total storage requirements are reduced.

The following statement creates an index-organized table:

CREATE TABLE admin_docindex(
        token char(20), 
        doc_id NUMBER,
        token_frequency NUMBER,
        token_offsets VARCHAR2(2000),
        CONSTRAINT pk_admin_docindex PRIMARY KEY (token, doc_id))
    ORGANIZATION INDEX 
    TABLESPACE admin_tbs
    PCTTHRESHOLD 20
    OVERFLOW TABLESPACE admin_tbs2;

This example creates an index-organized table named admin_docindex, with a primary key composed of the columns token and doc_id. The OVERFLOW and PCTTHRESHOLD clauses specify that if the length of a row exceeds 20% of the index block size, then the column that exceeded that threshold and all columns after it are moved to the overflow segment. The overflow segment is stored in the admin_tbs2 tablespace.
TOP
9. Indextypes
• Indextype is an object that specifies the routines that manage a domain (application-specific) index.
• Indextypes reside in the same namespace as tables, views, and other schema objects.
• "CREATE INDEXTYPE" statement binds the indextype name to an implementation type, which in turn specifies and refers to user-defined index functions and procedures that implement the indextype.
The following statement creates an indextype named position_indextype and specifies the position_between operator that is supported by the indextype and the position_im type that implements the index interface. Refer to "Using Extensible Indexing" for an extensible indexing scenario that uses this indextype:

CREATE INDEXTYPE position_indextype
   FOR position_between(NUMBER, NUMBER, NUMBER)
   USING position_im;

TOP
10. Java classes, Java resources, Java sources
• To call Java stored procedures from Oracle Database, you must load them into the Oracle database and publish them to SQL. Loading and publishing are separate tasks. Many Java classes, referenced only by other Java classes, are never published.
• To load Java stored procedures automatically, you use the command-line utility loadjava. It uploads Java source, class, and resource files into a system-generated database table, then uses the SQL CREATE JAVA {SOURCE | CLASS | RESOURCE} statement to load the Java files into the Oracle database. You can upload Java files from file systems, popular Java IDEs, intranets, or the Internet.
• To load Java stored procedures manually, you use CREATE JAVA statements. For example, in SQL*Plus, you can use the CREATE JAVA CLASS statement to load Java class files from local BFILEs and LOB columns into the Oracle database.
• Each Java class is stored as a schema object. The name of the object is derived from the fully qualified name (full name) of the class, which includes the names of containing packages. For example, the full name of class Handle is:
oracle.aurora.rdbms.Handle
• In the name of a Java schema object, slashes replace dots, so the full name of the class becomes:
oracle/aurora/rdbms/Handle
• The Oracle RDBMS accepts Java names up to 4000 characters long. However, the names of Java schema objects cannot be longer than 30 characters, so if a name is longer than that, the system generates an alias (short name) for the schema object. Otherwise, the full name is used. You can specify the full name in any context that requires it. When needed, name mapping is handled by the RDBMS.
• For an example see-https://docs.oracle.com/cd/B19306_01/java.102/b14187/chthree.htm
TOP
11. Jobs
• A job object (job) is a collection of metadata that describes a user-defined task that is scheduled to run one or more times. It is a combination of what needs to be executed (the action) and when (the schedule).
• A job is the combination of a schedule and a program, along with any additional arguments required by the program.
• A job instance represents a specific run of a job. Jobs that are scheduled to run only once will have only one instance. Jobs that have a repeating schedule will have multiple instances, with each run of the job representing an instance. For example, a job that is scheduled to run on Tuesday, Oct. 8th 2002 will have one instance. A job that runs daily at noon for a week has seven instances, one for each time the job runs.
• When a job is created, only one entry is added to the Scheduler's job table to represent the job. Depending on the logging level set, each time the job runs, an entry is added to the job log. Therefore, if you create a job that has a repeating schedule, you will find one entry in the job views and multiple entries in the job log. Each job instance log entry provides information about a particular run, such as the job completion status and the start and end time. Each run of the job is assigned a unique log id which is used in both the job log and job run details views.
• You create one or more jobs using the CREATE_JOB or CREATE_JOBS procedures or Enterprise Manager. The CREATE_JOB procedure is used to create a single job. This procedure is overloaded to enable you to create different types of jobs that are based on different objects. Multiple jobs can be created in a single transaction using the CREATE_JOBS procedure.
• After you create a job and enable it, the Scheduler automatically runs the job according to its schedule. You can view a job's run status and its job log by querying data dictionary views.
• For each job being created, you specify a job type, an action, a schedule, an optional job class, and other attributes. Jobs are created disabled by default and need to be enabled with DBMS_SCHEDULER.ENABLE to run. As soon as you enable a job, it is automatically run by the Scheduler at its next scheduled date and time. You can also set the enabled argument of the CREATE_JOB procedure to TRUE, in which case the job is ready to be automatically run according to its schedule as soon as you create it.
The following example demonstrates creating a database job called update_sales, which calls a package procedure in the OPS schema that updates a sales summary table:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'update_sales',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'OPS.SALES_PKG.UPDATE_SALES_SUMMARY',
   start_date         =>  '28-APR-08 07.00.00 PM Australia/Sydney',
   repeat_interval    =>  'FREQ=DAILY;INTERVAL=2', /* every other day */
   end_date           =>  '20-NOV-08 07.00.00 PM Australia/Sydney',
   auto_drop          =>   FALSE,
   job_class          =>  'batch_update_jobs',
   comments           =>  'My new job');
END;
/

TOP
12. Materialized view logs
• A materialized view log is required on a master if you want to fast refresh materialized views based on the master. When you create a materialized view log for a master table or master materialized view, Oracle creates an underlying table as the materialized view log. A materialized view log can hold the primary keys, rowids, or object identifiers of rows, or both, that have been updated in the master table or master materialized view. A materialized view log can also contain other columns to support fast refreshes of materialized views with subqueries.
• The name of a materialized view log's table is MLOG$_master_name. The materialized view log is created in the same schema as the target master. One materialized view log can support multiple materialized views on its master table or master materialized view. As described in the previous section, the internal trigger adds change information to the materialized view log whenever a DML transaction has taken place on the target master.
• Following are the types of materialized view logs:
• Primary Key: The materialized view records changes to the master table or master materialized view based on the primary key of the affected rows.
• Row ID: The materialized view records changes to the master table or master materialized view based on the rowid of the affected rows.
• Object ID: The materialized view records changes to the master object table or master object materialized view based on the object identifier of the affected row objects.
• Combination: The materialized view records changes to the master table or master materialized view based any combination of the three options. It is possible to record changes based on the primary key, the ROWID, and the object identifier of the affected rows. Such a materialized view log supports primary key, ROWID, and object materialized views, which is helpful for environments that have all three types of materialized views based on a master.
A combination materialized view log works in the same manner as a materialized view log that tracks only one type of value, except that more than one type of value is recorded. For example, a combination materialized view log can track both the primary key and the rowid of the affected row are recorded.

Though the difference between materialized view logs based on primary keys and rowids is small (one records affected rows using the primary key, while the other records affected rows using the physical rowid), the practical impact is large. Using rowid materialized views and materialized view logs makes reorganizing and truncating your master tables difficult because it prevents your ROWID materialized views from being fast refreshed. If you reorganize or truncate your master table, then your rowid materialized view must be COMPLETE refreshed because the rowids of the master table have changed.
• When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh.
• When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must reexecute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.
• A materialized view log is located in the master database in the same schema as the master table. A master table can have only one materialized view log defined on it. Oracle Database can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table.
The following statement creates a materialized view log on the oe.customers table that specifies physical and storage characteristics:

CREATE MATERIALIZED VIEW LOG ON customers 
   PCTFREE 5 
   TABLESPACE example 
   STORAGE (INITIAL 10K NEXT 10K); 

TOP
13. Materialized views
• Oracle uses materialized views (also known as snapshots in prior releases) to replicate data to nonmaster sites in a replication environment and to cache expensive queries in a data warehouse environment.
• A materialized view is a replica of a target master from a single point in time. The master can be either a master table at a master site or a master materialized view at a materialized view site. Whereas in multimaster replication tables are continuously updated by other master sites, materialized views are updated from one or more masters through individual batch updates, known as a refreshes, from a single master site or master materialized view site,
• When a materialized view is fast refreshed, Oracle must examine all of the changes to the master table or master materialized view since the last refresh to see if any apply to the materialized view. Therefore, if any changes where made to the master since the last refresh, then a materialized view refresh takes some time to apply the changes to the materialized view. If, however, no changes at all were made to the master since the last refresh of a materialized view, then the materialized view refresh should be very quick.
The following statement creates and populates the materialized aggregate view sales_by_month_by_state using tables in the sample sh schema. The materialized view will be populated with data as soon as the statement executes successfully. By default, subsequent refreshes will be accomplished by reexecuting the defining query of the materialized view:

CREATE MATERIALIZED VIEW sales_by_month_by_state
     TABLESPACE example
     PARALLEL 4
     BUILD IMMEDIATE
     REFRESH COMPLETE
     ENABLE QUERY REWRITE
     AS SELECT t.calendar_month_desc, c.cust_state_province,
        SUM(s.amount_sold) AS sum_sales
        FROM times t, sales s, customers c
        WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id
        GROUP BY t.calendar_month_desc, c.cust_state_province;

TOP
14. Mining models
• Mining models are database schema objects that perform data mining. As with all schema objects, access to mining models is controlled by database privileges. Models can be exported and imported. They support comments, and they can be tracked in the Database auditing system.
• Oracle Data Mining provides comprehensive, state-of-the-art data mining functionality within Oracle Database.
• Oracle Data Mining is implemented in the Oracle Database kernel, and mining models are first class database objects. Oracle Data Mining processes use built-in features of Oracle Database to maximize scalability and make efficient use of system resources.
• Mining models are created by the CREATE_MODEL procedure in the DBMS_DATA_MINING PL/SQL package. Models are created for a specific mining function, and they use a specific algorithm to perform that function. Mining function is a data mining term that refers to a class of mining problems to be solved. Examples of mining functions are: regression, classification, attribute importance, clustering, anomaly detection, and feature extraction. Oracle Data Mining supports one or more algorithms for each mining function.
• Most types of mining models can be used to score data. However, it is possible to score data without applying a model. Dynamic scoring and predictive analytics return scoring results without a user-supplied model. They create and apply transient models that are not visible to the user.
TOP
15. Object tables
• An object table is a special kind of table in which each row represents an object. Below statement creates an object table for person_typ objects.

CREATE TABLE person_obj_table OF person_type;

You can view this table in two ways:
• As a single-column table in which each row is a person_typ object, allowing you to perform object-oriented operations
• As a multi-column table in which each attribute of the object type person_typ; such as idno, name, and phone; occupies a column, allowing you to perform relational operations
• By default, every row object in an object table has an associated logical object identifier (OID) that uniquely identifies it in an object table. In a distributed and replicated environment, the system-generated unique identifier lets Oracle identify objects unambiguously.
• You can define some object data types and store the objects in columns in relational tables, which enables you to extend the system built-in types with user-defined ones. You can also create object views of existing relational data to represent and access this data according to an object model. Or you can store object data in object tables, where each row is an object.
TOP
16. Object types
• Oracle object types are user-defined types that make it possible to model real-world entities such as customers and purchase orders as objects in the database.
• Oracle object technology is a layer of abstraction built on Oracle relational technology. New object types can be created from any built-in database types and any previously created object types, object references, and collection types. Metadata for user-defined types is stored in a schema that is available to SQL, PL/SQL, Java, and other published interfaces.
• Object types and related object-oriented features such as variable-length arrays and nested tables provide higher-level ways to organize and access data in the database. Underneath the object layer, data is still stored in columns and tables, but you are able to work with the data in terms of the real-world entities, such as customers and purchase orders, that make the data meaningful. Instead of thinking in terms of columns and tables when you query the database, you can simply select a customer.
Define A TYPE : First we define a type PersonObj to represent a person. In this example, a person is defined by three attributes (first_name, last_name, date_of_birth) and one member function (getAge).

CREATE OR REPLACE TYPE PersonObj AS OBJECT (
  first_name  VARCHAR2(50),
  last_name   VARCHAR2(50),
  date_of_birth  DATE,
  MEMBER FUNCTION getAge RETURN NUMBER
);
/

Define A TYPE BODY : Next we define a TYPE BODY to add functionality to the getAge member function.

CREATE OR REPLACE TYPE BODY PersonObj AS
  MEMBER FUNCTION getAge RETURN NUMBER AS
  BEGIN
    RETURN Trunc(Months_Between(Sysdate, date_of_birth)/12);
  END getAge;
END;
/

Defining A Table : Now the object type is defined we can use it as a datatype in a table.

CREATE TABLE people (
  id      NUMBER(10) NOT NULL,
  person  PersonObj
);

TOP
17. Object views
• Just as a view is a virtual table, an object view is a virtual object table.
• An object view is a way to access relational data using object-relational features. It lets you develop object-oriented applications without changing the underlying relational schema.
• Oracle allows the creation of an object abstraction over existing relational data through the object view mechanism. You access objects that belong to an object view in the same way that you access row objects in an object table. Oracle also supports materialized view objects of user-defined types from data stored in relational schemas and tables. By using object views, you can develop object-oriented applications without having to modify existing relational database schemas.
• Object views also let you exploit the polymorphism that a type hierarchy makes possible. A polymorphic expression can take a value of the expression's declared type or any of that type's subtypes. If you construct a hierarchy of object views that mirrors some or all of the structure of a type hierarchy, you can query any view in the hierarchy to access data at just the level of specialization you are interested in. If you query an object view that has subviews, you can get back polymorphic data—rows for both the type of the view and for its subtypes.
For example, the following SQL statements define an object view:

CREATE TABLE emp_table  (
    empnum   NUMBER (5),
    ename    VARCHAR2 (20),
    salary   NUMBER (9, 2),
    job      VARCHAR2 (20) );

CREATE TYPE employee_t  (
    empno    NUMBER (5),
    ename    VARCHAR2 (20),
    salary   NUMBER (9, 2),
    job      VARCHAR2 (20) );

CREATE VIEW emp_view1 OF employee_t
    WITH OBJECT OID (empno) AS
        SELECT   e.empnum, e.ename, e.salary, e.job
        FROM     emp_table e
        WHERE    job = 'Developer';

The object view looks to the user like an object table whose underlying type is employee_t. Each row contains an object of type employee_t. Each row has a unique object identifier.
TOP
18. Operators
• An operator manipulates individual data items and returns a result. The data items are called operands or arguments.
• Operators are represented by special characters or by keywords. For example, the multiplication operator is represented by an asterisk (*) and the operator that tests for nulls is represented by the keywords IS NULL.
• There are two general classes of operators: unary and binary.
• Unary Operators : A unary operator uses only one operand. A unary operator typically appears with its operand in the format: "operator operand".
• Binary Operators : A binary operator uses two operands. A binary operator appears with its operands in the format: "operand1 operator operand2".
• Oracle Database supports following other operators:
• Set Operators : Set operators combine the results of two queries into a single result. It combines sets of rows returned by queries, instead of individual data items. All set operators have equal precedence. Oracle supports the UNION, UNION ALL, INTERSECT, MINUS set operators.
• Arithmetic Operators : Arithmetic operators manipulate numeric operands. The - operator is also used in date arithmetic.
• Character Operators : Character operators are used in expressions to manipulate character strings.
• Comparison Operators : Comparison operators are used in conditions that compare one expression with another. The result of a comparison can be TRUE, FALSE, or UNKNOWN.
• Logical Operators : Logical operators manipulate the results of conditions.
TOP
19. Packages
• A package is a schema object that groups logically related PL/SQL types, items, variables, and subprograms. Packages usually have two parts, a specification (spec) and a body, although sometimes the body is unnecessary. The specification (spec for short) is the interface to the package for your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use and can be referenced from outside the package. The body fully defines the queries for the cursors and the code for the subprograms, and so implements the spec.
• you can think of the spec as an operational interface and of the body as a "black box." You can debug, enhance, or replace a package body without changing the interface (package spec) to the package.
• The spec holds public declarations, which are visible to your application. You must declare subprograms at the end of the spec after all other items (except pragmas that name a specific function; such pragmas must follow the function spec).
• The body holds implementation details and private declarations, which are hidden from your application. Following the declarative part of the package body is the optional initialization part, which typically holds statements that initialize package variables.
• For example, a Human Resources package might contain hiring and firing procedures. Once written, your general-purpose package is compiled, then stored in an Oracle database, where its contents can be shared by many applications.

Package:

• Packages are PL/SQL constructs that allow related objects to be stored together. A package has two separate parts. Each of them is stored separately in the data dictionary.
• A package can include procedures, functions, cursors, types, and variables.
Example:


create or replace package XYZ as 

procedure p1 (p_id IN tablename.id % type, …………, ……..)

end XYZ;

TOP
20. Queues
• A queue is a repository for messages. There are two types of queues: user queues, also known as normal queues, and exception queues. The user queue is for normal message processing. Messages are transferred to an exception queue if they cannot be retrieved and processed for some reason. Queues can be created, altered, started, stopped, and dropped by using the Oracle AQ administrative interfaces.
• User queues can be persistent (the default) or nonpersistent queues. Persistent queues store messages in database tables. These queues provide all the reliability and availability features of database tables. Nonpersistent queues store messages in memory. They are generally used to provide an asynchronous mechanism to send notifications to all users that are currently connected.
• When Web-based business applications communicate with each other, producer applications enqueue messages and consumer applications dequeue messages. Advanced Queuing provides database-integrated message queuing functionality. Advanced Queuing leverages the functions of the Oracle database so that messages can be stored persistently, propagated between queues on different machines and databases, and transmitted using Oracle Net Services, HTTP(S), and SMTP.
• In computing, Oracle Advanced Queuing (AQ) is a sort of Message Oriented Middleware developed by Oracle Corporation and integrated into its Oracle database.
• AQ uses database structures as a repository for asynchronous queuing as an element in various Oracle-oriented and heterogeneous operations.
• Since Oracle Advanced Queuing is implemented in database tables, all the operational benefits of high availability, scalability, and reliability are applicable to queue data. Standard database features such as recovery, restart, and security are supported in Advanced Queuing, and queue tables can be imported and exported. You can also use database development and management tools such as Oracle Enterprise Manager to monitor queues.
Queue Example:
Creating a very simple object type for our messages:

CREATE TYPE demo_queue_payload_type AS OBJECT
 ( message VARCHAR2(4000) );
/

Queue tables are created using the DBMS_AQADM package as follows:

BEGIN
 DBMS_AQADM.CREATE_QUEUE_TABLE (
 queue_table        => 'demo_queue_table',
 queue_payload_type => 'demo_queue_payload_type'
 );
END;
/

Create a queue:

BEGIN
 DBMS_AQADM.CREATE_QUEUE (
  queue_name  => 'demo_queue',
  queue_table => 'demo_queue_table'
  );
END;
/

Start a queue:

BEGIN
 DBMS_AQADM.START_QUEUE (
  queue_name => 'demo_queue'
  );
END;
/

TOP
21. Queues tables
• Queues are stored in queue tables.
• Each queue table is a database table and contains one or more queues.
• Each queue table contains a default exception queue.
Queue tables are created using the DBMS_AQADM package as follows:

BEGIN
 DBMS_AQADM.CREATE_QUEUE_TABLE (
 queue_table        => 'demo_queue_table',
 queue_payload_type => 'demo_queue_payload_type'
 );
END;
/

TOP
22. Scheduler
• A schedule defines when a job should be run or when a window should open. Schedules can be shared among users by creating and saving them as objects in the database.
• You create schedules by using the CREATE_SCHEDULE procedure or Enterprise Manager. Schedules are created in the schema of the user creating the schedule, and are enabled when first created. You can create a schedule in another user's schema. Once a schedule has been created, it can be used by other users. The schedule is created with access to PUBLIC. Therefore, there is no need to explicitly grant access to the schedule.
• To use the Scheduler, you create Scheduler objects. These are schema objects that define the what, when, and how for job scheduling. Scheduler objects enable a modular approach to managing tasks. One advantage of the modular approach is that objects can be reused when creating new tasks that are similar to existing tasks.
• All Scheduler objects have attributes. You assign values to these attributes when you create or modify the objects.
• The Scheduler provides sophisticated, flexible enterprise scheduling functionality, which you can use to:
• Run PL/SQL anonymous blocks, PL/SQL stored procedures, and Java stored procedures.
• Run executables that are external to the database (external executables), such as applications, shell scripts, and batch files.
• Schedule job execution using the following methods:
• Time-based scheduling
• Event-based scheduling
• Dependency scheduling
• Prioritize jobs based on business requirements.The Scheduler enables control over resource allocation among competing jobs, thus aligning job processing with your business needs.
• Manage and monitor jobs, there are multiple states that a job undergoes from its creation to its completion. Scheduler activity is logged and information such as the status of the job and the last run time of the job can be easily tracked. This information is stored in views and can be easily queried using Enterprise Manager or SQL.
• Execute and manage jobs in a clustered environment, a cluster is a set of database instances that cooperates to perform the same task. Oracle Real Application Clusters (RAC) provides scalability and reliability without any change to your applications. The Scheduler fully supports execution of jobs in such a clustered environment. To balance the load on your system and for better performance, you can also specify the database service where you want a job to run.
Create a schedule for the job to run:

BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
 schedule_name   => 'my_weekend_5min_schedule',
 start_date    => SYSTIMESTAMP,
 repeat_interval  => 'FREQ=MINUTELY; INTERVAL=5; BYDAY=SAT,SUN',
 end_date     => SYSTIMESTAMP + INTERVAL '30' day,
 comments     => 'Every 5 minutes');
END;
/

TOP
23. Scheduler group
• A schedule group affinitizes, or groups, related tasks together.
• You can define schedule groups then assign them to the jobs that you create. For example, you can define a schedule group then assign that schedule group to a group of jobs that are being built on a specific production line and that must be completed on a specific date for a planned departure.
The following code creates a new group with a single member.

BEGIN
  DBMS_SCHEDULER.create_group(
    group_name    => 'test_db_group',
    group_type    => 'DB_DEST',
    member        => 'LOCAL');
END;
/

The keyword "LOCAL" can be used to indicate the job should be run on the local machine also. In the case of remote external jobs the LOCAL keyword can be prefixed with a credential, but for database jobs it can not and always runs using the credentials of the owner of the job.

The ADD_GROUP_MEMBER and REMOVE_GROUP_MEMBER procedures can be used to maintain the member list of the group. The following example adds the destination created in the previous section to the destination group.

BEGIN
  DBMS_SCHEDULER.add_group_member(
    group_name    => 'test_db_group',
    member        => 'test_credential@rac1_rac1_dest');
END;
/

The *_SCHEDULER_GROUPS and *_SCHEDULER_GROUP_MEMBERS views display information about the groups.
TOP
24. Sequences
• A sequence is a database object that generates unique numbers, mostly used for primary key values.
• One can select the NEXTVAL and CURRVAL from a sequence. Selecting the NEXTVAL will automatically increment the sequence.
• A counter, often used to generate unique numbers as identifiers for new rows as they are added to a table. Tables usually have a primary key which uniquely identifies a row in a table.
Example:


create sequence xyz increment by 1 start with 1;

TOP
25. Stored functions, stored procedures
• SQL functions are built into Oracle Database and are available for use in various appropriate SQL statements. Do not confuse SQL functions with user-defined functions written in PL/SQL.
• These functions can be used in SQL statements or queries in Oracle. Or, they can be used within the programming environment provided by the Oracle/PLSQL database, such as stored procedures, functions, triggers, etc. Below is the list of Oracle/PLSQL functions, sorted by category (ie: type of function).
• A procedure is a PL/SQL block alike the functions of the 3rd generation languages. You just have to compile them so as to use them later.
• When a procedure is created, it is compiled and stored in the database in the compiled form.
• Parameters can be passed to a procedure.
• A procedure call is a PL/SQL statement by itself. A procedure is a PL/SQL block with a declarative section, an executable section and an exception handling section.
The following statement creates the function get_bal on the sample table oe.orders (the PL/SQL is in italics):

CREATE FUNCTION get_bal(acc_no IN NUMBER) 
   RETURN NUMBER 
   IS acc_bal NUMBER(11,2);
BEGIN 
   SELECT order_total 
   INTO acc_bal 
   FROM orders 
   WHERE customer_id = acc_no; 
   RETURN(acc_bal); 
 END;
/

The following statement creates the procedure remove_emp in the schema hr.

CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
   tot_emps NUMBER;
   BEGIN
      DELETE FROM employees
      WHERE employees.employee_id = remove_emp.employee_id;
   tot_emps := tot_emps - 1;
   END;
/

TOP
26. Synonyms
• An alias for another object in the database, often used to specify an alternative name for a table or view.
• 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 PUBLIC synonym is owned by the special user group named PUBLIC and is accessible to every user in a database. A PRIVATE synonym is contained in the schema of a specific user and available only to the user and to grantees for the underlying object.
• Synonyms themselves are not securable. When you grant object privileges on a synonym, you are really granting privileges on the underlying object, and the synonym is acting only as an alias for the object in the GRANT statement.
• A synonym is a name assigned to a table or view that may be used refer to it thereafter. If you have an access to another user's table, you may create a synonym for it and refer to it by the synonym alone, without entering the user's name as a qualifier.
• Using synonyms is a good way to implement location transparency.
A PUBLIC synonym named customers for oe.customers:

CREATE PUBLIC SYNONYM customers FOR oe.customers;

To create a PUBLIC synonym for the employees table in the schema hr on the remote database, you could issue the following statement:

CREATE PUBLIC SYNONYM emp_table 
   FOR hr.employees@remote.us.oracle.com;

TOP
27. Tables
• A structure that can store data.
• All data is structured in columns and rows.
• Each column’s datatype is explicitly defined.
• A table is a two-dimensional representation of a relation in the form of rows (tuples) and columns (attributes). Each row in a table has the same set of columns. A relational database is a database that stores data in relations (tables). For example, a relational database could store information about company employees in an employee table, a department table, and a salary table.
• A table describes an entity such as employees. You define a table with a table name, such as employees, and set of columns. In general, you give each column a name, a data type, and a width when you create the table.
• A table is a set of rows. A column identifies an attribute of the entity described by the table, whereas a row identifies an instance of the entity. For example, attributes of the employee's entity correspond to columns for employee ID and last name. A row identifies a specific employee.

Below sql will create a table named admin_emp in the hr schema and store it in the admin_tbs tablespace:

CREATE TABLE hr.admin_emp (
         empno      NUMBER(5) PRIMARY KEY,
         ename      VARCHAR2(15) NOT NULL,
         ssn        NUMBER(9) ENCRYPT,
         job        VARCHAR2(10),
         mgr        NUMBER(5),
         hiredate   DATE DEFAULT (sysdate),
         photo      BLOB,
         sal        NUMBER(7,2),
         hrly_rate  NUMBER(7,2) GENERATED ALWAYS AS (sal/2080),
         comm       NUMBER(7,2),
         deptno     NUMBER(3) NOT NULL
                     CONSTRAINT admin_dept_fkey REFERENCES hr.departments
                     (department_id))
   TABLESPACE admin_tbs
   STORAGE ( INITIAL 50K);

COMMENT ON TABLE hr.admin_emp IS 'Enhanced employee table';

Partitions:

• Very large tables and indexes can be difficult and time-consuming to work with. To improve manageability, you can break your tables and indexes into smaller pieces called partitions.
• Partitioning provides tremendous advantages to applications by improving manageability, performance, and availability.
• Partitioning allows a table, index or index-organized table to be subdivided into smaller pieces. Each piece of database object is called a partition.
Techniques for partitioning tables:
- Range Partitioning :
- List Partitioning:
- Hash Partitioning:
- Composite Range-Hash Partitioning:
- Composite Range-List Partitioning:

Range Partitioning Example:

CREATE TABLE sales_range 
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30), 
sales_amount  NUMBER(10), 
sales_date    DATE)
PARTITION BY RANGE(sales_date) 
(
PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
);

Note: If you are updating the partition key column, "ORA-14402: updating partition key column would cause a partition change" will come unless you execute :

alter table sales_range ENABLE ROW MOVEMENT;

TOP
28. Types
• Object-oriented programming is especially suited for building reusable components and complex applications. In PL/SQL, object-oriented programming is based on object types. They let you model real-world objects, separate interfaces and implementation details, and store object-oriented data persistently in the database.
• An object type can represent any real-world entity. For example, an object type can represent a student, bank account, computer screen, rational number, or data structure such as a queue, stack, or list.
• Currently, you cannot define object types in a PL/SQL block, subprogram, or package. You can define them interactively in SQL*Plus using the SQL statement CREATE TYPE. (See Object types)
• After an object type is defined and installed in the schema, you can use it to declare objects in any PL/SQL block, subprogram, or package. For example, you can use the object type to specify the datatype of an attribute, column, variable, bind variable, record field, table element, formal parameter, or function result. At run time, instances of the object type are created; that is, objects of that type are instantiated. Each object can hold different values.
• Such objects follow the usual scope and instantiation rules. In a block or subprogram, local objects are instantiated when you enter the block or subprogram and cease to exist when you exit. In a package, objects are instantiated when you first reference the package and cease to exist when you end the database session.
• Every PL/SQL constant, variable, parameter, and function return value has a data type that determines its storage format and its valid values and operations.
• A scalar data type can have subtypes. A subtype is a data type that is a subset of another data type, which is its base type. A subtype has the same valid operations as its base type. A data type and its subtypes comprise a data type family.
• PL/SQL predefines many types and subtypes in the package STANDARD and lets you define your own subtypes.
• User defined data types are PL/SQl types that are based on the existing types. Subtypes are used to gives an alternate name to for a type.
Example:


Declare

subtype counter is number;

counter a;

TOP
29. Views
• A “filter” through which you can search a table, and interact with a table, but that stores no data itself, and simply serves as a “window” onto one or more tables.
• An Oracle VIEW, in essence, is a virtual table that does not physically exist. Rather, it is created by a query joining one or more tables.
• VIEW objects can be used to mask portions of the underlying table logic for various reasons—perhaps to simplify business logic, or perhaps to add a layer of security by hiding the real source of information.
• A VIEW can be used to display certain parts of a table, while hiding other parts of the same table.
• A view is a way of hiding the logic that created the joined table just displayed.
Example:


create view AB
select A.x, B.y from A, B where A.x = B.y;

You can query it as:

select x, y from AB;

Note: AB is the view name, A, B are the Table names with x and y as their column names respectively.
For views, you don’t need to specify the tables as the logic is hidden inside the views.

TOP
30. XML Schema
• XML Schema is a schema definition language written in XML. It can be used to describe the structure and various other semantics of conforming instance documents.
• For example, we can have a XML schema definition, purchaseOrder.xsd, describes the structure and other properties of purchase-order XML documents.

BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd',
    SCHEMADOC => XDBURIType('/source/schemas/poSource/xsd/purchaseOrder.xsd').getCLOB(),
    LOCAL     => TRUE,
    GENTYPES  => TRUE, 
    GENTABLES => TRUE);
END;
/

• The URL used is a name that uniquely identifies the registered XML schema within the database:
  http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd. This need not point to a location where the XML schema document is located. The target namespace of the XML schema is another URL, different from the XML schema location URL, which specifies an abstract namespace within which elements and types get declared.
• An XML schema can optionally specify the target namespace URL. If this attribute is omitted, the XML schema has no target namespace. The target namespace is commonly the same as the URL of the XML schema.
• An XML instance document must specify the namespace of the root element (same as the target namespace of the XML schema) and the location (URL) of the XML schema that defines this root element. The location is specified with attribute xsi:schemaLocation. When the XML schema has no target namespace, use attribute xsi:noNamespaceSchemaLocation to specify the schema URL.
TOP

Non-Schema Objects-

1. Contexts
• An application context is a set of name-value pairs that Oracle Database stores in memory. The application context has a label called a namespace, for example, empno_ctx for an application context that retrieves employee IDs. Inside the context are the name-value pairs (an associative array): the name points to a location in memory that holds the value. An application can use the application context to access session information about a user, such as the user ID or other user-specific information, or a client ID, and then securely pass this data to the database. You can then use this information to either permit or prevent the user from accessing data through the application. You can use application contexts to authenticate both database and nondatabase users.


What are the Components of the Application Context?

• Name: Refers to the name of the attribute set that is associated with the value. For example, if the empno_ctx application context retrieves an employee ID from the HR.EMPLOYEES table, it could have a name such as employee_id.
• Value: Refers to a value set by the attribute. For example, for the empno_ctx application context, if you wanted to retrieve an employee ID from the HR.EMPLOYEES table, you could create a value called emp_id that sets the value for this ID.


• Think of an application context as a global variable that holds information that is accessed during a database session. To set the values for a secure application context, you must create a PL/SQL package procedure that uses the DBMS_SESSION.SET_CONTEXT procedure. In fact, this is the only way that you can set application context values if the context is not marked INITIALIZED EXTERNALLY or INITIALIZED GLOBALLY. You can assign the values to the application context attributes at run time, not when you create the application context. Because the trusted procedure, and not the user, assigns the values, it is a called secure application context. For client-session based application contexts, another way to set the application context is to use Oracle Call Interface (OCI) calls.
• Application contexts facilitate the implementation of fine-grained access control. They allow you to implement security policies with functions and then associate those security policies with applications. Each application can have its own application-specific context. Users are not allowed to arbitrarily change their context (for example, through SQL*Plus).
• A context is a named set of attribute/value pairs associated with a PL/SQL package. A context is attached to, and is global within, a session. Your application can use a context to set values that are then accessed from within your code and, specifically, from within code that is used to generate WHERE clause predicates for fine-grained access control.
• Suppose you are building a human resources application. You might create a context called HRINFO and define the following attributes for that context:

position
organizational_unit
country

• There are local application contexts - private to a particular session and expire (disappear) when the session goes away.
• Then there are global application contexts - contexts that may be shared across sessions (like a global variable) and survive session creation and deletion (only a database "bounce" truly gets rid of them).
TOP
2. Directories
• An Oracle directory is a database object pointing to a operating system directory on the database server machine for reading and writing files.
• Directories do not allow subdirectory traversal. That means that you need to create Oracle directories for all directories and subdirectories you need to access.
• You can perform following operations related to directory:
• Create a database directory:

SQL> CREATE OR REPLACE DIRECTORY tmp AS '/tmp';

• Grant a user access to the directory:

SQL> GRANT read, write ON DIRECTORY tmp TO scott;

• Revoke access from a directory:

SQL> REVOKE write ON DIRECTORY tmp FROM scott;

• Allow a user to create their own directories (not recommended, seen as a security risk as users can overwrite any file on the OS):

SQL> GRANT create any directory TO scott;
SQL> GRANT drop any directory TO scott;

TOP
3. Parameter files (PFILEs) and server parameter files (SPFILEs)
• When an Oracle instance starts, it reads initialization parameters from an initialization parameter file. For any initialization parameters not specifically included in the initialization parameter file, the database supplies defaults.
• The initialization parameter file can be either a read-only text file, or a read/write binary file. The binary file is called a server parameter file. A server parameter file enables you to change initialization parameters with ALTER SYSTEM commands and to persist the changes across a shutdown and startup. It also provides a basis for self-tuning by Oracle Database. For these reasons, it is recommended that you use a server parameter file. You can create one manually from your edited text initialization file, or automatically by using Database Configuration Assistant (DBCA) to create your database.


How to Change Parameter Values in a Parameter File?
You change the value of a parameter in a parameter file in one of the following ways:


• By editing an initialization parameter file.In most cases, the new value takes effect the next time you start an instance of the database.
• By issuing an following statement to update a server parameter file:

ALTER SYSTEM SET ... SCOPE=SPFILE;



• Before you manually create a server parameter file, you can start an instance with a text initialization parameter file. Upon startup, the Oracle instance first searches for a server parameter file in a default location, and if it does not find one, searches for a text initialization parameter file. You can also override an existing server parameter file by naming a text initialization parameter file as an argument of the STARTUP command.
• A parameter file is a file that contains a list of initialization parameters and a value for each parameter. You specify initialization parameters in a parameter file that reflect your particular installation. Oracle supports the following two types of parameter files:
• Server Parameter Files
A server parameter file is a binary file that acts as a repository for initialization parameters. The server parameter file can reside on the machine where the Oracle database server executes. Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist across instance shutdown and startup.
• Initialization Parameter Files
An initialization parameter file is a text file that contains a list of initialization parameters. The file should be written in the client's default character set.

The following are sample entries in an initialization parameter file:


PROCESSES = 100

OPEN_LINKS = 12

GLOBAL_NAMES = true

The name of the initialization parameter file varies depending on the operating system. For example, it can be in mixed case or lowercase, or it can have a logical name or a variation of the name init.ora. Also supplied is an initdw.ora file, which contains suggested parameter settings for data warehouses and data marts. The database administrator can choose a different filename for the initialization parameter file.

Sample initialization parameter files are provided on the Oracle distribution medium for each operating system. A sample file is sufficient for initial use, but you will probably want to modify the file to tune the database for best performance. Any changes will take effect after you completely shut down and restart the instance.

TOP
4. Profiles
• Profiles are a means to limit resources a user can use. Before profiles can be assigned, they must be created with create profile. Then, they can be assigned to users with alter user ... profile.
• You can set up limits on the system resources used by setting up profiles with defined limits on resources. Profiles are very useful in large, complex organizations with many users. It allows you to regulate the amount of resources used by each database user by creating and assigning profiles to users.
• Use the CREATE PROFILE statement to create a profile, which is a set of limits on database resources. If you assign the profile to a user, then that user cannot exceed these limits.
The following statement creates the app_user profile with password limits values set:

CREATE PROFILE app_user LIMIT
   FAILED_LOGIN_ATTEMPTS 5
   PASSWORD_LIFE_TIME 60
   PASSWORD_REUSE_TIME 60
   PASSWORD_REUSE_MAX 5
   PASSWORD_VERIFY_FUNCTION verify_function
   PASSWORD_LOCK_TIME 1/24
   PASSWORD_GRACE_TIME 10;

• A profile is a database object - a named set of resource limits to:
• Restrict database usage by a system user – profiles restrict users from performing operations that exceed reasonable resource utilization. Examples of resources that need to be managed:
• Disk storage space.
• I/O bandwidth to run queries.
• CPU power.
• Connect time.
• Cpu time
• Enforce password practices – how user passwords are created, reused, and validated.
• Profiles are assigned to users as part of the CREATE USER or ALTER USER commands.
• User accounts can have only a single profile.
• A default profile can be created – a default already exists within Oracle named DEFAULT – it is applied
• to any user not assigned another profile.
• Assigning a new profile to a user account supersedes any earlier profile.
• Profiles cannot be assigned to roles or other profiles.
• Here are some system privileges for PROFILE.
• alter profile
• create profile
• drop profile

Note:Oracle recommends that you use the Database Resource Manager rather than this SQL statement to establish resource limits.
TOP
5. Restore points
• Oracle Flashback Database and restore points are related data protection features that enable you to rewind data back in time to correct any problems caused by logical data corruption or user errors within a designated time window. These features provide a more efficient alternative to point-in-time recovery and does not require a backup of the database to be restored first. The effects are similar to database point-in-time recovery (DBPITR). Flashback Database and restore points are not only effective in traditional database recovery situations but can also be useful during database upgrades, application deployments and testing scenarios when test databases must be quickly created and re-created. Flashback Database also provides an efficient alternative to rebuilding a failed primary database after a Data Guard failover.
• Restore points provide capabilities related to Flashback Database and other media recovery operations. In particular, a guaranteed restore point created at a system change number (SCN) ensures that you can use Flashback Database to rewind the database to this SCN. You can use restore points and Flashback Database independently or together.
• Flashback Database is accessible through the RMAN command FLASHBACK DATABASE or the SQL statement FLASHBACK DATABASE. You can use either command to quickly recover the database from logical data corruption or user errors. The following examples return the database to a specified SCN or restore point:

FLASHBACK DATABASE TO RESTORE POINT 'before_upgrade';
FLASHBACK DATABASE TO SCN 202381;

How to create a Restore points?
Use the CREATE RESTORE POINT statement to create a restore point, which is a name associated with an SCN of the database corresponding to the time of the creation of the restore point. A restore point can be used to flash a table or the database back to the time of creation of the restore point without the need to determine the SCN or timestamp.

There are two types of restore point:

• Guaranteed restore points: A guaranteed restore point enables you to flash the database back deterministically to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter setting. The guaranteed ability to flash back depends on sufficient space being available in the flash recovery area.
• Normal restore points: A normal restore point enables you to flash the database back to a restore point within the time period determined by the DB_FLASHBACK_RETENTION_TARGET initialization parameter. The database automatically manages normal restore points. When the maximum number of restore points is reached, according to the rules described restore_point, the database automatically drops the oldest restore point. However, you can explicitly drop a normal restore point using the DROP RESTORE POINT statement.
TOP
6. Roles
• A set of one or more privileges that can be granted to a user.
• A role is a set or group of privileges that can be granted to users or another role. This is a great way for database administrators to save time and effort.
The following statement creates the role dw_manager:

CREATE ROLE dw_manager;

What is a privilege and how is it different from a Role?
A user privilege is a right to execute a particular type of SQL statement, or a right to access another user's object. The types of privileges are defined by Oracle.

Roles, on the other hand, are created by users (usually administrators) and are used to group together privileges or other roles. They are a means of facilitating the granting of multiple privileges or roles to users.

There are over 100 distinct system privileges. Each system privilege allows a user to perform a particular database operation or class of database operations.
TOP
7. Rollback segments
• Each database contains one or more rollback segments. A rollback segment records the old values of data that were changed by each transaction (whether or not committed). Rollback segments are used to provide read consistency, to roll back transactions, and to recover the database.
• A Rollback Segment is a database object containing before-images of data written to the database.
  Rollback segments are used to:
• Undo changes when a transaction is rolled back
• Ensure other transactions do not see uncommitted changes made to the database
• Recover the database to a consistent state in case of failures
• In order to support the rollback facility in oracle database, oracle takes the help of rollback segments. Rollback segments basically holds the before image or undo data or uncommitted data of a particular transaction, once the transaction is over the blocks in that rollback segment can help any other transaction.
The following statement creates a rollback segment with default storage values in an appropriately configured tablespace:

CREATE TABLESPACE rbs_ts
   DATAFILE 'rbs01.dbf' SIZE 10M
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100K;

/* This example and the next will fail if your database is in 
   automatic undo mode.
*/
CREATE ROLLBACK SEGMENT rbs_one
   TABLESPACE rbs_ts;

• Rollback segment is just like any other table segments and index segments, which consist of extents, also demand space and they get created in a tablespace. In order to perform any DML operation against a table which is in a non system tablespace ('emp' in 'user' tablespace), oracle requires a rollback segment from a non system tablespace.
• When a transaction is going on a segment which is in non system tablespace, then Oracle needs a rollback segment which is also in non system tablespace. This is the reason we create a separate tablespace just for the rollback segment.
Why rollback segments?

• Undo the changes when a transaction is rolled back.
• Ensure read consistency (other transactions do not see uncommitted changes made to the database).
• Recover the database to a consistent state in case of failures.
• There are two types of rollback segments:
• Private rollback segments (for single instance database).
• Public rollback segments (for RAC or Oracle Parallel Server).
TOP
8. Tablespaces
• An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database's data. Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.
• A tablespace is a logical group of data files in a database. A database typically contains at least one tablespace, and usually two or more. Within the database, the tablespace plays a role similar to that of a folder on the hard drive of a computer. Some database programs, such as Oracle, automatically create a tablespace called SYSTEM that contains general information about the structure and contents of the database. A small database can be entirely contained in the SYSTEM tablespace, but in most cases, user data is placed in other tablespaces.
• The files in a tablespace usually share a common characteristic. For example, a database for a wholesale distributor might consist of tablespaces entitled ACCOUNTING, ORDERS, SHIPPING, SERVICE, and SYSTEM. Each tablespace might contain only one data file, or thousands of files, or anything in between.
• Tablespaces can be created, deleted, and merged, just as can the folders on the hard drive of a computer.
• Tablespaces are the bridge between certain physical and logical components of the Oracle database.
• Tablespaces are where you store Oracle database objects such as tables, indexes and rollback segments. You can think of a tablespace like a shared disk drive in Windows. You can store files on your shared drive, move files around and remove files. The same is true with tablespaces.
• A tablespace is made up of one or more database datafiles. The total amount of storage space available in a tablespace is the sum of the physical disk size of all the datafiles associated with that tablespace (less some system overhead). The datafiles are created automatically when the tablespace is defined. In most cases, all datafile space is pre-allocated; that is, the space is set aside when the datafile is created. Thus, when you create a tablespace, you define the initial size of the associated datafile.
• Tablespaces are given names as they are created. For example, the first tablespaces that are created are named SYSTEM and SYSAUX (though SYSAUX is only created in 10g).
The following example creates a bigfile tablespace bigtbs_01 with a datafile bigtbs_f1.dat of 10 MB:

CREATE BIGFILE TABLESPACE bigtbs_01
  DATAFILE 'bigtbs_f1.dat'
  SIZE 20M AUTOEXTEND ON;

TOP
9. Users
• Users are the “owners” of database objects.
• User is an account through which you can log in to the database, and to establish the means by which Oracle Database permits access.
• Following query lists all users of the database visible to the current user.

select * from ALL_USERS;

• A schema is a collection of logical structures of data, or schema objects, such as tables, indexes, and views, all of which are owned by a user account.
• A user and a schema are pretty much synonymous. A schema has the same name as the user account. You can think of a “schema” as being the same thing as a user account. Oracle Database automatically creates a schema when you create a user.
• A user account should be seen and used as a logical collection of database objects, driven by business rules, collected into one organized entity, the schema.
• One user will often have one user account, and therefore one schema. But the opposite isn’t necessarily true. There can be more user accounts than there are actual users.
The following statement creates a user. If you create a new user with PASSWORD EXPIRE, then the user's password must be changed before the user attempts to log in to the database. You can create the user sidney by issuing the following statement:

CREATE USER sidney 
    IDENTIFIED BY out_standing1 
    DEFAULT TABLESPACE example 
    QUOTA 10M ON example 
    TEMPORARY TABLESPACE temp
    QUOTA 5M ON system 
    PROFILE app_user 
    PASSWORD EXPIRE;

TOP

Related Blogs-

1. Oracle Database Introduction
2. Oracle Blogs Listing

References-

1. https://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm#CNCPT010
2. https://docs.oracle.com/cd/A57673_01/DOC/server/doc/SCN73/ch4.htm#toc040

Subscribe to Java 4 Brains by Email