Home » SQL & PL/SQL » SQL & PL/SQL » tables in non-default tablespace
tables in non-default tablespace [message #939] Thu, 14 March 2002 10:05 Go to next message
Allan Tingey
Messages: 1
Registered: March 2002
Junior Member
Hi,

How do you select (drop, etc...) tables that are
not in your default tablespace?

Thanks,
Al
Re: tables in non-default tablespace [message #940 is a reply to message #939] Thu, 14 March 2002 10:25 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
do u mean schema?
Re: tables in non-default tablespace [message #942 is a reply to message #939] Thu, 14 March 2002 10:47 Go to previous messageGo to next message
Mike
Messages: 417
Registered: September 1998
Senior Member
There is no such command.

I recommand you to use the view user_segments to create a script which will delete all the related tables. For example

select 'DROP TABLE '|| SEGMENT_NAME ||';' from user_segments where tablespace_name <>'YOUR_DEFAULT_TS';

You may have to use the drop table option CASCADE CONSTRAINTS (be carefull with it).

HTH
Mike
Re: tables in non-default tablespace [message #957 is a reply to message #939] Fri, 15 March 2002 04:43 Go to previous message
Grant
Messages: 578
Registered: January 2002
Senior Member
You can move tables and indexes to your default tablespace.

alter TABLENAME move tablespace TABLESPACENAME;
alter index INDEXNAME rebuild tablespace TABLESPACENAME;
Previous Topic: Work Tables/Temp Tables
Next Topic: Group By
Goto Forum:
  


Current Time: Fri Mar 29 10:53:22 CDT 2024