Oracle User

• 01.创建用户
• 02.删除用户
• 03.修改用户

---

01.创建用户

/*第1步：创建临时表空间 */
create temporary tablespace WORK_TEMP
tempfile 'D:\App\oracle\oradata\orcl\WORK_TEMP.dbf'
size 50m 
autoextend on
next 50m maxsize 20480m 
extent management local; 

/*第2步：创建数据表空间 */
create tablespace WORK_DATA 
logging 
datafile 'D:\App\oracle\oradata\orcl\WORK_DATA.dbf'
size 50m 
autoextend on
next 50m maxsize 20480m 
extent management local; 

/*第3步：创建用户并指定表空间 */
create user rwddb identified by password
default tablespace WORK_DATA 
temporary tablespace WORK_TEMP; 

/*第4步：给用户授予权限 */
grant connect,resource,dba to rwddb;

删除用户

/*第1步：创建数据表空间 */
drop user ×× cascade
/*说明： 删除了user，只是删除了该user下的schema objects，是不会删除相应的tablespace的。*/
/*第2部：删除tablespace */
DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

select t1.name,t2.name from v$tablespace t1, v$datafile t2 where t1.ts# = t2.ts#;
--删除空的表空间，但是不包含物理文件
drop tablespace tablespace_name;
--删除非空表空间，但是不包含物理文件
drop tablespace tablespace_name including contents;
--删除空表空间，包含物理文件
drop tablespace tablespace_name including datafiles;
--删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
--如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

03.修改用户

001.表空间

--修改默认表空间
alter user xwiki default tablespace DATA;

002.赋权

grant create session to USERID（select * from user_sys_privs 字段ADM为No，如果要是YES的话需要 在最后加上WITH ADMIN OPTION;）
---赋权
grant CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE,
ALTER ANY TABLE, ALTER ANY PROCEDURE,
DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE,
SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE
TO USERID;

003.修改用户名

select user#,name,password from user$ where name ='userid';
update user$ set name='newUserId' where user#=92;
alter user newUserId identified by password;
--强制写入数据文件
alter system checkpoint; 
--清楚缓存数据字典信息，强制oracle读实际数据（即更改后的数据）
alter system flush shared_pool;

04.root权限登录

1.运行->sqlplus /nolog
2.输入conn / as sysdba 管理员账户登录
3.如果用户被锁定先解锁：
alter user system account unlock;
4.修改密码：
alter user system identified by wanglj;

05.批量删除用户

思路：
Export出DB中所有的user和tablespace, 筛选出系统的和有用的tablespace,把有用的信息load到一张表中去。
然后写例程循环，把不在有用表的tablespace删掉
1. select username,default_tablespace from dba_users;
2. 
create table MTUSEFULSPACE
(
ID Number(4) NOT NULL PRIMARY KEY,
USERNAME varchar2(30),
TABLESPACENAME varchar2(60),
OWNERNAME varchar2(30)
);
3.
declare icount number(2);
tempspace varchar2(60);
begin
for curTable in (select username as allusr,default_tablespace as alltblspace from dba_users)
loop
tempspace :=curTable.alltblspace;
dbms_output.put_line(tempspace);
select count(TABLESPACENAME) into icount from MTUSEFULSPACE where TABLESPACENAME = tempspace;
if icount=0 then
DROP TABLESPACE tempspace INCLUDING CONTENTS AND DATAFILES;
end if;
commit;
end loop;
end;
执行后会报如下错误
ORA-06550: 第 10 行, 第 5 列: 
PLS-00103: 出现符号 "DROP"在需要下列之一时：
begin case declare exit
for goto if loop mod null pragma raise return select update
while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
好像是被锁了。。

没办法，例程不能写，就只能组出语句执行了。
把需要删除的user, tablespace 导出到Excel. 使用CONCATENATE 组出SQL.
贴到SQLdevelop 批量执行。
整个删除会比较耗时间， 100多个user. 用了12个小时左右。

06.查询用户相关数据

--1、查看表空间的名称及大小 
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size 
FROM dba_tablespaces t, dba_data_files d 
WHERE t.tablespace_name = d.tablespace_name 
GROUP BY t.tablespace_name; 
--2、查看表空间物理文件的名称及大小 
SELECT tablespace_name, 
file_id, 
file_name, 
round(bytes / (1024 * 1024), 0) total_space 
FROM dba_data_files 
ORDER BY tablespace_name; 
--3、查看回滚段名称及大小 
SELECT segment_name, 
tablespace_name, 
r.status, 
(initial_extent / 1024) initialextent, 
(next_extent / 1024) nextextent, 
max_extents, 
v.curext curextent 
FROM dba_rollback_segs r, v$rollstat v 
WHERE r.segment_id = v.usn(+) 
ORDER BY segment_name; 
--4、查看控制文件 
SELECT NAME FROM v$controlfile; 
--5、查看日志文件 
SELECT MEMBER FROM v$logfile; 
--6、查看表空间的使用情况 
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name 
FROM dba_free_space 
GROUP BY tablespace_name; 
SELECT a.tablespace_name, 
a.bytes total, 
b.bytes used, 
c.bytes free, 
(b.bytes * 100) / a.bytes "% USED ", 
(c.bytes * 100) / a.bytes "% FREE " 
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; 
--7、查看数据库库对象 
SELECT owner, object_type, status, COUNT(*) count# 
FROM all_objects 
GROUP BY owner, object_type, status; 
--8、查看数据库的版本 
SELECT version 
FROM product_component_version 
WHERE substr(product, 1, 6) = 'Oracle'; 
--9、查看数据库的创建日期和归档方式 
SELECT created, log_mode, log_mode FROM v$database;