<![CDATA[ilonng]]> http://chenzs19850728.blog.163.com zh-CN Fri, 8 Aug 2008 18:31:42 +0800 Fri, 8 Aug 2008 18:31:42 +0800 http://blogs.law.harvard.edu/tech/rss 120 <![CDATA[ilonng]]> http://ava.blog.163.com/photo/R3iimTUoqOJtk0KBjk6tSg==/174795960537518343.jpg http://chenzs19850728.blog.163.com <![CDATA[mysql连接通道中的字符集和校验规则]]> http://chenzs19850728.blog.163.com/blog/static/7629609200868104214954

    这里首先需要解释的是，我想应该就是连接通道的含义了。那什么是连接通道呢？
    所谓连接通道，就是客户端和服务器端保持连接的一个通道，它是逻辑上的一个概念。客户端通过连接通道发送sql语句到服务器端，服务端执行，将结果再通过连接通道返回至客户端。the connection is the pass when you connect to the server.

    这个过程中，有几个临界点（逻辑上概念），是我们需要注意的，mysql也就在这几个临界点上做了文章。
    1、当语句离开客户端的时候：
    从客户端出来的，包括sql语句本身（这里里面就包含字符串和关键字等了），以及character_set_client系统变量。为什么要包含这个变量呢？这个变量的作用说明2点，也是它的作用：一是表示该语句中的字符集是使用character_set_client指定的字符集编码的，二是通过此系统变量来告诉服务器所发送来的语句中的字符集编码。
    2、当服务器端接受到客户端的语句的时候：
    mysql会使用character_set_connection/collation_connection指定的字符集以及校验规则，将客户端的字符串，做一个从character_set_client到character_set_connection的转换。
    3、当服务器处理好结果以后，在把结果传给客户端前：
    mysql会先将结果转换成character_set_results指定的字符集，然后传回给客户端。

    当字符串在mysql服务器的时候，最终以什么格式存储到mysql数据库中，这个是受到具体的数据表级别、列级别字符集设置的控制了。

    从上面的介绍中，我们就知道和连接通道相关几个参数了，他们分别是character_set_client/connection/results，可以如下查看：
mysql> show variables like 'char%';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_client     | latin1            |
| character_set_connection | latin1            |
| character_set_database   | gbk               |
| character_set_results    | latin1            |
| .........................| ......            |
+--------------------------+-------------------+
8 rows in set (0.00 sec)

mysql> show variables like 'colla%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | gbk_bin           |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

    下面我们来做个实验，来证明一下这个结果：
    首先保证character_set_connection与character_set_results以及底层存储字符集的一致性，看看character_set_client的效果。
mysql> show variables like 'char%';
+--------------------------+------------------+
| Variable_name            | Value            |
+--------------------------+------------------+
| character_set_client     | latin1           |
| character_set_connection | gbk              |
| character_set_database   | gbk              |
| character_set_results    | gbk              |
| .........................| ......           |
+--------------------------+------------------+
8 rows in set (0.00 sec)

mysql> create table t (a varchar(10));  --  这里没有指定字符集，就默认使用了database的字符集gbk了
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t values('中国');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+-------+
| a     |
+-------+
| ???ú |
+-------+
1 row in set (0.00 sec)

    由此可以看到由于latin1与gbk对汉字的编码方式不一样（或者说latin1根本就不能正确编码汉字），在这个collection过程中，从character_set_client到character_set_connection转换时，就把你输入的好好的汉字转换成乱码了。那么，如果让过程不发生转换呢？

mysql> set character_set_client=gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values('人民');
Query OK, 1 row affected (0.02 sec)

mysql> select * from t;
+-------+
| a     |
+-------+
| ???ú |
| 人民  |
+-------+
2 rows in set (0.00 sec)

    可见，这里是能正确存储和显示的，很简单，因为任何转换都没有发生，当然就不会出现乱码了。

    接着做实验，我们让character_set_connection发生变化：
mysql> set character_set_connection=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values ('共和国');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+-------+
| a     |
+-------+
| ???ú |
| 人民  |
| ???   |
+-------+
3 rows in set (0.00 sec)

    可见，这里character_set_client=gbk,character_set_connection=latin1,character_set_database=gbk。首先，client到connection过程中，汉字被转换成乱码（这个过程可能就会丢失信息）；然后存储数据的时候，又从connection到存储的字符集（gbk）发生一次转换，乱码被转换成“更”乱码。这里如果connection与client的字符集有种包容性关系的话，如character_set_client=gbk, character_set_connection=utf8,character_set_results=gbk,底层存储也是gbk编码，由于utf8“兼容”所有的字符集，故在转换过程中不会发生信息丢失，查询的时候也不会是乱码，如下：

