Oracle中使用的若干技术经典总结

　　怎么样让我的用户名和密码不泄漏？　
　　=====================
　　在unix下，我用sqlplus sys/sys登陆，别的用户很容易就能看到我的密码：怎么办？　
　　　
　　$ ps -ef|grep sqlplus　
　　oracle 3787 3781 1 22:05:34 pts/3 0:00 sqlplus sys/sys　
　　oracle 3789 3772 0 22:05:44 pts/2 0:00 grep sqlplus　
　　采用sqlplus /nolog　
　　　
　　sql>connect sys/sys，这样别的用户就看不到你的密码啦。　
　　　
　　怎样生成建表的完整的DDL语句？　
　　====================
　　用exp ,再Imp，show=y可以看到。　
　　　
　　或者使用某些Oracle 的小工具，比如quest的toad和sql*navigator.　
　　　
　　truncate table和delete table有些什么区别？　
　　==========================
　　truncate: DDL ,no rollback possibility and no rollback segment usage, quick ,release space used by the table except the original one.　
　　　
　　delete: dml, can rollback, use rollback space, not release space, slow, delete large table may cause ora-1555 error.　
　　　
　　如何删除重复的记录：　
　　=============
　　第一个办法： 1。生成建表的完整DDL语句,并且生成tab_bak的表名。　
　　　
　　2。insert into tab_bak select distinct * from tab_name;　
　　　
　　3。drop table tab_name, rename tab_bak to tab_name;　
　　　
　　第二个办法：　
　　　
　　DELETE FROM table_name A WHERE ROWID >　
　　( SELECT min(rowid) FROM table_name B　
　　　
　　WHERE A.key_values = B.key_values);　
　　　
　　第三个办法：　
　　　
　　Delete from my_table where rowid not in　
　　( SQL> select max(rowid) from my_table　
　　　
　　group by my_column_name );　
　　　
　　第四个办法：　
　　　
　　delete from my_table t1　
　　where exists (select 'x' from my_table t2　
　　　
　　where t2.key_value1 = t1.key_value1　
　　　
　　and t2.key_value2 = t1.key_value2　
　　　
　　and t2.rowid > t1.rowid);　
　　　
　　如何快速为已有的表加上一个主键？　
　　=====================
　　加上一个非空的列，比如seqno，然后：　
　　　
　　update table_name set seqno=rownum;　
　　　
　　或者：　
　　　
　　CREATE SEQUENCE testseq START WITH 1 INCREMENT BY 1;　
　　update table_name set seqno=testseq.nextval;　
　　　
　　SQL排序问题：我怎么才能选择出按照某个列排序后前N行来？　
　　=====================================
　　在SQL*Server 里面，可以用这样的语句：select top 10 col1,col2 from table_name;　
　　　
　　从Oracle8i开始，支持这样的语法（在子查询里面使用order by语句）　
　　　
　　select * from (select col1,col2 from table_name order by col1,col2)　
　　　
　　where rownum<11;　
　　　
　　这样就能够起到同样的效果。　
　　　
　　在Oracle8或者以下，可以这样：　
　　　
　　SELECT col1,col2 FROM　
　　(SELECT /*+ INDEX_DESC (table_name index_name) */ col1,col2 FROM table_name)　
　　WHERE rownum < 6;　
　　　
　　使用提示可以让Oracle在子查询返回结果之前先对他进行排序，一般可以使用hintINDEX_DESC(TABLE_NAME,INDEX_NAME)来起到这个作用。　
　　　
　　我们可以分别查看两个SQL的执行计划：　
　　　
　　scott@testdb> select * from sort_sample;　
　　　
　　ID NAME　
　　---------- ----------------------------------------　
　　1 aa　
　　5 33　
　　90 23s　
　　23 fdisk　
　　746 2343　
　　24 format　
　　3 low format　
　　7 rows selected.　
　　　
　　scott@testdb> create index sort_id_idx on sort_sample(id);　
　　　
　　Index created.　
　　scott@testdb> set autotrace on explain　
　　scott@testdb> --way 1:　
　　scott@testdb> select * from (select * from sort_sample order by id desc) where rownum<3;　
　　　
　　ID NAME　
　　---------- ----------------------------------------　
　　746 2343　
　　90 23s　
　　　
　　Execution Plan　
　　-------------------------------------　
　　0 SELECT STATEMENT Optimizer=CHOOSE　
　　1 0 COUNT (STOPKEY)　
　　2 1 VIEW　
　　3 2 SORT (ORDER BY STOPKEY)　
　　4 3 TABLE ACCESS (FULL) OF 'SORT_SAMPLE'　
　　　
　　scott@testdb> --way 2 :wrong result　
　　scott@testdb> select * from sort_sample where rownum<3;　
　　　
　　ID NAME　
　　---------- ----------------------------------------　
　　1 aa　
　　5 33　
　　　
　　Execution Plan　
　　----------------------------------------------------------　
　　0 SELECT STATEMENT Optimizer=CHOOSE　
　　1 0 COUNT (STOPKEY)　
　　2 1 TABLE ACCESS (FULL) OF 'SORT_SAMPLE'　
　　　
　　scott@testdb> ANALYZE TABLE SORT_SAMPLE COMPUTE STATISTICS;　
　　　
　　Table analyzed.　
　　　
　　scott@testdb> ANALYZE INDEX SORT_ID_IDX COMPUTE STATISTICS;　
　　　
　　Index analyzed.　
　　　
　　scott@testdb> --way 3: can work in oracle8 and oracle7　
　　scott@testdb> select * from (select /*+index_desc(sort_sample sort_id_idx)*/ * from sort_sample)　
　　2 where rownum<3;　
　　　
　　ID NAME　
　　---------- ----------------------------------------　
　　1 aa　
　　5 33　
　　　
　　//原因：col sort_id_idx列为nullable,所以CBO不能确定，加上not null约束即可达到目的。　
　　　
　　用group by可以生成从小开始的排序：　
　　　
　　scott@testdb> SELECT ID,NAME FROM　
　　2 (SELECT ID,NAME,COUNT(*) FROM SORT_SAMPLE GROUP BY ID, NAME)　
　　3 WHERE ROWNUM<3;　
　　　
　　ID NAME　
　　---------- ----------------------------------------　
　　1 aa　
　　3 low format　
　　　
　　Execution Plan　
　　----------------------------------------------------------　
　　0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=7 Bytes=175)　
　　1 0 COUNT (STOPKEY)　
　　2 1 VIEW (Cost=3 Card=7 Bytes=175)　
　　3 2 SORT (GROUP BY STOPKEY) (Cost=3 Card=7 Bytes=56)　
　　4 3 TABLE ACCESS (FULL) OF 'SORT_SAMPLE' (Cost=1 Card=7　
　　　
　　6。怎么每隔N条记录获得一条记录？比如第3，6，9等？　
　　=================================
　　CHAO@PING>select * from testseq;　
　　　
　　ID NAME　
　　---------- -------------------------------------　
　　1 this is 1th record　
　　2 this is 2th record　
　　3 this is 3th record　
　　4 this is 4th record　
　　5 this is 5th record　
　　6 this is 6th record　
　　7 this is 7th record　
　　8 this is 8th record　
　　9 this is 9th record　
　　10 this is 10th record　
　　　
　　10 rows selected.　
　　　
　　CHAO@PING>select id, name from　
　　2 (select id, name, rownum rz from testseq) temp　
　　3 where mod(rz,3)=0;　
　　　
　　ID NAME　
　　---------- -------------------------------------------　
　　3 this is 3th record　
　　6 this is 6th record　
　　9 this is 9th record　
　　　
　　CHAO@PING>　
　　　
　　如何删除一个列？　
　　===========
　　从Oracle8i开始，Oracle支持一个列的删除，语法如下：　
　　　
　　alter table tab_name drop column col1;　
　　　
　　7。如何重命名一个列？　
　　==============
　　CHAO@PING> create table testrename(id number, nama varchar2(30));　
　　　
　　Table created.　
　　　
　　CHAO@PING> begin　
　　2 for x in 1..10 loop　
　　3 insert into testrename values(x,'this is '||to_char(x)||'th record');　
　　4 end loop;　
　　5 end;　
　　6 /　
　　　
　　PL/SQL procedure successfully completed.　
　　　
　　CHAO@PING> commit;　
　　　
　　Commit complete.　
　　　
　　CHAO@PING> alter table testrename add name varchar2(30);　
　　　
　　Table altered.　
　　　
　　CHAO@PING> update testrename set name=nama;　
　　　
　　10 rows updated.　
　　　
　　CHAO@PING> alter table testrename drop column nama;　
　　　
　　Table altered.　
　　　
　　CHAO@PING> select * from testrename;　
　　　
　　ID NAME　
　　---------- ---------------------------------------------　

查看本文来源