oracle学习笔记

June 6th, 2007

作者: Jeffrey
出处:http://blog.zhangjianfeng.com/?p=581

++调整显示格式
SQL> set lines 256
SQL> set pages 42

++查看当前用户
show user
conn sys as sysdba

++创建用户hillary,密码为window的用户,默认的表空间是users,临时表空间是temp
create user hillary identified by window default tablespace users temporary tablespace temp;

++删除用户
drop user user_name [cascade]; #如果被删除的用户拥有对象,则需要使用cascade关键字

++修改口令
sqlplus ‘/ as sysdba’
alter user user_name identified by new_password;
password; #仅能修改当前用户的口令

++锁定/解锁用户
alter user user_name account [lock|unlock];

++修改默认表空间
alter user user_name default tablespace new_def_tablespace [temporary tablespace new_temp_tablespace];

++给用户相应权限
grant system_prifvilege[,system_privilege] to user_name [with admin option]; #with admin option表示将要授予系统权限的用户
grant create session to hillary; #授予create session privilege
grant connect,resource to username; #不赋此权限,此用户无法登录

++查看当前系统用户信息
select username,password from dba_users;

++查看用户的系统权限
connect user/password;
select * from user_sys_privs;

++回收系统权限
revoke system_privilege [,system_privilege] from user_name

++为用户授予对象权限
grant object_privilege [(column_name)] on object_name to user_name [with grant option]
#object_privilege表示对象权限,column_name表示对象中的列名称,object_name表示指定的对象名称,with grant option 允许该用户将当前的对象权限转授予其他用户.
例:(1)为hillary用户授予对scott.authors表的select/insert/delete的对象权限.命令如下
connect scott/password;
grant select,insert,delete on scott.authors to hillary [with grant option];
(2)为hillary授予对scott.authors表中first_name和phone列的更新权限.
connect scott/password;
grant update (first_name,,phone) on scott.authors to hillary;
++回收授予的对象权限
revoke object_privilege on object_name from user_name
connect scott/password; revoke select,insert on scott.authors from hillary;

++角色创建 / 查看角色信息
create role role_name [identified by role_password];
select * from user_role_privs;

++清空table_name表
delete from table_name

++删除表空间及相关数据文件
DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

++ORA-27123: unable to attach to shared memory segment错误
echo 4294967295 >/proc/sys/kernel/shmmax

++查看HS_INFO表的索引情况
SQL> select index_name,index_type from user_indexes where table_name=’HS_INFO’;

++检查索引键值:

SQL> select index_name,column_name from user_ind_columns where table_name =’HS_INFO’;

++CPU占用100%分析
1、top
看到暂用cpu利用率较高的oracle或者相关进程。获取进程id
2、使用如下语句查询
select sql_text,spid,v$session.program,process from
v$sqlarea,v$session,v$process
where v$sqlarea.address=v$session.sql_address
and v$sqlarea.hash_value=v$session.sql_hash_value
and v$session.paddr=v$process.addr
and v$process.spid in (PID);
将第一步查询到的pid输入上面的括号里面，用逗号分开。也可以用下面的方法
SELECT   sql_text
    FROM v$sqltext a
   WHERE a.hash_value = (SELECT sql_hash_value
                           FROM v$session b
                          WHERE b.SID = ‘&sid’)
ORDER BY piece ASC
/
3、就可以得到占用cpu比较多的语句。

++启用sqlplus的AutoTrace功能
sqlplus ‘ / as sysdba ‘
SQL> @?/rdbms/admin/utlxplan
grant all on plan_table to public ;
@?/sqlplus/admin/plustrce
grant plustrace to public ;
SQL> set autotrace on
SQL> set timing on
SQL> set autotrace traceonly
SQL> select table_name from user_tables;
测试
SQL> set autotrace trace explain
SQL> select * from tab

++记录/关闭语句执行时间
set timing on/off;
select * from tab;

Last modified at: Tuesday, June 24th, 2008 04:25:37 pm
Trackback URL: http://blog.zhangjianfeng.com/article/581/trackback

给本文打分: 1 Star