Define your favorite editor for sqlplus

Two way to do so.

1. Inside sqlplus : define _editor=vi
2. Outside sqlplus : export EDITOR=vi

lock and create/drop index

Conclusion:

DDL (CREATE&DROP INDEX) conflict with DML. More explanation can be found below

Locks and ORA-00054 error

SQL> create table t1 (f1 number);

Table created.

SQL> select * from t1;

no rows selected

SQL> create index t1_idx on t1(f1);

Index created.

SQL> drop index t1_idx;
drop index t1_idx
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


SQL> commit;

Commit complete.

SQL> select * from t1;

no rows selected

SQL> select * from t1;

F1
----------
2

SQL> drop index t1_idx;

Index dropped.

SQL> create index t1_idx on t1(f1);
create index t1_idx on t1(f1)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Using gdb for debug

install Oracle XE on Ubuntu x86_64

1. download oracle-xe_10.2.0.1-1.0_i386.deb from otn.oracle.com
2. Double click the deb file, througs you below error message
"Error: wrong architecture i386"
3. Try below command

hmc@hmc-desktop:~/Desktop$ sudo dpkg -i --force-architecture oracle-xe_10.2.0.1-1.0_i386.deb
[sudo] password for hmc:
dpkg - warning, overriding problem because --force enabled:
package architecture (i386) does not match system (amd64)
Selecting previously deselected package oracle-xe.
(Reading database ... 344121 files and directories currently installed.)
Unpacking oracle-xe (from oracle-xe_10.2.0.1-1.0_i386.deb) ...
dpkg: dependency problems prevent configuration of oracle-xe:
oracle-xe depends on libaio (>= 0.3.96) | libaio1 (>= 0.3.96); however:
Package libaio is not installed.
Package libaio1 is not installed.
dpkg: error processing oracle-xe (--install):
dependency problems - leaving unconfigured
Errors were encountered while processing:
oracle-xe

4. Repeat below command to install missing packages

