首页 > 代码库 > 【oracle ocp 知识点二】

【oracle ocp 知识点二】

1.数据库操纵语言

DML语句实在下面的时候执行

向表添加新行
更新表现有的行
从表删除现有行
一个事务处理是由一系列的DML语句逻辑组成
A.insert 每次插入一行数据 字符和日期的需要单引号引起来,日期的插入需要to_date()处理
SQL> insert into dept values(54,‘‘,null); //插入空值
1 row created.
SQL> select * from dept;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        54
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
一次插入多条数据使用子查询,即insert into es select * from emp;
DML事务结束需要commit/DDL/DCL/exit/conn
取消事务 rollback/quit
SQL> alter table dept modify(loc default ‘TX‘);
Table altered.
SQL> insert into dept values (56,default,default);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dept where deptno=56;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        56                TX
SQL> desc user_tab_columns
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 COLUMN_NAME                               NOT NULL VARCHAR2(30)
 DATA_TYPE                                          VARCHAR2(106)
 DATA_TYPE_MOD                                      VARCHAR2(3)
 DATA_TYPE_OWNER                                    VARCHAR2(30)
 DATA_LENGTH                               NOT NULL NUMBER
 DATA_PRECISION                                     NUMBER
 DATA_SCALE                                         NUMBER
 NULLABLE                                           VARCHAR2(1)
 COLUMN_ID                                          NUMBER
 DEFAULT_LENGTH                                     NUMBER
 DATA_DEFAULT                                       LONG
 NUM_DISTINCT                                       NUMBER
 LOW_VALUE                                          RAW(32)
 HIGH_VALUE                                         RAW(32)
 DENSITY                                            NUMBER
 NUM_NULLS                                          NUMBER
 NUM_BUCKETS                                        NUMBER
 LAST_ANALYZED                                      DATE
 SAMPLE_SIZE                                        NUMBER
 CHARACTER_SET_NAME                                 VARCHAR2(44)
 CHAR_COL_DECL_LENGTH                               NUMBER
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 AVG_COL_LEN                                        NUMBER
 CHAR_LENGTH                                        NUMBER
 CHAR_USED                                          VARCHAR2(1)
 V80_FMT_IMAGE                                      VARCHAR2(3)
 DATA_UPGRADED                                      VARCHAR2(3)
 HISTOGRAM                                          VARCHAR2(15)
SQL> select column_name,data_default from user_tab_columns where table_name=‘DEPT‘;


COLUMN_NAME      DATA_DEFAULT
--------------   --------------
DEPTNO
DNAME
LOC                  ‘TX‘
B.update 根据需要可以更新一条或者多条记录
C.delete 不释放表空间,truncate 删除释放空间
SQL> analyze table e3 compute statistics;
Table analyzed.
SQL> select num_rows,blocks from user_tables where table_name=‘E3‘;
  NUM_ROWS     BLOCKS
---------- ----------
       112          8
SQL> delete e3;
112 rows deleted.
SQL>  analyze table e3 compute statistics;
Table analyzed.
SQL> select num_rows,blocks from user_tables where table_name=‘E3‘;
  NUM_ROWS     BLOCKS
---------- ----------
         0          8
SQL> truncate table e3;
Table truncated.
SQL> analyze table e3 compute statistics;
Table analyzed.
SQL> select num_rows,blocks from user_tables where table_name=‘E3‘;
  NUM_ROWS     BLOCKS
---------- ----------
         0          0
表经常delete的话会造成空间的浪费,就是说删除后空间没有释放
解决方法:alter table e3 mvoe;或者导出表
SQL> c/emp/e3
  1* select count(*) from e3
SQL> /
  COUNT(*)
----------
       196
SQL> analyze table e3 compute statistics;
Table analyzed.
SQL> select num_rows,blocks from user_tables where table_name=‘E3‘;
  NUM_ROWS     BLOCKS
---------- ----------
       196          5
SQL> delete e3 where rownum<180;
179 rows deleted.
SQL>  select count(*) from e3;
  COUNT(*)
----------
        17
SQL> analyze table e3 compute statistics;
Table analyzed.
SQL> select num_rows,blocks from user_tables where table_name=‘E3‘;
  NUM_ROWS     BLOCKS
