【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.

巴特西

【oracle ocp 知识点二】

最新文章

热门文章