怎么样让我的用户名和密码不洩漏？
=====================
在unix下我用sqlplus sys/sys登陆别的用户很容易就能看到我的密码怎么办？
$ ps ef|grep sqlplus
oracle :: pts/ : sqlplus sys/sys
oracle :: pts/ : grep sqlplus
采用sqlplus /nolog
sql>connect sys/sys这样别的用户就看不到你的密码啦
怎样生成建表的完整的DDL语句？
====================
用exp 再Impshow=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 error
如何删除重复的记录
=============
第一个办法 生成建表的完整DDL语句并且生成tab_bak的表名
insert into tab_bak select distinct * from tab_name;
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 Akey_values = Bkey_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 t
where exists (select x from my_table t
where tkey_value = tkey_value
and tkey_value = tkey_value
and trowid > trowid);
如何快速为已有的表加上一个主键？
=====================
加上一个非空的列比如seqno然后
update table_name set seqno=rownum;
或者
CREATE SEQUENCE testseq START WITH INCREMENT BY ;
update table_name set seqno=testseqnextval;
SQL排序问题我怎么才能选择出按照某个列排序后前N行来？
=====================================
在SQL*Server 里面可以用这样的语句select top colcol from table_name;
从Oraclei开始支持这样的语法（在子查询里面使用order by语句）
select * from (select colcol from table_name order by colcol)
where rownum<;
这样就能够起到同样的效果
在Oracle或者以下可以这样
SELECT colcol FROM
(SELECT /*+ INDEX_DESC (table_name index_name) */ colcol FROM table_name)
WHERE rownum < ;
使用提示可以让Oracle在子查询返回结果之前先对他进行排序一般可以使用hintINDEX_DESC(TABLE_NAMEINDEX_NAME)来起到这个作用
我们可以分别查看两个SQL的执行计划
scott@testdb> select * from sort_sample;
ID NAME
aa
s
fdisk
format
low format
rows selected
scott@testdb> create index sort_id_idx on sort_sample(id);
Index created
scott@testdb> set autotrace on explain
scott@testdb> way :
scott@testdb> select * from (select * from sort_sample order by id desc) where rownum<;
ID NAME
s
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE
COUNT (STOPKEY)
VIEW
SORT (ORDER BY STOPKEY)
TABLE ACCESS (FULL) OF SORT_SAMPLE
scott@testdb> way :wrong result
scott@testdb> select * from sort_sample where rownum<;
ID NAME
aa
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE
COUNT (STOPKEY)
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 : can work in oracle and oracle
scott@testdb> select * from (select /*+index_desc(sort_sample sort_id_idx)*/ * from sort_sample)
where rownum<;
ID NAME
aa
//原因col sort_id_idx列为nullable所以CBO不能确定加上not null约束即可达到目的
用group by可以生成从小开始的排序
scott@testdb> SELECT IDNAME FROM
(SELECT IDNAMECOUNT(*) FROM SORT_SAMPLE GROUP BY ID NAME)
WHERE ROWNUM<;
ID NAME
aa
low format
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=)
COUNT (STOPKEY)
VIEW (Cost= Card= Bytes=)
SORT (GROUP BY STOPKEY) (Cost= Card= Bytes=)
TABLE ACCESS (FULL) OF SORT_SAMPLE (Cost= Card=
怎么每隔N条记录获得一条记录？比如第等？
=================================
CHAO@PING>select * from testseq;
ID NAME
this is th record
this is th record
this is th record
this is th record
this is th record
this is th record
this is th record
this is th record
this is th record
this is th record
rows selected
CHAO@PING>select id name from
(select id name rownum rz from testseq) temp
where mod(rz)=;
ID NAME
this is th record
this is th record
this is th record
CHAO@PING>
如何删除一个列？
===========
从Oraclei开始Oracle支持一个列的删除语法如下
alter table tab_name drop column col;
如何重命名一个列？
==============
CHAO@PING> create table testrename(id number nama varchar());
Table created
CHAO@PING> begin
for x in loop
insert into testrename values(xthis is ||to_char(x)||th record);
end loop;
end;
/
PL/SQL procedure successfully completed
CHAO@PING> commit;
Commit complete
CHAO@PING> alter table testrename add name varchar();
Table altered
CHAO@PING> update testrename set name=nama;
rows updated
CHAO@PING> alter table testrename drop column nama;
Table altered
CHAO@PING> select * from testrename;
ID NAME
