oracle创建用户、表空间、授权、建表

苍雪殷

　　发现一牛叉网站：http://www.eygle.com/archives/2009/07/profile_failed_login_attempts.html
　　一：登入
　　调整plsql的显示

SQL> set linesize 1000
SQL> col status format a10
　　 设置时间显示格式

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
　　 
　　切换到oracle用户

su - oracle
　　 启动pl/sql

sqlplus /nolog
　　 使用dba登入

conn /as sysdba
　　 也可以用scott用户以sysdba的身份登录oracle. 

• conn scott/tiger as sysdba  
  

　　二：用户操作
　　创建用户 

• create user zzg identified by zzg123;  
  

　　创建用户时分配表空间-----如果你有表空间的话

create user zzg identified by zzg123  default tablespace users;
　　强制用户修改密码

Alter user zzg  password expire;
　　修改用户的密码. 

• alter user zzg identified by unis;  
  

　　查看系统中的用户

select * from dba_users;   #显示信息详细
select * from all_users;     
select * from user_users;   #查看当前用户的详细信息
　　修改用户密码错误锁定次数及密码过期时间（这里不永不过期）

alter profile DEFAULT limit failed_login_attempts unlimited;
Alter PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
　　删除用户及其相关对象 

• drop user zzg cascade;  #去掉cascade只删除用户
  

　　用户锁定

select username,lock_date from dba_users where username='gbz'; #查看用户锁定时间
alter user OUTLN account unlock;     #解锁
Alter user OUTLN account lock;        #加锁
select * from dba_profiles where RESOURCE_NAME = 'FAILED_LOGIN_ATTEMPTS';    #查看锁定规则
alter profile default limit FAILED_LOGIN_ATTEMPTS 30;     #设置30次锁定
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;   #不锁定
　　查看用户密码过期时间（默认180天）

SELECT username,PROFILE FROM dba_users;
　　
　　三：表空间操作
默认情况下用户创建好后系统会默认给该用户分配一个表空间（users）; 
查看一下所有用户所在的表空间. 

• select username,default_tablespace from dba_users;  
  

　　创建一个新的表空间. 

• create tablespace ts_zzg datafile 'f:\ts_zzg\zzg_data.dbf' size 200M;  
  

　　注:datafile后面是表空间的物理存储路径，文件名的后缀可以随便. 

给表空间增加文件，可实现多磁盘

create tablespace billing_yz_14 datafile '/data/oratablespace/data1.dbf' size 20M;    创建
alter tablespace billing_yz_14 add datafile '/data/oratablespace/dat2.db' size 200M;    增加一个文件
alter database datafile '/data/oratablespace/dat2.db' resize 20M;    修改表空间文件大小

ORA-01144: File size (201326592 blocks) exceeds maximum of 4194303 blocks

原因：表空间最大32G，10G之后可以使用bigfile创建大空间
create bigfile tablespace billing_yz_15 datafile '/data/oratablespace/data.dbf' size 2048M;

ORA-32771: cannot add file to bigfile tablespace

问题：bigfile的表空间不能增加大小或文件
解决：创建时让它自增 autoextend on
　　删除表空间

DROP TABLESPACE billing_yz_14 INCLUDING CONTENTS AND DATAFILES;
　　将表空间分配给用户. 

• alter user zzg default tablespace ts_zzg;  
  

　　设置用户表空间的配额

alter user zzg  quota unlimited on ts_zzg;    #这里是不限制
　　也可以给用户加表空间配额(表空间不够会报ORA-01536)

alter user zzg quota 50m on ts_zzg;
　　
　　四：角色操作

创建并给权限

create role normal_role;
-- Grant/Revoke role privileges
grant connect to normal_role;
grant resource to normal_role;
grant exp_full_database to normal_role;
grant imp_full_database to normal_role;
-- Grant/Revoke system privileges
grant select any dictionary to normal_role;
grant alter session to normal_role;
grant alter tablespace to normal_role;
grant create database link to normal_role;
grant debug any procedure to normal_role;
grant debug connect session to normal_role;
　　把角色给用户

grant normal_role to zzg
　　查看角色的权限

select * from dba_sys_privs where grantee='NORMAL_ROLE';
select * from role_sys_privs where role='NORMAL_ROLE';
　　五：权限操作
给用户分配了表空间,用户还不能登陆（没有登录权限）,因此还需要为用户分配权限 

• grant create session,create table,create view,create sequence,unlimited tablespace to zzg;  
  

　　查看用户的权限

select * from dba_sys_privs ;   #显示所有权限
select * from user_sys_privs;   #当前用户的权限
　　删除用户的权限

Revoke AUDIT SYSTEM from zzg;
　　给用户分配了权限之后我们就可以用zzg用户来登录了. 

• conn zzg/unis;  
  

　　登录之后我们也可以来查询用户所具有的权限 

• select *from session_privs;  
  

　　六：表操作

创建表结构

CREATE TABLE alarm_customerbandwidth (
id number(11) NOT NULL PRIMARY KEY,  --主键   
customername varchar2(100) NOT NULL,
customercode varchar2(100) NOT NULL,
productname varchar2(100) NOT NULL,
productcode varchar2(20) default NULL,   --默认值
uplimit varchar2(10) NOT NULL,
downlimit varchar2(10) NOT NULL,
delay_time varchar2(10) NOT NULL,
status char(1) NOT NULL, -- 1 告警中  0 未告警
alarm_count number(11) NOT NULL,
send_num number(11) NOT NULL,
email varchar2(4000),
sms varchar2(4000),
enable char(1) DEFAULT '1',
constraint customername unique(productcode)   --联合唯一
)
 修改表

alter table 表  modify|add   (字段 number(11) default 0)   --修改增加  
ALTER TABLE 表名 RENAME COLUMN 当前列名 TO 新列名;           --改字段名
ALTER TABLE 表名 DROP COLUMN 列名;                         --删除字段  
ALTER TABLE 当前表名 RENAME TO 新表名;                      --改表名   
comment on column  表名.列名 is '注释内容';   //修改表的列的注释
COMMENT ON TABLE MOVO_NEW.TEST_SAKTE  IS '注释内容';  //修改表的注释
 修改字段约束

alter table alarm_customerbandwidth add constraint 约束名 unique|PRIMARY key(customername ，productcode);

ORA-02299: cannot validate (SYS.UC_PHONE) -duplicate keys found
--这里报错，因为我们在插入数据的时候，有重复值，先删除掉重复值



修改字段可以为空

alter table alarm_customerbandwidth modify (uplimit varchar2(11) null,downlimit varchar2(11) null);

 创建索引（picture 表，aid字段）

create index picture_album_idx on picture (aid);

 查看表已经有的索引

select index_name from all_indexes where table_name = 'PICTURE';

 修改表名

ALTER TABLE 表名 rename to 新表名

 修改字段名

ALTER TABLE 表名 rename column 列名 to 新列名