sirwsl的博客

创建用户表空间

-- 移除表空间
DROP TABLESPACE HTHSETEMP INCLUDING CONTENTS AND DATAFILES

-- 移除用户
drop user HSE_TEMP CASCADE

-- 创建表空间
CREATE TABLESPACE NTHSETESTPSPACE
DATAFILE '/opt/oracle/oradata/ORCL/ORCLTEST/NTHSETESTPSPACE.dbf' 
SIZE 20G AUTOEXTEND ON next 4G

-- 创建用户
CREATE USER HSE_TEMP IDENTIFIED  BY 000000

--- 修改密码
alter user 用户名 identified by 新密码； 

-- 修改用户表空间
ALTER USER HSE_TEMP quota unlimited ON HTHSETEMP

-- 实际应用
create user <username> identified by <password> 
default tablespace <tablespace_name> -- 默认表空间
temporary tablespace temp -- 临时表空间
quota unlimited on <tablespace_name> -- 表空间额度

-- 权限设设置
grant create session to HSE_TEMP
grant create table to HSE_TEMP
grant create tablespace to HSE_TEMP;
grant create any view to HSE_TEMP;
grant connect,resource to HSE_TEMP;
grant create any sequence to HSE_TEMP;
grant create any table to HSE_TEMP;
grant delete any table to HSE_TEMP;
grant insert any table to HSE_TEMP;
grant select any table to HSE_TEMP;
grant unlimited tablespace to HSE_TEMP;
grant execute any procedure to HSE_TEMP;
grant update any table to HSE_TEMP;

-- 查看当前用户的权限
select * from role_sys_privs;

常用命令

startup mount  #启动
shutdown immediate; 关闭
sqlplus / as sysdba  进入
 lsnrctl status 查看监听状态
 lsnrctl start  开启监听
 lsnrctl reload  重启
 lsnrctl stop  停止

查看锁定次数上限

SELECT RESOURCE_NAME, LIMIT FROM DBA_PROFILES WHERE RESOURCE_NAME = 'FAILED_LOGIN_ATTEMPTS'

用户解锁

PS： 由于密码错误次数超过限制次数出现用户锁定情况

alter user user_name account unlock

用户授权

alter user HSE quota unlimited on users;

表授权

grant select,insert,update,delete,all on 表 to 用户

查询表空间大小

/**查询大小**/
SELECT A.TABLESPACE_NAME "DEMO",
       A.BYTES / 1024 / 1024 "总大小（MB）",
       round(B.BYTES / 1024 / 1024, 1) "已使用（MB）",
       round(C.BYTES / 1024 / 1024, 1) "未使用（MB）",
       round((B.BYTES * 100) / A.BYTES, 2) "使用率（%）",
       round((C.BYTES * 100) / A.BYTES, 2) "空闲率（%）"
  FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
   AND A.TABLESPACE_NAME = C.TABLESPACE_NAME

查询表空间文件位置

   /**查询表空间文件位置**/
select * from dba_data_files

查询用户对应表空间

select username,default_tablespace from dba_users;

表空间对应的数据文件，用户和表空间对应关系

select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;

满32G后自动扩展

alter tablespace DEMO add datafile 'D:SDE_03.DBF' size 1G autoextend on next 50M Maxsize UNLIMITED;