resourceone.info Biography Oracle Basics Pdf

ORACLE BASICS PDF

Thursday, July 25, 2019


Oracle Database Concepts, 11g Release 2 (). E Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be. Oracle Database Concepts, 10g Release 2 (). B Oracle is not responsible for the availability of, or any content provided on, third-party Web sites. Covered topics are: Introduction to Databases and the Relational Model, Database Objects, Responsibilities of a Database Administrator, The Oracle Product Family. Oracle Database Concepts PDF P. RDBMS Concepts and Oracle 8i (PDF P).


Oracle Basics Pdf

Author:JAME PANICO
Language:English, Spanish, Dutch
Country:Saudi Arabia
Genre:Technology
Pages:532
Published (Last):06.07.2016
ISBN:787-8-78806-624-5
ePub File Size:29.60 MB
PDF File Size:15.54 MB
Distribution:Free* [*Regsitration Required]
Downloads:50370
Uploaded by: ALICIA

Download free Oracle Database 11g: SQL Fundamentals course material and training, PDF file on pages. of a relational database. •Describe the Oracle implementation of the. RDBMS and ORDBMS. •Describe how SQL and PL/SQL are used in the Oracle product set. If you are willing to compile and execute SQL programs with Oracle 11g RDBMS but you in this tutorial, please notify us at [email protected]

Undo files: These special datafiles, which can only contain undo information, aid in recovery, rollbacks, and read-consistency. Archive log files: These files, copies of the redo log files, are usually stored at different locations. They are necessary for example when applying changes to a standby database, or when performing recovery after a media failure. One can store identical archive logs in multiple locations.

Tempfiles: These special datafiles serve exclusively for temporary storage data used for example during large sorts or for global temporary tables Control files, necessary for database startup.

Navigation menu

Oracle Corporation defines a control file as "[a] binary file that records the physical structure of a database and contains the names and locations of redo log files, the time stamp of the database creation, the current log sequence number, checkpoint information, and so on". Data files can occupy pre-allocated space in the file system of a computer server, use raw disk directly, or exist within ASM logical volumes.

After the installation process sets up sample tables, the user logs into the database with the username scott and the password tiger. Other default schemas [83] [84] include: SYS essential core database structures and utilities SYSTEM additional core database structures and utilities, and privileged account OUTLN used to store metadata for stored outlines for stable query-optimizer execution plans.

The information in the SGA consists of the following elements, each of which has a fixed size, established at instance startup: Datafiles Every Oracle database has one or more physical datafiles, which contain all the database data.

The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database. Datafiles have the following characteristics: One or more datafiles form a logical unit of database storage called a tablespace. A datafile can be associated with only one tablespace. Datafiles can be defined to extend automatically when they are full.

Data in a datafile is read, as needed, during normal database operation and stored in the memory cache of Oracle Database. For example, if a user wants to access some data in a table of a database, and if the requested information is not already in the memory cache for the database, then it is read from the appropriate datafiles and stored in memory.

Figure illustrates each entity by a small illustration, with the relationships between each entity represented by an arrow and a description. The employee fills out the expense sheets for the expenses incurred on behalf of the company.

Figure 3: An entity-relationship diagram of process flow in the system Then, the employees send their vouchers to the accounts payable organization, which creates a check for the employee and mails the payment to the employee.

The process is very simple, but it accurately models the business process within an organization to reimburse an employee for his expenses. When the developers of a database application create the employee expenditure system modeled by the entity-relationship diagram above, they will first take those entities and map out the relationship, then take the entity-relationship diagram and create a logical data model out of those entities and processes. A logical data model is a more detailed diagram than the entity-relationship diagram in that it fills in details about the process flow that the entity-relationship diagram attempts to model.

Figure shows the logical data model of the employee table and the invoice table.

Oracle Tutorial

Figure 4: Logical data model of employee table and invoice table On the expense sheet, the employee will fill in various pieces of information, including the expense ID number, the employee ID number, and the expense amount. The line between the two entities is similar to a relationship; however, in the logical data model, the entities are called tables and the relationships are called foreign keys.

There is an interesting piece of information communicated above and below the line on the opposite side of each table as well. That piece of information identifies a pair of facts about the relationship.

The first element of the pair identifies whether the relationship is mandatory from the perspective of the table appearing next to the pair. A one indicates that the relationship is mandatory for the pair, while a zero 0 indicates that the relationship is optional. In the example in the diagram above, the relationship between employee and expense sheet is optional for employees but mandatory for expense sheets.

The second component of that pair indicates whether there is a one-to-one, one-to-many, or many-to-many correspondence between records of one table and records of another table. That is to say, each employee may have submitted one or more expense sheets, or none at all, while each expense sheet corresponds to one and only one employee.

This pair of facts is referred to as the ordinality of the database tables. The relationship between columns to tables corresponds loosely to the activity or relationship that exists between the two entities that the tables represent.

In terms of the entity-relationship diagram, the empid is the tie that binds an expense sheet to the employee who created it. Therefore, the relationship of one table to another through foreign keys should correspond somewhat to the relationship that occurs between two entities in the process flow being modeled. The database designer may ask several questions related to the physical design of that system as follows: How many employees will be allowed to use the system?

