第一章用户访问 创建用户 create user user identified by passwd; 授权 grant privilege to user; grant create session,create table,create sequence,create view to scott; 创建角色 create role manager; grant create table,create view to manager; grant manager to user1,user2; alter user hr identified by hr; alter user test account lock/unlock; 授查询权限 grant select on employee to hr,scott; 授修改部分列的权限 grant update(department_name,location_id) on departments to scott,manager; 授管理权限 grant select,insert on departments to scott with grant option; 授所有用户的权限 grant select on scott.emp to public; 权限相关的视图 role_sys_privs: role_tab_privs: user_role_privs: user_tab_privs_made: user_tab_privs_recd: user_col_privs_made: user_col_privs_recd: user_sys_privs: 回收权限 revoke privilege on object from user ; 例如： revoke select,insert on departments from scott; 第二章管理Schema对象 添加列： alter table table_name add column datatype ...; 修改列： alter table table_name modify column datatype ...; 删除列： alter table table_name drop column_name; 设置列不可用 alter table table_name set unused column_name; 或者 alter table table_name set unused column column_name; 删除不可用列 alter table table_name drop unused columns; 添加约束语法 alter table table_name add constraint constraint_name ...; alter table emp modify employee_id primary key ; alter table emp2 add constraint emp_mgr_fk foreign key(manager_id) references emp2(employee_id); alter table emp2 add constraint emp_dt_fk foreign key(department_id) refences departments on delete cascade; 添加主键 alter table dept2 add constraint dept_id_pk primary key(department_id) deferrable initially deferred; 删除约束 alter table emp2 drop constraint emp_mgr_fk; 删除主键 alter table dept2 drop primary key cascade; 禁止用约束 alter table emp2 disable constraint emp_dt_fk; 启用用约束 alter table emp2 enable constraint emp_dt_fk; 级联约束 alter table emp2 drop column employee_id cascade constraints; alter table test1 drop (pk,fk,col1) cascade constraints; 索引创建方式： 1自动创建： 主键创建 唯一主键创建 2手动创建 create index create table 为主键指定特定索引 create table new_emp (employee_id number primary key using index (create index emp_id_idx on new_emp (employee_id)), first_name varchar(20), last_name varchar(25)); select index_name,table_name from user_indexes where table_name='new_emp' 基于特定函数的索引 create index upper_dept_name_idx on dept2(upper(department_name)); select * from from dept2 where upper(department_name)='SALES'; 删除索引 drop index index_name; 删除表 drop table emp3 purge; flashback table 语句 FLASHBACK TABLE[schema.]table[,[ schema.]table ]... TO { TIMESTAMP | SCN } expr [ { ENABLE | DISABLE } TRIGGERS ]; select original_name,operation,droptime from recyclebin; flashback table emp2 to before drop; 创建外部表 1.创建目录 create or replace directory emp_dir as '/.../emp_dir'; 2.授权 grant read on directory emp_dir to hr; 3.创建外部表的语法： CREATE TABLE <table_name> ( <col_name> <datatype>, … ) ORGANIZATION EXTERNAL (TYPE <access_driver_type> DEFAULT DIRECTORY <directory_name> ACCESS PARAMETERS (… ) ) LOCATION ('<location_specifier>') ) REJECT LIMIT [0 | <number> | UNLIMITED]; 4.外部表的创建 CREATE TABLE oldemp ( fname char(25), lname CHAR(25) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY emp_dir ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE NOBADFILE NOLOGFILE FIELDS TERMINATED BY ',' (fname POSITION ( 1:20) CHAR, lname POSITION (22:41) CHAR)) LOCATION ('emp.dat') ) PARALLEL 5 REJECT LIMIT 200;?