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 |
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.
labdb.world = INTERNETPROG.WORLD = |
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
Once connected using the SQL*Plus tool, you
can now issue SQL commands to edit the table.
(note below we will do manipulations on the
customers and prices tables.)
>DESCRIBE customers;
>select * from customers where name='Butch Grewe';
>DESCRIBE productspace.products;
>SELECT * FROM pricespace.prices;
|
|
|
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(
|
|
ROWID | Row ID variable type | 6 bytes |
As a user, the DBA had to give you the privilege to create a table.
Using SQL*Plus to create a Tablebelow 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(
|
SQL> create table products(
|
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 Viewbelow 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; |
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 privilegeThere 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; |
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.
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
|
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 UserOnly the DBA does this and has the privilegeThere 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 |
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 Userbelow 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 Rolebelow 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 Rolebelow 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; |
*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 |