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 suclearcase/" target="_blank" >ccessfully 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　
　　---------- ---------------------------
　　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.　
　　　
　　8。强大的Decode的用法：在SQl里面实现IF－THEN-ELSE的控制？　
　　========================================
　　CHAO@PING>create table testdecode(id number, sex char, name varchar2(20));　
　　　
　　Table created.　
　　　
　　CHAO@PING>insert into testdecode values(1,'F','this is a woman');　
　　　
　　1 row created.　
　　　
　　CHAO@PING>insert into testdecode values(1,'M','this is a man');　
　　　
　　1 row created.　
　　　
　　CHAO@PING>commit;　
　　　
　　Commit complete.　
　　　
　　CHAO@PING>select decode(sex,'F','女','M','男','未知') as 性别, name as 名字 from testdecode;　
　　　
　　性别 名字　
　　-------- -------------------------
　　女 this is a woman　
　　男 this is a man　
　　　
　　9。怎样从表中随机选择一些数据？（Oracle8i/9i支持）－－sample语句
　　==============================
　　scott@QINGH> create table testsample(a number);　
　　　
　　Table created.　
　　　
　　scott@QINGH> begin　
　　2 for x in 1..1000 loop　
　　3 insert into testsample values(x);　
　　4 end loop;　
　　5 end;　
　　6 /　
　　　
　　PL/SQL procedure successfully completed.　
　　　
　　scott@QINGH> commit;　
　　　
　　Commit complete.　
　　　
　　scott@QINGH> select * from testsample sample(1);　
　　　
　　A　
　　----------　
　　8　
　　156　
　　234　
　　373　
　　416　
　　469　
　　494　
　　603　
　　714　
　　827　
　　829　
　　　
　　A　
　　----------　
　　925　
　　　
　　12 rows selected.　
　　　
　　限制：只能够用于单个表的查询　
　　　
　　关于数据类型转换；　
　　　
　　long-clob: to_lob　
　　　
　　long-varchar：in sql*plus, set copytypecheck off, and use copy command.　
　　　
　　clob->varchar　
　　　
　　SQL> create table lob(a clob);　
　　　
　　Table created.　
　　　
　　SQL> insert into lob values('this is for test');　
　　　
　　1 row created.　
　　　
　　SQL> commit;　
　　　
　　Commit complete.　
　　　
　　SQL> select dbms_lob.getlength(a) from lob;　
　　　
　　DBMS_LOB.GETLENGTH(A)　
　　---------------------　
　　16　
　　　
　　SQL> declare　
　　2 x long;　
　　3 y clob;　
　　4 begin　
　　5 select a into y from lob;　
　　6　
　　7 x:=dbms_lob.substr(y,dbms_lob.getlength(y),1);　
　　8 dbms_output.put_line(x);　
　　9 end;　
　　10 /　
　　this is for test　
　　　
　　PL/SQL procedure successfully completed.　
　　　
　　在SQLplus里面这么返回函数的执行结果？　
　　===========================
　　scott@QINGHAI> create or replace function test (inputvar in varchar)　
　　2 return varchar　
　　3 is　
　　4 begin　
　　5 return upper(inputvar);　
　　6 end;　
　　7 /　
　　　
　　Function created.　
　　　
　　scott@QINGHAI> declare x varchar2(20);　
　　2 y varchar2(20);　
　　3 begin　
　　4 x:='thistest';　
　　5 select test(x) into y from dual;　
　　6 dbms_output.put_line(y);　
　　7 end;　
　　8 /　
　　THISTEST　
　　　
　　PL/SQL procedure successfully completed.　
　　或者：　
　　　
　　sql>exec dbms_output.put_line(test('this is for test'));　
　　　
　　
　　初学者的一个大难题：Oracle里面的日期问题　
　　===========================
　　1. init.ora中的nls_date_format几乎总是不会起作用。　
　　这是因为: 系统参数的设置，session优先于system　
　　而对于几乎所有的客户端， register或environment varible 中nls_lang都已被定义，从而造成nls_date_format或者被显式定义，或者根据nls_lang取得了一个缺省值。　
　　　
　　2. 在nt的注册表中，oracle\homeX下的值优先于oracle下，所以你应该在oracle\homeX\下设置。　
　　　
　　3. 如果还是不行，或者你有多个oracle home, 可通过在系统环境变量中设置，其作用等同于unix下.profile中设置。　
　　　
　　4. 修改注册表中的参数，不必重新启动nt或oracle, 重新建立的新SESSION会使用新的设置。　
　　　
　　13。关于View：什么样的View允许DML，怎么查看View的源代码，怎么修改View，怎么编译view？　
　　　
　　scott@QINGH> CREATE TABLE MYTABLE(AA NUMBER);　
　　　
　　Table created.　
　　　
　　scott@QINGH> CREATE VIEW TESTVIEW AS SELECT * FROM MYTABLE;　
　　　
　　View created.　
　　　
　　scott@QINGH> SELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS　
　　WHERE STATUS='INVALID';　
　　　
　　no rows selected　
　　　
　　scott@QINGH> DROP TABLE MYTABLE;　
　　　
　　Table dropped.　
　　　
　　scott@QINGH> SELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS　
　　　
　　WHERE STATUS='INVALID';　
　　　
　　OBJECT_NAME　
　　--------------------------
　　TESTVIEW　
　　　
　　scott@QINGH> CREATE TABLE MYTABLE(AA NUMBER);　
　　　
　　Table created.　
　　　
　　scott@QINGH> SELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS　
　　　
　　WHERE STATUS='INVALID';　
　　　
　　OBJECT_NAME　
　　-------------------------------
　　TESTVIEW　
　　　
　　scott@QINGH> ALTER VIEW TESTVIEW COMPILE;　
　　　
　　View altered.　
　　　
　　scott@QINGH> SELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS　
　　　
　　WHERE STATUS='INVALID';　
　　　
　　no rows selected　
　　　
　　scott@QINGH>
　　

原文转自：http://www.ltesting.net