CSDN博客

img ntljy

ORACLE使用若干技术

发表于2004/6/30 21:01:00  819人阅读


怎么样让我的用户名和密码不泄漏?  
=====================
在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>
阅读全文
0 0

相关文章推荐

img
取 消
img