mysql> show variables like 'char%';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_client     | gbk             |
| character_set_connection | utf8            |
| character_set_database   | gbk             |
| character_set_results    | gbk             |
| .........................| ......          |
+--------------------------+-----------------+
8 rows in set (0.00 sec)

mysql> create table t (a varchar(10)) charset=gbk;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t values('中国');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+------+
| a    |
+------+
| 中国 |
+------+
1 rows in set (0.00 sec)

    我来解释一下这个过程，假设“中国”的gbk编码是1234，而utf8的编码是4321，utf8的编码为1234的假设是“淘宝”。client的“中国”的编码1234进入connection，仍然是1234（但实际上它的含义已经发生变化为“淘宝”），存储的时候connection的1234编码到表存储也是1234，刚好正确表达了“中国”的意思，查询返回时，由于results也是gbk，所以不发生转换，正确显示。很显然，如果results又是一种与gbk不兼容的字符集如latin1，查询又会出问题，如下：

mysql> set character_set_results=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| a    |
+------+
| ??   |
+------+
1 rows in set (0.00 sec)

    当我们改变底层存储的字符集的时候，会怎样？请看如下实验：
mysql> show variables like 'char%';
+--------------------------+---------------+
| Variable_name            | Value         |
+--------------------------+---------------+
| character_set_client     | gbk           |
| character_set_connection | gbk           |
| character_set_database   | gbk           |
| character_set_results    | gbk           |
| .........................| ......        |
+--------------------------+---------------+
8 rows in set (0.01 sec)

mysql> create table t (a varchar(10)) charset=latin1;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t values('淘宝');
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> select * from t;
+------+
| a    |
+------+
| ??   |
+------+
1 row in set (0.00 sec)
   
   很显然，由于latin1字符集无法存储汉字，故出现乱码。

   下面，我们只改变results字符集，看看效果如何：
mysql> show variables like 'char%';
+--------------------------+----------------+
| Variable_name            | Value          |
+--------------------------+----------------+
| character_set_client     | gbk            |
| character_set_connection | gbk            |
| character_set_database   | gbk            |
| character_set_results    | latin1         |
| .........................| ......         |
+--------------------------+----------------+
8 rows in set (0.00 sec)

mysql> create table t (a varchar(10)) charset=gbk;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t values('淘宝');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+------+
| a    |
+------+
| ??   |
+------+
1 row in set (0.00 sec)

    很显然，底层为gbk编码的字符串转换成latin1的字符集的字符，变成乱码，传给客户端，再次转换成gbk字符集，变成“更”乱码，故显示乱码。

    下面，我再介绍几个“简约”命令。
    SET NAMES 'x'，(SET NAMES 'charset_name' COLLATE 'collation_name')相当于：
SET character_set_client = x;
SET character_set_results = x;
SET character_set_connection = x;

mysql> show variables like 'char%';
+--------------------------+----------------+
| Variable_name            | Value          |
+--------------------------+----------------+
| character_set_client     | gbk            |
| character_set_connection | gbk            |
| character_set_results    | latin1         |
| .........................| ......         |
+--------------------------+----------------+
8 rows in set (0.00 sec)

mysql> set names 'ascii';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'char%';
+--------------------------+----------------+
| Variable_name            | Value          |
+--------------------------+----------------+
| character_set_client     | ascii          |
| character_set_connection | ascii          |
| character_set_results    | ascii          |
| .........................| ......         |
+--------------------------+----------------+

    SET CHARACTER SET 'x',相当于：
SET character_set_client = x;
SET character_set_results = x;
SET collation_connection = @@collation_database;
    这里collation_connection = @@collation_database的意思就是把collation_connection的设置的collation_database的值，由于collation肯定能确定character set，故其又相当于多做了个设置：把character_set_connetion设置成character_set_database的值。请看如下实验：

mysql> show variables like 'char%';
+--------------------------+-------------+
| Variable_name            | Value       |
+--------------------------+-------------+
| character_set_client     | latin1      |
| character_set_connection | latin1      |
| character_set_database   | gbk         |
| character_set_results    | latin1      |
| .......................  | ......      |
+--------------------------+-------------+
8 rows in set (0.00 sec)

mysql> show variables like 'collat%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | gbk_bin           |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql> set character set 'ascii';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'char%';
+--------------------------+-------------+
| Variable_name            | Value       |
+--------------------------+-------------+
| character_set_client     | ascii       |
| character_set_connection | gbk         |
| character_set_database   | gbk         |
| character_set_results    | ascii       |
| .......................  | ......      |
+--------------------------+-------------+
8 rows in set (0.00 sec)

