OraFAQ Forum: Security » Drop unused Schema

Home » RDBMS Server » Security » Drop unused Schema

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

Drop unused Schema [message #169428]

Wed, 26 April 2006 16:19

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Hi

I have old unused schemas which needs to be dropped,
4 schemas are there with each one having 120(+) tables, data and other objects, I issued

drop user username cascade

it took more than 25 minutes, According to you people the time it took is correct or should have taken less ?,
Another doubt does dropping like this releases all the space the schema was holding.

Another question is , there is a partition table, what is the best way to remove the partition from it how, this table is holding around 2 millions records.i just want to remove partition from that table,dropping table is not possible because so many reference are there.

Thanks

[Updated on: Wed, 26 April 2006 16:35]

Report message to a moderator

Re: Drop unused Schema [message #169503 is a reply to message #169428]

Thu, 27 April 2006 05:00

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>> According to you people the time it took is correct or should have taken less ?,
Depends on the volume of your data. No hard rule here.
>>Another doubt does dropping like this releases all the space the schema was holding.
Yes. If the schema had table/indexes.
>>there is a partition table, what is the best way to remove the partition from it how,
Do You want to
remove ONE partition among many? Then use

sql> alter table partitioned drop partition partition_name;

or
convert the table into a single partition / or make it into a non-partitioned table by EXCHANGING the partition.
I have no idea what will happen to dependencies and will leave it for yourself to test.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/partiti.htm#11985

[Updated on: Thu, 27 April 2006 05:00]

Report message to a moderator

Re: Drop unused Schema [message #169547 is a reply to message #169503]

Thu, 27 April 2006 09:48

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Thanks Mahesh for your response

I am following the link you provided for removing the partitions to make that table unpartitioned,
I had a Range partitioned table, around 120 partitions now i want to remove those partitions, the document says
Use Merge if you want to keep data and drop partitions

You are allowed to merge the contents of two adjacent range partitions into one partition. 
ALTER TABLE four_seasons 
MERGE PARTITIONS quarter_one, quarter_two INTO PARTITION quarter_two;

thing is not 1 or 2, i just want to remove all the partitions basically to make that table unpartitioned now, How can i do it, what is the best way to unpartitioned the table keeping the data.

Thanks a Lot Again.

[Updated on: Thu, 27 April 2006 10:11]

Report message to a moderator

Previous Topic:	Create oracle session manually
Next Topic:	[poll ] Oracle still vulnerable?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 11 20:15:49 CST 2024