OraFAQ Forum: SQL & PL/SQL » multiple DROP questions from balaji

Home » SQL & PL/SQL » SQL & PL/SQL » multiple DROP questions from balaji (10.2.0.2.0, Unix)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

multiple DROP questions from balaji [message #506984]

Thu, 12 May 2011 02:58

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Hi ,

When i tried to drop a type using below command , i received errors

DROP TYPE JAM_ACAS_MSG_TYPE

ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

How to resolve it?

Thanks

Edit :by ROD
modified title..previously its bajil..changed to balaji

[Updated on: Thu, 12 May 2011 08:02] by Moderator

Report message to a moderator

Re: ORA-02303: cannot drop type [message #506987 is a reply to message #506984]

Thu, 12 May 2011 03:07

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Drop the dependent types or tables.
USER_DEPENDENCIES will give you their names.

Regards
Michel

[Edit: typo]

[Updated on: Thu, 12 May 2011 03:30]

Report message to a moderator

Re: ORA-02303: cannot drop type [message #506993 is a reply to message #506987]

Thu, 12 May 2011 03:20

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Thank you Michel

Report message to a moderator

Drop queue [message #506994 is a reply to message #506984]

Thu, 12 May 2011 03:23

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Hi,

When i tried to drop a queue i received error message.

DROP QUEUE JAM_ACAS_QUEUE
*
ERROR at line 1:
ORA-00950: invalid DROP option

How to resolve the error.

Thanks

Report message to a moderator

Re: Drop queue [message #506995 is a reply to message #506994]

Thu, 12 May 2011 03:30

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Use DBMS_AQADM package.

Regards
Michel

Report message to a moderator

Re: Drop queue [message #506996 is a reply to message #506995]

Thu, 12 May 2011 03:37

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

can i have one example of this DBMS_AQADM package to drop queue?

Thanks

Report message to a moderator

Re: Drop queue [message #506998 is a reply to message #506996]

Thu, 12 May 2011 03:44

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

http://psoug.org/reference/dbms_aqadm.html

Report message to a moderator

Re: Drop queue [message #507001 is a reply to message #506998]

Thu, 12 May 2011 04:10

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Thanks.

Is this the command to drop queue.Can you confirm ?

dbms_aqadm.drop_queue (
queue_name IN VARCHAR2,
auto_commit IN BOOLEAN DEFAULT TRUE);

And Am i suppose to run these 2 sql ?

$ORACLE_HOME/rdbms/admin/dbmsaqad.sql

also see: $ORACLE_HOME/rdbms/admin/catqueue.sql

Thanks

[Updated on: Thu, 12 May 2011 04:12]

Report message to a moderator

Re: Drop queue [message #507006 is a reply to message #507001]

Thu, 12 May 2011 04:24

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you already have queues (which is a prerequisite to be able to drop them) then the associated script has already been executed.

Regards
Michel

Report message to a moderator

Re: Drop queue [message #507010 is a reply to message #507006]

Thu, 12 May 2011 04:27

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

I could not understand your reply michel.pls be clear.

Report message to a moderator

Re: Drop queue [message #507011 is a reply to message #507001]

Thu, 12 May 2011 04:27

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Quote:

BEGIN
-- Drop Queue rx_queue
dbms_aqadm.drop_queue(queue_name => 'rx_queue');
END;
/

-- Verify queues is dropped
SELECT queue_name
FROM user_queues;

See the demo on given link .

Report message to a moderator

Re: Drop queue [message #507014 is a reply to message #507011]

Thu, 12 May 2011 04:36

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Thanks Ram,

But am i suppose to run these 2 sql's?

*$ORACLE_HOME/rdbms/admin/dbmsaqad.sql

*also see: $ORACLE_HOME/rdbms/admin/catqueue.sql

Thanks

Report message to a moderator

Re: Drop queue [message #507017 is a reply to message #507014]

Thu, 12 May 2011 04:43

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

If you have a queue in your DB, which you presumably do since you are trying to drop it, then the dbms_aqadm package must exist, since you need it to create queues.
In which case those scipts (which create that package and associated objects) have already been run.

So, no, you don't need to run those sql scripts.

Report message to a moderator

Re: Drop queue [message #507018 is a reply to message #507014]

Thu, 12 May 2011 04:44

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

As I said, if you want to drop some queues this means you have queues.
If you have queues this means that you already executed dbms_aqadm.
If you already executed the package you have not to create it using the script.
So no you have not to execute the script.
But a simple test using the statements given by Sriram would already give you the answer.

Regards
Michel

Report message to a moderator

Re: Drop queue [message #507027 is a reply to message #507018]

Thu, 12 May 2011 04:54

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Thank you Sriram,cookiemaster and Michel for explaining the use of
dbms_aqadm package.

Report message to a moderator

How to drop lob [message #507057 is a reply to message #506984]

Thu, 12 May 2011 05:58

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Hi,

How to drop lob objects? while importing dump file i received below error.

DROP LOB SYS_LOB00003C00009$
*
ERROR at line 1:
ORA-00950: invalid DROP option

I queried dba_lobs to find the related table of lob but it did not return any rows.

How to resolve it?

Thanks

Report message to a moderator

Re: How to drop lob [message #507058 is a reply to message #507057]

Thu, 12 May 2011 06:02

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

How to drop lob objects?

You don't; a LOB does not exist without a table.
You do not drop a LOB you drop the column or the table.

Quote:

I queried dba_lobs to find the related table of lob but it did not return any rows.

You did it wrong.

Regards
Michel

Report message to a moderator

Re: How to drop lob [message #507059 is a reply to message #507058]

Thu, 12 May 2011 06:07

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

http://psoug.org/reference/lobs.html
Se the last example there in the URL

Quote:

SYS_LOB00003C00009$

Is a lob segment_name.

Report message to a moderator

Re: How to drop lob [message #507061 is a reply to message #507058]

Thu, 12 May 2011 06:18

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

How to find out which table or column the lob belongs to ?

i queried dba_tab_columns but it returned no rows.

Thanks

Report message to a moderator

Re: How to drop lob [message #507062 is a reply to message #507061]

Thu, 12 May 2011 06:23

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

ind> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME
  2  from dba_segments
  3  where SEGMENT_NAME='SYS_LOB0000053285C00010$$';

OWNER
------------------------------
SEGMENT_NAME
-------------------------------------------------------------------------------
PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ --------------------
APPOWNER
SYS_LOB0000053285C00010$$
                               LOBSEGMENT         DATA


1 row selected.

ind> select TABLE_NAME,COLUMN_NAME,SEGMENT_NAME
  2  from user_lobs
  3  where SEGMENT_NAME='SYS_LOB0000053285C00010$$';

TABLE_NAME
------------------------------
COLUMN_NAME
-------------------------------------------------------------------------------
SEGMENT_NAME
------------------------------
ORG_TEXT_CUSTOMIZATION
LONG_TEXT
SYS_LOB0000053285C00010$$


1 row selected.

I suggest you to read Oracle documents ...

Good luck
Sriram

Report message to a moderator

Re: How to drop lob [message #507063 is a reply to message #507062]

Thu, 12 May 2011 06:26

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I suggest you to read Oracle documents ...

Ouaaarfff! Did you see to whom you suggested that?

Regards
Michel

[Updated on: Thu, 12 May 2011 06:26]

Report message to a moderator

Re: How to drop lob [message #507064 is a reply to message #507063]

Thu, 12 May 2011 06:27

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Ok, no comments i will read documents .

Report message to a moderator

Re: How to drop lob [message #507065 is a reply to message #507064]

Thu, 12 May 2011 06:33

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

@ Michel

/forum/fa/7255/0/

/forum/fa/7257/0/

/forum/fa/7263/0/

Got the Point.You mean DBA ? But Just 6 months right! Wink

Wink

Sriram

Report message to a moderator

Re: How to drop lob [message #507067 is a reply to message #507065]

Thu, 12 May 2011 06:35

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Just 6 months... 5 years ago.

Regards
Michel

Report message to a moderator

Re: How to drop lob [message #507069 is a reply to message #507067]

Thu, 12 May 2011 06:41

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

/forum/fa/5263/0/

May be he updated his profile/Bio recently ?

Sriram

Report message to a moderator

Re: How to drop lob [message #507070 is a reply to message #507069]

Thu, 12 May 2011 06:43

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

@ balaji ...
Why you are trying to drop these objects from your DB?Any reason ? Smile

Smile

Sriram

Report message to a moderator

Re: How to drop lob [message #507076 is a reply to message #507070]

Thu, 12 May 2011 06:54

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

He likes to drop objects.
...or this is today lesson...

Regards
Michel

Report message to a moderator

Re: How to drop lob [message #507081 is a reply to message #507069]

Thu, 12 May 2011 07:22

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

here is the reason i want to drop these objects .

i exported a dump file from production and i want to import those dump file into
development database.what that import script will do is it will first drop
all the database objects in development database and will
import the production database dump file into it.

Report message to a moderator

Re: How to drop lob [message #507083 is a reply to message #507081]

Thu, 12 May 2011 07:25

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And what we said is that LOBs are not independent objects, just drop the tables and do not care about lobs.

Regards
Michel

Report message to a moderator

Re: How to drop lob [message #507094 is a reply to message #507081]

Thu, 12 May 2011 07:44

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Hmmm..
Is that a full DB dump? or specific Users.

why don`t you just drop the db User?and re import....

Sriram

Report message to a moderator

Re: How to drop lob [message #507269 is a reply to message #507094]

Fri, 13 May 2011 07:32

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

I am looking for a sql script which would find out all the tables belonging
to lobs and then drops all that tables.

Thanks

Report message to a moderator

Re: How to drop lob [message #507271 is a reply to message #507269]

Fri, 13 May 2011 07:38

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

A simple query against dba_tab_cols will give all the tables with LOBs.

Report message to a moderator

Re: How to drop lob [message #507276 is a reply to message #507271]

Fri, 13 May 2011 07:59

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

ind> select comments
  2  from dict
  3  where table_name='DBA_LOBS';

COMMENTS
------------------------------------------------------------------------
Description of LOBs contained in all tables

1 row selected.

ind> select comments
  2  from dict
  3  where table_name='USER_LOBS';

COMMENTS
------------------------------------------------------------------------
Description of the user's own LOBs contained in the user's own tables

1 row selected.

Quote:

all the tables belonging
to lobs

Its a reverse statement.
Table contains LOB no LOB contain TABLE.

ramoradba wrote on Thu, 12 May 2011 16:53

ind> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME
  2  from dba_segments
  3  where SEGMENT_NAME='SYS_LOB0000053285C00010$$';

OWNER
------------------------------
SEGMENT_NAME
-------------------------------------------------------------------------------
PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ --------------------
APPOWNER
SYS_LOB0000053285C00010$$
                               LOBSEGMENT         DATA


1 row selected.

ind> select TABLE_NAME,COLUMN_NAME,SEGMENT_NAME
  2  from user_lobs
  3  where SEGMENT_NAME='SYS_LOB0000053285C00010$$';

TABLE_NAME
------------------------------
COLUMN_NAME
-------------------------------------------------------------------------------
SEGMENT_NAME
------------------------------
ORG_TEXT_CUSTOMIZATION
LONG_TEXT
SYS_LOB0000053285C00010$$


1 row selected.

I suggest you to read Oracle documents ...

Good luck
Sriram

Did you read it properly ?

Sriram

[Updated on: Fri, 13 May 2011 11:17] by Moderator

Report message to a moderator

Previous Topic:	Trouble with SQL query (merged)
Next Topic:	Technologies used in Oracle (2 Merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Aug 22 10:01:48 CDT 2025