一．Window下关闭Oracle 11g
正常在Windows下安全关闭Oracle数据库系统的步骤如下： 1.从命令行下到oracle的BIN目录，然后输入isqlplusctl stop命令停止isqlplus进程。 2.同样在BIN目录下，emctl stop dbconsole停止企业管理器的控制台进程。 3.同样在BIN目录下，lsnrctl stop停止监听进程。 4.在命令行下输入sqlplus /nolog , 然后conn / as sysdba连接到数据库shutdown immediate; ====================================================================== select username,password from dba_users; --查看用户信息 select username,account_status from dba_users; --查看用户是否解锁 alter user scott account unlock; --解锁scott用户 alter user scott account lock;--锁scott用户 alter user scott identified by tiger; --为scott用户修改密码为tiger 创建用户以及给表赋予权限： 创建用户create user user_name identified by password; grant dba to user_name； 查询表的权限grant select on gecs_law to user_name; select file_name from dba_data_files; --查看数据文件路径 select name from v$controlfile; --查看控制文件路径 select member from v$logfile; --查看日志文件路径 ====================================================================== Oracle关于shutdown、startup几个参数： shutdown有四个参数：normal、transactional、immediate、abort。缺省不带任何参数时表示是normal； shutdown normal：不允许新的连接、等待会话结束、等待事务结束、做一个检查点并关闭数据文件，启动时不需要实例恢复。 shutdown transactional：不允许新的连接、不等待会话结束、等待事务结束、做一个检查点并关闭数据文件，启动时不需要实例恢复。 shutdown immediate：不允许新的连接、不等待会话结束、不等待事务结束、做一个检查点并关闭数据文件。没有结束的事务是自动rollback的，启动时不需要实例恢复。 shutdown abort：不允许新的连接、不等待会话结束、不等待事务结束、不做检查点且没有关闭数据文件。启动时自动进行实例恢复。 对于normal、transactional、immediate关闭数据库时Cache的内容写入了数据文件，没有提交的事务被回滚，所有的资源被释放，数据库被“干净”的关闭。 对于abort关闭数据库时Cache的内容没有写入数据文件，没有提交的事务也没有回滚。数据库没有dismount和关闭，数据文件也没有关闭。当数据库启动时，需要通过redo log恢复数据，通过回滚段对事务回滚，对资源进行释放。 startup [force] [restrict] [pfile= filename] [open [recover][ database] | mount | nomount] startup open：startup缺省的参数就是open，打开数据库，允许数据库的访问。当前实例的控制文件中所描述的所有文件都已经打开。 startup mount：mount数据库，仅仅给DBA进行管理操作，不允许数据库的用户访问。仅仅只是当前实例的控制文件被打开，数据文件未打开。 这种模式时要经过2个步骤启动数据库：1.startup mount；2.alter database open； startup nomount：仅仅通过初始化文件，分配出SGA区，启动数据库后台进程，没有打开控制文件和数据文件。不能任何访问数据库。 这种模式时要经过3个步骤启动数据库：1.startup nomount；2.alter database mount；3.alter database open； startup pfile= filename：以filename为初始化文件启动数据库，不是采用缺省初始化文件。 startup force：中止当前数据库的运行，并开始重新正常的启动数据库。 startup restrict：只允许具有restricted session权限的用户访问数据库。 startup open recover：数据库启动，并开始介质恢复。 ====================================================================== cd D:\u01\app\oracle\product\11.2.0\dbhome_1\inventory\Templates\BIN isqlplusctl stop 停止isqlplus进程 emctl stop dbconsole 停止企业管理器进程 lsnrctl stop 停止监听 sqlplus / as sysdba shutdown immediate exit shutdown -s -t 0 二. 数据字典 安装数据库后默认的表空间有：system,sysaux,temp,undotbsl,user，example select tablespace_name from dba_tablespaces; 数据字典： 简单来说user_自己的，all_可以被自己看到的，dba_全数据库所有的 dba_tables --所有用户的表信息、all_tables--当前用户能看到的表信息、user_tables--只有当前用户下表的信息。 Oracle数据库里常用的数据字典视图，一般分为这三种。 其中，user_打头的数据字典视图指的是当前用户下的内容。 例如：user_users查询结果就只有当前用户自己的信息，user_tables只有当前用户下的表的信息。 all_打头的数据字典视图指的是：所有可以被当前用户看到的内容。 例如all_users可以查看到所有用户的基本信息。而all_tables可以查看到所有其他用户对你目前使用的用户赋过权的表以及你自己的表的相关信息。 dba_打头的视图，通常包含数据库中所有的信息。 例如dba_users可以看到全部用户，而dba_tables可以看到全部表，无所谓别人给不给授权。 当然，有一些内容dba_视图也不包含，例如dba_db_links不如user_db_links详细。但这类情况很少。 dba_tab_columns 所有用户所建立的表以及对应的表字段； all_tab_columns 当前用户与赋予权限的用户的表以及对应的表字段； 与数据库组件相关的数据字典 select * from v$datafile --记录数据库系统的运行情况 dba_spacetables --记录表空间基本信息 三、针对表与缓冲区的一些查看命令 Sqlplus /nolog连接到数据库 Conn / as sysdba 查看当前用户所建的表中列的定义，命令为：SQL>desc table_name，例如： 就是查看COM$表下所有列的定义。 SQL> clear scr --清屏幕 SQL> show user --显示当前用户 desc emp --查看表结构 下边是针对缓冲区的一些操作命令： SQL> cl --清除缓冲区所有行 SQL> i text --插入指定text文本 SQL> c /old/new --将缓冲区中的old字符串改为new字符串 SQL> del 2，4 --删除缓冲区中2到4行内容 SQL> l 3 --显示第三行内容 SQL> sav aa cre --创建aa文件并把缓冲区内容保存到该文件； SQL> sav aa app --如果aa存在，把缓冲区内容追加到aa文件，如果aa不存在新建aa文件，并把缓存区内容加到aa； SQL> sav aa rep --如果存在则清除aa文件，把缓冲区内容写入aa，如果aa不存在就创建aa，并把缓存区内容加到aa； SQL> save aa replace --如果aa存在的话，则替换掉，重新建立aa SQL> get aa list --打开文件并列出缓冲区内容 SQL> get aa nolist --打开文件但不列出缓冲区内容 start aa --运行文件 spo aa cre --将输出结果也保存下来 spo aa app --追加 spo aa rep --替换 spo out --开启spool命令 spo off --关闭spool命令 该部分主要作用是：在sqlplus>SQL>下直接运行已经存在的脚本。 ====================================================================== 四、 Oracle数据类型与临时变量 常用的数据类型有： Varchar2（n） Char(n) Number(p,s)：定义固定长度的整数和浮点数； p表示精度，用来指定数字的总位数； S表示标度，用于指定小数点后的数字位数； Date：定义日期和数据 Boolean：定义布尔变量，值为：true，false，null 表列不能采用该数据类型； Int：整型；float：单精度浮点数；double：双精度浮点数； Timestamp定义时间和日期数据，当显示timestamp变量数据时，不仅会显示日期，还会显示上午或下午的标记； 定义变量类型： set verify off --关闭原变量值 set verify on --开启原变量值 两者的区别：当set ver on时，会显示原值和新值 当set ver off时，不会显示原值和新值 如图： identifier语法： Identifier 变量名称 datatype 数据类型 [:=default expr] Default是默认值 expr是初始值 Identifier赋值语法： Identifier 变量名:=值 Accept语句语法： Accept 变量名 数据类型 prompt ‘提示内容：’ hide Prompt命令用于输出提示用户的信息，hide用于隐藏用户的输入； 如：accept a char prompt ‘输入时间：’ hide Accept a char prompt ‘input a:’ hide define语法： define 变量名=值 如define test=5 define --查看所有已定义变量 define 变量名 --显示指定变量的名称、值、数据类型 清除变量语法： undefine 变量名 替换变量创建通用脚本： 替换变量的格式是在变量名称前加一个&，以便在运行sql时提示用户输入代替值，然后按照输入值来运行sql命令，语法如下： （1）.&变量名 如&name 使用范围：where、order by、列表达式、表名、整个select语句中 （2）.&&变量名 如&&name select * from emp where empno>=&test; --使用已定义变量 select empno,job,sal,deptno from emp where empno> &temp; select &&column_name,job,sal from emp where &&column_name>7850; ====================================================================== 五、 表空间操作 创建表空间：基本表空间，临时表空间，大文件表空间，非标准数据块表空间，撤销表空间 格式：create temporary（临时表空间）, unido（基本表空间） tablespace tablespace_name Datafile ‘……….路径’ size ‘..M’； 例子：create tablespace myspace datafile 'd:\myspace.dbf' size 10M autoextend on next 5M maxsize 100M; --创建表空间myspace存放数据文件d:\myspace.dbf，大小为10M ，自动扩展为5M，最大为100M alter tablespace myspace online --设置表空间在线状态 alter tablespace myspace offline --设置表空间为离线状态，可以进行数据库备份和数据库升级和维护等操作 alter tablespace myspace offline normal --设置表空间以正常方式进入离线状态，并设置检查点 alter tablespace myspace offline tempprorary --设置表空间以临时方式进入离线状态，不设置检查点 alter tablespace myspace offline immediate --设置表空间立即进入离线状态，不设置检查点 alter tablespace myspace offline for recover --设置表空间恢复方式进入离线状态 alter tablespace myspace read only --设置表空间为只读状态（只能查看） alter tablespace myspace read write --设置表空间为正常状态（可进行增，删改，查操作） select tablespace_name,status from dba_tablespaces --查看表空间状态 增加表空间大小 alter database datafile 'd:\myspace.dbf' resize 20M; --修改数据文件大小来增加表空间大小 alter tablespace myspace add datafile 'd:\myspace_new.dbf' size 10M autoextend on next 5M maxsize 100M; --增加新的数据文件来增加表空间的大小 select tablespace_name,bytes from dba_data_files; --查看表空间数据文件大小 修改表空间中数据文件的自动扩展性 alter database datafile ‘myspace’ autoextend off --关闭自动扩展性 alter database datafile ‘myspace’ autoextend on next 5M maxsize 100M; --开启自动扩展性并设增量5M最大为100M 数据文件的状态：online ，offline ，offline drop alter database datafile 'd:\myspace.dbf' autoextend on next 5M maxsize 100M; alter database datafile 'd:\myspace.bdf' offline; 移动表空间中的数据文件 select tablespace_name,file_name from dba_data_files where tablespace_name='myspace'; --查看表空间 alter tablespace myspace rename datafile 'd:\myspace_new.dbf' to 'f:\myspace02.dbf'; --移动数据文件路径 重命名表空间 alter tablespace myspace offline; alter tablespace myspace rename to myspace_new; 删除表空间 drop tablespace myspace incluing contents and datafiles; --删除表空间连视图等对象一起删除并将数据文件一起删除 drop tablespace myspace and datafiles; --删除表空间并连数据文件一起删除 创建和修改临时（temporary）表空间 临时表空间组 create temporary tablespace tempgroup tempfile 'd:\tempgroup.dbf' size 5M tablespace group group01; --创建临时表空间tempgroup，并放入tempgroup01 表空间组 select * from dba_tablespace_groups; --查看表空间组 create temporary tablespace tempgroup01 tempfile 'd:\tempgroup01.dbf' size 5M tablespace group group01; --创建表空间tempgroup01，并放入tempgroup01 表空间组 alter tablespace tempgroup tablespace group group2; --把tempgroup表空间移到group2表空间组里 大文件表空间 create bigfile tablespace bigspace datafile 'd:\bigspace.dbf' size 10M; --创建大文件表空间 select tablespace_name,bigfile from dba_tablespaces; --查看大文件表空间 select property_name,property_val ,description from database_properties where propty_name='DEFAULT_TBS_TYPE'; --查看表空间类型表 非标准数据块表空间 blocksize --指定数据块大小 db_nk_cache_size --缓冲区大小 2k db_2k_cache_size 4k db_4k_cache_size 8k db_8k_cache_size 16k db_16k_cache_size 32k db_32k_cache_size select tablespace_name,block_size from dba_tablespaces; --查看表空间块的大小 alter bigspace set db_16k_cache_size=16M; --修改表空间块的大小 create tablespace blockspace datafile 'd:\blockspace.dbf' size 10M blocksize 16k; --创建表空间blockspace,块大小为16k 撤销表空间 undo_tablespace --撤销表空间的文件 alter system set undo_tablespace=undotbs02; --切换撤销表空间语法 alter system set undo_retention=600; --修改撤销表空间保留时间为10分钟，默认为900（15分钟） show parameter undo; --查看undo信息 drop tablespace undotbs02 incl ing contents and datafiles; --删除撤销表空间及文件 创建撤销表空间 create undo tablespace undotbs datafile 'd:\undotbs.dbf' size 20M autoextend on; --创建撤销表空间 alter tablspace updotbs add datafile 'd:\undo02.dbf' size 10M; --增加撤销数据文件 alter database datafile 'd:\undo02.dbf' resize 15M; --修改撤销表空间数据文件的大小 alter tablespace undotbs offline; --修改撤销表空间状态 alter tablespace undotbs add datafile 'd:\undo02.dbf' size 10M; alter database datafile 'd:\undo02.dbf' resize 15M; alter tablespace undotbs offline; 设置默认表空间 alter database default temporary tablespace myspace; --修改默认的临时表空间为myspace alter database default tablespace myspace； --修改默认的普通表空间为myspace select default_tablespace from user_users; --查询当前用户的默认表空间 select property_name,property_val from database_properties where property_name in('default_permanent_tablespace','default_temp_tablespace'); --查询默认表空间 create tablespace testspace datafile 'd:\testspace.dbf' size 10M; --创建默认表空间 alter database default tablespace testspace; alter database default temporary tablespace mytemp; ============================================================================ 创建日志文件 select group#,member from v$logfile; --查看已有的日志文件组 alter database myspace add logfile group 1 mylog size 10M reuse; --创建日志文件组 alter database add logfile group 4 ('d:\redo03.log', 'd:\redo04.log' ) size 10M; --创建日志文件组并指定两个文件 alter database add logfile member 'd:\redo05.log' to group 4; --创建日志文件 重新定义日志成员 select group#,member from v$logfile; connect sys/passwd as sysdba shutdown startup mount alter database rename file 'f:\redo03.log' to 'f:\redo07.log'; --将原来的redo03.log 更改为redo07.log文件 alter database open; 切换日志文件组 alter system switch logfile; --切换日志文件组 清空日志文件组 select group#,member,status from v$logfile; --inacti (非活动状态)，acdtive(活动状态) alter database clear logfile group 4; --清空4号日志文件组 删除日志文件 alter database drop logfile member redo07.log; --删除日志文件redo.log 日志文件组状态有：active（活动状态），current（当前数据库正使用这个日志文件组），inactive（非活动状态） 日志文件状态：valid（可以使用的），invalid（不可以使用的），stale（产生了作物） alter database drop logfile group 4; --删除4号日志文件组 alter database drop logfile member 'd:\redo05.log'; --删除日志文件 管理归档日志 oracle日志模式：非归档日志模式（noarvhivelog），归档日志模式（archivelog） alter database archivelog; --设置为归档模式 alter database noarchivelog; --设置为非归档模式 archive log list; --显示日志归档模式 shutdown startup mount alter database archivelog; alter database open; --打开数据库为非归档模式 connect sys/passwd as sysdba archive log list; --显示日志归档模式 设置归档目标 db_recovery_file_dest --查看归档目标 alter system set log_archive_dest_N='location=d:\redo08.log'; --设置为当地 alter system set log_archive_dest_N='server=d:\redo08.log'; --设置为sever 设置归档日志名称 alter system set log_archive_format='fix_name%S_%R.%T' scope= ; fix_name:自定义命名前缀 %S:日志序列号 %R:联机重做日志 %T:归档线程编号 scope:memory(只改变当前实例运行参数),spfile(改变服务器参数文件),both(两者都改变) show parameter db_recovery_file_dest; --查看默认归档路径 alter system set log_archive_dest_1='location=d:myspace'; --把归档路径修改为本地的d:myspace路 径 show parameter log_archive_dest_1; --查看默认归档路径 alter system set log_archive_format='myachive%S_%R.%T' scope=spfile; --修改归档名称 =========================================================================== 使用sql语句创建表 数据类型 char --固定长度字符数据 varchar2 --可变长度字符数据 nchar --固定长度的unicode字符数据 number --可变长度数字 numberric --和number一样 int,integer,smallint --number的子类型 date times**p --时间戳 clob 创建表 create table mytable ( id number(4), name varchar2(8), ** char(2), birthday date ) ; --创建表 指定使用哪个表空间 select table_name,tablespace_name from user_tables; --查看表放在哪个表空间 select table_name,tablespace_name from user_tables where table_name='MYTABLE'; --查看mytable表放在哪个表空间 create table mytable2( id number(4), name varchar2(8), ** char(2), age number(3)) tablespace testspace; --创建的表放到testspace表空间里 创建和管理撤销表空间 create undo tablespace undotbs datafile 'd:\undo01.bdf' size 20M autoextend on; 管理表中的列 alter table mytable add leng number(3); --向mytable表增加id字段，数据类型为number(3) alter table mytable rename column leng to hight; --修改表的字段名称 alter table mytable modify leng varchar2(15); --修改字段类型 alter table mytable drop column leng; --删除字段 alter table mytable drop(leng,name); --删除多个字段 foreign key约束 添加外键约束 crete table mytable( id number(3), name char(25), dptno number(3) references emp(deptno)); -- crete table mytable( id number(3), name char(25), dptno number(3) constraint myt_dptno_fk foreign key(dptno) references emp(deptno)); --具体创建方法 修改表 alter table mytable rename to mytable_new; --重命名表名 rename mytable to mytable_new; --重命名表名 alter table mytable move tablespace myspace; --移动表到指定表空间 drop table mytable cascade constraints; --删除表同时删除这个表的索引，视图，约束，触发器 drop table mytable cascade purge; --删除表后立即释放该表所占用的资源空间 select table_name,tablespace_name from user_tables where table_name='PERSON'; --查看PERSON表属于哪个表空间 约束分类和查询 表级约束 列级约束 Not null primary key uniq check foreign key user_constraints --查询约束表获得约束信息 select constraint_name,constraint_type from user_constraints where table_name='p'; --查看emp表的约束信息 user_cons_columns --查看列约束的信息 select table_name,column_name,constraint_name from user_cons_columns where table_name='p'; select constraint_name,constraint_type from user_constraints where table_name='T'; --显示约束名称 alter table mytable modify leng constraint emp_name_nk not null; --创建非空约束 alter table mytable modify leng null; --删除列的not null 约束 create table person( pid number(4) not null, pname varchar2(20), p** char(2)); alter table person modify pname not null; --修改pname列为not null约束 alter table person modify pname null; --修改pname列为null约束 primary key主键约束 create table mytable( name char(15) constraint myt_name_pk primary key, id number(8), constraint myt_id_pk primary key(id)); --创建主键约束 alter table mytable add constraint myt_leng_pk primary key(leng); --添加主键约束 alter table mytable drop constraint myt_leng_pk; --删除主键约束 此生我一搏，誓死做英豪！！ - - - - 肖春座右铭 指定级联操作类型 cascade --主表删除，子表相应记录也同时删除 set null no action create table st lass( cid number(4) primary key, cname varchar2(40), num number(4)); create table stu( sid number(4), scode varchar2(10), sname varchar2(20), cid number(4) references st lass(cid) on delete set null); --设置删除主表段时同时把子表相应的记录修改为空 create table stu( sid number(4), scode varchar2(10), sname varchar2(20), cid number(4) references st lass(cid) on delete cascade); --设 置删除主表段时同时也删除子表相应的记录 ============================================================================ SQL语言 数据操纵语言（Data Manipulation Lang ge ， 简称DML） select insert update delete call merge commit rollback 数据定义语言（Data Definition Lang ge ，简称DDL） create alter drop rename truncate 数据控制语言（Data Control Lang ge ，简称DCl） grant --授权 revoke --收回授权 between操作符 select * p where empno between 7800 and 7900; in操作符 select * p where empno in(7788,7800,7900); lower --大写转换成小写 upper --小写转换成大写 set echo off set verify off insert into mytable val s('',.... ); set echo on set verify on undefine p_first_name undefine p_last_name like操作符 like %\%% --包含有%号的内容 select * p where ename like '%E_'; select * p where ename like '%\%% escape '\'; --把\转换成普通\ order by子语句 group by 子语句 select deptno,count(*),avg(sal),max(sal),min(sal) p group by deptno order by deptno desc; update 语句适合五一发的朋友圈 p set deptno=deptno+1; p set deptno=deptno+1 where empno=11; having子句 select deptno,count(*) p group by deptno having count(*)>5; merge 语句 meger into mytable1 using mytable2 on name='jj' where matched then update set --如果符合条件就执行这个语句 where not mached then insert --如果不符合条件就执行这个语句 create table person( pid number(4), page number(3)); insert into person valuse(1,20); insert into person valuse(2,21); insert into person valuse(3,22); create table newperson( pid number(4), page number(3)); insert into newperson val s(1,100); insert into newperson val s(4,100); insert into newperson val s(5,100); merge into person p1 using newperson p2 on(p1.pid=p2.pid) when matched then --当p1.pid=p2.pid update set p1.page=p2.page --执行update set p1.page=p2.page when not mached then --当p1.pid<>p2.pid insert(pid,page) val s(p2.pid,p2.page); --执行 简单连接 ame||'的编号是：',e1.empno||',上级是编号是:',e2.mgr||',上级名称是:',e1.ename p p e2 (+) pno=7839; join连接表 ame,e.pno,d.loc p e,scott.dept d where e.deptno=d.deptno; ame,e.pno,d.loc p e inner join scott.dept d on(e.deptno=d.deptn) where ename='ALLEN'; -- 不等连接 select empno,ename,sal,grade p e inner join scott.salgrade s on e.sal between s.losal and s.hisal; --不等连接 自然连接 ame,esal,deptno,d.dname p e natural join scott.dept d where d.dname='ALLEN'; using关键字 select empno,ename,sal,deptno,dname p e inner join scott.dept d using(deptno); ame,e.sal,deptno,d.dname p e inner join scott.dept d using(deptno); 交叉连接 事务处理 特性：原子性，一致性，隔离性，持久性 commit rollbacke savepoint delete_savep roolback to savepoint delete_savep ======================================================================================= 字符函数 ascii('a') ---->97 --返回十进制值 chr(97) ---->a --返回数字对应的ascii码 concate('abc','def') ---->abcdef --连接字符串 initcap('abcd') ---->Abcd --第一个字母变大写 instr('abcdef','cd') ---->3 --查cd在字符串的位置 lower('ABCD') ----->abcd --大写变小写 upper('abcd') ---->ABCD --小写变大写 lpad('abcd',2,'@') ----> rpad('abcd',2,'@') -----> ltrim(' aabcd') ---->aabcd --删除左边的空格 rtrim('aabcd ') ----->abcd --删除右边的空格 replace('abcd','abc','efg') ----->efgd --替换字符 s str('abcdefg',2,3) ---->bcd --截取固定长度字符 length('abcdef') ---->6 --查看字符长度 数字函数 abs(-12) ---->12 --绝对值 ceil(38) ---->38 --返回大于或等于38的最小整数值 floor(38) ---->38 --返回小于或等于38的最大整数值 sin(12) ---->0.850903525 --正弦 cos(38) ---->0.52532** --余弦 exp(2) ----> 7.3890561 --返回以e为低的指数值 ln(2) ---->0.6393147181 --返回自然对数 log(12) ----> --返回以10为低的对数 power(3,2) ---->0.630929754 --返回指数值 round(123.456,2) ---->123.46 --四舍五入 mod(38,10) ---->8 --求余数 sqrt(3) ---->9 --返回平方根 trunc(123.456,2) ---->123.45 --截断 trunc(123.456,-1) ----->120 trunc(123.456,-2) ----->100 trunc(123.456,-3) ----->0 create table numbers ( number_u number, numeric_u numeric, decimal_u decimal, integer_u integer, smallint_u smallint, number_p number(9), numeric_p numeric(9), decimal_p decimal(9), number_ps number(9,2), numeric_ps numeric(9,2), decimal_ps decimal(9,2), number_s number(*,2), numeric_s numeric(*,2), decimal_s decimal(*,2), float_u float, float_p float(30), real_u real, do le_u do le precision); 聚合函数 avg(x) sum(x) max(x) min(x) count(x) media(x) 日期函数 sysdate() current_times**p() add_months(date,count) last_day(date) months_between(datel,date2) new_time(date,'this','other') next_day(date,'day') gteatest(date1,date2,..) select sysdate from d l; select current_times**p from d l; select months_between(sysdate,'08-8月 -2008') from d l select sysdate-to_date('08-8月 -2008') from d l; 转换函数 cast(val as type) convert(val ,source_char_set,dest_char_set) decode(val ,search,result,default) bin_to_num(val ) to_times**p(val ) to_date(string,'format') to_char(val [,format]) to_num(val [,format]) 日期时间格式化参数 cc 世纪 scc 公元前 q 季度 yyyy 年份 yy year 年份的全拼 mm 月份 month 月份的全拼 mon 月份的前三个字母 ww 周 ddd 第几天 dd d day 周几的全拼 dy 周几的前三个字母 hh24 24格式小时 hh 12 小时格式 mi 分 ss 秒 ms 毫秒 am 上午 pm 下午 正则表达式 ^200[0-9]$ ============================================================================= 子查询 单行子查询：=,>,>=,<=,<>,!= 多行子查询 ：all,any,in,exists,not exists from子句使用子查询 select deptno from(select deptno p group by deptno order by count(deptno) asc); select empno from(select empno p group by empno,deptno ); having子句使用子查询 select deptno,avg(sal) p group by deptno having avg(sal) <(select avg(sal) p); any操作符 select empno,ename,sal,deptno p where sal >any(select avg(sal) p group by deptno); select empno,ename,sal,deptno p where sal>all( select avg(sal) p group by deptno); 多列子查询 select * from emp where (dptno,ename) in(select deptno,ename from emp where deptno=20); 关联子查询 select empno,ename,sal,deptno p outer where sal<(select avg(sal) p inner where inner.deptno=outer.deptno); select empno,ename,sal,hiredate p outer where exists( select 1 p inner pno and s str(hiredate,8,2)='80'); select empno,ename,sal,hiredate p outer where not exists( select 1 p inner pno and s str(hiredate,8,2)='80'); 嵌套子查询 select empno,ename,sal,deptno p where sal>( select max(avg(sal)) p where deptno in( select deptno from scott.dept where loc in('NEW YORK','CHICAGO')) group by deptno); ========================================================================================== 集合操作 union union all intersect minus select * from emp union select * from emp1; --获得两个表的所有不重复的行 select * from emp union all select * from emp1; --显示重复的行 select * from emp intersect select * from emp1; --获得两个表相同的行 select * from emp minus select * from emp1; --获得emp表的所有行再减去上两个表的差集 层次化查询 select empno,ename,mgr p start with ename='KING' connect by prior empno=mgr; 格式化查询 伪列 select level,empno,ename,mgr p start with ename='KING' connect by prior empno=mgr order by level asc; --显示在树层结构的第几层 select level,lpad(' ',4*level-1)||ename p start with ename='KING' connect by prior empno=mgr order by level asc; --添加空格 遍历查询 select level,lpad(' ',4*level-1)||ename from emp start with ename='JONES' connect by prior empno=mgr; 从JONES开始查询 select level,lpad(' ',4*level-1)||ename from emp start with ename='JONES' connect by prior mgr=empno; --从下向上 删除节点 select level,lpad(' ',4*level-1)||ename from emp where ename!='JONES' start with ename='KING' connect by prior empno=mgr; --删除JOINES节点 扩展的group by 子句 select d.dname,sum(e.sal) from emp e,dept d where e.deptno=d.deptno group by rollup(d.dname) order by d.dname; --获得总和 select d.dname,sum(e.sal) from emp e,dept d where e.deptno=d.deptno group by rollup(d.dname,e.job); select d.dname,sum(e.sal) from emp e,dept d where e.deptno=d.deptno group by c e(d.dname,e.job) order bye e.job,d.name; ===================================================================== PL/SQL基本语法 =============================================================================== 创建用户 create user user1 identified by user1 default tablespace users --使用users表空间为默认表空间 temporaray tablespace temp --指定临时表空间为temp quota 20m on users password expire --登录就要修改密码 accout unlock; --不锁定 修改用户 alter user user1 identified by user1; grant connect to user1 identified by; --通过授权修改用户口令 alter user user1 default tablespace temp01; --修改用户默认表空间 alter user user1 account lock; --锁定用户 alter user user1 account unlock; --解锁用户 资源**参数 session_per_user **用户连接数量 cpu_per_session **用户在一次数据库会话时间可实用的cpu时间 cpu_per_call **每条sql语句所能使用的cpu时间 logical_reads_per_session **每个会话所能读取的数据库数量 logical_reads_per_call **每条sql语句所能读取的数据块数 private_sga 在共享模式下**一个会话可以使用的sga区的大小 connect_time **每个用户能够连接到数据库的最长时间 idle_time 指定用户在数据库被禁止之前，可以让连接处于多长的空闲状态 composite_limit dba_profiles --资源**参数数据字典视图 select * from dba_profiles where profile='DEFAULT' and resource_type='KERNEL'; -- 查看 口令**参数 用户锁定 口令的过期 口令的强度 failed_login_attempts **连续失败的次数 password_life_time **用户的有效期 password_reuse_time **用户口令失效前，重新设置口令的天数 password_reuse_max **口令在下次被重新使用前，期间必须经历的口令改变次数 password_lock_time 设置连续登陆错误用户被锁定 password_grace_time 设置宽限时间 password_verify_function 设置用于判断口令的复杂性函数 dba_profiles select * from dba_profiles where profile='DEFAULT' and resource_type='PASSWORD'; 创建用户配置文件 create profile myprofile limit session_per_user 10 failed_login_attempts 3; 创建复杂的用户配置文件 1.允许3此输入错误 2.每隔15天修改一次登录口令 3.在100天后才允许重复使用同一个口令 4.用户最多能够建立10个数据库会话 5.保持15分钟的空闲状态后，会话自动断开 6.每个会话持续连接到数据库的最长时间为24小时 7.会话中每条sql语句最多占用50个单位的cpu时间 8.使用口令复杂度验证函数verify_function_11g对用户口令进行验证 1.到G:\oracle\prod t\10.2.0\db_1\RDBMS\ADMIN\utlpwdmg.sql文件 2.运行 @G:\oracle\prod t\10.2.0\db_1\RDBMS\ADMIN\utlpwdmg.sql 3. create profile profile1 limit failed_login_attempts 3 password_life_time 15 password_lock_time 100 sessions_per_user 10 idle_time 15 connect_time 1440 cpu_per_call 50 password_verify_function verify_function; 11g为 verify_function_11g 4.alter system set resource_limit = tr ; --设置系统参数起作用 5.alter user user1 profile profile1; --指定给用户 查看配置文件信息 select resource_name,resource_type,limit from dba_profiles where profile='PROFILE1'; 修改配置文件内容 alter profile profile1 limit password_lock_time 150 session_per_user 5 idle_time 30; 删除配置文件 drop profile profile1 cascade; 权限的概述 系统权限 对象权限 系统权限 create session create tablespace alter tablespace drop tablespace create user alter user drop user create table create any table drop any table alter any table select any table insert any table update any table delete any table creat view create any view drop any view create role alter any role grant any role alter database create procedure create any procedure alter any procedure drop any rpocedeure create profile alter profile drop profile grant alter table to user1 with admin option; select username,privilege,admin_option from user_sys_privs; --查看当前用户被授予的系统权限 对象权限 alter delete execute index insert read reference select update grant select p to user1 with grant option; 查看和撤销权限 dba_col_privs --包含数据中所有授予表列上的对象权限信息 all_col_privs_made --包含当前用户作为对象权限的授予者，对所有列上的对象权限信息 all_col_privs_recd --包含当前用户作为对象权限的接收者，在所有列上的对象权限 dba_tab_privs --包含数据库所有对象权限信息 dba_sys_privs --包含数据库所有系统权限信息 session_privs --包含当前数据库用户可以使用的权限信息 创建角 create role manager; grant create table to manage; grant select p to manger; grant manager,session to user1; 预定义角（系统自带角） connect --普通用户权限 resource --给开发人有所授予的权限 dba --拥有系统所有权限 exp_full_database --数据库逻辑备份是的数据导出权限 imp_full_database -- delete_catalog_role execute_catalog_role select_catalog_role 查看角信息 dba_roles --记录数据库中所有的角 dba_roles_privs --记录所有已经被授予用户和角的角 立春祝福语简短user_roles --包含已经授予当前用户的角信息 role_role_privs --包含角授予角信息 role_sys_privs --包含角授予系统权限信息 role_tab_privs --包含角授予对象权限信息 session_roles --包含当前会话所包含的角信息 修改角 alter role role1 not identified; alter role role1 not identified by passwd; 删除角 drop role role1; ========================================================================================= 备份与还原 要备份的数据 参数文件 G:\oracle\prod t\10.2.0\db_1\dbs\SPFILEAPP.ORA G:\oracle\prod t\10.2.0\db_1\a 控制文件 G:\ORACLE\PROD T\10.2.0\ORADATA\APP\CONTROL01.CTL G:\ORACLE\PROD T\10.2.0\ORADATA\APP\CONTROL02.CTL G:\ORACLE\PROD T\10.2.0\ORADATA\APP\CONTROL03.CTL 数据文件 G:\ORACLE\PROD T\10.2.0\ORADATA\APP\SYSTEM01.DBF G:\ORACLE\PROD T\10.2.0\ORADATA\APP\UNDOTBS01.DBF G:\ORACLE\PROD T\10.2.0\ORADATA\APP\SYSAUX01.DBF G:\ORACLE\PROD T\10.2.0\ORADATA\APP\USERS01.DBF 日志文件 G:\ORACLE\PROD T\10.2.0\ORADATA\APP\REDO03.LOG G:\ORACLE\PROD T\10.2.0\ORADATA\APP\REDO02.LOG G:\ORACLE\PROD T\10.2.0\ORADATA\APP\REDO01.LOG ========================================================================= 数据库审计 审计（A it）是指对用户所执行的数据库活动做跟踪记录，它是数据库管理安全性的重要部分。 审计的目的 1.审查可以活动 2.监视和收集关于指定数据活动的数据 审计类型 1.语句审计 对某种类型的sql语句进行审计，不指定结构或者对象。 a it create table 2.权限审计 对执行相应动作的系统权限进行审计。 3.对象审计 对一个特殊模式对象上的DML语句进行审计。 a it select p 启动数据库审计 a it_trail参数 db/tr 启用审计（默认） os db_extended xml extended none/false 禁用审计 show parameter a it_trail; --查看审计状态 alter system set a it_trail=DB scope=spfile; --开启数据库审计 @G:\oracle\prod t\10.2.0\db_1\RDBMS\ADMIN\cata it.sql 语句审计 a it create table by scott; a it create table by scott by access; --每执行一个sql语句都要审计 a it create table by scott whenever s cessful; --只对成功的语句执行审计 a it create table by scott whenever not s cessful; --只对不成功的语句执行审计 noa it alter table by scott; --取消审计 对象审计 a it select,insert,p by access; insert p val s('','',''); noa it select,insert,delete p; 审计信息查询 a it select,insert,delete on scott.dept by access; delete sys.a $; --清空 insert into select * p; select username,times**p,owner,action_name,obj_name from dba_a it_object; --查看审计 细粒度的审计 dbms_fga包 add_policy() --添加审计策略 drop_policy() --删除审计策略 disable_policy() --禁用审计策略 enable_policy() --启用策略 begin dbms_fga.add_policy( object_schema -> 'scott', object_name -> 'dept', policy_name -> 'dept_a it', a it_column -> 'deptno', a it_condition -> 'deptno >20'; end; / ============================================================================== 索引 创建索引 B树索引 create index dept_dname_dx on dept(dname) tablespace my_tbs; --创建索引放到my_tbs表空间 create uniq index emp_ename_dx on emp(ename) tablespace my_tbs --创建唯一索引放到my_tbs表空间 create index emp_ename_empno_dx on emp(empno,ename) tablespace my_tbs --家中有蚂蚁怎么办创建复合索引 位图索引 在列的基数很低时使用 create bitmap index e**_bitmap_dx on employee(e**) tablesapce my_tbs; 反向键索引 特殊的B树索引，适用于有序列数的列上 creat index eid_reverse_dx on employee(eid) reverse tablesapce my_tbs; 基于函数的索引 create index edate_func_dx on employee(to_char(edate,'yyyy-mm-dd')) tablespace my_tbs; 管理索引 合并和重建索引 alter index ename_dx coalesce deallocate unused; --合并索引 alter index ename_dx rebuild tablespace my_tbs; --重建索引 监视索引 alter index ename_dx monitoring usage; --打开监视索引 select * from v$object_usage; --查看监视索引信息 alter index ename_dx nomonitoring usage; --关闭监视索引 删除索引 drop index emp_ename_dx; 索引组织表 create table index_table( id number primary key, name varchar2(10)) organization index tablesapce my_tbs; --创建索引组织表 临时表 事务级临时表 会话级临时表 create global temporary table user_transcation( id number, uname varchar2(10), u** varchar2(2), irthday date) on commit delete rows; --创建事务临时表 create global temporary table user_transcation( id number, uname varchar2(10), u** varchar2(2), irthday date) on commit preserve rows; --创建会话临时表 外部表 create table st ent( sid number, sname varchar2(8), sclass varchar2(3), ss ject varchar2(12), sscore number) organization external( type oracle_loader default access parameter( fields terminated by ',') location('st ent.csv')); 创建外部表 create directory external_st ent as 'd:\app' ; --创建目录 create table st ent( sid number, sname varchar2(8), sclass varchar2(3), ss ject varchar2(12), sscore number) organization external( type oracle_loader default directory external_st ent access parameters( fields terminated by ',') location('')); 分区表 为了提高巨型数据库的读写和查询速度，将数据以分区形式保存 分区是指将巨型的表或索引分隔成相对较小的，可**管理的部分。 5中分类： 1.范围分区 create table mytable( id number primary key, name varchar2(8), s ject varchar2(10), score number ) partition by range(score) ( partition part1 val s less than(60) tablespace my_tbs, --小于60存储testspace partition part2 val s less than(80) tablespace my_tbs, --大于60小于80存储testspace partition part3 val s less than(maxval ) tablespace users --大于80的存储users ); insert into mytb val s(1,'Yang','Java',90); --插入数据 insert into mytb val s(2,'Zhao','Java',80); --插入数据 insert into mytb val s(3,'Zhang','Java',54); --插入数据 insert into mytb val s(4,'Yang','Java',70); --插入数据 select * from mytb partition(part1); --查询part1部分数据 select * from mytb partition(part2); --查询part2部分数据 select * from mytb partition(part3); --查询part13部分数据 2.散列分区（通过hash算法据云分布数据的一种分区类型） create table part_book2( bid number(4), bookname varchar2(30), bookprice number(4,2), booktime date ) partition by hash(bid)( partition part1 tablespace my_tbs, partition part2 tablespace users ); insert into part_book2 val s(1,'Oracle 10g',23.5,'25-8月 -10'); insert into part_book2 val s(2,'Struts',23.8,'25-8月 -10'); insert into part_book2 val s(3,'ssh',23.5,'25-8月 -10'); insert into part_book2 val s(4,'jsf',23.5,'25-8月 -10'); select * from part_book3 partition(part1); select * from part_book3 partition(part2); 3.列表分区（列值为非数字或日期数据类型，并且分区列的取值范围较少时使用，例如：成绩科目） create table part_book3( bid number(4), bookname varchar2(30), bookpress varchar2(30), booktime date) partition by list(bookpress)( partition part1 val s('清华大学出版社') tablespace my_tbs, partition part2 val s('教育出版社') tablespace users); insert into part_book3 val s(1,'jsf','清华大学出版社','25-11月 -10'); insert into part_book3 val s(2,'Oracle 10g','清华大学出版社','25-11月 -10'); insert into part_book3 val s(3,'Sttus 2','清华大学出版社','25-11月 -10'); insert into part_book3 val s(4,'ssh','教育出版社','25-11月 -10'); select * from part_book3 partition(part1); select * from part_book3 partition(part2); 4.组合范围散列分区 5.组合范围列表分区 曾加分区 为范围分区表增加分区 create table test( id number, name varchar2(8), s ject varchar2(12), score number) partition by range(score)( partition part1 val s less than(60), partition part2 val s less than(80), partition part3 val s less than(100)); alter table test add partition part4 val s then(150); --增加分区 alter table test split partition part2 at(70) into(partition part6,partition part7); --在part2上分隔为part7，part8 alter table test split partition part3 at(90) into(partition part8, partition part9); 为散列分区增加分区 alter table part_book2 add partition part3; 为列表增加分区 alter table part_book3 add partition part3 val s(default); --除了**部分值意外的所有值都存到part3 合并分区 alter table test merge partition part6,part7 into partition part2; 删除分区 alter table test drop partition part3; 簇和簇表 簇是一组共享相同数据块的多个表组成,它将这些表的行一起存储到相同数据块中，这样可以减少查询数据所需的磁盘读取量。 create cluster test() pctused 40 pctfree 10 initrans 1 --头部块的大小 maxtrans 255 --可以并行使用某个数据块的事务处理的最大数量值 tablespace my_tbs --指定簇所属表空间 storage storage --表空间参数设置，不指定使用默认表空间参数 create cluster stu_ach(sid number) pstused 40 pctfree 10 size 1024 storage ( initial 128k next 128k minextents 2 maxextents 20 ) tablespace my_tbs; --创建簇 create table st ent( sid number, sname varchar2(28), sage number ) cluster stu_ach(sid); --创建簇表 管理簇 insert into st ent val s(1,'dfsgfd',24); insert into st ent val s(1,'dfsgfd',24) * 第 1 行出现错误: ORA-02032: 聚簇表无法在簇索引建立之前使用 create index stu_ach_idex on cluster stu_ach tablespace my_tbs; --创建索引后才能插入数据 alter cluster stu_ach pctfree 60 pctused 50; --修改簇参数 drop cluster stu_ach; --删除空的簇表 drop cluster stu_ach incl ing tables; --删除有簇表的簇 视图 视图是一个虚拟表 create or replace view my_view as select * p; create or replace view my_view(姓名,编号) as select * p; 可更新的视图 select column_name,updatable,insertable,deletable from user_updatable_columns where table_name=upper('my_view'); --查看可修改的视图字段 序列 nextval currval user_seq nces create seq nce st_seq start with 1 increment by 1 nomaxcal nocycle; alter seq nce stu_seq maxval 9999 cache 9 cycle order; --修改序列 select val from d l; --查询下一个序列值 select stu_seq.currval from d l; --查询当前序列值 同义词 可以用在表，索引，视图等 create p lic synonyw p lic_stu p; --创建同义词 drop p lic sysnonym p lic_stu; --创建同义词 ========================================================================= 简单对象类型 create type stu as object( name varchar2(20), ** varchar2(2), birthday date, note varchar2(300));/ --创建对象类型 create type st_ as object( sid number(4), st ent stu);/ --使用对象类型 create table st ents( sid number(4) not null, st ent stu); --使用对象类型创建表 带有函数的对象类型 create type st_ as object( name varchar2(20), ** varchar2(2), birthday date, note varchar2(300), member function get_age return number); / 对象体 create type body st_ as member function get_age return number as v_months number; begin select floor(months_between(sydate,birthday)/12) into v_nonths from d l; return v_nonths; end; end;/ --创建对象体 create table st ents( sid number(4), st ent st_); --使用函数对象类型 insert into st ents3 val s(1,st_('Wang','ma',to_date('19850101','yyyymmdd'),'My note')); select s.st ent.name,s.st ent.birthday,s._age() from st ents3 s; --查询 对象标识符和对象引用 select ref(s) from st ents6 s where s.st ent.name='王晓雪'; 创建对象标识符过程 1. create type myclass as object( id number(4), classname varchar2(20), stunum number(3), st ent stu);/ 2. create table class1 of myclass; insert into class1 of myclass; 3. insert into class1 val s( 1,'Oracle',30,stu('王晓雪','女',to_date('19840101','yyyymmdd'),'st ent')); 4. create table scholl( id number(4) primary key, class_ref ref my class scope is class1); create table table_name( ref_name ref type_name scope is table_type_name); ============================================================================================= 对象简介 clob create table clob_table( id number primary key, clob_column clob not null); insert into clob_table(id,clob_column) val s(1,to_clob('这是clob类型数据')); select * from clob_table; update clob_table set clob_column=to_clob('这是clob类型数据') where id=1; select * from clob_table; delete from clob_table where id=1; select * from clob_table; nclob blob 同clob bfile ============================================================================================= sql优化 Oracle处理SQL语句的步骤： 1.在共享池中搜索SQL语句是否已经存在。 2.验证SQL语句的语法是否准确 3.执行数据字典来验证表和列的定义 4.获取对象的分析锁，以便在语句的分析过程中对象的定义不会改变 5.检查用户是否具有相应的操作权限 6.确定语句的最佳执行计划 7.将语句和执行方案保存到共享的SQL区 set timing on --设置开启时间显示 Where子句有关的优化 select deptno,avg(sal) p group by deptno having deptno>10; select depno,avg(sal) p where deptno >10 group by deptno; --使用Where子句比使用having子句更好 使用<=替代< select * p where empno<7901; select * p where empno<=7900; -- 使用<=更好 使用exists 使用exists代替in select * p where deptno in(select deptno from scott.dept where scott.dept.p.deptno and loc='NEW YORK'); select * p where exists(select deptno from scott.dept where scott.dept.p.deptno and loc='NEW YORK'); 使用exists代替distinct select distinct e.deptno,d.dname p e,scott.dept d where e.deptno=d.deptno; select d.deptno,d.dname from scott.dept d where exists(select 1 p e where e.deptno=d.deptno); --使用exists好 表的连接方法 select empno,ename,deptno p where deptno=(select deptno from scott.dept where dname='ACCOUNTING'); ame,d.deptno p e inner join scott.dept d on e.deptno=d.deptno where d.dname='ACCOUNTING'; --使用一条查询语句好 from子句中表的顺序 放在from子句最后的表被作为驱动表（from表的顺序是从右到左） ame,d.deptno,d.dname from scott.dept p e; ame,d.deptno,d.dname p e,scott.dept d; --把行数较少表放到后面好 where子句的连接顺序（自右向左或自下向上） select * p e where sal>1000 and job='MANAGER' and 10 < (select count(*) p where mgr=empno); select * p e where 10 < (select count(*) p where mgr=empno) and sal>1000 and job='MANAGER'; --把过滤掉信息多的条件放到后面好 有效使用索引 索引列上所使用的操作符 避免“非”操作符的使用，例如：not、！=、<>、!<、!>、not exists、not in和not like七夕情人节祝福的美句等。非操作符会造成Oracle对表执行全表扫描。 like '%5400%' --不会使用索引 避免对唯一索引列使用null值 where后使用is null或is not null时不会使用索引 选择符合索引主列 建立符合索引原侧： 1.选择经常在where子句中使用、并且由and操作符连接的列作为符合索引列 2.选择where子句中使用频率相对较高的列排在最前面，或者根据需要为**列创建单独的索引 create index depno_sal_index p(deptno,sal); select empno,ename,sal,deptno p where deptno>10 and sal>2000; select empno,ename,sal,deptno p where sal>2000 and deptno>10; --where条件列与符合索引列相一致好 ============================================================================== 数据备份 物理备份 完全数据库脱机备份 部分数据库脱机备份 部分数据库联机备份 逻辑备份 使用Data Pump Export数据泵） 完全数据库脱机备份 select 'host copy '||member||' d:\backup' copy from v$logfile; select 'host copy '||name||' d:\backup' copy from v$controlfile; select 'host copy '||name||' d:\backup' copy from v$datafile; shutdown immediate host copy 备份数据库脱机备份 注意： 1.不能将system表空间脱机 2.模式对象跨表空间存储 select tablespace_name from dba_tablespaces; --查询表空间 select tablespace_name,file_name from dba_data_files where tablespace_name='MY_TBS'; alter tablespace my_tbs offline; --把表空间设置脱机状态 host copy d:\demo\my_tbs.dbf d:\backup alter tablespace my_tbs online; 部分数据库联机备份 在归档模式下才能使用 archive log list alter tablespace my_tbs begin backup; select a.file#,a.checkpoint_change#,b.file_name from v$datafile a,dba_data_files b where a.file#=b.file_id; --查看scn号是否一致 alter tablespace end backup; 用户管理的完全恢复机制 recover database --在mount状态下完全恢复 recover tablespace --在open状态下恢复 recover dataafile --在mount或open状态下都可以恢复 不完全恢复机制 基于时间的恢复 recover database until time 2010-08-19 using backup controlfile; 基于撤销的不完全恢复 recover database until cancel; 基于更改的恢复（最准确的恢复） recover database until change 107811; ======================================================================= rman简介 创建恢复目录 archive log list create tablespace recovery_tbs datafile 'd:\demo\recovery_tbs.dbf' size 10m autoextend on next 5m extent management local; create user rman_admin identified by admin123 default tablespace recovery_tbs; grant connect,resource,recovery_catalog_owner to rman_admin; rman connect catalog rman_admin/admin123 create catalog; 连接目标数据库 1.无恢复目录连接 <1>.使用rman taget语句 c:\>rman target/ <2>.使用rman nocatalog语句 c:\>rman nocatalog <3>.使用rman target ... nocatalog语句 c:\>rman target sys/oracle nocatalog 将目标中的控制文件存储在恢复目录中 register database; 进行目录同步 resync catalog; 有恢复目录连接 <1>.使用rman target ... catalog ... c:/>rman target sys/oracle catalog rman_admin/admin123 取消目标数据库的注册 <1>.使用unregister命令 rman>unregister database; <2>.使用存储过程 sql>connect rman_admin/admin123 select * from db where db_id=(连接目标数据库的DBID值) exec DBMS_RC AT.UNREGISTERDATABASE(1,DBID); rman命令 @ startup run set show shutdown sql advise failure backup catalog delete flashback list recover register database report restore transport tablespace validate show all --rman配置参数 rman操作目标数据库 shutdown transaction alter database archivelog alter database noarchivelog alter dataabse flashback off alter database flashback on backup命令 backup [full|incremental level [=] n](backup_type option); backup_type是备份对象 1.database --表示备份全部数据 2.tablespace --表示备份空间 3.datafile --表示备份数据文件 4.archivlog[all] --表示备份归档日志文件 5.current controlfile --表示备份控制文件 6.datafilecopy[tag] --表示使用copy命令备份的数据文件 7.controlfilecopy --表示使用copy命令备份的控制文件 8.backupset[all] --表示使用backup命令备份的所有文件 option 1.tag --指定一个标记 2.format --表示文件存储格式 3.incl e current controlfile --表示备份控制文件 4.filesperset --表示每个备份集所包含的文件 5.channel --指定备份通道 6.delete[all] input --备份结束后删除归档日志 7.maxsetsize --指定备份集的最大尺寸 8.skip[offline|readonly|inaccessible] --可选择的备份条件 format参数 %c --表示备份段的多个copy的序号 %D --以DD格式显示日期 %Y --以YYYYY格式显示年度 %n --8位长度的数据库名称，不足备份用“X”填充 %s --备份集号，此数字是控制文件中随备份集增加的一个计数器，从1开始 %T --指定年、月、日，格式为YYYYMMDD %U --指定一个便于使用的、由%u_%p_%c构成的、确保不会重复的备份文件名称，rman默认使用%U %d --指定数据库 %M --以MM格式显示月份 %F --结合数据库标识DBID、日、月、年及序列，构成唯一的自动产生的字符串名字 %p --文件备份序号，在备份集中的备份文件片编码，从1开始每次增加1 %t --指定备份集的时间戳，是一个4字节值的秒数值，%t与%s结合构成唯一的备份集名称 %u --指定备份集编码，以及备份集创建的时间构成的8个字符的文件名 %% --指定字符串%，如%%Y表示为%Y rman备份 完全备份 rman>run{ #backup the complete database --注释 allocate channel ch1 type disk; --打开一条磁盘通道ch1 backup full --完全备份 tag full_db_backup --做个标记 fromat "g:\backup\db_t%t_s%s_p%p" --指定备份文件的文件格式 (database); --执行完全备份 release channel ch1; --释放通道ch1 } rman>list backup of database --查看备份信息 备份表空间 run{ allocate channel ch1 type disk; backup tag tbs_users_read_only format "g:\backup\rman\tbs_users_t%t_s%s" (tablespace users); release channel ch1;} list backup of tablespace 'USERS'; --查看备份的表空间信息 增量备份 可以指定级别（level=0 - 4） 方式： 1.差异备份:备份上一次的同级或低级备份以来所有变化的数据块 2.累计备份：将备份上次低级备份以来所有的数据块 差异曾量备份 rman>run{ allocate channel ch1 type disk; backup incremental level 0 as compressed backupset database; release channel ch1; } 执行0级后的1级差异曾量备份 rman>backup incremental level 1 as compressed backupset database; 执行累积曾量备份 rman>backup incremental level 1 cumulative as compressed backupset database; 镜像复制 copy [full|incremental level [=] 0] input_file to location_name; report schema; --获取需要备份的文件信息 copy datafile 5 to 'g:\backup\rman\big01.dbf', datafile 6 to 'g:\backup\rman\block02.dbf'; --备份数据文件5、6 rman数据库恢复 恢复数据库有两个过程：圆通速递价格 1.修复数据库（使用restore命令）：指物理上文件的复制。 2.恢复数据库（使用recover命令）：恢复数据库主要是指数据文件的介质恢复，即为修复后的数据文件应用联机或归档重做日志，从而将修复的数据文件更新到当前时刻或指定时刻下的状态。 数据库在非归档模式下恢复 只需要执行restore，然后打开数据库。（如果使用了recover就恢复了所有数据文件） sql>shutdown immediate rman>startup mount; sql>alter database datafile 'd:\demo\users01.dbf' offline drop; sql>alter database open; rman>run{ restore database; } 数据库在归档模式下恢复 rman>run{ allocate channel ch1 type disk; restore database; sql "alter database mount"; recover database; sql "alter database open resetlogs"; release channel ch1; win7远程桌面连接} ============================================================================= 数据的加载和传输 Data Pump数据导入导出工具 exp和imp expdp和impdp 数据泵功能如下： 1.在导出导入作业中，能够控制用于此作业的并行线程的数量 2.支持在网络上进行导出或导入，而不需要使用转储文件集 3.如果失败或停止，能够重启一个Data Pump作业。并且能挂起和恢复导出和导入作业 4.通过一个客户端程序能够连接或脱一个运行的作业 5.空间估算能力，而不需要实际执行导出 6.可以指定导出或导入对象的数据库版本。允许导出和导入对象进版本控制，以便与低版本的数据库兼容 与数据泵相关的数据字典 DBA_DATAPUMP_JOBS --显示运行数据泵作业的信息，也可以使用USER_DATAPUMP_JOBS变量 DBA_DATAPUMP_SESSIONS --提供数据泵作业会话级的信息 DATAPUMP_PATHS --提供一系列有效的对象类型，可以将其与expdp或者impdp的incl e或excl e 参数关联起来 DBA_DIRECTORIES --提供一系列已定义的目录 在开始操作前，需要进行以下3个操作： <1>.环境变量中对bin目录进行了配置。 <2>.在Oracle安装路径的bin文件夹中，确定和文件的存在 <3>.创建一个外部目录 创建文件夹d:\app\temp sql>create directory mypump as 'd:\app\temp'; --创建目录 grant read,write on directory mypump to scott; select * from dba_directories where directory_name='MYPUMP'; --查看创建的目录 数据泵导出选项 expdp命令的参数 help --显示用于导出的联机帮助，默认为n compress --指定要压缩的数据，可选值有：all、data only、metadata only和none content --筛选导出的内容 directory --指定用于日志文件和转储文件集的目的的目录 dumpfile --为转储文件指定名称和目录 encryption --指定一个加密级别 excl e --排除导出的对象和数据 flashback_scn --用于数据库在导出过程中闪回的系统更改号（scn） incl e --规定用于导出对象和数据的标准 logfile --导出日志的名字和可选的目录名 parfile --指定参数文件名 q ry --在导出过程中从表中筛选行 reuse_dumpfiles --覆盖已有的转储文件 status --显示Data Pump作业的详细状态 attach --将一个客户会话连接到一个当前运行的data pump export作业 full --在一个full 模式导出下通知data pump导出所有数据和元数据 schemas --在一个schemas模式导出中命名将导出的模式 tables --列出将用于一个table模式导出而导出的表和分区 tablespaces --列出将导出的表空间 transport tablespace --指定transportable tablespace 模式导出 transport_full_check --是否应该验证正在导出的表空间是一个自包含集 add_file exit_client kill_job start_job stop_job 操作 用命令行到bin目录下 expdp help = y expdp sys/oracle ctrl^c 实现数据导出 具有exp_full_database权限 expdp system/passwd directory=mytemp dumpfile=exptab.dmp p,scott.dept; --导出scott用户下的emp和dept表 expdp system/passwd directory=mytemp dumpfile=mydb.dmp full=y; --导出整个数据库 expdp system/passwd directory=mytemp dumpfile=schema.dmp schemas=scott nologfile=y; --导出对象scott用户的数据 expdp system/passwd directory=mytemp dumpfile=serspace.dmp tablespaces=users; --导出表空间数据 alter tablespace users read only; --修改users表空间为只读状态 expdp system/passwd directory=mytemp dumpfile=read_only_tbs.dmp transport_tablespaces=users; 使用导出参数 excl e --排除参数对象 incl e --只导出指定的参数对象 expdp system/passwd directory=mytemp dumpfile=excl e.dmp tablespaces=users excl e=table:"IN('emp')" excl e=table:"IN('dept')"; --使用excl e参数 expdp system/passwd directory=mytemp dumpfile=excl e.dmp tablespaces=users incl e=index; --使用incl e参数 expdp system/passwd directory=mytemp dumpfile=q ry.dmp scott.dept q ry=\"where deptno=10\"; --使用q ry参数 数据泵导入选项 impdp命令的参数 help excl e directory dumpfile incl e q ry full schemas tables tablespaces transport_tablespaces contin _client exit_client help kill_job parallel start_job status stop_job 实现数据导入 impdp help=y impdp system/passwd ctrl^c 具有imp_full_database权限 impdp system/passwd directory=mytemp dumpfile=exptab.dmp tables=scott.dept,scott.dept table_exists_action=replace; --导入表，如果存在执行replace impdp system/passwd directory=mytemp dumpfile=expdb.dmp full=y; --导入整个数据库 impdp system/passwd directory=mytemp dumpfile=expdb.dmp schemas=scott; --导入scott用户 impdp system/passwd directory=mytemp dumpfile=expdb.dmp tablesapce=users; --导入表空间users impdp system/passwd directory=mytemp dumpfile=expdb.dmp transport_tablespaces=users; --导入 =========================================================================================== 闪回技术概述 闪回的形式 1.flashback table --闪回表 2.flashback drop --闪回删除 3.flashback vesion q ry --闪回版本查询 4.flashback transaction q ry --闪回事务查询 5.flashback database --闪回数据库 6.flashback data archive --闪回数据归档（11g） show parameter undo --查看闪回设置信息 alter system set undo_retention=1200 scope=both; --设置闪回参数（scope=both 是马上生效 同时修改spfile文件 ） alter system set undo_retention=1200 scope=spfile; --设置闪回参数（scope=spfile 是重启生效 只修改spfile文件） 闪回表的使用语法 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from d l; delete feom emp1 where deptno=10; commit; alter table emp1 enable row movement; flashback table emp1 to times**p to_times**p('2010-08-22 10:56:00','yyyy-mm-dd hh24:mi:ss'); --用时间闪回 select times**p_to_scn(to_date('2010-08-22 10:56:00','yyyy-mm-dd hh24:mi:ss')) from d l; --把日期转换成scn号 flashback table emp1 to scn 获得的scn; --用scn闪回 1154955 ========================================================================================== 临时表空间为空解决办法 第 1 行出现错误: ORA-00604: 递归 SQL 级别 1 出现错误 ORA-25153: 临时表空间为空 select * from v$tempfile; --查看是否有临时表空间文件 未选定行 alter tablespace temp add tempfile 'd:\demo\temp01.dbf' size 10m; ============================================================================================ 回收站概念 BIN$globalUID$version -- 回收站命名规则 drop table emp1; show recyclebin; --查看回收站信息 使用闪回删除 flashback table emp1 to before drop; --闪回删除的表 flashback table "BIN$SLceC/x9**L9zb7V1vFbQ==$0" to before drop rename to test; --用回收站表名闪回删除的表，并重命名表名 ===================================================================================== select * from dba_2pc_pending; --查看未提交的事务处理个数 ====================================================================================== 管理回收站 启用与禁用回收站 dba_recyclebin --通过视图查看回收站信息 show parameter recyclebin --管理员用户查看当前回收站是否启用 show recyclebin --普通用户查看回收站是否启动 alter session set recyclebin=off; --设置回收站为off状态 desc user_recyclebin --查看回收站信息表 purge {tablesapace tablespace_name [user user_name] | table table_name | index index_name | [recyclebin | dba_recyclebin]} purge table emp3; --删除回收站中的表 purge table "BIN$YIlzg**+TN6aov3bMNfnhg==$0"; --删除回收站中的表 purge tablespace myspace; --删除回收站中的表空间 purge tablespace user scott; --删除回收站中scott用户的所有信息 purge index index_emp; --删除回收站中的索引 purge recyclebin; --清空回收站信息 闪回版本查询 select id,content,versions_operation,versions_xid,versions_starttime,versions_endtime from tt versions between times**p minval and maxval order by id,versions_starttime; --闪回时间查询 select id,content,versions_operation from tt versions between times**p to_date('2010-08-24 12:05:00','yyyy-mm-dd hh24:mi:ss') and to_date('2010-08-24 12:07:00','yyyy-mm-dd hh24:mi:ss'); 闪回事务查询 desc flashback_transaction_q ry; --闪回事务查询表 select table_name,operation,undo_sql from flashback_transaction_q ry where xid='09000C009E010000'; --查看对行所进行的操作 update "SCOTT"."TT" set "CONTENT" = 'The two Record' where ROWID = 'AAAMuqAAEAAAAA8AAB'; --闪回更新前的状态 闪回数据库 flashback [standby] database database_name [to [scn] | times**p] expr | to before [scn] | times**p] expr ] db_recovery_file_dest --闪回日志的存放位置 db_recovery_file_dest_size --存放闪回日志的空间（即恢复区）的大小 db_flashback_retention_target --闪回数据的保留时间，其单位为分，默认值为1440，即一天 show parameter db_recovery_file_dest; --查看闪回相应日志 show parameter db_flashback_retention_target; --查看闪回保留时间 select flashback_on from v$database; --查看闪回是否开启 select log_mode from v$database; --查看归档模式 archive log list; --查看归档模式 shutdown immediate; startup mount; alter database flashback on; --设置闪回为开启 select flashback_on from v$database; alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; --格式化字段 select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log; --查看最早能够执行到闪回的scn号和时间 使用闪回数据库 shutdown immediate startup mount exelusive flashback database to times**p(to_date('2010-08-24 13:00:31','yyyy-mm-dd hh24:mi:ss')); alter database open resetlogs; 使用闪回数据归档 create table table01(id number,text varchar2(10)) flashback archive archive01; --把table01表的归档放到archive01数据归档区 alter table table01 flashback archive archive01; --修改闪回数据归档区 alter table table01 flashback archive default; --修改为默认 alter table table01 no flashback archive; |
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论