Lihb +

Oracle培训笔记

2014-07-24

是由实例（instance）和库文件组成的。

  1. C:\>sqlplus / as sysdba;
  3. SQL*Plus: Release 11.2.0.1.0 Production on 星期四 7月 24 22:37:49 2014
  5. Copyright (c) 1982, 2010, Oracle.  All rights reserved.
  8. 连接到:
  9. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
  10. With the Partitioning, OLAP, Data Mining and Real Application Testing options

两种方法：

  1. 1.
  2. SQL> select instance_name from v$instance;
  4. INSTANCE_NAME
  5. --------------------------------
  6. orcl
  7. 2.
  8. SQL> select name from v$database;//这种常用
  10. NAME
  11. ------------------
  12. ORCL

ps：凡是以 v$表名的表都为系统表

2种方法：

  1. 1.
  2. SQL> shutdown immediate;
  3. 数据库已经关闭。
  4. 已经卸载数据库。
  5. ORACLE 例程已经关闭。
  7. 2.
  8. SQL> shutdown abort; //此方法是强制关闭，少用。
  9. ORACLE 例程已经关闭。
  1. SQL> startup;
  2. ORACLE 例程已经启动。
  4. Total System Global Area  431038464 bytes
  5. Fixed Size                  1375088 bytes
  6. Variable Size             289408144 bytes
  7. Database Buffers          134217728 bytes
  8. Redo Buffers                6037504 bytes
  9. 数据库装载完毕。
  10. 数据库已经打开。

startup命令实际上包含了三条命令来打开数据库。

  1. 1.
  2. SQL> startup nomount;  //启动数据库的参数文件
  3. ORACLE 例程已经启动。
  5. Total System Global Area  431038464 bytes
  6. Fixed Size                  1375088 bytes
  7. Variable Size             289408144 bytes
  8. Database Buffers          134217728 bytes
  9. Redo Buffers                6037504 bytes
  11. 2.
  12. SQL> alter database mount; // 启动数据库的控制文件
  14. 数据库已更改。
  16. 3.
  17. SQL> alter database open; // 启动数据库的数据库文件和日志文件
  19. 数据库已更改。
  1. SQL> select status from v$instance;
  3. STATUS
  4. ------------------------
  5. OPEN   //open为正常

Oracle的功能由管理+开发完成

ps：逻辑结构中的数据块和是和物理结构中的OS块相对应的。

pps: 一个表空间由一个或者多个数据文件组成，整个oracle是由表空间组成的。

查看表空间命令

  1. SQL> select name from v$tablespace; // 查询系统自带的6个表空间
  3. NAME
  4. --------------------------------------
  5. SYSTEM    //系统表空间
  6. SYSAUX    //系统表空间
  7. UNDOTBS1  //回滚表空间
  8. USERS     //用户表空间
  9. TEMP      //临时表空间
  10. EXAMPLE   //例子表空间
  12. 已选择6行。

一般用户的表空间分为数据表空间和临时表空间两种。

自定义数据表空间

  1. SQL> create tablespace tbs1 datafile 'c:\tbs\tbs1.dbf' size 10m //基础语法
  2.   2  autoextend on next 1m   //开启自增模式，每次增加1m
  3.   3  maxsize 20m;   // 最大值为20m
  5. 表空间已创建。

ps：其中tbs1为表空间的名字，datafile表示数据文件，‘c:\tbs\tbs1.dbf’表示数据文件存放的位置，size 10m表示初始大小。

自定义临时表空间

  1. SQL> create temporary tablespace usertemptbs1 tempfile 'c:\tbs\tempdbs1.dbf' //基础语法
  2.   2  size 10m               //初始值为10m
  3.   3  autoextend on next 1m  //开启自增模式，每次增加1m
  4.   4  maxsize 20m;           // 最大值为20m
  6. 表空间已创建。

ps：其中usertemptbs1为临时表空间的名字，tempfile表示临时文件，‘c:\tbs\tempdbs1.dbf’表示临时文件存放的位置，size 10m表示初始大小。

ps：数据文件的位置如下图所示：

数据文件

删除表空间命令

  1. SQL> drop tablespace tbs1; //表空间中无数据
  3. 表空间已删除。
  5. SQL> drop tablespace tbs1 including contents; //表空间中有数据
  7. 表空间已删除。

创建用户命令

  1. SQL> create user test identified by 123; //用户名为test，密码为:123
  3. 用户已创建。