mysql> show variables like 'collat%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | gbk_bin           |
| collation_database   | gbk_bin           |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

    通过以上的介绍，所以为了防止出现乱码，我们可以把character_set_client,character_set_connection,character_set_database,character_set_results设置成同样的值，把collation_connection和collation_database也设置成同样的值，这样就“一劳永逸”了。程序连接mysql的时候，一般都会显示设置character_set_client的值，如java连接中，一般都有如下的一段代码来显示设置这个值：
jdbc:mysql://10.1.6.174:3306/notify?connectTimeout=1000&characterEncoding=utf8

]]> http://chenzs19850728.blog.163.com/blog/static/7629609200868104214954 0 http://chenzs19850728.blog.163.com/blog/static/7629609200868104214954 Tue, 8 Jul 2008 22:42:14 +0800 2008-07-08T22:42:14+08:00 <![CDATA[mysql字符集与校验规则的设置]]> http://chenzs19850728.blog.163.com/blog/static/7629609200868104124489

    默认情况下，我们可以在4个级别分别设定mysql的字符集和校验规则，分别是server、database、table和column。实际上，我们甚至还可以单独对一串字符串设定其字符集和校验规则。下面就分别来对四个级别的设定说明一下：
    server:
    在启动mysql服务的时候，可以指定mysql server的字符集和校验规则，通过如下方式：
mysqld --character-set-server=latin1 --collation-server=latin1_swedish_ci
    如果不亲自指定它们，那么mysql就会使用默认值。一般是latin1和latin1_swedish_ci。当然这个默认值是可以修改的，但是方法只有一种，那就是重编译源代码。采用如下方式：
./configure --with-charset=gbk  --with-collation=gbk_bin
    如何查询当前的server的字符集和校验规则的值，可以查看系统参数character_set_server和collation_server：
mysql> show variables like 'character_set_server%';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| character_set_server | latin1 |
+----------------------+--------+
1 row in set (0.00 sec)

mysql> show variables like 'collation_server%';
+------------------+-------------------+
| Variable_name    | Value             |
+------------------+-------------------+
| collation_server | latin1_swedish_ci |
+------------------+-------------------+
1 row in set (0.00 sec)
   
    server的字符集和校验规则的唯一作用就是，当database的字符集和校验规则没有指定的时候，就默认使用server的对应值。

    database:
    我们在创建和修改数据库的时候，可以指定其字符集和校验规则：
CREATE DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name];
ALTER DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name];
    对应查看当前数据库的字符集和校验规则的系统参数是'character_set_database'和'collation_database'，如下：
mysql> show variables like 'character_set_database';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | gbk   |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'collation_database';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| collation_database | gbk_bin |
+--------------------+---------+
1 row in set (0.00 sec)

    数据库的字符集和校验规则的作用有二，其一是当没有在表级别知道它们，那么默认就使用数据库基本的值；其二是在“load data infile”时也起到了作用。

    table:
    在创建表或者修改表结构的时候，我们可以通过如下方式指定这个表的字符集和校验规则：
CREATE TABLE tbl_name (column_list)
    [[DEFAULT] CHARACTER SET charset_name] [COLLATE collation_name]]

ALTER TABLE tbl_name
    [[DEFAULT] CHARACTER SET charset_name] [COLLATE collation_name]

    column:
    具体的表的具体的某个字段，我们也可以指定其字符集和校验规则，如下：
col_name {CHAR | VARCHAR | TEXT} (col_length)
    [CHARACTER SET charset_name] [COLLATE collation_name]

    那么，这四个级别的字符集和校验规则的指定（级别高低server>database>table>column）,mysql最终以何种形式来存储和显示字符串的呢？
1、本级别中，如果同时指定character set和collate，那么就使用指定的值；
2、本级别中，只指定character set而没有指定collate，那么就使用指定的character set值和其对应的默认的collation值；
3、本级别中，只指定collate而没有指定character set，那么就使用指定的collation值和其所对应的character set值；
4、本级别中，都没有指定这2个值，那么默认就使用上一级别的对应的值。

    国家字符集：
    mysql中国家字符集的概念，其表示使用预先定义的字符集，在mysql5.1中使用utf8作为其预先的定义的字符集。
    因此，下面几种类型的定义，其实是等效的：
CHAR(10) CHARACTER SET utf8
NATIONAL CHARACTER(10)
NCHAR(10)
    下面几种类型也是等效的：
