Oracle常见问题解答

Oracle常见问题解答

以前搜集的一个Oracle比较常见问题的列表,忘记了是从哪来的 关于 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 ( 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 -- 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进程来完成这个任务。 　 oracle FAQ(2) from chao_ping 关于OPTIMAL参数 optimal是用于限制回滚段大小的一个存储参数。在执行一个长的事务之后，那个事务所使用的回滚段会比较大，而设置了Optimal这个参数以后，一旦事务提交结束，回滚段自动收缩到Optimal所指定的大小。 如果你的系统中有许多长时间运行的事务的话，那么应该把回滚段的Optimal参数设置的比较大一点。这样有利于保持回滚段表空间的连续性。否则不断的扩张、收缩会使表空间更加破碎。 如果系统中主要的事务都是短时间的，那么应该把回滚段设置的比较小一些，这样有利于让回滚段里面的信息可以存储在SGA里边，以利于提高系统性能。 回滚段的Optimal参数可以在创