1.手动启动方法:
1.以oracle帐号登陆服务器
2.执行命令sqlplus "/as sysdba"
3.在SQLPLUS中执行命令startup
注:停止命令 shutdown
shutdown immediate;
Oracle用户登录,启动监听
lsnrctl start
cat /etc/init.d/oracle自启动的脚本
chkconfig --add oracle设置自启动
/opt/oracle11g/product/11.1.0/network/admin/tnsnames.ora
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.30)(PORT = 1521))
/opt/oracle11g/product/11.1.0/network/admin/listener.ora
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.97)(PORT = 1521))
2.创建删除数据库用户
[oracle] $sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Wed May 16 15:37:48 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 – Production
SQL>create user emmg identified by emmg;
User created.
SQL>grant dba to emmg;
drop userafmscascade;
注:消字符
ctrl +退格
3.oracle服务器上执行语句
SQL>emmg/emmg@orcl
以指定的数据库用户登录
Sqlplus emmg/emmg@orcl
SQL>start filename;
或
SQL>@ filename;
4.解锁oracle用户
SQL>alter user“emmg”account unlock;
5.表空间相关
注:数据恢复时,如果数据文件比较大,需要先增加表空间,再执行恢复
查询数据文件,表空间名和大小
select file_name,tablespace_name,bytes/(1024*1024) MB from dba_data_files;
增加数据文件:
SQL> alter tablespace users add datafile '/opt/oracle11g/oradata/ORCL/users04.db' size 10 M autoextend on next 10m maxsize unlimited;
ALTER TABLESPACE "USERS"
ADD
DATAFILE '/opt/oracle11g/oradata/ORCL/user002.ora' SIZE
5M AUTOEXTEND
ON NEXT 1024K MAXSIZE UNLIMITED
6.删除表空间
步骤:
1.删除用户 drop user CSCDATA
2.删除表空间 drop tablespace CSCDATA including contents;
3.删除数据库文件 rm–rf CSCDATA*
7.删除表中的数据
truncate table info_stb
delete frominfo_stb
8.设置oracle自启动
在启动了Linux系统之后,转到/etc/init.d目录下;
将附件中的Oracle文件名称修改为oracle后,上传到/etc/init.d/目录下,
赋予执行权限
#chmod 750 /etc/init.d/oracle
链接:
#ln–s /etc/init.d/oracle /etc/rc1.d/K61oracle
#ln–s /etc/init.d/oracle /etc/rc3.d/S61oracle
执行以下命令:
#chkconfig --level 345 oracle on
#chkconfig --add oracle
9.Case when 语句
Case里面是列值,后面是列名(别名)
统计每个省男女数目
select sex,count(case province when '广东省' then '广东省' end )广东省,
count(case province when '江西省' then '江西省' end )江西省,
count(case province when '浙江省' then '浙江省' end )浙江省
from student
group by sex
select sid,
( case when chinese >= 80 then '优秀'
when chinese >= 60 then '及格'
else '不及格' end ) as语文,
( case when math >= 80 then '优秀'
when math >= 60 then '及格'
else '不及格' end) as数学,
( case when english >= 80 then '优秀'
when english >= 60 then '及格'
else '不及格' end) as 英语
from stu_grade
10.触发器
发生相应的事件时自动执行
create or replace TRIGGER tr_before_insert_user
BEFORE INSERT
ON PMS_USER
FOR EACH ROW
BEGIN
:new.USERSTATUS :=0;
END;
Update表1时,触发表2的更新:
11.存储过程
执行存储过程
execute pro_insert_user(10,10,'test10','test10pwd',10);
或者直接在界面上执行
create or replace PROCEDURE pro_insert_user(id in NUMBER,sn in number,name in VARCHAR2,pwd in VARCHAR2,card in NUMBER)
AS
BEGIN
Insert into PMS_USER (USERID,USERSN,USERNAME,USERPWD,IDCARDNO) values (id,sn,name,pwd,card);
commit;
END pro_insert_user;
create or replace procedure insert_audience_data
is
starttime timestamp;
createtime timestamp;
begin
for i in 101..102
LOOP
insert into info_stb values(99999,'112640000056',16512,1,i,0,'cctv',0,starttime,1,createtime);
END LOOP;
end insert_audience_data;
/
create or replace procedure procedure_Samplesignalvalue as
begin
for i in 1 .. 10
loop
insert into Info_Samplesignalvalue(RecordID,OID,NodeValue,Createtime) values (i,i,i,to_date('2012-04-09 15:20:01','yyyy-mm-dd hh24:mi:ss'));
end loop;
end procedure_Samplesignalvalue;