oracle学习笔记

一、oracle常用命令和查询语句
1.登录
   a.普通用户登录：sqlplus 用户名/密码
   b.管理员登录：sqlplus / as sysdba
   c.启动监听程序：Isnrctl start;
   d.oradim -starup -sid orcl
   e.修改用户密码：alert user 用户名 identified by 新密码；
2.系统权限
  a.管理员可以创建用户：create user lisi identified by lisi;,删除用户 drop user lisi [cascade];
  b.授予create session权限： grant create session to lisi；
  c.授予create table 和unlimited tablespace权限:grant create table,unlimited tablespace to lisi;
  d.grant connect, resource to 用户;
  撤销权限关键字是：revoke；
  revoke select on tablename from username;
3.对象(表、视图)权限
  a.授予查询表权限： grant select on 表名 to 用户名；
  b.授予插入表权限： grant insert on 表名[列名] to 用户名；
  c.授予删除表权限： grant delete on 表名 to 用户名；
  d.授予更新表权限： grant update on 表名[列名] to 用户名；
  e.授予所有的权限： grant all on 表名[列名] to 用户名；
  f.授予执行存储过程的权限： grant execute on 存储过程名 to 用户名；
4.orcal默认是手动提交，表插入记录是要加commit语句。
5.查询当前用户拥有哪些系统权限：select * from user_sys_privs;
6.设置控制台窗口的行宽： set linesize 400；
7. 权限的传递:A用户拥有的权限可以授予给B用户
   系统权限：grant alert any table to 用户名 with admin option
   对象权限：grant select on 表名 to 用户名 with grant option
8.角色：权限的集合
  创建角色：create role 角色名;
  授予角色create session权限：grant create session to 角色名;
  给用户授予角色： grant 角色名 to 用户名;
  删除角色：drop role 角色名;
  不能给角色授予的权限: unlimited tablespace;
9.oracle中的小技巧
  a.执行文件中的sql语句用 @c:/text.txt;
  b.定位缓冲区中的语句用 list或l 显示第几行用 list 1 或l 1  显示第几行到第几行用 list 2 3或l 2 3
  c.改变缓冲区中的字符用  change或c  把n变成m change /n/m 或c /n/m
  d.执行缓冲区中的语句用 /
  f.获取文件中的sql语句用 get c:/test.txt
  g,报表制作命令： break comp
10.查询sqlplus 的命令用 help index ，查询语法的使用用 ？ 语法。
11.查询表结构用 desc[ribe] 表名；
12.decode 函数的使用  decode(字段，'值'，'真的时候','假的时候')；
13.nvl 函数的使用，当数据为空时使用，例如 nvl(字段，'没有数据时显示的值')
14.创建同义词语句：create public synonym dept for scot.dept
15.修改表结构
   增加主键约束：alert table 表名 add constraint 约束名 primary key(字段名)；
   增加外键约束：alert table 表名 add constraint 约束名 foreign key（字段名）references 表名(字段名)；
   增加check约束：alert table 表名 add constraint 约束名 check(表达式)；
16.经常要查询的列要建立索引，这样会提高性能
   查询时要加 where 建立索引的列名="";ong
17.dba用户给普通用户解锁
   alter user 用户名 account unlock
18.创建保存点 savepoint 保存点名，恢复到保存点 rollback to 保存点名
19.事物处理：
    set transaction read only
20.设置时间格式：alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

oracle 常用查询语句
1、查看当前用户拥有的角色权限信息
     select * from role_sys_privs;
2、查看所有用户：
    select * from dba_users;
    select * from all_users;
    select * from user_users;
3、查看用户系统权限
    select * from system_privilege_map;
    select * from dba_sys_privs;
    select * from session_privs;
    select * from user_sys_privs;
4、查看用户对象权限
    select *  from dba_tab_privs;
    select *  from all_tab_privs;
    select *  from user_tab_privs;
5、查看所有角色：
    select * from dba_roles; 
6、查看用户的角色信息：
    select * from dba_role_privs;
    select * from user_role_privs;
7、快速删除表数据 ,但是不写日志，不能恢复
    truncate table 表名;
8、设置显示执行时间
    set timing on;
9、查看表的索引
    select * from dba_indexs;
    select * from user_indexs;
10、更改oracle占用的8080端口 
call dbms_xdb.cfg_update(updateXML(dbms_xdb.cfg_get(),
'/xdbconfig/sysconfig/protocolconfig/httpconfig/http-port/text()',8081))
/

二，oracle管理工具
  1.sql*plus
  2.oracle 企业管理器（enterprise manger console）
  3.第三方软件 pl/sql developer

三、oracle管理员职责
  1.安装和升级oracle数据库
  2.建库、表空间、表、视图、索引。。。
  3.制定并实施备份和恢复计划
  4.数据库权限管理、调优、故障排除
  5.对于高级dba，能参与项目开发、会编写sql语句、存储过程、触发器、规则、约束、包。。。