---------- ----------
        17          5
SQL> alter table e3 move;
Table altered.
SQL>  analyze table e3 compute statistics;
Table analyzed.
SQL>  select num_rows,blocks from user_tables where table_name=‘E3‘;
  NUM_ROWS     BLOCKS
---------- ----------
        17          4
2.事务
DDL/DCL语句自动提交
事务结束与开始
save point 保存点 rollback to 保存点
3.数据类型
vrachar2 4000
char 2000
number(p,s) 
interval 间隔
SQL> create table t1(id number,t1 date,t2 timestamp,t3 timestamp with time zone,
    t4 timestamp with local time zone,t5 interval year(5) to month,
    t6 interval day(6) to second );
Table created.
SQL> insert into t1 values(1,sysdate,sysdate,sysdate,sysdate,‘1-10‘,‘3 2:10:10‘);


1 row created.


SQL> select * from t1;


        ID T1
---------- ---------
T2
---------------------------------------------------------------------------
T3
---------------------------------------------------------------------------
T4
---------------------------------------------------------------------------
T5
---------------------------------------------------------------------------
T6
---------------------------------------------------------------------------
         1 19-JUL-14


        ID T1
---------- ---------
T2
---------------------------------------------------------------------------
T3
---------------------------------------------------------------------------
T4
---------------------------------------------------------------------------
T5
---------------------------------------------------------------------------
T6
---------------------------------------------------------------------------
19-JUL-14 09.53.11.000000 PM


        ID T1
---------- ---------
T2
---------------------------------------------------------------------------
T3
---------------------------------------------------------------------------
T4
---------------------------------------------------------------------------
T5
---------------------------------------------------------------------------
T6
---------------------------------------------------------------------------
19-JUL-14 09.53.11.000000 PM +08:00


        ID T1
---------- ---------
T2
---------------------------------------------------------------------------
T3
---------------------------------------------------------------------------
T4
---------------------------------------------------------------------------
T5
---------------------------------------------------------------------------
T6
---------------------------------------------------------------------------
19-JUL-14 09.53.11.000000 PM


        ID T1
---------- ---------
T2
---------------------------------------------------------------------------
T3
---------------------------------------------------------------------------
T4
---------------------------------------------------------------------------
T5
---------------------------------------------------------------------------
T6
---------------------------------------------------------------------------
+00001-10


        ID T1
---------- ---------
T2
---------------------------------------------------------------------------
T3
---------------------------------------------------------------------------
T4
---------------------------------------------------------------------------
T5
---------------------------------------------------------------------------
T6
---------------------------------------------------------------------------
+000003 02:10:10.000000


        ID T1
---------- ---------
T2
---------------------------------------------------------------------------
T3
---------------------------------------------------------------------------
T4
---------------------------------------------------------------------------
T5
---------------------------------------------------------------------------
T6
---------------------------------------------------------------------------
还原scott资料用户
SQL>startup ?/rdbms/admin/utlsampl
SQL>drop user sctt scade;
SQL>select username,sid,serial# from v$session where username=‘SCOTT‘;
SQL>alter system kill session ‘44,21‘;
4.DDL的管理及操作
数据库对象:表 视图 序列 索引 同义词
命名规则:以字母开头、1-30字符、A-Z/a-z/0-9/_/$、字符开头
database link 128
库名 8
实例名 12
使用内部保留字,小写加上双引号即可
create table "user"....
alter table t1 modify (sal default null);
5.约束
自动命名(sys)、手动命名
列级约束 非空只能在列级定义 逗号隔开
表级约束 空格隔开
非空约束 not null constraint
SQL> create table b(id number not null);
Table created.


SQL> insert into b values(null);
insert into b values(null)                    *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."B"."ID")


SQL> alter table b modify (id null);
Table altered.


SQL> alter table b modify (id constraint b_id_null not null);
Table altered.
唯一性约束,自动简历唯一索引
SQL> create table c(id number unique);
Table created.
SQL> insert into c values(1);
1 row created.
SQL> /
insert into c values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0014385) violated
SQL> select constraint_name from user_constraints where table_name=‘C‘;


