Oracle用户创建及删除
偶尔会用到,记录、分享。
1. Oracle用户创建
#创建用户表空间
create tablespace $username datafile '/u01/app/oracle/oradata/ufgovdb1/${username}.dbf' size 5120m autoextend on next 1024m maxsize unlimited extent management local; #创建用户临时表空间
create temporary tablespace ${username}temp tempfile '/u01/app/oracle/oradata/ufgovdb1/${username}temp.dbf' size 1024m autoextend on next 1024m maxsize unlimited extent management local; #创建用户、密码
CREATE USER $username PROFILE DEFAULT IDENTIFIED BY crux DEFAULT TABLESPACE $username TEMPORARY TABLESPACE ${username}temp ACCOUNT UNLOCK; #用户表空间不限额
GRANT UNLIMITED TABLESPACE TO $username WITH ADMIN OPTION; #赋予用户分配权限
GRANT "CONNECT" TO $username WITH ADMIN OPTION;
#赋予用户connect、resource权限
grant connect,resource to $username;
#赋予用户session、dba权限
grant create session, dba to $username;
2.Oracle用户删除
#删除用户
drop user $username cascade;
#删除用户表空间
drop tablespace $username including contents and datafiles;
#删除用户临时表空间
drop tablespace ${username}temp including contents and datafiles;
示例:
1. 创建用户
create tablespace cwy_DE0624 datafile '/u01/app/oracle/oradata/orcl/cwy_DE0624.dbf' size 20480M autoextend on next 1024M maxsize unlimited extent management local; create temporary tablespace cwy_DE0624temp tempfile '/u01/app/oracle/oradata/orcl/cwy_DE0624temp.dbf' size 1024M autoextend on next 1024M maxsize unlimited extent management local; CREATE USER cwy_DE0624 PROFILE DEFAULT IDENTIFIED BY demo_0624 DEFAULT TABLESPACE cwy_DE0624 TEMPORARY TABLESPACE cwy_DE0624temp ACCOUNT UNLOCK; GRANT UNLIMITED TABLESPACE TO cwy_DE0624 WITH ADMIN OPTION; GRANT "CONNECT" TO cwy_DE0624 WITH ADMIN OPTION; grant connect,resource to cwy_DE0624; grant create session, dba to cwy_DE0624; 用户名:cwy_DE0624 密码:demo_0624
2. 新建用户登录
[oracle@ufdb125 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 31 15:56:27 2023 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> connect cwy_DE0624 Enter password: Connected. SQL>
表空间&临时表空间概念
1、表空间
用来进行数据存储的(表、function、存储过程等),所以是实际物理存储区域。
每个table都有相应的user,而每个user都有默认的tablespace,如果你创建user时没有指定默认的tablespace,默认tablespace会指定USERS这个tablespace。
表空间一般设置为自动扩展.
2、临时表空间
临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。
主要用途是在数据库进行排序运算[如创建索引、order by及group by、distinct、union/intersect/minus/、sort-merge及join、analyze命令]、管理索引[如创建索 引、IMP进行数据导入]、访问视图等操作时提供临时的运算空间,当运算完成之后系统会自动清理
重启数据库可以释放临时表空间,如果不能重启实例,而一直保持问题sql语句的执行,temp表空间会一直增长,直到耗尽硬盘空间。
本文来自博客园,作者:查拉图斯特拉面条,转载请注明原文链接:https://www.cnblogs.com/n00dle/p/16637059.html