Oracle 9i Database

Home » News and Society » Economics » Oracle 9i Database
Economics, News and Society No Comments

Introduction

The Oracle 9i database is a free relational database that makes use of system resources in all hardware architectures, to ensure maximum utilization in environments laden with information.

Oracle is basically a client / server to manage databases. Is a product sold worldwide, but the great power it has and its high price makes it only looks at very large companies and multinationals, as a rule

It is also the data set that provides the ability to store and go to these recursively defined as a relational model. It is also a product suite that provides a wide variety of tools.

OBJECTIVE:

The main goal of Oracle 9i SQL, constantly improve the data server technology and introduce the concept of relational database and SQL programming language. Also, create and maintain database objects, store, retrieve and manipulate data to solve complex challenges in information management for businesses of all types and sizes.

COMPONENTS of the system global area (SGA)

The SGA is a shared memory area used to store information and data control instance. It is created when the instance is up and is cleared when you stop using it (when you shutdown).

COMPONENTS:

Table Spaces: A tablespace is a logical division of a database. Every database has at least one tablespace (tablespace named SYSTEM). You can use other table spaces to group users or applications to ease maintenance and improve performance. Some examples of these table spaces can be: USERS (utility) and undo (to undo changes.)

Data Files: Each table space consists of one or more files called data files that are stored on disk. May be located in different folders and drives. The size of these files can be changed after creation.

Records reconstruction: Oracle maintains records of all transactions in the database. These transactions are recorded in files called log files online reconstruction. These files are used to retrieve transaction database in the right order, if there is a bug in the database. They also allow Oracle to coordinate the way in which data is written to disk.

Control files: the global physical architecture of a database is maintained through their control files, in which records the control information relating to all files in the database. They are used to maintain internal consistency and guide recovery operations. These files are fundamental to the database, so multiple copies are stored online. These files should be stored in separate physical disks.

Program Global Area (PGA)

This memory area contains data and control information for processes running on the Oracle server (related to the database, of course). The size and content of the PGA depends on the server options are installed. . Is a non-shared area in which you can type and contains:

* Session Information

* Cursor State

* Space Battery

Transactions

The term transaction describes a logical unit of work consists of one or more SQL statements, which should end with a commit or rollback statement. At that moment, will begin a new transaction and will be active until you run one of these two commands again.

Note that a transaction is not considered confirmed until it finishes writing the redo log file.

9I Oracle system privilege

PRIVILEGES the ability of a user within the database to perform certain operations or access to certain objects of other users.

Privileges can be of two types:

* 1. Setting the system, for example to create tables.

* 2. Privileges on objects, such as permission to select on a concrete slab.

* SYSTEM PRIVILEGES

Allows the user to do certain tasks on the database, such as creating a tablespace. These permissions are granted by the administrator or someone who has received permission to manage that kind of privilege.In general, system permissions, allow executing commands of the type DDL (Data Definition Language), such as CREATE, ALTER and DROP or type DML (Data Manipulation Language.

Syntax for granting permits.

SQL> GRANT CREATE USER, ALTER USER, DROP USER TO (nombre_usurio)

Example:

SQL> GRANT CONNECT, RESOURCE TO inventory;

PRIVILEGES ON OBJECTS

This type of privilege allows the user to some action on an object in the database, such as a table, view, function, etc. If a user does not get these privileges could only access their own objects. And these types of privileges the owner of the object gives the administrator or someone who has received this permission explicitly. Privileges on objects agree that an object (created by a user), these privileges can be SELECT, UPDATE, DELETE, INSERT.

Roles and profiles

* ROLES

Roles are sets of privileges. A role can be guaranteed a number of privileges on both the system and objects, while other roles can be guaranteed.

By default when you create a user from the Enterprise Manager is assigned the permission of Connect, which allows the user to connect to the database and create their own objects in their own scheme. Otherwise, we assign them manually.

Syntax to create a role and assign it to a user:

SQL> CREATE ROLE appl_dba;

Optionally, you can assign a key role:

SQL> SET ROLE IDENTIFIED BY appl_dba app_pwd;

To assign this role to a user:

SQL> GRANT appl_dba TO jdoe;

Another common use is to assign roles Object-level privileges, for example in an invoice table where we just want to be able to Querys and Inserts:

SQL> CREATE ROLE reference;

SQL> GRANT SELECT, INSERT on analista.factura TO consultation;

* PROFILE

Profiles to define resource constraints. For example we can define a profile that limits the number of concurrently open sessions for any user, and then apply the profile to one or more specific users.

BASIC SAFETY SCHEME

Practical exercises

SHOW USER user .- know that we are working

SQL> SHOW USER

the user is “SYSTEM”

User creation

SQL> CREATE USER US3

2 IDENTIFIED BY PUS3

3 PASSWORD EXPIRE

4 ACCOUNT LOCK;

User created.

Granting privileges

SQL> GRANT CREATE SESSION TO US3;

Grant finished