CONSTRAINT_NAME
------------------------------
SYS_C0014385
SQL>alter table c drop constraint sys_c0014385
Table altered.
SQL> alter table c add constraint a_id_u unique(id);
Table altered.
主键约束
一个表只能有一个主键
主键是唯一的并且非空
可以联合主键,联合主键要求每列都非空
主键唯一定位一行,所有主键也叫逻辑ROWID
主键不是必须的,可以没有
主键是通过索引实现的
索引的名称和主键的名称相同
外键约束
  表级列级都可以定义
  表级定义关联到子表中的列
  执行删除操作时会出现错误,特别注意
检查约束 check constraint
   check.....
违反约束 violating constraint
启用/停用 enable disable
建立表使用子查询
create table e as select * from emp; 新表不包含数据
create table e as select * from emp where 0=1;
alter table e read only; 使表只读
alter table e read write;
10g没有只读这个说法,所以只能建成一个试图
SQL> drop table c;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
C                BIN$/o6ox8o+iMjgQ2YAqMDn3Q==$0 TABLE        2014-07-19:23:38:02
SQL> flashback table c to before drop;
Flashback complete.
SQL> alter table c add (name varchar2(20));
Table altered.
SQL> select * from c;


        ID NAME
---------- --------------------
         1
SQL> alter table c modify(name varchar2(28));
Table altered.
SQL> alter table c rename column name to dname;
Table altered.
SQL> alter table c drop column dname;
Table altered.
SQL> alter table c set unused column sex; //标记为不使用,在业务低峰期删除
Table altered.
SQL> alter table c drop unused columns;
Table altered.
SQL> alter table c drop column dname;
Table altered.
6.视图
本身就是一个查询语句
限制数据访问
复杂查询简单化
提供数据独立性
没有自己的数据,来源于查询结果
简单试图 一个表  dml不限制
复杂试图  一个或者多个表  dml限制
create [or replace] [force|noforce] view
.... as subquery
with check option ...
with read only ...
子查询不能包含order by
SQL> select * from session_privs;


PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
SQL> grant create view to scott; 授权创建试图
SQL>  create force view v1 as select * from v;
Warning: View created with compilation errors.
SQL> select object_name,status from user_objects where object_name=‘V1‘;


OBJECT_NAME
--------------------------------------------------------------------------------
STATUS
-------
V1
INVALID
SQL> select text from user_views where view_name=‘V1‘;


TEXT
--------------------------------------------------------------------------------
select * from v
创建基表,执行查询
SQL> select object_name,status from user_objects where object_name=‘V1‘;


OBJECT_NAME
--------------------------------------------------------------------------------
STATUS
-------
V1
VALID
SQL> create or replace view empsal as select * from emp where sal>2000 with check option;


View created.
SQL> select sal from empsal;


       SAL
----------
      2975
      2850
      2450
      3000
      5000
      3000
SQL> update empsal set sal=1799 where sal=2450;
update empsal set sal=1799 where sal=2450
       *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
SQL> update empsal set sal=7000 where sal=2450;


1 row updated.
drop view empsal;
视图DML操作限制
  碰到如下语法不能删除试图行数据
     使用分组函数
     使用group by语法
     使用去除重复行语句
     使用了rownum伪列
  修改限制
     使用分组函数
     使用group by语法
     使用去除重复行语句
     使用了rownum伪列
     使用了表达式
  insert操作限制
     使用分组函数
     使用group by语法
     使用去除重复行语句
     使用了rownum伪列
     使用了表达式
     非空约束的列没在select列表中引用
7.索引
自动建立
手动建立
creat unique|bitmap index ...on table...
多个列上建立索引需要注意顺序
user_indexes
user_ind_columns
基于函数的建立索引
SQL> set autot trace exp;
SQL> select * from emp where ename=‘SCOTT‘;


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("ENAME"=‘SCOTT‘)


SQL> create index emp_ename_i on emp(ename);


Index created.


SQL> select * from emp where ename=‘SCOTT‘;


Execution Plan
----------------------------------------------------------
Plan hash value: 549418132


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


| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|
 Time     |


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


