Oracle FAQ 2003.02精华

• 精华
• 长度
• 使用
• select
• from
• where
• 最大

1.Q: 如何使用NID来更改数据库名称？
A:
在Oracle9i二版以前，要想更改数据库名称，我们必须重建控制文件，具体更改过程参见我网站上的文章。
在Oracle9i二版中，我们可以使用一个新的工具"NID"来完成这个任务，值得一体的是这个工具同时还可以修改同一主机上低版本的数据库名称。

下面是将现有数据库更名为"gototop"的详细过程：

1. Shutdown database
2. Startup mount
3. $ nid target=/ dbname=gototop
4. Shutdown database
5. Change init.ora/spfile name, i.e.: initgototop.ora
6. Change db_name parameter in init.ora/spfile, i.e.: db_name='gototop'
7. Change ORACLE_SID to point to new database name (如果5没有做，这一步同样可以不做)
8. Startup mount
9. Alter database open resetlogs
10. Take a backup

和很多情况类似，对数据库的修改都需要在EXCLUSIVELY模式下进行，如果试运行在RAC环境中则会出现下面的错误提示：
NID-00120: Database should be mounted exclusively

解决方法也很简单，可以先注释掉相关RAC的参数。

2.Q: 如何将数字转换成大写？
A:
SQL> select to_char(to_date(1234567,'J'),'JSP') from dual;

TO_CHAR(TO_DATE(1234567,'J'),'JSP')
----------------------------------------------------------------------
ONE MILLION TWO HUNDRED THIRTY-FOUR THOUSAND FIVE HUNDRED SIXTY-SEVEN

这是英文的大写，可以实现从1到5,373,484之间数字的转换；中文大写我们可以通过下面的两个函数来实现：

CREATE OR REPLACE FUNCTION ZH(N NUMBER) RETURN CHAR AS
T VARCHAR(20):='零壹贰叁肆伍陆柒捌玖';
BEGIN
RETURN SUBSTR(T,N+1,1);
END;
/
CREATE OR REPLACE FUNCTION DX(N NUMBER) RETURN VARCHAR AS
B VARCHAR(16):='拾万千百拾元角分';
C VARCHAR(8);
G VARCHAR(32);
BEGIN
C:=REPLACE(LPAD(LTRIM(TO_CHAR(N,'999999.99')),9,'0'),'.','');
FOR I IN 1..8 LOOP
G:=G||ZH(SUBSTR(C,I,1))||SUBSTR(B,I,1);
END LOOP;
RETURN G;
END;
/

3. Q:如何确定当前的Oracle版本？
A:
当一个系统中安装有多个版本的ORACLE时，可以按照下面的方法来确定当前的Oracle版本：
1、比较“/etc/oratab”与$ORACLE_HOME环境变量；
2、进入ORACLE数据库中，执行SELECT * FROM V$INSTANCE;

4. Q:如何知道hidden参数的含义？
A:
在init.ora中有时会用到类似下面以"_"开头的hidden参数：
_corrupted_rollback_segments=(r01,r02,r03,r04)
_allow_resetlogs_corruption=ture
这些参数一般都是由oracle support建议才会使用的，没有公布这方面的文档，我们可以通过下面的语句，看到一些解释：
select * from X$KSPPI where KSPPINM='_corrupted_rollback_segments';
select * from X$KSPPI where KSPPINM='_allow_resetlogs_corruption';

4. Q:如何使用rownum进行分页？
A:
下面是我们单位开发人员提出的分页方法：
“更为简单、清晰的ORACLE分页SQL：
select * from t_xxx where rownum < 201 minus select * from t_xxx where rownum < 101”
下面是我的回复：

这样的SQL是令人担忧的！

原因基于两点：
1、我们知道rownum是oracle输出结果中的虚行，即在实际的表中并没有相应的存储结构存在；

2、minus运算是在两次操作结果的基础上进行的，因此在非不得不的情况下，我们一贯主张避免使用类似的操作；

由此可以看出楼主提出的SQL：
select * from t_xxx where rownum < 201 minus select * from t_xxx where rownum < 101;
为了取出100条纪录实际上执行了两次SELECT共300条纪录，举个简单的例子，如果共1000条纪录，分页为100条，那么全部SELECT总数据量是
100+（200+100）+（300+200）+。。。
共是10000条，整整是10倍的数据量！如果是几万、几十万的数量级，再加上实际操作中的ORDER BY以及相关的WHERE条件，更不敢想象这样SQL的效率。

