Home » SQL & PL/SQL » SQL & PL/SQL » Drop objects
Drop objects [message #251992] Tue, 17 July 2007 11:08 Go to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member

I need to drop just the objects from some table spaces. I don't wana drop the tables spaces.
drop tablespace including contents;]

I think this is the script for dropping table spaces and contents. But if i just wana drop the contents.

Thanks in advance
Re: Drop objects [message #251997 is a reply to message #251992] Tue, 17 July 2007 11:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no statement for this.
dba_segments gives you the objects in each tablespace.
Query it and loop to drop all segments.
Take care of (sub)partitions.

Regards
Michel

Re: Drop objects [message #252088 is a reply to message #251992] Tue, 17 July 2007 16:02 Go to previous messageGo to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member

Thanks for you help

Re: Drop objects [message #252091 is a reply to message #252088] Tue, 17 July 2007 16:25 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
You can also use some thing like this but keep in mind MICHEL adive.
But it could be produce error for FK/Pk relationship.
SQL> select 'drop '||segment_type||' '||segment_name||' ;' from dba_segments
  2  where tablespace_name='DBRD_USER_TS';

'DROP'||SEGMENT_TYPE||''||SEGMENT_NAME||';'
--------------------------------------------------------------------------------
drop INDEX SLA_DETAILS_PK ;
drop INDEX BACKEND_METRICS_PK_IDX ;
drop INDEX SLA_LOCATIONS_PK ;
drop TABLE MOSDATABASECONTROL ;
drop TABLE MOSDATABASES ;
drop TABLE BSACONFIGHISTORY ;
drop TABLE BSAFACTSERIESROOTCAUSES ;
drop TABLE BSAROOTCAUSEHIERARCHY ;
drop TABLE BSAROOTCAUSECHAINS ;
drop TABLE BSAROOTCAUSEREASONS ;
drop TABLE BSAROOTCAUSETREES ;
drop TABLE BSAFACTSERIESSUMMARY ;
drop TABLE BSAALARMQUEUE ;


[Updated on: Tue, 17 July 2007 16:26]

Report message to a moderator

Re: Drop objects [message #252093 is a reply to message #252091] Tue, 17 July 2007 16:33 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
You can use this to aviod FK/PK error.

 1  select 'drop '||segment_type||' '||segment_name||' CASCADE CONSTRAINTS;' from dba_segments
  2  where tablespace_name='DBRD_BSW_TS'
  3  and segment_type='TABLE'
  4  union
  5  select 'drop '||segment_type||' '||segment_name||' ;' from    dba_segments
  6  where tablespace_name='DBRD_BSW_TS'
  7* and segment_type !='TABLE'


drop INDEX SYS_C003034 ;
drop INDEX SYS_C003040 ;
drop INDEX SYS_C003048 ;
drop INDEX SYS_C003053 ;
drop TABLE BSAADAPTERS CASCADE CONSTRAINTS;
drop TABLE BSAALARMDATA CASCADE CONSTRAINTS;
drop TABLE BSAALARMMETADATA CASCADE CONSTRAINTS;

[Updated on: Tue, 17 July 2007 16:39]

Report message to a moderator

Re: Drop objects [message #252161 is a reply to message #252093] Wed, 18 July 2007 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't drop an index before the constraint it belongs to.
Just "drop table cascade".
Then drop index in the tablespace which are not already dropped but maybe this is not a "drop index" but an "alter table drop constraint".

Dropping all segments in a tablespace is not really easy but it can be done with care and patience to handle all cases.

Regards
Michel
Re: Drop objects [message #252162 is a reply to message #252161] Wed, 18 July 2007 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without forgetting the case of recycle bin objects. Wink

Regards
Michel
Re: Drop objects [message #252328 is a reply to message #252162] Wed, 18 July 2007 11:36 Go to previous messageGo to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member

It means I have index belong to any constraint, If i drop or rename the that constraint it will simply drop or rename that index too.

Thanks for you help
Re: Drop objects [message #252332 is a reply to message #252328] Wed, 18 July 2007 11:42 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no strict relation between constraint and index names.
They may have or not the same name.
So changing the name of one does not change the name if the other one.

Drop case is another thing. If an index is the support of a constraint, you can't drop the index without dropping the constraint. You can drop a constraint with (default behaviour) or without the associated index...

Regards
Michel
Previous Topic: Grant - All objects in a schema to a new user
Next Topic: Insert or/and update
Goto Forum:
  


Current Time: Sun Dec 11 00:15:00 CST 2016

Total time taken to generate the page: 0.08761 seconds