The free version of the Oracle Database 11g2 express edition could be downloaded from the

http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html

The documentation about the installation of the database and the application development could be found on
http://www.oracle.com/technetwork/database/database-technologies/express-edition/documentation/index.html

The list of documents on the next picture shows that there are a lot of to be read, learn, and practice.
The first to be read is the License document, then the Installation Guide, the Getting Started Guide, etc.

After the successful download of the database 11g2 on a Windows XP 32-bit computer,
here are the steps of the installation process that would take a couple of minutes:
step#1 Click twice on the DISK/setup.exe file to start the installation.

step#2 Then, accept the terms in the Oracle license agreement.

step#3 Next, choose destination location for the database files.

step#4 Next, decide about the password for the database accounts SYS and SYSTEM.

step#5 Review all settings such as destination folders and ports.

step#6 After a few minutes, the successful installation would finish with this message.

step#7 Start the Oracle 11g2 database by clicking on
AllPrograms=> Oracle Database 11g Express Edition => Get Started.

step#8 The Database would start in the Google Chrome Browser on the “127.0.0.1:8080/apex/”.

step#9 Click on Storage and login as the SYS user.

step#10 The Storage page would show the current tablespaces: SYSAUX, SYSTEM, UNDO, TEMP, and USER, and their free and used spaces in MB.

step#11 Logout from the database.
step#12 Open the Microsoft Command Prompt window and connect to the database as sysdba by using SQL*Plus command user interface.

SQL> sqlplus / as sysdba

sys@XE> select instance_name || version from v$instance;

INSTANCE_NAME||VERSION
---------------------------------
xe11.2.0.2.0

step#13 Unlock user “HR” account, change its password, and test it to be sure that all is set up correctly

sys@XE> ALTER USER hr ACCOUNT UNLOCK;

User altered.

sys@XE> ALTER USER hr IDENTIFIED BY hr;

User altered.

sys@XE> connect hr/hr
Connected.

hr@XE> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
EMP_DETAILS_VIEW VIEW
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE

8 rows selected.

hr@XE> connect / as sysdba
Connected.

step#14 Create a new schema

sys@XE> create user biljana identified by davidovic;

User created.

sys@XE> grant CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, -
> CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, -
> CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, -
> CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE -
> to biljana;

Grant succeeded.

sys@XE> connect biljana/davidovic
Connected.

biljana@XE> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
APEX$_ACL TABLE
APEX$_WS_FILES TABLE
APEX$_WS_HISTORY TABLE
APEX$_WS_LINKS TABLE
APEX$_WS_NOTES TABLE
APEX$_WS_ROWS TABLE
APEX$_WS_TAGS TABLE
APEX$_WS_WEBPG_SECTIONS TABLE
APEX$_WS_WEBPG_SECTION_HISTORY TABLE
DEMO_CUSTOMERS TABLE
DEMO_ORDERS TABLE
DEMO_ORDER_ITEMS TABLE
DEMO_PRODUCT_INFO TABLE
DEMO_STATES TABLE
DEMO_USERS TABLE
DEPT TABLE
EMP TABLE

17 rows selected.

step#15 Create a new table

biljana@XE> CREATE TABLE "BILJANA"."HOWWELEARN"
( "ID" NUMBER,
"HOW" VARCHAR2(20),
"VALUE" NUMBER(3,0);

Table created.

biljana@XE> alter table howwelearn add constraint test_pk primary key (id);
Table altered.

biljana@XE> CREATE SEQUENCE learn_seq START WITH 1 INCREMENT BY 1 NOCYCLE NOCACHE;
Sequence created.

biljana@XE> create trigger LEARN_TR_INSERT
before
insert on howwelearn
for each row
declare
var_id number;
begin
select learn_seq.nextval into var_id from dual;
:new.ID := var_id;
end;

Trigger created.

biljana@XE> insert into howwelearn(how, value) values( 'Lecture',5 );
1 row created.

step#16 Create the function funct_get_table_ddl and get DDL statement about the created table ‘HowWeLearn’

biljana@XE> CREATE OR REPLACE FUNCTION funct_get_table_ddl
RETURN CLOB
AUTHID CURRENT_USER
IS

-- Define local variables.

l_h NUMBER; --handle returned by OPEN
l_th NUMBER; -- handle returned by ADD_TRANSFORM
l_doc CLOB; --returned sql ddl statement

BEGIN

-- Specify the object type.

l_h := DBMS_METADATA.OPEN('TABLE');

-- Use filters to specify the particular object desired.

DBMS_METADATA.SET_FILTER(l_h,'SCHEMA','BILJANA');
DBMS_METADATA.SET_FILTER(l_h,'NAME','HOWWELEARN');

-- Request that the metadata be transformed into creation DDL.

l_th := DBMS_METADATA.ADD_TRANSFORM(l_h,'DDL');

-- Fetch the object.

l_doc := DBMS_METADATA.FETCH_CLOB(l_h);

-- Release resources.

DBMS_METADATA.CLOSE(l_h);

-- Return the result

RETURN l_doc;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

biljana@XE> SET PAGESIZE 0
biljana@XE> SET LONG 1000000
biljana@XE> SELECT funct_get_table_ddl FROM dual;
biljana@XE> set timing on
biljana@XE> SELECT funct_get_table_ddl FROM dual;

CREATE TABLE "BILJANA"."HOWWELEARN"
( "ID" NUMBER,
"HOW" VARCHAR2(20),
"VALUE" NUMBER(3,0),
CONSTRAINT "LEARN_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
Elapsed: 00:00:01.12

step#17 The data inserted into the table “HOWWELEARN” has been taken from my favorite YouTube channel GoogleTechTalks.

Here is the section from its ABSTRACT:

Explore the brain’s amazing ability to change throughout a person’s life. This phenomenon—called neuroplasticity—is the science behind brain fitness, and it has been called one of the most extraordinary scientific discoveries of the 20th century.

PBS had recently aired this special, The Brain Fitness Program, which explains the brain’s complexities in a way that both scientists and people with no scientific background can appreciate.

This is opportunity to learn more about how our minds work—and to find out more about the latest in cutting-edge brain research, from the founder of Posit Science and creator of the Brain Fitness Program software, Dr. Michael Merzenich.
The video “Think faster focus better and remember more – Rewiring our brain to stay younger” by Dr. Michael Merzenich can be watched on YouTube on

And, here is the important message learned:

biljana@XE> select 'After ' ||
rpad(how,15) ||
' We Learn ONLY ' ||
nvl(value,0) || '%'
from howwelearn;

After Lecture We Learn ONLY 5%
After Reading We Learn ONLY 10%
After Audio We Learn ONLY 20%
After Visual We Learn ONLY 20%
After Demonstration We Learn ONLY 30%
After Discussion We Learn ONLY 50%
After Practice by doing We Learn ONLY 75%
After Teach others We Learn ONLY 90%
After Nothing We Learn ONLY 0%

9 rows selected.

Elapsed: 00:00:00.00
biljana@XE>

As we can see from the result set, we remember things mostly only as we start teaching others about it.

Summary

In this post the test environment for the applications that are database-centric has been set up using the free express edition of the Oracle database 11g2.
I hope this will be helpful for anyone who would like to learn more about Oracle Database.

Thank you for visiting my blog!

Have a nice day!

P.S. Keep climbing Mount Olympus ( I mean keep learning and make things happen ) !