Oracle知识点记录

这篇博客详细记录了Oracle学习过程中的关键知识点,包括常用命令、数据类型、登录机制、登录认证方式的修改、浏览器登录方法、管理员密码遗忘的处理以及特殊符号的含义。此外,还涵盖了Oracle的权限管理、对象访问、用户管理、口令策略和PLSQL Developer的连接问题解决方案。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

    记录一下本人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,屏蔽如下两行:

    重启监听服务,终于折腾好了…… 新手,贵在折腾!……
    ~~~~(>_<)~~~~……说出来都是泪啊!
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值