大家好，又见面了，我是你们的朋友全栈君。
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;
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/160315.html原文链接：https://javaforall.net
