NO.012 知识库-数据库篇-Oracle-dba常用功能

总目录： No.0 Web开发知识库

 

一、用户相关 创建用户、改用户名、改密码

二、表空间相关 创建表空间、表空间查询

三、重建失效索引

四、锁表查询

 

用户相关	--先创建一个tmpdb,再用下面的改名，省的每次创建用户都得批量替换以下语句中的用户名   --用户操作 -- Create the user create user tmpdb   identified by tmpdbpw   default tablespace yourtablespace   temporary tablespace TEMP   profile DEFAULT;   grant read, write on directory yourdirctory to tmpdb;   grant connect to tmpdb; grant exp_full_database to tmpdb; grant imp_full_database to tmpdb; grant resource to tmpdb;   grant create any sequence to tmpdb; grant debug connect session to tmpdb; grant unlimited tablespace to tmpdb;   --修改用户名 update  user$ set name='NEWNAME' where name='TMPDB';--注意要大写 commit; alter system checkpoint; alter system flush shared_pool;   -- 改密码 alter user sys   identified by change_on_install;
表空间相关	--创建表空间 create tablespace INSDATATBS logging datafile '/oradata/mydb/MYDBTBS.DBF' size 4096m autoextend on next 50m maxsize 10240m extent management local;   --查看用户下所有表占用的空间 Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name;      --查询所有表空间大小    select tablespace_name,sum(bytes)/1024/1024 from dba_data_files    group by tablespace_name;      --表空间未使用空间大小    select tablespace_name,sum(bytes)/1024/1024 from dba_free_space    group by tablespace_name;      --所有使用空间这样计算    select a.tablespace_name,total,free,total-free used from    ( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files    group by tablespace_name) a,    ( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space    group by tablespace_name) b    where a.tablespace_name=b.tablespace_name;
重建失效索引	select 'alter index '||index_name||' rebuild;' from user_indexes where status = 'UNUSABLE';
锁表查询	SELECT /*+ rule */        s.username,        decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,        o.owner,        o.object_name,        o.object_type,        s.sid,        s.serial#,        s.terminal,        s.machine,        s.program,        s.osuser   FROM gv$session s, gv$lock l, dba_objects o  WHERE l.sid = s.sid    AND l.id1 = o.object_id(+)    AND s.username is NOT NULL ;
锁表查询2	select t2.username,t2.sid,t2.serial#,t2.logon_time,   t2.OSUSER,t2.PROGRAM,t3.OBJECT,t3.TYPE from v$session t2,v$locked_object t1,v$access t3 where t3.OWNER=t2.USERNAME  and t2.SID=t1.SESSION_ID  and t2.SID=t3.SID;