在实际中针对分页比较典型的做法是：
select * from
( select rownum rnm, a.* from
( select * from t where name=&myname order by name,dept) a
where rownum <= &maxrnm )
where rnm >= &minrnm ;

这里有三个变量：
&myname where条件
&maxrnm要查询的最大行号
&minrnm要查询的最小行号

使用这样的SQL在8i以后ORACLE是使用专门的优化来处理的，它需要排序字段预先建有索引，由于索引是已排序好的结构，因此取TOP n的问题，就变为从索引中直接从头提取n个索引关键字，然后再根据索引就可快速的找到记录并返回给用户。从而有效避免了检索全部记录的情况。 中国_网管联盟bitsCN.com

按分页计算，如果用户很少浏览后面页面的数据使用上面的SQL效率是很好，否则到某一刻index range scan所有满足条件记录时带来的高成本，CBO甚至认为它高于FULL TABLE SCAN而选择FULL TABLE SCAN。为了避免这一情况，针对经常需要浏览后面分页数据的用户，可以使用下面的SQL来处理：

select * from t where rowid in
( select rid from
( select rownum rno,rowid rid from
( select rowid from t where name = &myname order by name,dept )
where rownum <= &maxrnm )
where rno >= &minrnm )
;

在这一方法中，我们考虑到索引与表相比，数据量大大小于后者，因此我们试图先在索引中搜索出某页记录的物理位置，然后根据这些物理位置（rowid）在表中直接取出相应的记录，这将有利消除上述FULL TABLE SCAN的出现。

当然二者之间的选择要看我们的实际应用以及我们INDEX的情况，以便更好地提高效率。

另外一种做法在ORACLE8.1.6之后可以使用row_number() over()函数来实现：

select * from
(SELECT owner,table_name,row_number() OVER(ORDER BY table_name) rownumber FROM dba_tables) 

where rownumber >= 100 and rownumber <= 200;

5.Q: EXP with compress=y是不是总有好处？
A:
在使用EXP时，很多人不分情形，总喜欢使用COMPRESS=Y参数，其实这一种错误的做法。

导出选项COMPRESS=Y 将生成创建初始EXTENT的代码，该初始EXTENT大小等于当前分
配给对象的所有EXTENT的总大小，如果对象上有很多已删除的行或者如果上一个EXTENT有很多未使用的块，这将不必要地分配给对象很多空间。

另外在使用EXP还有以下几点需要注意：
1、可以将常用命令行选项写在参数文件中；
2、仅在小数据量的情况下使用CONSISTENT=Y参数；
3、分配尽量大的BUFFER并使用DIRECT=Y参数。

6.Q: ORACLE常用的数据类型有哪些？
A:
之所以把这个问题也放在这里，是想为使用时提供一个参考：

字段类型 中文说明 限制条件 其它说明
CHAR 固定长度字符串 最大长度2000 bytes
VARCHAR2 可变长度的字符串 最大长度4000 bytes 可做索引的最大长度749
NCHAR 根据字符集而定的固定长度字符串 最大长度2000 bytes
NVARCHAR2 根据字符集而定的可变长度字符串 最大长度4000 bytes bitsCN.nET*中国网管博客
DATE 日期（日-月-年） DD-MM-YY（HH-MI-SS） 经过严格测试，无千虫问题
LONG 超长字符串 最大长度2G（231-1） 足够存储大部头著作
RAW 固定长度的二进制数据 最大长度2000 bytes 可存放多媒体图象声音等
LONG RAW 可变长度的二进制数据 最大长度2G 同上
BLOB 二进制数据 最大长度4G
CLOB 字符数据 最大长度4G
NCLOB 根据字符集而定的字符数据 最大长度4G
BFILE 存放在数据库外的二进制数据 最大长度4G
ROWID 数据表中记录的唯一行号 10 bytes ********.****.****格式，*为0或1
NROWID 二进制数据表中记录的唯一行号 最大长度4000 bytes
NUMBER(P,S) 数字类型 P为整数位，S为小数位
DECIMAL(P,S) 数字类型 P为整数位，S为小数位
INTEGER 整数类型 小的整数
FLOAT 浮点数类型 NUMBER(38)，双精度
REAL 实数类型 NUMBER(63)，精度更高