|   0 | SELECT STATEMENT            |             |     1 |    38 |     2   (0)|
 00:00:01 |


|   1 |  TABLE ACCESS BY INDEX ROWID| EMP         |     1 |    38 |     2   (0)|
 00:00:01 |


|*  2 |   INDEX RANGE SCAN          | EMP_ENAME_I |     1 |       |     1   (0)|
 00:00:01 |


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




Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("ENAME"=‘SCOTT‘)
SQL> select * from emp where substr(ename,1)=‘KING‘;


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter(SUBSTR("ENAME",1)=‘KING‘)


SQL> create index emp_e_i on emp(substr(ename,1));


Index created.


SQL> select * from emp where substr(ename,1)=‘KING‘;


Execution Plan
----------------------------------------------------------
Plan hash value: 1426330053


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


| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Tim
e     |


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


|   0 | SELECT STATEMENT            |         |     1 |    38 |     2   (0)| 00:
00:01 |


|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    38 |     2   (0)| 00:
00:01 |


|*  2 |   INDEX RANGE SCAN          | EMP_E_I |     1 |       |     1   (0)| 00:
00:01 |


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




Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access(SUBSTR("ENAME",1)=‘KING‘)
8.序列
自动产生唯一值
是一个共享对象
典型的用于创建主键值
可替代应用程序代码
如果将序列值缓存在内存中可以提交访问效率
create sequence ...
    increment by ...
    start with ...
    ...
SQL> create sequence s increment by 1 start with 50;


Sequence created.
SQL> insert into dept values(s.nextval,‘D‘||s.nextval,‘LL‘||s.nextval);


1 row created.


SQL> select * from dept;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        54
        56                TX
        50 D50            LL50
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
SQL> select s.currval from dual;


   CURRVAL
----------
        50
SQL> desc user_sequences
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SEQUENCE_NAME                             NOT NULL VARCHAR2(30)
 MIN_VALUE                                          NUMBER
 MAX_VALUE                                          NUMBER
 INCREMENT_BY                              NOT NULL NUMBER
 CYCLE_FLAG                                         VARCHAR2(1)
 ORDER_FLAG                                         VARCHAR2(1)
 CACHE_SIZE                                NOT NULL NUMBER
 LAST_NUMBER                               NOT NULL NUMBER


SQL> select s.currval+increment_by from user_sequences where sequence_name=‘S‘;


S.CURRVAL+INCREMENT_BY
----------------------
                    51
SQL> alter sequence s cycle cache 10;


Sequence altered.
SQL> select s.currval+increment_by from user_sequences where sequence_name=‘S‘;


S.CURRVAL+INCREMENT_BY
----------------------
                    51


SQL> select last_number from user_sequences where sequence_name=‘S‘;


LAST_NUMBER
-----------
         51


SQL> insert into dept values(s.nextval,‘D‘||s.nextval,‘LL‘||s.nextval);


1 row created.


SQL> select last_number from user_sequences where sequence_name=‘S‘;


LAST_NUMBER
-----------
         61


SQL> select s.currval+increment_by from user_sequences where sequence_name=‘S‘;


S.CURRVAL+INCREMENT_BY
----------------------
                    52


SQL> alter sequence s maxvalue 55;


Sequence altered.


SQL> create table dept1 as select * from dept;


Table created.


SQL>  insert into dept1 values(s.nextval,‘D‘||s.nextval,‘LL‘||s.nextval);


1 row created.


SQL> /


1 row created.


SQL> /


1 row created.


SQL> /


1 row created.


SQL> /


1 row created.


SQL> select * from dept1;


    DEPTNO DNAME          LOC
---------- -------------- -------------
        54
        56                TX
        50 D50            LL50
        51 D51            LL51
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        52 D52            LL52
        53 D53            LL53
        54 D54            LL54


    DEPTNO DNAME          LOC
---------- -------------- -------------
        55 D55            LL55
         1 D1             LL1
******序列需要调用后才会有当前值,否则是没有的。********
序列中start with不能修改,其他都可以修改
序列不连续
发生回滚
系统崩溃
被其他对象调用过
12c实现自动增长
9.同义词
对象的别名
共有的
私有的
create synonym ... for ...
SQL> grant create synonym to scott;


