Oracle 101. How to be an oracle programmer, bluffers guide part 1
author: totierne version 0.3
TODO:
Give latest 9iR2 details,
(to startup and shutdown from the command line it is now
sqlplus /nolog
conn sys/password as sysdba
startup
sqlplus /nolog
conn sys/password as sysdba
shutdown abort
)
Note that I am particularly interested in people interested in migrating
to oracle,
http://raq437.uk2net.com/twiki/bin/view/TWiki/MigrationDotcom
Encourage growth by readers in a twiki.org or www.c2.com wiki style,
is getting a postcard more likely, Turloch, 33 Elm Mount Close, Beaumont,
Dublin 9, Ireland.
What insane memes do I want to send on this informative enough carrier wave...
:)
------------------
please write comments on: http://raq437.uk2net.com/twiki/bin/view/TWiki/OracleHowtoDiscussion
goto www.google.com and search... [but read my effort and send me comments
ecard etc at totnospamierne at hot mail dot com]
- Oracle/SQL
Tutorial
- Google
Directory - Computers > Software > Databases > Oracle > FAQs,
Help, and Tutorials
Oracle is a relational database management system
Most big programs store information in this way, alternatives are in files
or in non relational database systems.
su - oracle
password: xxxxxxxxxx
[oracle@localhost oracle]$ pwd
/home/oracle
[oracle@localhost oracle]$ ls
816home jre nsmail oraclextra
setup tmp
818db ns_imap oraInventory output.log
start
[run setup to set up any variables required, you do not need to
worry about this for now]
export ORACLE_SID=elm
[oracle@localhost oracle]$ . ./setup
[svrmgrl is the administration tool for startup and shutdown of oracle]
[oracle@localhost oracle]$ svrmgrl
Oracle Server Manager Release 3.1.6.0.0 - Production
Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.
Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
SVRMGR> connect internal
Connected.
SVRMGR> startup
ORACLE instance started.
Total System Global Area
56020976 bytes
Fixed Size
69616 bytes
Variable Size
39002112 bytes
Database Buffers
16777216 bytes
Redo Buffers
172032 bytes
Database mounted.
Database opened.
SVRMGR> exit
Server Manager complete.
[sqlplus is the command line user tool (non graphical) scott is the identifier
(schema) tiger is the passwod]
[oracle@localhost oracle]$ sqlplus scott/tiger
SQL*Plus: Release 8.1.6.0.0 - Production on Sat Feb 15 23:20:04 2003
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected to:
Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
[Describe table emp (Employee table)]
SQL> desc emp
Name
Null? Type
----------------------------------------- -------- ----------------------------
EMPNO
NOT NULL NUMBER(4)
ENAME
VARCHAR2(10)
JOB
VARCHAR2(9)
MGR
NUMBER(4)
HIREDATE
DATE
SAL
NUMBER(7,2)
COMM
NUMBER(7,2)
DEPTNO
NUMBER(2)
[Describe table dept (Department)]
SQL> desc dept
Name
Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO
NOT NULL NUMBER(2)
DNAME
VARCHAR2(14)
LOC
VARCHAR2(13)
[select all the information from emp]
SQL> select * from emp;
EMPNO ENAME JOB
MGR HIREDATE
SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH
CLERK 7902 17-DEC-80
800
20
7499 ALLEN
SALESMAN 7698 20-FEB-81
1600 300
30
7521 WARD
SALESMAN 7698 22-FEB-81
1250 500
30
EMPNO ENAME JOB
MGR HIREDATE
SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES
MANAGER 7839 02-APR-81
2975
20
7654 MARTIN SALESMAN
7698 28-SEP-81
1250 1400
30
7698 BLAKE
MANAGER 7839 01-MAY-81
2850
30
EMPNO ENAME JOB
MGR HIREDATE
SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK
MANAGER 7839 09-JUN-81
2450
10
7788 SCOTT
ANALYST 7566 19-APR-87
3000
20
7839 KING
PRESIDENT 17-NOV-81
5000
10
EMPNO ENAME JOB
MGR HIREDATE
SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN
7698 08-SEP-81
1500 0
30
7876 ADAMS
CLERK 7788 23-MAY-87
1100
20
7900 JAMES
CLERK 7698 03-DEC-81
950
30
EMPNO ENAME JOB
MGR HIREDATE
SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD
ANALYST 7566 03-DEC-81
3000
20
7934 MILLER CLERK
7782 23-JAN-82
1300
10
14 rows selected.
[select all the information from dept]
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
[oracle@localhost oracle]$ cd 816home
[oracle@localhost 816home]$ cd sqlplus
[oracle@localhost sqlplus]$ cd demo
[oracle@localhost demo]$ ls -l demobld.sql
-rw-r--r-- 1 oracle oracle
3542 Sep 19 1999 demobld.sql
[demobld is to build the scott/tiger demonstation tables, surely it is not worth complaining about this minor copyright infringement, maybe it is cat (unix) or type (dos) the command yourself]
[oracle@localhost demo]$ cat demobld.sql
... [ some coments on file follow. I should not really publish the file to the web , but is available on oracle distributions...]
[drop (delete) tables in case they already exist]
DROP TABLE xxx;
[create(build) tables]
CREATE TABLE ... (column_name type whether_it_can_be_null, ...)
[insert(add) values to tables]
INSERT INTO xxx VALUES
(val1,val2, dates are a bit odd, ...);
end of comments on file.
there is documentation available on all this stuff which I will get to you
you can select insert update on the tables and select with a where clause
to join the tables to merge using a criteria to match the tables such as
what department is 'KING' is in ?
SQL> desc emp
Name
Null? Type
----------------------------------------- -------- ----------------------------
EMPNO
NOT NULL NUMBER(4)
ENAME
VARCHAR2(10)
JOB
VARCHAR2(9)
MGR
NUMBER(4)
HIREDATE
DATE
SAL
NUMBER(7,2)
COMM
NUMBER(7,2)
DEPTNO
NUMBER(2)
SQL> desc dept
Name
Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO
NOT NULL NUMBER(2)
DNAME
VARCHAR2(14)
LOC
VARCHAR2(13)
emp.deptno = dept.deptno is the join condition where there is a match in both tables allowing well a match, joining the two rows of data one from each table
SQL> select DNAME from dept, emp where ENAME='KING' and emp.deptno =
dept.deptno;
DNAME
--------------
ACCOUNTING
SQL> exit
to shutdown oracle (to save memory etc)
>svrmgrl
Oracle Server Manager Release 3.1.6.0.0 - Production
Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.
Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
SVRMGR> connect internal
Connected.
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
Oracle is now on version 9iR2 which has replaced svrmgrl with sqlplus /nologin
[or something like that] or the graphical enterprise manager. An oracle database
is built from a graphical tool kicked off on login. Good Luck...
goto www.google.com and search...
- Oracle/SQL
Tutorial
- Google
Directory - Computers > Software > Databases > Oracle > FAQs,
Help, and Tutorials