1.数据库操纵语言

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

 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.