Java开发网 - ORACLE使用若干技术

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

海纳百川，有容乃大
壁立千仞，无欲则刚
智者不惑，勇者无惧
止戈为武，仁者无敌

话题树型展开

人气	标题	作者	字数	发贴时间
8190	[精华] ORACLE使用若干技术	九佰	10101	2003-07-09 16:48
5682	Re:ORACLE使用若干技术	nothing	19	2003-07-09 16:57

已读帖子

新的帖子

被删除的帖子