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))
      )

fixed-length cahracter field, paded with trailing blanks

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

Currently, same as CHAR

varchar(size)           varchar(30)

Variable length character field

varchar2(size)

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

Data time values

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

Variable length raw binary data

raw(size)

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.

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);

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;

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;

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

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.

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

update

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;

Function

Meaning