VARCHAR(10) CHARACTER SET utf8
NATIONAL VARCHAR(10)
NCHAR VARCHAR(10)
NATIONAL CHARACTER VARYING(10)
NATIONAL CHAR VARYING(10)

   字符串的字符集和校验规则的设置问题，请稍等！

]]> http://chenzs19850728.blog.163.com/blog/static/7629609200868104124489 0 http://chenzs19850728.blog.163.com/blog/static/7629609200868104124489 Tue, 8 Jul 2008 22:41:24 +0800 2008-07-08T22:41:24+08:00 <![CDATA[mysql字符集和校验规则概念小介]]> http://chenzs19850728.blog.163.com/blog/static/762960920086810392831     刚刚接触mysql的人（like me），可能对这2个概念不是太理解，这里小小解释一下，希望能说明清楚这个问题。
    字符集，character set，就是一套表示字符的符号和这些的符号的底层编码；而校验规则，则是在字符集内用于比较字符的一套规则。字符集还是比较容易理解的，主要是校验规则，下面我简单举个例子来说明一下：
如在某个字符集“X”的A与a，他们的底层编码分别是A=0,a=100。这里符号“A”“a”和底层编码“0”“100”就是字符集的概念范围。假设我们要比较A与a的大小，我们得到a>A，因为我们是根据其底层编码进行比较的，这就是这个字符集“X”的一种校验规则“Z”（根据底层编码来比较）。假设，现在有另外一种校验规则，是先取其相反数，然后再比较大小，那么就很显然的得到a<A，这就是字符集“X”的另外一种校验规则“Z1”。由此可见，一种字符集可能存在多个与之对应的校验规则（一对多关系）。
    关于字符集与校验规则，mysql能：
1、使用字符集来存储字符串，支持多种字符集；
2、使用校验规则来比较字符串，同种字符集还能使用多种校验规则来比较；
3、在同一台服务器、同一个数据库或者甚至在同一个表中使用不同字符集或校对规则来混合组合字符串；
4、可以在任何级别（服务器、数据库、表、字段、字符串），定义不同的字符集和校验规则。
    查询你的mysql数据库所支持的字符集种类，可以如下：
mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| .........| ......................      | ................    |      . |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
+----------+-----------------------------+---------------------+--------+
36 rows in set (0.00 sec)
   这里的maxlen表示要用最大多少个字节来存储字符集的单个词，default collation表示该字符集的默认校验规则。
   你也可以利用like来进行筛选，如下：
mysql> show character set like 'latin%';
+---------+-----------------------------+-------------------+--------+
| Charset | Description                 | Default collation | Maxlen |
+---------+-----------------------------+-------------------+--------+
| latin1  | cp1252 West European        | latin1_swedish_ci |      1 |
| latin2  | ISO 8859-2 Central European | latin2_general_ci |      1 |
| latin5  | ISO 8859-9 Turkish          | latin5_turkish_ci |      1 |
| latin7  | ISO 8859-13 Baltic          | latin7_general_ci |      1 |
+---------+-----------------------------+-------------------+--------+
4 rows in set (0.00 sec)
    查询你的mysql数据库所支持字符集的校验规则，可以如下：
mysql> show collation;
+----------------------+----------+-----+---------+----------+---------+
| Collation            | Charset  | Id  | Default | Compiled | Sortlen |
+----------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci      | big5     |   1 | Yes     | Yes      |       1 |
| big5_bin             | big5     |  84 |         | Yes      |       1 |
| dec8_swedish_ci      | dec8     |   3 | Yes     |          |       0 |
| ........             | ....     |  .. | ...     | ...      |       . |
| eucjpms_bin          | eucjpms  |  98 |         | Yes      |       1 |
+----------------------+----------+-----+---------+----------+---------+
127 rows in set (0.00 sec)
   这里compiled表示该collation所对应的character set是否被编译到此mysql数据库，通过此点就可以知道该mysql数据库是否支持某个字符集。sortlen表示要在内存中排序时，该字符集的字符要占用多少个字节。
   你也可以利用like来进行筛选，如下：
