ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [转]DBA管理-008

[转]DBA管理-008

原创 Linux操作系统 作者:31597359 时间:2018-12-14 09:27:03 0 删除 编辑
DBA管理-008

79 managing password security and resources

<1>.controlling account lock and password
sql> alter user juncky identified by oracle account unlock;

<2>.user_provided password function
sql> function_name(userid in varchar2(30),password in varchar2(30),
old_password in varchar2(30)) return boolean

<3>.create a profile : password setting
sql> create profile grace_5 limit failed_login_attempts 3
sql> password_lock_time unlimited password_life_time 30
sql>password_reuse_time 30 password_verify_function verify_function
sql> password_grace_time 5;

<4>.altering a profile
sql> alter profile default failed_login_attempts 3
sql> password_life_time 60 password_grace_time 10;

<5>.drop a profile
sql> drop profile grace_5 [cascade];

<6>.create a profile : resource limit
sql> create profile developer_prof limit sessions_per_user 2
sql> cpu_per_session 10000 idle_time 60 connect_time 480;

<7>. view => resource_cost : alter resource cost
dba_Users,dba_profiles

<8>. enable resource limits
sql> alter system set resource_limit=true;


80.managing privileges

<1>.system privileges: view => system_privilege_map ,dba_sys_privs,session_privs

<2>.grant system privilege
sql> grant create session,create table to managers;
sql> grant create session to scott with admin option;
with admin option can grant or revoke privilege from any user or role;

<3>.sysdba and sysoper privileges:
sysoper: startup,shutdown,alter database open|mount,alter database backup controlfile,
alter tablespace begin/end backup,recover database
alter database archivelog,restricted session
sysdba: sysoper privileges with admin option,create database,recover database until

<4>.password file members: view:=> v$pwfile_users

<5>.O7_dictionary_accessibility =true restriction access to view or tables in other schema

<6>.revoke system privilege
sql> revoke create table from karen;
sql> revoke create session from scott;

<7>.grant object privilege
sql> grant execute on dbms_pipe to public;
sql> grant update(first_name,salary) on employee to karen with grant option;

<8>.display object privilege : view => dba_tab_privs, dba_col_privs

<9>.revoke object privilege
sql> revoke execute on dbms_pipe from scott [cascade constraints];

<10>.audit record view :=> sys.aud$

<11>. protecting the audit trail
sql> audit delete on sys.aud$ by access;

<12>.statement auditing
sql> audit user;

<13>.privilege auditing
sql> audit select any table by summit by access;

<14>.schema object auditing
sql> audit lock on summit.employee by access whenever successful;

<15>.view audit option : view=> all_def_audit_opts,dba_stmt_audit_opts,dba_priv_audit_opts,dba_obj_audit_opts

<16>.view audit result: view=> dba_audit_trail,dba_audit_exists,dba_audit_object,dba_audit_session,dba_audit_statement

81 manager role

<1>.create roles
sql> create role sales_clerk;
sql> create role hr_clerk identified by bonus;
sql> create role hr_manager identified externally;

<2>.modify role
sql> alter role sales_clerk identified by commission;
sql> alter role hr_clerk identified externally;
sql> alter role hr_manager not identified;

<3>.assigning roles
sql> grant sales_clerk to scott;
sql> grant hr_clerk to hr_manager;
sql> grant hr_manager to scott with admin option;

<4>.establish default role
sql> alter user scott default role hr_clerk,sales_clerk;
sql> alter user scott default role all;
sql> alter user scott default role all except hr_clerk;
sql> alter user scott default role none;

<5>.enable and disable roles
sql> set role hr_clerk;
sql> set role sales_clerk identified by commission;
sql> set role all except sales_clerk;
sql> set role none;

<6>.remove role from user
sql> revoke sales_clerk from scott;
sql> revoke hr_manager from public;

<7>.remove role
sql> drop role hr_manager;

