Recovering a dropped User.

Jonathan Lewis provided a link on how to recover a dropped user and I went to that link to find out how that was done. This is the link to that site.

I tried it out on the database on my laptop and it works like jiffy. Below is what I did.



create user flashtest identified by flashtest ;

grant connect,resource,dba to flashtest;

conn flashtest/flashtest;

create table emp(ename varchar2(20),city varchar2(20));

insert into emp values('azar','riyadh');

insert into emp values('kareem','dubai');

insert into emp values('azmi','chennai');

insert into emp values('idress','riyadh');

insert into emp values('ajmal','chennai');

commit;

select current_timestamp from dual;

conn / as sysdba

drop user flashtest cascade;

shutdown immediate

startup mount

flashback database to timestamp to_date('22-DEC-10 09.25.30','DD-MM-YY HH24:MI:SS');

alter database open read only;

conn flashtest/flashtest;

select * from emp;

host exp flashtest/flashtest file=emp.dmp log=emp.log direct=y consistent=y statistics='none';

conn / as sysdba

shutdown immediate

startup mount

recover database;

alter database open;

conn sys as sysdba

create user flashtest identified by flashtest;

grant connect,resource,dba to flashtest;

host imp flashtest/flashtest file=emp.dmp log=emp.log full=y



This seem to be perfect if we can shutdown and startup the data base so easily. How do we recover if the database can not be shutdown and still the user need to be recovered. I know if it is asking for too much. But since I am a developer and not a DBA down time is still a taboo for me. I am eager to know how this is done traditionally.

Alert Log

We can obtain the location of the alert log using the below query.

select VALUE from v$parameter where upper(name) ='BACKGROUND_DUMP_DEST';

Nocopy

In today's plsqlchallenge I learned another new thing. It is the NOCOPY option for the parameters in a procedure or a function. I was under the impression that when we pass a variable with the nocopy option the variables changes in the procedures will not be passed over. But that doesn't seem to be correct. When we pass a parameter with the NOCOPY option, no seperate copy of the variable is made in the memory and the passed variable is used to modify. This makes more sense while using collection variables. If for example we pass a VARRAY as in out parameter without the NOCOPY option then the variable is copied to a temporary variable and then the changes are made. In the case of NOCOPY the changes are made to the original variable itself. In case an exception occurs then in the case of NOCOPY the original variable would have changed but in the case where NOCOPY is not specified the orginal value remains intact. Below is the sample code from todays sample quiz.



create or replace package plch_pkg
is
type number_nt is table of number;

procedure pass_nums1 (nums in out number_nt);

procedure pass_nums2 (nums in out nocopy number_nt);

procedure show_nums (nums in number_nt);

end;
/

create or replace package body plch_pkg
is
procedure pass_nums1(nums in out number_nt) is
begin
for indx in nums.first..nums.last
loop
nums(indx):=nums(indx)*2;
raise value_error;
end loop;
end pass_nums1;

procedure pass_nums2(nums in out nocopy number_nt) is
begin
for indx in nums.first..nums.last
loop
nums(indx):=nums(indx)*2;
raise value_error;
end loop;
end pass_nums2;

procedure show_nums(nums in number_nt) is
l_return pls_integer :=0;
begin
for indx in nums.first..nums.last
loop
l_return := l_return+nums(indx);
end loop;
dbms_output.put_line(l_return);
end show_nums;
end plch_pkg;
/

show err


declare
nums1 plch_pkg.number_nt :=plch_pkg.number_nt(1,2,3);
nums2 plch_pkg.number_nt :=plch_pkg.number_nt(1,2,3);
begin
begin
plch_pkg.pass_nums1(nums1);
exception when others then
plch_pkg.show_nums(nums1);
end;
begin
plch_pkg.pass_nums2(nums2);
exception when others then
plch_pkg.show_nums(nums2);
end;
end;
/