[img]http://dl.iteye.com/upload/attachment/236897/d292e815-80cd-3d00-bef7-4f584ba3c4b5.jpg[/img]
不能正确启动监听服务
D:\oracle\ora92\network\admin\listener.ora
D:\oracle\ora92\network\admin\tnsnames.ora 这两个文件中修改用户名
SQL> create user test2 identified by test2;
用户已创建。
SQL> connect test2/test2@erp2;
ERROR:
ORA-01045: user TEST2 lacks CREATE SESSION privilege; logon denied
警告: 您不再连接到 ORACLE。
SQL> grant connect to test2;
授权成功。
create table student(stuid char(5) not null,
name varchar(20) not null,
classid char(3) );
alter table student add primary key(stuid);
grant dba to test2;
create table result(stuid char(5) not null,
socre number(5,2),subject varchar2(10) not null,
examday date not null
)
insert into team values ('t47','47班');
insert into team values ('t44','44班');
insert into student values('t4301','小陈','t43')
insert into student values('t4302','小葛','t43')
insert into student values('t4701','小欧,'t47')
insert into student values('t4702','小玉','t47')
select * from test2.student
insert into result values ('t4301',90,'数学','20-2月-09')
insert into result values ('t4302',90,'数学','20-2月-09')
insert into result values ('t4301',90,'数学','20-3月-09')
insert into result values ('t4301',90,'英语',
to_date('2009-04-22','yyyy-mm-dd')
)
insert into result values ('t4302',96,'英语',
to_date('2009-04-22','yyyy-mm-dd')
)
insert into result values ('t4302',76,'数学',
to_date('2009-04-22','yyyy-mm-dd')
);
insert into result values ('t4701',90,'英语',
to_date('2009-04-22','yyyy-mm-dd')
);
insert into result values ('t4702',96,'英语',
to_date('2009-04-22','yyyy-mm-dd')
);
insert into result values ('t4701',66,'数学',
to_date('2009-05-22','yyyy-mm-dd')
);
insert into result values ('t4701',99,'英语',
to_date('2009-04-22','yyyy-mm-dd')
);
insert into result values ('t4702',86,'英语',
to_date('2009-02-22','yyyy-mm-dd')
);
insert into result values ('t4702',46,'数学',
to_date('2009-01-22','yyyy-mm-dd')
);
select sysdate from dual;
select extract(sysdate,'year') from dual;
SQL> select translate('dcsa87s7d89q234nksjd0asc0asidwj9','#0123456789','#') from dual;
TRANSLATE('DCSA87S7D89Q234NKSJD0ASC0ASIDWJ
------------------------------------------
dcsasdqnksjdascasidwj
select to_char(sysdate,'yyyy-mm') from dual
create table team (id char(3) not null,
name varchar2(10) not null) primary key(id)
select id,team.name,max(socre) ,min(socre) from
team,result,student
where
and
group by id,team.name
;
select team.id,team.name,max(nvl(result.socre,0)) ,
min(nvl(result.socre,0)) from
team
left join result on result.stuid=student.stuid
left join team on student.classid= team.id
group by team.id,team.name;
selct * from team;
select a.id,a.name,nvl(temp.maxresult,0),
nvl(temp.minresult,0)from
team a,
(select team.id,team.name,max(socre) as maxresult ,min(socre) as minresult from
team,result,student
where result.stuid=student.stuid
and student.classid= team.id
group by id,team.name) temp
where temp.id(+)
;
select a.id,a.name,nvl((select max(socre) from result,student where student.classid=a.id and result.stuid=student.stuid),0),
nvl((select min(socre) from result,student where student.classid=a.id and result.stuid=student.stuid),0)
from
team a
不能正确启动监听服务
D:\oracle\ora92\network\admin\listener.ora
D:\oracle\ora92\network\admin\tnsnames.ora 这两个文件中修改用户名
SQL> create user test2 identified by test2;
用户已创建。
SQL> connect test2/test2@erp2;
ERROR:
ORA-01045: user TEST2 lacks CREATE SESSION privilege; logon denied
警告: 您不再连接到 ORACLE。
SQL> grant connect to test2;
授权成功。
create table student(stuid char(5) not null,
name varchar(20) not null,
classid char(3) );
alter table student add primary key(stuid);
grant dba to test2;
create table result(stuid char(5) not null,
socre number(5,2),subject varchar2(10) not null,
examday date not null
)
insert into team values ('t47','47班');
insert into team values ('t44','44班');
insert into student values('t4301','小陈','t43')
insert into student values('t4302','小葛','t43')
insert into student values('t4701','小欧,'t47')
insert into student values('t4702','小玉','t47')
select * from test2.student
insert into result values ('t4301',90,'数学','20-2月-09')
insert into result values ('t4302',90,'数学','20-2月-09')
insert into result values ('t4301',90,'数学','20-3月-09')
insert into result values ('t4301',90,'英语',
to_date('2009-04-22','yyyy-mm-dd')
)
insert into result values ('t4302',96,'英语',
to_date('2009-04-22','yyyy-mm-dd')
)
insert into result values ('t4302',76,'数学',
to_date('2009-04-22','yyyy-mm-dd')
);
insert into result values ('t4701',90,'英语',
to_date('2009-04-22','yyyy-mm-dd')
);
insert into result values ('t4702',96,'英语',
to_date('2009-04-22','yyyy-mm-dd')
);
insert into result values ('t4701',66,'数学',
to_date('2009-05-22','yyyy-mm-dd')
);
insert into result values ('t4701',99,'英语',
to_date('2009-04-22','yyyy-mm-dd')
);
insert into result values ('t4702',86,'英语',
to_date('2009-02-22','yyyy-mm-dd')
);
insert into result values ('t4702',46,'数学',
to_date('2009-01-22','yyyy-mm-dd')
);
select sysdate from dual;
select extract(sysdate,'year') from dual;
SQL> select translate('dcsa87s7d89q234nksjd0asc0asidwj9','#0123456789','#') from dual;
TRANSLATE('DCSA87S7D89Q234NKSJD0ASC0ASIDWJ
------------------------------------------
dcsasdqnksjdascasidwj
select to_char(sysdate,'yyyy-mm') from dual
create table team (id char(3) not null,
name varchar2(10) not null) primary key(id)
select id,team.name,max(socre) ,min(socre) from
team,result,student
where
and
group by id,team.name
;
select team.id,team.name,max(nvl(result.socre,0)) ,
min(nvl(result.socre,0)) from
team
left join result on result.stuid=student.stuid
left join team on student.classid= team.id
group by team.id,team.name;
selct * from team;
select a.id,a.name,nvl(temp.maxresult,0),
nvl(temp.minresult,0)from
team a,
(select team.id,team.name,max(socre) as maxresult ,min(socre) as minresult from
team,result,student
where result.stuid=student.stuid
and student.classid= team.id
group by id,team.name) temp
where temp.id(+)
;
select a.id,a.name,nvl((select max(socre) from result,student where student.classid=a.id and result.stuid=student.stuid),0),
nvl((select min(socre) from result,student where student.classid=a.id and result.stuid=student.stuid),0)
from
team a