oracle没有提供直接修改用户名的sql,需要修改用户名的话可以通过直接修改字典表,但可能会出现什么问题不太能确定;我遇到的问题是修改用户名后job状态会变成broken,但又不是每次都这样。以下操作尽供参考。
1.创建用户(sys)
2.用户下创建表、过程、作业(olduser)
3.修改用户名(sys)
4.新用户下job的状态等信息
1.创建用户(sys)
- create user olduser identified by olduser;
grant resource to olduser;
grant create session to olduser;
grant create job to olduser;
2.用户下创建表、过程、作业(olduser)
- create table t_test(c1 date);
create or replace procedure proc_test
as
begin
insert into t_test values(sysdate);
commit;
end proc_test;
/
begin
dbms_scheduler.create_job
(
job_name => 'job_test',
job_type => 'plsql_block',
job_action => 'begin proc_test; end;',
start_date => sysdate,
repeat_interval => 'freq=minutely; interval=1',
enabled => true
);
end;
/
3.修改用户名(sys)
- 修改用户名后需要修改一下新用户的密码,这一步是必须要做的,哪怕新密码与旧密码一样也得修改一下,否则无法登录;
- 修改密码时可能会报以下错:
SQL Error: ORA-01918: user 'NEWUSER' does not exist
01918. 00000 - "user '%s' does not exist"
*Cause: User does not exist in the system.
*Action: Verify the user name is correct.
将shared_pool、buffer_cache清空一下就可以了
01918. 00000 - "user '%s' does not exist"
*Cause: User does not exist in the system.
*Action: Verify the user name is correct.
将shared_pool、buffer_cache清空一下就可以了
- update user$ set name='NEWUSER' where name='OLDUSER';
commit;
alter system flush shared_pool;
alter system flush buffer_cache;
alter user newuser identified by newuser;
- 修改用户名后,scheduler$_job中job的创建者还是显示原来的用户名,需要手动修改一下
- select * from scheduler$_job where creator='OLDUSER';
update scheduler$_job set creator='NEWUSER' where creator='OLDUSER';
commit;
原来用户下的job可能(有的时候又不会)会在下一次执行时state变成broken,因为job状态不可用,user_scheduler_job_run_details中也没有job的日志
select job_name, state, job_creator, job_action,
last_start_date, last_run_duration, next_run_date
from user_scheduler_jobs;
重新enable可能也没用,job下次执行时state还是会变成broken; - 此时应该只能删除job重建,暂时不知道还有没有其它方法
begin
dbms_scheduler.enable (name => 'JOB_TEST');
end;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7417681/viewspace-2049192/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7417681/viewspace-2049192/