The free version of the Oracle Database 11g2 express edition could be downloaded from the
The documentation about the installation of the database and the application development could be found on
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 --------------------------------- xe220.127.116.11.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.
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 ) !