Grant succeeded.
SQL> l
  1* grant create public synonym to scott
SQL> /


Grant succeeded.
SQL> create synonym  es for empsal;


Synonym created.


SQL> desc es;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)
给表起个别名
SQL> desc user_synonyms
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SYNONYM_NAME                              NOT NULL VARCHAR2(30)
 TABLE_OWNER                                        VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 DB_LINK                                            VARCHAR2(128)
公有的同义词,在当前用户下有两个同名的同义词,一个私有的,一个是共有的,访问时先访问的是私有的
SQL> desc all_synonyms
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 SYNONYM_NAME                                       VARCHAR2(30)
 TABLE_OWNER                                        VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)

 DB_LINK                                            VARCHAR2(128)

1.控制用户访问
系统权限
对象权限
角色权限
数据库安全性 
   系统安全
   数据安全
create user identifued by password; 12c 公有账号(PDB)、私有账号(CDB)c##开头的
grant privilege,... to suer
SQL> desc system_privilege_map;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PRIVILEGE                                 NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(40)
 PROPERTY                                  NOT NULL NUMBER
不同版本系统权限个数不一样,
SQL> select name from system_privilege_map;
SQL> create user u1 identified by oracle;
User created.
SQL> conn u1/oracle
ERROR:
ORA-01045: user U1 lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> grant create session to u1;
Grant succeeded.
SQL> grant unlimited tablespace to u1;
Grant succeeded.
11g建立用户之后有默认使用表空间的权限,而10g则没有该权限,需要给用户授予访问表空间的权限
12c cdb库必须是C##开头的,
create user c##u1 identified by oracle;
show con_name;
插件数据库启动在mont或者open状态
desc v$pdbs
select con_id,name from v$pdbs; 查看插件数据可数量
select * from user_sys_privs; 查看用户具备系统的权限
回收权限指令
revoke create table from  u1;
select * from dba_sys_privs where grantee=‘U1‘;
级联授权问题
grant create table to scott with admin option; //sys
grant create table to v1;//scott
如果收回scott的create权限,此时v1的还是有create权限,但是此时的授予者变成了sys而不是scott
对象权限
SQL> grant select on scott.emp to u1;
Grant succeeded.
SQL> grant update(sal)on scott.emp to u1;
Grant succeeded.
SQL> grant select on scott.dept to u1 with grant option; 对象权限级联
Grant succeeded.
SQL> select * from user_tab_privs;


GRANTEE                        OWNER
------------------------------ ------------------------------
TABLE_NAME                     GRANTOR
------------------------------ ------------------------------
PRIVILEGE                                GRA HIE
---------------------------------------- --- ---
U1                             SCOTT
DEPT                           SCOTT
SELECT                                   YES NO


U1                             SCOTT
EMP                            SCOTT
SELECT                                   NO  NO


GRANTEE                        OWNER
------------------------------ ------------------------------
TABLE_NAME                     GRANTOR
------------------------------ ------------------------------
PRIVILEGE                                GRA HIE
---------------------------------------- --- ---
SQL> update scott.emp set sal=sal+1;


14 rows updated.


SQL> update scott.emp set comm=1;
update scott.emp set comm=1
             *
ERROR at line 1:
ORA-01031: insufficient privileges
对象权限的回收,级联的权限也会收回的。例如A对象的有某个权限,A对象有创建了B,当A的某个权限被收回时,B也相应该的权限也收回去了。
角色权限
create role manager;
create role c##manager; 12c
connect resource dba select_catlog_owner recovery_catalog_owner
SQL> desc dba_roles
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ROLE                                      NOT NULL VARCHAR2(30)
 PASSWORD_REQUIRED                                  VARCHAR2(8)
 AUTHENTICATION_TYPE                                VARCHAR2(11)


SQL> select role from dba_roles
  2  ;


ROLE
------------------------------
CONNECT
RESOURCE
DBA
SELECT_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
LOGSTDBY_ADMINISTRATOR
DBFS_ROLE
AQ_ADMINISTRATOR_ROLE


