Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Information on System Tables

Posted on 2003-03-05
5
Medium Priority
?
715 Views
Last Modified: 2012-06-21
Hello Friends,
 I want the information on system tables of oracle and other information on oracle tables.
  Actually I want to know where the oracle procedures and other PL/SQL blocks, triggers are stored. I had created a trigger in oracle which is written in a file with new.sql. The trigger was created succesfully. After that I deleted the file new.sql. now I want the code that i had wirtten in that trigger.how I can get that information. i.e., In which user/system table the trigger and procedures information will be stored.

I waiting for your reply.

bye.
0
Comment
Question by:ydramu
5 Comments
 
LVL 3

Expert Comment

by:allahabad
ID: 8077261
For triggers:
user_triggers,all_triggers,dba_triggers.

For procedures:
user_procedures,all_procedures,dba_procedures.
0
 
LVL 4

Expert Comment

by:iozturk
ID: 8078227
triggersand procedures are stored in database you may see its definitons, status etc from user_triggers, all_triggers, dba_triggers, user_procedures...

Deleting your sql file doesnot have any affect after creation. if you want to delete tem issue the command:

alter table <tablename> drop trigger <triggername>;
drop procedure <procedurename>;
0
 
LVL 7

Author Comment

by:ydramu
ID: 8078344
I donot want to delete the trigger. After deletion of my sql file. The code I written for that trigger will be no more. after I want to get the code from the trigger created in oracle. Where the code will be stored. Is the code of any procedure will store only in files or in any other format in oracle.
0
 
LVL 2

Accepted Solution

by:
Datamonkey earned 200 total points
ID: 8078384
Procedure code is stored in user/all/dba_source, not user_procedures.

To view the code,
select text from user_source where name='YOUR_PROCEDURE' order by line ;

For a trigger,
select trigger_body from user_triggers where trigger_name='YOUR_TRIGGER' ;

Note that trigger body is a LONG so will be trucated to 80 chars by default in SQLPlus. Do,
set long 5000
to override this.

0
 
LVL 7

Author Comment

by:ydramu
ID: 8093476
Please reply soon a small request how to see the users table space ie, where his tables are storing.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question