Archive for the ‘SQL and Databases’ Category


To use a data source in WLS (e.g. to benefit from the built-in connection pooling, XA transactions etc.) you have to configure it from the WLS admin console.
Prior to this post, we installed the Oracle DB and Oracle WLS.

  1. start the admin console, e.g. http://localhost:7001/console
  2. in the left tree menu named ‘Domain Structure’ go to ‘Services’ -> ‘JDBC’ -> ‘Data Sources’
  3. click ‘New’ and enter your data:
    • name:     mh_JDBC_DS_orcl (just a unique name in WLS to identify your ds)
    • JNDI Name:     jdbc/mh_orcl
    • Database Type:    Oracle
    • Database Driver:     oracle.jdbc.xa.client.OracleXADataSource (use the XA version if your App participates in global/distributed transactions)
    • Database Name:     orcl (the name you specified in the db installation)
    • Host Name:     localhost (or your IP where the db listens)
    • Port:     1521 (port where the db listens)
    • Database User Name:  mh (user to login)
    • Password:     <pwd>
    • under ‘Targets’: check the server, e.g. ‘AdminServer’ where you want to use this ds
  4. You can explore your settings also on the file system:
    • C:\Oracle\Middleware\user_projects\domains\mh_domain\config\jdbc\mh_JDBC_DS_orcl-5723-jdbc.xml, and an entry to this file in
    • C:\Oracle\Middleware\user_projects\domains\mh_domain\config\config.xml

Alternatively, if you need to make multiple installations/configurations, there is the possibility to use the silent installation and WLST (WebLogic Scripting Tool).

Now you can call the ds in your application via JNDI or if you use JPA you need to specify the JNDI name in persistence.xml.

, , , ,


To install and run a HSQLDB in server mode proceed as follows:

  • Download the release from
  • Unzip it, e.g. C:\hsqldb-2.2.5\.
  • The HSQLDB-jar is then found at C:\hsqldb-2.2.5\hsqldb\lib\hsqldb.jar.
  • Create a batch file for starting the HSQLDB in server mode, e.g. C:\hsqldb-2.2.5\hsqldb\bin\hsqldbServer_start.bat with content:

@java -classpath ../lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:../data/MH --dbname.0 MH_DB

  • This creates and starts a db with name MH_DB with db-files in the directory C:\hsqldb-2.2.5\hsqldb\data. You can start several databases at once, replace the batch file content with:

@java -classpath ../lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:../data/MH1 --dbname.0 MH_DB1 --database.1 file:../data/MH2 --dbname.1 MH_DB2

  • You then can reach the HSQLDB via JDBC / JPA

driver: 'org.hsqldb.jdbcDriver'
url: 'jdbc:hsqldb:hsql://localhost/MH_DB'
user: 'SA'
pwd: ''

  • When using JPA with Hibernate then you can specify a dialect in persistence.xml (for other JPA providers, check the documentation):

<property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect" />

, , , , , ,


After installing the database, you may want to create a schema with tables, indexes, etc., to start your project.

Create User / Schema with Privileges

Log on as user SYSTEM (e.g. in sqldeveloper) and execute the script

-- clean SCHEMA (only if created earlier, for dev only!)

-- create
  DEFAULT TABLESPACE users -- if this line is omitted, the database default tablespace will be used; after installation it's 'USERS', to check it: SELECT property_value FROM database_properties WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE'; (if you want another existing tablespace: SELECT * FROM user_tablespaces;)
  QUOTA UNLIMITED ON users; -- such that you can insert data, you can also limit this

-- grant priviliges to create/alter/drop objects
  CREATE SESSION, -- to log on; equivalent to the role 'CONNECT'
TO mh;

-- though different, but shorter (also see remarks below)


  • if you have more users, create a role, and grant the role to the users
  • DROP and ALTER the named objects are implicitly granted (there exist no such privilege DROP TABLE)
  • the role RESOURCE provides the following system privileges: CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE. This role is provided for compatibility with previous releases of Oracle Database. You can determine the privileges encompassed by this role by querying the DBA_SYS_PRIVS data dictionary view.
  • Oracle recommends that you design your own roles for database security rather than relying on this role. This role may not be created automatically by future releases of Oracle Database.

