Home » RDBMS Server » Server Administration » Drop Tablespace
Drop Tablespace [message #233371] Wed, 25 April 2007 20:51 Go to next message
kmkumar24
Messages: 69
Registered: January 2007
Location: Singapore
Member
Hi All,

I was trying to drop all the tablespace including the contents and datafiles. I followed with the below said command but it doesn't works.

SQL> @droptablespace.sql
drop tablespace BPL_DAY1 including contents and datafiles
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace

Is there is any other command to drop the tablespace including the datafiles.

Thanks in advance

Magesh
Re: Drop Tablespace [message #233373 is a reply to message #233371] Wed, 25 April 2007 21:13 Go to previous messageGo to next message
colly_liu
Messages: 6
Registered: April 2007
Junior Member
drop the partition table at first,then drop your tablespace
Re: Drop Tablespace [message #233374 is a reply to message #233373] Wed, 25 April 2007 21:18 Go to previous messageGo to next message
kmkumar24
Messages: 69
Registered: January 2007
Location: Singapore
Member
Hi,

Can you tell me how to delete the partition table. b'cos i inserted this tables new in my development by running the scripts. So i have no idea about which are the tables have the partition in which tablespaces.

Could you give me the query to delete this.

Thanks for reply.
Re: Drop Tablespace [message #233377 is a reply to message #233374] Wed, 25 April 2007 21:48 Go to previous messageGo to next message
kmkumar24
Messages: 69
Registered: January 2007
Location: Singapore
Member
Hi,

Here are some of my partioned table with the table name and the tablespace name.

TABLE_NAME TABLESPACE_NAME PAR
------------------------------ ------------------------------ ---
LOGMNRT_ATTRCOL$ NO
LOGMNRT_COL$ NO
LOGMNRT_TAB$ NO
LOGMNRT_OBJ$ NO
LOGMNRT_DICTIONARY$ NO
LOGMNRT_MDDL$ NO
LOGMNRT_SEED$ NO
LOGMNR_INDCOMPART$ YES
LOGMNR_INDSUBPART$ YES
LOGMNR_INDPART$ YES
LOGMNR_LOBFRAG$ YES

TABLE_NAME TABLESPACE_NAME PAR
------------------------------ ------------------------------ ---
LOGMNR_ICOL$ YES
LOGMNR_CCOL$ YES
LOGMNR_CDEF$ YES
LOGMNR_LOB$ YES
LOGMNR_ATTRIBUTE$ YES
LOGMNR_COLTYPE$ YES
LOGMNR_TYPE$ YES
LOGMNR_TABCOMPART$ YES
LOGMNR_TABSUBPART$ YES
LOGMNR_TABPART$ YES
LOGMNR_IND$ YES

TABLE_NAME TABLESPACE_NAME PAR
------------------------------ ------------------------------ ---
LOGMNR_TS$ YES
LOGMNR_ATTRCOL$ YES
LOGMNR_COL$ YES
LOGMNR_TAB$ YES
LOGMNRC_GSII YES
LOGMNRC_GTCS YES
LOGMNRC_GTLO YES
LOGMNR_USER$ YES
LOGMNR_OBJ$ YES
LOGMNR_DICTIONARY$ YES
LOGMNR_DICTSTATE$ YES

Now can any one tell me how to delete the tablespace by dropping the partitioned tables.

Thanks in Advance
Re: Drop Tablespace [message #233380 is a reply to message #233371] Wed, 25 April 2007 23:07 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
A tablespace cannot be droped if tablepartitions exist in other tablespaces .
Work around is
Drop the table ,then drop the tablespace
or
For each table,drop the partition exist in other tablespace;Then drop desired tablespace.
or
Move the table partitions exist in other tabelspace to the one you want to drop;then drop the tablespace.

Hope this helps
Re: Drop Tablespace [message #233419 is a reply to message #233377] Thu, 26 April 2007 01:49 Go to previous messageGo to next message
colly_liu
Messages: 6
Registered: April 2007
Junior Member
your tables are logmnr view,are you really drop these?
Re: Drop Tablespace [message #233422 is a reply to message #233419] Thu, 26 April 2007 02:00 Go to previous messageGo to next message
kmkumar24
Messages: 69
Registered: January 2007
Location: Singapore
Member
Hi,

Instead of dropping the tablespaces iam adding datafiles to increase the size. Now the problem is i am able to add a datafile but not resize the datafile. The output is given below.

SQL> alter database datafile '/ls10g/tmsprddb/DATA02.dbf' resize 1000M;

Database altered.

SQL> alter database datafile '/ls10g/tmsprddb/DATA02.dbf' resize 2048M;
alter database datafile '/ls10g/tmsprddb/DATA02.dbf' resize 2048M
*
ERROR at line 1:
ORA-01237: cannot extend datafile 8
ORA-01110: data file 8: '/ls10g/tmsprddb/DATA02.dbf'
ORA-27039: create file failed, file size limit reached
Additional information: 2

Please help me with command to resize the datafile instead off adding new datafile to the tablespace.

Thanks for all your support.


Magesh
Re: Drop Tablespace [message #233428 is a reply to message #233422] Thu, 26 April 2007 02:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have an OS limit to 2GB.
Check ulimit from your Oracle owner.
Check hard limit.

Regards
Michel
Re: Drop Tablespace [message #233467 is a reply to message #233371] Thu, 26 April 2007 04:20 Go to previous message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Usualy fileysystems in unix has largefiles disabled which doest allow you to create file larger thar 2GB.Check with your unix admins on this.
Previous Topic: Unable to connect to sqlplus
Next Topic: Running .exe
Goto Forum:
  


Current Time: Fri Dec 09 09:30:08 CST 2016

Total time taken to generate the page: 0.12888 seconds