四、oracle的备份与恢复
  1.备份
    备份包括三种方式：导出表、导出方案、导出数据库，导出命令式exp[ort],常用的选项有
    userid：用于执行导出操作的用户、口令、连接字符串
    tables：用于指定执行导出操作的表
    owner：用于指定执行导出操作的方案
    fully=y：用于指定执行导出操作的数据库
    inctype：用于指定执行导出操作的增量类型
    rows：用于指定执行导出操作是否导出表中的数据
    direct:快速导出
    file:用于指定导出文件名
   例如：exp userid=scott/triger@myorcl tables=(emp) file=d:\emp.dmp direct=y
         exp userid=scott/triger@myorcl tables=(emp,dept) file=d:\emp.dmp
exp userid=scott/triger@myorcl tables=(emp,dept) rows=n file=d:\emp.dmp
exp userid=scott/triger@myorcl owner=(scott) file=d:\emp.dmp
   注意：一定要在oracle安装名录下的bin目录下执行操作
  2.恢复
    恢复包括三种方式：导入表、导入方案、导入数据库，导入命令式imp[ort],常用的选项有
    userid：用于执行导入操作的用户、口令、连接字符串
    tables：用于指定执行导入操作的表
    fromuser：用于指定源用户
    touser：用于指定目标用户
    owner：用于指定执行导入操作的方案
    fully=y：用于指定执行导入操作的数据库
    inctype：用于指定执行导入操作的增量类型
    rows：用于指定执行导入操作是否导出表中的数据
    file:用于指定导入文件名
    ignore：如果表存在，则只导入数据
   例如：imp userid=scott/triger@myorcl tables=(emp) file=d:\emp.dmp
imp userid=scott/triger@myorcl file=d:\emp.dmp //导入自己的方案
         imp userid=scott/triger@myorcl tables=(emp,dept) file=d:\emp.dmp touser=scott
imp userid=scott/triger@myorcl tables=(emp,dept) rows=n file=d:\emp.dmp ignore=y
imp userid=scott/triger@myorcl owner=(scott) file=d:\emp.dmp
imp userid=system/manger@myorcl file=d:\emp.dmp fromuser=system touser=scott
   注意：一定要在oracle安装名录下的bin目录下执行操作
五、表空间的建立和使用
   表空间<--段<--区<--块
   1.什么是表空间
     表空间是数据库的逻辑划分，一个表空间只属于一个数据库，所有的数据库对象都存放在表空间中
     但主要存放的是表，所以称作表空间。Oracle数据库中至少存在一个表空间，即SYSTEM的表空间。
   2.建立表空间
     create tablespace 表空间名 datafile 'd:\test\test.dbf' size 20M uniform size 128k
     注意：表空间数据文件的最大容量是500M
   3.修改表空间
     修改状态：alter tablespace 表空间名 offline、alter tablespace 表空间名 online
               alter tablespace 表空间名 read only、alter tablespace 表空间名 read write
   4.查询指定表空间所有的表
     select * from all_tables where tablespace_name='表空间名'
   5.知道表名，查看该表属于哪个表空间
     select tablespace_name,table_name from user_tables where table_name='表名'
   6.删除表空间
     drop tablespace 表空间名 [including contents and datafiles]
    注意：后面那部分表式删除表空间时会删除该表空间的所有数据库对象和数据文件
   7.扩展表空间
     有三种方法
     a.修改数据文件大小
       alter tablespace 表空间名 '文件名' resize 500M
     b.增加数据文件
       alter tablespace 表空间名 add datafile '文件名' size 20M
     c.设置文件的自动增长
       alter tablespace 表空间名 '文件名' autoextend on next 10M maxsize 500M
   8.移动数据文件
     步骤：
     a.确定数据文件所在表空间
       select tablespace_name from dba_data_files where file_name='文件名'
     b.使用表空间脱机
       alter tablespace 表空间名 offline
     c.使用命令移动数据文件所在的位置
       host move '文件名' '新文件名'
     d.执行修改表空间数据文件位置命令
       alter tablespace 表空间名 rename datafile '文件名' '新文件名'
     e.使得表空间联机
       alter tablespace 表空间名 online
六、oracle pl/sql编程
   1. 在pl/sql 中连接用||而不是+号，行注释用--。
   2. 打开缓冲区，让执行结果显示的语句：set serveroutput on size 大小;
   3. 定义变量关键字：declare
   4. 连接命令 conn
   conn 用户名/密码@网络服务器名[as sysdba/sysoper]
   5. 运行sql脚本
   start 或@ 命令
   例如： start 脚本文件
   6.数据类型
   字符型 char 类型最大长度为2000 ，varchar2 类型最大长度为4000，char 数据类型的查询速度比
    varchar数据类型快好多
    数字型 只有一种number类型  例如 number(5,2),number(5)，number可以表示小数和整数
    时间类型 data类型和timestamp
    图片音频视频 blob
   7.删除表数据的另一种写法 truncate table 表名，主要用于删除数据量大的表，速度很快
   8.赋值语句用:=号而不是=号
   9.要在pl/sql中输出内容，要执行语句 set serveroutput on