oracle表空间和用户的创建、修改、授权、查看等执行SQL

--创建表空间
CREATE TABLESPACE hy_framework 
DATAFILE '/home/oracle/app/oradata/orcl/HY.dbf' --windows下的路径 H:\app\TigerLin\oradata\orcl\HY.dbf
size 500m 
AUTOEXTEND ON NEXT 200M 
MAXSIZE 20480M 
EXTENT MANAGEMENT LOCAL;

--创建临时表空间  
CREATE TEMPORARY TABLESPACE hy_framework_temp  
TEMPFILE '/home/oracle/app/oradata/orcl/hy_framework_temp.dbf'  
SIZE 200M  
AUTOEXTEND ON NEXT 50M 
MAXSIZE 20480M 
EXTENT MANAGEMENT LOCAL;

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

--也可同时指定临时表空间
CREATE USER hy_fw IDENTIFIED BY 123456 DEFAULT TABLESPACE hy_framework 
TEMPORARY TABLESPACE hy_framework_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 hy_fw;

--删除表空间  
DROP TABLESPACE hy_fw 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 * FROM DBA_DATA_FILES;

--删除用户  
DROP USER hy_fw CASCADE；

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

 

转载于:https://www.cnblogs.com/limho/p/4632106.html