【oracle ocp 知识点二】

浏览数：41 / 时间：2015年06月12日

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.