HOW TO LIST TRIGGERS AND PROCEDURES WRITTEN BY ME

[marcos.santos]

Good afternoon, I would like to know the syntax to list the triggers and written procedures for me.
I thanked the attention.



Environment information:
* version of Oracle: 10g
* Operating system: XP
:-O

[diegoleite]

Check the View User_Source, DBA_Source.

Select all

SELECT TEXT  
FROM USER_SOURCE   
WHERE TYPE IN('PROCEDURE','TRIGGER');

a help for you to search more ....

is to Futuc the Oracle Data Dictionary You will Learning a lot from this little query below. See what C has curiosity ...

Ex:

Select all

SELECT TABLE_NAME  
FROM DICTONARY 
WHERE TABLE_NAME LIKE '%TABLE%'; 
 
 
SELECT TABLE_NAME  
FROM DICTONARY 
WHERE TABLE_NAME LIKE '%INDEX%'; 
 
SELECT TABLE_NAME  
FROM DICTONARY 
WHERE TABLE_NAME LIKE '%CONST%'; 
 
SELECT TABLE_NAME  
FROM DICTONARY 
WHERE TABLE_NAME LIKE '%FILE%';

I hope I have helped!

[marcos.santos]

Sorry,
But I did not understand, I performed these scripts, but did it err to
what's every one of them?
I would appreciate that they could explain
thanks!

[dr_gori]

The Oracle data dictionary contains information about all objects that exist in the bank.

What is the Data Dictionary?
are tables, views where this information is saved.

For example:

Select all

SELECT * FROM USER_OBJECTS

- will show all objects created by the user.

Select all

SELECT * FROM ALL_OBJECTS

- All objects that the user has access.

So on.

Our colleague diegolite told you to learn about each of the data dictionary tables. It's all there!
Down Query that lists all data dictionary tables:

Select all

SELECT * from DICTIONARY

More examples:

Select all

select * from ALL_TAB_COLUMNS - mostra as tabelas e suas colunas 
select * from ALL_TABLES - todas tabelas 
select * from DBA_JOBS - todos jobs do banco

etc

[diegoleite]

Check the View User_Source, DBA_Source.

- Esssa Query aqui You will see your Owner's procedures and triggers.

Select all

SELECT TEXT  
FROM USER_SOURCE  
WHERE TYPE IN('PROCEDURE','TRIGGER');  

An help for you to search more ....

is to Futuc the Oracle Data Dictionary You will learn a lot from this queryzinha BELLOW. See what C has curiosity ...

Ex:

Select all

SELECT TABLE_NAME  
FROM DICTIONARY  
WHERE TABLE_NAME LIKE '%TABLE%';  
 
 
SELECT TABLE_NAME  
FROM DICTIONARY  
WHERE TABLE_NAME LIKE '%INDEX%';  
 
SELECT TABLE_NAME  
FROM DICTIONARY  
WHERE TABLE_NAME LIKE '%CONST%';  
 
SELECT TABLE_NAME  
FROM DICTIONARY  
WHERE TABLE_NAME LIKE '%FILE%';

Sorry I wrote wrong Try now ...

Inside the like you put a key word ... like I want to know .. what tablespac is stored to my table employees .. then you want to know what kind of object in the bank Dice? Table ne? !!

Select all

SELECT TABLE_NAME  
FROM DICTIONARY  
WHERE TABLE_NAME LIKE '%TABLE%'; 

then you will list some internal views of Oracle.

Select all

DBA_ADVISOR_SQLW_TABLES 
DBA_ALL_TABLES 
DBA_APPLY_TABLE_COLUMNS 
DBA_BASE_TABLE_MVIEWS 
DBA_CACHEABLE_NONTABLE_OBJECTS 
DBA_CACHEABLE_TABLES 
DBA_CACHEABLE_TABLES_BASE 
DBA_CAPTURE_PREPARED_TABLES 
DBA_EVALUATION_CONTEXT_TABLES 
DBA_EXTERNAL_TABLES 
DBA_FILE_GROUP_TABLES 
DBA_FILE_GROUP_TABLESPACES 
DBA_HIST_TABLESPACE_STAT 
DBA_NESTED_TABLES 
DBA_NESTED_TABLE_COLS 
DBA_OBJECT_TABLES 
DBA_PART_TABLES 
DBA_PENDING_CONV_TABLES 
DBA_QUEUE_TABLES 
DBA_SOURCE_TABLES 
DBA_STREAMS_RENAME_TABLE 
DBA_STREAMS_TABLE_RULES 
DBA_SUBSCRIBED_TABLES 
DBA_TABLES 
DBA_TABLESPACES 
DBA_TABLESPACE_GROUPS 
DBA_TABLESPACE_USAGE_METRICS 
DBA_UPDATABLE_COLUMNS 
DBA_XML_TABLES 
ALL_ALL_TABLES 
ALL_APPLY_TABLE_COLUMNS 
ALL_BASE_TABLE_MVIEWS 
ALL_CAPTURE_PREPARED_TABLES 
ALL_EVALUATION_CONTEXT_TABLES 
ALL_EXTERNAL_TABLES 
ALL_FILE_GROUP_TABLES 
ALL_FILE_GROUP_TABLESPACES 
ALL_NESTED_TABLES 
ALL_NESTED_TABLE_COLS 
ALL_OBJECT_TABLES 
ALL_PART_TABLES 
ALL_PENDING_CONV_TABLES 
ALL_QUEUE_TABLES 
ALL_SOURCE_TABLES 
ALL_STREAMS_TABLE_RULES 
ALL_SUBSCRIBED_TABLES 
ALL_TABLES 
ALL_UPDATABLE_COLUMNS 
ALL_XML_TABLES 
USER_ADVISOR_SQLW_TABLES 
USER_ALL_TABLES 
USER_BASE_TABLE_MVIEWS 
USER_EVALUATION_CONTEXT_TABLES 
USER_EXTERNAL_TABLES 
USER_FILE_GROUP_TABLES 
USER_FILE_GROUP_TABLESPACES 
USER_NESTED_TABLES 
USER_NESTED_TABLE_COLS 
USER_OBJECT_TABLES 
USER_PART_TABLES 
USER_PENDING_CONV_TABLES 
USER_QUEUE_TABLES 
USER_SOURCE_TABLES 
USER_SUBSCRIBED_TABLES 
USER_TABLES 
USER_TABLESPACES 
USER_UPDATABLE_COLUMNS 
USER_XML_TABLES 
TABLE_PRIVILEGES 
GV$FIXED_TABLE 
GV$TABLESPACE 
V$DB_TRANSPORTABLE_PLATFORM 
V$FIXED_TABLE 
V$TABLESPACE 
V$TRANSPORTABLE_PLATFORM

Usually those who are going to have the information that you need are those who eat with all_, dba_, user_, v $, gv $

Example:

Select all

SQL> desc all_tables 
 Nome                                      Nulo?    Tipo 
 ----------------------------------------- -------- ---------------------- 
 OWNER                                     NOT NULL VARCHAR2(30) 
 TABLE_NAME                                NOT NULL VARCHAR2(30) 
 TABLESPACE_NAME                                    VARCHAR2(30)

.
.
.


Poxa aqui has the information I want ...

Select TablesPace_Name from ALL_Tables WHERE TABLE_NAME = 'Employees';

tablespace_name
---------------------------
tablespace_teste

[diegoleite]

Vlw,

dr_gori ...


: D