-----------------安全性
grant select on SYSTEM.PSY_U_DEPARTMENT to wenzhong;--授权
alter user wenzhong default tablespace system;---修改用户默认表空间
alter user wenzhong identified by admin123;----修改用户的密码
alter user wenzhong account lock; ---锁定用户账号
alter user wenzhong account unlock; ---解锁用户账号
create user wenzhong identified by admin123 ---创建用户账号
default tablespace system;
grant connect to wenzhong;---给用户账号授权(连接权限)
revoke create user from wenzhong;---解除授权的权限
revoke select on syste.PSY_U_DEPARTMENT from wenzhong;---解除授权的权限
grant select on system.PSY_U_DEPARTMENT to wenzhong with grant option;---对象授权(级联授权)即sys授权给wenzhong同时也给wenzhon给其他用户授权查询的权限
---系统授权
grant create user to wenzhong;
grant drop user to wenzhong;
grant create user to wenzhong with grant[admin] option;
-----------角色授权
create role myrole;---创建角色
grant select on SYSTEM.PSY_U_DEPARTMENT to myrole;--给角色授权(查询权限)
grant myrole to wenzhong;--将用户账号指定到所属角色。
create table tablespace tabs
datafile 'E:/OracleDataBase/app/Administrator/oradata/wenzhong/tbs.dbf' size 10M;
alter user wenzhong default tablespace tabs;
grant unlimited tablespace .DBA to wenzhong;
create table tt(id int,name char(6)) tablespace tabs;--创建表时指定表所属表空间(如果不指定所属表空间,则表所属表空间就是当前用户所属表空间)
alter table tt add constraint conf foreign key(refid) references tab (id);---参照完整性(外键约束)
alter table tt add constraint cksex check(sex in ('男,女'));-----------check约束
alter table tt add constraint cksex check(sex ='男' or sex='女');-----------check约束
desc dba_constraints;
select constraint_name,constraint_type from user_constraints where table_name='tt';
---创建位图索引(当表中某一列数据量很大,但是唯一值很少(如只有男、女)这时候为了查询快可以创建位图索引)
create bitmap index bitindex on tt(sex);
create unique index uniqueindex on tt(code);---创建唯一索引