Oracle笔记1

Oracle笔记1

sqlplus “sys/test1234 as sysbda”desc $controlfileselect status,name form v$controlfile;desc v$datafileselect file#,status from v$datafile;desc v$logfileselect member from v$logfile;SGA ：DB buffer， 大共享区， 共享池， Redo buffer， 固定SGADB高速缓存池（DB buffer）： 默认缓存池， 保持缓池， 再生缓存池共享池： 库缓存区（共享SQL区 PL／SQL区）， 字典缓存区 块(block 8K 操作系统的整数倍) 盘区(extent) 段(segment) 表空间(tablespace) 数据文件(datafile)我们只能指定表在那个表空间中sqlplus/nologconnect sys/test1234 as sysdbastartup 实例 控制文件 数据文件startup mount 启动数据文件 但是不启动控制文件archive log list startup mount alter database open 非归档方式改为归档方式startup nomount 控制文件失的时间，重新创建控制文件shutdown immediateshutdown shutdown transactional shutdown abort 强行关闭数据库​​​user TESTUSER account unlock; create user “test” identified by “test”;grant connect to “test”;sqlplus scott/tigerhelp indexselect * from dept? setset sqlblanklines on 支持空格行替代变量select * from dept where deptno=10select * from dept where deptno=&tt查看命令list ll 1 2c /n/ml/? change del 4 ldel 2 3la from deptsave d:/oracle/test.txtl@c:/oracle/test.txtget c:/orcle/text.txtedit /? colcol deptno heading “编号”desc deptcol dname format a10 heading “部门名称”col deptno format 999,999,999connect sys/test1234 as sysdbaset linesize 50ttitle center “我的标题” skip 1-left “测试报表” right “页” -format 999 sql.pno skip 2ttitle off break ? conpbreak on pub select * from books conp count label “计数” of books_name on pubspool d:/1.txtspool off edit d:/1.txt视图学习视图称为虚表视图的作用 安全性 方便 一致性create or replace view myviewasselect * from dept

