SQL & PL/SQL :: List All Procedures Running On DB?

May 4, 2010

we are doing a performance tuning on a large database. Our main aim is to capture all the packages/procedures/view that are running on database. Below given is the format we have prepared

Eg:
SPIdSP_Name CPU TimeI/OLine Executed
1Emp_AllDetails221
1Emp_AllDetails582
1Emp_AllDetails25103

[code]...

Requirement:Write an Procedure that captures the above given information in the background of database and stores the information in a table.write the procedure to capture the above given information.

View 17 Replies


ADVERTISEMENT

Procedures Are Not Running Paralleled?

Mar 17, 2011

I have a job that runs two procedure parallely . The code is as follows:

DECLARE
jobno1 NUMBER;
jobno2 NUMBER;
flag NUMBER;
BEGIN
dbms_job.submit(jobno1,'pc;', next_date=>SYSDATE);
dbms_job.submit(jobno2, 'pc2;', next_date=>SYSDATE);

[code]....

And the stored procedure (in SQL File) is as follows:

procedure pc:

create or replace procedure pc
as
begin
for idx in 1..100 loop
dbms_output.put_line(idx);
end loop;
end;
/

procedure pc2:

create or replace procedure pc2
as
begin
for idx in 1..1000 loop
dbms_output.put_line(idx);
end loop;
end;
/

However, when I execute the job , I get the following output:

1
2
3
.
.
.
.
100

[code]....

Which indicates the job 1 got executed 1st and then job2. But this is not what I want.

View 4 Replies View Related

PL/SQL :: List Of Unused Tables And Procedures

Aug 28, 2012

I am in the task of clean up of tables. I need to find the list unused tables and procedures. Is there any way where i can find when was the last time the table queried?

Give sql query to find the list of unused tables and procedures.

View 6 Replies View Related

Forms :: How To Avoid (no List Elements Defined For List Item) Error

Sep 27, 2011

I am creating the Dynamic list but when i am compiling the form it gives the compilation error "No list elements defined for the list item".

I can eliminate it by entering the dummy list element but this dummy value will be displayed at form run time.

View 1 Replies View Related

List Products List Of Client Grouped By Type Of Product?

Dec 14, 2011

Im trying to list the products list of a client grouped by type of the product. Ex:

product type

prod.A acid
prod.B flavour
prod.C acid
prod.D cleaner
prod.E flavour

I want to list something as:

Acid

Prod.A
Prod.C

Cleaner

prod.D

Flavour

prod.B
prod.E

View 1 Replies View Related

Forms :: FRM-30351 / No List Elements Defined For List Item

Oct 30, 2011

DECLARE
CURSOR GRP IS
SELECT RowNum rn, Letter_Group_ID||'-'||A_Desc AName,Letter_Group_ID
FROM Hrs_Group;
BEGIN
Clear_list('Letter_Group_ID');
FOR I IN GRP LOOP
Add_List_Element('Letter_Group_ID',I.rn,I.AName,I.Letter_Group_ID);
end loop;
END;

FRM-30351: No list elements defined for list item.

List LETTER_GROUP_ID

View 4 Replies View Related

SQL & PL/SQL :: List All Tables Connected To Each Other With Constraints And List All Together

Apr 22, 2013

I just want to list and group all my tables that are linked together by constraints. I just want my tables to be able to be listed together as one particular database. my tables are , CUSTOMER, ORDER_INFO, ORDER_LINE, PRODUCT. They're all linked together by way of constraint and I want to list and print them all together as one DB. HOW DO I put them all in one schema and then also list them all together and print/illustrate them as one. also, I tried to import them into their own scheme but i ran into a series of probs regaurding the .dmp file being read.

View 2 Replies View Related

How To Use Member Procedures

Dec 13, 2009

create type employee is object(
name varchar2(30),
member procedure change_name(new_name varchar2)
)

[Code]....

Now how to use the change_name procedure to change the name of the employees in the table?

View 1 Replies View Related

PL/SQL :: Drop All Procedures At One Go?

Aug 29, 2012

I have a schema sys_eg .I dont have any DBA privileges .How can i delete all the procedure with one sql or pl/sql.

ORACLE 11G
I am using sql developer 3.2 version tool

View 6 Replies View Related

Monitoring Runtime Of Procedures

May 23, 2013

runtime monitoring procedures....need a sample script

View 1 Replies View Related

Executing WHOLE Package (of Procedures) At Once?

Aug 30, 2007

I could execute a package for eg if i had a package with procedures related to statistics and i run them each night, could i just do an exec on the package and it would run all those procedures??

Its not possible but i could call each procedure from ONE procedure

View 2 Replies View Related

Modifying Table Through A Procedures?

Aug 28, 2007

whats the best way to modify a table via a procedure for example if i have a table with records: id, fn, ln, address, tel, dob