ROLE
------------------------------
AQ_USER_ROLE
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
ADM_PARALLEL_EXECUTE_TASK
GATHER_SYSTEM_STATISTICS
JAVA_DEPLOY
RECOVERY_CATALOG_OWNER
SCHEDULER_ADMIN
HS_ADMIN_SELECT_ROLE
HS_ADMIN_EXECUTE_ROLE
HS_ADMIN_ROLE


ROLE
------------------------------
GLOBAL_AQ_USER_ROLE
OEM_ADVISOR
OEM_MONITOR
WM_ADMIN_ROLE
JAVAUSERPRIV
JAVAIDPRIV
JAVASYSPRIV
JAVADEBUGPRIV
EJBCLIENT
JMXSERVER
JAVA_ADMIN


ROLE
------------------------------
CTXAPP
XDBADMIN
XDB_SET_INVOKER
AUTHENTICATEDUSER
XDB_WEBSERVICES
XDB_WEBSERVICES_WITH_PUBLIC
XDB_WEBSERVICES_OVER_HTTP
OLAP_DBA
ORDADMIN
OLAP_XS_ADMIN
CWM_USER


ROLE
------------------------------
OLAP_USER
SPATIAL_WFS_ADMIN
WFS_USR_ROLE
SPATIAL_CSW_ADMIN
CSW_USR_ROLE
APEX_ADMINISTRATOR_ROLE
OWB$CLIENT
OWB_DESIGNCENTER_VIEW
OWB_USER
MGMT_USER
SQL> select * from role_role_privs where role=‘CONNECT‘;


no rows selected


SQL> c/_role/_sys
  1* select * from role_sys_privs where role=‘CONNECT‘
SQL> /


ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE SESSION                           NO


SQL> c/_sys/_tab
  1* select * from role_tab_privs where role=‘CONNECT‘
SQL> /


no rows selected
SQL> create role r1;


Role created.


SQL> create role r2 identified by oracle;


Role created.


SQL> grant create vicw to r1;
grant create vicw to r1
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege




SQL> grant create view to r1;


Grant succeeded.


SQL> grant create synonym to r2;


Grant succeeded.


SQL> grant select on scott.emp to r1;


Grant succeeded.


SQL> grant select on scott.dept to r2;


Grant succeeded.


SQL> grant connect to r1,r2;


Grant succeeded.


SQL> grant r1,r2 to u1;


Grant succeeded.


SQL> conn u1/oracle
Connected.
SQL> select * from session_roles;


ROLE
------------------------------
R1
CONNECT


SQL> select * from user_role_privs;


USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
U1                             R1                             NO  YES NO
U1                             R2                             NO  NO  NO


SQL> set role r2 identified by oracle;


Role set.


SQL> select * from session_roles;


ROLE
------------------------------
R2
CONNECT


SQL> set role r1,r2 identified by oracle;


Role set.


SQL> select * from session_roles;


ROLE
------------------------------
R1
CONNECT
R2
简化用户管理,方便管理
drop role r2; 收回相应的权限
2.管理模式对象
alter table 语句增加修改删除列
alter table XXX add(s type);
alter table XXX drop column s;
alter table XXX set unused (s); //打标签
alter table XXX set unused column s; //打标签
alter table XXX drop unused cilumns;
列位置修改
12c更改列的位置是很方便的
隐藏列:首先将需要增加的列增加好后,
alter table scott.tt modify(deptno invisible);
alter table scott.tt modify(deptno visible);
10g
rename ee4 to e41
create table e4 as select empno,ename,job,hirdate
修改基表,不推荐使用
alter table emp disable constraint pk_name;
alter table emp enable constraint pk_name;
alter table emp disable constraint pk_name cascade; 禁用级联的,禁用后连个都会变为disable的,启用的话,首先启用主依赖,在启用次依赖
alter table dept drop (deptno); 删除出错,由于存在级联
alter table dept drop (deptno) cascade constraints; 这样删除就不会出错
SQL> alter table dept drop (deptno);
alter table dept drop (deptno)
                       *
