Oracle offer light XE editions, you can install on your home PC if you need to test something out.

1. sqlplus

Starting sqlplus

Use the command line and type 'sqlplus user/passs'

or click on "Run SQL Command Line"



connect <user>/<password>;

where <password> is your password and <user> is your username.

2. User Management

Start the HTML administration site. Go to Home -> Administration -> Manage Database Users -> Create Database User

3. useful SQL

List all tables

select * from tabs where table_name = 'BAS_PAYMENTS';

List all sequences

Use the all_sequences view.

SQL> desc all_sequences;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SEQUENCE_OWNER                            NOT NULL VARCHAR2(30)
 SEQUENCE_NAME                             NOT NULL VARCHAR2(30)
 MIN_VALUE                                          NUMBER
 MAX_VALUE                                          NUMBER
 INCREMENT_BY                              NOT NULL NUMBER
 CYCLE_FLAG                                         VARCHAR2(1)
 ORDER_FLAG                                         VARCHAR2(1)
 CACHE_SIZE                                NOT NULL NUMBER
 LAST_NUMBER                               NOT NULL NUMBER

Select  SEQUENCE_OWNER, SEQUENCE_NAME from all_sequences where sequence_owner = 'INTRANET';


4. Administration

Max processes

You can alter this using by changing a system variable then restarting.

select name, value from v$parameter where name like '%processes%';

alter system set PROCESSES=100 scope=SPFILE;

5. Oracle UCP for JDBC

Oracle Universal Connection Pool can be used as the connection pool. If you are using Oracle then consider using this over a generic connection pooling library.

It supports:

  • Connection validation without executing SQL queries. e.g. instead of running 'select 1 from dual', it can execute a lightweight ping on the database

  • fast connection failover

  • ONS (Oracle Services Notification) support for load balancing.

Oracle UCP also supports non-Oracle JDBC drivers.

6. Oracle database backup using imp and exp

Oracle import and export programs can help backup and restore databases. The imp and exp are for importing/restoring and exporting/dumping respectively. These are installed as part of Oracle XE server, you do not have to have a database running to execute these programs.

For command line help, use exp help=y and imp help=y.

7. ORA-28000: the account is locked

Login to sqlplus as the "system" user and unlock the user.

SQL> alter user magic_monster account unlock;

User altered.