create or replace view myviewasselect * from books where price>30with check option edit c:/1.txt@ c:/1.txtcreate or replace view myviewasselect * from books where price>30with read onlydba_views desc all_viewsdesc user_viewsselect text from user_views where view_name=’u_views’oracle的同义词select user from dualselect * from scott.dept同义词create synonym dept for scott.deptselect * from dept drop synonym deptcreate public synonym dept for scott.deptselect * from deptconnect tt/tt11select * from dept desc dba_synonymsdesc user_synonyms序列create sequence myseq start with 1increment by 1order nocycle;select myseq.nextval from dual;select myseq.currual form dual;create table auto((a number,b varchar2(10)))create sequence myseq insert into auto values(myseq.nextval,”dd”)desc dba_sequencesselect sequence_name,sequence_owner from dba_sequences where sequence_owner=’TT’select user from dualalter sequence myseq increment by 3select myseq.nextval from dualsql语言基础connect scott/tigerDDLcreate table abc(a varchar2(20),b char(20))alter table abc add c numberalter table abc drop column cDCLgrant select on dept to ttrevoke select on dept from ttDMLinsert into abc values(’aa’,'cc’)delete update常用系统函数字符length ltrim,replace,rtrim,substr,trim日期Sysdate,current_date,next_day转换To_char,to_date,to_number聚集函数sum,avg,max,min,count其它user,decode,nvlselect length(’ddd’) from dualselect lengthb(’dd好d’) from dualselect trim(’ ddd ‘) from dualselect rtrim(’ ddd ‘) from dualselect ltrim(’ ddd ‘) from dualselect SUBSTR(’abacedf’,1,3) from dualselect SUBSTR(’abacedf’,length(’abacedf’)-3+1,3) from dualSELECT current_date FROM dualALTER SESSION SET NLS_DATE_FORMAT=’dd-mon-yyyy hh:mi:ss’SELECT NEXT_DAY(sysdate,’星期五’) from dualSELECT TO_CHAR(sysdate,’yyyy-mm-dd hh:mi:ss’) from dualSELECT TO_DATE(’12-3月-04′) from dualSELECT TO_NUMBER(’222′) from dualselect user from dualselect sum(decode(sex,’男’,1,0)) 男人数,sum(decode(sex,’女’,1,0)) 女人数 from e;select a1,nvl(a2,’地输入’) a2 from aa;select * from aa where a2 is nullselect * from aa where a2 is not null分组查询聚集函数不能在where中，如果要用则用having select a,count(a) from aa group by a having count(a)>1模糊查询select * from aa where a2 like ‘a_’ select * from aa where a2 like ‘a%’select * from aa where a2 like ‘_a’select * from aa where a2 like ‘__a’select * from aa where a2 like ‘%a’ select * from aa where a2 like ‘%a%’ 表的连接from a,b where a.=b.a join b on a.=b.from a,b where a.id=b.id(+)左连接 左边为全部显示出来，有匹配值，则写上，无则以空值填充右连接则相反

子查询无关子查询select * from e where id in (select id from d);相关子查询select * from e where id in (select id from d where id=e.id and id=’03′);select * from e where id not in (select id from d where id=e.id and id=’03′);select * from e where exists (select id from d where id=e.id and id=’03′);select * from e where not exists (select id from d where id=e.id and id=’03′);select * from a union select * from dselect * from a intersect select * from d 返回两者教匹配的记录

insert into e(id,name) select id,name from d ;create table ttt as (select * from e)

PL/SQL基础declare…begin…exception…end

declarex varchar2(20);beginx:=’this is..’;dbms_output.put_line(’x的值为:’||x);end;/

set serveroutput on size 10000l/

save D:/1.txt@ D:/1.txt

/**/块注释

declarex varchar2(20):=’456kkk’;–y integer:=123;y string(10):=’123′;begin–x:=’this is..’;–dbms_output.put_line(’x的值为:’||x);dbms_output.put(’x的值为:’||x||’y的值是：’||y);dbms_output.new_line;end;/

declarea number;b varchar2(10);begina:=2;if a=1 thenb:=’a';elsif a=2 thenb:=’b';elseb:=’c';end if;dbms_output.put_line(’B值是：’||b);end;/

declarea number;b varchar2(10);begina:=10;case when a=1 then b:=’a';when a=2 then b:=’b';when a=3 then b:=’c';when a=4 then b:=’d';elseb:=’others’;end case;dbms_output.put_line(’B值是：’||b);end;/

declarecursor mycur ISselect * from dept;myrecord dept%rowtype;beginopen mycur;fetch mycur into myrecord;while mycur%found loopdbms_output.put_line(myrecord.deptno||’,'||myrecord.dname);fetch mycur into myrecord;end loop;close mycur;end;/

declarecursor mycur_para(id varchar2) ISselect dname from dept where deptno=id;t_name dept.dname%type;beginopen mycur_para(’10′);loopfetch mycur_para into t_name;exit when mycur_para%notfound;dbms_output.put_line(t_name);end loop;close mycur_para;end;/

declarecursor mycur_para(id varchar2) ISselect dname from dept where deptno=id;begindbms_output.put_line(’*******结果集为********’);for mycur in mycur_para(’10′) loopdbms_output.put_line(mycur.dname);end loop;end;/

declaret_name dept.dname%type;cursor cur(id varchar2) ISselect dname from dept where deptno=id;beginif cur%isopen thendbms_output.put_line(’游标己被打开’);elseopen cur(’10′);end if;fetch cur into t_name;close cur;dbms_output.put_line(t_name);end;/

declaret_name varchar2(20);cursor mycur ISselect dname from dept;beginopen mycur;loopfetch mycur into t_name;exit when mycur%notfound or mycur%notfound is null;dbms_output.put_line(’游标mycur的rowcount是:’||mycur%rowcount);end loop;close mycur;end;/

declare cursor IS select dname from dept for update;text varchar2(20);beginopen cur;fetch cur into text;while cur%found loopupdate dept set dname=name||’_t’ where current of cur;fetch cur into text;end loop;close cur;end ;/

beginfor cur in(select dname from dept) loopdbms_output.put_line(cur.dname);end loop;end;/建议不要使用游标因为效率不是很高

存储过程create or replace procedure myproc(id in number)ISname varchar2(10);beginselect dname into name from dept where deptno=id;dbms_output.put_line(name);end myproc;/

show errors procedure myproc;

declaretid number(10);begintid:=10;myproc(tid);end;/

beginmyproc(10);end;/

execute myproc(10);

create or replace procedure myproc2(id varchar2,name out varchar2)is beginselect dname into name from dept where deptno=id;end;/

declaretid varchar2(10);dname varchar2(10);begintid:=’10′;myproc2(tid,tname);end;/事务与触发器delete from books where books_id=’21′commit;delete from books where books_id=’22′rollback;事务用于确保数据完整性和并发处理的能力它将一条／一组SQL语当作成一个逻辑上的单元，用于保障这些语句都成功／失败原子性atomicity一致性consistency隔离性isolation永久性durability行级触发器create or replace trigger del_deptmentidafter delete on deptmentfor each rowbegindelete from empl where id=:old.id;end del_deptmentid;/delete from deptment where id=1;rollback;

create or replace trigger insert_deptafter insert on deptmentfor each rowbegininsert into empl(eid,ename,id) values(’123′,’dd’,:new.id);end;/

create or replace trigger update_deptafter updateon deptmentfor each rowbeginupdate empl set id=:new.id where id=:old.id;end;/在触发器中不能写rollback,commit等，可以用以下语句实现某些记录不更新create or replace trigger books_deleteafter delete on booksfor each rowbeginif :old.books_id=22 thenraise_application_error(-20000,’不充许删除’);end if ;end;/

语句级触发器create table mylog(curr_user varchar2(100),curr_date date,act char(1));create or replace trigger dml_booksafter insert or delete or update on booksbeginif inserting theninsert into mylog values(user,sysdate,’I');elsif deleting theninsert into mylog values(user,sysdate,’D');elseinsert into mylog values(user,sysdate,’U');end if;end;/

update books set books_name=’中途镐’ where books_id=43insert into books values(myseq.nextval,’二级战犯’,33.5,5,’人民文学’)select curr_user,to_char(curr_date,’yyyy-mm-dd hh24:mi:ss’) 日期 ,act 动作   from mylog

create or replace trigger set_numberbefore insert on booksfor each rowdeclaresn number(5);beginselect myseq.nextval into sn from dual;:new.books_id:=sn;end;/

create or replace view empl_deptmentasselect eid,ename,sex,e.id,d.name from empl e,deptment d where e.id=d.id/

select * from empl_deptment

create or replace trigger tr_empl_deptmentinstead of insert on empl_deptmentfor each rowbegininsert into deptment values(:new.id,:new.name);insert into empl values(:new.eid,:new.ename,:new.sex,:new.id);end;/insert into empl_deptment values(1,’罗’,'男’,1,’销售部’)/

安全管理oracle的安全管理体系用户管理角色管理配置文件的设置

(用户,角色)相当于操作系统的用户和组

查询当前账号select user from dual;conn scott/tiger as sysdba;create or replace trigger tr_empl_deptment;grant select on scott.dept to test

alter user test default tablespace ttalter user test identified by test1234alter user test account lockalter user test account unlockcreate user test identified by test1234grant connect to test;conn /as sysdbagrant select on scott.dept to test with grant option 把权限下放给testgrant all on scott.dept to test with grant optiongrant execute on scott.mypro to test with grant optiongrant create user to testgrant drop user to testconn /as sysdba grant create user to test with admin optionconn test/test1234grant create user to abcrevoke select on scott.dept from test;revoke create user from test;总结:无论是系统授权还是对象授权都可以续联选项系统授权加的是with admin option对象授权加的是with grant option 角色授权create role myrolegrant myrole to test/grant select on scott.dept to myrole概要文件实现全局设置特别是对口令的管理与设置

表空间create tablespace mytabsdatafile ‘E:/oracle/product/10.1.0/oradata/test/mytabs.dbf’ size 10M/alter user test default tablespace mytabsgrant unlimited tablespace,dba to testcreate table test(id number(10),name char(10)) tablespace tt做项目时，先创建表空间 再创建用户将用户设置所创建的表空间

表的管理表的完整性与约束实体完整性域完整性参照完整性alter table empl add constraint pk_nn primary key(eid)/alter table empl add constraint fk_empl foreign key(id) references deptment(id)/alter table deptment add constraint pk_deptment primary key(id)/insert into empl values(myseq.nextval,’张’,'女’,3)/alter table empl add constraint ck_empl_sex check(sex=’男’ or sex=’女’)/desc select constraint_name,constraint_type from user_constraintswhere table_name=’EMPL’/desc all_constraintscreate index my_dept on deptment(id)/create bitmap index bit_empl on empl(sex)

/create unique index myidx on empl(eid)/SQL*Loader的使用sqlldr

d:/loader.txtabc,xyzdef,bbb eee,ttt

d:/cont.ctlload datainfile ‘D:/loader.txt’append into table mm(m1 position(1:3)) char,m2 position(5,7) char)

create table mm(m1 varchar2(10),m2 varchar2(10))/

sqlldr scott/tiger control=d:/cont.ctl data=d:/loader.txtd:/cont.ctlload datainfile ‘D:/loader.txt’append into table mm(m1 char terminated by “,”,m2 char terminated by “,”) sqlldr scott/tiger control=d:/cont.ctl data=d:/loader.txtoem的配置sqlplus /nologconnect / as sysdbaalter user sys identified by angel918alter user system identified by angel918

/

sqlplus “/@服务名 as sysdba”然后在sqlplus中alter user sys identified by 新密码;alter user system identified by 新密码;

监听lsnrctl status lsnrctl startlsnrctl stop数据的备份exp ​​​scott/tiger@test​​​d:/mybak.dmpimp scott/tigerconnect ​​sys/test1234@test​​ as sysdbashutdown immediatestartuparchive log listalter system set log_archive_start=true scope=spfile/shutdown immediatestartup mountalter database archivelog alter database open;alter tablespace test begin backup/考备表空间到指定目录alter tablespace test end backup

/alter system archive log current/alter system switch logfile/alter system switch logfile/select * from v$recover_file/alter database datafile 6 offline drop/表空间到指定目录select * from v$recover_file/autoalter database datafile 6 online;备份控制文件alter database backup controlfile to trace/shutdown immediate@c:/create_ctl.txt直接考备控制文件ho clsrevover database until concelalter database open resetlogs

开发人员create user abc identified by abc;grant connect,resource to abc;select table_name from user_tables;DDL create drop alter trancateDML select update insert deleteDCL grant revokeshow usercreate sequence myseq increment by 1 start with 1;select myseq.currentval from dualselect myseq.nextval from dual总结：oracle体系结构实列和数据库sql*pluspl/sql安全管理表空间、表的完整性备份与恢复

===============================================================

Inthirties关注Oracle数据库 维护，优化，安全，备份，恢复，迁移，故障处理

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。