收集：常见问题解答.txt

oracle问题集

ITPUB论坛 - [TIP] 收集：常见问题解答
ITPUB论坛 (http://www.itpub.net/index.php)
- Oracle认证专家 (http://www.itpub.net/forumdisplay.php?forumid=4)
-- [TIP] 收集：常见问题解答 (http://www.itpub.net/showthread.php?threadid=2407)


由 easyfree 于 01-11-06 18:03 发表: 
收集：常见问题解答
我们在学习和工作中都会遇到很多问题，一些具有普遍性和典型性，同样的问题很多人需要到很多地方去询问和查找，我希望在这里进行这样一个活动，将oracle的许多常见问题收集在一起，便于大家查询，即节省时间，也触类旁通

当然Oracle的技术是浩瀚的，我一个人的力量也是很微弱的，希望能得到大家尤其是高手和经验丰富的fans们的支持

这个帖子也作为不断补充的形式进行，到一定时间，斑竹整理后可以变成一个文件供大家下载，欢迎参与

希望针对问题的说明和解答比较明确，给出适应环境、版本等，斑竹无法对所有帖子进行验证是否正确，如果网友们发现问题，欢迎指出并展开讨论

谢谢！

以下一篇是我很久以前写的一篇文章，在实际中有一定应用，正好借一个网友的要求，作为抛砖引玉


代码:
关于 SELECT N 问题

有感于一些网友多次咨询和讨论选取某些指定行数据的问题, 我写了下面这样的简单说明, 请大家指正.

这里描述的 SELECT N 包括这样几种情况:
1. 选取TOP N行记录
2. 选取N1-N2行记录
3. 选取FOOT N行记录
当然需要考虑是否有ORDER BY子句的情况, 下面试以系统视图CAT为例分别说明.
注: A. 为没有ORDER BY的情况
B. 有ORDER BY的情况

1. 选取 TOP N 行记录
A. SELECT * FROM CAT WHERE ROWNUM<=N
B. SELECT * FROM
( SELECT * FROM CAT ORDER BY TABLE_TYPE )
WHERE ROWNUM<=N

2. 选取N1-N2行记录
A. SELECT TABLE_NAME,TABLE_TYPE FROM
( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT )
WHERE ROWSEQ BETWEEN N1 AND N2;
或:
SELECT * FROM CAT WHERE ROWNUM<=N2
MINUS
SELECT * FROM CAT WHERE ROWNUM<N1
B. SELECT TABLE_NAME,TABLE_TYPE FROM
( SELECT ROWNUM ROWSEQ,X.* FROM (SELECT * FROM CAT ORDER BY TABLE_TYPE) X)
WHERE ROWSEQ BETWEEN N1+1 AND N2;

3. 选取FOOT N行记录
这里是说明不知道记录集的记录个数的情况, 如果已知, 用上面2的方法即可
A. SELECT TABLE_NAME,TABLE_TYPE FROM
( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT )
WHERE ROWSEQ > ( SELECT COUNT(*)-N FROM CAT )
B. SELECT TABLE_NAME,TABLE_TYPE FROM
( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT ORDER BY TABLE_TYPE)
WHERE ROWSEQ > ( SELECT COUNT(*)-N FROM CAT )
或
SELECT * FROM 
( SELECT TABLE_NAME,TABLE_TYPE FROM CAT ORDER BY TABLE_TYPE DESC) 
WHERE ROWNUM<=N

以上在ORACLE8.1.5 for Windows2000pro 上测试通过
-- end --

__________________

生年不满百，常怀千岁忧。
昼短苦夜长，何不秉烛游！
为乐当及时，何能待来兹？
愚者爱惜费，但为后世嗤。
仙人王子乔，难可与等期。



由 jlandzpa 于 01-11-07 04:31 发表: 
选取N1-N2行记录的补充
select * from cat where rownum < n2+1
minus
select * from cat where rownum < n1+1
__________________
任弱水三千，我独取一瓢饮！
勿以善小而不为，勿以恶小而为之！
不要以为登上了山峰，你就征服了世界！
出差[忙].



由 jlandzpa 于 01-11-07 04:43 发表: 
 oracle FAQ(1) from chao_ping
1.快速整理破碎的表(在Oracle8i里边才可以这样使用)

ALTER TABLE table_name MOVE ( TABLESPACE XXX);

如何移动一张表所在的表空间

方法一：
1． Export 这张表
2． Drop这张表
3． Create table xxx tablespace xxx;
4． Imp Ignore=y

还要注意的一点是，所有要读取这张表的PL/SQL储存过程都会失效。需要重新编译。
1． SELECT * FROM DBA_OBJECTS WHERE STATUS = 'INVALID';
2． 对这些包，函数，过程重新编译。
方法二：
仅对Oracle8i适用。
使用下面的语句：
ALTER TABLE table_name MOVE 
TABLESPACE new_tablespace;

这样的话，所有的约束、索引、触发器都不会受到影响。

但是需要rebuild这个标上的所有索引。

2.怎样直接进入sql*plus而不用输入用户名，密码：

sqlplus /nolog;

sqlplus username/password@connect_string

3.怎样快速重建索引：

alter index xxx rebuild storage();

alter index xxx coalesce;

4. 为什么我看不到dbms_output的结果？

SET SERVEROUTPUT ON

5． 进行一次大的事务以后，已经COMMIT了，但为什么我的回滚段还是那样大？

因为没有设置OPTIMAL的值，所以不会自动收缩。

可以用alter rollback segment shrink to Xm;来手工进行收缩。

6． 为什么要使用VARCHAR2，而不用CHAR?

A．CHAR只支持2000字节长，而VARCHAR2支持4000字节的长度，适用性更好

B． CHAR 占用更多的存储空间，定义多长，它就占用多长的空间，插入字符后面自动加空格填充；而VARCHAR2不论定义多长，都只使用实际插入的长度。

7． 为什么从不同的数据字典看，表/索引所占用的空间不一样？

SQL> select blocks , empty_blocks from dba_tables where table_name=’表名';
BLOCKS EMPTY_BLOCKS
---------- ------------
1575 1524

SQL> select bytes,blocks,extents from dba_segments where segment_name='表名';
BYTES BLOCKS EXTENTS
---------- - --------- ----------

6348800 3100 1

这是因为第一个数据库视图DBA_TABLES的BLOCKS列是指实际上使用的BLOCK数目，还有一些BLOCK虽然被占用了，但是没有数据存在，不计入里边。而在DBA_SEGMENTS这个数据库视图里边，BLOCKS列是指这个表总共占用的BLOCK的数目，包括有数据和没有数据的BLOCK总量。如果把第一个视图里边的BLOCKS和EMPTY_BLOCKS地总和加起来，正好等于第二个视图的BLOCKS列的大小。

8． 怎样把数据库的一张，多张表存为一个普通的文本文件？

可以在SQL*Plus里边用SPOOL命令把选出来的数据保存在SPOOL指定的文件里边。

9． 怎样从一张表里删除重复的记录

SQL> SELECT * FROM EMP;

EMP_ID OFFICE_ID EMPNAME

305 12 ELLISON, GEORGE

305 12 MERCURIO, JASON

128 17 SIMPSON, LINDA

305 22 JACKSON, DREW

使用下面的SQL语句来识别那些重复的记录：

SQL> SELECT COUNT(*), EMP_ID, OFFICE_ID

FROM EMP

GROUP BY EMP_ID, OFFICE_ID

HAVING COUNT(*) > 1;

结果如下：

COUNT(*) EMP_ID OFFICE_ID

2 305 12

Table Example, with duplicate values:

SQL> SELECT * FROM EMP;

EMP_ID OFFICE_ID EMPNAME

305 12 ELLISON, GEORGE

305 12 MERCURIO, JASON

128 17 SIMPSON, LINDA

305 22 JACKSON, DREW

使用下面的语句来删除重复的记录：

SQL> DELETE FROM EMP A WHERE

(EMP_ID, OFFICE_ID, 2) IN

(SELECT EMP_ID, OFFICE_ID, decode(count(*),1,1,2)

FROM EMP B

WHERE A.EMP_ID=B.EMP_ID AND

A.OFFICE_ID = B.OFFICE_ID

GROUP BY EMP_ID, OFFICE_ID);

10． 怎样在SQL*PLUS里想数据库插入特殊字符？

可以使用CHR函数。

11． 怎样删除一个列？

在Oracle8i里边，可以直接Drop一个列。语法为alter table table_name drop column_name;

但是注意要在initsid.ora里边设定compatible=8.1.0以上。

12． 怎样重命名一个列？

1 alter table "table_name" add 
(new_column_name data_type); 

2 update table_name set new_column_name =
old_column_name where rowid=rowid; 

3 alter table table_name drop column
old_column_name; 

13． 怎样快速清空一张表？

Truncate table table_name;

14． 怎样为事务指定一个大的回滚段？

Set transaction use rollback segment rbs_name;

15． 怎样知道一张表上有那些权限赋予了哪些人，给他们了什么权限？

select * from dba_tab_privs where table_name='表名';

16． 怎么发现是谁锁住了你需要的一张表？

Select object_id from v$locked_object;

Select object_name, object_type from dba_objects where object_id=’’;

每次清空一张表的时候，（使用truncate），这张表的存储参数NEXT自动复位到最后被删除的那个extent的大小。同样，如果显式地从一张表里边释放空间，NEXT参数也会自动被设置成最后被释放的那个extent的大小。

在SQL*Plus里边可以为一个事务指定一个回滚段：这在有大的事务将要发生的话时候还是很有用的。使用下面的语句可以为这个事务指定一个回滚段：

SQL>SET TRANSACTION USE ROLLABCK SEGMENT 回滚段名称；

还可以在PL/SQL里边为一个事务指定一个回滚段（不使用动态sql语句）。这个需要使用Oracle提供的包：DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT(‘回滚段名称’);

在有些平台上的Oracle，在启动的时候会自动生成一个sgadefSID.dbf，用这个文件是否存在就可以判断一个实例是否在运行。这个文件包含了SGA在内存中的地址。在数据库关闭的时候，Oracle会自动删除这个文件。但是在Oracle8i里边，这个文件不再存在了。需要使用新的判断方式来断定究竟某个实例是否在运行。比如PS命令。

在Oracle7里边，想要知道数据文件是否可以自动扩展，必须从sys.filext$这张表里边查取，但是在Oracle8里边，从dba_data_files里边就可以知道数据文件是否可以自动扩展了。

从Oracle8i开始，可以创建另一类数据库一级的触发器，比如数据库启动、关闭，用户登录、注销等事务，都可以触发这个事件的发生，从而作某些记录。在数据库一级定义的触发器会在所有用户相应事件发生的时候触发，而在Schema一级定义的触发器只有在某个特定用户的相应事件发生的时候才会触发。

从Oracle8i开始，多了一种关闭数据库的方式：SHUTDOWN 
TRANSACTIONAL。这种方式允许所有的用户提交它们的工作。但是一旦提交之后就马上被切断联接，等所有用户都完成了各自的事务，shutdown就开始了。

从Oracle8开始，可以创建临时表，这些表的定义对于所有该用户的会话都是可以看到的，但是每个会话查询、插入、删除的数据和别的会话查询、插入、删除的数据都是不相关的。就像每个会话都分别有这样一份表一样。

从Oracle8i开始，对于那些没有进行分区的表，可以不用IMP/EXP就可以快速重组。但是这需要两倍于该表容量的表空间。这个语句就是：

ALTER TABLE TB_NAME MOVE TABLESPACE TS_NAME;

在Oracle8i里边可以创建反序索引。（CREATE INDEX i ON t (a,b,c) 
REVERSE;）。由于反序索引的相邻键值不是存放在物理相邻的位置，因此只有全索引扫描或者通过单个列这一类语句才能够有效利用这些索引。这类反序索引在Oracle并行服务器上能够较好地协调不同实例对数据库的修改，可以在一定程度上提高系统性能。

从Oracle8开始，$instance视图可以查获许多有用的信息：比如主机名称，实例名，启动时间，版本号等。

临时表空间里边创建的临时段只有在shutdown地时候才会被释放。

但是在permanent表空间里边创建的临时段在一个事务结束之后就会被释放，有Smon进程来完成这个任务。
__________________
任弱水三千，我独取一瓢饮！
勿以善小而不为，勿以恶小而为之！
不要以为登上了山峰，你就征服了世界！