?
Solved

Information on System Tables

Posted on 2003-03-05
5
Medium Priority
?
709 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

777 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