Oracle Database


Oracle 8i = latest version of Oracle, has built-in support for not only accessing Oracle DB through Java but, also, allows to store "Java proceedures" directly in the database (hence has a built in JVM)

   from www.oracle.com

Installation Client Tools DB Identification SQL*Plus
Data Types Table Creation Tablespace Creation Views
Dictionary Tables and Views Users, Roles, Grants Built-In Functions  

Installation

It is during the installation of the Oracle Database and later using Oracle DBA tools that you may create the number of tables and their configuration for the database you are creating.  


 

Client Tools


 


Identification

You identify an Oracle Database by the following information: This information should be added to an Oracle configuration file names tnsnames.ora using a text editor or the Net8 Assistant client SW Oracle provides to setup identification information for the purposes of connection. The following shows an example of some items that could be listed in this file:
 

labdb.world =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP) (HOST = 134.154.11.126) (PORT = 1521))
        (CONNECT_DATA = (SID = labdb))
      )

INTERNETPROG.WORLD =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP) (HOST = 207.62.129.150) (PORT = 1521))
        (CONNECT_DATA = (SID = intprog))
      )

The first entry means means that on the (134.154.11.126) server under port number 1521, Oracle was installed with an SID = labdb to identify the table space under question and that TCP communications protocol should be used in accessing it. You will use the labdb string to connect via SQL*Plus.

The second entry means that on the (207.62.129.150) server under port number 1521, Oracle was installed with an SID = intprog to identify the table space under question and that TCP communications protocol should be used in accessing it. You will use the INTERNETPROG string to connect via SQL*Plus
 


SQL*Plus ....creating and editing database

To connect to the Oracle Database using a client tools such as SQL*Plus  to enter data into a table,  you need to know the following:

Some Oracle8i Datatypes

Datatype
Description
Maximum Size
CHAR

fixed-length cahracter field, paded with trailing blanks

char(size)                  char(30) //30 characters

2000 bytes
VARCHAR

Currently, same as CHAR

varchar(size)           varchar(30)

 
VARCHAR2

Variable length character field

varchar2(size)

4000 bytes
LONG Variable length character data 2GB
NUMBER

Variable length numeric data

number

OR

number(l,d)      where "l" stands for length and "d" for the number of decimal digits

number(5,2)       can store numbers like 100.02

1x10^-130 to 9.99x10^125
BOOLEAN holds values of true or false only. This is actually a PL/SQL data type.  
DATE

Data time values

represents using format DD-MON-YY (e.g. 09-SEP-0 which means September 9, 2004).

Dec 31, 9999
RAW

Variable length raw binary data

raw(size)

20000 bytes
LONG RAW Variable-length raw binary data 2 GB
VARRAY

New to Oracle 8i, an array that is an ordered list.

varray(num_elements) of type

e.g. varray(100) of number     // this is an array of                                                    maximum of  100                                                    numbers.

 
ROWID Row ID variable type 6 bytes
User Defined Type

User can define their own data type. You do so using the following SQL command. As a user, the DBA had to give you the privilege to be able to create user-defined data types.

create type  your_data_type_name as XXXX

where XXX specifies the kind of info you want represented as your_data_type. Here are two examples:

create type price_list as varray(100) of number;

 

create type room_type as object(

capacity        number,

podium         boolean,

computers    number);

 

 
ROWID Row ID variable type 6 bytes

 


 

Creating a Table

As a user, the DBA had to give you the privilege to create a table.

Using SQL*Plus to create a Table

below are two examples of creating tables. The second specifies the tablespace the table should appear in, used if you have more than one tablespace. It also specifies the initial size, how it grows in size and minimum number of extents allocated for it. Note: the first example will place the table inside of the user's default tablespace (as specified by the DBA during creation of user profile).

 

SQL> create table customer(

last_name       varchar2(30) not null,

state_cd          varchar(2),

sales                 number);

SQL> create table products(

name              varchar2(30) not null,

description    varchar2(300),

price             number(4,2) )

tablespace     productspace

storage(initial 25k next 25k minextents 1);



Creating Views in Oracle

Views represent a collection of one ore more SQL commands that you can invoke with a single name. As a user, the DBA had to give you the privilege to be able to create a view.

Using SQL*Plus to create a View

below is an example to create a view and then how to invoke it.

 

SQL> create  view    v_customer_sales   as

           select sales from customer;

 

SQL> select   * from v_customer_sales;

 

 


Creating Tablespace

As a user, the DBA had to give you the privilege to be able to create a tablespace.

Usually the DBA does this and has the privilege

There are GUI Administration tools (e.g Oracle Secuirty Manager which is part of Oracle Enterprise Manager) to do this work and it is best to use them. Alternatively you can use the Server Manager tool which has a console interface. The Server Manager is a tool that is run directly on the Database Server (you can not run from a client machine....you can telnet to the server and run through the telnet session if you have the ability to telnet). The Server Manager takes SQL commands to create the tablespace. Below we create a tablespace called productspace that is associated with the datafile /home/oracle/oradata/grewedatafile.dbf. Initially we have set the size to 20Mbytes, with storage set initially to use a chunk of 10K and each time allocateds 50K at a time with a minimum of 1 Extent and Maximum of 999 Extents. Note that at the end we require the tablespace to be ONLINE. If we did not do so, we could not access the tablespace.

 

