SQL & PL/SQL :: Unable To Drop A Procedure?
Nov 29, 2012I tried to Drop a procedure we have created, but an error is raised while droping it.
ora-04043 object does not exist
ADVERTISEMENT
Unable To Drop Procedure
Oct 29, 2013i'm issuing drop procedure generatePasswordForEportalUser
i have an
error ORA-00600: [kqlobjlod-no-result-from-proc$], [1403], [659858], [], [], [], [], [], [], [], [], []. i found on metalink then Doc ID 1267554.1
is about this error i'm using oracle 11.2.0.3 in this document there is an info that 11.2.0.2 solves the issue Interesting thing is that select * from user_procedures where upper(object_name) like upper ('generatePasswordForEportalUser');
generatePasswordForEportalUser6598581PROCEDURENONONONONODEFINERobject name is in lovercase it was created by create procedure generatePasswordForEportalUser asbeginnull;end ;without " name " why it is in lowercase in data dictionary
SQL & PL/SQL :: Unable To Drop A Trigger?
Jun 14, 2011I have a trigger called DDL_TRIG which is used to audit all DDL level operation on the Database. The trigger script is as below:-
CREATE OR REPLACE TRIGGER ddl_trig
AFTER DDL
ON DATABASE
BEGIN
INSERT INTO ddl_log
(user_name, ddl_date, ddl_type,
[code].....
The Table DDL_LOG exists.Now i want to disable or drop this trigger and it wont allow us. While doing on one schema it fails and with below error:
when logged in schema1 and give drop or disable command it fails
ORA:04098:trigger schema2.ddl_trig is invalid and failed re-validation.
when logged in schema2 and give drop or disable command it fails
ORA:04098:trigger schema1.ddl_trig is invalid and failed re-validation.
how can we drop this trigger in both the schema's. This trigger is not allowing us to do any activity on the Database and causing lot of problem.
PL/SQL :: Unable To Drop A Broken Job
Jan 3, 2013i want to drop a broken job but it is taking long time why?
i am using below syntax:
begin
dbms_job.broken(job=> 345,FALSE);
commit;
end;
/
SQL & PL/SQL :: Unable To Truncate / Drop Table
May 13, 2011I am trying to Truncate the table but it doesnt get truncated. When I issue the command it even doesnt throw the error. I also tried to drop the table but cant even able to drop the table. I thought table might be locked. But it allows me delete a row from the table.
I am using Oracle 11g Release 11.1.0.7.0
PL/SQL :: Unable To Drop Unused Column?
Nov 14, 2013SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER F_TRADING_COLLATERAL_SR 1 SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER F_TRADING_COLLATERAL_SR 1 SQL> alter table F_TRADING_COLLATERAL_SR drop unused columns; Table altered. SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER F_TRADING_COLLATERAL_SR 1 SQL>
View 6 Replies View RelatedClient Tools :: Unable To Drop Roles
Jan 10, 2012When I try to drop the roles in dbartisan tool, I get an error saying "Drop DDL for **_***********_ROLE has not been generated because it is a system object", but if i go to sql prompt and say "drop role rolename", it is working. How do I remove the role in dbartisan itself?
View 9 Replies View RelatedStreams :: Unable To Drop Apply Queue?
Oct 10, 2013,I am trying to set up Streams on a 11.2.0.3 on a Windows 2008R2 server. Due to an error in running propagation, i a, trying to delete both Capture and Apply queues. I have deleted the Apply queues but unable to drop the Catpure,
SQL>EXEC DBMS_APPLY_ADM.STOP_APPLY(apply_name =>'LAO_NLPG73_BLPU_APPLY'); SQL> select * from dba_apply; LAO_NLPG73_BLPU_APPLY NLPG73_BLPU_APPLY_Q STRMADMIN YES RULESET$_732 STRMADMIN STRMADMIN 301355 ABORTED 09/10/2013 17:34:21 1013 ORA-01013: user requested cancel of current operation CAPTURED STREAMS APPLY SQL> select * from dba_queues; STRMADMIN NLPG73_BLPU_APPLY_Q
[code]...
SQL & PL/SQL :: Unable To Drop Interim Table After Redefinition Process
May 11, 2012I performed the Table Redefinition process on one of my transaction tables. I wanted to redefine the existing table to a new table with partitions. Everything worked fine until i reached the last step where we had to drop the interim table. Now in between the redefinition , we created the interim table with partition along with the constraints and indexes and then atlast ran the "dbms_redefinition.finish_redef_table" proc to finish the redefinition.
And after that when I tried to drop the interim table , I am getting the below error message
"ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys"
So this means all the constraints from my actual table were swapped with the interim table and hence I am getting this error.So is this a normal case or I have missed something while performing the redefinition? If thats normal then should i Just Drop table tablename CASCADE CONSTRAINTS?
Server Administration :: Unable To Drop User Without Database Shutdown
Apr 19, 2013We are unable to drop user due to below error, how to drop the below user without shutdown the database.
SQL> drop user mvm_2010 cascade;
drop user mvm_2010 cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
No session was available for particular user.
Server Administration :: Unable To Drop User - Table Or View Does Not Exist
Jun 6, 2011Im facing the problem whenever I try to drop a user. Following thing that I m trying..
system@vahan> drop user knp cascade;
Error at line 1:
ORA-00604: error occured at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 7
SQL & PL/SQL :: Procedure To Display Data From Drop Down List
Feb 6, 2012I have to write a proc to display data based on user input for t_grp, and it should exclude grp0
So below is the proc I have written, and I want to display the report for all t_grp when user gives user_input as All or gives null. How can I acheieve this is in procedure.
table: target_data
create table target_data
(
t_mon varcahr2(6)
, t_grp varchar2(30)
, t_data varchar2(50)
)
;
INSERT INTO target_data VALUES ('Apr-11', 'grp1', 'some_data');
INSERT INTO target_data VALUES ('Apr-11', 'grp2', 'some_data');
INSERT INTO target_data VALUES ('Apr-11', 'grp4', 'some_data');
[code]......
Now the above proc is running fine, if I give the input as grp1 or any other t_grp.
SQL & PL/SQL :: Create And Drop Table Inside The Procedure
Jun 10, 2013I've to create a table every time a procedure is run, initially the table should be dropped and then created every time. From this procedure, the table is neither created nor dropped.
I cant track the error. Why is it so?
CREATE OR REPLACE PROCEDURE TESTPROC IS
S_SQL VARCHAR2(1000);
BEGIN
S_SQL := 'DROP TABLE MYTEST PURGE';
EXECUTE IMMEDIATE S_SQL;
[code]........
SQL & PL/SQL :: Unable To Override Parent Procedure
Jun 4, 2013--Create a parent object
CREATE OR REPLACE TYPE PARENTOBJ AS OBJECT
(
FIRST_NAME VARCHAR2(50),
static function GETNAME return varchar2,
MEMBER PROCEDURE getoutput
)
NOT FINAL;
/
----Create the body for parent object type
CREATE OR REPLACE TYPE BODY PARENTOBJ AS
static function GETNAME return VARCHAR2 IS
BEGIN
RETURN 'PARENTOBJ';
END ;
MEMBER PROCEDURE getoutput iS
BEGIN
DBMS_OUTPUT.PUT_LINE( 'Hello world from '||PARENTOBJ.getname );
END ;
END;
/
--Create a child object
CREATE OR REPLACE TYPE CHILDOBJ UNDER PARENTOBJ
(
static function GETNAME return VARCHAR2,
--override the parent method
OVERRIDING MEMBER PROCEDURE getoutput
)
FINAL;
/
----Create the body for child object type
CREATE OR REPLACE TYPE BODY CHILDOBJ AS
static function GETNAME return VARCHAR2 IS
BEGIN
RETURN 'childobj';
END ;
--override the parent method
OVERRIDING MEMBER PROCEDURE getoutput IS
BEGIN
DBMS_OUTPUT.PUT_LINE( 'Hello world from '||CHILDOBJ.getname );
END;
END;
/
The above types are created with no issues. However, when I am trying to inherit the child method and override its parent, I am getting the follwoing error:-
--NOW INHERIT THE PARENT INTO CHILD, AND OVERRIDE THE PARENT METHOD BY CALLING THE CHILD METHOD
DECLARE
OBJ PARENTOBJ;
BEGIN
OBJ := NEW CHILDOBJ(NULL);
DBMS_OUTPUT.PUT_LINE('obj method getoutput() = ' || GETOUTPUT());
END;
/
ORA-06550: line 6, column 56:
PLS-00201: identifier 'GETOUTPUT' must be declared
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored
Unable To Debug Procedure In 11g Database Using PL/SQL Developer
Mar 21, 2013We have recently migrated our database from 10.2.0.4 to 11.2.0.2. We were able to debug procedures using PL/SQL Developer earlier but after 11g migration we are not able to debug the same procedure in 11g database.
Is it that we require 11g PL / SQL developer with 11g client installed to debug?
SQL & PL/SQL :: Table Update - Unable To Identify Procedure
Feb 21, 2011We have one table which is updated by some program but we are not able to identify the procedure, package or script which update it.
View 2 Replies View RelatedServer Utilities :: Unable To Import One Procedure From Full Database Dump ( 10g )
Jun 20, 2008i have full export dump file....from this, i need to import only one procedure belongs to schema : IC_MIGR_DATA... i need to import into SCHEMA : rep_user...
iam giving syntax:
impdp system/icg0ld@ICPRD directory=DUMPDIR dumpfile=IC_FULL_19062008.dmp logfile=imp_IC_FULL_190608.log schemas=rep_user parfile=imp_proc.par
parfile :
--------
INCLUDE=PROCEDURE:"LIKE 'IC_MIGR_DATA.JET_UPLIFT'"
while importing, iam getting below error,
*****[oracle10@AIICDELL IC]$ impdp system/icg0ld@ICPRD directory=DUMPDIR dumpfile=IC_FULL_19062008.dmp logfile=imp_IC_FULL_190608.log schemas=rep_user parfile=imp_proc.par
Import: Release 10.2.0.2.0 - 64bit Production on Friday, 20 June, 2008 16:19:46
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-31694: master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" failed to load/unload
ORA-31644: unable to position to block number 30698425 in dump file "/AIIC_backup/expbkp/dumps/IC/IC_FULL_19062008.dmp"
******
how to import this one procedure JET_UPLIFT , this has to be imported into REP_USER schema, owner of this procedure is IC_MIGR_DATA
Sum Of All Drop Down Values
Nov 28, 2012I have a dropdown in my jsp with all users from database. When I select a user I'm able to get the details of the corresponding result, but when I click on all it should give the total individually. Below is the code that I use to get individually.
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@include file="DBCon.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta charset="utf-8" />
[code]...
How To Drop Column
Aug 25, 2010i am tring to drop column but it is taking much time . And there is not locking session also. The table size is 500GB. We have any way to drop to column in fast?
View 2 Replies View RelatedSQL & PL/SQL :: Drop A Table?
Apr 6, 2010When drop a table what happen to view,sequence,synonyms,index,constraint for the table
View 9 Replies View RelatedPL/SQL :: Drop All Procedures At One Go?
Aug 29, 2012I 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
Oracle 11.2.0.2 - How To Drop Partition
Apr 19, 2012we have our production database running on 11.2.0.2. I have a user table which has partitions. i would like to drop the partition and seeing weird issue:
The command to drop the partition i have is:
alter table SA.accounts drop partition dec_20111203 update global indexes;
Say after 10 minutes, the session is terminated and we are seeing the following: ORA-00028. We don't have a process that kills the session so we are trying to find the root cause why the session is being killed. what might be going on? also as an alternative - i was looking to do the following and want to know - if this works or not:
ALTER TABLE sa.accounts TRUNCATE PARTITION dec_20111203;
Then try to do the drop partition again.
SQL & PL/SQL :: Determine Index Drop?
Apr 3, 2012How to find out who has dropped and when was the index created in database?
View 39 Replies View RelatedDrop All Tables In Oracle 8
Dec 17, 2012i want drop all tables in oracle8
View 2 Replies View RelatedSQL & PL/SQL :: Drop Partitions With Zero Rows
Sep 26, 2011Query to drop partitions on a table who have no.of rows as zero.
select 'ALTER' || '' || 'TABLE' || TABLE_NAME || 'DROP' || 'PARTITION' || PARTITION_NAME from dba_tab_partitions where TABLE_OWNER='xyz' ;
select count(*) from table_name partition (partition_name);
SQL & PL/SQL :: Create A Drop-down Field?
Oct 16, 2013i want to create a dropdown field it will display list of currencies for selection derived from
sy_curr_code (columns currency_code and des1)
in the s_sales_center(table )
-If INF_SRC_DB = AUS, will default to USD, otherwise default to BPS.
i have created this so far
vSQl := 'select toValueText(a.code1, a.descr) from (select currency_code code1, DES1 descr1 '||
'from sy_curr_code where active = ''Y'' '||
'and subcat1 = ''Y'' '||
') a ';
how to add the other logic into my code
How To Drop More Than One User At Time
Nov 3, 2012in one short i want to drop more than one user?
View 6 Replies View RelatedPL/SQL :: Drop Context Index
Nov 9, 2012i tried creating an context index. But it is aborted in the middle. do to reuse the spce i want to drop the index. But it shows an error that " cannot drop index marked as process" how to delete the index.
View 1 Replies View RelatedXE :: How To Drop User And Schema
Aug 31, 2012I want to free up some space , as i have created some testing users and schema.
View 3 Replies View RelatedHow To Monitor Drop User
Jan 31, 2013How to monitor the DROP USER command?
OS: AIX, Oracle: 11.2.0.2