Log off as SYSTEM, and log in as newly created user MH. Now you can create all objects with the user (fast setup for dev).

If you have more than one user, a better approach would be to run all scripts under an install user (SYSTEM would have enough priviliges ;-) ), but then don’t forget to name the objects fully qualified with schema names, e.g., CREATE schema.tablename (col1 numeric(20), …);.

Create Table with Primary Key and Foreign Key

We want to create a table with autoincrement primary key. This can be done in Oracle through a sequence and a “before insert” trigger. We additionally create a realistic environmet with foreign key and an index.

Suppose an already existing schema/user mh.

Note: A sequence does not guarantee a lückenlos Folge, i.e., we can get 1,2,3,7,10,11,…

CREATE TABLE mh.mh_table_2(
  id                 NUMERIC(20) PRIMARY KEY

CREATE TABLE mh.mh_table(
  id                 NUMERIC(20),
  name               VARCHAR2(255) NOT NULL,
  id_fk              NUMERIC(20),
  FOREIGN KEY (id_fk) REFERENCES mh.mh_table_2 (id),

A foreign key is not automatically indexed like a primary key! You have to declarate an index yourself, see below.

Create Sequence, Trigger

A sequence for thread-safely incrementing a value and trigger putting the autoincrement value of the sequence into when inserting row

CREATE SEQUENCE mh.mh_sequence
  -- MAXVALUE 9999999999999999999999999999 is default
  NOCYCLE -- is default: means after reaching the max, don't restart
  NOORDER -- is default: means requests are not put in order
  CACHE 100; -- is default with 20: number of next values that are hold in cache, for performance

  -- if condition: when manually id is set, then do nothing (if this makes sense in your context)
    SELECT mh.mh_sequence.NEXTVAL INTO FROM dual;

Don’t forget the slash at the end!

Create Index (unique, bitmap, function based)

The foreign key mh_table.id_fk may need an index, as well as may need a function based index (with the funny name FBI) to support performant search on the first letter:

CREATE INDEX mh.idx_mh_table_id_fk ON mh.mh_table (id_fk);
-- or if this column needs a unique constraint
-- or if this column only has a few (say 100) different values (like status ids)

CREATE BITMAP INDEX mh.idx_mh_table_name_firstletter ON mh.mh_table (SUBSTR(name, 1,1));

The latter should contain only the 26 letters of the alphabet, hence we choose the BITMAP version (only available in Enterprise Edition).

Gather Table Statsitics

If this table would already have have data, you should gather table statistics (Oracle cost based optimizer (CBO) uses these),

parameter “cascade=>TRUE”, not only stats on columns, but also on indexes (it’s not clear to me, what it does in detail internaly)

exec dbms_stats.gather_table_stats(OWNNAME=>'MH', TABNAME=>'MH_TABLE', cascade=>TRUE);
-- or if you want to update only a certain index:
exec dbms_stats.gather_index_stats(OWNNAME=>'MH', INDNAME=>'IDX_[...]');

, , , ,


Another vm for dev, another installation of Oracle Database 11g Release 2 ( Enterprise Edition, 64-bit version for Microsoft Windows (x64). This short description is not intended for production installation, but for local installation for dev purposes. You can download and use the database for dev purposes; excerpt from the “OTN License Agreement”, which you have to accept:

[...] LICENSE RIGHTS: We grant you a nonexclusive, nontransferable limited license to use the programs only for the purpose of developing, testing, prototyping and demonstrating your application, and not for any other purpose. [...]

Summary: Quite easy with the exe-installer, but you need time: about 2-3 hours if you know what you’re doing (download of the GBs and installation), if not longer :-)


At first you should check physical requirements: Hard drive 5.92 GB (+5GB for the installation zips and its extraction), RAM 1 GB minimum or 2 GB minimum for Windows 7 (for further details see the documentation)

Download and unzip

1. At the download page of Oracle Database, download the two parts

  • (about 1,1 GB)
  • (about 0,9 GB)

Optional downloads:

  • Documentation package, if you want offline all the db 11gR2 oracle docs in html and pdf version.
  • Interactive quick reference, for which you need a Flash Player. This gives a nice overview of dba views, architecture and background processes.
  • Examples package
  • Client package
  • there are more downloads, e.g. Oracle Grid Infrastructure (necessary e.g. for Oracle Real Application Clusters (RAC)), or Web Tier Utilities (for HTTP access to the database via the Apache HTTP Server), etc.
  • sqldeveloper for sql-Development:

2. Unzip both files at the same directory level: I created C:\Oracle\db_11gR2\, put both zips there, and chose “Extract here” with 7-zip. This will produce one directory of name database.


3. Double-click setup.exe to start the Installer

4. Follow the installation wizard, which will guide you straightforward through the installation.

  • Choose the desired installation: I choose database with configuration for a desktop (not server) for a fast setup, and the Enterprise Edition.
  • I change the Oracle base directory and accept the other suggestions:
    • Oracle Base directory: C:\Oracle
    • software directory: C:\Oracle\product\11.2.0\dbhome_1
    • directory of db file: C:\Oracle\oradata
    • global database name: e.g. orcl.[domain name]
    • administrative password for user SYS: for dev purposes you don’t have to follow the guidelines, it’s enough if it’s not empty and no user name.
  • You get a summary, which you can save as a file (good to have for later reference…). Check it, and hit “Finish” to begin the installation, which takes about 20-30 min., time to have a cup of coffee ;-)

5. After Completion you get a report:

  • Log files are under [Oracle base dir]\cfgtoollogs\dbca\orcl
  • global database name : orcl.[domain name]
  • SID (system id): orcl
  • name of server parameter file: [software dir]\database\spfileorcl.ora
  • db control URL (Enterprise Manager): https://localhost:1158/em

Database Users / Schemas

Admin Users/Schemas

6. After installation only the admin-users SYS, SYSTEM, DBSNMP, SYSMAN are not locked. Still in the installer or later (via OEM, sqlplus, or sqldeveloper), you can lock/unlock users and change the default passwords. In my dev environment I changed the one for SYSTEM to the same as for SYS. Later on I also change the password of SYSMAN to the same: Logon via sqlplus or sqldeveloper as a dba user, and issue the command: alter sysman identified by [pwd];

  • SYS: You have already changed the default pwd change_on_install for SYS with the Installer. It possesses the DBA role. All of the base tables and views for the database data dictionary are stored in the schema SYS. To maintain the integrity of the data dictionary, tables in the SYS schema should never be modified by any user or database administrator, and no one should create any tables in the schema of user SYS.
  • SYSTEM: default pwd is manager. It possesses the DBA role. SYSTEM is used to create additional tables and views that display administrative information, and internal tables and views used by various Oracle Database options and tools. Never use the SYSTEM schema to store tables of interest to non-administrative users.
  • SYSMAN: It is used to perform OEM administration tasks.
  • DBSNMP: default pwd is the same as its name. The management agent of OEM uses this account to monitor and manage the database.

Sample Users/Schemas

7. All other users are locked: Sample schema accounts (e.g. SCOTT, HR, OE, PM, SH, IX, BI, etc.); I unlock those, just to already have a few schemas and tables to experiment. There are scripts for resetting those under [software dir]\demo\schema, if you messed them up ;-)

Internal Users/Schemas

8. Internal accounts (individual Oracle Database features or components can have their own schemas); I don’t change anything.

Exploring OEM (Oracle Enterprise Manager)

. Now you can logon to OEM with an admin account https://localhost:1158/em

Windows Services

9. As you notice in the Windows task manager, some Windows Services are created. I’ll explain the three most important, to start up and shut down the db (alternatively you can use the Enterprise Manager):

  • OracleService[SID], which is the Oracle database instance.
  • Oracle[ORACLE_HOME]TNSListener, which is your listener. The listener is required for clients (e.g. sqlplus, sqldeveloper) to connect to your database.
  • OracleDBConsole[SID], which enables clients to connect to Database Control.

In our context SID=orcl and ORACLE_HOME=OraDb11g_home1, i.e., we have the services OracleServiceORCL, OracleOraDb11g_home1TNSListener, OracleDBConsoleorcl. Start or stop all three services through Start->Control Panel->Administrative Tools->Services: start/stop. You can deactivate the other services for dev purposes.

, , ,