记录一下本人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,屏蔽如下两行:
重启监听服务,终于折腾好了…… 新手,贵在折腾!……
~~~~(>_<)~~~~……说出来都是泪啊!