ORACLE : Installation, Instance, Tablespace, Tables, Data ?

If you want to learn Oracle without any DB concepts ... stop for now and try a easier MySQL before jumping onto Oracle. That's just an advice though and you may or may not agree!. [My Mysql tutorial is available here].

If you want to try out Oracle then consider installing Oracle 10 g Express edition (otherwise known as Oracle XE) which is distributed free from Oracle Corp. [My Oracle XE tutorial is available here]

Installing Oracle 10 g on Fedora 5:

There is some package called  libaio (check for other dependencies too), which needs to be installed before the proper installation of Oracle. Search the web and install something like libaio-0.3.103-2.2.i386.rpm , login as root : USE : rpm -ivh libaio-0.3.106-2.2.i386.rpm

create user "oracle" : USE : useradd -m oracle
make install directories :mkdir /oracle
                                      mkdir /oracle/10g

The installation window is just the same as in Windows.

Using SQLPLUS to manipulate Oracle (for Windows Operating system) :

Invoke SQLPLUS from command prompt by typing SQLPLUS /NOLOG or open SQLPLUS application from Program Files>Oracle>Application Development>SQLPLUS

FOR DOS COMMAND PROMPT :

USE command : connect <TABLESPACE USER>/password   e.g. conn DBA_RITESHSPACE/rakesh
Note: A user must be assigned, granted privileges and the account unlocked  before logging into custom tablespace. NOTE : you can use CONNECT OR CONN

DESC or DESCRIBE : Used to describe the table structure present in the tablespace.

USE : DESC <tablename>  e.g. DESC CHENNAI ;

USE : SELECT * FROM <tablename> to view all the data inside the table. e.g. SELECT * FROM CHENNAI

INSERTING DATA INTO A TABLE in Oracle :

USE : INSERT INTO <tablename> VALUES (<'column1','column2','column3',.....'column n'>) ;        e.g. INSERT INTO CHENNAI VALUES (6, 'SOME NAME');   The structure should exactly match as the table structure else Oracle loves to shower you with errors.

DELETING DATA FROM A TABLE in Oracle :

USE : DELETE FROM <tablename> WHERE <SOME CONDITION> (Careful without specifying any condition for deletion will remove all records from the Table.  e.g. DELETE FROM CHENNAI WHERE id=2;

UPDATING A COLUMN DATA OF A TABLE in Oracle :

USE : UPDATE <tablename> SET <column name>=blahblah WHERE <some condition>    e.g. UPDATE CHENNAI SET NAME = 'RITESH MANDAL' WHERE id =1 ;

ADD AND DROP COLUMN INSIDE TABLE (Oracle) :

USE : ALTER TABLE <tablename> ADD (<field name> <field type>) ;  e.g. ALTER TABLE CHENNAI ADD (PHONE NUMBER(10));

USE : ALTER TABLE <tablename> DROP <field name> ; e.g. ALTER TABLE CHENNAI DROP COLUMN PHONE;

RENAMING A COLUMN INSIDE TABLE (Oracle) :

USE : ALTER TABLE <tablename> RENAME COLUMN <field name> TO <new field name>;     e.g. ALTER TABLE CHENNAI RENAME COLUMN PHONE TO MOBILEPHONE ;

MODIFY A COLUMN INSIDE TABLE (Oracle) :

USE : ALTER TABLE <tablename> MODIFY (<field name> <new field type>);     e.g. ALTER TABLE CHENNAI MODIFY (NAME VARCHAR(40)) ;

NOTE : It is necessary to remove all the records of the column before modification. The modification to a column with data is only possible when  incremental value of the same data type is used.

Learning Data types : The data types every database systems handle is slightly different. The list is exhaustive with Oracle when compared to MySQL and others. Few databases have unique datatype as well, like PostgreSQL has  currency datatype MONEY while others don't have such

Tablespace : What's that ?
Tablespaces created are actually virtual and within this virtual (theoretical) Tablespaces all actual tables exits which holds the data. So you cannot create tables in a Instance unless tablespace is created. A tablespace : "system" is automatically created (by default) along with every Database instance.

That's enough for the basics. 

Resources : www.oracle.com

 NEXT PAGE >>   NEXT PAGE >>