Oracle常用小知识(一)

1、用户解锁及修改密码

--以解锁scott用户为例
alter user scott account unlock;
--修改scott用户密码为tiger
alter user scott identified by tiger;

2、创建表空间

--创建数据表空间
create tablespace oags
logging
datafile 'E:\oracle\product\10.2.0\oradata\orcl\oags.dbf'
size 32m
autoextend on
next 32m maxsize 20480m
extent management local; 

3、给用户添加权限

GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,
   DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,
      DBA,CONNECT,RESOURCE,CREATE SESSION  TO oa

4、创建用户

create user oa identified by oa
default tablespace oa
temporary tablespace oa_temp;

5、查询用户连接

select p.spid,
       a.serial#,
       c.object_name,
       b.session_id,
       b.oracle_username,
       b.os_user_name
  from v$process p, v$session a, v$locked_object b, all_objects c
 where p.addr = a.paddr
   and a.process = b.process
   and c.object_id = b.object_id

6、查询表空间使用量(好大一堆，谁能记得住啊)

select a.tablespace_name,
       a.bytes / 1024 / 1024 "Sum MB",
       (a.bytes - b.bytes) / 1024 / 1024 "used MB",
       b.bytes / 1024 / 1024 "free MB",
       round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used(%)"
  from (select tablespace_name, sum(bytes) bytes
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes, max(bytes) largest
          from dba_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 order by ((a.bytes - b.bytes) / a.bytes) desc;