总目录: 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; |