-
import
Hi DBAs,
I want to import a particular procedure from an export dump file.
Is there any way to import the procedure,, bcoz my developer
wrongly droped an important procedure.
Ple give some input if anything possible.
Thanx in advance.
-
You should be able to see the code of the procedure. Just copy the code and create the procedure.
or If you have the procedure in any other database or schema just query the code from DBA_SOURCE and create the procedure.
HTH
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
Dear Sanjay,
What do u mean? ,, please give some detail explination on this.
import show=y
what does mean.
-
-
First there is no way to import just the procedure you want. If you do not know what show=y is, you can try this:
1.create a user called dummy.
2.import the dump file into user dummy using FROMUSER=EXPORTUSER, TOUSER=DUMMY params.
3.Using TOAD, sql*navigator, OEM or ALL_SOURCE data dictionary view get the procedure code. Example:
SQL> select text from all_source where name='PSTUB';
procedure pstub(pname varchar2, uname varchar2,
stubSpec in out varchar2, stubText in out varchar2,
flags varchar2 := '6') is
rc varchar2(40);
ty varchar2(5);
cursor tub (una varchar2, dbna varchar2, luna varchar2, luty varchar2) is
select line from sys.pstubtbl
where (una is null or username = una) and
(dbna is null or dbname = dbna) and
lun = luna and lutype = luty
order by lineno;
begin -- main
sys.pstubt(pname, uname, '', flags, rc);
if rc like '$$$%' then stubText := rc; return; end if;
if not (rc = 'PKG' or rc = 'SUB')
then stubText := '$$$ other'; return;
end if;
stubSpec := '';
stubText := '';
if rc = 'PKG' then
for s in tub(uname, '', pname, 'PKS') loop
stubSpec := stubSpec || s.line;
end loop;
end if;
if rc = 'PKG' then ty := 'PKB'; else ty := 'SUB'; end if;
for s in tub(uname, '', pname, ty) loop
stubText := stubText || s.line;
end loop;
end;
29 rows selected.
4. Spool the code to a script and execute the script to create the procedure the original schema.
Remember the Golden Rule - He who has the gold makes the rules!
===================
Kris109
Ph.D., OCP 8i, 9i, 10g, 11g DBA
-
Originally posted by kris109
3.Using TOAD, sql*navigator, OEM or ALL_SOURCE data dictionary view get the procedure code. Example:
SQL> select text from all_source where name='PSTUB';
Er, I would at least add ORDER BY clause to the above query, ie
Code:
select text from all_source where name='PSTUB'
order by line;
Also, an owner specification should be added to the WHERE clause, ie
Code:
select text from all_source
where name='PSTUB' and owner = 'DUMMY'
order by line;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Originally posted by jmodic
Er, I would at least add ORDER BY clause to the above query, ie
Code:
select text from all_source where name='PSTUB'
order by line;
Well, is that needed?
PS text code when its coming in ordered way ( by line ), then why use?
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Originally posted by abhaysk
Well, is that needed?
PS text code when its coming in ordered way ( by line ), then why use?
It is very much needed. Oracle doesn't gurantee that rows will be returend in sorted manner unless you specify 'order by'.
Imagine you are querying a 5000 lines of code and the lines aren't sorted.
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
Hi everybody,,,
Thanx for ur reply..
My problem is I don't have more space in my DB to import the entire dump file and the size of dump is 1.2 GB.
Is there anyother way to do this.
Thanx in advance.
-
import only the objects of the user who dropped that
important procedure.
-Raja
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|