Oracle培训笔记
2014-07-24
oracle体系：
是由实例（instance）和库文件组成的。
管理员方式连接数据库
C:\>sqlplus / as sysdba;
SQL*Plus: Release 11.2.0.1.0 Production on 星期四 7月 24 22:37:49 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
查看数据库名称命令
两种方法：
1.
SQL> select instance_name from v$instance;
INSTANCE_NAME
--------------------------------
orcl
2.
SQL> select name from v$database;//这种常用
NAME
------------------
ORCL
ps：凡是以 v$表名的表都为系统表
关闭数据库命令
2种方法：
1.
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
2.
SQL> shutdown abort; //此方法是强制关闭，少用。
ORACLE 例程已经关闭。
打开数据库命令
SQL> startup;
ORACLE 例程已经启动。
Total System Global Area 431038464 bytes
Fixed Size 1375088 bytes
Variable Size 289408144 bytes
Database Buffers 134217728 bytes
Redo Buffers 6037504 bytes
数据库装载完毕。
数据库已经打开。
startup命令实际上包含了三条命令来打开数据库。
1.
SQL> startup nomount; //启动数据库的参数文件
ORACLE 例程已经启动。
Total System Global Area 431038464 bytes
Fixed Size 1375088 bytes
Variable Size 289408144 bytes
Database Buffers 134217728 bytes
Redo Buffers 6037504 bytes
2.
SQL> alter database mount; // 启动数据库的控制文件
数据库已更改。
3.
SQL> alter database open; // 启动数据库的数据库文件和日志文件
数据库已更改。
查看数据库的状态命令
SQL> select status from v$instance;
STATUS
------------------------
OPEN //open为正常
要点一
Oracle的功能由管理+开发完成
-
管理分为逻辑结构和物理结构
-
逻辑结构：表空间——段——区——数据块
-
物理结构：数据文件——OS块
-
ps：逻辑结构中的数据块和是和物理结构中的OS块相对应的。
pps: 一个表空间由一个或者多个数据文件组成，整个oracle是由表空间组成的。
查看表空间命令
SQL> select name from v$tablespace; // 查询系统自带的6个表空间
NAME
--------------------------------------
SYSTEM //系统表空间
SYSAUX //系统表空间
UNDOTBS1 //回滚表空间
USERS //用户表空间
TEMP //临时表空间
EXAMPLE //例子表空间
已选择6行。
一般用户的表空间分为数据表空间和临时表空间两种。
自定义数据表空间
SQL> create tablespace tbs1 datafile 'c:\tbs\tbs1.dbf' size 10m //基础语法
2 autoextend on next 1m //开启自增模式，每次增加1m
3 maxsize 20m; // 最大值为20m
表空间已创建。
ps：其中tbs1为表空间的名字，datafile表示数据文件，‘c:\tbs\tbs1.dbf’表示数据文件存放的位置，size 10m表示初始大小。
自定义临时表空间
SQL> create temporary tablespace usertemptbs1 tempfile 'c:\tbs\tempdbs1.dbf' //基础语法
2 size 10m //初始值为10m
3 autoextend on next 1m //开启自增模式，每次增加1m
4 maxsize 20m; // 最大值为20m
表空间已创建。
ps：其中usertemptbs1为临时表空间的名字，tempfile表示临时文件，‘c:\tbs\tempdbs1.dbf’表示临时文件存放的位置，size 10m表示初始大小。
ps：数据文件的位置如下图所示：
删除表空间命令
SQL> drop tablespace tbs1; //表空间中无数据
表空间已删除。
SQL> drop tablespace tbs1 including contents; //表空间中有数据
表空间已删除。
创建用户命令
SQL> create user test identified by 123; //用户名为test，密码为:123
用户已创建。
切换用户
SQL> create user test identified by 123; //创建用户
用户已创建。
SQL> conn test/123; 切换用户
ERROR:
ORA-01045: user TEST lacks CREATE SESSION privilege; logon denied //登录不了数据库，因为没有登录权限
警告: 您不再连接到 ORACLE。
SQL> conn /as sysdba; // 切换到管理员账户
已连接。
SQL> grant connect to test; // 给test账户connect权限
授权成功。
SQL> conn test/123; //再次切换，登录成功。
已连接。
为用户创建的表指定表空间
SQL> create table stu1(id int, age int, name varchar(20)) tablespace tbs1;
create table stu1(id int, age int, name varchar(20)) tablespace tbs1
*
第 1 行出现错误:
ORA-01031: 权限不足 //因为新建的test用户只给了connect权限，没给resource权限（该权限涉及到表的创建、删除以及增删改查）。
SQL> conn /as sysdba;
已连接。
SQL> grant resource to test; //切换到管理员账户给test账户赋予resource权限。
授权成功。
SQL> conn test/123;//切换回test账户
已连接。
SQL> create table stu1(id int, age int, name varchar(20)) tablespace tbs1; // 建表同时为用户显式的指定表空间
表已创建。
查询用户中的表所在的表空间
SQL> create table stu2(id int, age int, name varchar(20)); //新建表stu2，不显式的指定表空间
表已创建。
SQL> select table_name,tablespace_name from user_tables; //查询用户中的表所在的表空间
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
STU1 TBS1
STU2 USERS //默认放在Users表空间中
为用户指定默认的数据表空间和临时表空间
SQL> alter user test default tablespace tbs1; //指定默认的数据表空间
User altered
SQL> alter user test temporary tablespace usertemptbs1; //指定默认的临时表空间
User altered
SQL> create table stu3(id int, name varchar(30), age int);//新建表stu3,没有显式的给该表指定表空间
Table created
SQL> select table_name,tablespace_name from user_tables;//查看用户的表和表所在的表空间
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
STU1 TBS1
STU2 USERS
STU3 TBS1 //默认表空间已经改成tbs1了
常用命令
1. conn / as sysdba; //用sys账户登录数据库
2. create user test identified by 123; //创建用户及密码
3. grant connect,resource to test; //给test用户赋予连接数据库及创建表的权限
4. conn test/123; //普通用户登录数据库
5.
=======
layout: post title: Oracle培训笔记一 description: oracle启动、用户表空间、权限、角色、 category: blog —
oracle体系：
是由实例（instance）和库文件组成的。
管理员方式连接数据库
C:\>sqlplus / as sysdba;
SQL*Plus: Release 11.2.0.1.0 Production on 星期四 7月 24 22:37:49 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
查看数据库名称命令
两种方法：
1.
SQL> select instance_name from v$instance;
INSTANCE_NAME
--------------------------------
orcl
2.
SQL> select name from v$database;//这种常用
NAME
------------------
ORCL
ps：凡是以 v$表名的表都为系统表
关闭数据库命令
2种方法：
1.
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
2.
SQL> shutdown abort; //此方法是强制关闭，少用。
ORACLE 例程已经关闭。
打开数据库命令
SQL> startup;
ORACLE 例程已经启动。
Total System Global Area 431038464 bytes
Fixed Size 1375088 bytes
Variable Size 289408144 bytes
Database Buffers 134217728 bytes
Redo Buffers 6037504 bytes
数据库装载完毕。
数据库已经打开。
startup命令实际上包含了三条命令来打开数据库。
1.
SQL> startup nomount; //启动数据库的参数文件
ORACLE 例程已经启动。
Total System Global Area 431038464 bytes
Fixed Size 1375088 bytes
Variable Size 289408144 bytes
Database Buffers 134217728 bytes
Redo Buffers 6037504 bytes
2.
SQL> alter database mount; // 启动数据库的控制文件
数据库已更改。
3.
SQL> alter database open; // 启动数据库的数据库文件和日志文件
数据库已更改。
查看数据库的状态命令
SQL> select status from v$instance;
STATUS
------------------------
OPEN //open为正常
要点一:用户表空间
Oracle的功能由管理+开发完成
-
管理分为逻辑结构和物理结构
-
逻辑结构：表空间——段——区——数据块
-
物理结构：数据文件——OS块
-
ps：逻辑结构中的数据块和是和物理结构中的OS块相对应的。
pps: 一个表空间由一个或者多个数据文件组成，整个oracle是由表空间组成的。
查看表空间命令
SQL> select name from v$tablespace; // 查询系统自带的6个表空间
NAME
--------------------------------------
SYSTEM //系统表空间
SYSAUX //系统表空间
UNDOTBS1 //回滚表空间
USERS //用户表空间
TEMP //临时表空间
EXAMPLE //例子表空间
已选择6行。
一般用户的表空间分为数据表空间和临时表空间两种。
自定义数据表空间
SQL> create tablespace tbs1 datafile 'c:\tbs\tbs1.dbf' size 10m //基础语法
2 autoextend on next 1m //开启自增模式，每次增加1m
3 maxsize 20m; // 最大值为20m
表空间已创建。
ps：其中tbs1为表空间的名字，datafile表示数据文件，‘c:\tbs\tbs1.dbf’表示数据文件存放的位置，size 10m表示初始大小。
自定义临时表空间
SQL> create temporary tablespace usertemptbs1 tempfile 'c:\tbs\tempdbs1.dbf' //基础语法
2 size 10m //初始值为10m
3 autoextend on next 1m //开启自增模式，每次增加1m
4 maxsize 20m; // 最大值为20m
表空间已创建。
ps：其中usertemptbs1为临时表空间的名字，tempfile表示临时文件，‘c:\tbs\tempdbs1.dbf’表示临时文件存放的位置，size 10m表示初始大小。
ps：数据文件的位置如下图所示：
增大表空间大小
- 方法一：增加数据文件
SQL> alter tablespace tbs2 add datafile 'c:\tablespace\tbs0725.dbf' size 10m; //为tbs2表空间怎加一个数据文件
Tablespace altered
- 方法二：修改原数据文件大小
SQL> alter database datafile 'c:\tablespace\tbs2.dbf' resize 25m; // 直接修改tbs2表空间的大小
Database altered
删除表空间命令
SQL> drop tablespace tbs1; //表空间中无数据
表空间已删除。
SQL> drop tablespace tbs1 including contents; //表空间中有数据
表空间已删除。
创建用户命令
SQL> create user test identified by 123; //用户名为test，密码为:123
用户已创建。
切换用户
SQL> create user test identified by 123; //创建用户
用户已创建。
SQL> conn test/123; 切换用户
ERROR:
ORA-01045: user TEST lacks CREATE SESSION privilege; logon denied //登录不了数据库，因为没有登录权限
警告: 您不再连接到 ORACLE。
SQL> conn /as sysdba; // 切换到管理员账户
已连接。
SQL> grant connect to test; // 给test账户connect权限
授权成功。
SQL> conn test/123; //再次切换，登录成功。
已连接。
为用户创建的表指定表空间
SQL> create table stu1(id int, age int, name varchar(20)) tablespace tbs1;
create table stu1(id int, age int, name varchar(20)) tablespace tbs1
*
第 1 行出现错误:
ORA-01031: 权限不足 //因为新建的test用户只给了connect权限，没给resource权限（该权限涉及到表的创建、删除以及增删改查）。
SQL> conn /as sysdba;
已连接。
SQL> grant resource to test; //切换到管理员账户给test账户赋予resource权限。
授权成功。
SQL> conn test/123;//切换回test账户
已连接。
SQL> create table stu1(id int, age int, name varchar(20)) tablespace tbs1; // 建表同时为用户显式的指定表空间
表已创建。
查询用户中的表所在的表空间
SQL> create table stu2(id int, age int, name varchar(20)); //新建表stu2，不显式的指定表空间
表已创建。
SQL> select table_name,tablespace_name from user_tables; //查询用户中的表所在的表空间
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
STU1 TBS1
STU2 USERS //默认放在Users表空间中
为用户指定默认的数据表空间和临时表空间
SQL> alter user test default tablespace tbs1; //指定默认的数据表空间
User altered
SQL> alter user test temporary tablespace usertemptbs1; //指定默认的临时表空间
User altered
SQL> create table stu3(id int, name varchar(30), age int);//新建表stu3,没有显式的给该表指定表空间
Table created
SQL> select table_name,tablespace_name from user_tables;//查看用户的表和表所在的表空间
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
STU1 TBS1
STU2 USERS
STU3 TBS1 //默认表空间已经改成tbs1了
要点二：关于权限
分类：
-
系统权限：系统规定的用户使用的数据库的权限（针对用户）
-
connect：登陆数据库的权限
-
resource：资源权限，用户可以创建实体（如：创建表、创建视图等）
-
dba：管理员权限（系统管理的权限）
-
-
实体权限：某种权限用户对其他用户的表、视图的存取权限（针对表、视图）
查看dba用户的权限
QL> conn / as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SYS
SQL> select * from dba_sys_privs;
GRANTEE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
DBA CREATE SESSION YES
DBA ALTER SESSION YES
DBA DROP TABLESPACE YES
DBA BECOME USER YES
DBA DROP ROLLBACK SEGMENT YES
......
......
......
......
.....
838 rows selected //总共838种权限
授予权限
SQL> conn / as sysdba //要授系统权限必须切换到dba账户
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SYS
SQL> grant connect,resource,dba to test; //给test账户授予权限
Grant succeeded
回收权限
SQL> revoke resource from test; // 回收权限命令
Revoke succeeded
ps：系统权限不级联：dba账户给用户a授予dba权限，用户a给用户b授予权限，如果回收a的权限，b的权限不会受到影响。
关于实体权限
实体权限包括：select、update、delete、insert、alter等
使用范围：平等用户之间，如用户a给用户b查询自己表的权限。
现在有test、lhb两个账户，test用户想要查询lhb用户中的stu1表的数据
（前提是test账户没有dba权限）
1.没给test账户赋予select权限之前，查询数据
SQL> conn test/123
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as test
SQL> select * from lhb.stu1;
select * from lhb.stu1
ORA-00942: 表或视图不存在 //查询不了数据
SQL> conn lhb/123
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as lhb
SQL> grant select on stu1 to test; // 切换到lhb用户给test账户针对stu1表授予select权限
Grant succeeded
SQL> conn test/123
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as test
SQL> select * from lhb.stu1; // 切换到test账户，再次查询；成功查询到数据
ID NAME EMAIL AGE
------ ------- ---------------- -------------
100 java100 java100@126.com 33
101 java101 java101@126.com 323
201 java201 java201@126.com 3233
301 java301 java301@125.com 44
401 java401 java401@125.com 445
5 rows selected
ps：
给用户一次性授予增删改查权限。
SQL> grant all on stu1 to test; // all 关键字
Grant succeeded
要点三：角色
主要是四个步骤：创建角色、赋予角色权限、将角色分配给用户、删除角色
- 创建角色：
SQL> create role role1;
Role created
- 给角色赋予权限
SQL> grant connect,resource to role1;
Grant succeeded
- 给用户分配角色
SQL> grant role1 to test; //将角色分配给test账户
Grant succeeded
- 删除角色
SQL> drop role role1;
Role dropped
ps:删除角色后，用户的权限也会相应消失（举个例子，角色的功能类似于一个小组的组长，如组长是小明，小明拥有组长的角色，他就有了组长的权限，假设哪天不需要小组长了，小明的权限也就消失了）。
c8c67df54592a0a74cda9a33ef5d49577b8549c6:_posts/blog/2014-07-24-Oracle培训笔记一.md