mysql> show collation like 'latin1%';
+-------------------+---------+----+---------+----------+---------+
| Collation         | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| latin1_german1_ci | latin1  |  5 |         | Yes      |       1 |
| latin1_swedish_ci | latin1  |  8 | Yes     | Yes      |       1 |
| latin1_danish_ci  | latin1  | 15 |         | Yes      |       1 |
| latin1_german2_ci | latin1  | 31 |         | Yes      |       2 |
| latin1_bin        | latin1  | 47 |         | Yes      |       1 |
| latin1_general_ci | latin1  | 48 |         | Yes      |       1 |
| latin1_general_cs | latin1  | 49 |         | Yes      |       1 |
| latin1_spanish_ci | latin1  | 94 |         | Yes      |       1 |
+-------------------+---------+----+---------+----------+---------+
8 rows in set (0.00 sec)
    大家可能已经发现collation的名字似乎有规律可循，其实的确也是这样的，并且它也有些特征，如下：
1、两个不同的字符集不能有相同的校验规则（字符集：校验规则 = 1：n）；
2、每个字符集都有一个校验规则，就是对应的DEFAULT=YES的那个collation；
3、collation命名规则：字符集名_对应的语言名_ci/cs/bin，其中ci表示大小写不敏感性，cs表示大小写敏感性，bin表示二进制。]]> http://chenzs19850728.blog.163.com/blog/static/762960920086810392831 0 http://chenzs19850728.blog.163.com/blog/static/762960920086810392831 Tue, 8 Jul 2008 22:39:02 +0800 2008-07-08T22:39:51+08:00 <![CDATA[快速复制一张大表讨论]]> http://chenzs19850728.blog.163.com/blog/static/7629609200841461443371

有这样的一类需求，快速复制一张表，而这张表的数据量又非常的大。比方说本来是分区表，要把它重新变成非分区表，怎么能够快速的完成这样的操作呢？我下面给出2种大致的方法：

第一，就是利用CTAS方式来创建一张新表，当然要想加快速度，在数据库不是force logging的前提下，可以使用nologging方式来创建表
SQL> create table auction_auctions_bak
  2  tablespace tbs_taobao
  3  nologging
  4  as
  5  select * from auction_auctions;

Table created.

SQL> select count(*) from auction_auctions;

  COUNT(*)
----------
   4179779

SQL> select count(*) from auction_auctions_bak;

  COUNT(*)
----------
   4179779

在建立完成表以后，要注意将表的属性重新变成logging：
SQL> select logging from tabs where table_name='AUCTION_AUCTIONS_BAK';

LOG
---
NO

SQL> alter table auction_auctions_bak logging;

Table altered.

当然也可以这样：CREATE TABLE ... AS SELECT .. WHERE 1=2;然后使用 INSERT /*+ APPEND */ INTO .. SELECT ...。

最后将表名更改过来，建立一下新的索引，然后就可以了。

SQL> drop table auction_auctions;

Table dropped.

SQL> rename auction_auctions_bak to auction_auctions;

Table renamed.

第二，在原理上，它其实和第一种方式差不多，就是阻止数据库记录日志来加快速度，大概过程如下：
1、exp出原表（建议compress=n）
2、drop 原表
3、以nologging的方式，新建与原表同名的空表
4、imp原表至空表中，注意要把ignore设置为y
5、更改新表的属性为logging

我这次分别都对两种方式做了测试，结果第二种方式远远快于第一种方式，大概是其10倍的速度。注意以上两种方式都是在数据库没有force logging的前提下完成的。如果数据库被force logging了，那么怎样才能加快复制速度，这个大家可以说说。

]]> http://chenzs19850728.blog.163.com/blog/static/7629609200841461443371 3 http://chenzs19850728.blog.163.com/blog/static/7629609200841461443371 Wed, 14 May 2008 18:14:43 +0800 2008-05-14T18:14:43+08:00 <![CDATA[怪异ORA-01502，创建唯一约束却无唯一索引]]> http://chenzs19850728.blog.163.com/blog/static/762960920084135524753

我们都知道，唯一约束是基于唯一索引的，如果没有唯一索引，那么在建立唯一约束的时候会自动创建相应的唯一索引。而今天我在创建唯一约束以后，发生了一件怪异的事情。

下面是过程描述。
因业务要求，要求在一表（test）上一列增加一个唯一约束，由于是在开发环境，数据量也很小，我采用了如下的方法来创建：
SQL> alter table test
  2    add constraint UK_ET_AIRSUP_USERS_supp_WW unique (air_supply_wangwang)
  3    using index
  4    tablespace TBS_ET_IND
  5    online
  6    compute statistics;

Table altered.

理论上按照这种方式，已经完全完成工作了。可是，后来测试人员测试的是，却告诉我发生了如下错误：
ORA-01502: index '.' or partition of such index is in unusable state