<8>.display role information
view: =>dba_roles,dba_role_privs,role_role_privs,dba_sys_privs,role_sys_privs,role_tab_privs,session_roles


81.查询当前正在执行的job的情况
有时候对于需要执行的job查询执行情况,比如正在执行那条语句,或者想把job停下来等。一般不知道怎么查询到
job执行的session的sid.
方法一:
select * from dba_jobs_running
如果运行比较慢,加
select /*+ rule */* from dba_jobs_running
方法二:
<1>首先得到job号,从user_jobs或者dba_jobs
select * from user_jobs where upper(what) like '%MYPROGRAM%'
<2> 根据job号查询sid号
select * from v$lock where id2 = 3361910 and type ='JQ'
就可以查询到sid了
比如查询当前的执行什么语句
select sql_text from v$sqlarea a,v$lock b,v$session c,user_jobs d
where d.upper(what) like '%2004PRESENT%'
and d.job = b.id2
and b.type='JQ'
and b.sid = c.sid
and a.hash_value = c.sql_hash_value
and a.address = c.sql_address

82.怎么样给sqlplus安装帮助
[A]SQLPLUS的帮助必须手工安装,shell脚本为$ORACLE_HOME/bin/helpins
在安装之前,必须先设置SYSTEM_PASS环境变量,如:
$ setenv SYSTEM_PASS SYSTEM/MANAGER
$ helpins
如果不设置该环境变量,将在运行脚本的时候提示输入环境变量
当然,除了shell脚本,还可以利用sql脚本安装,那就不用设置环境变量了,但是,我们必须以system登录。
$ sqlplus system/manager
SQL> @?/sqlplus/admin/help/helpbld.sql helpus.sql
安装之后,你就可以象如下的方法使用帮助了
SQL> help index

83.如何移动数据文件
<1>、关闭数据库,利用os拷贝
a.shutdown immediate关闭数据库
b.在os下拷贝数据文件到新的地点
c.Startup mount 启动到mount下
d.Alter database rename datafile '老文件' to '新文件';
e.Alter database open; 打开数据库
<2>、利用Rman联机操作
RMAN> sql "alter database datafile ''file name'' offline";
RMAN> run {
2> copy datafile 'old file location'
3> to 'new file location';
4> switch datafile ' old file location'
5> to datafilecopy ' new file location';
6> }
RMAN> sql "alter database datafile ''file name'' online";
说明:利用OS拷贝也可以联机操作,不关闭数据库,与rman的步骤一样,利用rman与利用os拷贝的原理一样,在rman中copy是拷贝数据文件,相当于OS的cp,而switch则相当于alter database rename,用来更新控制文件。

84.如何管理联机日志组与成员
以下是常见操作,如果在OPA/RAC下注意线程号
增加一个日志文件组
Alter database add logfile [group n] '文件全名' size 10M;
在这个组上增加一个成员
Alter database add logfile member '文件全名' to group n;
在这个组上删除一个日志成员
Alter database drop logfile member '文件全名';
删除整个日志组
Alter database drop logfile group n;

85.怎么样计算REDO BLOCK的大小
[A]计算方法为(redo size + redo wastage) / redo blocks written + 16
具体见如下例子
SQL> select name ,value from v$sysstat where name like '%redo%';
NAME VALUE
---------------------------------------------------------------- ----------
redo synch writes 2
redo synch time 0
redo entries 76
redo size 19412
redo buffer allocation retries 0
redo wastage 5884
redo writer latching time 0
redo writes 22
redo blocks written 51
redo write time 0
redo log space requests 0
redo log space wait time 0
redo log switch interrupts 0
redo ordering marks 0
SQL> select (19412+5884)/51 + 16 '"Redo black(byte)" from dual;
Redo black(byte)
------------------
512

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8000572/viewspace-62292/,如需转载,请注明出处,否则将追究法律责任。

上一篇: oracle查看权限
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2006-10-07

  • 博文量
    4
  • 访问量
    2328