dml语句权限有哪些 oracle_oracle ddl语句和dml语句区别 - CSDN
精华内容
参与话题
  • 转载:https://blog.csdn.net/u013412772/article/details/52733050 可参考:https://www.cnblogs.com/roger112/p/7873341.html https://www.cnblogs.com/roger112/p/7729318.html ...

    转载:https://blog.csdn.net/u013412772/article/details/52733050
    可参考:https://www.cnblogs.com/roger112/p/7873341.html
    https://www.cnblogs.com/roger112/p/7729318.html

    Oracle数据库推荐以引用博客:

    http://blog.csdn.net/leshami/article/details/5611738
    http://www.cnblogs.com/jimeper/p/3394635.html
    http://blog.csdn.net/bob007/article/details/5871126

    本编文章内容分布:(1)介绍数据库常见分类(2)关系型数据库概念(3)Oracle数据库基本知识介绍(4)Oracle数据库权限介绍,其中权限的内容涉及范围较大,权限在本文中和用户、角色练习较为紧密,角色是权限的集合,属于Oracle对象,创建用户需要权限,而新创建的用户要赋予权限,并且还可以给对象赋予权限。等等这些基本操作都离不开权限,所以在本文中主要学习权限!用户!角色!

    关于用户与模式:

    用户:对数据库的访问,需要以适当用户身份通过验证,并具有相关权限来完成一系列动作.SYS用户,缺省始终创建,且未被锁定,拥有数据字典及其关联的所有对象.SYSTEM用户,缺省始终创建,且未被锁定,可以访问数据库内的所有对象

    模式(schema):是某个用户拥有所有对象的集合。具有创建对象权限并创建了对象的用户称为拥有某个模式
    注意:创建数据库对象(视图,表等)的任一用户都拥有一个以该用户名称开头的模式,且被视为模式用户

    一、数据库基本知识

    小型数据库:没有安全性
                   Access
    
    中型数据库:有一定的安全性
                   SQL Server
                   MySQL    
    
    大型数据库:安全性最高 
                   Oracle:性能高,市场需求量大
                       DB2
                       SYSBASE
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    (关系型)数据库相关概念

           数据库 Database DB 存储数据的仓库
    
           数据库管理系统 Database Management System DBMS
    
           用于管理数据库的软件
                 负责数据的检索、存储、安全、一致性控制等
                 对已有的数据进行处理,得到新的有用的数据
    
              关系型数据库
                 E.F.Codde 关于关系模型的论文
                 建立在关系模型基础上的数据库
                 拥有一组有描述的表格,这些表格的作用是装载数据
    
             表格
                 行:一条记录,一个实体,具体存在的事物(Java 中的一个对象)
                 列:字段、属性,用于描述事物的某一个方面(Java中对象的属性)
                 实体集:所有实体的集合
                 关系:实体间的关系
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    SQL(Structured Query Language)

           结构化查询语言
           是一种语言
           是一种规范 ANSI SQL-82 SQL-86 SQL-92 SQL-99
           所有主流数据库都遵守
    
           关系:有方向
              11 一对一
              对于实体集A中的每个实体,在实体集B中最多只有一个实体与之对应;反之,亦然。
              1:n 一对多:外键总设在多方
              对于实体集A中的每个实体,在实体集B中可以有多个实体与之对应。
              n:n 多对多:分解成两个一对多,添加一个中间表
              对于实体集A中的每个实体,在实体集B中可以有多个实体与之对应;反之,亦然。
    
           数据库设计:逻辑模型
              E-R图(Entity-Relation图)
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    三大范式(规范):

         1.一张表必须要有一个主键,最好选择与业务无关的逻辑主键;
           逻辑主键,业务主键
         2.外键引用主键
           逻辑外键
         3.不要出现冗余字段
           实际开发中,有时候并不遵守,一定要冗余,以保证数据的正确性,提高查询效率,空间换时间;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    关于Oracle数据库简单介绍

           1970 Oracle5
           最新  Oracle12c
           9i 10g 小 支持最新的SQL规范SQL-99
           安装要求:
              内存:100M+ 正常运行所需最小内存 (3G+ 电脑不卡)
              硬盘:1.5+G
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    Oracle数据库相关概念

           数据库:存储数据的仓库,主要指存放数据的文件,包括数据文件、控制文件、数据库日志文件
           数据名:用于唯一的标识数据库
           实例:一系列为当前数据库提供服务的内存和后台进程,内存与服务进程的集合称为一个实例
           实例名/SID/服务名:用于唯一的标识一个实例
           实例名与数据库名可以不一致
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    Oracle服务

           OracleService+SID,数据库启动的基础服务
           OracleOraDb10g_home1TNSListner,为客户端提供监听服务,提供连接服务
           OracleOraDb10g_home1iSQL*Plus,让客户端可以通过浏览器来对数据库进行操作
    
    • 1
    • 2
    • 3
    • 4

    数据库的启动和关闭

       登录用户必须是管理员用户
               启动:startup open
               关闭:shutdown immediate
            Oracle客户端工具
               SQL*Plus
                  三种启动方式
                   第一种、开始 → 程序 → Oracle → 应用开发工具 → SQL*Plus
                   第二种、命令行窗口 → sqlplus 
                   第三种、浏览器 → http://ip:5561/isqlplus
                   第四种、本方法前提是Oracle文件放在能在linux终端找到的位置,利用linux命令运行Oracle客户端,一般情况下都不会运用此方式来打开Oracle客户端,建议使用第一种、第二种启动方式。
                   SqlDeveloper(linux系统上修改文件的读取权限)
                   终端 → cd sqldevelper → chmod 777 *.sh  
                      → ./sqldeveloper.sh | bash sqldeveloper.sh | sh sqldeveloper.sh
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    二、监听器

    监听客户端连接请求,位于服务端的、独立运行的一个后台进程,一旦建立服务器与客户端的连接,之后客户端与服务的通信不再通过它

    命令行窗口 → sqlplus:启动一个SQL*Plus客户端工具
    或 → sqlplus username/password [as sysdba]
    或 → sqlplus /nolog:打开SQL*Plus,但不登录
    → conn username/password@192.168.7.58:1521/orcl
    → disconnect 断开连接

    spool 命令要写到的文件的路径;注意:当使用完毕后要使用spool off;

    conn /as sysdba 操作系统验证

    Oracle 登录验证:操作系统、数据库验证、密码文件

    三、权限

    Oracle在刚开始的入门知识方面和mysql在权限方面有着较大的不同,在Oracle中几乎所有的操作(创建用户、创建表、删除表等等一些增删改查在没有获得权限之前是不允许执行的)在没有获得权限之前都不能够执行,在mysql中则没有那么严格,所以有必要先了解一下Oracle中权限的分配。

    在创建用户之前,可以登录系统管理员权限去创建一个用户(因为系统管理员有这个权限),下面是三种用户分类:

    sys;   //系统管理员,拥有最高权限
    system;//本地管理员,次高权限
    scott; //普通用户,密码默认为tiger,默认未解锁
    
    • 1
    • 2
    • 3
    • 4

    在登陆的时候,可以选择下面三种登录方式:

    sqlplus / as sysdba;  //登陆sys帐户
    sqlplus sys as sysdba;//同上
    sqlplus scott/tiger;  //登陆普通用户scott
    
    • 1
    • 2
    • 3
    • 4

    比如创建一个用户和给创建的用户分配权限代码示例:(下面创建用户是在系统管理员的身份下创建的,并且系统管理员本身也有这个权限,但是如果没有赋予普通用户创建用户的权限,那么普通用户创建用户这个操作就会失败。)

    3.1、在Oracle中创建一个新用户

    (用户名:zhangsan 密码:zhangsan, 默认登陆是在users表中,并分配10M的空间)

    ·先用超级管理员连上:sqlplus
    ·输入用户名及密码:scott/tiger
    ·连接成功后,使用:conn sys/sysdba as sysdba;登入超级管理员后,输入如下命令:
    create user zhangsan identified by zhangsan default tablespace users quota 10M on users
    分配权限给新用户zhangsan
    grant create session, create table, create view to zhangsan
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    下面语句可以在sys系统管理员登录的情况下创建的

    create user 用户名 identified by 密码;//在管理员帐户下,创建用户
    alert user scott identified by tiger;//修改密码    
    
    • 1
    • 2
    • 3

    系统权限: 允许用户执行特定的数据库动作,如创建表、创建索引、连接实例等
    对象权限: 允许用户操纵一些特定的对象,如读取视图,可更新某些列、执行存储过程等

    3.2系统权限和对象权限:

    (1)系统权限:

    grant 权限列表,.. to username [with admin option 同时获得权限分配权];
    revoke 权限列表,.. from usernam;    
    
    a.常见的系统权限
    
       CREATE SESSION                     创建会话
    
       CREATE SEQUENCE                    创建序列
    
       CREATE SYNONYM                     创建同名对象
    
       CREATE TABLE                       在用户模式中创建表
    
       CREATE ANY TABLE                   在任何模式中创建表
    
       DROP TABLE                         在用户模式中删除表
    
       DROP ANY TABLE                     在任何模式中删除表
    
       CREATE PROCEDURE                   创建存储过程
    
       EXECUTE ANY PROCEDURE              执行任何模式的存储过程
    
       CREATE USER                        创建用户
    
       DROP USER                          删除用户
    
      b.授予用户系统权限
    
               GRANT privilege [, privilege...] TO user [, user| role, PUBLIC...]
    
               [WITH ADMIN OPTION];
    
               PUBLIC     所有用户
    
               WITH ADMIN OPTION 使用户同样具有分配权限的权利,可将此权限授予别人
    
     c.使用系统权限
    
        --使用robinson具有创建会话、创建表
    
           SQL> CREATE TABLE tb1 AS SELECT * FROM USER_TABLES; --下面提示没有权限在users表空间创建对象
    
           CREATE TABLE tb1 AS SELECT * FROM USER_TABLES
    
    
           ERROR at line 1:
    
           ORA-01950: no privileges on tablespace 'USERS'
    
    
           SQL> CONN sys as sysdba;  --使用sys帐户登陆并为robinson在users表空间指定配额后可以创建表tb1
    
           Enter password:
    
           Connected.
    
           SQL> ALTER USER robinson QUOTA 10M ON USERS;
    
           User altered.
    
           SQL> CONN robinson/lion;
    
           Connected.
    
           SQL> CREATE TABLE tb1 AS SELECT * FROM USER_TABLES;
    
           Table created.
    
      d.查看系统权限
    
                dba_sys_privs  --针对所有用户被授予的系统权限
    
                user_sys_privs --针对当前登陆用户被授予的系统权限
    
      e.回收系统权限
    
      REVOKE {privilege | role} FROM {user_name | role_name | PUBLIC}
    
           --下面的示例中并没有回收掉原来由scott授予给robisnon EXECUTE ANY PROCEDURE 的权限
    
           SQL> REVOKE EXECUTE ANY PROCEDURE FROM scott;
    
           Revoke succeeded.
    
           SQL> select grantee,privilege,admin_option from dba_sys_privs
    
             2  where grantee in ('SCOTT','ROBINSON')  and privilege = 'EXECUTE ANY PROCEDURE'
    
             3  order by grantee;
    
           GRANTEE                        PRIVILEGE                                ADM
    
           ------------------------------ ---------------------------------------- ---
    
           ROBINSON                       EXECUTE ANY PROCEDURE                    NO
    
    
    
       注意:对于使用with admin option 为某个用户授予系统权限,那么对于被这个用户授予相同权限的所有
    
           用户来说,取消该用户的系统权限并不会级联取消这些用户的相同权限
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103

    (2)对象权限

    grant 权限 on 对象名 to username[with grant option 同时获得权限分配权];

    revoke 权限 on 对象名 from username;

    不同的对象具有不同的对象权限,对象的拥有者拥有所有权限,对象的拥有者可以向外分配权限.关于授予系统权限与授予对象权限的语法差异:授予对象权限时需要指定关键字ON,从而能够确定权限所应用的对象.对于表和视图可以指定特定的列来授权。

    对象授权示例

    SQL> SHOW USER;
    USER is "SCOTT"
    
    SQL> GRANT SELECT ON emp TO robinson;
    Grant succeeded.
    
    SQL> GRANT UPDATE(sal,mgr) ON emp TO robinson WITH GRANT OPTION;
    Grant succeeded.
    
     --新创建一个用户john,使用robinson账户授予更新scott.emp(sal,mgr)的权限
    SQL> CREATE USER john IDENTIFIED BY john;
    User created.
    
    SQL> GRANT CREATE SESSION TO john;
    Grant succeeded.
    
    SQL> CONN ROBINSON/LION
    Connected.
    
    SQL> GRANT UPDATE(sal,mgr) ON scott.emp TO john;   --授予scott.emp(sal,mgr)的更新权限
    Grant succeeded.
    
    SQL> UPDATE scott.emp SET sal = sal + 100 WHERE ename = 'SCOTT';  --成功更新
    1 row updated.   
    
    --向数据库中所有用户分配权限
    SQL> GRANT SELECT ON dept TO PUBLIC;
     Grant succeeded.
    
    c.查询权限分配情况
    
    数据字典视图                      描述
    
    ROLE_SYS_PRIVS            角色拥有的系统权限
    
    ROLE_TAB_PRIVS            角色拥有的对象权限
    
    
    USER_TAB_PRIVS_MADE      查询授出去的对象权限(通常是属主自己查)
    
    USER_TAB_PRIVS_RECD      用户拥有的对象权限
    
    
    USER_COL_PRIVS_MADE      用户分配出去的列的对象权限
    
    USER_COL_PRIVS_RECD      用户拥有的关于列的对象权限
    
    
    USER_SYS_PRIVS            用户拥有的系统权限
    
    USER_TAB_PRIVS            用户拥有的对象权限
    
    USER_ROLE_PRIVS       用户拥有的角色  
    
    --查询已授予的对象权限(即某个用户对哪些表对哪些用户开放了对象权限)
        SQL> SELECT * FROM user_tab_privs_made; --下面是scott用户开放的对象权限
    
       GRANTEE              TABLE_NAME               GRANTOR                   PRIVILEGE            GRA HIE
    
       -------------------- ------------------------ ------------------------- -------------------- --- ---
    
       PUBLIC               DEPT                     SCOTT                     SELECT               NO  NO
    
       ROBINSON             EMP                      SCOTT                     SELECT               NO  NO  
    
    
    --查询列上开放的对象权限
    
       SQL> SELECT * FROM user_col_privs_made;
    
    
    
       GRANTEE              TABLE_NAME           COLUMN_NAME          GRANTOR        PRIVILEGE            GRA
    
       -------------------- -------------------- --------------------- -------------- -------------------- ---
    
       ROBINSON             EMP                  SAL                   SCOTT          UPDATE               YES
    
       JOHN                 EMP                  MGR                   ROBINSON       UPDATE               NO
    
       ROBINSON             EMP                  MGR                   SCOTT          UPDATE               YES
    
       JOHN                 EMP                  SAL                   ROBINSON       UPDATE               NO
    
    
    
    --查询已接受的对象特权(即某个用户被授予了哪些表上的哪些对象特权)
    
       SQL> SELECT * FROM user_tab_privs_recd;
    
    
    
       OWNER                TABLE_NAME           GRANTOR                        PRIVILEGE            GRA HIE
    
       -------------------- -------------------- ------------------------------ -------------------- --- ---
    
       SCOTT                EMP                  SCOTT                          SELECT               NO  NO
    
    
    
    --查询用户已接受列的对象权限
    
       SQL> SELECT * FROM user_col_privs_recd;
    
    
    
       OWNER                TABLE_NAME         COLUMN_NAME      GRANTOR              PRIVILEGE            GRA
    
       -------------------- ------------------ ---------------- -------------------- -------------------- ---
    
       SCOTT                EMP                MGR              SCOTT                UPDATE               YES
    
       SCOTT                EMP                SAL              SCOTT                UPDATE               YES
    
    
    d.收回对象权限
    
       使用REVOKE 语句收回权限
    
       使用WITH GRANT OPTION 子句所分配的权限同样被收回
    
    
       REVOKE {privilege [, privilege...]|ALL}
    
       ON object
    
       FROM   {user[, user...]|role|PUBLIC}
    
       [CASCADE CONSTRAINTS];
    
    
       CASCADE CONSTRAINTS 为处理引用完整性时需要
    
    
       --收回权限示例     
    
       SQL> conn scott/tiger;
       Connected.
    
       SQL> REVOKE SELECT ON emp FROM robinson;
       Revoke succeeded.
    
       SQL> REVOKE UPDATE(sal,mgr) ON emp FROM robinson; --注意此处的提示revoke的是整个表,而非列
       REVOKE UPDATE(sal,mgr) ON emp FROM robinson
                   *
       ERROR at line 1:
    
       ORA-01750: UPDATE/REFERENCES may only be REVOKEd from the whole table, not by column
    
       SQL> REVOKE UPDATE ON emp FROM robinson;
       Revoke succeeded.
    
       --用户robinson的update 权限被revoke,曾级联赋予john的权限也被收回,
    
       --如下提示表、视图不存在,user_col_privs_recd中无记录
    
       SQL> CONN john/john;
    
       Connected.
    
       SQL> UPDATE scott.emp SET sal = sal - 100 WHERE ename = 'SCOTT';
       UPDATE scott.emp SET sal = sal - 100 WHERE ename = 'SCOTT'
                   *
       ERROR at line 1:
       ORA-00942: table or view does not exist
    
       SQL> SELECT * FROM user_col_privs_recd;
       no rows selected
    
    注意:如果取消某个用户的对象权限,对于该用户使用with grant option授予其它用户相同权限来说,将级联删除这些用户权限
    
    e.其它
       检查DBA权限的用户  
           select * from dba_role_privs where granted_role='DBA'; 
       查看用户具有的系统权限:
           SELECT * FROM session_privs;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124
    • 125
    • 126
    • 127
    • 128
    • 129
    • 130
    • 131
    • 132
    • 133
    • 134
    • 135
    • 136
    • 137
    • 138
    • 139
    • 140
    • 141
    • 142
    • 143
    • 144
    • 145
    • 146
    • 147
    • 148
    • 149
    • 150
    • 151
    • 152
    • 153
    • 154
    • 155
    • 156
    • 157
    • 158
    • 159
    • 160
    • 161
    • 162
    • 163
    • 164
    • 165
    • 166
    • 167
    • 168
    • 169
    • 170
    • 171
    • 172
    • 173
    • 174
    • 175
    • 176
    • 177
    
        在Oracle权限之间存在传递性:
    
        即用户A将权限授予B,B可以将操作的权限再授予C,命令如下:
    
          grant alert table on tablename to zhangsan with admin option;//关键字 with admin option同时获得权限分配权
          
          grant alert table on tablename to zhangsan with grant option;//关键字 with grant option效果和admin类似
          即with admin option 允许当前用户A将权限赋予B。
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    3.3权限总结

    1.使用create user语句创建用户,alter user语句修改用户,其语法大致相同
    
        drop user username [CASCADE] 会删除用户所拥有的所有对象及数据
    
    2.系统权限允许用户在数据库中执行特定的操作,如执行DDL语句。
    
        with admin option 使得该用户具有将自身获得的权限授予其它用户的功能
    
       但收回系统权限时,不会从其它帐户级联取消曾被授予的相同权限
    
    3.对象权限允许用户对数据库对象执行特定的操作,如执行DML语句。
    
        with grant option 使得该用户具有将自身获得的对象权限授予其它用户的功能
    
       但收回对象权限时,会从其它帐户级联取消曾被授予的相同权限
    
    4.系统权限与对象权限授予时的语法差异为对象权限使用了ON object_name 子句
    
    5. PUBLIC 为所有的用户
    
    6. ALL:对象权限中的所有对象权限
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    四、用户

    MySQL,一个用户,多个数据库,每个数据库拥有各自的表,而Oracle一个数据库,多个用户,每个用户拥有各自的表(数据库对象)
       sys (系统管理员拥有最高权限) as sysdba
       system //本地管理员 次高权限
       scott; //普通用户,密码默认为tiger,默认未解锁
       创建用户:
          create user username identified by password [account lock | unlock]
          [password expire 设置密码过期];
       解锁|加锁
          alter user username account unlock | lock;
       修改密码
          非管理员用户登录状态:password  → 旧口令 → 新口令 :修改当前用户的密码
          管理员登录状态:password username → 新口令 :修改指定用户的密码
          alter user username identified by newpassword;
       删除用户
          drop user username [cascade];
          cascade表示级联删除用户的所有对象,删用户时,一起删除该用户的对象
       删除当前正在连接的用户
          v$session 存放的是已经连接的用户的信息
          select sid,serial#,username from v$session where username = ‘’;
          alter system kill session 'sid,serial#';
          drop user username [cascade];
       查看当前的登录的用户
          show user;
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23

    关于用户常见操作代码(下面的代码都是可以运行的,在windows操作系统上大写小区分不敏感,所以大部分大写的关键命令单词都可以小写):

    1.修改用户
    
        修改用户的语法同创建用户,仅仅讲关键字create替换为alter,alter user可以修改除用户名之外的任一属性
    
       ALTER USER robinson ACCOUNT LOCK;
    
    2.修改密码
    
       DBA 可以创建用户和修改密码
    
       用户本人可以使用ALTER USER 语句修改密码
    
       SQL> ALTER user robinson  IDENTIFIED BY  newpassword;
    
    3.删除用户:
    
       DROP USER username [CASCADE]
    
       CASECADE 连同用户创建的对象一并删除,如果该用户创建了对象,要加CASCADE删除,否则删除不掉
    
       另外,不能删除当前正在与ORACLE服务器相连的用户。
    
    4.改变用户在表空间上的配额:
    
       ALTER USER username  QUOTA 0 ON system;
    
       ALTER USER scott QUOTA UNLIMITED ON USERS;
    
       ALTER USER dog QUOTA 30M ON system;
    
    5.查看用户表空间配额(dba_ts_quotas):
    
       SQL> SELECT USERNAME,TABLESPACE_NAME,MAX_BYTES/1024/1024 "Max MB"     
    
         2  FROM dba_ts_quotas WHERE USERNAME='SCOTT';
    
    
       USERNAME              TABLESPACE_NAME           Max MB
    
       ------------------------------ --------------------------
    
       SCOTT                 SYSTEM                     30
    
    6.查看特定对象下用户所拥有的对象
    
       使用dba_objects视图
    
       SQL> SELECT owner,object_name, object_type FROM dba_objects WHERE owner= 'SCOTT';
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49

    五、角色与权限

    角色基本内容如下:
            grant 角色 to username;
            revoke 角色 from username;    
            一个角色是多个权限的集合
            系统预定义角色
            connect 连接
            resource 访问资源权限,访问表、序列,不包括create session 
            dba 拥有所有权限
            自定义角色
            create role 角色名
            grant 权限列表|角色列表 to 角色名
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    每个Oracle用户都有一个名字和口令,并拥有一些由其创建的表、视图和其他资源。Oracle角色(role)就是一组权限(privilege) (或者是每个用户根据其状态和条件所需的访问类型)。用户可以给角色授予或赋予指定的权限,然后将角色赋给相应的用户。一个用户也可以直接给其他用户授 权。

    数据库系统权限(Database System Privilege)允许用户执行特定的命令集。例如,CREATE TABLE权限允许用户创建表,GRANT ANY PRIVILEGE 权限允许用户授予任何系统权限。

    数据库对象权限(Database Object Privilege)使得用户能够对各个对象进行某些操作。例如DELETE权限允许用户删除表或视图的行,SELECT权限允许用户通过select从 表、视图、序列(sequences)或快照 (snapshots)中查询信息。

    (1)、3种标准角色

    Qracle为了兼容以前的版本,提供了三种标准的角色(role):CONNECT、RESOURCE和DBA。

    1. CONNECT Role(连接角色)
    
    临时用户,特别是那些不需要建表的用户,通常只赋予他们CONNECTrole。CONNECT是使用Oracle的简单权限,这种权限只有在对其他用户 的表有访问权时,包括select、insert、update和delete等,才会变得有意义。拥有CONNECT role的用户还能够创建表、视图、序列(sequence)、簇(cluster)、同义词(synonym )、会话(session)和与其他数据库的链(link)。
    
    2. RESOURCE Role(资源角色)
    
    更可靠和正式的数据库用户可以授予RESOURCE role。RESOURCE提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)。
    
    3. DBA Role(数据库管理员角色)
    
    DBA role拥有所有的系统权限----包括无限制的空间限额和给其他用户授予各种权限的能力。SYSTEMDBA用户拥有。下面介绍一些DBA经常使用的典型权限。
    
    A.grant(授权)命令
    
    下面对刚才创建的用户user01授权,命令如下:
    
    grant connect, resource to user01;
    
    B.revoke(撤消)权限
    
    已授予的权限可以撤消。例如撤消(1)中的授权,命令如下:
    
    revoke connect, resource from user01;
    
    一个具有DBA角色的用户可以撤消任何别的用户甚至别的DBA的CONNECT、RESOURCE 和DBA的其他权限。当然,这样是很危险的,因此,除非真正需要,DBA权限不应随便授予那些不是很重要的一般用户。
    
    撤消一个用户的所有权限,并不意味着从Oracle中删除了这个用户,也不会破坏用户创建的任何表;只是简单禁止其对这些表的访问。其他要访问这些表的用户可以象以前那样地访问这些表。
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28

    “`

    (2)、创建角色

    除了前面讲到的三种系统角色—-CONNECT、RESOURCE和DBA,用户还可以在Oracle创建自己的role。用户创建的role可以由 表或系统权限或两者的组合构成。为了创建role,用户必须具有CREATE ROLE系统权限。下面给出一个create role命令的实例:

    create role STUDENT;
    
    这条命令创建了一个名为STUDENT的role。
    
    一旦创建了一个role,用户就可以给他授权。给role授权的grant命令的语法与对对用户的语法相同。在给role授权时,在grant命令的to子句中要使用role的名称,如下所示:
    
    grant select on CLASS to STUDENT;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    现在,拥有STUDENT角色的所有用户都具有对CLASS表的select权限。

    (3)、删除角色

       要删除角色,可以使用drop role命令,如下所示:
    
       drop role STUDENT;
    
    • 1
    • 2
    • 3
    • 4
    展开全文
  • Oracle数据库常用Sql语句大全

    千次阅读 2018-07-14 11:35:59
    一、数据控制语句 (DML) 部分 1.INSERT (往数据表里插入记录的语句)INSERT INTO 表名(字段名1, 字段名2, ……) VALUES ( 值1, 值2, ……);INSERT INTO 表名(字段名1, 字段名2, ……) SELECT (字段名1, 字段名2, ...


    一、数据控制语句 (DML) 部分

     

    1.INSERT  (往数据表里插入记录的语句)

    INSERT INTO 表名(字段名1, 字段名2, ……) VALUES ( 1, 2, ……);

    INSERT INTO 表名(字段名1, 字段名2, ……)  SELECT (字段名1, 字段名2, ……) FROM 另外的表名;

    字符串类型的字段值必须用单引号括起来, 例如: ’GOOD DAY’

    如果字段值里包含单引号需要进行字符串转换, 我们把它替换成两个单引号''.

    字符串类型的字段值超过定义的长度会出错, 最好在插入前进行长度校验.

    日期字段的字段值可以用当前数据库的系统时间SYSDATE, 精确到秒

    或者用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)

    TO_DATE()还有很多种日期格式, 可以参看ORACLE DOC.

    --小时:分钟:的格式YYYY-MM-DDHH24:MI:SS

    INSERT时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型,

    方法借用ORACLE里自带的DBMS_LOB程序包.

    INSERT时如果要用到从1开始自动增长的序列号, 应该先建立一个序列号

    CREATE SEQUENCE 序列号的名称 (最好是表名+序列号标记) INCREMENT BY 1  START  WITH  1

    MAXVALUE 99999  CYCLE  NOCACHE;

    其中最大的值按字段的长度来定, 如果定义的自动增长的序列号 NUMBER(6) , 最大值为999999

    INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL

    2,DELETE  (删除数据表里记录的语句)

    DELETE FROM表名 WHERE 条件;

    注意:删除记录并不能释放ORACLE里被占用的数据块表空间. 它只把那些被删除的数据块标成unused.

    如果确实要删除一个大表里的全部记录, 可以用 TRUNCATE 命令, 它可以释放占用的数据块表空间

    TRUNCATE TABLE 表名;

    此操作不可回退.truncate table

    3,UPDATE  (修改数据表里记录的语句)

    UPDATE表名 SET 字段名1=1, 字段名2=2, …… WHERE 条件;

    如果修改的值N没有赋值或定义时, 将把原来的记录内容清为NULL, 最好在修改前进行非空校验;

    N超过定义的长度会出错, 最好在插入前进行长度校验..

    注意事项:

    A,以上SQL语句对表都加上了行级锁,

    确认完成后, 必须加上事物处理结束的命令 COMMIT 才能正式生效,否则改变不一定写入数据库里。

    如果想撤回这些操作,可以用命令 ROLLBACK 复原。

    B,在运行INSERT, DELETE UPDATE 语句前最好估算一下可能操作的记录范围,应该把它限定在较小 (一万条记录) 范围内,否则ORACLE处理这个事物用到很大的回退段。

    程序响应慢甚至失去响应. 如果记录数上十万以上这些操作,可以把这些SQL语句分段分次完成,其间加上COMMIT 确认事物处理。

    二,数据定义 (DDL) 部分

    1.CREATE (创建表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等)

    ORACLE常用的字段类型有

    CHAR                       固定长度的字符串

    VARCHAR2               可变长度的字符串

    NUMBER(M,N)               数字型M是位数总长度, N是小数的长度

    DATE                       日期类型

    创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面

    创建表时可以用中文的字段名, 但最好还是用英文的字段名

    创建表时可以给字段加上默认值, 例如 DEFAULT SYSDATE

    这样每次插入和修改时, 不用程序操作这个字段都能得到动作的时间

    创建表时可以给字段加上约束条件

    例如不允许重复 UNIQUE, 关键字 PRIMARY KEY

    2,ALTER       (改变表, 索引, 视图等)

    改变表的名称

    ALTER TABLE 表名1  TO 表名2;

    在表的后面增加一个字段

    ALTER TABLE表名 ADD 字段名字段名描述;

    修改表里字段的定义描述

    ALTER TABLE表名 MODIFY字段名字段名描述;

    给表里的字段加上约束条件

    ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (字段名);

    ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (字段名);

    把表放在或取出数据库的内存区

    ALTER TABLE 表名 CACHE;

    ALTER TABLE 表名 NOCACHE;

    3,DROP (删除表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等)

    删除表和它所有的约束条件

    DROP TABLE 表名 CASCADE CONSTRAINTS;

    4,TRUNCATE (清空表里的所有记录, 保留表的结构)

    TRUNCATE 表名;

     

    三,查询语句 (SELECT) 部分

    SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 条件;

    字段名可以带入函数

      例如:  COUNT(*), MIN(字段名),  MAX(字段名),  AVG(字段名), DISTINCT(字段名),

              TO_CHAR(DATE字段名,'YYYY-MM-DDHH24:MI:SS')

    NVL(EXPR1,EXPR2)函数

    解释:

    IF EXPR1=NULL

                   RETURN EXPR2

    ELSE

                          RETURNEXPR1

    DECODE(AAV1R1V2R2....)函数

    解释:

    IF AA=V1 THENRETURN R1

    IF AA=V2 THENRETURN R2

    ..…

    ELSE

    RETURN NULL

    LPAD(char1,n,char2)函数

    解释:

    字符char1按制定的位数n显示,不足的位数用char2字符串替换左边的空位

    字段名之间可以进行算术运算

    例如:  (字段名1*字段名1)/3

    查询语句可以嵌套

    例如: SELECT …… FROM

    (SELECT …… FROM表名1, [表名2, ……] WHERE 条件) WHERE 条件2;

    两个查询语句的结果可以做集合操作

    例如: 并集UNION(去掉重复记录), 并集UNION ALL(不去掉重复记录), 差集MINUS,  交集INTERSECT

    分组查询

    SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] GROUP BY字段名1

    [HAVING 条件] ;

    两个以上表之间的连接查询

    SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE

                   表名1.字段名 = 表名2. 字段名 [ AND ……] ;

    SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE

                   表名1.字段名 = 表名2. 字段名(+) [ AND ……] ;

    (+)号的字段位置自动补空值

    查询结果集的排序操作, 默认的排序是升序ASC, 降序是DESC

    SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……]

    ORDER BY字段名1, 字段名2 DESC;

    字符串模糊比较的方法

    INSTR(字段名, ‘字符串’)>0

    字段名 LIKE  ‘字符串%’  [‘%字符串%’]

    每个表都有一个隐含的字段ROWID, 它标记着记录的唯一性.

     

    四,ORACLE里常用的数据对象 (SCHEMA)

    1,索引 (INDEX)

    CREATE INDEX 索引名ON 表名 ( 字段1, [字段2, ……] );

    ALTER INDEX 索引名 REBUILD;

    一个表的索引最好不要超过三个 (特殊的大表除外), 最好用单字段索引, 结合SQL语句的分析执行情况,

    也可以建立多字段的组合索引和基于函数的索引

    ORACLE8.1.7字符串可以索引的最大长度为1578 单字节

    ORACLE8.0.6字符串可以索引的最大长度为758 单字节

    2,视图 (VIEW)

    CREATE VIEW 视图名AS SELECT …. FROM …..;

    ALTER VIEW视图名 COMPILE;

    视图仅是一个SQL查询语句, 它可以把表之间复杂的关系简洁化.

    3,同义词 (SYNONMY)

    CREATE SYNONYM同义词名FOR 表名;

    CREATE SYNONYM同义词名FOR 表名@数据库链接名;

    4,数据库链接 (DATABASE LINK)

    CREATE DATABASELINK数据库链接名CONNECT TO 用户名 IDENTIFIED BY 密码 USING ‘数据库连接字符串’;

    数据库连接字符串可以用NET8 EASY CONFIG或者直接修改TNSNAMES.ORA里定义。

    数据库参数global_name=true时要求数据库链接名称跟远端数据库名称一样

    数据库全局名称可以用以下命令查出

    SELECT * FROMGLOBAL_NAME;

    查询远端数据库里的表

    SELECT …… FROM 表名@数据库链接名;

     

    五,权限管理 (DCL) 语句

    1,GRANT       赋于权限

    常用的系统权限集合有以下三个:

    CONNECT(基本的连接), RESOURCE(程序开发), DBA(数据库管理)

    常用的数据对象权限有以下五个:

    ALL        ON 数据对象名,        SELECT ON 数据对象名,        UPDATE ON 数据对象名,

    DELETE        ON 数据对象名,  INSERTON 数据对象名,   ALTER  ON 数据对象名

    GRANT CONNECT,RESOURCE TO 用户名;

    GRANT SELECT ON 表名 TO 用户名;

    GRANT SELECT,INSERT, DELETE ON表名 TO 用户名1, 用户名2;

    2,REVOKE 回收权限

    REVOKE CONNECT,RESOURCE FROM 用户名;

    REVOKE SELECT ON表名 FROM 用户名;

    REVOKE SELECT,INSERT, DELETE ON表名 FROM 用户名1, 用户名2;

    查询数据库中第63号错误:

    selectorgaddr,destaddr from sm_histable0116 where error_code='63';

    查询数据库中开户用户最大提交和最大下发数: select MSISDN,TCOS,OCOS fromms_usertable

    查询数据库中各种错误代码的总和:

    selecterror_code,count(*) from sm_histable0513 group by error_code order

    by error_code;

    查询报表数据库中话单统计种类查询。

    selectsum(Successcount) from tbl_MiddleMt0411 where ServiceType2=111

    selectsum(successcount),servicetype from tbl_middlemt0411 group by servicetype

     

    oracle常用SQL语句

    1、连接SQL*Plus system/manager

    2、显示当前连接用户SQL> show user

    3、查看系统拥有哪些用户SQL> select * from all_users;

    4、新建用户并授权SQL> create user a identified by a;(默认建在SYSTEM表空间下)SQL> grantconnect,resource to a;

    5、连接到新用户SQL> conn a/a

    6、查询当前用户下所有对象SQL> select * from tab;

    7、建立第一个表SQL> create table a(a number);

    8、查询表结构SQL> desc a

    9、插入新记录SQL> insert into a values(1);

    10、查询记录SQL> select * from a;

    11、更改记录SQL> update a set a=2;

    12、删除记录SQL> delete from a;

    13、回滚SQL> roll;SQL> rollback;

    14、提交SQL> commit;

    ----------------------------------------------------------------

    用户授权:GRANT ALTER ANY INDEX TO "user_id "GRANT"dba " TO "user_id ";ALTER USER "user_id "DEFAULT ROLE ALL创建用户:CREATE USER "user_id "PROFILE "DEFAULT " IDENTIFIED BY " DEFAULT TABLESPACE"USERS " TEMPORARY TABLESPACE "TEMP " ACCOUNT UNLOCK;GRANT"CONNECT " TO "user_id ";用户密码设定:ALTER USER"CMSDB " IDENTIFIED BY "pass_word "表空间创建:CREATE TABLESPACE "table_space " LOGGINGDATAFILE 'C:\ORACLE\ORADATA\dbs\table_space.ora' SIZE 5M

    ------------------------------------------------------------------------

    1、查看当前所有对象

    SQL > select* from tab;

    2、建一个和a表结构一样的空表

    SQL > createtable b as select * from a where 1=2;

    SQL > createtable b(b1,b2,b3) as select a1,a2,a3 from a where 1=2;

    3、察看数据库的大小,和空间使用情况

    SQL > coltablespace format a20SQL > select b.file_id  文件ID,b.tablespace_name  表空间,b.file_name     物理文件名,b.bytes       总字节数,(b.bytes-sum(nvl(a.bytes,0)))   已使用,sum(nvl(a.bytes,0))        剩余,sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比  from dba_free_space a,dba_data_files bwhere a.file_id=b.file_idgroup byb.tablespace_name,b.file_name,b.file_id,b.bytesorder byb.tablespace_name/dba_free_space--表空间剩余空间状况  dba_data_files --数据文件空间占用情况 4、查看现有回滚段及其状态

    SQL > colsegment format a30SQL > SELECTSEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROMDBA_ROLLBACK_SEGS;

    5、查看数据文件放置的路径

    SQL > colfile_name format a50SQL > selecttablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order byfile_id;

    6、显示当前连接用户

    SQL > showuser

    7、把SQL*Plus当计算器

    SQL > select100*20 from dual;

    8、连接字符串

    SQL > select 1 | |2 from 1;SQL > select concat(1,2) from 1;

    9、查询当前日期

    SQL > selectto_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;

    10、用户间复制数据

    SQL > copyfrom user1 to user2 create table2 using select * from table1;

    11、视图中不能使用order by,但可用group by代替来达到排序目的

    SQL > createview a as select b1,b2 from b group by b1,b2;

    12、通过授权的方式来创建用户

    SQL > grantconnect,resource to test identified by test;

    SQL > conntest/test

    13、查出当前用户所有表名。

    select uniquetname from col;

    -----------------------------------------------------------------------

    /* 向一个表格添加字段 */alter table alist_table add address varchar2(100);

    /* 修改字段属性字段为空 */alter tablealist_table modify address varchar2(80);

    /* 修改字段名字 */create table alist_table_copy as selectID,NAME,PHONE,EMAIL,QQ as QQ2, /*qq 改为qq2*/ADDRESSfrom alist_table;

    drop tablealist_table;rename alist_table_copy to alist_table/* 修改表名 */

    空值处理有时要求列值不能为空create table dept (deptno number(2) not null, dnamechar(14), loc char(13));

    在基表中增加一列alter table deptadd (headcnt number(3));

    修改已有列属性alter table deptmodify dname char(20);注:只有当某列所有值都为空时,才能减小其列值宽度。只有当某列所有值都为空时,才能改变其列值类型。只有当某列所有值都为不空时,才能定义该列为not null。例:alter table dept modify (loc char(12));alter table deptmodify loc char(12);alter table dept modify (dname char(13),loc char(12));

    查找未断连接select process,osuser,username,machine,logon_time,sql_textfrom v$session a,v$sqltext b where a.sql_address=b.address;

    -----------------------------------------------------------------1.USER_开始的数据字典视图包含当前用户所拥有的信息, 查询当前用户所拥有的表信息:select * from user_tables;2.ALL_开始的数据字典视图包含ORACLE用户所拥有的信息,查询用户拥有或有权访问的所有表信息:select * fromall_tables;

    3.DBA_开始的视图一般只有ORACLE数据库管理员可以访问:select * from dba_tables;

    4.查询ORACLE用户:conn sys/change_on_installselect* from dba_users;conn system/manager;select * from all_users;

    5.创建数据库用户:CREATE USER user_name IDENTIFIED BY password;GRANTCONNECT TO user_name;GRANT RESOURCE TO user_name;授权的格式: grant (权限) on tablename to username;删除用户(或表):drop user(table) username(tablename) (cascade);6.向建好的用户导入数据表IMP SYSTEM/MANAGER FROMUSER = FUSER_NAME TOUSER =USER_NAME FILE = C:\EXPDAT.DMP COMMIT = Y7.索引create index[index_name] on [table_name]( "column_name ")intersect运算

    返回查询结果中相同的部分

    exp:各个部门中有哪些相同的工种

    selectjob

    fromaccount

    intersect

    selectjob

    fromresearch

    intersect

    selectjob

    fromsales;

     

    minus运算

    返回在第一个查询结果中与第二个查询结果不相同的那部分行记录。

    有哪些工种在财会部中有,而在销售部中没有?

    exp:selectjobfromaccount

    minus

    selectjobfromsales;

    1. oracle安装完成后的初始口令

    internal/oracle 

    sys/change_on_install 

    system/manager 

    scott/tiger 

    sysman/oem_temp 

     

    2. oracle9iasweb cache的初始默认用户和密码? 

    administrator/administrator 

     

    3. oracle 8.0.5怎么创建数据库

    orainst。假如有motif界面,可以用orainst /m 

     

    4. oracle 8.1.7怎么创建数据库

    dbassist 

     

    5. oracle 9i 怎么创建数据库

    dbca 

     

    6. oracle中的裸设备指的是什么

    裸设备就是绕过文件系统直接访问的储存空间 

     

    7. oracle如何区分 64-bit/32bit 版本??? 

    $ sqlplus '/ assysdba' 

    sql*plus:release 9.0.1.0.0 - production on mon jul 14 17:01:09 2003 

    (c) copyright2001 oracle corporation. all rights reserved. 

    connectedto: 

    oracle9ienterprise edition release 9.0.1.0.0 - production 

    with thepartitioning option 

    jserver release9.0.1.0.0 - production 

    sql> select *from v$version; 

    banner 

    ---------------------------------------------------------------- 

    oracle9ienterprise edition release 9.0.1.0.0 - production 

    pl/sql release9.0.1.0.0 - production 

    core 9.0.1.0.0production 

    tns for solaris:version 9.0.1.0.0 - production 

    nlsrtl version9.0.1.0.0 - production 

    sql> 

     

    8. svrmgr什么意思? 

    svrmgrlserver manager. 

    9i下没有,已经改为用sqlplus 

    sqlplus/nolog 

    变为归档日志型的 

     

    9. 请问如何分辨某个用户是从哪台机器登陆oracle

    select machine ,terminal from v$session; 

     

    10. 用什么语句查询字段呢? 

    desc table_name 可以查询表的结构 

    selectfield_name,... from ... 可以查询字段的值 

    select * fromall_tables where table_name like '%' 

    select * fromall_tab_columns where table_name='??' 

     

    11. 怎样得到触发器、过程、函数的创建脚本? 

    descuser_source 

    user_triggers 

     

    12. 怎样计算一个表占用的空间的大小? 

    selectowner,table_name, 

    num_rows, 

    blocks*aaa/1024/1024"size m", 

    empty_blocks, 

    last_analyzed 

    fromdba_tables 

    wheretable_name='xxx'; 

    here: aaa is thevalue of db_block_size ; 

    xxx is the tablename you want to check 

     

    13. 如何查看最大会话数? 

    select * fromv$parameter where name like 'proc%'; 

    sql> 

    sql> showparameter processes 

    name typevalue 

    ------------------------------------------- ------------------------------ 

    aq_tm_processesinteger 1 

    db_writer_processesinteger 1 

    job_queue_processesinteger 4 

    log_archive_max_processesinteger 1 

    processes integer200 

    这里为200个用户。 

    select * fromv$license; 

    其中sessions_highwater纪录曾经到达的最大会话数 

     

    14. 如何查看系统被锁的事务时间? 

    select * fromv$locked_object ; 

     

    15. 如何以archivelog的方式运行oracle 

    init.ora 

    log_archive_start= true 

    restartdatabase 

     

    16. 怎么获取有哪些用户在使用数据库 

    select usernamefrom v$session; 

     

    17. 数据表中的字段最大数是多少

    表或视图中的最大列数为 1000 

     

    18. 怎样查得数据库的sid ? 

    select name fromv$database; 

    也可以直接查看 init.ora文件 

     

    19. 如何在oracle服务器上通过sqlplus查看本机ip地址

    selectsys_context('userenv','ip_address') from dual; 

    假如是登陆本机数据库,只能返回127.0.0.1,呵呵 

     

    20. unix 下怎么调整数据库的时间? 

    su -root 

    date -u08010000 

     

    21. oracle table中如何抓取memo类型字段为空的数据记录

    select remarkfrom oms_flowrec where trim(' ' from remark) is not null ; 

     

     

    22. 如何用bbb表的数据去更新aaa表的数据(有关联的字段

    up2003-10-17 aaaset bns_snm=(select bns_snm from bbb where aaa.dpt_no=bbb.dpt_no) wherebbb.dpt_no is not null; 

     

    23. p4计算机安装方法 

    symcjit.dll改为sysmcjit.old 

     

    24. 何查询server是不是ops? 

    select * fromv$option; 

    假如parallel server=true则有ops 

     

    25. 何查询每个用户的权限

    select * fromdba_sys_privs; 

     

    26. 如何将表移动表空间

    alter tabletable_name move tablespace_name; 

     

    27. 如何将索引移动表空间

    alter indexindex_name rebuild tablespace tablespace_name; 

     

    28. linux,unix下如何启动dbastudio? 

    oemappdbastudio 

     

    29. 查询锁的状况的对象有

    v$lock, v$locked_object,v$session, v$sqlarea, v$process ; 

    查询锁的表的方法

    select s.sidsession_id, s.username, decode(lmode, 0, 'none', 1, 'null', 2, 'row-s (ss)', 3,'row-x (sx)', 4, 'share', 5, 's/row-x (ssx)', 6, 'exclusive', to_char(lmode))mode_held, decode(request, 0, 'none', 1, 'null', 2, 'row-s (ss)', 3, 'row-x(sx)', 4, 'share', 5, 's/row-x (ssx)', 6, 'exclusive', to_char(request))mode_requested, o.ccbzzp||'.'||o.object_name||' ('||o.object_type||')', s.typelock_type, l.id1 lock_id1, l.id2 lock_id2 from v$lock l, sys.dba_objects o,v$session s where l.sid = s.sid and l.id1 = o.object_id ; 

     

    30. 如何解锁

    alter systemkill session ‘sid,serir#’; 

     

    31. sqlplus下如何修改编辑器

    define_editor="" -- 必须加上双引号 

    来定义新的编辑器,也可以把这个写在$oracle_home/sqlplus/admin/glogin.sql里面使它永久有效。 

     

     

    34. 查询当前用户对象

    select * fromuser_objects; 

    select * fromdba_segments; 

     

    35. 如何获取错误信息

    select * fromuser_errors; 

     

    36. 如何获取链接状况

    select * fromdba_db_links; 

     

    37. 查看数据库字符状况

    select * fromnls_database_parameters; 

    select * fromv$nls_parameters; 

     

    38. 查询表空间信息

    select * fromdba_data_files; 

     

    39. oracleinteral用户要口令

    修改 sqlnet.ora 

    sqlnet.authentication_services=(nts) 

     

    40. 出现java.exe的解决办法

    一般是将oracleorahomexihttpserver改成手工启动可以的 

    x8

     

    41. 如何给表、列加注释? 

    sql>commenton table is '表注释'; 

    注释已创建。 

    sql>commenton column . is '列注释'; 

    注释已创建。 

    sql> select *from user_tab_comments where comments is not null; 

     

    42. 如何查看各个表空间占用磁盘情况? 

    sql> coltablespace format a20 

    sql>select 

    b.file_id 文件id

    b.tablespace_name表空间名

    b.bytes 字节数

    (b.bytes-sum(nvl(a.bytes,0)))已使用

    sum(nvl(a.bytes,0))剩余空间

    sum(nvl(a.bytes,0))/(b.bytes)*100剩余百分比 

    fromdba_free_space a,dba_data_files b 

    wherea.file_id=b.file_id 

    group byb.tablespace_name,b.file_id,b.bytes 

    order byb.file_id 

    43. 如把oracle设置为mts或专用模式? 

    #dispatchers="(protocol=tcp)(service=sidxdb)" 

    加上就是mts,注释就是专用模式,sid是指你的实例名。 

     

    44. 如何才能得知系统当前的scn

    selectmax(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe; 

     

    45. 请问如何在oracle中取毫秒

    9i之前不支持,9i开始有timestamp. 

    9i可以用select systimestamp from dual; 

     

    大家在应用oracle的时候可能会碰到很多看起来不难的问题, 非凡对新手来说, 今天我简单把它总结一下, 发布给大家, 希望对大家有帮助! 和大家一起探讨, 共同进步

     

    oracle高手来说是不用看的

     

    46. 如何在字符串里加回车? 

    select 'welcometo visit'||chr(10)||'www.csdn.net' from dual ; 

     

    47. 中文是如何排序的? 

    oracle9i之前,中文是按照二进制编码进行排序的。 

    oracle9i中新增了按照拼音、部首、笔画排序功能。设置nls_sort 

    schinese_radical_m按照部首(第一顺序)、笔划(第二顺序)排序 

    schinese_stroke_m按照笔划(第一顺序)、部首(第二顺序)排序 

    schinese_pinyin_m按照拼音排序 

     

    48. oracle8i中对象名可以用中文吗? 

    可以 

     

    49. 如何改变winsql*plus启动选项? 

    sql*plus自身的选项设置我们可以在$oracle_home/sqlplus/admin/glogin.sql中设置。 

     

    50. 怎样修改oracel数据库的默认日期

    alter sessionset nls_date_format='yyyymmddhh24miss'; 

    or 

    可以在init.ora中加上一行 

    nls_date_format='yyyymmddhh24miss' 

     

    51. 如何将小表放入keep池中

    alter table xxxstorage(buffer_pool keep); 

     

    52. 如何检查是否安装了某个patch? 

    check thatorainventory 

     

    53. 如何使select语句使查询结果自动生成序号

    selectrownum,col from table; 

     

    54. 如何知道数据裤中某个表所在的tablespace? 

    select tablespace_namefrom user_tables where table_name='test'; 

    select * fromuser_tables中有个字段tablespace_name,(oracle

    select * fromdba_segments where …; 

     

    55. 怎么可以快速做一个和原表一样的备份表

    create tablenew_table as (select * from old_table); 

     

    55. 怎么在sqlplus下修改procedure? 

    selectline,trim(text) t from user_source where name =’a’ order by line; 

     

    56. 怎样解除procedure被意外锁定

    alter systemkill session ,把那个session给杀掉,不过你要先查出她的session id 

    or 

    把该过程重新改个名字就可以了。 

     

    57. sqlreference是个什么东西? 

    是一本sql的使用手册,包括语法、函数等等,oracle官方网站的文档中心有下载

     

    58. 如何查看数据库的状态

    unix 

    ps -ef | grepora 

    windows 

    看服务是否起来 

    是否可以连上数据库 

     

    59. 请问如何修改一张表的主键

    alter tableaaa 

    drop constraintaaa_key ; 

    alter tableaaa 

    add constraintaaa_key primary key(a1,b1) ; 

     

    60. 改变数据文件的大小

    alter database .... datafile .... ; 

    手工改变数据文件的大小,对于原来的数据文件有没有损害。 

     

    61. 怎样查看oracle中有哪些程序在运行之中? 

    查看v$sessions 

     

    62. 怎么可以看到数据库有多少个tablespace? 

    select * fromdba_tablespaces; 

     

    63. 如何修改oracle数据库的用户连接数? 

    修改initsid.ora,将process加大,重启数据库

     

    64. 如何查出一条记录的最后更新时间

    可以用logminer 察看 

     

    65. 如何在pl/sql中读写文件? 

    utl_file包答应用户通过pl/sql读写操作系统文件。 

     

    66. 怎样把“&”放入一条记录中? 

    insert into avalues (translate ('at{&}t','at{}','at')); 

     

    67. exp 如何加query参数? 

    exp user/passfile=a.dmp tables(bsempms) 

    query='"whereemp_no="'s09394"'""  

     

    68. 关于oracle8i支持简体和繁体的字符集问题? 

    zhs16gbk可以支 

     

    69. data guard是什么软件? 

    就是standby的换代产品 

     

    70. 如何创建spfile? 

    sql> connect/ as sysdba 

    sql> select *from v$version; 

    sql> createpfile from spfile; 

    sql> createspfile from pfile='e:"ora9i"admin"eygle"pfile"init.ora'; 

    文件已创建。 

    sql> createspfile='e:"ora9i"database"spfileeygle.ora' frompfile='e:"ora9i"admin"eygle"pfile"init.ora'; 

    文件已创建。 

     

    71. 内核参数的应用

    shmmax 

      含义:这个设置并不决定究竟oracle数据库或者操作系统使用多少物理内存,只决定了最多可以使用的内存数目。这个设置也不影响操作系统的内核资源。 

      设置方法:0.5*物理内存 

      例子:set shmsys:shminfo_shmmax=10485760 

    shmmin 

      含义:共享内存的最小大小。 

      设置方法:一般都设置成为1 

      例子:set shmsys:shminfo_shmmin=1 

    shmmni 

      含义:系统中共享内存段的最大个数。 

      例子:set shmsys:shminfo_shmmni=100 

    shmseg 

      含义:每个用户进程可以使用的最多的共享内存段的数目。 

      例子:set shmsys:shminfo_shmseg=20 

    semmni 

      含义:系统中semaphore identifierer的最大个数。 

      设置方法:把这个变量的值设置为这个系统上的所有oracle的实例的init.ora中的最大的那个processes的那个值加10 

      例子:set semsys:seminfo_semmni=100 

    semmns 

      含义:系统中emaphores的最大个数。 

      设置方法:这个值可以通过以下方式计算得到:各个oracle实例的initsid.ora里边的processes的值的总和(除去最大的processes参数)+最大的那个processes×210×oracle实例的个数。 

      例子:set semsys:seminfo_semmns=200 

    semmsl: 

      含义:一个setsemaphore的最大个数。 

      设置方法:设置成为10+所有oracle实例的initsid.ora中最大的processes的值。 

      例子:set semsys:seminfo_semmsl=-200 

    72. 怎样查看哪些用户拥有sysdbasysoper权限? 

    sql>connsys/change_on_install 

    sql>select *from v_$pwfile_users; 

     

    73. 如何单独备份一个或多个表? 

    exp 用户/密码 tables=(1,…,2) 

     

    74. 如何单独备份一个或多个用户? 

    expsystem/manager owner=(用户1,用户2,…,用户n) file=导出文件 

     

    75. 如何对clob字段进行全文检索? 

    select * from awhere dbms_lob.instr(a.a,'k',1,1)>0; 

     

    76. 如何显示当前连接用户

    show user 

     

    77. 如何查看数据文件放置的路径

    col file_nameformat a50 

    sql> selecttablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order byfile_id; 

     

    78. 如何查看现有回滚段及其状态

    sql> colsegment format a30 

    sql> selectsegment_name,ccbzzp,tablespace_name,segment_id,file_id,status fromdba_rollback_segs 

     

    79. 如何改变一个字段初始定义的check范围? 

    sql> altertable xxx drop constraint constraint_name; 

    之后再创建新约束

    sql> altertable xxx add constraint constraint_name check(); 

     

    80. oracle常用系统文件有哪些? 

    通过以下视图显示这些文件信息:v$database,v$datafile,v$logfile v$controlfilev$parameter; 

     

    81. 内连接inner join? 

    select a.* frombsempms a,bsdptms b where a.dpt_no=b.dpt_no; 

     

    82. 如何外连接

    select a.* frombsempms a,bsdptms b where a.dpt_no=b.dpt_no(+); 

    select a.* frombsempms a,bsdptms b wherea.dpt_no(+)=b.dpt_no; 

     

    83. 如何执行脚本sql文件

    sql>@$path/filename.sql; 

     

    84. 如何快速清空一个大表

    sql>truncatetable table_name; 

     

    85. 如何查有多少个数据库实例

    sql>select *from v$instance; 

     

    86. 如何查询数据库有多少表

    sql>select *from all_tables; 

     

    87. 如何测试sql语句执行所用的时间

    sql>settiming on ; 

    sql>select *from tablename; 

    88. chr()的反函数是

    ascii() 

    select char(65)from dual; 

    selectascii('a') from dual; 

     

    89. 字符串的连接 

    selectconcat(col1,col2) from table ; 

    selectcol1||col2 from table ; 

     

    90. 怎么把select出来的结果导到一个文本文件中? 

    sql>spoolc:"abcd.txt; 

    sql>select *from table; 

    sql >spooloff; 

     

    91. 怎样估算sql执行的i/o

    sql>setautotrace on ; 

    sql>select *from table; 

    or 

    sql>select *from v$filestat ; 

    可以查看io 

     

    92. 如何在sqlplus下改变字段大小

    alter tabletable_name modify (field_name varchar2(100)); 

    改大行,改小不行(除非都是空的) 

     

    93. 如何查询某天的数据

    select * fromtable_name where trunc(日期字段)to_date('2003-05-02','yyyy-mm-dd'); 

     

    94. sql 语句如何插入全年日期? 

    create tablebsyear (d date); 

    insert intobsyear 

    selectto_date('20030101','yyyymmdd')+rownum-1 

    fromall_objects 

    whererownum 

     

    95. 假如修改表名

    alter tableold_table_name rename to new_table_name; 

     

    96. 如何取得命令的返回状态值? 

    sqlcode=0 

     

    97. 如何知道用户拥有的权限

    select * fromdba_sys_privs ; 

     

    98. 从网上下载的oracle9i与市场上卖的标准版有什么区别? 

    从功能上说没有区别,只不过oracle公司有明文规定;从网站上下载的oracle产品不得用于商业用途,否则侵权。 

     

    99. 怎样判定数据库是运行在归档模式下还是运行在非归档模式下? 

    进入dbastudio,历程--〉数据库---〉归档查看。 

     

    100.sql>startup pfileifile,spfiled有什么区别? 

    pfile就是oracle传统的初始化参数文件,文本格式的。 

    ifile类似于c语言里的include,用于把另一个文件引入 

    spfile9i里新增的并且是默认的参数文件,二进制格式 

    startup后应该只可接pfile 

     

    101. 如何搜索出前n条记录? 

    select * fromemployee where rownum 

     

    102. 如何知道机器上的oracle支持多少并发用户数

    sql>conninternal ; 

    sql>showparameter processes ; 

     

    103.db_block_size可以修改吗

    一般不可以﹐不建议这样做的。 

     

    104. 如何统计两个表的记录总数

    select (selectcount(id) from aa)+(select count(id) from bb) 总数 fromdual; 

     

    105. 怎样用sql语句实现查找一列中第n大值? 

    select *from 

    (selectt.*,dense_rank() over (order by sal) rank from employee) 

    where rank =n; 

     

     

    106. 如何在给现有的日期加上2年?

    selectadd_months(sysdate,24) from dual; 

     

    107. used_ublk为负值表示什么意思

    it is"harmless". 

     

    108. connectstring是指什么

    应该是tnsnames.ora中的服务名后面的内容 

     

    109. 怎样扩大redo log的大小? 

    建立一个临时的redolog组,然后切换日志,删除以前的日志,建立新的日志。 

     

    110. tablespace 是否不能大于4g? 

    没有限制

     

    111. 返回大于等于n的最小整数值

    select ceil(n)from dual; 

     

    112. 返回小于等于n的最小整数值

    select floor(n)from dual; 

     

    113. 返回当前月的最后一天

    selectlast_day(sys2003-10-17) from dual; 

     

    114. 如何不同用户间数据导入

    impsystem/manager file=aa.dmp fromuser=user_old touser=user_new rows=y indexes=y; 

     

    115. 如何找数据库表的主键字段的名称

    sql>select *from user_constraints where constraint_type='p' andtable_name='table_name'; 

     

    116. 两个结果集互加的函数

    sql>select *from bsempms_old intersect select * from bsempms_new; 

    sql>select *from bsempms_old union select * from bsempms_new; 

    sql>select *from bsempms_old union all select * from bsempms_new; 

     

    117. 两个结果集互减的函数

    sql>select *from bsempms_old minus select * from bsempms_new; 

     

    118. 如何配置sequence? 

    sequence seq_custid 

    create sequenceseq_custid start 1 incrememt by 1; 

    建表时

    create tablecust 

    { cust_idsmallint not null, 

    ...} 

    insert

    insert intotable cust 

    values(seq_cust.nextval, ...) 

     

    日期的各部分的常用的的写法 

    119>.取时间点的年份的写法

    selectto_char(sys2003-10-17,'yyyy') from dual; 

     

    120>.取时间点的月份的写法

    selectto_char(sys2003-10-17,'mm') from dual; 

     

    121>.取时间点的日的写法

    selectto_char(sys2003-10-17,'dd') from dual; 

     

    122>.取时间点的时的写法

    selectto_char(sys2003-10-17,'hh24') from dual; 

     

    123>.取时间点的分的写法

    selectto_char(sys2003-10-17,'mi') from dual; 

     

    124>.取时间点的秒的写法

    selectto_char(sys2003-10-17,'ss') from dual; 

     

    125>.取时间点的日期的写法

    selecttrunc(sys2003-10-17) from dual; 

     

    126>.取时间点的时间的写法

    selectto_char(sys2003-10-17,'hh24:mi:ss') from dual; 

     

    127>.日期,时间形态变为字符形态 

    selectto_char(sys2003-10-17) from dual; 

     

    128>.将字符串转换成日期或时间形态

    selectto_2003-10-17('2003/08/01') from dual; 

     

    129>.返回参数的星期几的写法

    selectto_char(sys2003-10-17,'d') from dual; 

     

    130>.返回参数一年中的第几天的写法

    select to_char(sys2003-10-17,'ddd') from dual;

     

    展开全文
  • 知识点:在PL/SQL语句块中使用DML语句的基本知识,包括select into、insert、update、delete等操作。事务处理的掌握,理解提交、回滚、保存点等概念。 1、什么是DML(知识点回顾) SQL语言是针对数据库而言的...

    知识点:在PL/SQL语句块中使用DML语句的基本知识,包括select into、insert、update、delete等操作。事务处理的掌握,理解提交、回滚、保存点等概念。

     

    1、什么是DML(知识点回顾)

            SQL语言是针对数据库而言的一门语言,它可以创建数据库、数据表,可以针对数据库的数据进行增、删、改、查等操作,可以创建视图、存储过程,可以赋予用户权限等。

     

    Oracle数据库的DML表数据的操作有三种:① insert (插入);② update(更新);③ delete(删除)。

     

    2、在PL/SQL语句块中使用DML

    2.1 使用select into进行变量初始化

            在 PL/SQL 语句块中,存在两种主要的变量赋值方法。我们在上一章中已经了解过,主要学习了第 1 种方法,也就是使用 “ := ” 对变量进行初始化。除了在声明变量的同时进行赋值之外,在 PL/SQL 语句块的声明部分被声明的变量,后期可以使用选择语句进行赋值。这里,我们将学习如何借助于 SELECT INTO 语法,使用选择语句来初始化变量。

    select into赋值语法:

    select item_name into v_name from table_name [where condition];

    语法中:

    • item_name:表示查询项(表中某一列的列名)。
    • v_name:表示变量名。
    • table_name:表示表名。
    • condition:表示查询条件。

    注意:

    (1)查询结果只能返回单行语句才能赋值到变量中保存,返回多行或者零行语句则报错。

    (2)任何返回的单行数据可以经过计算再给变量赋值。

    实例练习:查询emp表中编号为7369的员工,输出员工星姓名和年薪

    实现思路:①先声明两个变量,分别用来存放员工姓名和年薪。②使用select into进行变量初始化,即将表中的姓名直接赋值给变量v_name,将表中的薪水乘以12以后再赋值给变量v_sal。③输出结果。

    SQL> DECLARE
      2     v_sal number(7);
      3     v_name varchar2(10);
      4  BEGIN
      5     select ename ,sal*12 into v_name,v_sal from emp where empno=7369;
      6     dbms_output.put_line(v_name || '的年薪是:' ||v_sal);
      7  END;
      8  /
    

    界面演示效果:

     

    2.2 使用insert插入语句

    insert插入语句的语法:

    insert into 表名 (列名1,列名2,……,列名n)  values (值1,值2,……,值n);

    实例练习:向dept表中插入一行数据(部门编号50,部门名称support,部门地址wuhan)

    实现思路:

    ①查询dept表的结构,以知道需要插入哪些数据及类型,语句desc dept;

    ②查询dept表的所有数据,以免重复输入,语句select * from dept;

    ③使用PL/SQL语句块插入一行数据到dept表中

    ④执行成功以后,再次查询dept表中的所有数据,进行验证

    SQL> DECLARE
      2     v_deptno number(2) := 50;
      3     v_dname varchar2(14) := 'support';
      4     v_loc varchar2(13) :='wuhan';
      5  BEGIN
      6     insert into dept (deptno,dname,loc) values (v_deptno,v_dname,v_loc);
      7  END;
      8  /

    界面演示效果:

     

    2.3 使用update更新语句

    update更新语句的语法:

    update 表名 set 列名1 = 值1, 列名2 = 值2, …… , 列名n = 值n [where 条件];

    实例练习:将dept表中部门为50的记录,loc地址修改为hubei

    SQL> DECLARE
      2     v_loc varchar2(13) := 'hubei';
      3  BEGIN
      4     update dept set loc = v_loc where deptno = 50;
      5  END;
      6  /

    界面演示效果:

     

    2.4 使用delete删除语句

    delete删除语句语法:

    delete from 表名 [where 条件];

    实例练习:删除表dept中部门编号为50的记录

    SQL> delete from dept where deptno=50;

    界面效果演示:

     

     

    3、PL/SQL中事务处理

    3.1 什么是事务

            事务(Transaction)是一个由多条SQL语句组成的工作逻辑单元,这些语句要么全部执行成功,要么全部不执行,只要有一条SQL语句执行失败,已执行的SQL语句会全部回滚到执行之前的状态,这样就保证了数据库数据的一致性。

           举例:比如银行的转账业务,该业务至少可以分为 A 账户的转出 和 B 账户的转入。也就是从 A 账户的余额中减掉一定的数额,然后再将 B 账户的余额增加一定的数额。整个过程等于是修改了两条记录,这两个操作就可以认为组成了一个事务。它们应该是一个整体,一个单个的逻辑工作单元,要么都执行成功,要么都不执行。

     

    3.2 事务的特点

            当使用事务修改多个数据表时,如果在处理的过程中出现了某种错误,例如系统死机或突然断电等情况,则返回结果是全部数据均没有被保存。因为事务处理的结果只有两种:一种是在事务处理的过程中,如果发生了某种错误则整个事务全部回滚,使所有对数据的修改全部撤销,事务对数据库的操作是单步执行的,当遇到错误时可以随时地回滚;另一种是如果没有发生任何错误且每一步的执行都成功,则整个事务全部被提交。从而可以看出,有效地使用事务不但可以提高数据的安全性,而且还可以增强数据的处理效率。

            事务包含 4 种重要的属性,被统称为 ACID(原子性、一致性、隔离性和持久性)特性,对一组 SQL 语句操作构成的事务,数据库操作系统必须确保这些操作的原子性一致性、隔离性持久性

    (1)原子性(Atomicity)

    事务的原子性是指事务中包含的所有操作要么全做,要么不做,也就是说所有的活动在数据库中要么全部反映要么全部不反映,以保证数据库的一致性。

    (2)一致性(Consistency)

    事务在完成时,必须使所有的数据都保持一致状态。在相关数据库中,所有规则都必须应用事务的修改,以保持所有数据的完整性。如果事务成功,则所有数据将变为一个新的状态; 如果事务失败,则所有数据将处于开始之前的状态。

    (3)隔离性(Isolation)

    隔离性是指由事务所做的修改必须与其他事务所做的修改隔离。事务查看数据时,数据所处的状态,要么是另一并发事务修改它之前的状态,要么是另一事物修改它之后的状态,事务不会查看中间状态的数据。

    (4)持久性(durability)

    事务的持久性是指在事务处理结束后,它对数据的修改应该是永久的。即便是系统在遇到故障的情况下也不会丢失,这是数据的重要性决定的。

     

    3.3 为什么要使用事务

           事务的作用:用于保持数据库数据的一致性

     

    3.4 操作事务

              事务的结束:COMMIT;   和    ROLLBACK;

             Oracle11g 中的事务是隐式自动开始的,它不需要用户显示地执行开始事务语句。但对于事务处理,则需要用户进行指定的操作,通常在以下情况时,Oracle 认为一个事务结束了。

    a)  执行 COMMIT 语句提交事务。

    b)  执行 ROLLBACK 语句撤销事务。

    c)  执行一条数据定义语句,比如 CREATEDROP ALTER 等语句。如果该语句执行成功,那么 Oracle 系统会自动执行 COMMIT 命令;否则,Oracle 系统会自动执行 ROLLBACK 命令。

    d)  执行一个数据控制命令,比如 GRANTREVOKE 等控制命令,这种操作执行完毕,Oracle 系统会自动执行 COMMIT 命令。

    e)  正常的断开数据库的联接、正常地退出 SQL*Plus 环境,则 Oracle 系统会自动执行 COMMIT 命令;否则,Oracle 系统会自动执行 ROLLBACK 命令。

            综上 5 种情况可知,Oracle 结束一个事务归根结底,要么执行 COMMIT 语句,要么执行ROLLBACK 语句,下面我们来做详细介绍。

    (1)保存点(savepoint语句)

            保存点用于保存事务未开始之前数据库各方面的状态。用于回滚时,如果想要撤回刚刚的操作,可以在这些操作之前设置一个保存点,在需要撤回时使用rollback进行回滚。

    声明一个保存点的语法:savepoint  保存点名称 ; 

     

    (2)提交事务

           commit语句用于把内存中的数据写到数据库。事务的提交指的是把你刚刚操作的数据真正的写入到数据库中,这个时候你就不能在运用rollback进行撤销你刚才的操作了。数据库一旦commit了数据,那么就会撤销所有的保存点。所以,这时rollback会得到一个错误(ORA-01086: 从未在此会话中创建保存点 's' 或者该保存点无效)。当使用commit语句结束事务之后,其他会话将可以查看到事务变化后的新数据。

    提交语句的语法:commit;

     

    (3)回滚事务

           事务回滚后,数据库各项状态恢复到最近的保存点;该事务好像就没有发生一样。在说回退事务之前,先理解Oracle事务的保存点(savepoint)的概念和作用,保存点是事务中的一点,用于取消部分事务,保存点记录的是当前数据库的状态。

    回滚语句的语法:

            rollback to 保存点名称 ;     --撤销单个事务

            rollback ;                            --不加保存点名称则撤销所有存在的事物

     

    (4)Oracle事务实例练习:利用事务模拟银行转账例子(小明给小华转账500元)

    目的:通过该实例,理解Oracle数据库的事务操作,理解提交、回滚事务和保存点

     

    第一步:在Oracle数据库中,使用scott用户,创建一个数据库表users,表的结构如下:(编号,卡号,名字,余额)

    第二步:向表users中插入两行数据

    第三步:查看当前操作效果(验证提交语句)

             再开启一个新的 SQL*Plus 环境(要求当前的 SQL*Plus 环境不能退出,如果退出,Oracle 系统会自动执行 commit 语句提交),然后在新环境下查询users 表,会发现新增的记录不存在。

    如果使用 commit 语句提交事务后,则在另一个 SQL*Plus 环境下就可以查询到新增的记录了。

    在另一个SQL*Plus 环境下,再次查询时,可以查到表中的记录。

     

    第四步:在PL/SQL程序中利用事务实现转账

    在另一个SQL*Plus 环境下,可以看到记录已经发送变化。

     

    第五步:验证回滚

    输入第四步的语句块, 注意把程序中的commit换成rollback后试一试最后结果。

    提示:

    • 在事务commit提交前,可以使用rollback 到指定的保存点,来回退到指定的保存点。
    • 在事务commit提交后,保存点会被删除,这个时候,就无法进行回退了。

    任何commit操作,也就是事务提交操作,都会导致savepoint的被删除!!!

     

     

     

     

     

     

    ==============这里是结束分割线================

    展开全文
  • oracle 数据库面试总结

    2018-08-24 14:38:18
    1.对字符串操作的函数? 答:ASCII() –函数返回字符表达式最左端字符的ASCII 码值 CHR() –函数用于将ASCII 码转换为字符 –如果没有输入0 ~ 255 之间的ASCII 码值CHR 函数会返回一个NULL 值 –应该是必须给chr()赋...

    1.对字符串操作的函数?
    答:ASCII() –函数返回字符表达式最左端字符的ASCII 码值
    CHR() –函数用于将ASCII 码转换为字符
    –如果没有输入0 ~ 255 之间的ASCII 码值CHR 函数会返回一个NULL 值
    –应该是必须给chr()赋数字值
    concat(str,str)连接两个字符串
    LOWER() –函数把字符串全部转换为小写
    UPPER() –函数把字符串全部转换为大写
    LTRIM() –函数把字符串头部的空格去掉
    RTRIM() –函数把字符串尾部的空格去掉
    TRIM() –同时去掉所有两端的空格
    实际上LTRIM(),RTRIM(),TRIM()是将指定的字符从字符串中裁减掉
    其中LTRIM(),RTRIM()的格式为xxxx(被截字符串,要截掉的字符串),但是TRIM()的格式为TRIM(要截掉的一个字符 from 被截的字符串)
    SUBSTR() –函数返回部分字符串
    INSTR(String,substring) –函数返回字符串中某个指定的子串出现的开始位置,如果不存在则返回0
    REPLACE(原来的字符串,要被替换掉的字符串,要替换成的字符串)
    SOUNDEX() –函数返回一个四位字符码
    –SOUNDEX函数可用来查找声音相似的字符串但SOUNDEX函数对数字和汉字均只返回NULL 值 

    2、事务概念
    答案:事务是这样一种机制,它确保多个SQL语句被当作单个工作单元来处理。事务具有以下的作用:
    * 一致性:同时进行的查询和更新彼此不会发生冲突,其他用户不会看到发生了变化但尚未提交的数据。
    * 可恢复性:一旦系统故障,数据库会自动地完全恢复未完成的事务。
    3、oracle中查询系统时间
    答:select sysdate from dual
    4、触发器的作用,什么时候用触发器,创建触发器的步骤,触发器里是否可以有commit, 为什么?
    答案:触发器是可以由事件来启动运行的,存在于数据库服务器中的一个过程。
    他的作用:可以实现一般的约束无法完成的复杂约束,从而实现更为复杂的完整性要求。
    使用触发器并不存在严格的限定,只要用户想在无人工参与的情况下完成一般的定义约束不可以完成的约束,来保证数据库完整性,那么就可以使用触发器。
    由于触发器主要是用来保证数据库的完整性的,所以要创建一个触发器,首先要明确该触发器应该属于那一种(DML,INSTEAD OF,SYSTEM)因为他们各有个的用途;其次就是要确定触发器被触发以后所设计到的数据。
    出发器中不可以使用COMMIT。
    5.数字函数
    abs()绝对值exp(value)e的value次幂ceil()大于等于该值的最小整数floor()小于等于该值的最大整数trunc(value,precision)保留precision个小数截取valueround(value,precision)保留precision个小数对value进行四舍五入 sign()根据值为正负零返回1,-1,0mod()取模操作power(value,exponent)value的exponent次幂sqrt()求平方根
    1.初级
    4、关系数据库系统与文件数据库系统的区别在那里?关系数据库系统一般适用那些方面?
    答案:
    关系数据库系统文件系统的区别在于:
    首先,关系性数据库的整体数据是结构化的,采用关系数据模型来描述,这是它与文件系统的根本区别。(数据模型包括:数据结构,数据操作以及完整性约束条件)
    其次,关系数据库系统的共享性高,冗余低可以面向整个系统,而文件系统则具有应用范围的局限性,不易扩展。
    第三,关系数据库系统采用两级映射机制保证了数据的高独立性,从而使得程序的编写和数据都存在很高的独立性。这方面是文件系统无法达到的,它只能针对于某一个具体的应用。(两级映射:保证逻辑独立性的外模式/模式映射和保证物理独立性的内模式/模式映射。外模式:用户模式,是数据库用户的局部数据的逻辑结构特征的描述。模式:数据库全体数据的逻辑结构特征的描述。内模式:也就是数据最终的物理存储结构的描述。)
    第四,就是关系性数据库系统由统一的DBMS进行管理,从而为数据提供了如安全性保护,并发控制,完整性检查和数据库恢复服务。

    5、触发器的概念,存储过程的概念.
    答案:
    触发器: 是存储在数据库中的过程,当表被修改(增、删、改)时它隐式地被激发。
    存储过程:是数据库语言SQL的集合,同样也存储在数据库中,但是他是由其他应用程序来启动运行或者也可以直接运行。
    6、基本SQL语句有哪些.
    答案:
    select、insert、update、delete、create、drop、truncate
    1.中级
    8、什么是事务一致性 ? 选择熟悉的数据库实现一个事务处理,如信用卡提款.
    答案:
    事务的一致性:是事务原子性的体现,事务所对应的数据库操作要么成功要么失败没有第三种情况。事务不管是提交成功与否都不能影响数据库数据的一致性状态。
    事务:用户定义的一个数据库操作序列,这些操作要么全部成功完成要么全部不做,是一个不可分割的整体。定义事务的SQL语句有:BEGIN TRANSACTION,COMMIT,ROLLBACK。
    事务的原子性:就是事务所包含的数据库操作要么都做,要么都不做.
    事务的隔离性:事务对数据的操作不能够受到其他事务的影响。
    事务的持续性:也就是说事务对数据的影响是永久的。
    对’信用卡提款’这一事务而言就是要保证’提取到现金’和’卡帐号余额’的修改要同时成功或失败.
    BEGIN TRANSACTION
    读取A的帐户余额BALANCE;
    BALANCE=BALANCE-AMOUNT转帐金额;
    IF(BALANCE<0) THEN
    ROLLBACK;
    ELSE
    BEGIN
    将A的新余额写回;
    读取B的帐户余额BALANCEB;
    BALANCEB=BALANCEB+AMOUNT转帐金额;
    将B的新余额写回;
    COMMIT;
    END IF;
    END;

    9、实际编写和调试存储过程或触发器.
    答案:
    10、实现索引的方式? 索引的原理? 索引的代价? 索引的类型?
    答案: 实现索引的方式有两种:针对一张表的某些字段创建具体的索引,如对oracle: create index 索引名称 on 表名(字段名);在创建表时为字段建立主键约束或者唯一约束,系统将自动为其建立索引。
    索引的原理:根据建立索引的字段建立索引表,存放字段值以及对应记录的物理地址,从而在搜索的时候根据字段值搜索索引表的到物理地址直接访问记录。
    引入索引虽然提高了查询速度,但本身占用一定的系统存储容量和系统处理时间,需要根据实际情况进行具体的分析.
    索引的类型有:B树索引,位图索引,函数索引等。
    11、view 的概念 ? 何时应用?
    答案: view 是对表级数据的多角度的透视,适用于对查询安全性、灵活性有一定要求的环境
    12、sql语句例外处理?举例说明?
    答案: 当sql 语句在执行过程中出现意外时,如查询时,未查询到结果;更改时无记录等情况的应采取的措施.
    以oracle为例:测试其对exception的理解.如
    select 字段 into 变量 from table ;
    若查询出多条记录,应增加exception 例外处理.
    Oracle处理异常有三种:
    1.预定义的例外处理,EXCEPTION WHEN exception_name THEN
    2.EXCEPTION_INIT产生例外
    首先要declare 声明一个例外名;然后将例外名和错误代码绑定pragma exception_init(错误名,错误代码);最后在EXCEPTION 处when 错误名then
    3.用户自定义例外,不用EXCEPTION,当判断条件成熟时用raise_application_error(-20000~~~-20999,提示信息)。
    13、判断下列论述是否正确。 (1) 一个事务中的某条SQL命令提交时,其它SQL命令可以不提交。 (2) 在一个关系表中,主键可唯一标识一行记录。 (3) 一个关系表中的外键必定是另一表中的主键。 (4) 回滚可以在事务失败时,回到操作事务前的数据状态。
    答案: (1)错误 (2)正确 (3)正确 (4)正确
    14、以下哪些手段可保证数据库中数据的完整性和一致性: (1)自定义数据类型 (2)触发器 (3)主键和外键 (4)事务
    答案: (1)(2)(3)(4) (为什么有1)
    15、使用存储过程访问数据库比直接用SQL语句访问有哪些优点?
    答案:存储过程是预编译过的,执行时勿须编译,执行速度更快;存储过程封装了一批SQL语句,便于维护数据的完整性与一致性;可以实现代码的复用。
    1.高级
    16、对于精通的数据库系统描述其体系结构,主要包括存储机制、回滚机制、运行机制等.
    答案:对oracle 系统而言,描述sga的结构; 后台pmon,ckpt、lgwr,smon等进程的功能;表空间的分配策略; 回滚段的结构
    oracle的sga(系统全局区)包括的主要区有:数据库缓存区,重做日志缓存区,共享池(数据字典缓存和库缓存),大池等。数据库缓存区用来存放最近使用过的数据块主要和后台进程中的数据库写进程(DBWR)以及数据文件发生关系;重做日志缓存区用于存放操作数据库数据所产生的重做日志信息,与之合作的有重做日志写进程(LGWR)和重做日志文件;共享池主要缓存SQL/PLSQL,资源锁,控制信息等,其中的库缓存主要缓存被解析执行过的SQL/PLSQL库缓存可分为共享SQL和私有SQL两个区,共享SQL用于存放SQL语句的语法分析结果和执行计划,私有SQL则用来存放与具体SQL语句执行有关的绑定变量,会话参数等。
    ORACLE实例的另外一个重要部分就是其后台进程,主要的后台进程有:数据库写进程(DBWR),重做日志写进程(LGWR),系统监视器(SMON),进程监视器(PMON),检查点进程(CKPT)。DBWR主要是对数据库缓存区中的脏冷数据进行写入数据文件操作;LGWR主要是将对数据库数据操作所产生的重做日志信息写入到重做日志文件中;SMON完成由于非正常关闭数据库的情况下重起数据库时对数据库的恢复;PMON用来恢复失败的用户进程和服务进程,并释放其所占的系统资源;CKPT可以表示数据库在此出处于完整状态。
    逻辑存储结构:数据块BLOCK,区EXTENT,段SEGMENT,表空间TABLESPACE
    物理存储空间:表空间,数据文件,控制文件,日志文件,数据字典
    软件体系结构就是上边对SGA和后台进程的描述。
    17、对于精通的数据库系统描述其数据一致性的保证机制,包括lock,事务一致性等.
    答案: 在并发环境下,采用多种机制保持其数据的一致性,如oracle系统提供的事务级的一致性、行级锁、表级锁等等.
    18、对精通的数据库系统描述其联机备份机制、恢复机制,考核其对日志的理解.
    答案: 描述相关数据库的实时联机备份策略,如数据库系统在运行中通过何种方式保证其数据的实时备份, 出现问题时,应采取何种办法从联机备份进行恢复.
    对对oracle而言,其archive online 备份方式应如何设置、修改什么参数、如何安排备份空间等等.
    19、精通的数据库系统描述性能优化方法,包括优化的策略、参数、验证方法等.
    答案:
    20、Truncate; 与 delete;的区别?(这道题可以衍生出很多的问题比如:想快速的清除一个很大的表中的数据应该用设么操作?答案是:应该用truncate。还有,在对两个相同纪录数的分别表使用了truncate,和delete commit;后对这两个表进行count(*)统计记录数操作,那个表速度会快些?答案是:使用了truncate的会快些。)
    21、写一个‘游标’并使用它。(这道题应该注意的是:在使用游标后一定要记得关闭游标)。
    Declare
    Cursor cursor_name is
    Select * from tablename;
    Begin
    Open cursor_name;
    Loop
    Fetch cursor_name into bianliang;
    Exit when cursor_name%notfound;
    Sql……..
    End loop;
    Close cursor_name;
    End;
    22、在创建表的时候会设置
    pctfree 10
    pctused 40
    而创建表的索引的时候却没有
    pctused 40
    这是为什么?
    (这道题的主要思想是索引不能在pctused 40上得到什么好处)
    23、使用过的最大的表有多少行纪录?(这道题不是看应试者的技术过不过关,而是考察应试者是否有大型数据库的管理操作经验)
    不要对表的索引列进行函数操作,因为这样系统就不能使用索引,使查询变得很慢,但是在ora8i以后就已经开始支持函数的索引,弥补了这个不足。

    24、ORACLE数据库启动与关闭的步骤
    启动:启动实例—-加载数据库数据—–打开数据库
    关闭:关闭数据库—-卸载数据库数据—-关闭实例
    25、Delete与truncate的区别
    delete一般用于删除少量记录的操作,而且它是要使用回滚段并且要进行显示的提交事务。而truncate则使用于大量删除数据,而且隐式提交事务,其速度要比使用delete快的多。
    26、DDL和DML分别代表什么??
    DDL表示数据定义语言,在ORACLE中主要包括CREATE,ALTER,DROP
    DML表示数据操作语言,主要的DML有SELECT,INSERT,UPDATE,DELETE
    28、Javascript中动态效果时调用的函数。
    29、数据库中有若干条相同的记录,删除到只剩下一条记录。如何做,请用SQL语句通过ROW_ID来写出执行过程。
    Delete table_name where ziduan=’’ and rowed<(select max(rowed) from table_name where ziduan=’’);
    30、oracle数据库表存放到磁盘什么地方(什么物理空间上——大概意思)?
    Oracle数据库表存放在数据文件上。
    31、Oracle使用什么语句给用户分配权限?
    GRANT TO 语句
    32、你在项目现场,用户要求你向正在运行的表中添加一个字段,你该怎么做?
    第一种方法:关闭数据库,然后使用受限模式打开,由sys/sysdba来进行
    第二种方法:不关闭数据库,将数据库置于静默状态在SYS/SYSDBA模式下用ALTER SYSTEM QUISCE RESTRICTED,这种状态下只有SYS/SYSDBA才可以对数据库进行操作,修改完毕之后再退出静默状态ALTER SYSTEM UNQUISCE
    在这里复习到了数据库的两种特殊状态:静默状态(QUISCE)和挂起状态
    静默状态就是只有特殊权限的SYS/SYSDBA才可以对数据库进行操作,使用ALTER SYSTEM QUISCE RESTRICTED以后系统将等候活动着的会话主动结束,同时阻止建立新的会话,系统挂起所有的SQL语句,等恢复以后再重新激活会话执行挂起的SQL。
    挂起状态就是系统将数据库所有对物理文件(数据文件,控制文件,日志文件)的I/O操作都暂停,但是并不禁止非DBA用户对数据库进行操作。这种状态主要用于进行数据库备份。

    33、Oracle中回滚的概念?回滚段有什么作用。
    回滚就是在事务提交之前将数据库数据恢复到事务修改之前数据库数据状态。
    回滚段就是为回滚提供依据,记录的是事务操作数据库之前的数据或者对应于以前操作的操作,这个内容要根据以前的操作而定。比如说以前事务操作如果是UPDATE那么回滚段则存储UPDATE以前的数据,如果事务是DELETE操作那么存储的则是与之相对应的INSERT操作语句,相反如果事务操作是INSERT那么记录相应的则是DELETE操作了。
    34、Oracle的8I和9I有什么区别
    35、一张表有10万条记录,如何删除其中的任意20条记录?请用SQL语句进行操作
    36、客户端如何访问服务器端的oracle?如果客户端无法访问服务器端的oralce,可能会是什么原因?
    客户端通过网络或者进程方式以合法的用户身份来取得和服务器端ORACLE的连接。如果客户端无法访问服务器端ORACLE可能出现的原因是:用户无权访问;服务器端数据库并没有打开(启动数据库的第三步没有完成);如果服务器是在共享模式下的则有可能没有对应于该客户所使用的通信协议的调度进程Dnnn。
    37、oracle中执行语句错误时去哪里查找错误信息?
    Select * from USER_ERRORS
    38、select语句需要提交吗?insert和update语句呢?
    39、在执行insert语句并提交后,这些提交的数据存储到什么地方??
    被存储到数据文件中
    40、oracle中有哪些类型的文件?
    数据文件,控制文件,日志文件和数据字典
    41、介绍一下oracle的体系结构?
    逻辑体系结构:块,区,段,表空间
    物理体系结构:表空间,三大文件
    软件体系结构:SGA,后台进程
    42、谈谈对oracle的row_ id是否理解?请简述?
    ORACLE的row_id是一个伪列,其个是为18个字节可将这18个字节用6363来划分,分别表示段编号,数据文件编号,数据块编号和记录编号。
    Row_id表示的是一个记录的物理存储地址。
    43、oracle中如何删除用户?
    Oracle中使用DROP USER来删除用户,如果使用DROP USER CASCADE那么用户的对象也同时被删除掉。为了达到删除用户的效果而又不影响对用户下的对象的使用可以使用alter user username account lock将用户锁定。
    44、客户端对服务器端的oralce操作的流程是什么?
    专用模式下:用户通过应用程序进程试图去得到一个与ORACLE数据库服务器的连接客户端通过网络传递连接请求,ORACLE服务器则使用监听进程监听用户请求,并且来验证用户身份,通过验证则为用户分配一专用服务进程用户提交SQL语句专用服务进程则首先在SGA区的共享池中检查是否有与该SQL语句相似的已经被解析执行并且缓存的SQL语句,如果有则采用它的解析结果和执行计划执行SQL语句,如果没有则对SQL语句进行语法解析生成执行计划通过解析则执行操作获取数据将执行结果返回给客户。
    共享模式下:与专用模式不同在于当监听程序验证用户的合法性以后并不为它分配一个专用的服务进程,而是将该请求与响应的调度进程相联系起来,并将起放入到一个请求队列中,最终由响应的Dnnn来从调度队列中获取一个请求并为之分配一个空闲的服务进程,接下来有服务进程对该请求进行服务操作和专用方式下相同,处理完成之后由服务进程先将结果放入一个返回队列最后再由调度进程(Dnnn)将返回队列中的结果返回给对应的用户。
    45、exits和in在ORALCE数据库中那个执行效率更高?
    Exits执行效率比in高。因为:
    46、如何判断游标已经到最后一行?
    Cursor_name%notfound
    47、聚簇索引和普通索引在不同的SQL语句中哪个效率更高??(笔试题,原题回忆不起来,主题就是聚簇索引和普通索引的区别)
    概念类:
    聚合函数? Session的定义和用法? Oracle的存储过程 ? 什么是构造函数? 

    48、简述ORACLE中SGA的组成部分。
    答:系统全局区包括:共享池、重做日志缓存区、数据高速缓存区,大池,JAVA池。
    49、简述ORACLE的启动和关闭各有多少步骤?
    启动:启动实例、装载数据库数据、打开数据库。
    关闭:关闭数据库、卸载数据库数据、关闭实例。
    50、在Oracle表空间的分类和作用,如排序时数据将放在什么表空间?
    作用是为了突破存储容量的限制,是一个逻辑概念。排序数据放在临时表空间。
    51、执行COMMIT命令时,数据库将会发生什么改变,ROLLBACK呢?
    答:commit 提交时首先是与事务对应的重做日志信息将被写入到数据库物理文件中的重做日志文件中,至于是否会真正将事务操作的内容反映到数据文件还好看DBWR是否启动了。在完成数据库的插入,删除和修改操作时,只有当事务提交到数据库才算完成,有提交前只有操作数据库的本人才能看到,别人只有在最后提交完成才可以看到。
    ROLLBACK回滚当前尚未提交的事务,使数据库恢复到事务操作前的状态。
    52、用命令创建用户,并为用户授权。
    Create user user_name
    identified by password /
    identified externally/
    identified blobally as ‘CN=user’
    default tablespace tablespace_name
    temporary tablespace tablespace_name;
    grant role/privilege to user_name;

    53、写一个存储过程,使employee(name,age,emp_no,salary)表中的salary值在0-1000之间的员工的工资上涨20%,(提示:要求用到游标)
    Cteate or replace procedure emp_sal
    V_name employee.name%type;
    V_emp_no employee.emp_no%type;
    V_salary employee.salary%type;
    Cursor cursor_sal is
    Select name,emp_no,salary from employee where salary between 0 and 1000;
    Begin
    Open cursor_sal;
    Loop
    Fetch cursor_sal into v_name,v_emp_no,v_salary;
    Exit when cursor_sal%notfound;
    Update employee set salary=salary*1.2 where name=v_name and emp_no=v_emp_no;
    End loop;
    Close cursor_sal;
    Commit;
    End;
    54、ORACLE数据库都有哪些类型的文件?
    数据文件,控制文件,日志文件,参数文件
    55、用命令创建表空间、用户,并为用户授权、收回权限。
    Create tablespace tabllespacename
    Datafile ‘f:\orcl\user001.dbf size 20m
    Default storage(
    Initial 512k
    Next 512k
    minextents 2
    pctincrease 50%
    maxExitnts 2048)
    minimum extent 512k
    logging
    online
    permanent
    extent management dictionary;
    回收权限: revoke privilege from user;
    create tablespace tablespace_name
    datafile ‘’
    default storage(
    initial xxxkb
    next yykb
    minextents 2
    pctincrease nnnn
    maxextents mmm)
    logging
    online
    extent management dictionary/local[autoallocate/uniform size xxxmb];
    create user user_name
    identified by passwore/
    identified externally/
    identified globally as ‘CN=user’
    default tablespace tablespace_name
    temporary tablespace tablespace_name
    [account lock /unlock]
    grant connect to user_name;
    grant create table to suer_name;
    grant update on table_name to user_name;
    revoke create table from user_name;
    revoke update on table_name from user_name;
    56、在Oracle中如何更改用户名?
    57、如何在Oracle中查询某个用户下所有已建的表?
    答:select * from cat;
    57、执行TRUNCATE命令后,存储空间是否还存在,是否可被其他的表占用?
    执行TRUNCATE之后,为表分配的区空间将被回收HWM将回退,如果在使用TRUNCATE的时候没有指定REUSE STORAGE那么执行操作后仅仅留下由MINEXTENTS所指定的区否则表的所有空间将被回收用于再分配。
    59、何在Oracle中查看当前用户,通常有哪些方式?(提示:show user和select * from user_users)show user;/select username from user_users;
    60、出在Oracle中,创建表空间的语法结构,以及所含参数说明。
    Create tablespace tablespacename
    Datafile ‘’
    Logging/nologging
    Default storage(initial kb
    Next kb
    Minextents x
    Maxextents y
    Pctincrease z
    )
    online/not online
    extent management dictionary/local[autoallocate/uniform size kb];
    61、如何判断游标是否到了末尾?(提示:用%notfound)
    cursor_name%notfound
    62、在Oracle中,如何查看当前用户下的所有表空间?
    Select tablespace_name from user_tablespaces;
    63、在Oracle中,你所创建的表空间信息放在哪里?
    存放在数据字典中,数据字典内容对应于系统表空间SYSTEM表空间。
    64、ORACLE中的控制文件什么时候读取?
    ORACLE服务器启动时,先启动实例然后再读取数据库的各个文件当然也包括控制文件。也就是说在数据库服务器启动的第二步时读取。
    65、Oracle的表格信息存储在哪个地方?
    SYSTEM表空间的数据字典文件中。
    66、如何根据字典创建一个表空间,并说明参数?
    66、ORACLE中当一个用户正在操作一个执行过程,管理员此时取消了他的权限,会发生什么事情?

    68、谈谈你对角色的理解,常用的角色有哪些?
    角色就是一组权限的数据库实体,它不属于任何模式或用户但是可以被授予任何用户。常用的角色有CONNECT,DBA,RESOURCE,SELECT_CATALOG_ROLE(查询所有表视图权),DELETE_CATALOG_ROLE(删除权限)等。
    角色的创建和授权:和创建用户为用户授权差不多。Create role role_name identified …
    grant 权限to role_name。
    69、简述Oracle的归档与不归档工作模式,分别说明。
    Oracle归档模式是指在创建数据库时指定了ARCHIVELOG参数,这种模式下,当重做日志文件写满的时候会将该重做日志文件的内容保存到指定的位置(由初始化文件中的参数ARCHIVE_LOG_DEST_n来决定)。并不是数据库在归档模式下工作的时候就可以自动完成归档操作,在归档模式下可以有两种归档方式:自动归档(在初始化文件中的参数ARCHIVE_LOG_START被设置为TRUE)和手动归档。如果归档模式下没有启动自动归档的话,而且又没有实行手动归档那么当LGWR进程将重做日志信息写入已经写满的重做日志文件时数据库将会被挂起直到进行了归档。可见归档是对重做日志文件信息的一种保护措施。
    Oracle非归档模式下当重做日志文件写满以后若是有LGWR进行重做日志信息的写入操作时,以前保存在重做日志文件中的重做日志信息就会被覆盖掉。
    70、Oracle索引分为哪几类,说出唯一索引和位图索引的概念。
    Oracle索引有B树索引,位图索引,函数索引,簇索引等。
    唯一索引也是B树索引的一种,它要求被索引的字段值不可以重复。在创建的时候使用B树算法创建。
    位图索引并不是采用象唯一索引那样存储(索引字段值,记录ROWID)来创建索引段的,而是为每一个唯一的字段值创建一个位图,位图中使用位元来对应一个记录的ROWID。位元到ROWID是通过映射的到的。
    71、ORACLE的基本数据类型有哪些?
    Char()存储定长字符,定义的时候可以不为他指定长度但是如若往里插入值则会出错;varchar2()存储变长字符定义的时候必须指定长度,date存储时间日期;Number()数字类型,包括整型,浮点型等;clob()大容量字符串;blob()大二进制对象
    72、SQL中,执行四舍五入的是哪个函数?
    Round(value,保留的小数位数)与只对应的还有一个特别相似的函数trunc(value,保留的小数位数)它的作用是根据要保留的小数位数来截取原数。
    73、oracle数据库表存放到磁盘什么地方?数据文件
    ORACLE数据库的表存放在物理文件中的数据文件中。
    74、当执行insert语句并提交后,这些提交的数据存储到什么地方??
    存储到了数据库的数据文件中。
    75、Exits和in在ORALCE数据库中那个执行效率更高?
    EXITS执行效率要比使用IN要快。
    76、ORACLE自己提供的函数,想知道具体位置,如何操作?
    77、数据库的几种物理文件?
    1)数据文件 2)控制文件 3)日志文件
    78、控制文件都含有哪些信息?
    控制文件存放有实例信息(实例名称创建时间等),数据文件和日志文件信息,还有系统运行时记录的系统变更码(SCN),检查点信息和归档的当前状态信息等。数据库在加载数据库的时候首先要读取控制文件获得和数据库有关的物理结构信息之后才能够正确加载数据文件和日志文件并打开数据库。
    79、Decode函数的用法?
    DECODE的语法:DECODE(value,if1,then1,if2,then2,if3,then3,…,else),表示如果value等于if1时,DECODE函数的结果返回then1,…,如果不等于任何一个if值,则返回else。初看一下,DECODE 只能做等于测试,但刚才也看到了,我们通过一些函数或计算替代value,是可以使DECODE函数具备大于、小于或等于功能。
    2.如何用decode进行大于小于的比较?
    利用sign()函数和DECODE和在一起用
    select decode(sign(变量1-变量2),-1,变量1,变量2) from dual;

    decode(条件,值1,翻译值1,值2,翻译值2,…值n,翻译值n,缺省值)
    该函数的含义如下:
    IF 条件=值1 THEN
    RETURN(翻译值1)
    ELSIF 条件=值2 THEN
    RETURN(翻译值2)
    ……
    ELSIF 条件=值n THEN
    RETURN(翻译值n)

    ELSE
    RETURN(缺省值)
    END IF
    • 使用方法:
    1、比较大小
    select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; –取较小值
    sign()函数根据某个值是0、正数还是负数,分别返回0、1、-1

    例如:
    变量1=10,变量2=20
    则sign(变量1-变量2)返回-1,decode解码结果为“变量1”,达到了取较小值的目的。

    2、表、视图结构转化
    现有一个商品销售表sale,表结构为:
    month char(6) –月份
    sell number(10,2) –月销售金额

    现有数据为:
    200001 1000
    200002 1100
    200003 1200
    200004 1300
    200005 1400
    200006 1500
    200007 1600
    200101 1100
    200202 1200
    200301 1300

    想要转化为以下结构的数据:
    year char(4) –年份
    month1 number(10,2) –1月销售金额
    month2 number(10,2) –2月销售金额
    month3 number(10,2) –3月销售金额
    month4 number(10,2) –4月销售金额
    month5 number(10,2) –5月销售金额
    month6 number(10,2) –6月销售金额
    month7 number(10,2) –7月销售金额
    month8 number(10,2) –8月销售金额
    month9 number(10,2) –9月销售金额
    month10 number(10,2) –10月销售金额
    month11 number(10,2) –11月销售金额
    month12 number(10,2) –12月销售金额

    结构转化的SQL语句为:
    create or replace view
    v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)
    as
    select
    substrb(month,1,4),
    sum(decode(substrb(month,5,2),’01′,sell,0)),
    sum(decode(substrb(month,5,2),’02′,sell,0)),
    sum(decode(substrb(month,5,2),’03′,sell,0)),
    sum(decode(substrb(month,5,2),’04′,sell,0)),
    sum(decode(substrb(month,5,2),’05′,sell,0)),
    sum(decode(substrb(month,5,2),’06′,sell,0)),
    sum(decode(substrb(month,5,2),’07′,sell,0)),
    sum(decode(substrb(month,5,2),’08′,sell,0)),
    sum(decode(substrb(month,5,2),’09′,sell,0)),
    sum(decode(substrb(month,5,2),’10′,sell,0)),
    sum(decode(substrb(month,5,2),’11′,sell,0)),
    sum(decode(substrb(month,5,2),’12′,sell,0))
    from sale
    group by substrb(month,1,4); 

    79、CASE语句的用法?
    Oracle用法很简单:
    SELECT last_name, job_id, salary
    CASE job_id
    WHEN ‘IT_PROG’ THEN 1.10*salary
    WHEN ‘ST_CLERK’ THEN 1.15*salary
    WHEN ‘SA_REP’ THEN 1.20*salary
    ELSE salary END “REVISED_SALARY”
    FROM employees 

    80、truncate和delete的区别?
    1、TRUNCATE在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令DELETE将被撤销,而TRUNCATE则不会被撤销。
    2、TRUNCATE是一个DDL语言而DELETE是DML语句,向其他所有的DDL语言一样,他将被隐式提交,不能对TRUNCATE使用ROLLBACK命令。
    3、TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时,经过TRUNCATE操作后的表比DELETE操作后的表要快得多。
    4、TRUNCATE不能触发触发器,DELETE会触发触发器。
    5、不能授予任何人清空他人的表的权限。
    6、当表被清空后表和表的索引讲重新设置成初始大小,而delete则不能。
    7、不能清空父表。
    81、表空间如何扩展?并用语句写出?
    两种扩展方式:
    a)增加数据文件
    alter tablespace tablespace_name add datafile ‘’ xxMB
    b)扩展数据文件大小
    alter database datafile ‘’ resize newMB
    82、表空间区管理方式?哪种方式现在是推荐使用的?
    a)字典管理方式
    extent management dictionary;默认方式
    b)本地管理方式
    extent management local[autoallocate/uniform xxmb];
    83、用什么函数获得日期?和日期中的月,日,年
    to_char(sysdate,’year’):tow thsound six to_char(sysdate,’yyyy’) :2006
    to_char(sysdate,’month’):8月to_char(sysdate,’mm’):08
    to_char(sysdate,’day’):星期4 to_char(sysdate,’dd’):22
    84、分区表的应用?

    a)一个分区表有一个或多个分区,每个分区通过使用范围分区、散列分区、或组合分区分区的行
    b)分区表中的每一个分区为一个段,可各自位于不同的表空间中
    c)对于同时能够使用几个进程进行查询或操作的大型表分区非常有用

    85、谈谈索引的用法及原理?
    索引是若干数据行的关键字的列表,查询数据时,通过索引中的关键字可以快速定位到要访问的记录所在的数据块,从而大大减少读取数据块的I/O次数,因此可以显著提高性能。
    86、存储过程的应用,如何既有输入又有输出?
    Create procedure pro_name
    (xxxx in/out type;
    yyyy in/out/inout type;
    ) is/as
    zzzz type;
    begin
    sqlpro;
    exception
    exceptionxxxxx;
    commit;
    end;
    87、常发生的异常有哪些?
    常用预定义例外
    CURSOR_ALREADY_OPEN — ORA-06511 SQLCODE = -6511 游标已经打开
    DUP_VAL_ON_INDEX — ORA-00001 SQLCODE = -1 违反唯一性约束
    INVALID_CURSOR — ORA-01001 SQLCODE = -1001 非法游标操作
    INVALID_NUMBER — ORA-01722 SQLCODE = -1722 字符向数字转换失败
    LOGIN_DENIED — ORA-01017 SQLCODE = -1017
    NO_DATA_FOUND — ORA-01403 SQLCODE = +100 没有找到数据
    NOT_LOGGED_ON — ORA-01012 SQLCODE = -1012 没有连接到数据库
    PROGRAM_ERROR — ORA-06501 SQLCODE = -6501 内部错误
    STORAGE_ERROR — ORA-06500 SQLCODE = -6500
    TIMEOUT_ON_RESOURCE — ORA-00051 SQLCODE = -51
    TOO_MANY_ROWS — ORA-01422 SQLCODE = -1422 返回多行
    TRANSACTION_BACKED_OUT — ORA-00061 SQLCODE = -61
    VALUE_ERROR — ORA-06502 SQLCODE = -6502 数值转换错误
    ACCESS_INTO_NULL试图为NULL对象的属性赋值
    ZERO_DIVIDE — ORA-01476 SQLCODE = -1476 被零除
    OTHERS — 其它任何错误的处理 

    88、如何使用异常?
    在oracle中有三种类型的异常。预定义的异常 非预定义的异常 用户定义的异常 第二种非预定义的异常是与特定的oracle错误关联。并且用PRAGM EXCEPTION_INIT(EXCEPTION_NAME,ERROR_NUMBER)关联一起的。但是到底有什么用啊? 例如:declare dup_primary_key exception; pragma exception_init(dup_primary_key,-1); begin insert into itemfile values(‘i201′,’washer’,'spares’,100,50,250,12,30); exception when dup_primary_key then dbms_output.put_line(‘重复项编号-主键冲突’); end
    第一种的使用方法:exception
    when 异常名称 then
    异常处理代码;
    第三种的用法:if 条件 then
    raise_application_error(-20000“““`-20999,提示信息);
    end if;
    89、优化的策略一般包括:
    •内存优化
    •操作系统优化
    •数据存储的优化
    •网络优化等方法
    具体到不同的数据库涉及到要调整不同的数据库配置文件、不同的操作系统参数、网络参数等等, 不同的数据库不同.

    展开全文
  • oracle数据库权限管理

    2014-08-04 18:45:32
    oracle数据库使用权限
  • oracle DML、DDL语句区别

    千次阅读 2014-01-03 17:57:00
    DML:数据操作语言,SQL中处理数据等操作统称为数据操纵语言  它们是SELECT、UPDATE、INSERT、DELETE,就象它的名字一样,这4条命令是用来对数据库里的数据进行操作的语言,不修改表的结构,只修改表的内容 需要...
  • oracle中使用sql语句执行了delete和update和insert命令,在本地数据库中已执行成功查看并没有删除成功或更新数据库,很是尴尬,为什么会出现这种不一致的情况呢? 原来是因为我执行命令后没有commit提交,那么...
  • Oracle 事务的开始与结束

    千次阅读 2015-08-20 11:48:47
    事务是用来分割数据库活动的逻辑工作单元,事务即起点,也终点; 当下列事件之一发生时,事务就开始了: 连接到数据库上,并执行了第一天 DML 语句; 当前一个事务结束后,又输入了另外一条 DML 语句; 当...
  • oracle:何时需要commit命令提交

    千次阅读 2016-11-25 08:12:51
    SQL语言分为五大类: DDL(数据定义语言) - Create、Alter、Drop ...DML(数据操纵语言) - Insert、Update、Delete 这些语句需要Commit才能提交。 DTL(事务控制语言) - Commit、Rollback 事务提交与回滚语句。 DCL(数据
  • Oracle锁表查询和解锁方法

    万次阅读 多人点赞 2016-12-09 15:12:40
    数据库操作语句的分类 DDL:数据库模式定义语言,关键字:create DML:数据操纵语言,关键字:Insert、delete、update DCL:数据库控制语言 ,关键字:grant、remove DQL:数据库查询语言,关键字:select oracle表...
  • Oracle数据操作和控制语言详解 (一)

    千次阅读 2004-07-31 16:37:00
    SQL语言共分为四大类:数据查询语言DQL,数据操纵语言DML, 数据定义语言DDL,数据控制语言DCL。... DML语言 DML是SQL的一个子集,主要用于修改数据,下表列出了ORACLE支持的DML语句。 语句 用途 INSERT 向表
  • oracle 学习笔记 - commit和rollback

    千次阅读 2017-08-21 14:43:10
    COMMIT 是提交你的DML操作.ROLLBACK 是取消你的DML操作. DML操作主要指一些插数据\更新数据的操作,你的操作在没有COMMIT和ROLLBACK前,都还在本机的SESSION中,还没有更新至数据库。1、首先commit和rollback是在进行...
  • SQL语言的3种分类

    万次阅读 2020-06-13 14:53:08
    允许用户对数据库中的数据进行查询 ,插入,更新和删除等操作常用的DML语句及其功能:DML语句功能说明SELECT从表或视图中检索数据行INSERT插入数据到表或视图UPDATE更新数据DELETE删除数据CALL调用过程M...
  • ORACLE的plsql命令窗口与sql窗口的区别

    千次阅读 2018-02-11 11:13:29
    SW(SQL窗口): 执行的是dml,ddl语句,主要用户语句的查询、显示、执行统计信息等(应用最多的一个窗口)。例如 desc table不能在SQL window中执行,必须在Command window中才能执行。RW(报告窗口...
  • Sql语句中的DDL、DML、DCL的介绍

    万次阅读 2016-12-10 16:09:23
    一、DDL DDL is Data Definition Language statements. Some examples:数据定义语言,用于定义和管理 SQL 数据库中的所有对象的语言 1.CREATE - to create objects in the database 创建 ...
  • Oracle SQL语句分类

    千次阅读 2015-04-08 21:52:01
    Oracle SQL(Structured Query Language)即结构化查询语句主要分为四类: 1、DML(Data Mannipulation Language)数据操纵语言:查询、操纵数据表资料行; 2、DDL(Data Definition Language)数据定义语言:建立、修改、...
  • Oracle什么时候需要Commit?

    千次阅读 2018-08-13 16:05:06
    作为一个小白,刚接触数据库操作,今天在oracle中使用sql语句执行了delete和update命令,在本地数据库中已执行成功,兴致冲冲的向老大汇报任务时,但是他那边查看并没有删除成功或更新数据库,很是尴尬,为什么会...
  • SQL语言分类DDL、DML、DQL、TCL、DCL

    千次阅读 2017-12-10 15:37:35
    关系型数据库的SQL语句都可以分为4大类:1. DDL(数据定义语言)2. DML(数据操纵语言)3. DQL(数据查询语言)4. TCL(事务处理语言)5. DCL(数据控制语言)
  • Oracle 的基础创建、增删改查sql语句

    千次阅读 2018-05-12 10:30:18
    Oracle一.下载软件上网按照步骤进行下载和安装二.用户(1)创建用户(2)删除用户三.SQL分类(重点)数据定义语言(DDL)--&gt;对表的操作:create创建表 alter 修改 drop 删除数据操纵语言(DML)--&gt;对表中数据的...
  • Oracle数据库基础(一)

    万次阅读 多人点赞 2019-05-29 21:52:41
    一、数据库简介 1.数据库系统的发展 文件存储方式保存数据的弊端: (1)缺乏对数据的整体管理,数据不便修改。 (2)不利于数据分析和共享。 (3)数据量急剧增长,大量数据不可能长期保存在文件中。...
1 2 3 4 5 ... 20
收藏数 20,314
精华内容 8,125
关键字:

dml语句权限有哪些 oracle