Oracle

1. sqlplus

connect <user>/<password>;
        

2. User Management

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

3. useful SQL

      select * from tabs where table_name = 'BAS_PAYMENTS';
      

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';

      

4. Administration

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.