hmc@hmc-desktop:~/Desktop$ sudo apt-get install libaio*
Reading package lists... Done
Building dependency tree
Reading state information... Done
Note, selecting libaiksaurus0c102 for regex 'libaio*'
Note, selecting libaiksaurus-1.2-data for regex 'libaio*'
Note, selecting libaiksaurusgtk-1.2-dev for regex 'libaio*'
Note, selecting libaiksaurusgtk-1.2-0 for regex 'libaio*'
Note, selecting libaio1 for regex 'libaio*'
Note, selecting libaiksaurus-1.2-dev for regex 'libaio*'
Note, selecting libaiksaurus0 for regex 'libaio*'
Note, selecting libaiksaurusgtk0c102 for regex 'libaio*'
Note, selecting libaio-dev for regex 'libaio*'
Note, selecting libaiksaurusgtk-1.2-0c2a for regex 'libaio*'
Note, selecting libaiksaurusgtk0 for regex 'libaio*'
Note, selecting libaio for regex 'libaio*'
Note, selecting libaio1-dbg for regex 'libaio*'
Note, selecting libaiksaurus-1.2-0 for regex 'libaio*'
Note, selecting libaiksaurusgtk-bin for regex 'libaio*'
Note, selecting gaiksaurus instead of libaiksaurusgtk-bin
Note, selecting libaiksaurus-1.2-0c2a for regex 'libaio*'
Note, selecting libaiksaurusgtk-dev for regex 'libaio*'
Note, selecting libaiksaurusgtk-1.2-dev instead of libaiksaurusgtk-dev
Note, selecting libaiksaurus-bin for regex 'libaio*'
Note, selecting aiksaurus instead of libaiksaurus-bin
Note, selecting libaiksaurus-data for regex 'libaio*'
Note, selecting libaiksaurus-1.2-data instead of libaiksaurus-data
Note, selecting libaiksaurus-dev for regex 'libaio*'
Note, selecting libaiksaurus-1.2-dev instead of libaiksaurus-dev
The following packages were automatically installed and are no longer required:
ubuntustudio-icon-theme ubuntustudio-gdm-theme ubuntustudio-theme
ubuntustudio-wallpapers
Use 'apt-get autoremove' to remove them.
The following extra packages will be installed:
aiksaurus gaiksaurus libaiksaurus-1.2-0c2a libaiksaurus-1.2-data
libaiksaurus-1.2-dev libaiksaurusgtk-1.2-0c2a libaiksaurusgtk-1.2-dev
libaio-dev libaio1 libaio1-dbg
The following NEW packages will be installed:
aiksaurus gaiksaurus libaiksaurus-1.2-0c2a libaiksaurus-1.2-data
libaiksaurus-1.2-dev libaiksaurusgtk-1.2-0c2a libaiksaurusgtk-1.2-dev
libaio-dev libaio1 libaio1-dbg
0 upgraded, 10 newly installed, 0 to remove and 1 not upgraded.
Need to get 513kB of archives.
After this operation, 1642kB of additional disk space will be used.
Do you want to continue [Y/n]? y
Get:1 http://tw.archive.ubuntu.com hardy/main libaiksaurus-1.2-data 1.2.1+dev-0.12-5 [318kB]
Get:2 http://tw.archive.ubuntu.com hardy/main libaiksaurus-1.2-0c2a 1.2.1+dev-0.12-5 [25.2kB]
Get:3 http://tw.archive.ubuntu.com hardy/universe aiksaurus 1.2.1+dev-0.12-5 [11.9kB]
Get:4 http://tw.archive.ubuntu.com hardy/main libaiksaurusgtk-1.2-0c2a 1.2.1+dev-0.12-5 [33.8kB]
Get:5 http://tw.archive.ubuntu.com hardy/universe gaiksaurus 1.2.1+dev-0.12-5 [9524B]
Get:6 http://tw.archive.ubuntu.com hardy/main libaiksaurus-1.2-dev 1.2.1+dev-0.12-5 [28.1kB]
Get:7 http://tw.archive.ubuntu.com hardy/main libaiksaurusgtk-1.2-dev 1.2.1+dev-0.12-5 [35.7kB]
Get:8 http://tw.archive.ubuntu.com hardy/main libaio1 0.3.106-8ubuntu1 [8430B]
Get:9 http://tw.archive.ubuntu.com hardy/main libaio-dev 0.3.106-8ubuntu1 [31.4kB]
Get:10 http://tw.archive.ubuntu.com hardy/main libaio1-dbg 0.3.106-8ubuntu1 [11.2kB]
Fetched 513kB in 4s (112kB/s)
Selecting previously deselected package libaiksaurus-1.2-data.
(Reading database ... 346502 files and directories currently installed.)
Unpacking libaiksaurus-1.2-data (from .../libaiksaurus-1.2-data_1.2.1+dev-0.12-5_all.deb) ...
Selecting previously deselected package libaiksaurus-1.2-0c2a.
Unpacking libaiksaurus-1.2-0c2a (from .../libaiksaurus-1.2-0c2a_1.2.1+dev-0.12-5_amd64.deb) ...
Selecting previously deselected package aiksaurus.
Unpacking aiksaurus (from .../aiksaurus_1.2.1+dev-0.12-5_amd64.deb) ...
Selecting previously deselected package libaiksaurusgtk-1.2-0c2a.
Unpacking libaiksaurusgtk-1.2-0c2a (from .../libaiksaurusgtk-1.2-0c2a_1.2.1+dev-0.12-5_amd64.deb) ...
Selecting previously deselected package gaiksaurus.
Unpacking gaiksaurus (from .../gaiksaurus_1.2.1+dev-0.12-5_amd64.deb) ...
Selecting previously deselected package libaiksaurus-1.2-dev.
Unpacking libaiksaurus-1.2-dev (from .../libaiksaurus-1.2-dev_1.2.1+dev-0.12-5_amd64.deb) ...
Selecting previously deselected package libaiksaurusgtk-1.2-dev.
Unpacking libaiksaurusgtk-1.2-dev (from .../libaiksaurusgtk-1.2-dev_1.2.1+dev-0.12-5_amd64.deb) ...
Selecting previously deselected package libaio1.
Unpacking libaio1 (from .../libaio1_0.3.106-8ubuntu1_amd64.deb) ...
Selecting previously deselected package libaio-dev.
Unpacking libaio-dev (from .../libaio-dev_0.3.106-8ubuntu1_amd64.deb) ...
Selecting previously deselected package libaio1-dbg.
Unpacking libaio1-dbg (from .../libaio1-dbg_0.3.106-8ubuntu1_amd64.deb) ...
Setting up libaiksaurus-1.2-data (1.2.1+dev-0.12-5) ...
Setting up libaiksaurus-1.2-0c2a (1.2.1+dev-0.12-5) ...

Setting up aiksaurus (1.2.1+dev-0.12-5) ...
Setting up libaiksaurusgtk-1.2-0c2a (1.2.1+dev-0.12-5) ...

Setting up gaiksaurus (1.2.1+dev-0.12-5) ...

Setting up libaiksaurus-1.2-dev (1.2.1+dev-0.12-5) ...
Setting up libaiksaurusgtk-1.2-dev (1.2.1+dev-0.12-5) ...
Setting up libaio1 (0.3.106-8ubuntu1) ...

Setting up libaio-dev (0.3.106-8ubuntu1) ...
Setting up libaio1-dbg (0.3.106-8ubuntu1) ...
Processing triggers for libc6 ...
ldconfig deferred processing now taking place

5. Try again

hmc@hmc-desktop:~/Desktop$ sudo dpkg -i --force-architecture oracle-xe_10.2.0.1-1.0_i386.deb
dpkg - warning, overriding problem because --force enabled:
package architecture (i386) does not match system (amd64)
(Reading database ... 346600 files and directories currently installed.)
Preparing to replace oracle-xe 10.2.0.1-1.0 (using oracle-xe_10.2.0.1-1.0_i386.deb) ...
Unpacking replacement oracle-xe ...
Setting up oracle-xe (10.2.0.1-1.0) ...
Executing Post-install steps...
-e You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database.


