ORACLE DBA常用语句.docx

连接命令 connect 用法: conn 用户名/密码网络服务名 as sysdba/sysoper 当用特权用户身份链接时，必须带上as sysdba或者as sysoper注：sysdba权限最大disconnect断开连接password修改用户的密码需先登陆文件操作命令start或者运行sql脚本案例 d:a.sql spool 将sql*plus屏幕上的内容输出到制定文件中案例 ： spool d:b.sql 并输入 spool off显示和设置环境变量linesize 设置显示行宽度，默认80set linesize pagesize 设置每页显示的行数目，默认14用户管理权限：系统权限：用户对数据库的相关权限对象权限：用户对其他用户的数据对象（该用户创建的数据：表视图过程）访问权限（select,insert updata delete all create index.)角色：一种特定的用户，包含已被赋予的权限自定义角色，预定义角色eq: connect dba resource创建用户create user 用户名 identified by 密码创建的用户是没有任何权限的，需要给权限赋予权限：grant connect to 用户名 grant select on emp to 用户名-select*from 用户名.table;权限维护 grant select on 用户名.emp to 用户名 with grant optiongrant connect to xiaoming with admin option注：取消中间者权限后被授权者人不再有该权限收回权限revoke select on 用户名.emp from 用户名修改密码 password 用户名删除用户drop user 用户名如果要删除的用户已经创建了表，那么删除是要带一个参数cascade使用profile管理用户口令creatr profile 配置文件名 limit failed_login_attempts 3 password_lock_time 2;alter user 用户名 profile 配置文件名;解锁alter user 用户名 account unlock;密码终止口令（可让用户定期修改密码）create profile 配置文件名 limit password_life_time 10 password_grace_time 2;alter user 用户名 profile 配置文件名口令历史建立PROFILEcreate profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10删除配置文件 drop profile 配置文件名 【casecade】表的创建1必须以字母开头2长度不能超过30字符3不能使用ORACLE的保留字4只能使用A-Z,a-z,0-9等字符类型char 定长 最大2000字符varchar2 变长 最大4000字符clob 字符型大对象数字型number 范围 10的-38次方到10的38次方可以整数和小数number (5,2)表示一个小数有5位有效数，2位小数范围-999.99到999.99number（5）表示一个五位整数范围-99999-99999日期型datetimestamp图片blob 二进制数据 可以存档图片声音 容量限制4GB显示表空间select tablespace_name from dba_tables where table_name=TABLE显示某用户的所有表select TABLE_NAME from all_tables where owner=USER;显示当前数据库的所有表select*from tab;显示当前用户的所有表select*from user_tables;创建表create table 表名（行名称 字段属性）；删除表drop table 表名添加字段alter table 表名 add （行名称 字段属性）；修改字段长度alter table 表名 modify (行名称 字段属性）； 有数据的最好不改删除字段alter table 表名 drop column 行名称；修改表名 rename table 表名 to 新表名；查看表结构（字段名称属性是否为空等属性）desc 表名;向表添加数据insert into 表名称 values (各行数据）默认格式日期是DD-MON-YY 例：25-12月-16改日期默认格式alter session set nls_date_format=yyyy-mm-dd;修改后insert into student values (2016-12-25)插入部分字段insert into 表名(字段名） values(字段值)多个字段用逗号隔开插入空值insert into 表名 （字段名） values （字段值，null);修改字段update 表名 set 字段名=更改后的值 where 字段名或主键=字段值；修改多个字段update 表名 set 字段名=更改后的值，字段名=更改后的值 where 字段名或者主键=字段值；修改含有null值的数据 删除数据delete from 表名；删除表的记录，但是表仍存在。并且存在日志，可恢复。truncate table student;删除表中所有记录，表仍存在。无日志，不可恢复。有点速度快。设置还原点savepoint 点名称；还原数据rollback to 点名称;显示操作时间set timing on;insert into 表名 （字段名） select*from 表名可实现大批量复制select count(*) from 表名；可数一个表中的所有行数select 字段名 from 表名select distinct 字段名 from 表名；查询emp表员工年工资范例select sal*13+nvl(comm,0)*13 年工资,ename from emp;多个条件用ANDEQ: select ename,sal from emp where sal=2000 and sal500 or job=manager) and ename like J%;order by 字句排序语句select*from emp oder by sal;按照薪水从低到高select*from emp order by sal desc;从高到低select*from emp order by deptno,sal desc;使用别名排序select ename,(sal+nvl(comm,0)*12 as 年薪from emp order by “年薪”；分页查询select max(sal),min(sal) from emp;select ename,sal from emp where sal=(select max(sal) from emp);group by 用于对查询的结果分组统计having子句用于限制分组显示结果EQ：查看部门平均工资和最高工资select avg(sal),max(sal),deptno from emp group by deptno;查看部门不同职位的平均工资和最低工资select avg(sal),max(sal),deptno,job from emp group by deptno,job;显示平均工资低于2000的部门好和它的平均工资select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)2000 order by avg（sal）；多表查询：显示雇员名，雇员工资以及所在部门的名字select a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno;显示部门号为10的部门名员工名和工资select a2.ename,a1.dname,a2.sal from dept a1,emp a2 where a1.deptno=a2.deptno and a1.deptno=10显示各个员工的姓名，工资，及其工资的级别。select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal btween a2.losal and a2.hisal;显示雇员名，雇员工资及所在部门的名字，并按部门排序select a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno order by a1.deptno;自连接指同一张表的连接查询显示某个员工的上级领导的姓名。select ,boss.ename from emp worker,emp boss where worker.mgr=boss.empno and worker.ename=FORD;子查询如何显示与SMITH同一部门的所有员工select deptno from emo where ename=SMITH;select*from emp where deptno=(select deptno from emo where ename=SMITH);多行子查询如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号select*from emp where job in (select distinct job from emp where deptno=10);在多行子查询中使用all操作符如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号select ename,sal,deptno from emp where salall (select sal from emp where deptno=30);slect*from emp where sal(select max(sal) from emp where deptno=30);any操作符如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号。select ename,sal,deptno from emp where salany (select sal from emp where deptno=30);select*from emp where sal(select min(sal) from emp where deptno=30);多列子查询如何查询与smith 的部门和岗位完全相同的所有雇员。select*from emp where (deptno,job)=(select deptno,job from emp where ename=SMITH);在FROM子句中使用子查询如何显示高于自己部门平均工资的员工信息select a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2, (select deptno,avg(sal) mysal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sala1.mysal分页查询 一共有三种方式 rownum分页select a1.*,rownum rn from (select*from emp) a1 where rownum=10;select*from (select a1.*,rownum rn from (select*from emp) a1 where rownum=6;指定查询列，只需修改最里层的子查询排序只需修改最里层rowid分页select*from t_xiaoxi where rowid in(select rid from(select rownum rn,rid from(select rowid rid,cid from t_xiaoxi order by cid desc)where rownum9980)order by cid desc;查询表的是所有行数select count(*) from emp;用查询结果创建新表create table 表名 (id,name,sal,job,deptno) as select empo,enmae,sal,job,deptno from emp;合并查询unionselect ename,sal,job from emp where sal2500 unionselect ename,sal,job from emo where job=manager;union all该操作赋予union相似，但是它不会取消重复行，而且不会排序select ename,sal,job from emp where sal2500 union all select ename,sal,job from emp where job=manager;intersect取两个查询结果的交集select ename,sal,job from emp where sal2500 intersect select ename,sal,job from emp where job=manager;minus取两个结果的差集，只会显示存在第一个集合中，而不存在第二个集合中的数据select ename,sal,job from emp where sal2500 minus select ename,sal,job from emp where job=manager;使用特定格式插入日期值insert into emp values(9998,小红，mannager,7782,11-11月-1988,78.9,55.33,10)insert into emp values(9997,小红2,to_date(1988-12-12,YYYY-mm-dd),78.9,55.33,10);行迁移 insert into kkk （myid,myname,mydept) select empo ,ename,deptno from emp where deptno=10;希望员工scott的岗位，工资，补助和SMITH一样update emp set (job,sal,comm)=(select job,sal,comm from emp where ename=SMITH) where ename=SCOTT;事务处理 DML 数据处理语言 增删改只读事务只读事务是指只允许执行查询的操作，而不是允许执行任何其他DML操作的事务set transacation read only;SQL函数字符函数 -是ORACLE中最常用的函数lower(char):将字符串转换为小写格式将所有员工的名字按小写的方式显示select lower(ename) from emp;upper(char):将字符串转换为大写格式将所有员工的名字按大写的方式显示select upper(ename) from emp;length(char):返回字符串的长度显示正好为5个字符的员工的姓名select*from emp where length(ename)=5;substr(char,m,n)：取字符串的子串显示所有员工姓名的前三个字符select substr(ename,1,3) from emp;-从第几个取，取三个以首字母大写的方式显示所有员工的姓名1.完成首字母大写select upper(substr(ename,1,1) from emp;2.完成后面字母小写select lower(substr(ename,2,length(ename)-1) from emp;3合并select upper(substr(ename,1,1) | lower(substr(ename,2,length(ename)-1) from emp;以首字母小写的方式显示所有员工的姓名select lower(substr(ename,1,1)|upper(substr(ename,2,length(ename-1) from emp;替换函数replace(char1,search_string,replace_string)select replace(ename,A,a) from emp;instr(char1,char2,n,m取子串在字符串的位置round(n,m)该函数用于执行四舍五入，如果省略掉m,则四舍五入到整数；如果m是正数，则四舍五入到小数点的m位后，如果m是负数，则四舍五入到小数点的m位前select round(sal,1),sal from emp where ename=shunping;效果: 2456.34-2456.3 55.66-55.7trunc(n,m)该函数用于截取数字，如果省掉m，就截去小数部分，如果m是正数就截取到小数点的m位后，如果m是负数，则截取到小数点的前m位select trunc(comm,1),comm from emp where ename=shunping;效果：55.66-55.6 select trunc(comm,-1),comm from emp where ename=shunping;效果：55.66-50mod(m,n) 取余数select mod(10,2) from dual;floor(n)返回小于或是等于n的最大整数向下取整ceil(n) 返回大于或是等于n的最小整数向上取整dual在做ORACLE测试，可以使用dual表显示在一个月为30天的情况下所有员工的日薪金，忽略余数。select trunc(sal/30),ename from emp;select floor(sal/30),ename from emp;数学函数abs(n) 返回数字n的绝对值acos(n)返回数字的反余旋值asin(n)返回数字的反正旋值atan(n)返回数字的反正切cos(n)exp(n)返回e的n次幂log（m,n)返回对数值power（m,n)返回m的n次幂日期函数sysdate:add_months(d,n)显示入职八个月多的员工select*from emp where sysdateadd_months(hiredate,700);last_day(d)返回指定日期所在的月份的最后一天显示工作满十年的员工select*from emp where sysdatea=dd_months(hiredate,12*10);对于每个员工，显示其加入公司的天数select trunc(sysdate-hiredate )入职天数,ename from emp;找出个月倒数第三天受雇的所有员工。select hiredate,ename from emp where last_day(hiredate)-2=hiredate;转换函数用于将数据类型从一种转为另外一种，在某些情况下，ORACLE SERVER 允许值的数据类型和实际的不一样，这是ORACLE SERVER会隐含的转化数据类型例：create table table1 (id int);insert into table1 values(10)-这样ORACLE会自动将10转换为10create table table2 (id varchar2(10);insert into table2 values(1); -这样ORACLE就会自动的将1转换为1to charselect ename,to_char(hiredate,yyyy-mm-dd hh24:mi:ss) from emp;to_char(sal,L99，999.99)注“9显示数字并忽略前面的00：显示数字，如位数不足，则用0补齐.在制定位置显示小数点，在指定位置显示逗号$在数字前加美元L在数字前加本地货币符号C在数字前加国际货币符号G在指定位置显示组分隔符D在指定位置显示小数点符号to_char显示1980年入职的所有员工select*from emp where to_char(hiredate,yyyy)=1980;显示所有12月份入职的员工select*from emp where to_char(hiredate,mm)=12;系统函数sys_contextterminal 当前会话客户所对应的终端的标识符lanuage语言db_name当前数据库名称nls_date_format当前会话客户所对应的日期格式session_user当前会话客户所对应的数据库用户名current_schema当前会话客户所对应的默认方案名host 返回数据库所在的主机的名称select sys_context(userenv,db_name) from dual;number(7,2) 7位数字，2位小数=5位整数show parameter;-显示数据库参数逻辑备份是指使用工具export将数据库对象的结构和数据导出到文件的过程。物理备份即可在数据库open的状态下进行，也可以在关闭后进行但是逻辑备份和恢复只能在OPEN状态下进行。导出：在导入和导出的时候要到ORACLE的主目录去导出导入导出需CMD定位至ORACLE的BIN文件夹下。导出分为：导出表，导出方案，导出数据库三种方式导出使用exp命令来完成的，该命令常用的选项有：userid:用于指定执行导出操作的用户名，口令，连接字符串tables用于指定执行导出操作的表owner用于指定执行导处操作的方案full=y用于指定执行导出操作的数据库inctype用于指定执行导出操作的增量类型rows用于指定执行导出操作是否要导出表中的数据file用于指定导出文件名导出自己的表(CMD环境）exp userid=scott/tigermycral tables=(emp) file=d:e1.dmp导出其它方案的表如果用户要导出其他方案的表，则需要DBA权限或者是exp_full_database权限，比如system就可以导出scott的表exp userid=system/managermyoral tables=(scott.emp) file=d:e2.dmp仅导出表的结构exp userid=scott/tigermycral tables=(emp) file=d:e2.dmp rows=n直接导出方式-当数据量比较大时可以使用exp userid=scott/tigeraccp tables=(emp) file=d:e3.dmp direct=y导出方案导出自己的方案exp scott/tigermyoral owner=scott file=d:scott.dmp导出其它方案exp system/managermyoral owner=(system,scott) file=d:system.dmp导出数据库要求用户具有DBA权限或者是exp_full_database权限exp userid=system/manager数据库名 full=y inctype=complete file=d：x.dmp PS:inctype=complete指增量备份。之后备份速度会很快。导入import导入自己表imp userid=scott/tiger数据库名 tables=(emp) file=d:xx.dmp导入表到其他用户要求有DBA权限或者imp_full_databaseimp userid=system/manager数据库名 tables=(emp) file=d:xx.dmp touser=scott导入表结构只导入表的结构而不导入数据imp userid=scott/tiger数据库名 tables=(emp) file=d:xx.dmp rows=n导入数据如果对象已经存在可以只导入表的数据imp userid=scott/tiger数据库名 tables=(emp) file-d:xx.dmp ignore=y导入方案导入自身的方案imp userid=scott/tiger file=d:xx.dmp导入其他方案imp userid=system/manager file=d:xx.dmp fromuser=system touser=scott导入数据库imp userid=system/manager full=y file=d:xxx.dmp数据字典提供了数据库的一些系统信息记录了数据库的系统信息它是只读表和视图的集合，数据字典的所有者为sys用户用户只能在数据字典上执行、select语句，而其维护和修改是由系统自动完成的。数据字典包括数据字典基表和数据字典视图，其中基表存储数据库的基本信息，普通用户不能直接访问数据字典的基表，数据字典视图是基于数据字典据表所建立的视图，普通用户可以通过查询数据字典视图取得系统信息，数据字典视图主要包括user_xxx.all_xxx,dba_xxx三种类型动态视图记载了例程启动后的相关信息user_tables用于显示当前用户所拥有的所有表，它只返回用户所对应方案的所有表select table_name from user_table;all_tables用户显示当前用户可以访问的所有表，它不仅会返回当前用户方案的所有表，还会返回当前用户可以访问的所有表select table_name from all_tables;dba_tables它会显示所有方案拥有的数据库表，但是查询这种数据库字典视图要求用户必须是DBA角色或者是有select any table系统权限。例：当用system用户查询数据字典视图dba_tables时，会返回system,sys,scott.方案所对应的数据库表dba_users-可查询数据库所有的用户dba_sys_privs-可显示用户具有的所有的系统权限dba_tab_privs-可以显示用户具有的对象权限dba_col_privs-可以显示用户具有的列权限dba_role_privs-可以显示用户所具有的角色查询ORA中所有的系统权限，要求具有DBA权限select*from system_privilege_map oder by name;查询ORA中所有的角色select*from dba_roles查询ORA中所有的对象权限select distinct privilege from dba_tab_privs;查询数据库的表空间select tablespace_name from dba_tablespaces；查询一个角色包括的权限select*from dba_sys_privs where gtantee=CONNEXT;如何查看某个用户具有什么角色？select*from dba_role_privs where grantee=SCOTT;显示当前用户可以访问的所有数据字典视图；select*from dict where comments like %grant%;显示当前数据库的全称select*from global_name;表空间表空间是数据库的逻辑组成部分，从物理学上讲，数据库数据存放在数据文件中；从逻辑上讲，数据库则是存放在表空间中，表空间由一个或是多个数据文件组成。ORACLE中逻辑结构包括表空间，段，区和块。为了提高运行、管理效率表空间作用1.控制数据库占用的磁盘空间2.DBA可以将不同数据类型部署到不同的位置，这样有利于提高I/O性能，同时利于备份和恢复等管理操作。创建表空间create tablespace需要DBA或者CREATE TABLESPACE的系统权限。建立数据表空间(最多分配500M，不够的话可以创建两个分配数据）create tablespace data01 datafile d:testdata01.dbf size 20m uniform size 128k执行上述命令后会建立名称为data01的表空间，并为该表空间建立名称为data01.dbf的数据文件，区的大小为128K使用表空间create table mypart(deptno number(4),dname varchar2(14),loc varchar2(13) tablespace data01;改变表空间的状态1使表空间脱机alter tablespace 表空间名 offline;2使表空间联机alter tablespace 表空间名 online;3只读表空间当建立表空间时，表空间可以读写，如果不希望在该表空间上执行update,delete,insert操作，那么可以讲表空间修改为只读alter tablespace 表空间名 read only4表空间可读写alter tablespace 表空间 read write;改变表空间的状态1知道表空间名，显示该表空间包括的所有表select*from all_tables where tablespace_name=表空间名;2知道表名，查看该表属于哪个表空间select tablespace_name,table_name from user_tables where table_name=emp;PS:system表空间不能设置为只读删除表空间(删除时同时删除表空间内所有数据库对象)需要DBA角色或者DROP TABLESPACE系统权限drop tablespace 表空间名 including contents and datafiles;扩展表空间1增加数据文件alter tablespace sp001 add 表空间名 dsp001.dnf size 20m2增加数据文件的大小（最大500M）alter tablespace 表空间名 d:sp001,dbf resize 20m;3设置文件的自动增长（最大500m）alter tablespace 表空间名 d:sp001.dbf autoextend on next 10m maxsize 500m;移动数据文件有时如果数据文件所在磁盘损坏，该数据将不能再使用，为了能够重新使用，需要将这些文件的副本移动到其他的磁盘，然后恢复。1确定数据文件所在的表空间select tablespace_name from dba_data_files where file_name=d:sp001.dbf;2使表空间脱机alter tablespace sp001 offline;3使用命令移动数据文件到指定的目标位置host move d:sp001.dbf c:sp001.dbf4移动数据文件物理移动数据后，还必须执行alter tablespace命令对数据库文件进行逻辑修改；alter tablespace 表空间名 rename datafile d:sp001.dbf to c:sp001.dbf;5使表空间联机alter tablespace sp001 online；显示表空间信息select tablespace_name from dba_tablespaces;显示表空间所包含的数据文件select file_name,bytes from dba_data_files where tablespace_name=表空间名;其他表空间1索引表空间2undo表空间3临时表空间4非标准块的表空间约束约束用于确保数据库数据满足特定的商业规则。在ORACLE中，约束包括：not null,unique,primary key,foreign key和check五种。not null插入数据时必须为列提供数据unique当定义唯一约束后，该列值是不能重复的，但是可以为nullprimary key用户唯一的标示表行的数据，当定义主键约束后，该列不但不能重复而且不能为NullPS：一张表最多只能有一个主键（可以将几列设置为一个主键），但是可以有多个unqiue约束foreign key用于定义主表和从表之间的关系，外键约束要定义在从表上，主表则必须具有主键约束或是unique约束。当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为NULLcheck用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000-2000之间，如果不在1000-2000之间，就会提示出错。商店售货系统表设计案例SQL语句-创建goods表create table goods (goodsid char(8) primary key,-主键goodsName varchar2(30),unitprice number(10,2) check (unitprice0),category varchar2(8),provider varchar2(30);PS:此处约束名为随机。如果指定约束名则在约束前增加 constraint 约束名。-创建customer表create table customer(customerId char(8) primary key,-主键name varch