oracle 建库 表空间 用户 查看表空间使用率 设置自动扩展

创建删除表空间

create tablespace TEST
datafile 'E:\DB\Oracle\oradata\TEST.dbf'
size 50M
AUTOEXTEND ON
NEXT 50M;

drop TABLESPACE TEST including contents and datafiles;

 

创建用户

--创建用户
create user TEST
identified by "Password"
default tablespace TEST;

 

删除用户

drop user TEST cascade;

 

用户授权

--连接访问权限
grant connect,resource to TEST;
--所有权限
GRANT ALL PRIVILEGES TO TEST;
--dba权限
grant dba to TEST;

废除权限

--权限分为system privileges 和 object privileges
revoke sysdba from TEST;

revoke all privileges from TEST;

revoke connect,resource from TEST;

对象权限
revoke all on table from public

查询用户

select * from all_users;

 

查看表空间使用率

select total.tablespace_name TBSN,
        round(total.MB,3) as total_space,
        case when free.MB is null then 0
                               else round(free.MB,3)
         end as free_space,
       case when free.MB is null then 0
         else round(free.MB,3)/round(total.MB,3)*100  end as free_rate
from ( select tablespace_name, sum(bytes/1024/1024) MB from dba_data_files group by tablespace_name )  total,
        ( select tablespace_name, sum(bytes/1024/1024) MB from dba_free_space group by tablespace_name )  free
where total.tablespace_name=free.tablespace_name(+)
order by  free_rate desc;

 

查看表空间是否开启自动扩展及文件名

select file_name ,autoextensible,increment_by from dba_data_files; 

 

设置表空间属性

ALTER DATABASE DATAFILE 'E:\DB\ORACLE\ORADATA\AGVSYSDB.DBF' AUTOEXTEND ON NEXT 50M [MAXSIZE <size/UNLIMITED>]；