hmc@hmc-desktop:~/Desktop$ sudo /etc/init.d/oracle-xe configure

Oracle Database 10g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 10g Express
Edition. The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts. Press to accept the defaults.
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:

Specify a port that will be used for the database listener [1521]:

Specify a password to be used for database accounts. Note that the same
password will be used for SYS and SYSTEM. Oracle recommends the use of
different passwords for each database account. This can be done after
initial configuration:
Confirm the password:

Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]:

Starting Oracle Net Listener...Done
Configuring Database...Done
Starting Oracle Database 10g Express Edition Instance...Done
Installation Completed Successfully.
To access the Database Home Page go to "http://127.0.0.1:8080/apex"

6. and edit your ~/.bashrc of id oracle

ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_HOME
export ORACLE_SID=XE

export PATH

7. If you like rlwrap, define the alias for sqlplus in .bashrc

alias sqlplus='rlwrap sqlplus'

hmc@hmc-desktop:~$ sudo su - oracle
oracle@hmc-desktop:~$ pwd
/usr/lib/oracle/xe
oracle@hmc-desktop:~$ sqlplus "/as sysdba"
-su: sqlplus: command not found
oracle@hmc-desktop:~$ . .bashrc
oracle@hmc-desktop:~$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 15 21:31:23 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO EDITION
---------- --- ----------------- ------------------ --------- --- -------
1 XE
hmc-desktop
10.2.0.1.0 15-SEP-08 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO XE

Cheers!

One-time scheduling of tasks with 'at'

cheduling services with cron and similar tools is great for repetitive tasks but inconvenient for one-time jobs that only need to be accomplished once. For those tasks, use the 'at' command.

At will execute a command at a particular time and is extremely useful for one-time jobs. For instance, if you are running a script or program and want to receive an e-mail when it's done, or need to schedule a job or task for a particular time or date, at will take care of it for you.

For instance, if you wanted to use at to schedule a job to run at 10:00 p.m., you would use:

$ at 10pm -f script

At uses a very useful time specification that will permit you great flexibility with scheduling. To schedule a job for 2:00 a.m. tomorrow, use:

$ at 2am tomorrow -f script

It can also schedule jobs relational to the current time. For instance, to run a job in exactly three hours, use:

$ at now + 3 hours -f script

When scheduling a command with at, it will print out what the job number is and when it will be executed. To view currently scheduled jobs, use the atq command:

$ atq

154    2008-08-04 20:31 a vdanen

To delete a currently scheduled job, use atrm and provide it the job number:

$ atrm 154

Commands can also be provided to at using standard input, or they can be specified by using the -f option, which takes the script or command to run as the argument.

Another way of scheduling jobs with at can be done using the batch command. This will execute a command when the system load average drops below a certain value, usually 0.8 by default. The batch command takes most of the same arguments as at (less a time specification), and is useful for telling the system to execute a new command after currently running processes--that are using large amounts of processing power--are complete.

Finally, at can be restricted to specific users by using the /etc/at.allow and /etc/at.deny files. If at.allow exists, only users listed in the file may use it. If at.deny exists, but at.allow does not, then all users may use at other than those listed in at.deny. By default, any user may use at.

The at command probably isn't something that will be used on a daily basis, but it is useful when certain tasks need to be executed at certain times, regardless of whether you are in front of the computer or not. If nothing else, it makes a great poor man's TO-DO reminder in that you can use it to send an e-mail or SMS message at specific times, as a reminder for certain events.

硬盘与闪存的IO性能

闪存主要的操作性能特征是随机读和顺序读一样快，但随机写很慢。作者给了一个典型的闪存与硬盘读写性能对比，闪存是随机读为0.4ms，随机写是 48ms，相对来说硬盘随机读是9.6ms，随机写是4.9ms，也就是随机读闪存性能是硬盘的24倍，但随机写硬盘却是闪存性能的10倍。

timing of shrink database vs shrink datafile

I have a sql server 2005 database with single data file and transaction log. The data file size is 40Gb and there is 23Gb free space.
So I want to shrink it by click "shrink database". By the help of belw sql, it only completed 50% even after running 3.5 hours.
select percent_complete, estimated_completion_time, cpu_time, total_elapsed_time, * from sys.dm_exec_requests;

I decide to stop it and change to use "shrink file". Surprising, it completed less than 1 minutes and return 7Gb to disk space.

Funny.

check current redo log file usage

-- Percentage full of the current redo log file.
SELECT ROUND((cpodr_bno/lesiz),2)*100||'%' PctCurLogFull
FROM X$kcccp a, X$kccle b
WHERE a.cpodr_seq = leseq;
PCTCURLOGFULL
---------------
35%

7 Interview Questions

如何诊断PGA设定是否合适

A good article from ITPUB about PGA diagnosis

using sysdba for exp/imp

notice that there is one space after slash inside the single quote.
imp userid='/ as sysdba' full=y log=d:\sysfull.txt file=d:\back\sysfull.dmp