how would i go about creating a procedure that modifies a record. The problem is what param needs to go in? i could certainly have different procedures that modify different things like one for modifying tel, one for modifying address, but is there a way to combine it all into ONE procedure so that if user wanted he could put in exec modifyTable(2, 099898787) to modify tel and then modifyTable(2, 'a new address') to modify address... ?

View 6 Replies View Related

SQL & PL/SQL :: IF ELSE ENDIF On Stored Procedures?

Apr 8, 2011

I come from a Microsoft SQL background and am having trouble adjusting to the PL/SQL syntax. I'd like to build this up to pass back a certain value back into a web form.

IF (SELECT COUNT(*) FROM EXCURSION WHERE EXCURSION_ID = 75)= 1 THEN
DBMS_OUTPUT.PUT_LINE 'True';
ELSE
DBMS_OUTPUT.PUT_LINE 'False';
END IF;

I'm having trouble writing values out at the moment

View 7 Replies View Related

SQL & PL/SQL :: Call Procedures In Cycle?

Jul 21, 2011

I've got some quantity of procedures in package

procedure1
procedure2
...
procedureN

I should call them in cycle instead of typing each of them manually. I know it's a bad idea to do something like this "dynamically".

View 13 Replies View Related

PL/SQL :: Exception Handling In Procedures

Oct 8, 2012

I am using for writing text files data to database. The problem here is let us assume there are 6 records in text file and if there is a problem at 2nd record, the later records are not getting inserted.

CREATE OR REPLACE PROCEDURE PROC1
IS
temp varchar2(500);
tmp_name varchar2(5);
tmp_no varchar2(4);
.
BEGIN
WHILE NOT end_of_file
LOOP

IF i = 18 THEN
tmp_no := temp;
END IF;

IF i = 21 THEN
tmp_name := temp;
END IF;

END LOOP;
END;
/If i=18 and temp = '12345' here, then tmp_no := temp; won't work (tmp_no varchar2(4);)
Similarly, If i=21 and temp = 'ABCDEFG' here, then tmp_name := temp; won't work (tmp_name varchar2(5));

how to handle this through EXCEPTIONS so that even if there is a problem with 1 record, while loop remain working for further records..

View 4 Replies View Related

PL/SQL :: How To Create A Package With Procedures

Aug 15, 2013

By referring through net I have written the following PCK file. I have installed my Oracle 11g database in VirtualBox(Win XP). I can able to select the tables using query from the host(Windows 7) 

CREATE OR REPLACE PACKAGE APEX_SYS_PCK  AS    TYPE refcursortype IS REF cursor  PROCEDURE GET_USER_DETAILS(  D1 OUT refcursortype,  P_USER IN VARCHAR2,  P_PASS IN VARCHAR2);END APEX_SYS_PCK; / CREATE OR REPLACE PACKAGE BODY APEX_SYS_PCKAS PROCEDURE GET_USER_DETAILS(  D1 OUT refcursortype,  P_USER IN VARCHAR2,  P_PASS IN VARCHAR2) IS BEGIN   OPEN D1 FOR SELECT * FROM APEX.PERSONS; END GET_USER_DETAILS;  END APEX_SYS_PCK;/

 When I try to run the script I am getting the following error. How to solve this problem?

Project: sqldev.temp:/IdeConnections%23vAPEX.jprF:PACKAGESAPEX_SYS_PCK.sqlError: PL/SQL: Compilation unit analysis terminatedError(1,14): PLS-00905: object APEX.APEX_SYS_PCK is invalidError(1,14): PLS-00304: cannot compile body of 'APEX_SYS_PCK' without its specification

View 9 Replies View Related

Where Server Side Procedures Are Stored

Jun 2, 2009

Where are the server side procedures are stored? Where are the server side procedures are visible??

View 5 Replies View Related

Stored Procedures Using Oracle Tables

Jul 22, 2010

Let us say there is an oracle table A. How do i find out what stored procedures/packages/views are using this table A.

Reason to know: When some DDL changes happen on table A, how do i know what are the impacted sp/pkg/views which should also be modified. Is there any query with which i can find this?

View 5 Replies View Related

Privileges Of Create Trigger - Procedures?

Aug 16, 2013

i want to give privilege of create trigger,procedures and functions privileges to a user "A"on the schema "B". how can i do it. i've already given select,insert,update,delete privilege to user "A"

how can user "A" create trigger(etc.) on tables of user "B".

View 1 Replies View Related

SQL & PL/SQL :: Saving Procedures With Compilation Errors

Feb 21, 2010

When creating objects, is there a way telling Oracle not to store(overwrite) procedures or other DDL in the database until the statements have no compilation errors?

View 4 Replies View Related

SQL & PL/SQL :: How Many Procedures And Functions Can Be Written In Package

May 4, 2010

I think there is no limit for number of procedures and functions can be written in a package, but little much confusion.

What is the maximum number of Procedures and Functions can be written in a package? is such type of limit is there or not ?

View 4 Replies View Related

SQL & PL/SQL :: Renaming Column In Multiple Procedures?