切换用户

  1. SQL> create user test identified by 123;  //创建用户
  3. 用户已创建。
  5. SQL> conn test/123; 切换用户
  6. ERROR:
  7. ORA-01045: user TEST lacks CREATE SESSION privilege; logon denied  //登录不了数据库，因为没有登录权限
  10. 警告: 您不再连接到 ORACLE。
  11. SQL> conn /as sysdba; // 切换到管理员账户
  12. 已连接。
  13. SQL> grant connect to test; // 给test账户connect权限
  15. 授权成功。
  17. SQL> conn test/123; //再次切换，登录成功。
  18. 已连接。

为用户创建的表指定表空间

  1. SQL> create table stu1(id int, age int, name varchar(20)) tablespace tbs1;
  2. create table stu1(id int, age int, name varchar(20)) tablespace tbs1
  3. *
  4. 第 1 行出现错误:
  5. ORA-01031: 权限不足 //因为新建的test用户只给了connect权限，没给resource权限（该权限涉及到表的创建、删除以及增删改查）。
  8. SQL> conn /as sysdba;
  9. 已连接。
  10. SQL> grant resource to test;  //切换到管理员账户给test账户赋予resource权限。
  12. 授权成功。
  14. SQL> conn test/123;//切换回test账户
  15. 已连接。
  16. SQL> create table stu1(id int, age int, name varchar(20)) tablespace tbs1; // 建表同时为用户显式的指定表空间
  18. 表已创建。

查询用户中的表所在的表空间

  1. SQL> create table stu2(id int, age int, name varchar(20)); //新建表stu2，不显式的指定表空间
  3. 表已创建。
  5. SQL>  select table_name,tablespace_name from user_tables; //查询用户中的表所在的表空间
  7. TABLE_NAME                     TABLESPACE_NAME
  8. ------------------------------ ------------------------------
  9. STU1                           TBS1
  10. STU2                           USERS  //默认放在Users表空间中

为用户指定默认的数据表空间和临时表空间

  1. SQL> alter user test default tablespace tbs1; //指定默认的数据表空间
  3. User altered
  5. SQL> alter user test  temporary tablespace usertemptbs1; //指定默认的临时表空间
  7. User altered
  9. SQL> create table stu3(id int, name varchar(30), age int);//新建表stu3,没有显式的给该表指定表空间
  11. Table created
  13. SQL> select table_name,tablespace_name from user_tables;//查看用户的表和表所在的表空间
  15. TABLE_NAME                     TABLESPACE_NAME
  16. ------------------------------ ------------------------------
  17. STU1                           TBS1
  18. STU2                           USERS
  19. STU3                           TBS1  //默认表空间已经改成tbs1了

常用命令

  1. 1. conn / as sysdba; //用sys账户登录数据库
  2. 2. create user test identified by 123; //创建用户及密码
  3. 3. grant connect,resource to test; //给test用户赋予连接数据库及创建表的权限
  4. 4. conn test/123; //普通用户登录数据库
  5. 5.

layout: post title: Oracle培训笔记一 description: oracle启动、用户表空间、权限、角色、 category: blog —

是由实例（instance）和库文件组成的。

  1. C:\>sqlplus / as sysdba;
  3. SQL*Plus: Release 11.2.0.1.0 Production on 星期四 7月 24 22:37:49 2014
  5. Copyright (c) 1982, 2010, Oracle.  All rights reserved.
  8. 连接到:
  9. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
  10. With the Partitioning, OLAP, Data Mining and Real Application Testing options

两种方法：

  1. 1.
  2. SQL> select instance_name from v$instance;
  4. INSTANCE_NAME
  5. --------------------------------
  6. orcl
  7. 2.
  8. SQL> select name from v$database;//这种常用
  10. NAME
  11. ------------------
  12. ORCL

ps：凡是以 v$表名的表都为系统表

2种方法：

  1. 1.
  2. SQL> shutdown immediate;
  3. 数据库已经关闭。
  4. 已经卸载数据库。
  5. ORACLE 例程已经关闭。
  7. 2.
  8. SQL> shutdown abort; //此方法是强制关闭，少用。
  9. ORACLE 例程已经关闭。
  1. SQL> startup;
  2. ORACLE 例程已经启动。
  4. Total System Global Area  431038464 bytes
  5. Fixed Size                  1375088 bytes
  6. Variable Size             289408144 bytes
  7. Database Buffers          134217728 bytes
  8. Redo Buffers                6037504 bytes
  9. 数据库装载完毕。
  10. 数据库已经打开。

