Java开发网 |
注册 |
登录 |
帮助 |
搜索 |
排行榜 |
发帖统计
|
您没有登录 |
» Java开发网 » Database/JDBC/SQL/JDO/Hibernate
打印话题 寄给朋友 订阅主题 |
作者 | ORACLE使用若干技术 [精华] |
九佰
版主 发贴: 983 积分: 126 |
于 2003-07-09 16:48
怎么样让我的用户名和密码不泄漏? ===================== 在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 ---------- --------------------------------------------- 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>
九佰 edited on 2003-07-24 10:42
http://www.teamlet.org 海纳百川,有容乃大 壁立千仞,无欲则刚 智者不惑,勇者无惧 止戈为武,仁者无敌 |
作者 | Re:ORACLE使用若干技术 [Re:九佰] |
nothing
天外飞仙..... CJSDN高级会员 发贴: 1636 积分: 131 |
于 2003-07-09 16:57
精华精华!!!(要是没有小鬼脸就好了) 躲得过的怪物,躲不过的刺激 |
已读帖子 新的帖子 被删除的帖子 |
Powered by Jute Powerful Forum® Version Jute 1.5.6 Ent Copyright © 2002-2021 Cjsdn Team. All Righits Reserved. 闽ICP备05005120号-1 客服电话 18559299278 客服信箱 714923@qq.com 客服QQ 714923 |