Oracle知识点记录

    记录一下本人Oracle学习过程中的琐碎知识点，备查！如有谬误，敬请告知！谢谢！

• 常用命令：
  show user： 查看当前登录身份
  show all: 查看全部环境变量：
  
  可以通过在文件名前加@来运行脚本：
  sql> @c:\data\someDirectory\somescrip.sql
  
  conn[ect] 用户名/密码@网络 [as sysdba / as sysoper] :  以不同权限切换登录身份
  
  disc [conn[ect]]:  断开登录，但不退出sql窗口，方便再次登录其它身份。
  exit：断开登录，同时退出sql窗口。
  
  edit: 编辑.sql脚本。 命令格式：edit 脚本文件路径
  
  spool：保存sql窗体内容到文件中。
  命令的执行步骤如下：
  1、开启功能：  spool on;
  2、设置文件路径： spool d:/1.txt;
  3、执行sql命令
  4、关闭功能,此时会自动保存spool on后执行的内容到文件： spool off;
  
  
• Oracle数据类型：
  1、char类型： 定长，存储占用固定长度，不足补空格，最大存储2000字符。通常用于保存商品编号等定长数据。
  2、nchar类型：定长。unicode编码存储，最大存储2000字符。无论是一个字母或汉字都只占一个字符空间。
  
  3、varchar类型：变长，按实际的字符数存储。最大存放4000个字符。
  4、nvarchar类型：变长，unicode编码存储，按实际的字符数存储。最大存放4000个字符。
  
  5、clob类型：变长。字符型对象，最大存储8tb。
  6、blob类型：变长。二进制数据，可以存放图像/声音。最大存储8tb。
  说明：实际开发中，一般是记录文件的存储路径/URL，很少会直接保存文件在数据库。除非对图像/声音文件的安全性有高要求，可以保存到数据库。
  
  7、number类型：数值，变长，既可以存整数，也可以存小数类型。
  语法：number(p,s) p为有效位位数，有效位从左边第一个不为0的数算起的位数。s为小数位
  范围: 1≤ p ≤38, -84≤ s ≤127
  说明：实际开发中，如果有明确要求保留几位小数，则指定明确指定，否则统一使用number即可。
  
  8、date类型： 表示时间，年月日时分秒
     Timestamp(n) 时间戳类型：随数据更新自动时间日期。
  
  
• Oracle的登录机制：
  使用windows系统学习oracle的初学者可能会发现一个现象，即当以sysdba身份登录oracle时，show user显示的身份并不是登录身份：
  
  更神奇的是，即便你用户名/密码是错的，也可以登录成功：
  
  原因在于oracle的登录验证机制，如果当前登录的windows用户属于系统的ora_dba用户组（安装oracle时自动创建的用户组），则oracle认为是特权用户（sys用户），默认会使用操作系统认证，否则才会使用普通用户的数据库验证方式（即用户名密码验证）登录。
  如果登录时加上as sysdba，dbms看到后则认为是要以特权用户登录，不检查用户名和密码，登陆后自动切换成sys用户。
  
  修改oracle登录认证方式：
  可通过修改sqlnet.ora文件更改oracle登录认证方式：
  SQLNET.AUTHENTICATION_SERVICES=(NTS) 基于操作系统验证；
  SQLNET.AUTHENTICATION_SERVICES=(NONE) 基于Oracle验证；
  SQLNET.AUTHENTICATION_SERVICES=(NONE,NTS) 二者共存。
  
  
• 如何浏览器中登录oracle?
  方法一： 执行命令:emctl start dbconsole
  启动数据库的dbconsole服务（不建议启用，有风险），得到访问链接就可以。
  但命令执行可能会出现如下错误：Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME to database unique name.  
  
  按照提示解决：
  1、先设置变量 ORACLE_UNQNAME=数据库实例名，如下：
  set ORACLE_UNQNAME=orcl
  
  2、重新配置emctl: emca -config dbcontrol db
  会输入挺多信息：
  
  等待一段时间，成功界面如下:
  
  以上成功信息出现后，再次执行emctl start dbconsole，直接复制提示信息中的URL到浏览器就可以了。
  
  直接按那个提示信息 复制URL 到浏览器就可以了：
  
  方法二、可以通过如下方式在浏览器中访问：
  https://(远程或本地ip地址):1158/em
  端口号可以去ORACLE_HOME/install目录的portlist.ini文件查看！
  
  
• 管理员密码遗忘怎么办？
  1、搜索‘PWD数据库实例名.ora’文件；
  2、删除该文件。（建议先备份一份）；
  3、cmd下命令生成新的密码文件：
  orapwd file=原来密码文件的全路径\PWD数据库实例名.ora password=新密码 entrise=10;
  说明：a、entrise指定允许几个特权用户；
    b、密码文件名要和原来的保持一致。
  4、重启数据库实例后生效；
  
  