ERROR at line 1:
ORA-12992: cannot drop parent key column
SQL> alter table dept drop (deptno) cascade constraints;
Table altered.
11g、12c只读
SQL> alter table emp read only;
Table altered.
SQL> update emp set sal=sal+1;
update emp set sal=sal+1
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."EMP"
SQL> alter table emp read write;
Table altered.
约束与索引的名字分开
SQL> create table a1(id number primary key using index(create unique index
  2  a1_id_i on a1(id)),name varchar2(2));


Table created.
SQL> insert into a1 values(1,‘A‘);


1 row created.


SQL> commit;


Commit complete.


SQL> alter table a1 move;


Table altered.
SQL>  select index_name,status from user_indexes;


INDEX_NAME                     STATUS
------------------------------ --------
A1_ID_I                        UNUSABLE
BIN$/o6ox8o9iMjgQ2YAqMDn3Q==$0 VALID
EMP_ENAME_I                    VALID
PK_EMP                         VALID
PK_DEPT                        VALID


SQL> insert into a1 values(2,‘a‘);
insert into a1 values(2,‘a‘)
*
ERROR at line 1:
ORA-01502: index ‘SCOTT.A1_ID_I‘ or partition of such index is in unusable
state
SQL>alter index a1_id_i rebuild


Index altered.
flashback闪回 不适合于sys用户的
drop table  a purge;
临时表
事务级别的 事务结束数据不在了
会话级别的 会话结束数据不再了
create global temporary table cart on commit delete rows;
外部表
文本文件存储行
二进制文件存储
不支持DML操作
datapump数据加载卸载
SQL> create directory ext as ‘/tmp‘;
Directory created.
SQL> grant read,write on directory ext to scott;
Grant succeeded.
建立脚本
create table fs(id number,name varchar2(10),loc varchar2(11))
organization external
(
type oracle_loader
default directory ext
access parameters(
records delimited by newline
fields terminated by ‘,‘ MISSING FIELD VALUES ARE NULL
(id,name,loc))
location(‘a.txt‘)
)
reject limit unlimited
/
SQL> select * from fs;


        ID NAME       LOC
---------- ---------- -----------
         1 a          f
         2 f          g
         3            f
         4 h
SQL> ho echo "6,u,i">>/tmp/a.txt


SQL> select * from fs;


        ID NAME       LOC
---------- ---------- -----------
         1 a          f
         2 f          g
         3            f
         4 h
         6 u          i


SQL> ho sed ‘2,4d‘ -i /tmp/a.txt


SQL> select * from fs;


        ID NAME       LOC
---------- ---------- -----------
         1 a          f
         6 u          i
**二进制文件平台之间迁移
***先导出文件,得到文本文件,然后在新的平台建立外部表执行导入操作。
数据字典
数据字典下面有基表,动态、静态
由基表和可以访问的视图构成
user/all/dba
v$
user_objects 自已拥有的所有对象
all_objects  有权限访问的所有对象
select * from dictionary
select * from dict
dba_ all_ user_  dba
all_ user_  普通用户


user_tables  all_tables
user_tab_columns  all_tab_columns
index
user_indexes  user_ind_columns
all_indexs   all_ind_columns
constraint
user_constraints user_cons_columns
all_        all_
view
user_views all_views
sequence
user_sequence all_sequences
synonym
user_synonyms all_synonyms
directory user_directories all_directories
注释
comments on table |column is ‘......‘;
user_tab_comments
user_col_comments
SQL>  comment on table dept is ‘deptment table‘;


Comment created.


SQL> select * from user_tab_comments;


TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
COMMENTS
--------------------------------------------------------------------------------
SALGRADE                       TABLE




FS                             TABLE




FAS                            TABLE






TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
COMMENTS
--------------------------------------------------------------------------------
FA                             TABLE




EMP                            TABLE




DEPT                           TABLE
deptment table




TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
COMMENTS
--------------------------------------------------------------------------------
BONUS                          TABLE
SQL> comment on column dept.dname is ‘aaaaaaa‘;


Comment created.


SQL> select * from user_col_comments where table_name=‘DEPT‘;


TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
COMMENTS
--------------------------------------------------------------------------------
DEPT                           DEPTNO




DEPT                           DNAME
aaaaaaa


DEPT                           LOC
SQL> comment on column dept.dname is ‘‘;


Comment created.