SQL & PL/SQL :: Dropping A Procedure During Execution
Apr 26, 2012When the procedure is executing can we drop a procedure . Is there any way to drop the procedure with force .
View 5 Replies
ADVERTISEMENT
Creating And Dropping Global Temp Tables Inside A Single Procedure
Aug 20, 2010I am writing a procedure that will be called from a java wrapper.
The procedure do a lot of data manipulations and in between i am creating global temp table and saving the data into it for each request thats given as a parameter to the procedure. After all the processing i have to write the data from this global temp table into a physical table and atlast drop the temp table.
Create or replace proc_name ()
update table........
delete from ..........
CREATE GLOBAL TEMPORARY TABLE TSAAG
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50)
)
insert into............
drop table TSAAG;
End;
creating a global temp table inside a procedure is expensive...
Do we have anything like creating table before and calling the instanse of it in procedure.
Do we have any alternatives to this..
Function And Procedure Execution
Jul 7, 2010I have question in procedure execution and function execution oracle database. I want know that which is faster in execution procedure or function.
how can i prove it through examples. can i see the explain plan for a procedure and a function or is there any way to prove which one is faster in execution.
SQL & PL/SQL :: To Generate Procedure Execution LOG
Sep 3, 2013I have a very big oracle procedure. Since it's too big and calling many other procedures, I am not able to debug the exceptions thrown. Any oracle utility which logs all the procedures called by the master procedure step by step and maintains a detailed record.
View 21 Replies View RelatedSQL & PL/SQL :: Parallel Execution Of A Procedure?
Jul 22, 2010Is it possible to run a single procedure in parallel. (Not looking for multifple, using DBMS_JOB)
I am using Oracle 9i.
SQL & PL/SQL :: Procedure And Function Execution
Jul 8, 2010I have question in procedure execution and function execution oracle database. I want know that which is faster in execution procedure or function. Can i see the time taken by procedure and select query only time.
View 2 Replies View RelatedSQL & PL/SQL :: Write Log When Procedure Execution Is Successful
Aug 10, 2011My need is to check whether procedure execution is successful and write sysdate and procedure name into log table.
Sure I can check target table which procedure uses to write result and count lines after execution. But I've got different procedures, some of them can write 0 lines and it's not error. I think Oracle should have something like return codes and some number means "ok".
SQL & PL/SQL :: Oracle 11g - Parallel Execution Of Procedure
Jul 28, 2010Oracle Version is 11g.
I need to call the same procedure with different parameter multiple time in parallel.
I have job_control Table
CREATE TABLE JOB_CONTROL
(
JOB_CONTROL_ID NUMBER NOT NULL,
JOB_SEQ_NO NUMBER NOT NULL,
MODULE_NAME VARCHAR2(32 BYTE) NOT NULL,
JOB_STATUS VARCHAR2(15 BYTE),
NO_OF_RECORDS NUMBER,
PROCESSED_RECORDS NUMBER
);
Insert into JOB_CONTROL
(JOB_CONTROL_ID, JOB_SEQ_NO, MODULE_NAME, JOB_STATUS, NO_OF_RECORDS)
Values
(20, 1, 'SALES', NULL, 5);
Insert into JOB_CONTROL
[code]........
Now the table look like this.
JOB_CONTROL_IDJOB_SEQ_NOMODULE_NAMEJOB_STATUSNO_OF_RECORDSPROCESSED_RECORDS
201SALES5
212SALES5
223SALES5
234SALES5
245SALES5
256SALES3
based on the data available in table i wanted to execute a procedure 6 times
create table job_table
(now date, seq_no number)
the procedure will have one parameter as job_seq_no
create of replace procedure job_call (i_job_seq_no number)
is
begin
for i in 1.. i_job_seq_no
loop
insert into job_table
values
[code]......
the problem is i want to do this activity in parallel , i.e. all six calls to the procedure would be parallel. and this will be one time activity.
SQL & PL/SQL :: Stored Procedure Execution Jump To End
Mar 13, 2013I created the following stored procedure which I am calling from a script. I compiled my Stored Procedure with Debug Info. For some reason Execution jumps from the second BEGIN to the END statement.
Since the SP compiles w/o any errors, I suspect I have a logic error.
Stored Procedure:
CREATE OR REPLACE PROCEDURE VALIDATE_PATIENT_NEW
(
VALIDATED OUT int,
LAST_NAME IN VARCHAR2 DEFAULT NULL,
FIRST_NAME IN VARCHAR2 DEFAULT NULL,
DOB date DEFAULT NULL,
PAT_NUMBER OUT int,
FACILITY_KEY OUT CHAR
)
AS
BEGIN
/* SELECT * */
BEGIN
[code].......
PL/SQL :: Return Results Before Procedure Execution Completes
May 21, 2013Is there any way of returning output parameter values to calling environment before completion of procedure execution. I may achieve it by using GTTs, looking for any other way (because calling environment again need to issue select statement to retrieve data from GTT).
Example case:
Procedure have multiple ref cursors as out parameters.
....
...
if exp1=exp2
then
open v_ref_var1 for select ...from ... ;
end;
[code]..........
If the first if condition satisfies, ref cursor - v_ref_var1 data should be immediately available for the calling environment.
Server Utilities :: Execution Of Oracle 11g Wrapped Procedure Gives Ora-00900 Error
Apr 4, 2013I have a procedure which i wrapped using the oracle 11g wrap utility. If i execute the wrapped procedure using jdbc i am getting an error of 0RA-00900 invalid sql statement.
The procedure is having basic sql statements only.The same procedure if i wrap using Oracle 9i and execute using jdbc it works fine.Is there any change in Oracle 9i wrap utility and Oracle 11g wrap utility.
I tried even Oracle 10g wrap it is also not working fine.
SQL & PL/SQL :: Dropping A Snapshot
Apr 13, 2008I am unable to drop a snapshot , i tried even from sys, it is giving the following error,
drop snapshot adm.dup_resource_status
9:46:29 ORA-08103: object no longer exists
but, when i try to create a new snapshot with the same name
CREATE MATERIALIZED VIEW ADM.DUP_RESOURCE_STATUS
..............
I get
ORA-12006: a materialized view with the same user.name already exists.
PL/SQL :: Dropping A Partition?
Jul 7, 2012Do i have to create indexes again if i drop a partition on a table?
View 6 Replies View RelatedPL/SQL :: Dropping Partitions At Once
Nov 2, 2012ALTER TABLE table_name DROP PARTITION (partition_1000);
ALTER TABLE table_name DROP PARTITION (partition_1001);
...
.........
......
ALTER TABLE table_name DROP PARTITION(partition_1320);(b
it is a delta partition,so trying to remove 320 partitions at once in pl/sql developer for a single table.
Like this i have to remove for more then 15 tables one by one, will this effect the database like filling up the archinve log destination by writing more logs.
kind of problems that i am going to face , as i am doing it on the production box directly.
Dropping A User Profile
Jun 9, 2010Would like to know:
When you drop a user profile, Oracle automatically assigns the default profile to that user - knowing that no other profile has been assigned to that user.
Does this happen in the same session or after a restart?
A user must have a profile at all times, so if a profile is dropped, then the default profile should be assigned in the same session because if not, then during that session the user has no assigned profile which shouldn't happen?
Dropping Users And Recreating?
Jun 18, 2012we are refreshing data base for our application from the base load,Every time when ever there is refresh required we need to drop the users and recreate every thing from the base load. For this we need to kill the sessions and drop the users ,Putting the instance in the restricted mode and refreshing the db.Some time when ever During the killing and dropping process there are some errors like you can not drop the users which is currently connected .
set termout on
set echo on
spool Kill_sessions_drop_users.log
DECLARE
[code]...
how to get rid of this
Dropping Encrypted Tablespace
Mar 29, 2013I created an encrypted tablespace for testing. I later dropped it but don't remember if I specified "including contents and datafiles". The tablespace was empty and there are no datafiles for it. However, the information for this dropped tablespace still shows up in v$encrypted_tablespaces. How do I get that lingering information removed?
View 3 Replies View RelatedDropping Column From Huge Table
Aug 9, 2012I am trying to drop a column from a huge partitioned table (non compressed including partitions). I am working on 11gR2 database for information.
i used below approach
1. alter table <tab_name> set t1 unused column;
2. alter table <tab_name> drop unused columns;
then i got the below error message
ORA-39726: unsupported add/drop column operation on compressed tables
First statement did work i could be able to add another column with the same name but still don't want the unused column on the table.
SQL & PL/SQL :: Dropping Users - Queue Tables
Dec 9, 2010I am trying to drop a user but i get the following error
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables
A couple of questions on this error:
- I did a search on the forum and the thread [URL] appears to have a solution to it by using
DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'DEF$_AQCALL', force =>TRUE);
What i am not sure of is which queue will the above statement drop if run as sys and there are multiple schemas with different schema names but with the same queue name?
- We recently rebuilt the database server and prior to the rebuild i have always managed to drop a user without the above error. Is it likely that some setting somewhere is changed?
SQL & PL/SQL :: Dropping Unused Columns In A Partition?
Jan 21, 2003I have been trying to drop an unused column in a partitioned table, and the number of records stored in this unused column was very high. I kept on running into errors as follows:
ORA-01562: failed to extend rollback segment number 10
ORA-01650: unable to extend rollback segment R09 by 256 in tablespace RBS
I tried to "SET TRANSACTION USE ROLLBACK SEGMENT <name>" with a larger rollback segment, but it still did not work. Can I drop the "unused column" from each partition instead?
How to apply that? Or, what are my options besides increasing the size of the rollback segment?
SQL & PL/SQL :: Does Dropping Schema Purges Data
Oct 21, 2011I am working on Oracle 10g. I want to drop a schema and I want to re use the space in data files to other schema.
If I drop the user by "DROP USER USERNAME CASCADE" , tables in that schema are purged by default or do I have to explicitly drop the tables , purge the re cycle bin and then drop the schema?
Dropping Database Also Drop SP File?
Aug 21, 2013when going through some white papers i find athing which made me confusing it stats that dropping a database will also drop the sp file.
is it true
sp file is important is n't it
PL/SQL :: Dropping Connected Users In Oracle DB?
Mar 27, 2013I want to drop some users in Oracle DB using sqlplus but I am getting error:
SQL> DROP USER test CASCADE;
DROP USER test CASCADE
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected.But when I ran below command to know sessions connected I am not getting any results:
SQL> select sid,serial# from v$session where username = 'test';
no rows selected
While Dropping Old Undo Tablespace / Getting Error
Dec 23, 2012i Cannot drop old undo tablespace. While dropping the old undo tablespace we get an error
ERROR at line 1: ORA-01548: active rollback segment '_SYSSMU77$' found, terminate dropping tablespace
SQL> select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';
TABLESPACE_NAME STATUS SEGMENT_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
APPS_UNDO NEEDS RECOVERY _SYSSMU77$
Change Start With Value On A Sequence Without Dropping And Recreating It?
Jul 26, 2010Is it possible to change the start with value on a sequence without dropping and recreating it?
View 3 Replies View RelatedSQL & PL/SQL :: Modify Supertype Without Dropping / Recreating Its Subtypes?
Oct 2, 2013is there a way to modify supertypes without dropping / recreating its subtypes?
View 6 Replies View RelatedServer Administration :: Error While Dropping User?
Mar 30, 2012Got following error while droping user
DB:9.2.0.8.0 OS:Windows 2003
SQL> drop user custom cascade;
drop user custom cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-21700: object does not exist or is marked for delete
PL/SQL :: Killing And Dropping Users Connected From Application
Jul 11, 2012I have a data base for the bank application development. So there might be more than one sessions of the application running on the data base as well as there might be more than one data base sessions being connected from the dbas.
There is refresh procedure to refresh the data base from the baseload scripts ,So for that first we need to disconnect the users (Kill the sessions) and then drop the users then recreate the users and populate the users with the base load data.
I have prepared the killing and dropping user script in the following manner, Most of the times it is working fine and killing the users but some times it is not able to kill the drop the users it is erroring out with the error that can not dorp the users as there are active sessions running ,
set termout on
set echo on
spool Kill_sessions_drop_users.log
DECLARE
v_alt_stmt VARCHAR2(1000) := 'ALTER SYSTEM DISCONNECT SESSION ''';
v_kill_stmt VARCHAR2(1000);
[code].......
--This will kill all the sessions except the current session
CURSOR cur_usr_dtls IS SELECT username FROM all_users WHERE username IN ('ACTIVITI','TP','TPCORR','TPTUX','TPVIEW');
v_dr_stmt VARCHAR2(1000);
BEGIN
EXECUTE IMMEDIATE 'ALTER SYSTEM ENABLE RESTRICTED SESSION';
DBMS_OUTPUT.PUT_LINE('The database has been put in the restricted mode');
FOR v_cur_ssn IN cur_session
[code]........
--Here we are making the processor to wait for 60 mins so that that the killing will happen at the OS label
DBMS_LOCK.SLEEP(60);
FOR v_cur_usr_dtls IN cur_usr_dtls LOOP
v_dr_stmt :='DROP USER '||v_cur_usr_dtls.username ||' CASCADE';
BEGIN
EXECUTE IMMEDIATE v_dr_stmt;
[code]........
PL/SQL :: Dropping Partition Dynamically In Interval Partitioning
Aug 22, 2012I have created an Interval Partition Table as show below, Is their any way, i can drop the partition dynamically when i truncate the table as oracle creates them with system generated name? Instead Alter table drop partition !
Create Table Script :
------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE TBL_EMP_SALES
(
EMP_ID NUMBER(38),
EMPNAME VARCHAR2(9),
EMP_SALES_AMOUNT NUMBER(5),
EMP_SALES_DATE DATE,
[Code]....
Insert Script :
------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO TBL_EMP_SALES VALUES (1001,'A',50,SYSDATE);
INSERT INTO TBL_EMP_SALES VALUES (1002,'B',100,SYSDATE+1);
INSERT INTO TBL_EMP_SALES VALUES (1003,'C',80,SYSDATE+2);
[Code]....
Partitions Created :
-------------------------------------------------------------------------------------------------------------------------------------------------
select segment_name,partition_name,bytes from user_segments where segment_name = 'TBL_EMP_SALES';
segment_name partition_name bytes
TBL_EMP_SALES P0 8388608
TBL_EMP_SALES SYS_P602 8388608
TBL_EMP_SALES SYS_P603 8388608
[Code]....
Server Administration :: Dropping Lower Case Tablespace
Nov 18, 2011How can I drop a lower case tablespace name from my DB?
SQL> select * from v$version;
BANNER
------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
[code]....