怎么样让我的用户名和密码不泄漏？
=====================
在unix下，我用sqlplus sys/sys登陆，别的用户很轻易就能看到我的密码：怎么办？
$ ps -efgrep 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
---------- ---------------------------
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
|