查询错误描述，可以很清楚的发现问题的原因：
[oracle@crmtest worksh]$ oerr ora 01502
01502, 00000, "index '%s.%s' or partition of such index is in unusable state"
// MERGE: 1489 RENUMBERED TO 1502
// *Cause: An attempt has been made to access an index or index partition
//         that has been marked unusable by a direct load or by a DDL
//         operation
// *Action: DROP the specified index, or REBUILD the specified index, or
//         REBUILD the unusable index partition

昨天都是没有问题，因此我就怀疑我今天这个唯一约束出了问题，查询了一下表的index：
SQL> select index_name,index_type,status from user_indexes where table_name = upper('test');

INDEX_NAME                     INDEX_TYPE                  STATUS
------------------------------ --------------------------- --------
PK_ET_AIRSUPPLY_USERS          NORMAL                      VALID
UK_ET_AIRSUPPLY_USERS_NICK     NORMAL                      VALID

怪异的时候发生了，怎么没有相应的唯一索引呢？确定看看唯一约束有没有什么问题：
SQL> alter table test
  2  modify constraint UK_ET_AIRSUP_USERS_SUPP_WW enable validate;

Table altered.

再看看结果如何：
SQL> select index_name,index_type,status from user_indexes where table_name = upper('test');

INDEX_NAME                     INDEX_TYPE                  STATUS
------------------------------ --------------------------- --------
PK_ET_AIRSUPPLY_USERS          NORMAL                      VALID
UK_ET_AIRSUPPLY_USERS_NICK     NORMAL                      VALID

SQL> delete from test t where t.id in ('1','2','3')
  2  ;
delete from test t where t.id in ('1','2','3')
*
ERROR at line 1:
ORA-01502: index '.' or partition of such index is in unusable state

还是不对。于是决定重建这个唯一约束：
SQL> select index_name,index_type,status from user_indexes where table_name = upper('test');

INDEX_NAME                     INDEX_TYPE                  STATUS
------------------------------ --------------------------- --------
PK_ET_AIRSUPPLY_USERS          NORMAL                      VALID
UK_ET_AIRSUPPLY_USERS_NICK     NORMAL                      VALID

SQL> alter table test
  2  drop constraint UK_ET_AIRSUP_USERS_SUPP_WW;

Table altered.

SQL> select index_name,index_type,status from user_indexes where table_name = upper('test');

INDEX_NAME                     INDEX_TYPE                  STATUS
------------------------------ --------------------------- --------
PK_ET_AIRSUPPLY_USERS          NORMAL                      VALID
UK_ET_AIRSUPPLY_USERS_NICK     NORMAL                      VALID

SQL> delete from test t where t.id in ('1','2','3');

0 rows deleted.

SQL> alter table test
  2    add constraint UK_ET_AIRSUP_USERS_supp_WW unique (air_supply_wangwang)
  3    using index
  4    tablespace TBS_ET_IND
  5    online
  6    compute statistics;

Table altered.

SQL> delete from test t where t.id in ('1','2','3');

0 rows deleted.

SQL> rollback;

Rollback complete.

SQL> select index_name,index_type,status from user_indexes where table_name = upper('test');

INDEX_NAME                     INDEX_TYPE                  STATUS
------------------------------ --------------------------- --------
PK_ET_AIRSUPPLY_USERS          NORMAL                      VALID
UK_ET_AIRSUPPLY_USERS_NICK     NORMAL                      VALID
UK_ET_AIRSUP_USERS_SUPP_WW     NORMAL                      VALID


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for Linux: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
这个时候问题已经解决。但是这个事件有点诡异，这个错误也无法重现，只是想提醒一下，可能会出现这种现象，虽然它几率很小。

为了避免此类错误的出现，最好的解决方法，至少在生产库上做操作，可以先自己手工创建唯一索引，然后在创建约束，这样就万无一失了。

]]> http://chenzs19850728.blog.163.com/blog/static/762960920084135524753 0 http://chenzs19850728.blog.163.com/blog/static/762960920084135524753 Tue, 13 May 2008 17:05:24 +0800 2008-05-13T17:06:12+08:00 <![CDATA[使用nid更改数据库名]]> http://chenzs19850728.blog.163.com/blog/static/7629609200848101225953

如果想更改数据库名，即修改db_name参数。在Oracle 9iR2提供了一个工具nid，可以通过它来完成这项工作，这样就避免了重建控制文件等繁琐方式来实现了。

nid工具可以只用来更改数据库名（db_name），或者只更改数据库的id（dbid），或者两个同时更改。

当涉及到更改db_name的时候，由于数据库名还存在与参数文件中，因此，更改数据库名时也要更改相应的参数。如果你使用了spfile，那么要重建它。另外，还需要重建密码文件。

