SVRMGR >;exit;
2. 使用操作系统命令来移动数据库文件位置(假设这里操作系统为SOLARIS 2.6). 在UNIX中用 mv命令可以把文件移动到新的位置，
#mv /ora13/orarun/document.dbf /ora12/orarun
3. 装载数据库并用alter database命令来改变数据库中的文件名.
SVRMGR >; connect internal;
SVRMGR >; startup mount RUN73;
SVRMGR >; alter database rename file
>; ‘/ ora13/orarun/document.dbf’
>; ‘/ ora12/orarun/document.dbf’;
4. 启动实例.
SVRMGR >; alter database open;
13.连接查询结果：
表a 列 a1 a2
记录 1 a
1 b
2 x
2 y
2 z
用select能选成以下结果:
1 ab
2 xyz
下面有两个例子：
1.使用pl/sql代码实现，但要求你组合后的长度不能超出oracle varchar2长度的限制
create or replace type strings_table is table of varchar2(20);
/
create or replace function merge (pv in strings_table) return varchar2
is
ls varchar2(4000);
begin
for i in 1..pv.count loop
ls := ls || pv(i);
end loop;
return ls;
end;
/
create table t (id number,name varchar2(10));
insert into t values(1,''''Joan'''');
insert into t values(1,''''Jack'''');
insert into t values(1,''''Tom'''');
insert into t values(2,''''Rose'''');
insert into t values(2,''''Jenny'''');
column names format a80;
select t0.id,merge(cast(multiset(select name from t where t.id = t0.id) as strings_table)) names
from (select distinct id from t) t0;
drop type strings_table;
drop function merge;
drop table t;
2.纯粹用sql：
表dept, emp
要得到如下结果
deptno, dname, employees
---------------------------------
10, accounting, clark;king;miller
20, research, smith;adams;ford;scott;jones
30, sales, allen;blake;martin;james;turners
每个dept的employee串起来作为一条记录返回
This example uses a max of 6, and would need more cut n pasting to do more than that：
SQL>; select deptno, dname, emps
2 from (
3 select d.deptno, d.dname, rtrim(e.ename ||'''', ''''||
4 lead(e.ename,1) over (partition by d.deptno
5 order by e.ename) ||'''', ''''||
6 lead(e.ename,2) over (partition by d.deptno
7 order by e.ename) ||'''', ''''||
8 lead(e.ename,3) over (partition by d.deptno
9 order by e.ename) ||'''', ''''||
10 lead(e.ename,4) over (partition by d.deptno
11 order by e.ename) ||'''', ''''||
12 lead(e.ename,5) over (partition by d.deptno
13 order by e.ename),'''', '''') emps,
14 row_number () over (partition by d.deptno
15 order by e.ename) x
16 from emp e, dept d
17 where d.deptno = e.deptno
18 )
19 where x = 1
20 /
DEPTNO DNAME EMPS
------- ----------- ------------------------------------------
10 ACCOUNTING CLARK, KING, MILLER
20 RESEARCH ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH
30 SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD
14.在Oracle中建一个编号会自动增加的字段,以利于查询
1、建立序列：
CREATE SEQUENCE checkup_no_seq
NOCYCLE
MAXVALUE 9999999999
START WITH 2;
2、建立触发器：
CREATE OR REPLACE TRIGGER set_checkup_no
BEFORE INSERT ON checkup_history
FOR EACH ROW
DECLARE
next_checkup_no NUMBER;
BEGIN
--Get the next checkup number from the sequence
SELECT checkup_no_seq.NEXTVAL
INTO next_checkup_no
FROM dual;
--use the sequence number as the primary key
--for the record being inserted
:new.checkup_no := next_checkup_no;
END;
15.查看对象的依赖关系(比如视图与表的引用)
查看视图：dba_dependencies 记录了相关的依赖关系
查东西不知道要查看哪个视图时，可以在DBA_Objects里看，
select object_name from dba_objects where object_name like ''''%ROLE%''''(假如查看ROLE相关)
然后DESC一下就大体上知道了。
16.要找到某月中所有周五的具体日期
select to_char(t.d,''''YY-MM-DD'''') from (
select trunc(sysdate, ''''MM'''')+rownum-1 as d
from dba_objects
where rownum < 32) t
where to_char(t.d, ''''MM'''') = to_char(sysdate, ''''MM'''') --找出当前月份的周五的日期
and trim(to_char(t.d, ''''Day'''')) = ''''星期五''''
--------
03-05-02
03-05-09
03-05-16
03-05-23
03-05-30
如果把where to_char(t.d, ''''MM'''') = to_char(sysdate, ''''MM'''')改成sysdate-90，即为查找当前
月份的前三个月中的每周五的日期
上一页 [1] [2] |