startup命令实际上包含了三条命令来打开数据库。

  1. 1.
  2. SQL> startup nomount;  //启动数据库的参数文件
  3. ORACLE 例程已经启动。
  5. Total System Global Area  431038464 bytes
  6. Fixed Size                  1375088 bytes
  7. Variable Size             289408144 bytes
  8. Database Buffers          134217728 bytes
  9. Redo Buffers                6037504 bytes
  11. 2.
  12. SQL> alter database mount; // 启动数据库的控制文件
  14. 数据库已更改。
  16. 3.
  17. SQL> alter database open; // 启动数据库的数据库文件和日志文件
  19. 数据库已更改。
  1. SQL> select status from v$instance;
  3. STATUS
  4. ------------------------
  5. OPEN   //open为正常

Oracle的功能由管理+开发完成

ps：逻辑结构中的数据块和是和物理结构中的OS块相对应的。

pps: 一个表空间由一个或者多个数据文件组成，整个oracle是由表空间组成的。

查看表空间命令

  1. SQL> select name from v$tablespace; // 查询系统自带的6个表空间
  3. NAME
  4. --------------------------------------
  5. SYSTEM    //系统表空间
  6. SYSAUX    //系统表空间
  7. UNDOTBS1  //回滚表空间
  8. USERS     //用户表空间
  9. TEMP      //临时表空间
  10. EXAMPLE   //例子表空间
  12. 已选择6行。

一般用户的表空间分为数据表空间和临时表空间两种。

自定义数据表空间

  1. SQL> create tablespace tbs1 datafile 'c:\tbs\tbs1.dbf' size 10m //基础语法
  2.   2  autoextend on next 1m   //开启自增模式，每次增加1m
  3.   3  maxsize 20m;   // 最大值为20m
  5. 表空间已创建。

ps：其中tbs1为表空间的名字，datafile表示数据文件，‘c:\tbs\tbs1.dbf’表示数据文件存放的位置，size 10m表示初始大小。

自定义临时表空间

  1. SQL> create temporary tablespace usertemptbs1 tempfile 'c:\tbs\tempdbs1.dbf' //基础语法
  2.   2  size 10m               //初始值为10m
  3.   3  autoextend on next 1m  //开启自增模式，每次增加1m
  4.   4  maxsize 20m;           // 最大值为20m
  6. 表空间已创建。

ps：其中usertemptbs1为临时表空间的名字，tempfile表示临时文件，‘c:\tbs\tempdbs1.dbf’表示临时文件存放的位置，size 10m表示初始大小。

ps：数据文件的位置如下图所示：

数据文件

增大表空间大小

  1. SQL> alter tablespace tbs2 add datafile 'c:\tablespace\tbs0725.dbf' size 10m;  //为tbs2表空间怎加一个数据文件
  3. Tablespace altered
  1. SQL> alter database datafile 'c:\tablespace\tbs2.dbf' resize 25m; // 直接修改tbs2表空间的大小
  3. Database altered

删除表空间命令

  1. SQL> drop tablespace tbs1; //表空间中无数据
  3. 表空间已删除。
  5. SQL> drop tablespace tbs1 including contents; //表空间中有数据
  7. 表空间已删除。

创建用户命令

  1. SQL> create user test identified by 123; //用户名为test，密码为:123
  3. 用户已创建。

切换用户

  1. SQL> create user test identified by 123;  //创建用户
  3. 用户已创建。
  5. SQL> conn test/123; 切换用户
  6. ERROR:
  7. ORA-01045: user TEST lacks CREATE SESSION privilege; logon denied  //登录不了数据库，因为没有登录权限
  10. 警告: 您不再连接到 ORACLE。
  11. SQL> conn /as sysdba; // 切换到管理员账户
  12. 已连接。
  13. SQL> grant connect to test; // 给test账户connect权限
  15. 授权成功。
  17. SQL> conn test/123; //再次切换，登录成功。
  18. 已连接。

为用户创建的表指定表空间

  1. SQL> create table stu1(id int, age int, name varchar(20)) tablespace tbs1;
  2. create table stu1(id int, age int, name varchar(20)) tablespace tbs1
  3. *
  4. 第 1 行出现错误:
  5. ORA-01031: 权限不足 //因为新建的test用户只给了connect权限，没给resource权限（该权限涉及到表的创建、删除以及增删改查）。
  8. SQL> conn /as sysdba;
  9. 已连接。
  10. SQL> grant resource to test;  //切换到管理员账户给test账户赋予resource权限。
  12. 授权成功。
  14. SQL> conn test/123;//切换回test账户
  15. 已连接。
  16. SQL> create table stu1(id int, age int, name varchar(20)) tablespace tbs1; // 建表同时为用户显式的指定表空间
  18. 表已创建。