有个nid工具的详细使用情况，在metalink 文章 Note:224266.1有详细的记载。

首先，我们来看看nid的使用帮助：

[oracle@testdbc oracle]$ nid

DBNEWID: Release 9.2.0.6.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

Keyword     Description                    (Default)

----------------------------------------------------

TARGET      Username/Password              (NONE)

DBNAME      New database name              (NONE)

LOGFILE     Output Log                     (NONE)

REVERT      Revert failed change           NO

SETNAME     Set a new database name only   NO

APPEND      Append to output log           NO

HELP        Displays these messages        NO

这里来解释一下几个问题：

(I):如果你只想更改数据库的id（dbid），那么你只需要指定target参数就可以了，具体的操作过程，请参考如下：

1. Backup the database

2. SHUTDOWN IMMEDIATE of the database

3. STARTUP MOUNT

4. Open one session and run NID with sysdba privileges

   % nid TARGET=SYS/password@test_db

5. Shutdown IMMEDIATE of the database

6. Set the DB_NAME initialization parameter in the initialization parameter

   file to the new database name

7. Create a new password file

8. Startup of the database with open resetlogs

(II):如果你想只更改数据库名（db_name），那么你需要设置SETNAME参数为Y，同时指定DBNAME参数，具体的操作过程，请参考如下：

1. Backup the database

2. SHUTDOWN IMMEDIATE of the database

3. STARTUP MOUNT

4. Open one session and run NID with sysdba privileges

   % nid TARGET=SYS/password@test_db DBNAME=test_db2 SETNAME=Y

   - the value of DBNAME is the new dbname of the database

   - SETNAME must be set to Y. The default is N and causes the

     DBID to be changed also.

5. shutdown IMMEDIATE of the database

6. Set the DB_NAME initialization parameter in the initialization parameter

   file to the new database name

7. Create a new password file

8. Startup of the database(without resetlogs)

(III):如果你想同时更改数据库名（db_name）和数据库ID（dbid），那么你要指定DBNAME参数，同时设置SETNAME为N（其值默认也为N），具体操作过程，请参考如下：

1. Backup of the database.

2. Shutdown IMMEDIATE of the database

3. STARTUP MOUNT

4. Open one session and run NID with sysdba privileges

   % nid TARGET=SYS/password@test_db DBNAME=test_db2

   - the value of DBNAME is the new dbname of the database

5. After DBNEWID successfully changes the DBID,Shutdown IMMEDIATE of the database

6. Set the DB_NAME initialization parameter in the initialization parameter file to the new database name.

7. Create a new password file.

8. Startup of the database with open resetlogs

(IV):如果你只更改了数据库名，而没有更改数据库id，那么你打开数据库就不需要open resetlogs了。

 

     下面是我自己做的一次操作，DBNAME和DBID同时更改。

1．数据库启动到mount状态

SQL> startup mount

ORACLE instance started.

total System Global Area  923904016 bytes

Fixed Size                   452624 bytes

Variable Size             385875968 bytes

Database Buffers          536870912 bytes

Redo Buffers                 704512 bytes

Database mounted.

2. 使用NID更改

[oracle@testdb2 dbs]$ nid target=sys/syspassword dbname=testdb2

DBNEWID: Release 9.2.0.6.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

Connected to database TESTDB1 (DBID=287286795)  -- 注意这里的数据库名和数据库id

Control Files in database:

    /cx300_testdb2/data1/control01.ctl

    /cx300_testdb2/data2/control02.ctl

    /cx300_testdb2/data3/control03.ctl

Change database ID and database name TESTDB1 to TESTDB2? (Y/[N]) => Y – 这里有个手工确认过程

Proceeding with operation

Changing database ID from 287286795 to 975736982

Changing database name from TESTDB1 to TESTDB2

    Control File /cx300_testdb2/data1/control01.ctl – modified  -- 更改控制文件中相关控制信息，这里估计是致控制文件于某种状态，以便后面信息的更改。

    Control File /cx300_testdb2/data2/control02.ctl - modified

    Control File /cx300_testdb2/data3/control03.ctl - modified

    Datafile /cx300_testdb2/data2/system01.dbf - dbid changed, wrote new name  -- 这里开始更改数据文件中的信息

    Datafile /cx300_testdb2/data2/tbs_taobao_3.dbf - dbid changed, wrote new name

…………………………………………

    Datafile /cx300_testdb2/data3/tbs_temp2.dbf - dbid changed, wrote new name

    Control File /cx300_testdb2/data1/control01.ctl - dbid changed, wrote new name  --- 这里开始更改控制文件中的信息

    Control File /cx300_testdb2/data2/control02.ctl - dbid changed, wrote new name

    Control File /cx300_testdb2/data3/control03.ctl - dbid changed, wrote new name

