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 新列名