Oracle11g 创建表空间、创建用户、授予权限、锁定、解锁以及删除用户等

 转载：http://blog.csdn.net/cai7095576/article/details/18898717

Oracle创建表空间、创建用户、授予权限、锁定、解锁以及删除用户等

--创建表空间

CREATE   TABLESPACE caiyl DATAFILE 'D:\Oracle\app\caiyl\oradata\orcl\caiyl_space.dbf' size 500m AUTOEXTEND ON  NEXT 200M MAXSIZE 20480M 

EXTENT MANAGEMENT LOCAL;

--创建临时表空间 CREATE TEMPORARY TABLESPACE caiyl_temp TEMPFILE 'D:\Oracle\app\caiyl\oradata\orcl\caiyl_temp.dbf' SIZE 200M AUTOEXTEND ON NEXT 50M MAXSIZE 20480M EXTENT MANAGEMENT LOCAL;

--创建用户并指定表空间 CREATE USER caiyl IDENTIFIED BY 123456 DEFAULT TABLESPACE caiyl_space;

--创建用户并指定表空间和临时表空间 CREATE USER caiyl IDENTIFIED BY 123456 DEFAULT TABLESPACE caiyl_space TEMPORARY TABLESPACE caiyl_temp;

--授权用户 GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,      DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,      DBA,CONNECT,RESOURCE,CREATE SESSION TO  caiyl;

--删除表空间 DROP TABLESPACE caiyl_space INCLUDING CONTENTS AND DATAFILES;

--查看表空间 SELECT tv.TABLESPACE_NAME "TABLESPACE_NAME",TOTALSPACE "TOTALSPACE/M",FREESPACE "FREESPACE/M",ROUND((1-FREESPACE/TOTALSPACE)*100,2) "USED%" FROM  (SELECT TABLESPACE_NAME,ROUND(SUM(bytes)/1024/1024) TOTALSPACE FROM    DBA_DATA_FILES GROUP BY TABLESPACE_NAME) tv,          (SELECT TABLESPACE_NAME,ROUND(SUM(bytes)/1024/1024) FREESPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) fs WHERE tv.TABLESPACE_NAME=fs.TABLESPACE_NAME;

--查看临时表空间 SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;

--增加表空间大小 ALTER TABLESPACE caiyl_space ADD DATAFILE 'D:\Oracle\app\caiyl\oradata\orcl\caiyl_space.dbf' size 4096M;

--增加临时表空间大小 ALTER DATABASE TEMPFILE 'D:\Oracle\app\caiyl\oradata\orcl\caiyl_temp.dbf' RESIZE 8192M;

--删除用户 DROP USER caiyl CASCADE

--把数据导入不同于原系统的表空间，在导入之后却往往发现，数据被导入了原表空间(下面解决此方法) grant connect, resource,dba to caiyl; --回收用户unlimited tablespace权限，这样就可以导入到用户缺省表空间： revoke unlimited tablespace from caiyl; alter user asd quota 0 on caiyl_space; alter user asd quota unlimited on caiyl_space;

--导入导出 EXP caiyl/caiyl@127.0.0.1:1521/orcl FILE=f:/caiyl.dmp FULL=y; EXP caiyl/caiyl@orcl FILE=f:/caiyl.dmp FULL=y; IMP caiyl/caiyl@orcl FILE=f:/caiyl.dmp FULL=y IGNORE=y;

--创建一个用户，指定在该表空间的配额为10mb,初始状态为锁定 create user caiyl identified by 123456 default tablespace caiyl_space 10M on caiyl_space account lock;

--创建用户，在表空间的配额为10mb,口令设为过期状态(即首次连接数据库时要修改口令) create user caiyl identified by 123456 default tablespace caiyl_space 10M on caiyl_space profile example_profile password expire;

--修改用户口令,同时将该用户解锁 alter user caiyl identified by newpassword account unlock;

--修改默认表空间,在该表空间的配额为20MB,在USER的表空间配额为10MB alter user caiyl default tablespace caiyl_space quota 20M ON caiyl_space quota 10M on users;