Database name changed to TESTDB2.  –- 数据库名更改完毕

Modify parameter file and generate a new password file before restarting. –- 这里提示要在再次启动数据库时要更改parameter file和password file

Database ID for database TESTDB2 changed to 975736982. –- 数据库id更改完毕

All previous backups and archived redo logs for this database are unusable.

Shut down database and open with RESETLOGS option.  –- 由于更改了dbid，所以要以resetlogs选项打开数据库

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

3．Shutdown database

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

4．修改初始化参数文件

###########################################

db_domain=""

db_name=testdb2

# db_name=testdb1

###########################################

5．重建口令文件

orapwd file=/opt/oracle/product/9.2/dbs/orapwdev-db2 password=syspassword

6.Startup mount,resetlogs打开

alter database open resetlogs;

7．创建新的spfile

create spfile from pfile='/opt/oracle/product/9.2/dbs/initdev-db2.ora';

8．对数据库做个全备份

 

     我这里其实在开始的时候还少了一步，就是在更改之前，要对全库做备份。这个很重要，备份最重要！

]]> http://chenzs19850728.blog.163.com/blog/static/7629609200848101225953 2 http://chenzs19850728.blog.163.com/blog/static/7629609200848101225953 Thu, 8 May 2008 22:12:25 +0800 2008-05-08T22:12:25+08:00 <![CDATA[sequence]]> http://chenzs19850728.blog.163.com/blog/static/762960920083710532829

概述

Oracle的sequence，就是序列号，它提供一系列的按照事先指定的方式进行增长的数字。oracle sequence的最大值是38个整数。【Sequences are database objects from which multiple users can generate unique integers.】。一般来说，sequence常用于生成数据库的主键。

    Oracle将sequence的定义存储在数据字典之中，因此，所有的sequence都在数据库的SYSTEM表空间里面。

    引用sequence的当前值使用CURRVAL，而生成sequence的下一个值使用NEXTVAL来实现。初始化一个刚刚新建的sequence使用NEXTVAL，它会返回新sequence的第一值。另外还要注意，在一个全新的会话中，使用CURRVAL之前必须至少使用一次NEXTVAL。

    sequence是独立于表的，也就是说一个sequence可以同时被多个表使用来生成主键。

       sequence是独立于事务的，就是说序列的增加不需要等待事务的完成，也就是说序列是异步于事务而增长的。这种现象就说明，如果你根本访问不了别的用户用sequence产生的值，也就是说你只能访问到你当前产生的值，即使其他用户已经增加了sequence的值；还说明如果你事务回滚，sequence不会回滚，它所发生的改变是一维的。请看一个例子：

-- session 1

SQL> select distinct sid from v$mystat;

       SID

----------

       147

SQL> create sequence seqtest;

Sequence created.

SQL> select seqtest.nextval from dual;  -- 第一次初始化sequence

   NEXTVAL

----------

         1

SQL> select seqtest.nextval from dual;

   NEXTVAL

----------

         2

-- session 2

SQL> select distinct sid from v$mystat;

       SID

----------

       143

SQL> select seqtest.currval from dual; -- 在一个新会话中，第一次使用currval之前必须先使用nextval

select seqtest.currval from dual

                            *

ERROR at line 1:

ORA-08002: sequence SEQTEST.CURRVAL is not yet defined in this session

SQL> select seqtest.nextval from dual; -- 是sequence值增加1

   NEXTVAL

----------

         3

-- session 1，虽然这个时候session已经增加了sequence的值，但是session 1只能看到自己增加的sequence的部分。

SQL> select seqtest.currval from dual;

   CURRVAL

----------

         2

       如果在一个语句中，有多个部分使用了NEXTVAL，那么只有第一个NEXTVAL会使sequence改变一次，其他的不会是sequence发生变化。请看：

SQL> select seqtest.nextval,seqtest.nextval,seqtest.nextval from dual;

   NEXTVAL    NEXTVAL    NEXTVAL

---------- ---------- ----------

         5          5          5

       sequence可以在下列场合中使用：

1. VALUES clause of INSERT statements

2. The SELECT list of a SELECT statement

3. The SET clause of an UPDATE statement

       而不能在下列场合使用：

■ A subquery

■ A view query or materialized view query

■ A SELECT statement with the DISTINCT operator

■ A SELECT statement with a GROUP BY or ORDER BY clause

■ A SELECT statement that is combined with another