• Oracle特殊符号含义： blog链接
  在where子句中，通配符可与like条件一起使用。在Oracle中：
  %(百分号)： 用来表示任意数量的字符，或者可能根本没有字符。
  
  _(下划线)： 表示确切的未知字符。
  ?(问号)： 用来表示确切的未知字符。（貌似不行）
  
  #(井号)： 用来表示确切的阿拉伯数字，0到9。(貌似不行)
  [a-d](方括号)：用来表示字符范围，在这里是从a到d。
  单引号(')：在Oracle中，应该只使用单引号将文本和字符和日期括起来，不能使用引号（包括单双引号）将数字括起来。
  双引号(")：在Oracle中，单双引号含义不同。双引号被用来将包含特定字符或者空格的列别名括起来。双引号还被用来将文本放入日期格式。
  
  撇号(')：在Oracle中，撇号也可以写成彼此相邻的两个单引号。为了在供应商名字中间查找所有带撇号的供应商名字，可以这样编写代码：select * from l_suppliers where supplier_name like '%''%'
  
  &符号：在Oracle中,&符号常用来指出一个变量。例如，&fox是一个变量，稍微有点不同的一种&& fox。每当&fox出现在Oracle脚本中时，都会要求您为它提供一个值。而使用&&fox，您只需要在& &fox第一次出现时为它提供变量值。如果想将&符号作为普通的符号使用，则应该关闭这个特性。要想关闭这个特性，可以运行以下的命令： set define off ，这是一个SQLplus命令，不是一个SQL命令。SQLplus设置了SQL在Oracle中运行的环境。
  
  双竖线（||）：Oracle使用双竖线表示字符串连接函数。
  星号(*)：select *意味着选择所有的列，count（*）意味着计算所有的行，表示通配符时，表示0个或任意多个字符。
  正斜杠(/)：在Oracle中，用来终止SQL语句。更准确的说，是表示了“运行现在位于缓冲区的SQL代码”。正斜杠也用作分隔项。
  多行注释：/*......*/。
  
  不等于：有多种表达方式：!=、^=、<>、not xxx=yyy、not(xxx=yyy)。
  注意，判断是否为null不能用!=和<>，如下区别：
  
  ESCAPE 定义转义符，格式为 ESCAPE 'escape_character'
  
  特别说明，'&'不能通过转义字符查找：
  SQL> SELECT * FROM testsql WHERE test_data LIKE '%/&%' escape'/';  
  
  可以通过另外的方式进行转义：
  SQL> SELECT ASCII('&') FROM dual;
   
  SQL> SELECT * FROM testsql WHERE test_data LIKE '%'||chr(38)||'%';
        
  

• Oracle里的set命令整理： 官网链接
  可以执行show all查看以下set指令的当前所有环境变量值。
  SQL> set timing on;     //设置显示“已用时间：XXXX”
  SQL> set autotrace on;  //设置允许对执行的sql进行分析
  SQL> set trimout on;    //去除标准输出每行的拖尾空格，缺省为off
  SQL> set termout off;    //显示脚本中的命令的执行结果，缺省为on
  SQL> set trimspool on;   //去除重定向（spool）输出每行的拖尾空格，缺省为off
  SQL> set echo on;      //设置运行命令是是否显示语句
  SQL> set echo off;     //显示start启动的脚本中的每个sql命令，缺省为on
  SQL> set feedback on;   //设置显示“已选择XX行”
  SQL> set feedback off;  //关闭显示“已选择XX行”，缺省为on
  SQL> set colsep' ';     //输出分隔符
  SQL> set heading off;    //输出域标题，缺省为on
  SQL> set numwidth 12;    //输出number类型域长度，缺省为10
  
  一页中显示的行数可以使用set pagesize命令设置
  一行中显示的字符数可以使用set linesize命令设置
  SQL> set pagesize 30;    //输出每页行数。避免分页显示多个表头，可设定为0。
  SQL> set linesize 120;   //输出一行字符个数，缺省为80
  要达到一劳永逸的效果，可以修改D:\Oracle\product\11.2.0\dbhome_1\sqlplus\admin\glogin.sql,添加如下两行：
  set linesize=120;
  set pagesize=30;
  
  
• create user
  语法：create user 用户名 identified by 密码
  注意：新创建的用户是没有任何权限的，需要dba授权才能登陆(grant create session to 用户名)
  说明：当创建一个新用户后，如果该用户创建了任意一个数据对象后，oracle dbms会自动创建一个与该用户名一致的方案（schema）与该用户对应。所以，oracle不同用户，可以创建名字相同的表。
  提示：用指定用户登录plsql developer，在[my projects]中列出的对象都是属于该用户方案。
  
• &交互命令：
  查询条件时可以实现动态交互，输入查找条件。
  如下实现动态输入要查询的'ENAME'，示例查询所有名字以'R'结尾的员工。
  
  查询结果如下：
  
  
  
• 用户a如何访问另一用户b方案的数据对象？
  步骤一：先以用户b身份（或dba身份）登录；
  步骤二：将欲操作的数据对象授权给用户a。
  grant select[delete|update|insert|all] on 表名 to 用户a [with grant/admin option]
  说明：
  with grant option选项，表示得到对象权限的用户，可以把权限继续分配。
  with admin option选项，表示得到系统权限的用户，可以把权限继续分配。
  
  示例：用户a把权限给用户b，用户b再把权限分配给c，并在查询后回收权限：
  conn 用户a/a密码;
  grant all on 表名 to 用户b with grant option;
  conn 用户b/b密码;
  grant select on 用户a.表名 to 用户c;  //如果没有with grant option则此行操作是不行的。
  select * from 用户a.表名;
  conn 用户b/b密码;   //注意，是以用户b身份回收c的权限
  revoke select on 用户a.表名 from 用户c;
  
  如果用户a中存在同名的表，可以用方案名做前缀以说明操作的是哪个方案下的数据对象。
  示例：假定用户a登录身份下查询方案b的表： select * from 用户b.表名;
  
  
• revoke，权限/角色收回
  注意：不能自己回收自己。
  revoke connect from 用户名;
  revoke resource from 用户名；
  
  
• 删除用户
  drop user 用户名 [cascade];
  说明：如果被删除用户已经创建过数据对象，则删除时必须加cascade选项，表示删除用户时连数据对象一同删除。 如果想保留数据，建议锁定用户。
  
  
• 权限说明：
  系统权限：和数据库管理相关的权限，create table、create session、create index、create view、create sequence、create trigger
  对象权限： 对表等对象的增删改查等操作的权限；
  预定义角色: dba（管理员）、connect（登陆权限）、resource（资源操作权限）
  
  grant connect to 用户名;    #授权，登陆权限
  grant resource to 用户名;   #授权，资源操作权限
  
  
• passw[ord]修改密码：
  修改用户密码，只有sys和system及有alter权限的用户有权限修改。。
  修改已登录用户密码，可以不加用户名。
  如果是修改别的用户密码，必须加用户名。
  
  
• Oracle启动流程：
  1、启动监听服务：lsnrctl start
  2、启动数据库实例： oradmin -startup -sid 数据库实例名
  
  
• 如何查看oracle的sid实例名?
  【方法0】服务中查看，服务名后面部分(本例为ORCL)就是服务器的实例名。
  
  【方法1】只有dba权限才可以。
  SQL> select INSTANCE_NAME from v$instance;
  
  【方法2】sqlplus / as sysdba
  show parameter instance_name
  
  
• profile管理用户口令：
  profile是口令限制、资源限制的命令集合，当建立数据时，oracle会自动建立名称为default的profile，如果用户没有指定profile选项，会将default profile分配给用户。
  
  1）账户限制：
  指定账户登陆时最多可以输入密码的次数，也可以指定账户锁定时间。一般用dba身份去执行。
  #1.创建profile
  create profile lock_account limit failed_login_attempts 3
  password_lock_time 2; 
  
  #2.profile应用
  alter user scott profile lock_account;
  
  2）解锁用户： alter user 用户名 account unlock;
  
  3）终止指令：
  实现让用户周期性的修改密码。需要dba身份执行。
  如，实现要求用户每隔5天修改登录密码，宽限日期2天。
  #1.创建profile
  create profile ChangePwRegular limit password_life_time 5
  password_grace_time 2;
  #2.应用profile
  alter user scott profile ChangePwRegular;
  解锁方法：同2)。
  
  4) 删除profile：  drop profile profile_name;
  说明：删除profile后，应用于用户的约束也随即失效。
  
  
• 如何禁用指定用户，同时又保留该用户的数据对象？
  1）锁定账户： alter user scott account lock;
  2）此时该账户已经无法登陆数据库，但登陆system身份，仍然可以使用其数据对象：
  conn system/密码;
  select * from scott.emp;
  
  
• PLSQL Developer安装后无法连接本机Oracle ORA-12547、ORA-12518、ORA-12505问题。
  新手，刚接触oracle。如题，安装PLSQL developer后立即运行，连接出错：
  
  因为是太新的新手，明显的异常就没注意，其实列表里的database根本就不是自己创建的……
  
  百度异常码，搜到这个解决方法，修改listener.ora：https://www.linuxidc.com/Linux/2014-11/109686.htm
  
  修改listener.ora后出现新的ORA-12505错误，参照下面文章：
  http://blog.youkuaiyun.com/zxp2624161989/article/details/52249206
  看到这篇文章，才幡然醒悟，PLSQL developer安装后是否需要特殊配置才能使用？？看到视频教程里貌似没讲这部分，都是直接使用了……
  于是，一阵百度设置方法，果然PLSQL developer安装后需要配置oracle监听文件：
  找到oracle安装目录D:\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN路径下的tnsnames.ora文件，用编辑器打开，做如下修改：
  
  圈选内容为新增oracle的sid内容，注意Service_name根据实际填写，语法格式如下：
  本地实例名 =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 数据库IP地址)(PORT = 服务器端口号))
                  )
               (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = 数据库服务名)
               )
          )
  
  
  修改D:\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora，屏蔽如下两行：
  
  重启监听服务，终于折腾好了…… 新手，贵在折腾！……
  ~~~~(>_<)~~~~……说出来都是泪啊！