Oracle常用小知识(一)

1、用户解锁及修改密码

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

2、创建表空间

--创建数据表空间create tablespace oagsloggingdatafile 'E:\oracle\product\10.2.0\oradata\orcl\oags.dbf'size 32mautoextend onnext 32m maxsize 20480mextent 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 oadefault tablespace oatemporary 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;