What sort of company chargeback system will be used to take the employee expense payment from the budget of the department for which the expense was incurred on behalf of the employee? How many expense sheets will be submitted per month and per year? The proper creation of a database in Oracle depends on answering these and many other questions regarding the physical relationship between the machine hosting Oracle and the data Oracle stores as part of the application model.

Some of these questions relate to Oracle-specific features. For example, the designer of the database should know row count estimates for each object to be created in Oracle. This estimate of row count should be something that is forecasted over a period of time, say two years.

This forecast of sizing for the database will allow the DBA some "breathing room" when the database application is deployed, so that the DBA is not constantly trying to allocate more space to an application that continually runs out of it. Some objects that the designer will need to produce sizing estimates for are the tables and indexes, and the tablespaces that will contain those tables and indexes.

In a point related to indexes, the designer of the application should know what the users of the database will need regarding data access. This feature of database design is perhaps the hardest to nail down after the initial estimate of transaction activity for the database application. The reason for the difficulty is knowing what the users will want with respect to data access. The developers of the application should, where possible, try to avoid providing users with free rein to access data via ad hoc queries, as many users will not know, for example, that searching a table on an indexed column is far preferable to searching on a nonindexed column, for performance reasons.

Providing the "canned" query access via graphical user interfaces or batch reporting allows the designers to tune the underlying queries that drive the screens or reports, scoring a positive response from the users while also minimizing the impact of application activity on the Oracle instance. There are several different options for specifying character sets in the Oracle database, just as there are many different languages available for use by different peoples of the world.

These languages fall into distinct categories with respect to the mechanisms on a computer that will store and display the characters that comprise those languages. The distinct categories are single-byte character sets, multibyte character sets, and languages read from right to left.

Examples of the multibyte character sets available are the languages that originated in Eastern Asia, Southeast Asia, or the Pacific Rim. These languages include Mandarin, Japanese, and Korean.

Examples of a language read right to left include Hebrew and Arabic. One final, and perhaps the most important, area of all to consider at the onset of database system creation in the Oracle environment is how the user will preserve the data in the system from any type of failure inherent in the usage of computer machinery.

Such methods may include full and partial backups for the database and the archiving or storing of redo logs created by Oracle to track changes made in the database. The first is creating the physical locations for data in tables and indexes to be stored in the database.

These physical locations are called datafiles. The second step is to create the files that will store the redo entries that Oracle records whenever any process makes a data change to the Oracle database. These physical structures are called the redo log files, or redo log members. The final step in creating an Oracle database is to create the logical structures of the data dictionary.

The data dictionary comprises an integral portion of the database system.

Both the users and Oracle refer to the data dictionary in order to find information stored in tables or indexes, to find out information about the tables or indexes, or to find out information about the underlying physical structure of the database, the datafiles, and the redo log files. Exercises What is an entity-relationship diagram? Explain both concepts of entities and relationships.

What is a logical data model? How does the logical data model correspond to the entity-relationship diagram? What structures in a data model relate loosely to the entities and the relationships of an entity-relationship diagram? What is ordinality?

Explain the concept of mandatory vs. What is a foreign key? Is a foreign key part of the entity-relationship diagram or the logical data model? To what item in the other model does the foreign key relate to?

Creation of the Oracle database is accomplished with the create database statement. The first thing to remember about database creation is the Oracle recommended methodology for actually creating the database.

Oracle Database

The steps are as follows: Back up existing databases. Create or edit the init. Verify the instance name. Start the appropriate database management tool.

Start the instance.

Create and back up the new database. Step 1 in the process is to back up the database.

ORA file. More details will be given shortly about the required parameters that must be unique for database creation. Steps 1 and 2 are critical in preserving the integrity of any existing databases that may already exist on the Oracle instance. Sometimes accidents do happen in database creation.

Reference Manual on Scientific Evidence: Third Edition

The worst thing a DBA can face when creating a new database is when a datafile or log filename in a parameter file may not have been changed before creating the second database.

This situation leaves the first database vulnerable to being overwritten when the second database is created, which causes the first database to be unusable. What is the difference between varchar and varchar2 data types?

Varchar can store up to bytes and varchar2 can store up to bytes. They are differentiated by space. What is the use of NVL function? NVL Value, replace value How do we get field detail of a table?

An alert is a window which appears in the center of the screen and overlays a portion of the current play. What is the fastest query method to fetch data from the table?

What is the parameter mode that can be passed to a procedure? Hash Cluster is a technique used for storing the table to make it faster to retrieve.

It order to retrieve the rows from the table, apply the hash value on the table. What are SET operators?Exercises What is an entity-relationship diagram? All the user processes that are connected to the database share an access to it. User Z is the last user logged off at p. The final option to be covered corresponds to situations where the DBA has the database open for use, and needs to make some changes to the database. This applies to any database environment.

The concept of an entity maps loosely to the nouns in the reality the database application is trying to model. Summary on tutorial Oracle Database 11g: What are SET operators? There are two issues associated with shutting down the database with immediate priority.