/*第1步:创建数据表空间 */dropuser ×× 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 CONSTRAINTSdrop tablespace tablespace_name including contents and datafiles CASCADECONSTRAINTS;
selectuser#,name,password fromuser$ where name ='userid';updateuser$ set name='newUserId'whereuser#=92;alteruser 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.
createtable MTUSEFULSPACE
(
ID Number(4) NOTNULLPRIMARYKEY,
USERNAME varchar2(30),
TABLESPACENAME varchar2(60),
OWNERNAME varchar2(30)
);
3.
declare icount number(2);
tempspace varchar2(60);
beginfor curTable in (select username as allusr,default_tablespace as alltblspace from dba_users)
loop
tempspace :=curTable.alltblspace;
dbms_output.put_line(tempspace);
selectcount(TABLESPACENAME) into icount from MTUSEFULSPACE where TABLESPACENAME = tempspace;
if icount=0 then
DROP TABLESPACE tempspace INCLUDING CONTENTS AND DATAFILES;endif;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
GROUPBY t.tablespace_name;--2、查看表空间物理文件的名称及大小 SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDERBY 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(+)
ORDERBY segment_name;--4、查看控制文件 SELECT NAME FROM v$controlfile;--5、查看日志文件 SELECT MEMBER FROM v$logfile;--6、查看表空间的使用情况 SELECTSUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUPBY 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
GROUPBY 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;