Home » RDBMS Server » Server Administration » Cannt drop tablespace (please, urgent)
Cannt drop tablespace (please, urgent) [message #287382] Wed, 12 December 2007 02:16 Go to next message
irremediable
Messages: 38
Registered: December 2007
Member


Hello!
I have a tablespace that has about 2000 tables.
The only owner of tables in this TS is a user DWH.
I was trying to drop it,

"DROP TABLESPACE DWH INCLUDING CONTENTS AND DATAFILES"

but it says
"unique/primary keys in table referenced by foreign keys"

I retrieved the list of all constraints of tables that are not in this TS like this

SELECT * FROM all_constraints
WHERE (r_owner, r_constraint_name) IN (SELECT owner
,constraint_name
FROM user_constraints F
WHERE constraint_type IN ('P', 'U')
AND table_name IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TABLESPACE_NAME = 'DWH')
)
and disabled them (staus is shown as DISABLED).

But when trying do drop it again it again says
"unique/primary keys in table referenced by foreign keys"

Could you please tell what I'm doing wrong?
Thank you!

Report message to a moderator

Re: Cannt drop tablespace (please, urgent) [message #287383 is a reply to message #287382] Wed, 12 December 2007 02:22 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member
use CASCADE keyword with drop tablespace statement.
http://www.google.ae/search?hl=en&q=drop+tablespace+%2B+unique%2Fprimary+keys+in+table+referenced+by+foreign+keys&btnG=Google +Search&meta=

and next time please don't use URGENT word.

Report message to a moderator

Re: Cannt drop tablespace (please, urgent) [message #287384 is a reply to message #287382] Wed, 12 December 2007 02:24 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member
Hi,

Today you registered to this site. And posted a query too. And now you have to read this link Orafaq-Link to follow rules of this site.

Regards,
Kiran.

Report message to a moderator

Re: Cannt drop tablespace (please, urgent) [message #287385 is a reply to message #287383] Wed, 12 December 2007 02:28 Go to previous messageGo to next message
irremediable
Messages: 38
Registered: December 2007
Member



Exsume me for using the "URGENT" word.
But the link-docs say that using the CASCADE word when dropping a tablespace it drops foreign key constraints that reference tables' unique or primary constraints in the dropped
tablespace.
That means I'll lose those foreign keys.
Am I right?

Report message to a moderator

Re: Cannt drop tablespace (please, urgent) [message #287387 is a reply to message #287385] Wed, 12 December 2007 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes you lose them but as you dropped the parent tables they became meaningless?

Regards
Michel

Report message to a moderator

Re: Cannt drop tablespace (please, urgent) [message #287389 is a reply to message #287387] Wed, 12 December 2007 02:34 Go to previous messageGo to next message
irremediable
Messages: 38
Registered: December 2007
Member


What I'm trying to do is to recreate the tablespace.
It contained only one big file.
I'm going to split the tablespace into 5 datafiles.
I've exported it's data into a dmup file.
This is why I want to drop it, recreate with 5 datafiles
and then import the data back.

Is there any other solution?
Thank you.

Report message to a moderator

Re: Cannt drop tablespace (please, urgent) [message #287393 is a reply to message #287389] Wed, 12 December 2007 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Precreate the new tablespace and move the objects (alter table move tablespace, alter index rebuild tablespace).
It is faster and you don't have to recreate the FK and other granted privileges and it does not invalidate dependent objects...

But the question is: why do you need to split your file? How big is big?

Regards
Michel

Report message to a moderator

Re: Cannt drop tablespace (please, urgent) [message #287398 is a reply to message #287382] Wed, 12 December 2007 02:45 Go to previous messageGo to next message
irremediable
Messages: 38
Registered: December 2007
Member


There is application that works exactly with a tablespace
that has the name of "DWH".
This is why I'm trying to recreate exactly this tablespace.
And the answer to your question is: the file is about 90 GB.

Report message to a moderator

Re: Cannt drop tablespace (please, urgent) [message #287399 is a reply to message #287398] Wed, 12 December 2007 02:51 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member
>And the answer to your question is: the file is about 90 GB.
And the data size is inside the datafile.

Instead of recreate/split tablespace you should maintain big datafile tablespace. as fas as size you can resize your datafile size upto HWM.

Report message to a moderator

Re: Cannt drop tablespace (please, urgent) [message #287400 is a reply to message #287399] Wed, 12 December 2007 02:52 Go to previous messageGo to next message
irremediable
Messages: 38
Registered: December 2007
Member


Can the following be a solution
1.To create a new TS, named DWH_TEMP.
2.Move the objects (alter table....move) from DWH to DWH_TEMP.
3.Rename the DWH to DWH_OLD.
4.Rename DWH_TEMP to DWH.
5.Drop DWH.
?

Thank you.

Report message to a moderator

Re: Cannt drop tablespace (please, urgent) [message #287401 is a reply to message #287400] Wed, 12 December 2007 02:56 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member
Can the following be a solution
1.To create a new TS, named DWH_TEMP.
2.Move the objects (alter table....move) from DWH to DWH_TEMP.
also rebuild all indexes and if your tables contents LONG datatype will not move to new tablespace.

3.Rename the DWH to DWH_OLD.
4.Rename DWH_TEMP to DWH.
5.Drop DWH.

Yes,but also test first on dev/test db.

[Updated on: Wed, 12 December 2007 02:57]

Report message to a moderator

Re: Cannt drop tablespace (please, urgent) [message #287404 is a reply to message #287401] Wed, 12 December 2007 03:02 Go to previous messageGo to next message
irremediable
Messages: 38
Registered: December 2007
Member



Ok. Thank you very mych.
I'll take some time.
Shall I close this topic or leave it open in case?

Report message to a moderator

Re: Cannt drop tablespace (please, urgent) [message #287416 is a reply to message #287404] Wed, 12 December 2007 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Live the topic as it is and come back if you are other related questions.

Point 5 is drop DWH_OLD
Renaming a tablespace only exists from 10g.

Regards
Michel

Report message to a moderator

Re: Cannt drop tablespace (please, urgent) [message #287440 is a reply to message #287416] Wed, 12 December 2007 04:29 Go to previous messageGo to next message
irremediable
Messages: 38
Registered: December 2007
Member



How can I list all objects contained in a tablespace?
And if there are objects other than tables in the tablespace
how then can I move those objects onto the new tablespace?

Report message to a moderator

Re: Cannt drop tablespace (please, urgent) [message #287459 is a reply to message #287440] Wed, 12 December 2007 05:21 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
irremediable wrote on Wed, 12 December 2007 05:29


How can I list all objects contained in a tablespace?

DBA_SEGMENTS
irremediable wrote on Wed, 12 December 2007 05:29


And if there are objects other than tables in the tablespace
how then can I move those objects onto the new tablespace?


Depends on what type of object it is, but as Michel pointed out typcially an ALTER TABLE...MOVE TABLESPACE or ALTER INDEX...REUILD TABLESPACE will suffice.

Report message to a moderator

Re: Cannt drop tablespace (please, urgent) [message #287475 is a reply to message #287459] Wed, 12 December 2007 05:55 Go to previous messageGo to next message
irremediable
Messages: 38
Registered: December 2007
Member



Package, for example.
But I exported it with the aid fo PL/SQL Developer.
I'll still leaving this topic open.

Report message to a moderator

Re: Cannt drop tablespace (please, urgent) [message #287476 is a reply to message #287475] Wed, 12 December 2007 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Packages are in SYSTEM tablespace.

Regards
Michel

Report message to a moderator

Re: Cannt drop tablespace (please, urgent) [message #287733 is a reply to message #287476] Thu, 13 December 2007 05:12 Go to previous message
irremediable
Messages: 38
Registered: December 2007
Member



I managed to do it.
Thank you.

Report message to a moderator

Previous Topic: Switching between Local and Automatic Segment Management
Next Topic: connection from oracle to Microsoft Access
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Sep 19 10:46:12 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.