Svrmgr> create tablespace productspace

                datafile '/home/oracle/oradata/grewedatafile.dbf' size 20M

                default storage (initial 10K next 50K

                                            minextents 1 maxextents 999)

                 online;

 


The Oracle Data Dictionary Tables and Views

Data Dictionary Tables

Collection of tables that store info about database objects such as tables, views, etc. In Oracle, these tables are owned by SYS and created at database creation..


Data Dictionary Views

Built on data dictionary tables.  Used to query the data dictionary.  Recall views are stored SQL statements.

DBA_* = shows info about all objects in database
ALL_* = shows info about all objects in database that can be accessed by user querying.
USER_*= shows info about all objects in databases that are OWNED by user querying.
Data Dictionary View Information it Contains
DBA_CATALOG All database tables, views and sequences
DBA_TABLES All tables in database
DBA_USERS All users of database
DBA_VIEWS All views in the database
DBA_DATA_FILES All data files and tablespaces in database
V$SESSION Current sessions running against the database
V$DATABASE  info such as DB name, archive log mode, etc.
V$PARAMETER DB parameters in effect.
How to Invoke Data Dictionary Views (if DBA has made active on your database):

1) Start SQLPlus and connect to DB of choice
2) Execute view as desired....below are some examples

select * from USER_TABLESs; // will get all fields from                                               user_tables;

select table_name from USER_TABLES; //will retrieve                                                                      //all of the                                                                      //names of user                                                                      //tables

select * from DBA_TABLES;   //will get everything from all tables

select name, value, description from v$parameter;  //givecolumns name,value,description

 


Users, Roles and Granting Privileges

Users = these are actually users of a database.

Privilege = represent acess abilities to portions of a database. Each user has assigned to him/her a set of privileges.

Role = a group of privileges that are collected together under the name of a particular role and can be granted to user(s).

Grants = privileges given out by owners of objects (e.g. tables), allowing other users to work with their data.

Revoke = revoking privileges givent to a user.

As a user, the DBA had to give you the privilege to be able to create users and roles.

Creating a User

Only the DBA does this and has the privilege

There are GUI Administration tools (e.g Oracle Secuirty Manager which is part of Oracle Enterprise Manager) to do this work and it is best to use them. But, below shows the SQL statements to create a user named grewe with the password mygrewe where the default tabelspace of SYSTEM is given and a temporary tablespace (where keep interim data) of TEMP is given. This user is then various system privileges: the ability to create a session and connect to the database. Finally, the DBA has previously setup a role (see below) called ALL and given this user this role.

SQL> create user "grewe" identified by "mygrewe"

           default tablespace "SYSTEM" temporary tablespace "TEMP"

           account unlock;

SQL> grant create session to "grewe";

SQL> grant "connect" to "grewe";

SQL> alter user "grewe" default role ALL;

Other SYSTEM related Privileges that can be given to a User:

(this is a partial list....the focus of the class is not to teach you to become a DBA)

See Database Terms or reading or do an Internet Search if you do not know the meaning of a term.

CREATE SESSION Ability to create a session
CREATE SNAPSHOT Ability to create a snapshot of the current database
CREATE SYNONYM Ability to create a synonym
CREATE TABLE Ability to create a table
CREATE TABLESPACE Ability to create a tablespace
CREATE TRIGGER Ability to create a trigger
CREATE TYPE Ability to create a user-defined data type
CREATE USER Ability to create a user
CREATE VIEW Ability to create a view
DELETE ANY TABLE Ability to delete any table.
CONNECT Ability to connect to database.

 

Altering Password of a user

SQL> alter user user_name identified by new_user_password;

Reserved Username

public

represents ALL Users of a database.

Some kinds of Access Privileges in Oracle

select

Allows user to look at contents of table.

insert

Allows user to create records/rows in table

update

Allows user to modify records/rows in a table

delete

Allows user to delete records/rows in a table

 

Using SQL*Plus to grant Privileges to a User

below is a generic and real example on how to grant privileges

NOTE: You can assign multiple privileges to a user.

NOTE: You can use the SQL command revoke to take back privileges from a user.

SQL> grant privilege on object to user;

 

SQL> grant select,insert on customer to lgrewe,butch;

 

Using SQL*Plus to create a Role

below is a generic and real example on how to create roles. Then you must subsequently grant privileges to the role.

NOTE: You can use the SQL command revoke to take back privileges from a role.

As a user, the DBA had to give you the privilege to be able to create a role.

SQL> create role role_name;

SQL> grant privilege1 on object1 to role_name;

SQL> grant privilege2 on object2 to role_name;

 

SQL> create role high_level_customers;

SQL> grant select on customer to high_level_customers;

 

Using SQL*Plus to give Users a Role

below is a generic and real example on how to assign a user a role.

NOTE: you can assign multiple roles to a user.

 

 

SQL> grant role_name to user;

SQL> grant high_level_customers to lgrewe, butch;

 

 

 

 


Oracle Built-In Functions

*these are only some of the built-in functions, see your reading or go to www.oracle.com to learn more about these and others.

Function

Meaning

ABS absolute value
CEIL ceiling
FLOOR the floor
MOD modular
POWER x to power of y
ROUND round off the number
SIGN retrieve the sign
SQRT square root
TRUNC truncate
AVG take the average
MAX the maximum of set
MIN the minimum of set
STDDEV calculate standard deviation
SUM take the sum
SYSDATE get the current sytem date

 

 

© Lynne Grewe