查询用户中的表所在的表空间

  1. SQL> create table stu2(id int, age int, name varchar(20)); //新建表stu2，不显式的指定表空间
  3. 表已创建。
  5. SQL>  select table_name,tablespace_name from user_tables; //查询用户中的表所在的表空间
  7. TABLE_NAME                     TABLESPACE_NAME
  8. ------------------------------ ------------------------------
  9. STU1                           TBS1
  10. STU2                           USERS  //默认放在Users表空间中

为用户指定默认的数据表空间和临时表空间

  1. SQL> alter user test default tablespace tbs1; //指定默认的数据表空间
  3. User altered
  5. SQL> alter user test  temporary tablespace usertemptbs1; //指定默认的临时表空间
  7. User altered
  9. SQL> create table stu3(id int, name varchar(30), age int);//新建表stu3,没有显式的给该表指定表空间
  11. Table created
  13. SQL> select table_name,tablespace_name from user_tables;//查看用户的表和表所在的表空间
  15. TABLE_NAME                     TABLESPACE_NAME
  16. ------------------------------ ------------------------------
  17. STU1                           TBS1
  18. STU2                           USERS
  19. STU3                           TBS1  //默认表空间已经改成tbs1了

分类：

查看dba用户的权限

  1. QL> conn / as sysdba
  2. Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
  3. Connected as SYS
  5. SQL> select * from dba_sys_privs;
  7. GRANTEE                        PRIVILEGE                                ADMIN_OPTION
  8. ------------------------------ ---------------------------------------- ------------
  9. DBA                            CREATE SESSION                           YES
  10. DBA                            ALTER SESSION                            YES
  11. DBA                            DROP TABLESPACE                          YES
  12. DBA                            BECOME USER                              YES
  13. DBA                            DROP ROLLBACK SEGMENT                    YES
  14. ......
  15. ......
  16. ......
  17. ......
  18. .....
  19. 838 rows selected //总共838种权限

授予权限

  1. SQL> conn / as sysdba //要授系统权限必须切换到dba账户
  2. Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
  3. Connected as SYS
  5. SQL> grant connect,resource,dba to test; //给test账户授予权限
  7. Grant succeeded

回收权限

  1. SQL> revoke resource from test; // 回收权限命令
  3. Revoke succeeded

ps：系统权限不级联：dba账户给用户a授予dba权限，用户a给用户b授予权限，如果回收a的权限，b的权限不会受到影响。

关于实体权限

实体权限包括：select、update、delete、insert、alter等

使用范围：平等用户之间，如用户a给用户b查询自己表的权限。

  1. 现在有test、lhb两个账户，test用户想要查询lhb用户中的stu1表的数据
  3. （前提是test账户没有dba权限）
  5. 1.没给test账户赋予select权限之前，查询数据
  7. SQL> conn test/123
  8. Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
  9. Connected as test
  11. SQL> select * from lhb.stu1;
  13. select * from lhb.stu1
  15. ORA-00942: 表或视图不存在  //查询不了数据
  17. SQL> conn lhb/123
  18. Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
  19. Connected as lhb
  21. SQL> grant select on stu1 to test;  // 切换到lhb用户给test账户针对stu1表授予select权限
  23. Grant succeeded
  25. SQL> conn test/123
  26. Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
  27. Connected as test
  29. SQL> select * from lhb.stu1;  // 切换到test账户，再次查询；成功查询到数据
  31.                                      ID   NAME                 EMAIL                                                                                  AGE
  32.                                   ------ -------             ----------------                                                                  -------------
  33.                                     100  java100              java100@126.com                                                                         33
  34.                                     101  java101              java101@126.com                                                                        323
  35.                                     201  java201              java201@126.com                                                                       3233
  36.                                     301  java301              java301@125.com                                                                         44
  37.                                     401  java401              java401@125.com                                                                        445
  39. 5 rows selected

ps：

给用户一次性授予增删改查权限。

  1. SQL> grant all on stu1 to test; // all 关键字
  3. Grant succeeded

主要是四个步骤：创建角色、赋予角色权限、将角色分配给用户、删除角色

  1. SQL> create role role1;
  3. Role created
  1. SQL> grant connect,resource to role1;
  3. Grant succeeded
  1. SQL> grant role1 to test; //将角色分配给test账户
  3. Grant succeeded
  1. SQL> drop role role1;
  3. Role dropped

ps:删除角色后，用户的权限也会相应消失（举个例子，角色的功能类似于一个小组的组长，如组长是小明，小明拥有组长的角色，他就有了组长的权限，假设哪天不需要小组长了，小明的权限也就消失了）。

c8c67df54592a0a74cda9a33ef5d49577b8549c6:_posts/blog/2014-07-24-Oracle培训笔记一.md

点击查看评论

Blog

Knowledge

Project