Jun 2, 2010

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

Is there any way to rename a column in a table A, so that it is changed wherever it is used, like all the procedures that uses this column, or other tables that has reference to this table A?

View 5 Replies View Related

SQL & PL/SQL :: Calling Other Procedures From Autonomous Procedure?

Feb 26, 2013

It's somewhat related to my previous post DBMS_SQL Usage But here I tried to capture whole scenario.

Scenario is like below:

create user a identified by a;
grant connect,resource to a;
create user b identified by b;
grant connect,resource to b;
create user c identified by c;
grant connect,resource to c;

[code]....

Not getting why the schema is not changing... and how to resolve the error.

View 8 Replies View Related

PL/SQL :: Trapping Errors In Stored Procedures?

Feb 27, 2013

I have to run queries on a gov't computer. There are errors in some of the stored procedures that will NEVER go away. Fact of life, and I have to live with it (I'm just a data analyst--not the developer of the procedures)So I do this, in the hope of trapping the error

BEGIN
run_stored_proc (my_cursor, my_id, param3);
EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('ERROR IN ID (' || my_id || '). The error was ' || SQLCODE || ' -ERROR- ' ||SQLERRM);
END;Well, it can still crash.
SQL> @c:mysql
un_test
ERROR IN ID (2692). The error was -20100 -ERROR- ORA-20100: Exception occurred in some_other_stored_procedure

ORA-01422: exact fetch returns more than requested number of rowsSo it looks like run_stored_proc calls some_other_stored_procedure which is having issues. I was hoping that by trying to capture the error in the top level procedure, it would also capture the error in a lower-level "sub-procedure". This is the way a TRY-CATCH in java works. If an error happens anywhere along the way inside the TRY-CATCH, it is caught. Sure, PL/SQL ins't java, but is there a way for me to catch a lower level error and not have this code crash?

Fortunately for me, it actually doesn't crash that often. But I have to put this code in a loop, and run it for many different my_id's. And when it does crash, it isn't significant from a data analysis standpoint. I can't do without a small percentage of the data. But I'd love to be able to just run my script and not have to watch it for unexpected crashes.

View 11 Replies View Related

PL/SQL :: How To Know A Trigger Gets Called By Which All Packages / Procedures

Sep 11, 2012

Wanted to know whether it is possible to know a trigger is getting fired due to which all packages/procedures. If it has got fired for a particular transaction say multiple times, then wanted to get details of the packages/procedures which caused an event so that trigger got fired?

View 2 Replies View Related

SQL & PL/SQL :: How Many Functions And Procedures Exist In Package

Apr 6, 2011

how many functions and Procedures are exist in Package. Is it possible to identify through query.

Eg: I have one Package:- ABC. This ABC package having 2 functions and 3 procedures. I want, total no.of functions and total no.of procedures through query.

View 1 Replies View Related

Listener Cannot Execute External Procedures?

Dec 7, 2011

I HAVE A PROBLEM WITH MY LISTENER, IT CANNOT EXECUTE EXTERNAL PROCEDURESWHEN I EXECUTE THE FOLLOWING PROCEDURE , I GET THIS ERRORS MSG.

[oracle2@dsedb encrypt]$ sqlplus cdsc/cdsc@db2
SQL*Plus: Release 10.2.0.2.0 - Production on Wed Dec 7 05:48:39 2011
Copyright © 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production

[code]...

No errors.
BEGIN
*
ERROR at line 1:
ORA-28546: connection initialization failed, probable Net8 admin error
ORA-06512: at "CDSC.ENCRYPTPW", line 1
ORA-06512: at line 2

No errors.
SQL>

I TRIES TO TEST SERVICE NAMES CONNECTION FROM NETMANAGER, I CAN CONNECT TO DATABASE BUT I WHEN I TRY I CONNECT TO EXTERNAL PROCEDURE IT FAILS

MY LISTENER.ORA AND TNSNAME.ORA ARE CONFIGURED AS FOLLOWS
LISTENER\:
SID_LIST_LISTENER =
(SID_LIST =

[code]...

View 1 Replies View Related

PL/SQL :: Java Stored Procedures And Triggers

Oct 31, 2012

I want to to use Java stored procedures and triggers within the Oracle 11g XE ?

View 3 Replies View Related

Print Out All Triggers / Procedures From Oracle 9iForms

Dec 15, 2011

I need to print out all the triggers/procedures from an Oracle 9iForms. When I open an Oracle 9iform in the Oracle form builder, I click on FILE/CONVERT but cannot see the triggers/procedures.

View 2 Replies View Related

Client Tools :: Cannot View 2 Procedures Simultaneously

May 6, 2010

I have problem viewing 2 procedures at the same time... e.g. i open procedure A, edit it, not close it. then i open procedure B from the left pane, and then i cannot see the procedure A... If i look at the File Menu on top left, it has the option of SAVE ALL, and not just SAVE.. that means both the procedures are open. but then why am i not able to see them?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved