Oracle

Published: Sunday, 18 November 2007

Oracle offer light XE editions that you can install on your home PC.

sqlplus

Starting sqlplus

Use the command line and type sqlplus user/pass or click on “Run SQL Command Line”

Commands

connect

connect <user>/<password>;

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

User Management

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

Useful SQL

List 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

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

Administration

Max processes

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

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

alter system set PROCESSES=100 scope=SPFILE;

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.

Oracle database backup using imp and exp

Oracle’s 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

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.
Oracle